consistent gets

From: freCho <svenson1_at_libero.it>
Date: Tue, 22 Mar 2005 11:16:34 +0100
Message-ID: <pan.2005.03.22.10.16.34.684127@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. Received on Tue Mar 22 2005 - 04:16:34 CST