Solved

Oracle keeps changing execution plan

Posted on 2011-09-08
15
694 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 74

Expert Comment

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

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

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 76

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 76

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle 11g 23 106
Row_number in SQL 6 33
Need SQL Query to Find Foreign-keys Without Indexed Columns 4 25
join a table with user_tab_columns in oracle 3 48
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

808 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