Thanks for the question, George.

Asked: February 04, 2008 - 2:22 pm UTC

Last updated: December 29, 2008 - 3:57 pm UTC

Version: 10.2.0.3

Viewed 10K+ times! This question is

You Asked

Hi Tom,

How to change the auto stats collection job (GATHER_STATS_JOB) in 10GR2? It is created and scheduled by default to run at 10PM, we like to schedule it to run at different time, say 8PM on Friday and Saturday.

I tried this

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => 'GATHER_STATS_JOB',
attribute => 'repeat_interval',
value => 'freq=daily;byday=FRI,SAT;byhour=20;byminute=0; bysecond=0');
END;
/

and get error:

ORA-27488: unable to set REPEAT_INTERVAL because SCHEDULE_NAME, EVENT_SPEC was/were already set
ORA-06512: at "SYS.DBMS_ISCHED", line 2834
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1847
ORA-06512: at line 2

What else I need to do to chenge it?

Thanks in advance.

and Tom said...

You want to

a) disable it
b) unschedule it (you are changing the schedule)
c) custom schedule it
d) enable it

sys%ORA10GR2> exec dbms_scheduler.disable( 'GATHER_STATS_JOB' );

PL/SQL procedure successfully completed.

sys%ORA10GR2> exec dbms_scheduler.set_attribute_null('gather_stats_job','schedule_name');

PL/SQL procedure successfully completed.

sys%ORA10GR2> exec DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'GATHER_STATS_JOB', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI,SAT;byhour=20;byminute=0; bysecond=0');

PL/SQL procedure successfully completed.

sys%ORA10GR2> exec dbms_scheduler.enable( 'GATHER_STATS_JOB' );

PL/SQL procedure successfully completed.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

George fu, February 05, 2008 - 4:36 pm UTC

excellent

Gather stats in 10g

Megala, February 05, 2008 - 5:42 pm UTC

Tom,

We usually analyze the schema after big data loading, in 9i using DBMS_STATS. Do we still need to do this in 10g (Note: GATHER_STATS_JOB enabled in the database).

Does it harm anything if we still run analyze after data loading (truncate and reload).

Thanks

February 05, 2008 - 9:32 pm UTC

the default job will only gather stale, so if you've done it - it'll skip it. It won't "hurt" to have done it.

Thanks

A reader, February 05, 2008 - 9:35 pm UTC

10g Automatic Statistics Gathering

A reader, December 29, 2008 - 2:04 pm UTC

In 10g's automatic stats gathering procedure, what are the parameter values that Oracle uses to gather stats?

estimate_percent
method_opt
cascade

Is there any way to change the default parameter values?

Does it gather stats for all schemas in the database including the sys schema?

December 29, 2008 - 3:57 pm UTC

see them with get_param

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1048775

ops$tkyte%ORA10GR2> select dbms_stats.get_param( 'estimate_percent' ) from dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
-------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

reset or set them with these:

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1047505
http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1048566

or, just do it explicitly (so as to NOT rely on defaults...)

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Gather stats in 10g

Thanks

10g Automatic Statistics Gathering

More to Explore

PL/SQL demos

PL/SQL docs

How to change repeat_interval for GATHER_STATS_JOB in 10GR2

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Gather stats in 10g

Thanks

10g Automatic Statistics Gathering

More to Explore

PL/SQL demos

PL/SQL docs