explain plan, can you explain this?

From: Dan Norris <dannorris_at_dannorris.com>
Date: Wed, 9 Jan 2008 15:28:40 -0800 (PST)
Message-ID: <617957.71921.qm@web35403.mail.mud.yahoo.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 |


Received on Wed Jan 09 2008 - 17:28:40 CST

Original text of this message