DBA_AUTOTASK_WINDOW_CLIENTS 每晚10点数据库繁忙调整

jnrjian

已于 2024-09-25 15:11:33 修改

阅读量315

点赞数

文章标签： oracle

于 2023-11-03 17:23:08 首次发布

本文链接：https://blog.csdn.net/jnrjian/article/details/134207704

版权

for SID in `ps -ef | grep pmon | grep -v grep | grep -v ASM | cut -d"_" -f3,4 `
for SID in PDB PDB1 PDB2
do
sqlplus -s ' / as sysdba' <<EOF
alter session set container=$SID;
select * from DBA_AUTOTASK_WINDOW_CLIENTS;
/

begin
dbms_scheduler.disable(name => 'MONDAY_WINDOW');
dbms_scheduler.set_attribute(
name => 'MONDAY_WINDOW',
attribute => 'REPEAT_INTERVAL',
value => 'freq=daily;byday=MON; byhour=22;byminute=40; bysecond=0');
dbms_scheduler.enable(name => 'MONDAY_WINDOW');
end;
/

begin
dbms_scheduler.disable(name => 'TUESDAY_WINDOW');
dbms_scheduler.set_attribute(
name => 'TUESDAY_WINDOW',
attribute => 'REPEAT_INTERVAL',
value => 'freq=daily;byday=TUE; byhour=22;byminute=40; bysecond=0');
dbms_scheduler.enable(name => 'TUESDAY_WINDOW');
end;
/

begin
dbms_scheduler.disable(name => 'WEDNESDAY_WINDOW');
dbms_scheduler.set_attribute(
name => 'WEDNESDAY_WINDOW',
attribute => 'REPEAT_INTERVAL',
value => 'freq=daily;byday=WED; byhour=22;byminute=40; bysecond=0');
dbms_scheduler.enable(name => 'WEDNESDAY_WINDOW');
end;
/

begin
dbms_scheduler.disable(name => 'THURSDAY_WINDOW');
dbms_scheduler.set_attribute(
name => 'THURSDAY_WINDOW',
attribute => 'REPEAT_INTERVAL',
value => 'freq=daily;byday=THU; byhour=22;byminute=40; bysecond=0');
dbms_scheduler.enable(name => 'THURSDAY_WINDOW');
end;
/

begin
dbms_scheduler.disable(name => 'FRIDAY_WINDOW');
dbms_scheduler.set_attribute(
name => 'FRIDAY_WINDOW',
attribute => 'REPEAT_INTERVAL',
value => 'freq=daily;byday=FRI; byhour=22;byminute=40; bysecond=0');
dbms_scheduler.enable(name => 'FRIDAY_WINDOW');
end;
/

select * from DBA_AUTOTASK_WINDOW_CLIENTS;
/
EOF
done

10点开始持续X个小时！

dba_autotask_schedule

How do you change the default windows for the automated maintenance task to run?
Maintenance windows can be modified using the DBMS_SCHEDULER PL/SQL package. For details see:
Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
Configuring Maintenance Windows
Configuring Maintenance Windows

Here is an example to change the default time for the daily maintenance window for example to 2:00 AM instead of 10:00 PM using the following commands :
BEGIN
dbms_scheduler.disable(
    name => 'SATURDAY_WINDOW');
dbms_scheduler.set_attribute(
    name      => 'SATURDAY_WINDOW',
    attribute => 'repeat_interval',
    value     => 'freq=daily;byday=SAT;byhour=02;byminute=0;bysecond=0');
dbms_scheduler.enable(
    name => 'SATURDAY_WINDOW');
END;
/

BEGIN
dbms_scheduler.disable(
    name => 'SUNDAY_WINDOW');
dbms_scheduler.set_attribute(
    name      => 'SUNDAY_WINDOW',
    attribute => 'repeat_interval',
    value     => 'freq=daily;byday=SUN;byhour=02;byminute=0;bysecond=0');
dbms_scheduler.enable(
    name => 'SUNDAY_WINDOW');
END;
/

BEGIN
dbms_scheduler.disable(
    name => 'MONDAY_WINDOW');
dbms_scheduler.set_attribute(
    name      => 'MONDAY_WINDOW',
    attribute => 'repeat_interval',
    value     => 'freq=daily;byday=MON;byhour=02;byminute=0;bysecond=0');
dbms_scheduler.enable(
    name => 'MONDAY_WINDOW');
END;
/

BEGIN
dbms_scheduler.disable(
    name => 'TUESDAY_WINDOW');
