Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: dbms_stats automatic collection in 10g

Re: dbms_stats automatic collection in 10g

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Fri, 12 May 2006 04:57:28 GMT
Message-Id: <pan.2006.05.12.04.57.28.354879@sbcglobal.net>


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.com
Received on Thu May 11 2006 - 23:57:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US