Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> equivalent plans but higher consistent gets

equivalent plans but higher consistent gets

From: Richard Kuhler <noone_at_nowhere.com>
Date: Mon, 23 Feb 2004 21:47:44 GMT
Message-ID: <4yu_b.1129$wP3.1010@twister.socal.rr.com>


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 Kuhler
Received on Mon Feb 23 2004 - 15:47:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US