Oracle 11g : Types of Statistics Collected

This article is the continuation of Oracle automated tasks discussed in the following post
BASH-DBA: Oracle 11g Automated Maintenance Tasks

To properly diagnose any issue with the database, stats should be available. Oracle gathers several types of statistics at the system, session or SQL level.Two main categories for statistic gathering are 

1. Database Statistics
2. OS Statistics


In this article we will cover both the categories briefly 


1.Database Statistics:


DB stats provide the type of load the database is experiencing currently or some time in past. The first and foremost of them would be wait events


1.1.Wait Events:


Wait Event records and presents all the bottlenecks that a process (user or background )encounters from start to finish. It keeps track of the number of times and the amount of time a process spent on each bottleneck.To get the “amount of time ” part, however, you must set the initialization parameter, TIMED_STATISTICS, to TRUE.


You can repeatedly querying some of the following wait event views in short successions when investigating performance bottlenecks.

V$EVENT_NAME

This is where we can find out the total number of events that Oracle has defined in the database. Do the COUNT (*) against this view.Also, this view can be queried to find the exact spelling of an event name.

V$SYSTEM_EVENT

The V$SYSTEM_EVENT view is a good place to start if you want to perform a quick health check on the database instance. This quick health check may be helpful when you must diagnose a poorly performing database instance, especially if you are unfamiliar with it and the application. You will quickly discover the top n bottlenecks that plague the database instance since startup. However, don’t judge performance or make any tuning decisions based on these bottlenecks just yet. The information is cumulative since the instance start-up.You should always order the data from this view by the TIME_WAITED column

V$SESSION_EVENT

The V$SESSION_EVENT view is useful when SID of the session is known that is currently connected to the instance.Let’s say you get a call about a job that is running very slowly. You ask for  the USERNAME and find the SID from the V$SESSION view. You then query the V$SESSION_EVENT view for the particular SID and order the result by the TIME_WAITED column

V$SESSION_WAIT

A good time to query the V$SESSION_WAIT view is when you get a call about a slow running application and you want to investigate what the application is doing at that particular moment. Due to its real-time nature, you have to query this view repeatedly, most likely in quick successions


V$SESSION

The V$SESSION view displays information about each current session and contains the same wait statistics as those found in the V$SESSION_WAIT view

V$SESSION_WAIT_CLASS

The V$SESSION_WAIT_CLASS view provides the number of waits and the time spent in each class of wait events for each session.

V$SESSION_WAIT_HISTORY

The V$SESSION_WAIT_HISTORY view displays information about the last ten wait events for each active session (such as event type and wait time).

V$EVENT_HISTOGRAM

The V$EVENT_HISTOGRAM view displays a histogram of the number of waits, the maximum wait, and total wait time on an event basis.

V$FILE_HISTOGRAM

The V$FILE_HISTOGRAM view displays a histogram of times waited during single block reads for each file.

V$SYSTEM_WAIT_CLASS

The V$SYSTEM_WAIT_CLASS view provides the instance wide time totals for the number of waits and the time spent in each class of wait events.

V$TEMP_HISTOGRAM

The V$TEMP_HISTOGRAM view displays a histogram of times waited during single block reads for each temporary file.

1.2.System and Session Statistics:


A large number of cumulative database statistics are available on a system and session level through the V$SYSSTAT and V$SESSTAT views for vital information about CPU usage


 1.3.Time Model Statistics:


When tuning an Oracle system, each component has its own set of statistics. To look at the system as a whole, it is necessary to have a common scale for comparisons. Because of this, most Oracle advisories and reports describe statistics in terms of time. In addition, the V$SESS_TIME_MODEL andV$SYS_TIME_MODEL views provide time model statistics. Using the common time instrumentation helps to identify quantitative effects on the database operations.


1.4.Active Session History (ASH)


The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. Active sessions are sampled every second and are stored in a circular buffer in SGA. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session. This includes any session that was on the CPU at the time of sampling.


For further information, see below the following articles on bash-dba.com

BASH-DBA: Manually Getting Active Session Information
BASH-DBA: Getting ASH Information from the Data Dictionary Views


