Solved

Oracle keeps changing execution plan

Posted on 2011-09-08
15
679 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
  • 6
  • 5
  • 4
15 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
can you post the query and use dbms_xplan.display  to generate the plan description
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
also, what version of the database?
0
 
LVL 76

Expert Comment

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

Please include all 4 numbers.  For example:  10.2.0.4
0
 

Author Comment

by:GerardMcL_1
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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 76

Expert Comment

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

For example the in-list isn't dynamic?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
you didn't answer the question...

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

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
http:#36522037

this is probably your problem
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now