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_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l