dbms_scheduler.set_attribute(
    name      => 'TUESDAY_WINDOW',
    attribute => 'repeat_interval',
    value     => 'freq=daily;byday=TUE;byhour=02;byminute=0;bysecond=0');
dbms_scheduler.enable(
    name => 'TUESDAY_WINDOW');
END;
/

BEGIN
dbms_scheduler.disable(
    name => 'WEDNESDAY_WINDOW');
dbms_scheduler.set_attribute(
    name      => 'WEDNESDAY_WINDOW',
    attribute => 'repeat_interval',
    value     => 'freq=daily;byday=WED;byhour=02;byminute=0;bysecond=0');
dbms_scheduler.enable(
    name => 'WEDNESDAY_WINDOW');
END;
/

BEGIN
dbms_scheduler.disable(
    name => 'THURSDAY_WINDOW');
dbms_scheduler.set_attribute(
    name      => 'THURSDAY_WINDOW',
    attribute => 'repeat_interval',
    value     => 'freq=daily;byday=THU;byhour=02;byminute=0;bysecond=0');
dbms_scheduler.enable(
    name => 'THURSDAY_WINDOW');
END;
/

BEGIN
dbms_scheduler.disable(
    name => 'FRIDAY_WINDOW');
dbms_scheduler.set_attribute(
    name      => 'FRIDAY_WINDOW',
    attribute => 'repeat_interval',
    value     => 'freq=daily;byday=FRI;byhour=02;byminute=0;bysecond=0');
dbms_scheduler.enable(
    name => 'FRIDAY_WINDOW');
END;
/
Whats new/changed in 11g with respect to automatic statistics collection?
The GATHER_STATS_JOB does not exist in 11g (the name does not exist). Instead it has been included in Automatic Maintenance Tasks. The accuracy of the statistics gathered has been significantly improved by the implementation of a better version of AUTO_SAMPLE_SIZE. In 11g, using auto size for ESTIMATE_PERCENT defaults to 100% and therefore is as accurate as possible for the table itself. In prior versions a 100% sample may have been impossible due to time collection constraints, however 11g implements a new hashing algorithm to compute the statistics rather than sorting (in 9i and 10g the "slow" part was typically the sorting) which significantly improves collection time and resource usage. There is more on this in the following blog:
https://blogs.oracle.com/optimizer/post/how-does-auto-sample-size-work-in-oracle-database-12c

The following are the tasks that AutoTask automatically schedules in these maintenance windows:
```
select CLIENT_NAME from DBA_AUTOTASK_CLIENT
 
 CLIENT_NAME
 ----------------------------------------------------------------
 auto optimizer stats collection
 auto space advisor
 sql tuning advisor
```

How to enable auto stats collection?

If for some reason automatic optimizer statistics collection is disabled, you can enable it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

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

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

How to disable the auto stats collection?
In situations when you want to disable automatic optimizer statistics collection, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
```
exec DBMS_AUTO_TASK_ADMIN.DISABLE(
 client_name => 'auto optimizer stats collection', 
 operation => NULL, 
 window_name => NULL);
```
What is the affect of setting STATISTICS_LEVEL= BASIC on automatic statistics collection?

Setting STATISTICS_LEVEL = BASIC disables automatic optimizer statistics collection.
How can I check the status of the 'auto optimizer stats collection'?
The status of the automatic statistics collection can be checked using:
```
select client_name, JOB_SCHEDULER_STATUS 
 from DBA_AUTOTASK_CLIENT_JOB
 where client_name='auto optimizer stats collection';
```
The possible Job status:
- DISABLED
- RETRY SCHEDULED
- SCHEDULED
- RUNNING
- COMPLETED
- BROKEN
- FAILED
- REMOTE
- SUCCEEDED
- CHAIN_STALLED
How can I check whether or not the database has the 'auto optimizer stats collection' job enabled to run during the next maintenance window?

SELECT CLIENT_NAME,
STATUS
FROM DBA_AUTOTASK_CLIENT
WHERE CLIENT_NAME = 'auto optimizer stats collection';

How can I see the history of the automatic stats job for each day?

SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed
 FROM dba_autotask_client_history
 WHERE client_name like '%stats%';
 
                                                  JOBS    JOBS     JOBS
 CLIENT_NAME                     WINDOW_NAME      CREATED STARTED  COMPLETED
 ------------------------------- ---------------- ------- -------- ----------
 auto optimizer stats collection THURSDAY_WINDOW        1        1          1
 auto optimizer stats collection SUNDAY_WINDOW          3        3          3
 auto optimizer stats collection MONDAY_WINDOW          1        1          1
 auto optimizer stats collection SATURDAY_WINDOW        2        2          2