2. OS Statistics
Operating system statistics are only an indication of how the hardware and operating system are working.


2.1.CPU Statistics

The V$OSSTAT view captures machine level information in the database, making it easier for you to determine if there are hardware level resource issues. TheV$SYSMETRIC_HISTORY view shows a one-hour history of the Host CPU Utilization metric, a representation of percentage of CPU usage at each one-minute interval. The V$SYS_TIME_MODEL view supplies statistics on the CPU usage by the Oracle database. Using both sets of statistics enable you to determine whether the Oracle database or other system activity is the cause of the CPU problems.

2.2.Disk I/O Statistics

Because the database resides on a set of disks, the performance of the I/O subsystem is very important to the performance of the database

I/O statistics of database files that are or have been accessed are captured in the V$IOSTAT_FILE view.I/O statistics for database functions (such as the LGWR and DBWR) are captured in the V$IOSTAT_FUNCTION view

When Oracle Database Resource Manager is enabled, I/O statistics for all consumer groups that are part of the currently enabled resource plan are captured in the V$IOSTAT_CONSUMER_GROUP view

2.3.Network Statistics

Oracle Database maintains a set of network I/O statistics in the V$IOS
TAT_NETWORK view


BASH-DBA: Oracle 11g Automated Maintenance Tasks
BASH-DBA: Resolving common Oracle Wait Events

BASH-DBA: Identifying Network-Intensive Processes

BASH-DBA: Mapping OS (Linux/unix) process to database process

BASH-DBA: Identifying Top Server-Consuming Processes (top)

BASH-DBA: Identifying Which Session to Terminate

BASH-DBA: Identifying System Bottlenecks (vmstat)

reference : Oracle documentation

Oracle Database 11g I/O Calibration

Every database reads and writes data on disk. This generates I/O. The front-end applications that spend most of the CPU time waiting for the read/write operations are called I/O-bound

When designing I/O system, following things are normally kept in mind

1. Storage in place and minimum requirement  

2. Application operation availability i.e. 24/7 or business hours

3. Data throughput and response times

The I/O calibration analyzes  the performance of the storage system by determining if I/O performance problems are because of the database itself or the storage subsystem. This is done by making random I/O calls to the datafiles producing results that closely matches the actual performance of the database.

Prerequisite for I/O calibration:

1. SYSDBA user

2. time_statistics = TRUE

SQL> show parameter timed_statistics


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                     boolean     TRUE

If the value of the timed_statistics parameter is false, set it to true with the following statement.


SQL> ALTER SYSTEM SET timed_statistics=true SCOPE=BOTH;
System altered.


3. Asynchronous I/O must be enabled

SQL> show parameter filesystemio_options


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none

If the value is none, change it to setall

SQL> ALTER SYSTEM SET filesystemio_options=setall SCOPE=SPFILE;
System altered.

Bounce the instance and run the below script to ensure Asynchroinous I/O is enabled 


SQL>SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I WHERE   F.FILE#=I.FILE_NO
AND FILETYPE_NAME='DATA FILE';

NAME                                               ASYNCH_IO
-------------------------------------------------- ---------
/u03/oradata/testdb/datafiles/system01.dbf       ASYNC_ON
/u03/oradata/testdb/datafiles/sysaux01.dbf       ASYNC_ON
/u03/oradata/testdb/datafiles/undotbs01.dbf     ASYNC_ON
/u03/oradata/testdb/datafiles/ sers01.dbf         ASYNC_ON

Note: only one calibration can be performed on a database instance at a time.

Run the I/O calibration:


Use DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure to run I/O calibration. Due to the overhead from running the I/O workload, I/O calibration should only be performed when the database is idle, or during off-peak hours, to minimize the impact of the I/O workload on the normal database workload.

CONN / AS SYSDBA
SET SERVEROUTPUT ON
DECLARE
  l_latency  PLS_INTEGER;
  l_iops     PLS_INTEGER;
  l_mbps     PLS_INTEGER;
