Indetifying Stale Indexes in Oracle
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Indetifying Stale Indexes in Oracle

  1. #1
    Join Date
    Dec 2002
    Location
    Granite City , Illinois
    Posts
    4

    Indetifying Stale Indexes in Oracle

    Can anyone help with an example query to find Stale indexes ? I am working on tuning an Oracle 8.1.7 PeopleSoft Database.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    may I ask whatīs stale index?

  3. #3
    Join Date
    Dec 2002
    Location
    Granite City , Illinois
    Posts
    4

    Stale Indexes in Oracle

    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 index validate structure;" <-- this populates the view.

    Thanks.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by pando
    may I ask whatīs stale index?
    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.

    So if a table has 100 rows, it will become STALE after the 11th DML operation.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well I understand what's stale table but never heard of stale index but guess he meant unbalanced index

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width