How to manually execute the Optimizer Statistics Auto Task?
In 11g the Auto-Task infrastructure replaced the need for the gather_stats_job and you can execute the following command to accomplish manual statistics collection:
```
SQL> exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;
```
This will prompt the Automated Maintenance Tasks subsystem into starting a job that will gather optimizer statistics, unless such a job is already running (for example if a maintenance window is currently open). If an immediate job is created it will be named ORA$_AT_OS_MANUAL_nnnnnn (nnnnn is one or more decimal digits). Unlike regular Automated Maintenance jobs, the "MANUAL" job is not tied to a specific maintenance window.
How do to check values of parameter( estimate percent, type of histograms etc) used by the job?
```
DBMS_STATS.GET_PARAM (pname IN VARCHAR2) RETURN VARCHAR2;
```
If there were any non-default preferences set for the job:
```
DBMS_STATS.GET_PREFS (pname IN VARCHAR2,ownname IN VARCHAR2 DEFAULT NULL, tabname IN VARCHAR2 DEFAULT NULL)
 RETURN VARCHAR2;
```
Possible preferences: -
- AUTOSTATS_TARGET
- CASCADE
- DEGREE
- ESTIMATE_PERCENT
- METHOD_OPT
- NO_INVALIDATE
- GRANULARITY
- PUBLISH
- INCREMENTAL
- STALE_PERCENT
How to set preference for the next maintenance job run to pick?
The automatic statistics-gathering job uses the default parameter values for the DBMS_STATS procedures. If you wish to change these default values you can use the DBMS_STATS.SET_GLOBAL_PREFS procedure. Remember these values will be used for all schemas including 'SYS'. There are 2 different procedures that you can use to set parameters depending on whether you have existing preferences or not:
- SET_GLOBAL_PREFS - This procedure enables you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for any object in the database that does not have an existing table preference or for any new objects created after this.
- SET_DATABASE_PREFS - This procedure enables you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for all user-defined schemas in the database.
How to I change the "STALE PERCENT", for example?
If the stale percentage does not collect statistics frequently enough to reflect changes in the data accurately, then you can use the "DBMS_STATS.SET_GLOBAL_PREFS" procedure to change this (and other parameters) so that statistics are collected more frequently in future. For Example:
To change the 'STALE_PERCENT' you can use:
```
exec DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT','5');
```
NOTE: if you only want to do this for a small subset of the objects and not all of them, you may be better creating a custom statistics gathering scheme
Does the auto stats gathering job gather statistics on all schemas in the instance?

By default 'auto optimizer stats collection' is controlled by the Global preference AUTOSTATS_TARGET which defaults to AUTO collecting all schemas including SYS. On 12c this includes fixed object statistics, however, on 11g it does not. See:

Document 457926.1 How to Gather Statistics on SYS Objects and 'Fixed' Objects?

Oracle® Database SQL Tuning Guide
12c Release 1 (12.1)
E15858-15
Chapter 12 Managing Optimizer Statistics: Basic Topics
Section 12.4.5 Gathering Statistics for Fixed Objects
Page Moved
What is the AUTOSTATS_TARGET of SET_GLOBAL_PREFS?
This additional parameter controls which objects the automatic statistic gathering job (that runs in the nightly maintenance window) will monitor. The possible values for this parameter are:
- ALL
  This setting means that the automatic statistics gathering job will gather statistics on all objects in the database.
  From 12c this includes statistics on fixed objects. See:
  
  Oracle® Database SQL Tuning Guide
  12c Release 1 (12.1)
  E15858-15
  Chapter 12 Managing Optimizer Statistics: Basic Topics
  Section 12.4.5 Gathering Statistics for Fixed Objects
  Page Moved
  
  On 11g and below this was APART FROM statistics on fixed objects.
  
  Since Fixed objects record current database activity, the representative workload you want to capture may not be active at the time of automatic statistics collection. You should gather statistics when the database has representative activity. You can manually collect statistics on fixed objects, such as the dynamic performance tables, using GATHER_FIXED_OBJECTS_STATS procedure.
- ORACLE
  ORACLE means that the automatic statistics gathering job will only gather statistics for Oracle owned schemas (sys, system, etc)
- AUTO (default)
  means that Oracle will decide what objects to gather statistics on. Currently AUTO and ALL behave the same.
How Are Statistics Gathered on Partitioned Tables during the Auto Gather Stats Job?

Document 1592404.1 Automatic Optimizer Statistics Collection on Partitioned Table
May I Perform Partition Maintenance while the Auto Gather Stats Job is Running?