BEGIN
   DBMS_RESOURCE_MANAGER.calibrate_io (num_physical_disks => 2,
                                       max_latency        => 10,
                                       max_iops           =>  l_iops,
                                       max_mbps           => l_mbps,
                                       actual_latency     => l_latency);
  dbms_output.put_line  ('max_iops = ' || iops);
  dbms_output.put_line  ('latency  = ' || lat);
  dbms_output.put_line('max_mbps  = ' || mbps);
END;
/
Max IOPS = 40
Max MBPS = 50
Latency  = 24
PL/SQL procedure successfully completed.

While the procedure is being executed, the progress can be viewed by the below SQL from another session

SQL>select * from V$IO_CALIBRATION_STATUS;
STATUS
---------------
IN PROGRESS


This procedure issues an I/O intensive read-only workload (made up of one megabytes of random of I/Os) to the database files to determine the maximum IOPS (I/O requests per second) and MBPS (megabytes of I/O per second) that can be sustained by the storage subsystem


Viewing the Results:

The below script can be executed to see the output of I/O calibration procedure

SQL> SELECT TO_CHAR(start_time, 'DD-MON-YYY HH24:MI:SS') AS start_time,
         TO_CHAR(end_time, 'DD-MON-YYY HH24:MI:SS') AS end_time, max_iops, max_mbps,     max_pmbps,latency,
          num_physical_disks AS disks
           FROM   dba_rsrc_io_calibrate;


START_TIME           END_TIME          MAX_IOPS   MAX_MBPS  MAX_PMBPS   LATENCY   DISKS
-------------------- -------------------- ---------- ---------- ---------- -------------------
10-MAY-012 15:55:19  10-MAY-012 16:02:20     40       50      38                     24           1

Oracle Database 11g Automatic Database Diagnostic Monitor (ADDM)

ADDM analyzes the AWR data on regular basis and diagnose the root cause of any performance issue with providing recommendations for correcting the problems.


AWR is discussed in detail in the following post


BASH-DBA: Oracle 11g AWR (Automatic Workload Repository)

ADDM Analysis:

An ADDM analysis is performed each time an AWR snapshot is taken and the results are saved in the database. The time period analyzed by ADDM is defined by the last two snapshots (the last hour by default). The goal of the analysis is to reduce a single throughput metric called DB time. DB timecan be viewed from V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.


Types of Problems Analyzed by ADDM:

• CPU load
• Memory usage
• I/O usage
• Resource intensive SQL
• Resource intensive PL/SQL and Java
• RAC issues
• Application issues
• Database configuration issues
• Concurrency issues
• Object contention

Setting up ADDM:


ADDM is enabled by default and controlled by two parameters

1. CONTROL_MANAGEMENT_PACK_ACCESS, if set to DIAGNOSTIC or DIAGNOSTIC+TUNNING 
2. STATISTICS_LEVEL if set to either TYPICAL or ALL

ADDM can analyze any two AWR snapshots (on the same database), as long as both snapshots are still stored in the AWR (have not been purged)


Manually Running the ADDM:


Oracle suggests running the ADDM  using Oracle Enterprise Manager, however in this article I am focusing on manual ADDM execution.


ADDM can be executed in three modes


1. Database Mode
2. Instance Mode
3. Partial Mode


Running ADDM in Database Mode:


In RAC, running the ADDM in database mode will analyze all the instances in the cluster. Database mode based ADDM executed can also be done in single instance but it will be treated as instance mode.


Below example creates an ADDM task in database analysis mode for the entire database during the time period defined by snapshots 11050 and 11056

VAR tname VARCHAR2(30);
BEGIN
  :tname := 'ADDM for 11PM to 5AM';
  DBMS_ADDM.ANALYZE_DB(:tname,  11050, 11056);
END;

where,


'ADDM for 7PM to 9PM' = Task Name

11050 = begin snap id

11056 =  end snap id

Snap ids can be taken from below SQL

SQL> select * from DBA_HIST_SNAPSHOT

Running the ADDM in Instance Mode:

To analyze a particular instance of the database

VAR tname VARCHAR2(30);
BEGIN
  :tname := 'my ADDM for 11PM to 5AM';
  DBMS_ADDM.ANALYZE_INST(:tname,11050,11056, 1);
END;



Where 1 = Instance number

