RE: explain plan, can you explain this?

From: "Ukja.dion" <ukja.dion@xxxxxxxxx>
To: <alberto.dellera@xxxxxxxxx>, <dannorris@xxxxxxxxxxxxx>
Date: Thu, 10 Jan 2008 12:59:38 +0900
> 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.

This is generally true, but we sometimes need to consider the effect of
"buffer pinning".
In case we hit same table and/or index block on the process of scan at same
execution unit, the buffer is pinned and Oracle reuse that buffer. 
Hence "logical reads" does not increase, instead "buffer pinned count"
increases.
This effect is called "buffer pinning" and we should have good clustering
factor to enjoy this.



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Alberto Dell'Era
Sent: Thursday, January 10, 2008 9:42 AM
To: dannorris@xxxxxxxxxxxxx
Cc: Oracle L
Subject: 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@xxxxxxxxxxxxx> 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"
--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l
References:
- explain plan, can you explain this?
  - From: Dan Norris
- Re: explain plan, can you explain this?
  - From: Alberto Dell'Era
RE: explain plan, can you explain this?

Other related posts:
