Re: explain plan, can you explain this?

  • From: "Luis Fernando Cerri" <lfcerri@xxxxxxxxx>
  • To: dannorris@xxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 9 Jan 2008 21:25:11 -0300

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
>
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2008