Theoretically, the Automatic Maintenance Tasks which controlled the automatic optimizer statistics can easily configured via Cloud Control. The problem is that, with version 12c, the database comes by standard only with the Enterprise Manager Database Express 12c. With EM Express you can only get to know that Automatic Maintenance is in use or it is not (Configuration > Used database features). In the “standard” Enterprise Manager 11g the appropriate configuration option still was installed (Server > Automated Maintenance Tasks).
So the only way with 12c beside Cloud Control is the command line. The following works with both 12c and 11g.
Installed by default, the database comes with three preconfigured Automatic Maintenance Tasks, called clients. First, the collection of optimizer statistics (Optimizer Statistics Collection), the Segment Advisor (Space Advisor) and the automatic SQL tuning (SQL Tuning Advisor).
a. Status
First, the query for the current state of the database in DBA_AUTOTASK_CLIENTS. Only in Enterprise Edition is the SQL Tuning Advisor ENABLED.
SQL> select client_name, status, attributes from dba_autotask_client;
CLIENT_NAME STATUS ATTRIBUTES
--------------------------------- ------- ----------------------------------------
auto optimizer stats collection ENABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor ENABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor ENABLED ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL
As a further query the configured time window in DBA_AUTOTASK_WINDOW_CLIENTS:
select window_name, autotask_status, optimizer_stats from dba_autotask_window_clients;
WINDOW_NAME AUTOTASK_STATUS OPTIMIZER_STATS
------------------------ ---------------- ----------------
SUNDAY_WINDOW ENABLED ENABLED
SATURDAY_WINDOW ENABLED ENABLED
FRIDAY_WINDOW ENABLED ENABLED
THURSDAY_WINDOW ENABLED ENABLED
WEDNESDAY_WINDOW ENABLED ENABLED
TUESDAY_WINDOW ENABLED ENABLED
MONDAY_WINDOW ENABLED ENABLED
By default seven time windows are configured:
select * from DBA_SCHEDULER_WINDOWS;
|
Time Window |
Start | Duration | Status |
| MONDAY_WINDOW | 22 = 10 pm | 4h | enabled |
| TUESDAY_WINDOW | 22 = 10 pm | 4h | enabled |
| WEDNESDAY_WINDOW | 22 = 10 pm | 4h | enabled |
| THURSDAY_WINDOW | 22 = 10 pm | 4h | enabled |
| FRIDAY_WINDOW | 22 = 10 pm | 4h | enabled |
| SATURDAY_WINDOW | 6 am | 20h | enabled |
| SUNDAY_WINDOW | 6 am | 20h | enabled |
DBA_AUTOTASK_SCHEDULE supplies the configured time windows for the next 32 days:
select * from DBA_AUTOTASK_SCHEDULE order by start_time;
b. Activation / Deactivation
Quite generally, the auto tasks can be controlled with the DBMS_AUTO_TASK_ADMIN package.
To start and stop AUTO_TASKS enable or disable is sufficient.
exec dbms_auto_task_admin.disable;
select window_name, autotask_status, optimizer_stats from dba_autotask_window_clients;
WINDOW_NAME AUTOTASK_STATUS OPTIMIZER_STATS
------------------------ ---------------- ----------------
SUNDAY_WINDOW DISABLED ENABLED
SATURDAY_WINDOW DISABLED ENABLED
FRIDAY_WINDOW DISABLED ENABLED
THURSDAY_WINDOW DISABLED ENABLED
WEDNESDAY_WINDOW DISABLED ENABLED
TUESDAY_WINDOW DISABLED ENABLED
MONDAY_WINDOW DISABLED ENABLED
Following command deactivates only Optimizer Stats Collection:
begin
dbms_auto_task_admin.disable(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
end;
/
select window_name, autotask_status, optimizer_stats from dba_autotask_window_clients;
WINDOW_NAME AUTOTASK_STATUS OPTIMIZER_STATS
------------------------ ---------------- ----------------
SUNDAY_WINDOW ENABLED DISABLED
SATURDAY_WINDOW ENABLED DISABLED
FRIDAY_WINDOW ENABLED DISABLED
THURSDAY_WINDOW ENABLED DISABLED
WEDNESDAY_WINDOW ENABLED DISABLED
TUESDAY_WINDOW ENABLED DISABLED
MONDAY_WINDOW ENABLED DISABLED
SQL> select client_name, status from dba_autotask_client;
CLIENT_NAME STATUS
--------------------------------- ---------
auto optimizer stats collection DISABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
c. Changing START time
If you want to change the start time, you have to adjust the REPEAT_INTERVAL. Below we will change for the MONDAY_WINDOW the start time to 5 clock with DBMS_SCHEDULER.SET_ATTRIBUTE:
begin
dbms_scheduler.set_attribute(
name => 'MONDAY_WINDOW',
attribute => 'repeat_interval',
value => 'freq=daily;byday=MON;byhour=5;byminute=0; bysecond=0');
end;
/
select window_name, repeat_interval, from dba_scheduler_windows where window_name = 'MONDAY_WINDOW';
WINDOW_NAME REPEAT_INTERVAL
---------------- -----------------------------------------------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=5;byminute=0; bysecond=0
d. Changing DURATION
Is the time window of 4 hours not enough to work through all the tables and the Optimizer Statistics Collection runs out of the predetermined windows, the DURATION must be adjusted. Below, we set the window up to 5 hours.
begin
dbms_scheduler.set_attribute(
name => 'MONDAY_WINDOW',
attribute => 'duration',
value => numtodsinterval(5, 'hour'));
end;
/
select window_name, duration from dba_scheduler_windows where window_name = 'MONDAY_WINDOW';
WINDOW_NAME REPEAT_INTERVAL
--------------- --------------------
MONDAY_WINDOW +00 05:00:00.000000
e. Creating a new WINDOW
begin
dbms_scheduler.create_window(
window_name => 'SPECIAL_WINDOW',
duration => numtodsinterval(3, 'hour'),
resource_plan => 'DEFAULT_MAINTENANCE_PLAN',
repeat_interval => 'FREQ=DAILY;BYHOUR=10;BYMINUTE=0;BYSECOND=0');
dbms_scheduler.add_group_member(
group_name => 'MAINTENANCE_WINDOW_GROUP',
member => 'SPECIAL_WINDOW');
end;
/
Thank you for taking time to put this explanation together. Very useful information.
At times we can verify when the statistics are actually gathered! Thank you.
That’s very useful information. Thanks.
Thanks. I have searched the entire web today looking for something like this. And finally stumbled upon this page.
I think it is worth noting here that the tuning pack has a prerequisite of the diagnostics pack and both of these are separately licensed options. Considering a 80 user system, this would cost $20,000 plus 22% support per year.
Awesome explanation — very good Article
Just checked an standard edition database…no diagnostic or tuning pack…
and surprise: the sql tuning advisor is ENABLED…
hhmmm..license violation?
But wait…there is no way to license diagnostic or tuning pack for standard edition 😉
SQL> show parameter pack
NAME TYPE VALUE
———————————— ———– ——————————
[color=red]control_management_pack_access string NONE[/color]
SQL> select client_name, status, attributes from dba_autotask_client;
CLIENT_NAME STATUS
—————————————————————- ——–
ATTRIBUTES
——————————————————————————–
auto optimizer stats collection ENABLED
ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor ENABLED
ON BY DEFAULT, VOLATILE, SAFE TO KILL
[color=red]sql tuning advisor ENABLED[/color]
ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL
SQL> select * from v$version;
BANNER
——————————————————————————–
CON_ID
———-
Oracle Database 12c [color=red]Standard [/color]Edition Release 12.1.0.2.0 – 64bit Production
0
PL/SQL Release 12.1.0.2.0 – Production
0
CORE 12.1.0.2.0 Production
0
TNS for Linux: Version 12.1.0.2.0 – Production
0
NLSRTL Version 12.1.0.2.0 – Production
0
As far as I know the usage of Diagnostic or Tuning Pack is strictly disallowed in Standard Edition. In case this is not a test environment this will lead to a license violation.
You should check AWR and SQL Tuning Pack usage:
select * from dba_feature_usage_statistics where name like ‘Au%W%R%’;
select * from dba_feature_usage_statistics where name like ‘SQL%T%A%’;
Overview:
select * from dba_feature_usage_statistics where name like ‘A%W%R%’ or ‘SQL%’;
You should also check control_management_pack_access parameter and disable if it’s not “NONE”:
select value from v$parameter where name like ‘control_m%’;
NONE – no package available
DIAGNOSTIC – Diagnostic Package available
DIAGNOSTIC+TUNING – Diagnostic and Tuning Package available
alter system set control_management_pack_access=NONE;
I already checked the feature usage view and the init parameter…
SQL> select name,DETECTED_USAGES,CURRENTLY_USED from dba_feature_usage_statistics where name like ‘Au%W%R%’ or name like ‘SQL%T%A%’;
NAME DETECTED_USAGES
—————————————————————- —————
CURRE
—–
SQL Tuning Advisor 0
FALSE
Automatic Workload Repository 0
FALSE
SQL> select value from v$parameter where name like ‘control_m%’;
VALUE
——————————————————————————–
NONE
it seems to me that the created automatic maintenance task ‘sql tuning advisor’ is ignored in standard edition.
SQL> select ADVISOR_NAME,STATUS,count(*) from dba_advisor_tasks group by ADVISOR_NAME,status
ADVISOR_NAME STATUS COUNT(*)
—————————— ———– ———-
Segment Advisor COMPLETED 62
SPM Evolve Advisor COMPLETED 1
SQL Tuning Advisor INITIAL 1
Acutally I have chacked the dba_advisor_tasks on three Standard Editions here and got the exact same result for Segment Advisor (COMPLETED 63) and SQL Tuning Advisor (INITIAL 1).
For your SPM entry – You can use SPM – SQL Plan Management in SE, but you shouldn’t: https://docs.oracle.com/cd/E11882_01/license.112/e47877/editions.htm#DBLIC116
It’s only a part of Enterprise Edition – it’s not a part of Diagnostics and Tuning Pack, so the control_management_pack_access didn’t work here.
For those who seeking cloud-native Oracle Database Monitoring and Performance capabilities, have fewer databases and thus may not have EM packs, there is now the OCI Database Monitoring service complimentary to Enterprise Manager