Fwd: Stats

From: manikandan <pvmanikandan_at_gmail.com>
Date: Tue, 24 Mar 2020 19:20:53 -0400
Message-ID: <CAB6Jwggc+7R5+rOcU5KKvAMrJdtTLu=UXf4DqiVhkde-GO87sw_at_mail.gmail.com>



Hi,

Env details :- RHEL 7 , 12.2.0.1 Jul 2018 RU, 2 Node RAC

We had observed a behavior post implementation that multiple calls to stats during the maintenance window which was due to bug (*Bug 26724511 <https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=2387466.1&id=26724511.8> :
AUTO OPTIMIZER STATS RUN MULTIPLE JOBS DURING MAINTENANCE WINDOWS*). Hence we decided to disable auto gather stats and space advisor jobs and set up manual scheduler job for gather stats on daily basis. Please find code below

--Disable auto optimizer stats collection and auto space advisor

begin

DBMS_AUTO_TASK_ADMIN.disable(client_name=>'auto space advisor',operation=>NULL,window_name=>NULL);

end;

/

begin

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

end;

/

  • create weekdays schedule

Begin

  DBMS_SCHEDULER.CREATE_SCHEDULE (       schedule_name => 'MANUALSTATS_WEEKDAYS_SCHEDULE',

      start_date => NULL, -- If start_date is null, then the date that the job or window is enabled is used.

      repeat_interval => 'freq=daily;byhour=22;byminute=0;bysecond=0',

      end_date => NULL,

      comments => 'starts every day at 10:00 pm');

End;

/

  • create weekdays window

Begin

   DBMS_SCHEDULER.CREATE_WINDOW (        window_name => 'MANUALSTATS_WEEKDAYS_WINDOW',

       resource_plan => 'DEFAULT_MAINTENANCE_PLAN',

       schedule_name => 'MANUALSTATS_WEEKDAYS_SCHEDULE',

       duration => '0 06:00:00',

       window_priority => 'HIGH',

       comments => 'window opens every day at 10:00 pm and ends at 04:00 am');

End;

/

  • create program that runs the stored procedure

Begin

   DBMS_SCHEDULER.CREATE_PROGRAM (     program_name => 'STATS_MAUNAL',

    program_type => 'PLSQL_BLOCK',

    program_action => 'BEGIN
DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC; END;',     number_of_arguments => 0,

    enabled                  => TRUE,

    comments                 => 'This is the program unit that calls the
concerned stored procedure');

End;

/

  • create job

Begin

   DBMS_SCHEDULER.CREATE_JOB (

    job_name                => 'PMP_MANUAL_STATS',

    program_name            => 'STATS_MAUNAL',

   schedule_name           => 'MANUALSTATS_WEEKDAYS_SCHEDULE',

    enabled                 => TRUE,

    auto_drop               => FALSE,

    Comments                => 'Job that gathers DB stats everyday at 10:00
pm to 04:00 am',

    job_style => 'REGULAR');

End;

/

begin

DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'PMP_MANUAL_STATS'

,attribute => 'STOP_ON_WINDOW_CLOSE'

,value => TRUE);

END; /

Although we have set stop_on_window_close to true, the job is running beyond the maintenance window.

*OWNER*

*WINDOW_NAME*

*RESOURCE_PLAN*

*SCHEDULE_OWNER*

*SCHEDULE_NAME*

*SCHEDULE_TYPE*

*START_DATE*

*REPEAT_INTERVAL*

*END_DATE*

*DURATION*

*WINDOW_PRIORITY*

*NEXT_START_DATE*

*LAST_START_DATE*

*ENABLED*

*ACTIVE*

*MANUAL_OPEN_TIME*

*MANUAL_DURATION*

*COMMENTS*
SYS MONDAY_WINDOW DEFAULT_MAINTENANCE_PLAN CALENDAR freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0

+00 04:00:00.000000

LOW 3/30/2020 10:00:00.000000 PM -04:00

3/23/2020 11:59:58.270880 PM -04:00

TRUE FALSE Monday window for maintenance tasks

SYS TUESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN CALENDAR freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0

+00 04:00:00.000000

LOW 3/24/2020 10:00:00.000000 PM -04:00

3/17/2020 11:59:58.196259 PM -04:00

TRUE FALSE Tuesday window for maintenance tasks

SYS WEDNESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN CALENDAR freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0

+00 04:00:00.000000

