[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 739
  • Last Modified:

Oracle keeps changing execution plan

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
GerardMcL_1
Asked:
GerardMcL_1
  • 6
  • 5
  • 4
1 Solution
 
sdstuberCommented:
can you post the query and use dbms_xplan.display  to generate the plan description
0
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
also, what version of the database?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
slightwv (䄆 Netminder) Commented:
>>also, what version of the database?

Please include all 4 numbers.  For example:  10.2.0.4
0
 
GerardMcL_1Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
slightwv (䄆 Netminder) Commented:
And between 'good' runs and 'bad' runs nothing in that query changes?  

For example the in-list isn't dynamic?
0
 
sdstuberCommented:
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
 
GerardMcL_1Author Commented:
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
 
sdstuberCommented:
you didn't answer the question...

is mda_create_dt  a DATE?  or a string?
0
 
sdstuberCommented:
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
 
GerardMcL_1Author Commented:
these columns are all varchar fields
we turn the date into a varchar and format the string to look like 20110912 090000
0
 
GerardMcL_1Author Commented:
Thanks for the help guys.
Eventually I just had to change code to use a different index and help performance
0
 
GerardMcL_1Author Commented:
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
 
sdstuberCommented:
http:#36522037

this is probably your problem
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 6
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now