DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Oracle 9i - Gather statistics

  1. #1
    Join Date
    Apr 2002

    Oracle 9i - Gather statistics

    2 node cluster Fail Safe Oracle
    We have a strange situation:
    From OEM, you can Analyze the DB in order to collect statistics and memorize these statistics in the DB.
    Before this operation our querys run fast, but after this operation the same querys are really slow.
    I then delete the statistics from the DB and everything return normal, the querys are fast again.
    Can anyone tell me what could be the problem?
    Thank you very much.

  2. #2
    Join Date
    Dec 2002
    Bangalore ( India )
    problem could be due many reasons.. but i think you need to RTM ..

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Aug 2002
    Colorado Springs
    What's happened here, it seems, is that by analyzing the tables you've invoked the cost-based optimizer instead of the rule-based optimizer.

    One common issue with the cost based optimizer, or rather with the people managing the db, is that they do not also collect system statistics (using DBMS_STATS.GATHER_SYSTEM_STATS), or alternatively they do not set the initialization parameter optimizer_index_cost_adj to a resonable value, such as "20" (the default of 100 is way too high).

    Gathering system stats is much preferred. I gives the optimizer real information on the relative costs of scattered and sequential reads of data, and ought to restore your good performance under the CBO.

    See ...
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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