Re: dbms_stats automatic collection in 10g

On Fri, 12 May 2006 04:57:28 GMT, Mladen Gogala <gogala_at_sbcglobal.net> wrote:

>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'

This is what I was looking for.. in 9.2.0.6 I do have jobs setup to enable monitoring, gather stale, etc.., was trying to understand how this works on 10g.. so default is some # of hours? b/c nothing shows nothing in column interval nor has a value for 'next_run_date'.. seems like it might run at 10pm daily by default? .......
We run Oracle 9.2.0.6 on RH4 AMD
we are currently looking for a DBA
remove NSPAM to email Received on Fri May 12 2006 - 16:54:42 CDT