Ideal frequency to gather table statistics in Oracle for database of 1GB in size 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.
Here are some issues to consider:


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.
Do you need histograms? If you are using an OLTP application then quite possibly not.
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