Ryan Gaffuri wrote:
> 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.
You must have just skimmed the message. The table T1 only has one row
in one block. Whether you use an index or a full table scan on it isn't
going to affect the consistent gets to any appreciable degree. The
range scan on T2 will be for the same single value in both cases.
--
Richard
Received on Wed Feb 25 2004 - 11:51:58 CST
