OraFAQ Forum: Performance Tuning » Query chooses bitmap index and runs slower

Home » RDBMS Server » Performance Tuning » Query chooses bitmap index and runs slower (11.2.0.2/11.2.0.4 Enterprise, RHL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query chooses bitmap index and runs slower [message #625115]

Wed, 01 October 2014 08:19

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 1934 times)

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625116 is a reply to message #625115]

Wed, 01 October 2014 08:20

d_seng
Messages: 78
Registered: November 2011
Location: UK

Member

Plan for 11.2.0.4

Attachment: 11204.pdf
(Size: 425.21KB, Downloaded 2035 times)

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625118 is a reply to message #625116]

Wed, 01 October 2014 08:41

BlackSwan
Messages: 26766
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!

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625121 is a reply to message #625118]

Wed, 01 October 2014 09:13

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...

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625122 is a reply to message #625121]

Wed, 01 October 2014 09:16

BlackSwan
Messages: 26766
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/

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625123 is a reply to message #625122]

Wed, 01 October 2014 09:29

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

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625124 is a reply to message #625123]

Wed, 01 October 2014 09:35

BlackSwan
Messages: 26766
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!

Report message to a moderator

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?

Report message to a moderator

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 #625135 is a reply to message #625133]

Wed, 01 October 2014 11:22

BlackSwan
Messages: 26766
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?

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.

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625137 is a reply to message #625115]

Wed, 01 October 2014 12:09

John Watson
Messages: 8929
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') ?

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625147 is a reply to message #625137]

Wed, 01 October 2014 19:02

Kevin Meade
Messages: 2103
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

Report message to a moderator

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.

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625200 is a reply to message #625147]

Thu, 02 October 2014 10:40

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

Attachment: ENH106755_SQL.sql
(Size: 59.50KB, Downloaded 1624 times)

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625201 is a reply to message #625200]

Thu, 02 October 2014 10:41

d_seng
Messages: 78
Registered: November 2011
Location: UK

Member

11.2.0.2 plan attached

Attachment: 11202.log
(Size: 26.10KB, Downloaded 1589 times)

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625202 is a reply to message #625201]

Thu, 02 October 2014 10:41

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 1636 times)

Report message to a moderator

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: 8929
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.

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625216 is a reply to message #625115]

Thu, 02 October 2014 21:12

Kevin Meade
Messages: 2103
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

Report message to a moderator

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: 8929
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.

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625277 is a reply to message #625115]

Fri, 03 October 2014 09:24

Kevin Meade
Messages: 2103
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.

Report message to a moderator

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: 8929
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.

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625301 is a reply to message #625282]

Sat, 04 October 2014 07:41

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:

11.2.0.2 with ALLSTATS LAST plan option (11202_AllStatsLast.log): Filter cardinality estimates way off (step 39) but still uses FTS
11.2.0.2 with ADVANCED plan option (11202_Advanced.log)
11.2.0.4 with ALLSTATS LAST plan option and ODS=4 (11204_ODS4_AllStatsLast_Original.log): Slow
11.2.0.4 with ADVANCED plan option and ODS=4 (11204_ODS4_Advanced_Original.log)
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:

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)
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

Attachment: 1102-1104.zip.txt
(Size: 61.23KB, Downloaded 1639 times)

Report message to a moderator

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

Re: Query chooses bitmap index and runs slower [message #625392 is a reply to message #625365]

Tue, 07 October 2014 05:19

Flyby
Messages: 188
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?

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625407 is a reply to message #625392]

Tue, 07 October 2014 14:59

d_seng
Messages: 78
Registered: November 2011
Location: UK

Member

Yes, stats were regenerated

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625561 is a reply to message #625407]

Fri, 10 October 2014 04:00

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.

Report message to a moderator

Re: Query chooses bitmap index and runs slower [message #625575 is a reply to message #625561]

Fri, 10 October 2014 09:18

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Excellent detective work. Thank you for sharing the resolution.

Kevin

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 17 23:11:53 CDT 2024