Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    184

    Unanswered: Last time indexes were rebuilt?

    Is there a query that will tell you the last time dbms_stats.gather_schema_stats was run or that a dbs indexes were rebuilt?

    I have a customer that I do not believe is maintaining their indexes properly. Not being a dba, I'm not sure what to look for other than performance downturns.

    Thank you.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Would something like this be useful?
    Code:
    SQL> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;
    
    NOW
    -------------------
    09.10.2006 23:01:21
    
    SQL> analyze index pk_test estimate statistics;
    
    Index analyzed.
    
    SQL> select to_char(last_analyzed, 'dd.mm.yyyy hh24:mi:ss') when
      2  from user_indexes where index_name = 'PK_TEST';
    
    WHEN
    -------------------
    09.10.2006 23:01:26
    
    SQL>

  3. #3
    Join Date
    May 2004
    Posts
    184
    Littlefoot,

    Awesome. Thank you! That looks like it will work very well for what I need.

    As a followup, is there an industry standard or rule of thumb as to how often dba's should rebuild their indexes? Understandably, this would vary based upon the type of application system the DB is supporting.

    Thank you again.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Well its debatable if you need to rebuild indexes regularly. If you goto Jonathan Lewis's or Tom Kyte's website you'll see they recomend NOT rebuilding indexes except when you can prove that there are tangible benefits which outweigh the cost of the rebuild (and that the index wont rapidly return to the state prior to the rebuild).

    Having said that due to the way our main application works we find an index rebuild reduces index size by 50%. This is due to a peculiarity with the way the app works which means when customers enter data and then subsequently updates the index multiple times the PK index fragments. Since the table is partitioned by month, once that month is completed there are no longer any updates thus the benifits of the rebuild stay.

    Alan

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The query given is when the index was last analyzed, which does NOT rebuild the index. It simply scans it to update statistics. If you want the date that it was recreated, then use the following. For my example, I am using an index named STARMATX1.

    1 select to_char(created,'MM/DD/YYYY HH24:MI.SS')
    2 FROM USER_OBJECTS
    3 WHERE OBJECT_NAME='STARMATX1';

    TO_CHAR(CREATED,'MM
    -------------------
    09/12/2004 02:11.58
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    May 2004
    Posts
    184
    Alan,

    Thank you. This was eye opening. I was under the impression that Oracle's indexes had to be manually analalyzed periodically because Oracle didn't do it itself and thus it was the dba's job.

    I'm still very new to Oracle, but I have had several scripts that crawled until I manually reindexed the my indexes. Maybe, reindex is the wrong word and I should use analyze instead. Is this typical that indexes have to be analyzed manually (or via a scheduled task)?

    Thank you. I'll check out the two sites you mentioned. I had heard of Jonathan Lewis before.

    Thank you again.

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You should analyze especially when your data volumes or distribution changes. Be careful with dbms_stats though as the optimizer is not perfect and can get things wrong, and dont forget that dbms_stats has a lot of options, its important to use the parameters correctly.

    However you should not rebuild indexes (using alter index rebuild) unless you can get a real benefit as mentioned earlier.

    Alan

  8. #8
    Join Date
    May 2004
    Posts
    184
    Alan,

    Thank you.

    Robert

Posting Permissions

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