Running the ADDM in Partial Mode:

VAR tname VARCHAR2(30);
BEGIN
  :tname := 'my ADDM for 7PM to 9PM';
  DBMS_ADDM.ANALYZE_PARTIAL(:tname, '1,2,4', 11050, 11056);
END;


Where 1, 2, and 4 are the instance numbers during the time period defined by snapshots 11050 and 11056 snapshots


Running the ADDM Reports:

SQL> SELECT DBMS_ADDM.GET_REPORT( :tname) FROM DUAL;


-----------------------------------------------------------------------------------------------------------

DBMS_ADDM.GET_REPORT(:TNAME)
--------------------------------------------------------------------------------
          ADDM Report for Task 'ADDM for 11PM to 5AM'
          -----------------
SQL> SET LONG 1000000 PAGESIZE 0;
SQL> SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;
          ADDM Report for Task 'ADDM for 7PM to 9PM'
          ------------------------------------------
Analysis Period
---------------
AWR snapshot range from 11050 to 11056.
Time period starts at 08-MAY-12 11.00.42 PM
Time period ends at 09-MAY-12 05.00.50 AM


Analysis Target
---------------
Database 'TESTDB' with DB ID 1544388080.
Database version 11.2.0.2.0.
Analysis was requested for all instances, but ADDM analyzed instance testdb,
numbered 1 and hosted at testdblab.
See the "Additional Information" section for more information on the requested
instances.


Activity During the Analysis Period
-----------------------------------
Total database time was 29 seconds.
The average number of active sessions was 0.
ADDM analyzed 1 of the requested 1 instances.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
There are no findings to report.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
There was no significant database activity to run the ADDM.
The database's maintenance windows were active during 49% of the analysis period.

ADDM Views:

DBA_ADVISOR_FINDINGS

DBA_ADDM_FINDINGS

DBA_ADVISOR_FINDING_NAMES
DBA_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_TASKS

Oracle 11g AWR (Automatic Workload Repository)

Oracle has provided many performance monitoring tools over the years. They started off with UTLBSTAT/UTLESTAT scripts followed by Statspack. Starting from Oracle 10g Automatic Workload Repository (AWR) is the new comprehensive tool for statistics gather and reporting

Database Statistics using AWR:

Gathering database statistics like wait events, time model statistics, ASH and system/session stats is enabled by defult using a STATISTICS_LEVEL parameter:

SQL> show parameter statistics_level


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

the parameter should be set to either TYPICAL or ALL to enable statistics gathering by AWR. If the parameter is set to BASIC, it will disable many database features. AWR can still be executed when the parameter is set to BASIC using DBMS_WORKLOAD_REPOSITORY pacakge but the statistics gathered will not be complete

Snapshots:

Snapshots are sets of historical data for specific time periods that are used for performance comparisons by ADDM. See below mentioned link for ADDM details.
Oracle Database 11g Automatic Database Diagnostic Monitor (ADDM)


By default, Oracle Database automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 8 days. 

Creating Snapshots:

To  manually create a snapshots in order to capture statistics at times different than those of the automatically generated snapshots. Use the below script

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;

query the below SQL and go at the end to view the newly created snapshot

SQL> select * from DBA_HIST_SNAPSHOT

 Dropping Snapshots:

To view a list of the snapshot Ids along with database Ids, check the DBA_HIST_SNAPSHOT view

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 11024, 
                           high_snap_id => 11036, dbid => 1544388080);
END;

Baselines (Fixed):

A baseline contains performance data from a specific time period that is preserved for comparison with other similar workload periods when performance problems occur. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely.

Creating a Baseline:

Based on the snapshots we created above, we'll create the baseline.

1. get the snap id to create the baseline on using DBA_HIST_SNAPSHOT

2. Create the base line as below 

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 11022, 
                   end_snap_id => 11023, baseline_name => 'peak baseline', 
                   dbid => 1544388080);
END;

view the baseline create using the script below

SQL> select dbid,baseline_id,baseline_name from DBA_HIST_BASELINE;

      DBID BASELINE_ID BASELINE_NAME

---------- ----------- ----------------------------------------------------------------

