Re: equivalent plans but higher consistent gets

Richard Kuhler <noone_at_nowhere.com> wrote in message news:<4yu_b.1129$wP3.1010_at_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

These are clearly different plans.

Plan one, join between full table scan and an index range scan plan two, join between a unique scan and a range scan. Received on Wed Feb 25 2004 - 07:13:05 CST