Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> equivalent plans but higher consistent gets
Why would two plans that seem to access the same data in the same manner
have drastically different consistent gets?
The cardinality in these plans are exactly right. There is only one row in T1 and 7168 rows in T2. Both plans are for the same query on the same instance and return the exact same results (a hint was used to force one plan over the other). The only difference in the plans is a full scan of T1 (which only has 1 row and a single 16k extent) instead of using an index. Also, the position where the 'TABLE ACCESS' for T2 appears in the plan is different. So why are there 6 times the consistent gets with the first plan?
SELECT STATEMENT Optimizer=CHOOSE (Cost=104 Card=7168 Bytes=93184) TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=102 Card=7168 Bytes=57344) NESTED LOOPS (Cost=104 Card=7168 Bytes=93184) TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=5) INDEX (RANGE SCAN) OF 'T2_I1' (NON-UNIQUE) (Cost=3 Card=7168)
7833 consistent gets
SELECT STATEMENT Optimizer=CHOOSE (Cost=101 Card=7168 Byte=207872) NESTED LOOPS (Cost=101 Card=7168 Byte=207872) TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Byte=13) INDEX (UNIQUE SCAN) OF 'T1_I1' (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=100 Card=7168 Byte=114688) INDEX (RANGE SCAN) OF 'T2_I1' (NON-UNIQUE) 1165 consistent gets
Since there are 7168 rows to get during the TABLE ACCESS of T2, it would seem that the second plan is using some optimization to not have to perform the get when it wants the same block it just used? But if that were true why wouldn't the first plan get to use the optimization as well?
Note: the queries were run repeatedly in different orders, etc.. etc... The consistent gets are not being inflated by recursive calls or anything else.
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production With the Partitioning and Oracle Data Mining options JServer Release 9.2.0.2.0 - Production
-- Richard KuhlerReceived on Mon Feb 23 2004 - 15:47:44 CST