Re: explain plan, can you explain this?

The query is equivalent to

SELECT ma_id
FROM some_lookup
WHERE program_id = :1 AND index_start <= :2 and :2 <= index_end

Since UK_SOME_LOOKUP is on (program_id, index_start), index keys are ordered on program_id, index_start, hence Oracle will access the index using "WHERE program_id = :1 AND index_start <= :2",
that is, will scan the index section that starts with the first entry with program_id = :1
until program_id is not longer equal to :1 or index_start > :2.

Say it gets N keys from this scan - it will make N consistent gets on the table to retrieve index_end and ma_id, and filter out the ones where is not ":2 <= index_end", possibly re-visiting the same block multiple times. On average, your N is slightly less than 273.

Try putting an index on
(program_id, index_start, index_end desc, ma_id)

You might also experiment with
(program_id, index_end desc, index_start, ma_id)

Both should give you a reduction of an order of magnitude in the consistent gets / exec (if I haven't botched something, but it's really too late into the night here in Italy to set up an experiment ;)

HTH !
Alberto

On Jan 10, 2008 12:28 AM, Dan Norris <dannorris_at_dannorris.com> wrote:
>
> 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
>

-- 
Alberto Dell'Era
"the more you know, the faster you go"
--
http://www.freelists.org/webpage/oracle-l