If you need to perform partition maintenance operations, it is best to avoid performing them while the Auto Gather Stats Job is executing. A partition maintenance operation, such as ALTER TABLE ... MOVE PARTITION, will require that you gather new statistics on the partition and any dependent indices. The Auto Gather Stats Job starts by creating a list of stale objects. These may be either tables or table partitions. That list is set only at the beginning of the job and does not change.
How does auto optimizer stats collection prioritize which tables are analyzed first?

Accurate statistics are important on all objects. The GATHER_DATABASE_STATS_JOB_PROC procedure called by the 'auto optimizer stats collection' job prioritizes database objects that have no statistics. This means that objects that most need statistics are processed first. Once these are done then objects with stale statistics are addressed. For these, there is no particular prioritization. The statistics may be ordered in some way but it is cursory, ordering by owner,object_name,part_name just to be consistent.
How do you disable automatic statistics gathering for a specific schema?

You can do this by using DBMS_STATS.LOCK_SCHEMA_STATS to lock the statistics. See:

Document 283890.1 Preserving Statistics using DBMS_STATS.LOCK_TABLE_STATS
Is there any reason to gather full schema stats regularly in addition to the automatic stats gathering job?

If your data changes very frequently then it the automatic job may not collect the statistics frequently enough (for example, it may be collecting other tables on occasion) and so you may find that you get better statistics by manually collecting on that table to keep up with the changes that are happening. There may also be cases where the default sample size does not pick representative data and you need to select a different sample.
Can extended statistics be gathered automatically by the automatic stats gathering job?
From Oracle 12c, column group statistics are created automatically as part of adaptive query optimization. For more details, refer to the following article:
Document 1964223.1 Are Extended Statistics Collected Automatically on Oracle 12c?

10g Collection: Via GATHER_STATS_JOB

What is the name of the default stats gathering job on 10g?

The automatic statistics gathering job on 10g is called "GATHER_STATS_JOB"
What are the default windows for the GATHER_STATS_JOB ?
In 10g, Two Scheduler windows are predefined upon installation of Oracle Database:
- WEEKNIGHT_WINDOW starts at 10 p.m. and ends at 6 a.m. every Monday through Friday.
- WEEKEND_WINDOW covers whole days Saturday and Sunday.
Together these windows constitute the MAINTENANCE_WINDOW_GROUP in which all system maintenance tasks are scheduled.
How do you disable the GATHER_STATS_JOB?
The most direct approach is to disable the GATHER_STATS_JOB as follows:
```
SQL> exec sys.dbms_scheduler.disable ('GATHER_STATS_JOB');
```
How do you enable the GATHER_STATS_JOB?
This is enabled by default. If you have disabled it, then you can re-enable it as
```
SQL> exec sys.dbms_scheduler.enable ("SYS"."GATHER_STATS_JOB");
```
How do you Determine That the GATHER_STATS_JOB Completed
```
SELECT job_name, state
 FROM dba_scheduler_jobs
 WHERE job_name='GATHER_STATS_JOB';
```
There are four types of jobs that are not running:
- FAILED
- BROKEN
- DISABLED
- COMPLETED
NOTE: if a job has recently completed successfully, but is scheduled to run again, the job state is set to 'SCHEDULED'. A job is marked as 'COMPLETED' if 'end_date' or 'max_runs' (in dba_scheduler_jobs) is reached.

How to Change the NEXT_RUN_DATE on the GATHER_STATS_JOB ?

You can adjust the predefined maintenance windows to a time suitable to your database environment using the DBMS_SCHEDULER.SET_ATTRIBUTE procedure

For Example:

begin 
 dbms_scheduler.disable('gather_stats_job'); 
 dbms_scheduler.set_attribute_null('gather_stats_job','schedule_name'); 
 dbms_scheduler.set_attribute(-
 'gather_stats_job','repeat_interval',-
'freq=minutely;byminute=1,11,21,31,41,51;byhour=0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,22,23;bysecond=0'-
); 
 dbms_scheduler.enable('gather_stats_job'); 
 end;

to run job on weekends only:

SQL> exec sys.dbms_scheduler.disable( '"SYS"."GATHER_STATS_JOB"' );
 SQL> exec sys.dbms_scheduler.set_attribute( name => '"SYS"."GATHER_STATS_JOB"', attribute =>'schedule_name', value => 'SYS.WEEKEND_WINDOW');
 SQL> exec sys.dbms_scheduler.enable( '"SYS"."GATHER_STATS_JOB"' );