Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: equivalent plans but higher consistent gets
Check the two executions for statistics
"buffer is pinned count"
you will probably find the missing consistent
gets have been transferred to "pinned buffer"
acquisitions.
The difference in plan is an Oracle 9 optimisation strategy is (I assume) intended to reduced cache buffers chains latching and redundant revisits to buffered blocks. (Sometimes, as in your case, the target is not achieved).
In the case with the full scan, Oracle estimates that the scan will return one row, and considers a new nested loop strategy - viz: collect all the rowids that will be required (for EVERY row in the first table) from the second table's index and sort them before visiting the second table at all. (I this this is known as table prefetching).
In the case with the unique index, Oracle knows that there will be just one row returned, from the first table, so the prefetch algorithm would be a waste of time, so it uses the traditional nested loop. The traditional nested loop has a lot of pinning of buffers built in (your comment about not 'getting' the block for the extra rows is accurate).
Unfortunately, the new "table pre-fetch" code doesn't seem to work the way the plan says it should. This probably explains the difference.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: March 2004 Hotsos Symposium - The Burden of Proof DYnamic Sampling - an investigation March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February ____UK___June The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html "Richard Kuhler" <noone_at_nowhere.com> wrote in message news:2h5%b.7367$qc4.4850_at_twister.socal.rr.com...Received on Sat Feb 28 2004 - 04:03:24 CST
> >>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?
> >>