1544388080           1 peak baseline

1544388080           0 SYSTEM_MOVING_WINDOW

Renaming a Baseline:

To rename a baseline:

1. Review the existing baselines in the DBA_HIST_BASELINE view as mentioned above to determine the baseline that you want to rename.

2. Use the scripte below the rename the baseline

BEGIN 

 DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE ( old_baseline_name => 'peak baseline', new_baseline_name => 'off peak baseline', dbid => 1544388080 );

 END;

Dropping the Baseline:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'off peak baseline',
                  cascade => FALSE, dbid => 1544388080);
END;

Moving Window Baseline:

Oracle 11g introduces the concept of a moving window baseline, which is used to calculate metrics for the adaptive thresholds. The window is a view of the AWR data within the retention period.

Viewing AWR Retention Period:

SQL> SELECT retention FROM dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00008 00:00:00.0

 The default size of the window matches the default AWR retention period of 8 days, but it can be set as a subset of this value. Before you can increase the size of the window you must first increase the size of the AWR retention period.

Modifying the AWR retention period:

BEGIN
    DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (
                   window_size => 30, 
                   dbid => 1544388080);
END;

Baseline Templates:

Baseline Templates are used for creating the baselines any time in the future. Baseline Templates can be single baseline and repeating baseline.

Single baseline:

Single baseline is created when the AWR data is to be collected at any time in the future e.g. you are testing a system over a week-end and want to capture the statistic data during the testing.

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
                   start_time => TO_DATE('12-MAY-12 00:00', 'DD-MON-YY HH24:MI'), 
                   end_time => TO_DATE('12-MAY-12 03:00', 'DD-MON-YY HH24:MI'), 
                   baseline_name => 'baseline_120518', 
                   template_name => 'template_120818', expiration => 30, 
                   dbid => 1544388080);
END;

Repeating baseline:

A repeating baseline template can be used to create and drop baselines based on a repeating time schedule. For example, you may want to capture the AWR data during every Monday morning for a month. In this case, you can create a repeating baseline template to automatically create baselines on a repeating schedule for every Monday, and automatically remove older baselines after a specified expiration interval such as one month.

BEGIN

    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
    day_of_week => 'monday', hour_in_day => 17,
     duration => 3, expiration => 30,
     start_time => TO_DATE('13-MAY-12 00:00', 'DD-MON-YY HH24:MI'), 
     end_time => TO_DATE('13-MAY-12 03:00', 'DD-MON-YY HH24:MI'), 
      baseline_name_prefix => 'baseline_2012_mondays_', 
      template_name => 'template_2012_mondays',
      dbid => 1544388080);
END;

The START_TIME and END_TIME parameters define when the template is activated and deactivated. The DAY_OF_WEEK, HOUR_IN_DAY and DURATION parameters define the day (MONDAY - SUNDAY or ALL) the baselines are generated on and the start and end point of the baseline.

Viewing Baseline templates:

SQL> select template_name, template_type,expiration  from DBA_HIST_BASELINE_TEMPLATE;

TEMPLATE_NAME                  TEMPLATE_ EXPIRATION
------------------------------ --------- ----------
template_120818                SINGLE            30
template_2012_mondays          REPEATING         30

Additional Links:

BASH-DBA: AWR related data dictionary views
BASH-DBA: How to read AWR reports
BASH-DBA: Modifying AWR Statistics Interval and Retention Periods
Finding Problematic SQL and Top waits from past using AWR tables
BASH-DBA: Manually Getting Active Session Information
Oracle Database 11g Automatic Database Diagnostic Monitor (ADDM)

http://docs.oracle.com/cd/B28359_01/server.111/b28274/autostat.htm#CHDHJADB

Oracle 11g Automated Maintenance Tasks

Automated maintenance tasks are tasks that are started automatically at regular intervals to perform maintenance operations on the database. These tasks run automatically by the database and are executed during the "maintenance window" which is a contiguous time interval during which automated maintenance tasks are run under.


Automated Tasks:


In oracle Database 10g, we had only the first two automatic maintenance tasks mentioned below. With Oracle 11g, we have a third task name Automatic SQL Tunning Advisor

