Skip to Main Content
  • Questions

  • Disabling of Automatic Statistics Gathering Job

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rihaz.

Asked: January 11, 2016 - 11:03 pm UTC

Last updated: January 13, 2016 - 3:42 am UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

We have data load process that runs every night.

Would like to disable the auto job and create the job with different time interval .
What are the steps ? Please advise.

and Connor said...

You can run:

DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto optimizer stats collection')

which will turn it off.

Otherwise, you will want to move the window in which the task can run in. That is controlled via the scheduler, eg

DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'SYS.MONDAY_WINDOW',attribute=>'repeat_interval',value=>'freq=daily;byday=MON;byhour=03;byminute=0; bysecond=0');

The optimizer runs each night, so you might need to change the start time and / or duration for each day, ie, xxx_WINDOW.

Hope this helps.

Rating

  (1 rating)

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

Comments

Rihaz Karady, January 12, 2016 - 9:25 pm UTC

Thanks very much.

I was planning to do the following .
Would this be good to run the stats job on certain days ?

thanks again.

BEGIN
sys.dbms_scheduler.set_attribute( name => '"SYS"."BSLN_MAINTAIN_STATS_SCHED"', attribute => 'repeat_interval', value => 'FREQ=WEEKLY;BYDAY=TUE,WED,FRI,SAT,SUN;BYHOUR=15;BYMINUTE=0');
END;
Connor McDonald
January 13, 2016 - 3:42 am UTC

BSLN_MAINTAIN_STATS_SCHED is unrelated to optimizer statistics.

You need to change window's as I specified earlier, or you could disable the job for certain days, eg

DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'auto optimizer stats collection',
window_name => 'FRIDAY_WINDOW');

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.