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 -> Re: equivalent plans but higher consistent gets

Re: equivalent plans but higher consistent gets

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 28 Feb 2004 10:03:24 +0000 (UTC)
Message-ID: <c1pp1c$jb$1@hercules.btinternet.com>

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...

> >>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?
> >>
Received on Sat Feb 28 2004 - 04:03:24 CST

Original text of this message

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