Re: explain plan, can you explain this?

Dan,

Is it completely read-only? If not don't forget to add consistent block reconstruction into the equation. In any case I'd go with assessing 10046 trace to evaluate two things - consistent reads for each operator in the plan and consistent reads for all its children. Vlad Sadilovskiy
Oracle Database Tools
Web site: http://www.fourthelephant.com
Blog: http://vsadilovskiy.wordpress.com

On Jan 9, 2008 7:25 PM, Luis Fernando Cerri <lfcerri_at_gmail.com> wrote:

> Hello, Dan.
>
> What is the parse to execute ratio for this statement? Maybe some
> recursive statements are in play demanding the extra buffer gets.
>
> HTH
> Cerri
>
>
>
>
> 2008/1/9, Dan Norris <dannorris_at_dannorris.com>:
> >
> > I'm just starting to dig in to tuning some SQL in a new (to me)
> environment.
> > One of the statements of interest is this one:
> >
> > SELECT ma_id
> > FROM some_lookup
> > WHERE program_id = :1 AND :2 BETWEEN index_start and index_end
> >
> > The very interesting part is that in a one-hour statspack, this
> statement
> > generates 30 mil buffer gets, executed 111,388 times (about 273 buffer
> gets
> > per exec). When combined with the facts below, it becomes a puzzler:
> > 1. Table has 11 columns (4 date and 7 number data types) and consumes
> 128
> > blocks total.
> > 2. Has two unique indexes, one a primary key (single number column--none
> of
> > the columns in the query above) that consumes 16 blocks and unique index
> > (UK_SOME_LOOKUP) on (program_id, index_start) that consumes 24 blocks.
> > 3. The explain plan only references the table and a range scan of the
> unique
> > index (24 blocks in the whole index).
> > 4. Explain plan has a cost of 3--see below.
> > 5. This is a lookup table that doesn't change often, but is referenced
> > frequently (111k references per hour).
> >
> > So, if all the table data can be scanned in 128 blocks and even if you
> do
> > full scans on both indexes, there's only 168 blocks, then how can this
> thing
> > do 273 buffer gets per execution on average? If there are 111,388
> executions
> > and I scanned the table and both indexes for each query (which would be
> > silly), I'd only do 18 mil buffer gets. That doesn't add up!
> >
> > Lots of numbers there, but hopefully there's enough information for
> someone
> > to help me understand what might be going on.
> >
> > I'm all ears.
> >
> > For reference:
> >
> --------------------------------------------------------------------------------
> > | Operation | PHV/Object Name | Rows | Bytes|
> > Cost |
> >
> --------------------------------------------------------------------------------
> > |SELECT STATEMENT |----- 2983477548 ----| | |
> > 3 |
> > |TABLE ACCESS BY INDEX ROWID |SOME_LOOKUP | 1 | 24 | 3
> |
> > | INDEX RANGE SCAN |UK_SOME_LOOKUP | 1 | | 2
> |
> >
> --------------------------------------------------------------------------------
> >
> > Thanks,
> > Dan
> >
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l