Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: consistent gets

Re: consistent gets

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 22 Mar 2005 11:43:01 +0000 (UTC)
Message-ID: <d1p0c5$fn7$1@sparta.btinternet.com>

"freCho" <svenson1_at_libero.it> wrote in message news:pan.2005.03.22.10.16.34.684127_at_libero.it...
> Hi all,
> I have a question regarding consistent gets.
> I've deleted about 10.000.000 records from a
> table using a where condition like "where
> cdate < somedate" against a date column and
> committed the work. There is an index on
> this table. After the delete the query to retrive
> the max(date) is very fast, but the same query for
> the min(date) is very very slow.
> I have used set autotrace traceonly, discovering that
> the cause was consistent gets:
>
> SQL> select max(cdate) from areasum;
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=10)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'ARSUMIX4' (NON-UNIQUE) (
> Cost=4 Card=50616215 Bytes=506162150)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 4 consistent gets
> 0 physical reads
> 0 redo size
> 389 bytes sent via SQL*Net to client
> 499 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> SQL> select min(cdate) from areasum;
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=10)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'ARSUMIX4' (NON-UNIQUE) (
> Cost=4 Card=50616215 Bytes=506162150)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 147838 consistent gets
> 133186 physical reads
> 0 redo size
> 389 bytes sent via SQL*Net to client
> 499 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
>
> After that, I,ve tried rebuilding the index and now the second query
> is fast as the first one:
>
> AFTER REBUILD INDEX
>
> SQL> select min(cdate) from areasum;
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=10)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'ARSUMIX4' (NON-UNIQUE) (
> Cost=4 Card=50616215 Bytes=506162150)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 4 consistent gets
> 3 physical reads
> 0 redo size
> 389 bytes sent via SQL*Net to client
> 499 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
>
> The question is: why so much consisten gets before
> rebuild the index??
>
> The server is an 9.2.0.1 Enterprise Edition running
> on Solaris 8 machine.
>
> thank's for the answer.
>
> sorry for my poor english.

Immediately after you deleted the huge amount of data, there were lots of empty leaf blocks at the left-hand edge of the index.

Your query for min() would have to start at the left hand edge, and walk through all those empty blocks (building read-consistent versions if necessary) to find the first row that still existed. This is why you get so many CR gets.

Your query for max() is the 8.1 optimisation for maximum within an index - Oracle knows that the highest value is at the right hand edge, so it doesn't need to walk the index from the left hand edge, so it takes only blevel + 1 CR gets to find the max().

The empty blocks will eventually be re-used or taken out of the index structure, but for very large deletes, the time-delay for a proper cleanout can be significant. It can be more efficient to disable the index (drop it, or mark it unusable) before you do such a massive delete and rebuild it afterwards.

--

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005
Received on Tue Mar 22 2005 - 05:43:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US