Solved

Oracle keeps changing execution plan

Posted on 2011-09-08
15
697 Views
Last Modified: 2012-05-12
Hi,

I have a piece of code in Oracle that since 2 days ago is now taking approx x10 longer to run i.e. 2mins -> 20 mins.

It appears, looking at the explain plan, that Oracle is forcing the code down the wrong path. The columns exist within the code for the optimizer to use an index but it refuses. Instead it does a 'bitmap conversion to rowids' or 4 partitions worth of data approx 8mill rows.

I have added the index hint to no avail. The weird thing is that every now and again when I check the plan it looks absolutely fine. I rerun it and it goes back to bit map conversion. Recent stats have been gathered on the index, I have changed the code around to try and force the optimizer to use the index but still no joy.

Has anyone experience of or know why Oracle is now all of a sudden choosing to take the wrong road as it were?

All help is greatly appreciated, this is having a very large, very negative impact.
Thanks.


/* Explain Plan


1 Some of the entries in the bitmap index Partitions determined by Key Values were accessed.
2 The bitmaps returned from steps 1 were converted to rowids.
3 Rows from table Partitions determined by Key Values were accessed using rowid got from a local (single-partition) index.
4 A range of partitions of steps 3 were accessed..
5 One or more rows were retrieved using index Partitions determined by Key Values. The index was scanned in ascending order..
6 A range of partitions of steps 5 were accessed..
7 For each row retrieved by step 4, the operation in step 6 was performed to find a matching row.
8 Rows from table TABLE1 were accessed using rowid got from a local (single-partition) index.
9 WINDOW SORT PUSHED RANK
10 A view definition was processed, either from a stored view SCHEMA or as defined by steps 9.
11 Rows were returned by the SELECT statement.


*/
0
Comment
Question by:GerardMcL_1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
15 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 36503756
can you post the query and use dbms_xplan.display  to generate the plan description
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36503773
Depending on your version of Oracle the Optimizer has issues at times.  It is getting better with each new release.

If the stats aren't changing the plans shouldn't be changing.  Might be a bug.

As far as hints go:  They can be ignored at times.

I find the best way to force index use is go RULE based (for as long as it still exists):

select /*+ RULE */ ...


0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36503793
also, what version of the database?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36503808
>>also, what version of the database?

Please include all 4 numbers.  For example:  10.2.0.4
0
 

Author Comment

by:GerardMcL_1
ID: 36503908
Plan and code. We are on Oracle 10g, I can't give you the full details as I'm not DBA and not 100% sure

PLAN_TABLE_OUTPUT                                                              
--------------------------------------------------------------------------------
Plan hash value: 502678818                                                      
                                                                               
--------------------------------------------------------------------------------
-----------------------------------------------                                
                                                                               
| Id  | Operation                              | Name                 | Rows  |
Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                
                                                                               
--------------------------------------------------------------------------------
-----------------------------------------------                                
                                                                               

PLAN_TABLE_OUTPUT                                                              
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                      |     1 |
   59 |   289   (3)| 00:00:04 |       |       |                                
                                                                               
|*  1 |  VIEW                                  |                      |     1 |
   59 |   289   (3)| 00:00:04 |       |       |                                
                                                                               
|*  2 |   WINDOW SORT PUSHED RANK              |                      |     1 |
  143 |   289   (3)| 00:00:04 |       |       |                                
                                                                               
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID   | TBL_MDA_FACT         |     1 |
   77 |    33   (0)| 00:00:01 |       |       |                                

PLAN_TABLE_OUTPUT                                                              
--------------------------------------------------------------------------------
                                                                               
|   4 |     NESTED LOOPS                       |                      |     1 |
  143 |   288   (2)| 00:00:04 |       |       |                                
                                                                               
|   5 |      PARTITION RANGE ITERATOR          |                      |     1 |
   66 |   255   (2)| 00:00:04 |   KEY |   106 |                                
                                                                               
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID| TBL_LOT_TRANSACTIONS |     1 |
   66 |   255   (2)| 00:00:04 |   KEY |   106 |                                
                                                                               
|   7 |        BITMAP CONVERSION TO ROWIDS     |                      |       |

PLAN_TABLE_OUTPUT                                                              
--------------------------------------------------------------------------------
      |            |          |       |       |                                
                                                                               
|*  8 |         BITMAP INDEX RANGE SCAN        | INDX_TXNS_DT_KEY     |       |
      |            |          |   KEY |   106 |                                
                                                                               
|   9 |      PARTITION RANGE ITERATOR          |                      |     1 |
      |    33   (0)| 00:00:01 |   KEY |   KEY |                                
                                                                               
|* 10 |       INDEX RANGE SCAN                 | TBL_MDA_FACT_PK      |     1 |
      |    33   (0)| 00:00:01 |   KEY |   KEY |                                
                                                                               

PLAN_TABLE_OUTPUT                                                              
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------                                
                                                                               
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   1 - filter("SEQ"=1 AND "A"."TRACK_OUT_DT">"A"."TXN_DT")                      
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "TXN"."LOT_ID","MDA"."MDA_CREATED
_DT" ORDER BY "TXN_DT")<=1)                                                    
                                                                               

PLAN_TABLE_OUTPUT                                                              
--------------------------------------------------------------------------------
   3 - filter((SUBSTR("MDA"."ROUTE",1,2)='QL' OR SUBSTR("MDA"."ROUTE",1,2)='BB'
OR SUBSTR("MDA"."ROUTE",1,2)='MO') AND                                          
                                                                               
              "MDA"."TRACK_OUT_DT">=TO_CHAR(SYSDATE@!-30,'yyyymmdd hh24miss') AN
D "TXN"."TXN_DT"<="MDA"."TRACK_OUT_DT" AND                                      
                                                                               
              "TXN"."ROUTE"="MDA"."ROUTE" AND "TXN"."ROUTE_REV"="MDA"."ROUTE_REV
")                                                                              
                                                                               
   6 - filter("TXN"."ACTIVITY"='AdhocReassign' AND "TXN"."STEP"='QM_STORE')    
   8 - access("TXN"."TXN_DT">=TO_CHAR(SYSDATE@!-30,'yyyymmdd hh24miss'))        

PLAN_TABLE_OUTPUT                                                              
--------------------------------------------------------------------------------
       filter("TXN"."TXN_DT">=TO_CHAR(SYSDATE@!-30,'yyyymmdd hh24miss'))        
  10 - access("MDA"."LOT_ID"="TXN"."LOT_ID" AND "MDA"."MDA_CREATED_DT">=TO_CHAR(
SYSDATE@!-30,'yyyymmdd hh24miss') AND                                          
                                                                               
              "TXN"."TXN_DT">="MDA"."MDA_CREATED_DT")                          


SELECT mda_id
  FROM (SELECT
              mda.lot_id,
               mda.mda_created_dt,
               mda.track_out_dt,
               txn.txn_dt,
               mda.mda_id,
               ROW_NUMBER () OVER (PARTITION BY txn.lot_id, mda.mda_created_dt ORDER BY txn_dt) seq
          FROM wlos_owner.tbl_mda_fact mda,
               wlos_owner.tbl_lot_transactions txn
         WHERE  mda.lot_id = txn.lot_id
               AND txn.activity = 'AdhocReassign'
               AND txn.txn_dt BETWEEN mda.mda_created_dt AND mda.track_out_dt
               AND txn.step = 'QM_STORE'
               AND txn.route = mda.route
               AND txn.route_rev = mda.route_rev
               AND SUBSTR (mda.route, 1, 2) IN ('QL', 'BB', 'MO')
               AND mda.mda_created_dt >= TO_CHAR (SYSDATE - 30, 'yyyymmdd hh24miss')) a
 WHERE seq = 1 AND a.track_out_dt > a.txn_dt

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36503944
>>I can't give you the full details as I'm not DBA and not 100% sure

from a sql prompt:
select * from v$version;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36503951
And between 'good' runs and 'bad' runs nothing in that query changes?  

For example the in-list isn't dynamic?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36504016
AND mda.mda_created_dt >= TO_CHAR (SYSDATE - 30, 'yyyymmdd hh24miss')) a


is mda_create_dt  a DATE?  or a string?

if it's a date then use this


AND mda.mda_created_dt >= SYSDATE - 30


is there anything at all that changes in any of the fields between runs?
step, activity, route?
0
 

Author Comment

by:GerardMcL_1
ID: 36508762
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

Nothing changes between runs.
TO_CHAR (SYSDATE - 30, 'yyyymmdd hh24miss') is actually formatted earlier in the proc a variable passed into the query but this is the form it takes.

mda.lot_id = txn.lot_id
AND txn.activity = 'AdhocReassign'
AND txn.txn_dt BETWEEN mda.mda_created_dt AND mda.track_out_dt

The three columns in bold make up an index indx_local_lottxns, but the optimizer will not use this index              
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36509672
you didn't answer the question...

is mda_create_dt  a DATE?  or a string?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36509976
also, what are the data types for these columns?

txn.txn_dt  

mda.track_out_dt


are they dates or are they strings?


"this is the form it takes."   -  if you're trying make a "date" have a format, you have the wrong idea.  dates don't have formats,  only strings do.


0
 

Author Comment

by:GerardMcL_1
ID: 36522037
these columns are all varchar fields
we turn the date into a varchar and format the string to look like 20110912 090000
0
 

Accepted Solution

by:
GerardMcL_1 earned 0 total points
ID: 36579263
Thanks for the help guys.
Eventually I just had to change code to use a different index and help performance
0
 

Author Closing Comment

by:GerardMcL_1
ID: 36708039
We seemed to hit a brick wall with other suggestions.
I was forced to change the code to help my performance although I'm still not any clearer as to what caused the optimizer to shift paths.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36579668
http:#36522037

this is probably your problem
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question