• Automatic Optimizer Statistics Collection

Collects optimizer statistics for all schema objects in the database for which there are no statistics or only stale statistics. The statistics gathered by this task are used by the SQL query optimizer to improve the performance of SQL execution.

• Automatic Segment Advisor

Identifies segments that have space available for reclamation, and makes recommendations on how to de-fragment those segments.

• Automatic SQL Tuning Advisor

Examines the performance of high-load SQL statements, and makes recommendations on how to tune those statements. 

To view the task, run the below SQL

SQL> select client_name from DBA_AUTOTASK_CLIENT;


CLIENT_NAME
----------------------------------------------------------------
auto optimizer stats collection
auto space advisor
sql tuning advisor

How to configure (disable/enable) the Automated Maintenance Tasks

DBMS_AUTO_TASK_ADMIN PL/SQL package can be used to do the following.


To enable the task

BEGIN

dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;

To enable it

BEGIN
dbms_auto_task_admin.enable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;

Use the following query to view the possible values for client_name 

SQL> select client_name from dba_autotask_client;

CLIENT_NAME
----------------------------------------------------------------
auto optimizer stats collection
auto space advisor
sql tuning advisor

By default, all the maintenance tasks run in all pre-defined windows. However, if you want to enable or disable the task for a "specific window", use the below script

BEGIN
dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor', 
    operation   => NULL, 
    window_name => 'MONDAY_WINDOW');
END;

Use the following query to view the window_name possible values mentioned above 

SQL> select window_name from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME
------------------------------
MONDAY_WINDOW
TUESDAY_WINDOW
WEDNESDAY_WINDOW
THURSDAY_WINDOW
FRIDAY_WINDOW
SATURDAY_WINDOW
SUNDAY_WINDOW

By default, all the maintenance tasks run in all pre-defined windows 

Creating the new Maintenance Window


To create a maintenance window, you must create a Scheduler window and then add it to the window group MAINTENANCE_WINDOW_GROUP


The DBMS_SCHEDULER PL/SQL package provides the ADD_WINDOW_GROUP_MEMBER subprogram, which adds a window to a window group



BEGIN
dbms_scheduler.create_window(
    window_name     => 'TEST_WINDOW',
    duration        =>  numtodsinterval(1, 'hour'),
    resource_plan   => 'DEFAULT_MAINTENANCE_PLAN',
    repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0');
    dbms_scheduler.add_window_group_member(
    group_name  => 'MAINTENANCE_WINDOW_GROUP',
    window_list => 'TEST_WINDOW');
END;

SQL> select window_name,window_next_time from DBA_AUTOTASK_WINDOW_CLIENTS;


WINDOW_NAME                    WINDOW_NEXT_TIME
------------------------------ ---------------------------------------------------------------------------
MONDAY_WINDOW                  14-MAY-12 10.00.00.000000 PM EST5EDT
TUESDAY_WINDOW                 08-MAY-12 10.00.00.000000 PM EST5EDT
WEDNESDAY_WINDOW               09-MAY-12 10.00.00.000000 PM EST5EDT
THURSDAY_WINDOW                10-MAY-12 10.00.00.000000 PM EST5EDT
FRIDAY_WINDOW                  11-MAY-12 10.00.00.000000 PM EST5EDT
SATURDAY_WINDOW                12-MAY-12 06.00.00.000000 AM EST5EDT
SUNDAY_WINDOW                  13-MAY-12 06.00.00.000000 AM EST5EDT
TEST_WINDOW                    09-MAY-12 05.00.00.000000 AM EST5EDT

Removing the Maintenance Window:




BEGIN
DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER(
    group_name  => 'MAINTENANCE_WINDOW_GROUP',
    window_list => 'TEST_WINDOW');
END;

Data Dictionary Views:

DBA_AUTOTASK_CLIENT_JOB
DBA_AUTOTASK_CLIENT
DBA_AUTOTASK_JOB_HISTORY
DBA_AUTOTASK_WINDOW_CLIENTS
DBA_AUTOTASK_CLIENT_HISTORY




source: Oracle Documentation