| Execution plan changed automatically. How does that happen? [message #231922] |
Wed, 18 April 2007 19:48  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
