Ideas how to gather optimizer stats
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Ideas how to gather optimizer stats

  1. #1
    Join Date
    Mar 2013
    Posts
    1

    Ideas how to gather optimizer stats

    Hi All,

    I'm having an issue with stale optimizer stats for some SQLs that are run in a batch process.
    The problem is that the process runs many times during the day - sometimes 20 to 30 times.
    And each time, the tables are updated, i.e. rows are inserted or deleted, etc.

    So eventually the optimizer stats for those tables become stale and the performance
    of the SQLs start to slow down (a lot).

    Any ideas how best to gather the optimizer stats on the tables so they
    don't become stale when the batch process runs each time?
    The problem is that I also can't add/modify the code in the batch process because it is delivered by the vendor as is.

    Any suggestions are greatly appreciated.

    Thanks

  2. #2
    Join Date
    Dec 2002
    Posts
    60
    You can schedule frequent gather stats on that particular table.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Cool

    What version Oracle?

    If you have 10g or higher you can set the STATISTICS_LEVEL to the default setting of TYPICAL which ensures collection of all major statistics required for database self-management functionality and provides best overall performance.

    Enable the automated maintenance task (Optimizer Statistics Gathering) to generate optimizer statistics regularly.

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You can also schedule dbms_stats.gather_schema_stats to run on the particular schema just after the batch job runs. Set options to gather_auto, this option will gather stats on statsless tables as well as in stale ones (tables that had 10%+ changes since last stats collection).
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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