Re: Plan change after moving to 10g 10.2.0.3
- From: hrishy <hrishys@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 18 Dec 2008 07:57:30 +0000 (GMT)
Hi
I am attaching the plan good and the bad plans.
First one using NL and a index is a good plan and the one using has join and a
full table join is bad
regards
Hrishy
SELECT sum((col1)/(col2+col1))*col1*100/(col3)
FROM tab1a, tab2 b
WHERE a.tab2_id = b.id
AND a.col2 = 26538
AND b.col3 > 0
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts |
E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 |
1 | 1 |00:00:00.30 | 64362 | 2 |
| 2 | NESTED LOOPS | | 1 |
23662 | 21348 |00:00:00.27 | 64362 | 2 |
| 3 | TABLE ACCESS BY INDEX ROWID| tab1 | 1 |
23662 | 21348 |00:00:00.03 | 316 | 1 |
|* 4 | INDEX RANGE SCAN | col2_idx | 1 |
23662 | 21348 |00:00:00.01 | 51 | 0 |
|* 5 | TABLE ACCESS BY INDEX ROWID| tab2 | 21348 | 1 |
21348 |00:00:00.21 | 64046 | 1 |
|* 6 | INDEX UNIQUE SCAN | tab2_PK | 21348 | 1 |
21348 |00:00:00.12 | 42698 | 0 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."col2"=26538)
5 - filter("B"."col3">0)
6 - access("A"."tab2_ID"="B"."ID")
Plan in 10g 10.2.0.3
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2369063540
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts |
E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 |
1 | 1 |00:00:45.80 | 196K| 193K| | | |
| 3 | TABLE ACCESS BY INDEX ROWID| tab1 | 1 | 24189 |
21348 |00:00:00.02 | 318 | 0 | | | |
|* 4 | INDEX RANGE SCAN | col2_idx | 1 | 24189 | 21348
|00:00:00.01 | 51 | 0 | | | |
|* 5 | TABLE ACCESS FULL | tab2 | 1 | 12M|
12M|00:00:37.20 | 196K| 193K| | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("A"."tab2_ID"="B"."ID")
4 - access("A"."col2"=26538)
5 - filter("B"."col3">0)
Other related posts:
