a few months ago I managed to enlarge the maintenance windows for autotask on a pluggable database, this way:

    name  => 'WINDOW_NAME');
    name      => 'WINDOW_NAME',
    attribute => 'DURATION',
    value     => numtodsinterval(6, 'hour'));
    name => 'WINDOW_NAME');

This was what I got:

WINDOW_NAME      START_TIME                               DURATION
---------------- ---------------------------------------- ---------------
THURSDAY_WINDOW  08-JUL-21 PM +02:00      +000 06:00:00
FRIDAY_WINDOW    09-JUL-21 PM +02:00      +000 06:00:00
SATURDAY_WINDOW  10-JUL-21 PM +02:00      +000 06:00:00
SUNDAY_WINDOW    11-JUL-21 PM +02:00      +000 06:00:00
MONDAY_WINDOW    12-JUL-21 PM +02:00      +000 06:00:00
TUESDAY_WINDOW   13-JUL-21 PM +02:00      +000 06:00:00
WEDNESDAY_WINDOW 14-JUL-21 PM +02:00      +000 06:00:00

But now, on that same pluggable db I'm noticing that since August 24th stats jobs are being stopped due to the end of the window:

TASK_NAME                 STATUS     CURRENT_JOB_NAME          LAST_TRY_DATE                       LAST_TRY_R
------------------------- ---------- ------------------------- ----------------------------------- ----------
gather_stats_prog         ENABLED                              12-SEP-21 AM +02:00 STOPPED AT
                                                                                                    END OF MA

auto_space_advisor_prog   ENABLED                              12-SEP-21 AM +02:00 SUCCEEDED
AUTO_SQL_TUNING_PROG      ENABLED                              11-SEP-21 PM +02:00 SUCCEEDED

At first I thought that it might be caused by some performance problem, but then I noticed that the jobs were still during 4 hours (default windows duration):

CLIENT_NAME                         JOB_NAME                  JOB_STATUS JOB_START_TIME                             JOB_DURATION
----------------------------------- ------------------------- ---------- ------------------------------------------ ---------------
auto optimizer stats collection     ORA$AT_OS_OPT_SY_9601     STOPPED    07-SEP-21 PM EUROPE/VIENNA +000 03:59:44
auto optimizer stats collection     ORA$AT_OS_OPT_SY_9621     STOPPED    08-SEP-21 PM EUROPE/VIENNA +000 03:59:53

I then noticed that the maintenance windows duration was still set to 4 hours on the container database:

WINDOW_NAME      START_TIME                               DURATION
---------------- ---------------------------------------- ---------------
SUNDAY_WINDOW    12-SEP-21 PM +02:00      +000 04:00:00
MONDAY_WINDOW    13-SEP-21 PM +02:00      +000 04:00:00
TUESDAY_WINDOW   14-SEP-21 PM +02:00      +000 04:00:00
WEDNESDAY_WINDOW 15-SEP-21 PM +02:00      +000 04:00:00
THURSDAY_WINDOW  16-SEP-21 PM +02:00      +000 04:00:00
FRIDAY_WINDOW    17-SEP-21 PM +02:00      +000 04:00:00
SATURDAY_WINDOW  18-SEP-21 PM +02:00      +000 04:00:00

Should I modify the duration on the container too?

Thank you.


1 Answer 1


I would certainly do that. Only oracle knows the internals and even there I have my doubts. But there is more to do. There are a lot of settings in gather statistics.

You seem to follow the brute force method. You might gain some benefits from studying the frequency of the tables, samplesizes and partitions. Involve a developer and use their knowledge about the table/application behavior. 4 hours is a long time to sample statistics in. When I am ready most of the times the sampling ends within an hour. Speaking about 10T databases with mixed workloads.

  • Thanks for answering. Do you know if there's any way to check on which objects the statistics took more time?
    – trustno1
    Sep 12, 2021 at 16:16
  • Not directly but check DBA tab stats history tables and the current statistics. See which tables/partitions are samples most. Are partitions in use?
    – user953
    Sep 12, 2021 at 16:24
  • Yes, some of the objects are partitioned. I'm sorry, what do you mean with "See which tables/partitions are samples most" ? Size sampling is not visible from that table
    – trustno1
    Sep 12, 2021 at 19:42
  • A table or partition that is sampled every day might be worth looking into. Also check the sample sizes. See docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/… in the stats hist tables you can also find the used sample sizes. For big tables set parallellism. For partitioned tables set incremental statistics. Both can save lots of sampling time.
    – user953
    Sep 12, 2021 at 19:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.