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


Other related posts: