I want to know what should be the ideal Frequency for Analyzing tables in a schema. Database size 1GB.

Currently the frequency is set to 7 days, it takes around 40 mins to complete. Data volume is not huge.

Please let me know how can I optimize it?

What all options and to what values they should be set for DBMS_STATS.GATHER_TABLE_STATS.

  • 1
    Usually you don't need to do anything. Oracle will automatically gather table statistics for you unless you have manually turned that off. How often you do gather statistics doesn't depend on the total size of the data, but how often that data changes substantially.
    – user1822
    Apr 26, 2013 at 7:31

1 Answer 1


Here are some issues to consider:

  1. Are you using an appropriate level of estimation in the statistics gathering? People often manually set the estimation to a value without considering whether they can get a similar level of accuracy with a lower amount. A good test is to gather statistics at different estimation percents, starting very low and increasing: eg. 0.1%, 0.5%, 1%, 3%, 6%, 10%. Use the stattab argument to direct the stats results to different tables, and compare them.

  2. Do you need histograms? If you are using an OLTP application then quite possibly not.

  3. How frequently does the data change? Table monitoring can be used to help judge when statistics should be gathered -- if a table changes very rapidly and frequently (eg a temporary table used in data loading) then deleting the stats and locking them can be helpful, as dynamic sampling will do the job for you and you won't waste time in a scheduled job analysing data that you have no use for.

Or just use the automatic option, monitoring it to see if there are situations worth intervening in manually.

Given that these are DAC metadata tables, I'd recommend that you set monitoring on for them and use the automatic statistics gathering job to gather statistics when they're stale: http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#i41282

  • The analyze table step is for DAC (Datawarehouse admin console) metadata tables. In this the analyze frequency is set to 7. There are 116 tables in the schema out of which 4 are analyzed daily and remaining on weekly basis. Below are the scripts that are fired: DBMS_STATS.GATHER_TABLE_STATS(ownname => '@TABLEOWNER', tabname => '%1', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE 1',cascade => false, degree => 8); I want to know what would be the optimum frequency for analyzing given the 1 GB DB size and not much updation on daily basis is there.
    – Sid
    Apr 26, 2013 at 9:56
  • 2
    @Sid: estimate_percent => 100 seems way too much. Did you verify that this actually improves your execution plans? Why don't you use "ESTIMATE"? And if you don't update the table very often, there is no point in doing this every day.
    – user1822
    Apr 26, 2013 at 10:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.