explain plan, can you explain this?

  • From: Dan Norris <dannorris@xxxxxxxxxxxxx>
  • To: Oracle L <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 9 Jan 2008 15:28:40 -0800 (PST)

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

Other related posts:

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