Can anyone help with an example query to find Stale indexes ? I am working on tuning an Oracle 8.1.7 PeopleSoft Database.
Printable View
Can anyone help with an example query to find Stale indexes ? I am working on tuning an Oracle 8.1.7 PeopleSoft Database.
may I ask what´s stale index?
Folks , Nevermind , I have since figured this one out, a stale index is an index which has several dead leaf nodes. This happens when there is a lot of updating, deleting from a table associated with the index, when a row is deleted Oracle leaves the index node there, then later when a query is using this index , performance degrades because it has to traverse through useless nodes to bring back data, once you find these it is recommended to rebuild these indexes. I found a script which selects from the INDEX_STATS view to identify these indexes, also you need to previously run "analyze indexvalidate structure;" <-- this populates the view.
Thanks.
I wouldn't bother with this issue unless you can actually identify the performance degradation -- index rebuilds are much overrated as a performance aid, and can harm performance.
According to Oracle a table is called STALE from time t1 till time t2 if the total number of inserts, deletes and updates for the table exceeds 10% of the number of rows in the table at momemnt t1.Quote:
Originally posted by pando
may I ask what´s stale index?
So if a table has 100 rows, it will become STALE after the 11th DML operation.
well I understand what's stale table but never heard of stale index but guess he meant unbalanced index