LOW 3/25/2020 10:00:00.000000 PM -04:00

3/18/2020 11:59:58.390856 PM -04:00

TRUE FALSE Wednesday window for maintenance tasks

SYS THURSDAY_WINDOW DEFAULT_MAINTENANCE_PLAN CALENDAR freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0

+00 04:00:00.000000

LOW 3/26/2020 10:00:00.000000 PM -04:00

3/19/2020 11:59:58.574353 PM -04:00

TRUE FALSE Thursday window for maintenance tasks

SYS FRIDAY_WINDOW DEFAULT_MAINTENANCE_PLAN CALENDAR freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0

+00 04:00:00.000000

LOW 3/27/2020 10:00:00.000000 PM -04:00

3/20/2020 11:59:58.093932 PM -04:00

TRUE FALSE Friday window for maintenance tasks

SYS SATURDAY_WINDOW DEFAULT_MAINTENANCE_PLAN CALENDAR freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0

+00 20:00:00.000000

LOW 3/28/2020 6:00:00.000000 AM -04:00

3/21/2020 11:59:58.237351 PM -04:00

TRUE FALSE Saturday window for maintenance tasks

SYS SUNDAY_WINDOW DEFAULT_MAINTENANCE_PLAN CALENDAR freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0

+00 20:00:00.000000

LOW 3/29/2020 6:00:00.000000 AM -04:00

3/22/2020 11:59:58.729725 PM -04:00

TRUE FALSE Sunday window for maintenance tasks

SYS WEEKNIGHT_WINDOW CALENDAR freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0

+00 08:00:00.000000

LOW 10/16/2018 10:00:00.000000 PM -04:00

FALSE FALSE Weeknight window - for compatibility only

SYS WEEKEND_WINDOW CALENDAR freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0

+02 00:00:00.000000

LOW 10/20/2018 12:00:00.000000 AM -04:00

FALSE FALSE Weekend window - for compatibility only

SYS MANUALSTATS_WEEKDAYS_WINDOW DEFAULT_MAINTENANCE_PLAN SYS MANUALSTATS_WEEKDAYS_SCHEDULE NAMED +00 06:00:00.000000

HIGH 3/24/2020 10:00:00.000000 PM +00:00

3/24/2020 10:00:00.295488 PM +00:00

TRUE TRUE window opens every day at 10:00 pm and ends at 04:00 am

*LOG_DATE*

*OWNER*

*JOB_NAME*

*JOB_SUBNAME*

*STATUS*

*ERROR#*

*REQ_START_DATE*

*ACTUAL_START_DATE*

*RUN_DURATION*

*INSTANCE_ID*
3/24/2020 6:14:34.583018 AM -04:00

SYS PMP_MANUAL_STATS SUCCEEDED 0

3/23/2020 10:00:00.732552 PM -04:00

3/23/2020 10:00:01.101570 PM -04:00

+00 08:14:33.000000

1

3/23/2020 12:00:35.121660 AM -04:00

SYS PMP_MANUAL_STATS SUCCEEDED 0

3/22/2020 10:00:00.868242 PM -04:00

3/22/2020 10:00:01.730078 PM -04:00

+00 02:00:33.000000

1

3/21/2020 11:36:47.174682 PM -04:00

SYS PMP_MANUAL_STATS SUCCEEDED 0

3/21/2020 10:00:00.471529 PM -04:00

3/21/2020 10:00:00.866283 PM -04:00

+00 01:36:46.000000

2

3/21/2020 12:44:57.617524 AM -04:00

SYS PMP_MANUAL_STATS SUCCEEDED 0

3/20/2020 10:00:00.047892 PM -04:00

3/20/2020 10:00:00.470036 PM -04:00

+00 02:44:57.000000

2

3/20/2020 2:26:56.067705 PM -04:00

SYS PMP_MANUAL_STATS SUCCEEDED 0

3/19/2020 10:00:00.268441 PM -04:00

3/19/2020 10:00:01.045376 PM -04:00

+00 16:26:55.000000

1

3/19/2020 11:47:48.701722 AM -04:00

SYS PMP_MANUAL_STATS SUCCEEDED 0

3/18/2020 10:00:00.912440 PM -04:00

3/18/2020 10:00:01.259249 PM -04:00

+00 13:47:47.000000

2

Could you please let me know if I need to make changes to any of the above scheduler job creation steps?

Thanks,

Mani

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 25 2020 - 00:20:53 CET

Original text of this message