Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    32

    Unanswered: ANALYZE performance issue

    Wonder if anyone here can offer any explanation for the following problem I'm experiencing.....

    I'm running 8.1.6 on AIX 4.3 and have a cron job each night that runs COMPUTE STATISTICS on every table in a schema. We've recently added a new schema and I've added those new tables, and some indexes to this cron job, as follows....
    ANALYZE TABLE [schema].[tb_name] COMPUTE STATISTICS FOR TABLE;
    ANALYZE INDEX [schema].[idx_name] COMPUTE STATISTICS FOR ALL INDEXED COLUMNS SIZE 75;

    Every morning performance on this specific table is poor. If I run the following....
    ANALYZE TABLE [schema].[tb_name] DELETE STATISTICS;
    ANALYZE INDEX [schema].[idx_name] DELETE STATISTICS;
    .... followed by the COMPUTE statements, performance returns to normal.

    Why should I have to delete stats to ensure there's no degredation in performance??

    Thanks in advance.

  2. #2
    Join Date
    Jan 2004
    Posts
    32

    No luck with this one??

    No one have any ideas about this then?

  3. #3
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106

    Red face

    What does statspack show? What are the stats before and after, what is the deterministic that shows there is performance degradation? I think nobody has replied because the experts need a heck of a lot more to determine a performance problem than the database version of 8.1.6. (Which is downlevel and de-supported). They would need to see the sql statement, explain plans, optimizer/initialization parameters, etc.

    Try http://asktom.oracle.com, copy and paste " performance poor after analyzing table " (without the quotes) into the search field and read what tom says about things. Maybe this could give you a better idea for what may be happening in your particular database.
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  4. #4
    Join Date
    Jan 2004
    Posts
    32
    Thanks dbtoo - I've found a great article on asktom by modifying my search criteria slightly.

    Also, info that we discovered yesterday that may have originally helped is that a column in one of the indexes contains very few distinct values, so using the SIZE parameter made better use of histograms when running queries.

    I can't supply the SQL used since we're still awaiting that from our complaining S/W Vendors.... Same old, same old...


Posting Permissions

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