How frequent do we Analyze Table Statistic We have a Oracle 11g database running with a table that insert entries with around 100records/min. We met some spiking issue mainly due to table is performing full table scan. We have indexed the required key, however, sometime it does not take into effect.

What we do is to do a 'analyze table statistic' and it resolve the issue.

May i ask, why do oracle require a table to be analyzed? how often do we analyze a table? Currently we have set a job scheduler for every 3 hours, however, it seems like sometime within 30mins it require another statistic to be analyzed
Do not use analyze table statistic, this is only supported for backward compatibility (this was already the case in Oracle 10g), see ANALYZE 


  Do not use the COMPUTE and ESTIMATE clauses of ANALYZE to collect optimizer statistics. These clauses are supported for backward compatibility. Instead, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. The optimizer, which depends upon statistics, will eventually use only statistics that have been collected by DBMS_STATS. See PL/SQL Packages and Types Reference for more information on the DBMS_STATS package. 


Use DBMS_STATS.GATHER_TABLE_STATS() instead, see DBMS_STATS. Be default Oracle collects statistics automatically during night time. Usually this is sufficient. Inserting data does not necessarily mean that you also need new statistics. Typically new data are similar to existing data, Min/Max value or distribution of values does not vary much.