Index Rebuilds
- From: Stalin <stalinsk@xxxxxxxxx>
- To: "Oracle Discussion List" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 28 Jul 2006 16:08:01 -0700
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
