Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: dbms_stats automatic collection in 10g
On Thu, 11 May 2006 16:19:20 -0400, NetComrade wrote:
> How does it work?
>
> I created a table.. I inserted about 80K rows in it... waited a bit..
> inserted another 80K nothing in user_tab_modifications or
> dba_tab_modifications.
The title of your message says 10g, yet below you say 9.2.0.6. The
difference is that in 9.2.0.6 you must turn on monitoring for tables.
SMON collects this information every 15 minutes or so. In 10G there is
a job which runs automatically and can be seen by using the following
query:
1 select job_name,enabled,repeat_interval
2 from dba_scheduler_jobs
3* where job_name='GATHER_STATS_JOB'
SQL> /
JOB_NAME ENABL REPEAT_INTERVAL ------------------------------ ----- -------------------- GATHER_STATS_JOB TRUE
On 9.2.0.6 you have to schedule DBMS_STATS by using DBMS_JOB or cron, as there is no DBMS_SCHEDULER. When you want DBMS_STATS to automatically determine for what objects to collect statistics and what kind of statistics, you will have to invoke it with options=>'GATHER AUTO' or options =>'GATHER STALE'
-- http://www.mgogala.comReceived on Thu May 11 2006 - 23:57:28 CDT