|
|
|
|
|
|
|
Re: Query chooses bitmap index and runs slower [message #625133 is a reply to message #625124] |
Wed, 01 October 2014 11:14 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
@OP, in addition to Blackswan's suggestion, I am curious to know whether your star schema is actually for a data warehousing environment. And is it being accessed through BI middleware? My curiosity is because I see you mentioned BITMAP index.
If your answer to my above questions is yes, then, could you tell us whether :
1. The issue is with only the fact table or the dimension tables too?
2. Does the issue occur in an ETL process?
|
|
|
Re: Query chooses bitmap index and runs slower [message #625134 is a reply to message #625133] |
Wed, 01 October 2014 11:21 |
|
d_seng
Messages: 78 Registered: November 2011 Location: UK
|
Member |
|
|
Hi Lalit, yes it is for a DWH environment, Cognos Framework Manager is the BI middleware.
The issue only occurs when running a report that joins the fact table to the dimension table, which now uses bitmap indexes and a subsequent nested loop. The ETL processing time has increased only very slightly.
[Updated on: Wed, 01 October 2014 11:21] Report message to a moderator
|
|
|
|
Re: Query chooses bitmap index and runs slower [message #625136 is a reply to message #625135] |
Wed, 01 October 2014 11:43 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
@d_seng, so my guess was correct. What I have observed with BI/product developers is that they keep creating unnecessary indexes without testing. Mostly, because they have a myth that creating indexes would improve performance. However, it is NOT ALWAYS TRUE.
Just go through the links provided by Blackswan, you will understand about indexes and their usage.
Now, based on your answers to my questions, I am guessing further that the queries are also generated through application framework, isn't it? Please confirm.
|
|
|
|
|
Re: Query chooses bitmap index and runs slower [message #625179 is a reply to message #625147] |
Thu, 02 October 2014 05:17 |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
Additionally, if you want to research why it has gotten it wrong, look into the 10053 trace - but this is only of often value if you have a way to get the old plan back without hints (like using kevins methods of overruling the optimizer) or you've spent a lot of time in them.
Very interesting once you get under the hood though.
|
|
|
|
|
|
Re: Query chooses bitmap index and runs slower [message #625205 is a reply to message #625202] |
Thu, 02 October 2014 11:43 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Looking at those exec plans, your problem might (only might) be the cardinality mis-estimates. For instance, this, filter(ROUND("GG_F_POLICY_FACTS_FR55"."F_REPORTINGPERIOD"/100,0)<2013) where there are ten or twenty times as many rows as the CBO expects. You need to remove the function call (which you really don't need) or create a stats extension to correct this.
|
|
|
|
Re: Query chooses bitmap index and runs slower [message #625217 is a reply to message #625216] |
Fri, 03 October 2014 01:15 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Kevin, I wasn't aware of a change to dynamic sampling in 11.2.0.4. I shall have to read up on this.
I always set optimizer_dynamic_sampling=4. This has sometimes given me spectacular performance improvements and has never caused a problem. Level 3 lets the CBO look into functions on columns in predicates, and level 4 lets it investigate complex predicates with ANDs and ORs. This is the lazy DBA's answer to extended stats: let the CBO generate them on the fly.
|
|
|
|
Re: Query chooses bitmap index and runs slower [message #625282 is a reply to message #625277] |
Fri, 03 October 2014 10:54 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Thank you for the reference, Kevin. I had been under the impression that O_D_S=11 was a 12.x new feature (and that is indeed how I, and Oracle Uni, have been teaching it for the last year). The doc is in fact not correct: level 3 and 4 have always done dynamic sampling if the predicate is complex, irrespective of whether the tables have stats.
|
|
|
|
Re: Query chooses bitmap index and runs slower [message #625365 is a reply to message #625301] |
Mon, 06 October 2014 14:15 |
|
d_seng
Messages: 78 Registered: November 2011 Location: UK
|
Member |
|
|
Further notes: I copied all the relevant tables/indexes to another 11.2.0.4 database (running on different hardware, with fewer CPUs but with exactly the same database parameters), gathered statistics in exactly the same manner (FOR ALL COLUMNS SIZE AUTO) and the query performs just fine there.
There were a few other reports that use the same fact table, and they behave exactly the same after copying: the queries use bitmap indexes on the fact table on the old 11.2.0.4 database but use the more efficient FTS/hash join on the new 11.2.0.4 database.
Then I chose a few other queries that use different fact tables and then run just fine on both the old and the new 11.2.0.4 database. Somehow this problem seems to be limited to one table on one specific database.
I cannot come up with any sensible explanation...
[Updated on: Mon, 06 October 2014 14:15] Report message to a moderator
|
|
|
|
|
|
|