Re: explain plan, can you explain this?

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@xxxxxxxxxxxxx>:
>
> 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

Other related posts: