Index Rebuilds

Hi All,

One of system started to choke today and apparantly it turned to be the sql that was executed more often had sub-optimal plan. After digging around i noticed that the index was fragmented as the index was heavily inserted/deleted and the query was using tablescan to index fast full scan. The index was a contcatenated index on type and last modified date which gets updated on last_modified_date often.

LBLKS: 66653
BlVL: 3
CF: 679426
NROWS: 829734
AVG_DATA_BLK/KEY: 2 Coalescing the index got the lblks to 11k and the desired plan however, the performance is still under water. 10046 trace on the sql is

select count(*)
from
objects where type = 'live'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     10.39      31.07      65484      69693          0           1


------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4     10.40      31.08      65484      69693          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------

      1 SORT AGGREGATE
 832154 INDEX FAST FULL SCAN IX_TYPE_LASTMODIFIED (object id 25015)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                       536        0.00          0.23
  db file scattered read                       5518        0.09         25.62
  SQL*Net message from client                     2       22.65         22.65

Any suggestions as to what i should be looking further. I really don't want to rebuild the index :)

Thanks,
Stalin
--

http://www.freelists.org/webpage/oracle-l Received on Fri Jul 28 2006 - 18:08:01 CDT