Home » RDBMS Server » Performance Tuning » Query chooses bitmap index and runs slower (11.2.0.2/11.2.0.4 Enterprise, RHL)
Query chooses bitmap index and runs slower [message #625115] Wed, 01 October 2014 08:19 Go to next message
d_seng
Messages: 78
Registered: November 2011
Location: UK
Member
Hi all,

I have an 11.2.0.2 database at my workplace. Cognos Framework Manager is used to generate reports.

One of the queries uses a fact table with 4 million rows (full table scan) and hash joins with a handful of other dimension tables (one of them containing 1.4 million rows) and returns the results in 3-4 minutes. There are a couple of UNION ALLs. Statistics are up to date.

Following an acquisition, we migrated the data to an 11.2.0.4 database, with identical database parameters, table structures & indexes, except for a couple of additional numeric fields in the fact table. We bumped up the fact table to 6 million rows and the large dimension table to 2.2 million rows and gathered the stats. Now the same query has started to use bitmap indexes on the fact/dimension tables followed by a nested loop and takes 40 minutes to run.

Supplementing the query with a FULL hint on the fact table reverts it back to 3-4 minutes.

I am puzzled by the fact that Oracle is choosing to use the more expensive plan even when the statistics are up to date.

I am attaching the plans for both the databases. I'll be grateful if anybody can spot anything obvious and advise.

Cheers,
Dhruva
  • Attachment: 11202.pdf
    (Size: 308.89KB, Downloaded 157 times)
Re: Query chooses bitmap index and runs slower [message #625116 is a reply to message #625115] Wed, 01 October 2014 08:20 Go to previous messageGo to next message
d_seng
Messages: 78
Registered: November 2011
Location: UK
Member
Plan for 11.2.0.4
  • Attachment: 11204.pdf
    (Size: 425.21KB, Downloaded 215 times)
Re: Query chooses bitmap index and runs slower [message #625118 is a reply to message #625116] Wed, 01 October 2014 08:41 Go to previous messageGo to next message
BlackSwan
Messages: 25859
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

for BOTH cases!
Re: Query chooses bitmap index and runs slower [message #625121 is a reply to message #625118] Wed, 01 October 2014 09:13 Go to previous messageGo to next message
d_seng
Messages: 78
Registered: November 2011
Location: UK
Member
Sorry, just to clarify my expectations, I am not looking for optimizing the SQL, I already am aware that a full scan and hash join is the best method. Unfortunately there is no native support for hints in Cognos. The SQL generated by Cognos also cannot be changed.

We do not have access to the O/S to run tkprof owing to data centre restrictions.

I am just hoping if somebody has faced a scenario where the plan changes drastically by merely adding more rows, that too from full table to indexes, and can possibly explain if there is an underlying cause for that...
Re: Query chooses bitmap index and runs slower [message #625122 is a reply to message #625121] Wed, 01 October 2014 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 25859
Registered: January 2009
Location: SoCal
Senior Member
https://blogs.oracle.com/optimizer/entry/how_do_i_capture_a

http://jonathanlewis.wordpress.com/2014/05/23/10053-trace/
Re: Query chooses bitmap index and runs slower [message #625123 is a reply to message #625122] Wed, 01 October 2014 09:29 Go to previous messageGo to next message
d_seng
Messages: 78
Registered: November 2011
Location: UK
Member
Thanks but as I mentioned, we do not have access to the O/S command prompt
Re: Query chooses bitmap index and runs slower [message #625124 is a reply to message #625123] Wed, 01 October 2014 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 25859
Registered: January 2009
Location: SoCal
Senior Member
>Thanks but as I mentioned, we do not have access to the O/S command prompt
if you want answers & they reside on the server, either you get answers or the mystery remains. It is your choice & problem; not ours.
You can't push a string!
Re: Query chooses bitmap index and runs slower [message #625133 is a reply to message #625124] Wed, 01 October 2014 11:14 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3123
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 Go to previous messageGo to next message
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 #625135 is a reply to message #625133] Wed, 01 October 2014 11:22 Go to previous messageGo to next message
BlackSwan
Messages: 25859
Registered: January 2009
Location: SoCal
Senior Member
>Now the same query has started to use bitmap indexes on the fact/dimension tables followed by a nested loop and takes 40 minutes to run.
So what is the result after you DROP the bitmap indexes?
Re: Query chooses bitmap index and runs slower [message #625136 is a reply to message #625135] Wed, 01 October 2014 11:43 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3123
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 #625137 is a reply to message #625115] Wed, 01 October 2014 12:09 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
I can't read your execution plans. Can you format them with EXPLAIN PLAN and DBMS_XPLAN.DISPLAY ? Or better still, with STATISTICS_LEVEL=ALL and DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST') ?
Re: Query chooses bitmap index and runs slower [message #625147 is a reply to message #625137] Wed, 01 October 2014 19:02 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
I would hazard a guess that changes in Cardinality and new Optimizer Enhancements make the difference. Consider the following notes:

1. the 11.2.0.2 plan has a bug in it. It reports that the final result set will contain only one row. I believe this can be traced back to a hash join loosing everything.
2. the 11.2.0.4 plan says the query will return just shy of 90,000 rows. I don't know the right number but that seems way more believable given the other plan numbers.

This suggests to me a few possibilities.

1. there is a possible optimizer update between 11.2.0.2 and 11.2.0.4 that changes the way cardinalities are estimated in joins.
2. changes in actual row counts have crossed a threshold that has changed the driving table and/or join order and access/join cardinality estimates and thus change the plan accordingly.
3. changes in the behavior of DYNAMIC SAMPLING. Though this would not be my first choice given the apparent correct increase in cardinality estimates based on your noted increase in row counts.
4. the 11.2.0.4 plan is also using query factoring whereas the 11.2.0.2 plan is not. So this is a big difference too.

Try doing this, use the OPTIMIZER_FEATURES_ENABLE hint to downgrade used features to 11.2.0.2 to see if it is a feature change that might be part of your problem. As in

  1* alter session set optimizer_features_enable='11.2.0.2'
19:56:35 SQL> /

Session altered.

or
19:56:36 SQL> alter system set optimizer_features_enable='11.2.0.2';

System altered.


This will cause you system to use only 11.2.0.2 features. Then check the query plan again.

Also, please follow through on what John has asked for.

Kevin
Re: Query chooses bitmap index and runs slower [message #625179 is a reply to message #625147] Thu, 02 October 2014 05:17 Go to previous messageGo to next message
Roachcoach
Messages: 1513
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 #625200 is a reply to message #625147] Thu, 02 October 2014 10:40 Go to previous messageGo to next message
d_seng
Messages: 78
Registered: November 2011
Location: UK
Member
Hi Kevin/John, I have now generated the plans as you suggested.

The result:
11.2.0.2 plan is exactly the same as 11.2.0.4 with OPTIMIZER_FEATURES_ENABLE set to 11.2.0.2, and returns the result in <2 minutes as we'd like. The original 11.2.0.4 plan still suffers.

So there IS possibly a change of optimizer logic which is causing Oracle to pick up the less efficient plan. I am in the process of trying to get the data center guys to run a 10053/tkprof which will hopefully reveal why.

Attached with this message is the SQL, followed by the plans
Re: Query chooses bitmap index and runs slower [message #625201 is a reply to message #625200] Thu, 02 October 2014 10:41 Go to previous messageGo to next message
d_seng
Messages: 78
Registered: November 2011
Location: UK
Member
11.2.0.2 plan attached
  • Attachment: 11202.log
    (Size: 26.10KB, Downloaded 110 times)
Re: Query chooses bitmap index and runs slower [message #625202 is a reply to message #625201] Thu, 02 October 2014 10:41 Go to previous messageGo to next message
d_seng
Messages: 78
Registered: November 2011
Location: UK
Member
11.2.0.4 plan attached, first without the parameter change, followed by the run with the parameter change
  • Attachment: 11204.log
    (Size: 64.38KB, Downloaded 94 times)
Re: Query chooses bitmap index and runs slower [message #625205 is a reply to message #625202] Thu, 02 October 2014 11:43 Go to previous messageGo to next message
John Watson
Messages: 7265
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 #625216 is a reply to message #625115] Thu, 02 October 2014 21:12 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
great, so now let us try to see if we can find the feature that might be causing the issue.

without changing the parameter on 11.2.0.4, use the following undocumented format option

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));


This will produce an OUTLINE DATA section. There may be other ways to get it, for example by asking for it +outline. See if by hook or by crook you can get an OUTLINE DATA section from both 11.2.0.2 and 11.2.0.4 and then compare them for differences. Hopefully there will be several new and terms for you to research.

I note in particular the there was an dynamic sampling upgrade done with 11.2.0.4. This my be giving you different cardinalities and thus different plans. Once you get the outline data and we have a look at them, we can talk about using OPT_ESTIMATE hint as a way to force specific cardinalities into the query plan to see if the cardinality estimates are actually crossing a threshold and thus changing the plan.

Kevin
Re: Query chooses bitmap index and runs slower [message #625217 is a reply to message #625216] Fri, 03 October 2014 01:15 Go to previous messageGo to next message
John Watson
Messages: 7265
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 #625277 is a reply to message #625115] Fri, 03 October 2014 09:24 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
According to this is should not matter.

Oracle Database 11g Release 2 (11.2.0.4) New Features in Oracle Database PerformanceThe new and updated performance tuning features include:

•Dynamic statistics enhancements

In previous releases, Oracle Database only gathered dynamic statistics (previously called dynamic sampling) when one or more of the tables in a query did not have optimizer statistics. Starting in Oracle Database 11g Release 2 (11.2.0.4), the optimizer can automatically decide whether dynamic statistics are useful and which dynamic statistics level to use for all SQL statements. For example, the optimizer automatically decides whether to gather dynamic statistics during table scans, index access, joins, and GROUP BY operations. The enhanced behavior is enabled only when the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter is set to the new value of 11.

Unless the OP is using level=11.

Re: Query chooses bitmap index and runs slower [message #625282 is a reply to message #625277] Fri, 03 October 2014 10:54 Go to previous messageGo to next message
John Watson
Messages: 7265
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 #625301 is a reply to message #625282] Sat, 04 October 2014 07:41 Go to previous messageGo to next message
d_seng
Messages: 78
Registered: November 2011
Location: UK
Member
Thanks for your valuable inputs.

I have run the query with a variety of options without changing query first:

  1. 11.2.0.2 with ALLSTATS LAST plan option (11202_AllStatsLast.log): Filter cardinality estimates way off (step 39) but still uses FTS
  2. 11.2.0.2 with ADVANCED plan option (11202_Advanced.log)
  3. 11.2.0.4 with ALLSTATS LAST plan option and ODS=4 (11204_ODS4_AllStatsLast_Original.log): Slow
  4. 11.2.0.4 with ADVANCED plan option and ODS=4 (11204_ODS4_Advanced_Original.log)
  5. 11.2.0.4 with ALLSTATS LAST plan option and ODS=11 (11204_ODS11_AllStatsLast_Original.log): Slow

I may be wrong but I couldn't spot any new major features in the profiles in the 11.2.0.4 version (it's a first for me, I admit).

I then removed the ROUND function from the column, replaced with inequality/BETWEEN and ran:
  1. 11.2.0.4 with ALLSTATS LAST plan option and ODS=4 (11204_ODS4_AllStatsLast_Changed.log): Filter cardinality still way off (step 86) and still slow (uses bitmap)
  2. 11.2.0.4 with ALLSTATS LAST plan option and ODS=11 (11204_ODS11_AllStatsLast_Changed.log): Filter cardinality still way off (step 78) and still slow (uses bitmap)

The ONLY option that works in my favour is changing OPTIMIZER_FEATURES_ENABLE to 11.2.0.2, and both the original query (11204_OFE11202_AllStatsLast_Original.log) and the modified query (11204_OFE11202_AllStatsLast_Changed.log) use FTS despite incorrect cardinality and run in respectable times.

I am still arranging to get the 10053 trace files, but I guess for now there is some information for us to mull over.

Files attached, please rename to zip.

Cheers
Re: Query chooses bitmap index and runs slower [message #625365 is a reply to message #625301] Mon, 06 October 2014 14:15 Go to previous messageGo to next message
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

Re: Query chooses bitmap index and runs slower [message #625392 is a reply to message #625365] Tue, 07 October 2014 05:19 Go to previous messageGo to next message
Flyby
Messages: 185
Registered: March 2011
Location: Belgium
Senior Member
Were the stats of the table updated after the migration from 11.2.0.2 to 11.2.0.4? Does the table have locked down statistics?
Re: Query chooses bitmap index and runs slower [message #625407 is a reply to message #625392] Tue, 07 October 2014 14:59 Go to previous messageGo to next message
d_seng
Messages: 78
Registered: November 2011
Location: UK
Member
Yes, stats were regenerated
Re: Query chooses bitmap index and runs slower [message #625561 is a reply to message #625407] Fri, 10 October 2014 04:00 Go to previous messageGo to next message
d_seng
Messages: 78
Registered: November 2011
Location: UK
Member
A quick update, this statement is not correct: Quote:
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)

STAR_TRANSFORMATION_ENABLED was set to FALSE on this database, I shouldn't have taken the DBA's word for it.

Setting STAR_TRANSFORMATION_ENABLED to FALSE on the original 11.2.0.4 causes Oracle to choose FTS/hash join and return the results in favourable time.
Re: Query chooses bitmap index and runs slower [message #625575 is a reply to message #625561] Fri, 10 October 2014 09:18 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
Excellent detective work. Thank you for sharing the resolution.

Kevin
Previous Topic: improve performance for the query
Next Topic: Default value of SEND_BUF_SIZE and RECV_BUF_SIZE for IBM Aix 7
Goto Forum:
  


Current Time: Sun Feb 25 12:36:55 CST 2018

Total time taken to generate the page: 0.03338 seconds