Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Apr 26 '13 at 7:38

This question came from our site for professional and enthusiast programmers.

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. –  a_horse_with_no_name Apr 26 '13 at 7:31
add comment

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

share|improve this answer
    
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 '13 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. –  a_horse_with_no_name Apr 26 '13 at 10:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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