Re: Index Rebuilds

Seems after the coalesce, the index_ffs is stills canning all teh blocks.
so you might want to do a full rebuild to make the index shrink, if you have
to stick with the index_ffs. Rebuild index is less cost than coalese, I
think.

Also serious challange your developer whether they really want that exact
number. I have seen similar case, a simple tradeoff will save you a lot of
cost in db.

On 7/29/06, Stalin <stalinsk@xxxxxxxxx> wrote: 

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





--
Regards
Zhu Chao
www.cnoug.org

Other related posts: