Home » RDBMS Server » Performance Tuning » Execution plan changed automatically. How does that happen?
Execution plan changed automatically. How does that happen? [message #231922] Wed, 18 April 2007 19:48 Go to next message
sganny
Messages: 1
Registered: April 2007
Junior Member
I had a slow running query. It used to take almost a two minutes to get results.

all of a sudden, one fine day without any changes to the query, the query started executing very fast and gave me results back in 2-3 seconds. when we looked at the query plan it changed. what causes the execution plan to change? this is pretty urgent. if someone can throw some light on what can be done for this without having to change the query, it will be great!

the execution plan for the slow and the fast query are as follows:

Slow query
----------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=24974 Card=1 Bytes=1
          026)

   1    0   SORT (UNIQUE) (Cost=24974 Card=1 Bytes=1026)
   2    1     NESTED LOOPS (Cost=24960 Card=1 Bytes=1026)
   3    2       NESTED LOOPS (Cost=24959 Card=1 Bytes=1016)
   4    3         HASH JOIN (Cost=24958 Card=1 Bytes=423)
   5    4           TABLE ACCESS (FULL) OF 'ITEM' (Cost=8746 Card=520
          Bytes=189800)

   6    4           MERGE JOIN (CARTESIAN) (Cost=16205 Card=2241038 By
          tes=129980204)

   7    6             TABLE ACCESS (FULL) OF 'SEARCH_RESULTS' (Cost=8
          Card=16360 Bytes=212680)

   8    6             BUFFER (SORT) (Cost=16197 Card=137 Bytes=6165)
   9    8               TABLE ACCESS (BY INDEX ROWID) OF 'ITEM_VIEW_TA
          BLE' (Cost=1 Card=137 Bytes=6165)

  10    9                 INDEX (RANGE SCAN) OF 'EXTERNAL_VIEW_CODE_ID
          X' (NON-UNIQUE)

  11    3         TABLE ACCESS (BY INDEX ROWID) OF 'ITEM_TL' (Cost=1 C
          ard=1 Bytes=593)

  12   11           INDEX (UNIQUE SCAN) OF 'ITEM_TL_PK' (UNIQUE)
  13    2       INDEX (UNIQUE SCAN) OF 'CATEGORY_ITEM_PK' (UNIQUE)

Fast Query
----------

0               SELECT STATEMENT Optimizer=CHOOSE       21      1       1029
1       0       -SORT (UNIQUE)  21      1       1029
2       1       --NESTED LOOPS  7       1       1029
3       2       ---NESTED LOOPS         6       1       1019
4       3       ----NESTED LOOPS        5       1       425
5       4       -----MERGE JOIN (CARTESIAN)     4       1       57
6       5       ------TABLE ACCESS (FULL) of 'SEARCH_RESULTS'    2      
1       13
7       5       ------BUFFER (SORT)     2       145     6380
8       7       -------TABLE ACCESS (BY INDEX ROWID) of
'ITEM_VIEW_TABLE'        2       145     6380
9       8       --------INDEX (RANGE SCAN) of 'EXTERNAL_VIEW_CODE_IDX'
(NON-UNIQUE)              145      
10      4       -----TABLE ACCESS (BY INDEX ROWID) of 'ITEM'     1      
1       368
11      10      ------INDEX (UNIQUE SCAN) of 'REQCAT.ITEM_PK' (UNIQUE)         
1        
12      3       ----TABLE ACCESS (BY INDEX ROWID) of 'ITEM_TL'   1      
1       594
13      12      -----INDEX (UNIQUE SCAN) of 'ITEM_TL_PK' (UNIQUE)       
        1        
14      2       ---INDEX (UNIQUE SCAN) of 'CATEGORY_ITEM_PK' (UNIQUE)   
        1       10
ID      PA_ID   Execution Plan  COST    CARD    BYTES


[Moderator: added CODE tags]

[Updated on: Wed, 18 April 2007 23:41] by Moderator

Report message to a moderator

Re: Execution plan changed automatically. How does that happen? [message #231967 is a reply to message #231922] Wed, 18 April 2007 23:46 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oracle's Cost based Optimizer uses statistics stored against the tables and indexes to determine what it thinks will be the best way to execute the query.

If any of those statistics are inaccurate or out of date, it can choose a bad plan. Simply re-gathering godd statistics to replace bad on a single table or index can affect the execution plan of the entire query.

In 10g, Oracle can also perform dynamic sampling, so the plan can change even if the statistics do not: scary.

Ross Leishman
Previous Topic: if the data is already available in cache,will index be used if we query again?
Next Topic: finding Tuing problem
Goto Forum:
  


Current Time: Thu Dec 12 05:56:17 CST 2024