1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Baselines and Adaptive Thresholds -- 11g

Discussion in 'General' started by zargon, Nov 19, 2012.

  1. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    AWR basaelines have been available since Oracle 10g; Oracle 11g enhances those baselines with new ways to generate them and new tricks in OEM to use adaptive thresholds to monitor the performance of the database. Starting off let's look at the way generating baselines has changed. The original 10g implementation still exists, with a new wrinkle of using date/time values to retrieve the snap ids:

    <pre>
    SQL>
    SQL> -- Create a static(fixed) baseline using the 10g and enhanced 11g methods
    SQL>
    SQL> SET SERVEROUTPUT ON
    SQL> DECLARE
    2 l_return NUMBER;
    3 BEGIN
    4 -- Using procedures.
    5 DBMS_WORKLOAD_REPOSITORY.create_baseline(
    6 start_snap_id => 5000,
    7 end_snap_id => 5001,
    8 baseline_name => 'test1_bl',
    9 expiration => 60);
    10
    11 DBMS_WORKLOAD_REPOSITORY.create_baseline(
    12 start_time => TO_DATE('23-APR-2012 17:00', 'DD-MON-YYYY HH24:MI'),
    13 end_time => TO_DATE('23-APR-2012 18:00', 'DD-MON-YYYY HH24:MI'),
    14 baseline_name => 'test2_bl',
    15 expiration => NULL);
    16
    17 -- Using functions.
    18 l_return := DBMS_WORKLOAD_REPOSITORY.create_baseline(
    19 start_snap_id => 5000,
    20 end_snap_id => 5001,
    21 baseline_name => 'test3_bl',
    22 expiration => 30);
    23 DBMS_OUTPUT.put_line('Return: ' || l_return);
    24
    25 l_return := DBMS_WORKLOAD_REPOSITORY.create_baseline(
    26 start_time => TO_DATE('23-APR-2012 17:00', 'DD-MON-YYYY HH24:MI'),
    27 end_time => TO_DATE('23-APR-2012 18:00', 'DD-MON-YYYY HH24:MI'),
    28 baseline_name => 'test4_bl',
    29 expiration => NULL);
    30
    31 DBMS_OUTPUT.put_line('Return: ' || l_return);
    32 END;
    33 /
    Return: 31
    Return: 32
    PL/SQL procedure successfully completed.
    SQL>
    </pre>

    Also available in 11g is the ability to create baseline templates:

    <pre>
    SQL>
    SQL> -- Create a baseline template
    SQL>
    SQL> BEGIN
    2 DBMS_WORKLOAD_REPOSITORY.create_baseline_template(
    3 start_time => trunc(sysdate),
    4 end_time => sysdate,
    5 baseline_name => '23_apr_012_00_05_bl',
    6 template_name => '23_apr_012_00_05_tp',
    7 expiration => 100);
    8 END;
    9 /
    PL/SQL procedure successfully completed.
    SQL>
    SQL> -- Create a repeating baseline template
    SQL>
    SQL> BEGIN
    2 DBMS_WORKLOAD_REPOSITORY.create_baseline_template(
    3 day_of_week => 'MONDAY',
    4 hour_in_day => 0,
    5 duration => 5,
    6 start_time => SYSDATE,
    7 end_time => ADD_MONTHS(SYSDATE, 6),
    8 baseline_name_prefix => 'monday_morning_bl_',
    9 template_name => 'monday_morning_tp',
    10 expiration => NULL);
    11 END;
    12 /
    PL/SQL procedure successfully completed.
    SQL>
    </pre>

    Creating such templates allows the DBA to prepare to capture baselines for future time periods. Repeating baseline templates also require some 'scheduling' information (the day of the week to start the baseline, the hour at which to start, the duration of the baseline window) as well as the overall starting time and ending time for the baseline series.
    Viewing the defined baselines is a simple task using the DBA_HIST_BASELINE view:

    <pre>
    SQL>
    SQL> -- View created baselines
    SQL>
    SQL> COLUMN baseline_name FORMAT A15
    SQL>
    SQL> SELECT baseline_id, baseline_name, START_SNAP_ID,
    2 TO_CHAR(start_snap_time, 'DD-MON-YYYY HH24:MI') AS start_snap_time,
    3 END_SNAP_ID,
    4 TO_CHAR(end_snap_time, 'DD-MON-YYYY HH24:MI') AS end_snap_time
    5 FROM dba_hist_baseline
    6 WHERE baseline_type = 'STATIC'
    7 ORDER BY baseline_id;
    BASELINE_ID BASELINE_NAME START_SNAP_ID START_SNAP_TIME END_SNAP_ID END_SNAP_TIME
    ----------- --------------- ------------- -------------------------- ----------- --------------------------
    29 test1_bl 5000 20-APR-2012 01:45 5001 20-APR-2012 02:00
    30 test2_bl 5349 23-APR-2012 17:00 5353 23-APR-2012 18:00
    31 test3_bl 5000 20-APR-2012 01:45 5001 20-APR-2012 02:00
    32 test4_bl 5349 23-APR-2012 17:00 5353 23-APR-2012 18:00
    SQL>
    </pre>

    The real power of the enhancements is the definition of a moving window, allowing Oracle to automatically adjust metrics for the defined window duration and retention period. These are defined using two procedures in the DBMS_WORKLOAD_REPOSITORY package -- MODIFY_SNAPSHOT_SETTINGS and MODIFY_BASELINE_WINDOW_SIZE:

    <pre>
    SQL>
    SQL> -- Get the current retention and window size
    SQL>
    SQL> SELECT retention FROM dba_hist_wr_control;
    RETENTION
    ---------------------------------------------------------------------------
    +00008 00:00:00.0
    SQL>
    SQL> SELECT moving_window_size
    2 FROM dba_hist_baseline
    3 WHERE baseline_type = 'MOVING_WINDOW';
    MOVING_WINDOW_SIZE
    ------------------
    8
    SQL>
    SQL> -- Set the retention and window size to 30 days
    SQL>
    SQL> BEGIN
    2 DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    3 retention => 43200); -- Minutes (= 30 Days).
    4 END;
    5 /
    PL/SQL procedure successfully completed.
    SQL>
    SQL> BEGIN
    2 DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(
    3 window_size => 30);
    4 END;
    5 /
    PL/SQL procedure successfully completed.
    SQL>
    SQL> -- Verify settings are changed
    SQL>
    SQL> SELECT retention FROM dba_hist_wr_control;
    RETENTION
    ---------------------------------------------------------------------------
    +00030 00:00:00.0
    SQL>
    SQL> SELECT moving_window_size
    2 FROM dba_hist_baseline
    3 WHERE baseline_type = 'MOVING_WINDOW';
    MOVING_WINDOW_SIZE
    ------------------
    30
    SQL>
    </pre>

    Oracle recommends a window size of at least 30 when configuring adaptive thresholds, which we set in the example shown above. The moving window metrics can be viewed using the pipelined DBMS_WORKLOAD_REPOSITORY function SELECT_BASELINE_METRIC with the supplied value of SYSTEM_MOVING_WINDOW, as shown below:

    <pre>
    SQL>
    SQL> SELECT *
    2 FROM TABLE(DBMS_WORKLOAD_REPOSITORY.select_baseline_metric('SYSTEM_MOVING_WINDOW'));
    BASELINE_NAME DBID INSTANCE_NUMBER BEG_TIME END_TIME
    --------------- ---------- --------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
    METRIC_NAME METRIC_UNIT NUM_INTERVAL INTERVAL_SIZE AVERAGE MINIMUM MAXIMUM
    ---------------------------------------------------------------- ---------------------------------------------------------------- ------------ ------------- ---------- ---------- ----------
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Writes Direct Per Sec Writes Per Second 12414 108064 .059240174 0 75.982024
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    User Rollbacks Percentage % (UserRollback/TotalUserTxn) 12414 108064 .000671285 0 8.33333333
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Recursive Calls Per Sec Calls Per Second 12414 108064 12.7557887 0 1461.5048
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Logical Reads Per Txn Reads Per Txn 12414 108064 240.53724 0 99586.3333
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    DBWR Checkpoints Per Sec Check Points Per Second 12414 108064 .00034437 0 .773153479
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Long Table Scans Per Txn Scans Per Txn 12414 108064 .006696865 0 20
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Total Table Scans Per Sec Scans Per Second 12414 108064 .251030756 0 158.649149
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Total Parse Count Per Sec Parses Per Second 12414 108064 .524208187 0 67.983925
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Hard Parse Count Per Txn Parses Per Txn 12414 108064 .177616064 0 248
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    User Calls Ratio % UserCalls/AllCalls 12414 108064 .380478697 0 44.6735395
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Enqueue Deadlocks Per Txn Deadlocks Per Txn 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    DB Block Gets Per Sec Blocks Per Second 12414 108064 11.5311522 0 8813.11071
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    DB Block Changes Per Sec Blocks Per Second 12414 108064 11.2496831 0 9201.67791
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    DB Block Changes Per Txn Blocks Per Txn 12414 108064 70.0493331 0 91735.3333
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    CPU Usage Per Sec CentiSeconds Per Second 12414 108064 .284175993 0 89.4022655
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    CR Undo Records Applied Per Txn Records Per Txn 12414 108064 .033059873 0 10.5384615
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    User Rollback UndoRec Applied Per Sec Records Per Second 12414 108064 .000699413 0 .978928157
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Branch Node Splits Per Sec Splits Per Second 12414 108064 .000139576 0 .033189512
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Branch Node Splits Per Txn Splits Per Txn 12414 108064 .000923118 0 1
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Read Total Bytes Per Sec Bytes Per Second 12414 108064 44570.4048 0 6335347.48
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Global Cache Blocks Corrupted Blocks 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Database CPU Time Ratio % Cpu/DB_Time 12414 108064 16.3211451 0 1515.51891
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Row Cache Miss Ratio % Misses/Gets 12414 108064 .160004885 0 33.9179518
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Process Limit % % Processes/Limit 12414 108064 17.7211213 0 24
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Write Total Bytes Per Sec Bytes Per Second 12414 108064 23779.8617 0 1301367.72
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Write IO Requests Per Sec Requests Per Second 12414 108064 .494292232 0 45.5897093
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Write Bytes Per Sec Bytes Per Second 12414 108064 8558.21676 0 1162700.17
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Session Count Sessions 12414 108064 31.6833414 0 46
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Background Time Per Sec Active Sessions 12414 108064 .028510473 0 1.34740371
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Writes Per Txn Writes Per Txn 12414 108064 52.4997763 0 7306
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Reads Direct Per Txn Reads Per Txn 12414 108064 .311276782 0 2196
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    User Commits Per Sec Commits Per Second 12414 108064 .024006574 0 2.97936085
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Enqueue Waits Per Txn Waits Per Txn 12414 108064 .007339779 0 78
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Enqueue Requests Per Txn Requests Per Txn 12414 108064 403.577146 0 1463
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    DB Block Gets Per Txn Blocks Per Txn 12414 108064 70.0203256 0 92924.3333
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    User Rollback Undo Records Applied Per Txn Records Per Txn 12414 108064 .002867363 0 2.56521739
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    PX downgraded 25 to 50% Per Sec PX Operations Per Second 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Response Time Per Txn CentiSeconds Per Txn 12414 108064 5.63276768 0 5937.1627
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    PGA Cache Hit % % Bytes/TotalBytes 12414 108064 98.2106734 0 100
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Executions Per Sec Executes Per Second 12414 108064 2.36112562 0 565.813836
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Background CPU Usage Per Sec CentiSeconds Per Second 12414 108064 .312897023 0 43.9291335
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Total PGA Allocated bytes 12414 108064 29908477.5 0 94219264
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Writes Per Sec Writes Per Second 12414 108064 1.0447042 0 141.931173
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Redo Generated Per Sec Bytes Per Second 12414 108064 3850.56697 0 1193076.95
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Redo Generated Per Txn Bytes Per Txn 12414 108064 26554.3456 0 11664381.3
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Recursive Calls Per Txn Calls Per Txn 12414 108064 412.332461 0 14126
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Logical Reads Per Sec Reads Per Second 12414 108064 36.0629895 0 10971.1041
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Redo Writes Per Sec Writes Per Second 12414 108064 .14293493 0 13.5234284
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Total Index Scans Per Sec Scans Per Second 12414 108064 3.76348565 0 2714.31412
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Total Index Scans Per Txn Scans Per Txn 12414 108064 42.3459951 0 14894.1818
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Parse Failure Count Per Txn Parses Per Txn 12414 108064 .002764398 0 3
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Host CPU Utilization (%) % Busy/(Idle+Busy) 12414 108064 13.4775267 0 49.2092823
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Enqueue Waits Per Sec Waits Per Second 12414 108064 .00055054 0 1.51742538
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Read Total IO Requests Per Sec Requests Per Second 12414 108064 2.63465845 0 438.516667
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Global Cache Average CR Get Time CentiSeconds Per Get 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Write Total IO Requests Per Sec Requests Per Second 12414 108064 1.30694661 0 47.3772135
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Current Logons Count Logons 12414 108064 23.5786209 0 33
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Row Cache Hit Ratio % Hits/Gets 12414 108064 99.8399951 0 100
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    User Limit % % Sessions/License_Limit 12414 108064 5.4898E-07 0 7.6834E-07
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Txns Per Logon Txns Per Logon 12414 108064 .62950524 0 100
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Read Bytes Per Sec Bytes Per Second 12414 108064 11815.3565 0 6307963.23
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    DB Block Gets Per User Call Blocks Per Call 12414 108064 146.908515 0 178583
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Current OS Load Number Of Processes 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    PQ QC Session Count Sessions 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    DML statements parallelized Per Sec Statements Per Second 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    I/O Requests per Second Requests per Second 12414 108064 3.93093068 0 442.166667
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Replayed user calls calls 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Logons Per Sec Logons Per Second 12414 108064 .014771839 0 .302979296
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    User Rollbacks Per Sec Rollbacks Per Second 12414 108064 1.3415E-06 0 .016652789
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Disk Sort Per Sec Sorts Per Second 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Soft Parse Ratio % SoftParses/TotalParses 12414 108064 51.5186554 0 100
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    CR Blocks Created Per Sec Blocks Per Second 12414 108064 .00359321 0 3.43376536
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Leaf Node Splits Per Txn Splits Per Txn 12414 108064 .361051482 0 48
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Database Wait Time Ratio % Wait/DB_Time 12414 108064 9.54733766 0 100
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Library Cache Miss Ratio % Misses/Gets 12414 108064 .347510682 0 20.5882353
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Session Limit % % Sessions/Limit 12414 108064 12.7755409 0 18.5483871
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Executions Per Txn Executes Per Txn 12414 108064 40.6061532 0 6436
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    PX operations not downgraded Per Sec PX Operations Per Second 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Buffer Cache Hit Ratio % (LogRead - PhyRead)/LogRead 12414 108064 99.7850699 0 100
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Reads Per Sec Reads Per Second 12414 108064 1.44230416 0 769.998329
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Reads Direct Lobs Per Sec Reads Per Second 12414 108064 .000429011 0 2.37094333
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Open Cursors Per Txn Cursors Per Txn 12414 108064 21.6667378 0 6400
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    User Calls Per Sec Calls Per Second 12414 108064 .048269677 0 7.31023102
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    User Calls Per Txn Calls Per Txn 12414 108064 1.56810643 0 443
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Total Table Scans Per Txn Scans Per Txn 12414 108064 1.68909139 0 1212
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Parse Failure Count Per Sec Parses Per Second 12414 108064 .00188185 0 1.77494977
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Enqueue Timeouts Per Txn Timeouts Per Txn 12414 108064 .000291952 0 3
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Consistent Read Gets Per Txn Blocks Per Txn 12414 108064 170.517158 0 92826
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    CR Undo Records Applied Per Sec Undo Records Per Second 12414 108064 .005277994 0 23.0600909
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    PX downgraded 1 to 25% Per Sec PX Operations Per Second 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    GC CR Block Received Per Txn Blocks Per Txn 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    GC Current Block Received Per Second Blocks Per Second 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    GC Current Block Received Per Txn Blocks Per Txn 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Current Open Cursors Count Cursors 12414 108064 31.0540519 0 74
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Read IO Requests Per Sec Requests Per Second 12414 108064 .6323203 0 435.833333
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    PQ Slave Session Count Sessions 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    DDL statements parallelized Per Sec Statements Per Second 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Active Parallel Sessions Sessions 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Redo Allocation Hit Ratio % (#Redo - RedoSpaceReq)/#Redo 12414 108064 99.9998408 0 100
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Reads Per Txn Reads Per Txn 12414 108064 16.4781525 0 46069
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Reads Direct Per Sec Reads Per Second 12414 108064 .01910471 0 36.7408399
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Writes Direct Per Txn Writes Per Txn 12414 108064 .079662619 0 78.4117647
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Open Cursors Per Sec Cursors Per Second 12414 108064 1.95703643 0 546.911294
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Background Checkpoints Per Sec Check Points Per Second 12414 108064 .000093978 0 .033411293
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Total Parse Count Per Txn Parses Per Txn 12414 108064 8.36283032 0 371
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Cursor Cache Hit Ratio % CursorCacheHit/SoftParse 12414 108064 183.220909 0 34023.2877
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Disk Sort Per Txn Sorts Per Txn 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Enqueue Requests Per Sec Requests Per Second 12414 108064 8.34372951 0 100.083375
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    CPU Usage Per Txn CentiSeconds Per Txn 12414 108064 1.93508742 0 4104.6875
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    PX downgraded 50 to 75% Per Sec PX Operations Per Second 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    GC CR Block Received Per Second Blocks Per Second 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Shared Pool Free % % Free/Total 12414 108064 13.6728249 0 76.2217607
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Executions Per User Call Executes Per Call 12414 108064 41.2719073 0 12911
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Logical Reads Per User Call Reads Per Call 12414 108064 436.477937 0 226708
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Total Sorts Per User Call Sorts Per Call 12414 108064 15.6212676 0 2405
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Average Synchronous Single-Block Read Latency Milliseconds 12414 108064 4.43388865 0 60.1
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    I/O Megabytes per Second Megabtyes per Second 12414 108064 .065183815 0 6.11733244
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Active Serial Sessions Sessions 12414 108064 1.03310778 0 6
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Captured user calls calls 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Writes Direct Lobs Per Sec Writes Per Second 12414 108064 .000372805 0 .806858296
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Logons Per Txn Logons Per Txn 12414 108064 .449300343 0 7
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Full Index Scans Per Txn Scans Per Txn 12414 108064 .006215481 0 8.73684211
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Hard Parse Count Per Sec Parses Per Second 12414 108064 .031503073 0 18.6369725
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Execute Without Parse Ratio % (ExecWOParse/TotalExec) 12414 108064 88.2675968 0 100
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Network Traffic Volume Per Sec Bytes Per Second 12414 108064 1.16863167 0 1873.43234
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Enqueue Timeouts Per Sec Timeouts Per Second 12414 108064 .000060679 0 .485599464
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Enqueue Deadlocks Per Sec Deadlocks Per Second 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Consistent Read Changes Per Txn Blocks Per Txn 12414 108064 .22227636 0 486.363636
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    PX downgraded to serial Per Sec PX Operations Per Second 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Global Cache Average Current Get Time CentiSeconds Per Get 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Global Cache Blocks Lost Blocks 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    SQL Service Response Time CentiSeconds Per Call 12414 108064 .009604131 0 9.02682266
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    DB Block Changes Per User Call Blocks Per Call 12414 108064 147.558791 0 186456.667
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Streams Pool Usage Percentage % Memory allocated / Size of Streams pool 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Queries parallelized Per Sec Queries Per Second 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Workload Capture and Replay status status 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Temp Space Used bytes 12414 108064 17034925.5 0 29360128
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Total PGA Used by SQL Workareas bytes 12414 108064 2191.03335 0 3530752
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Memory Sorts Ratio % MemSort/(MemSort + DiskSort) 12414 108064 100 0 100
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    User Transaction Per Sec Transactions Per Second 12414 108064 .024007916 0 2.97936085
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Reads Direct Lobs Per Txn Reads Per Txn 12414 108064 .010398373 0 14.1
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Physical Writes Direct Lobs Per Txn Writes Per Txn 12414 108064 .004131925 0 12
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    User Commits Percentage % (UserCommit/TotalUserTxn) 12414 108064 19.5015037 0 100
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Redo Writes Per Txn Writes Per Txn 12414 108064 4.39188284 0 323
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Long Table Scans Per Sec Scans Per Second 12414 108064 .001166133 0 .631964078
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Full Index Scans Per Sec Scans Per Second 12414 108064 .007050405 0 5.99464166
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Rows Per Sort Rows Per Sort 12414 108064 25.1654053 0 11061.9241
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Consistent Read Gets Per Sec Blocks Per Second 12414 108064 24.5318372 0 10853.2153
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Consistent Read Changes Per Sec Blocks Per Second 12414 108064 .095397042 0 88.6348575
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    CR Blocks Created Per Txn Blocks Per Txn 12414 108064 .037030372 0 9
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Leaf Node Splits Per Sec Splits Per Second 12414 108064 .059013446 0 2.05490989
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    PX downgraded 75 to 99% Per Sec PX Operations Per Second 12414 108064 0 0 0
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Library Cache Hit Ratio % Hits/Pins 12414 108064 99.6528115 0 104
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Database Time Per Sec CentiSeconds Per Second 12414 108064 1.0375165 0 686.426462
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Total Table Scans Per User Call Tables Per Call 12414 108064 2.03286603 0 860
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Average Active Sessions Active Sessions 12414 108064 .010375165 0 6.86426462
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Host CPU Usage Per Sec CentiSeconds Per Second 12414 108064 50.0894046 0 191.712707
    SYSTEM_MOVING_W 1546135824 1 18-APR-12 11.59.21.000 PM 27-APR-12 03.00.02.000 PM
    Cell Physical IO Interconnect Bytes bytes 12414 108064 4100829.15 0 384118784

    158 rows selected.
    SQL>
    </pre>

    Using the above query the window can be monitored with a script, if necessary, although the preferred method is to set adaptive thresholds in OEM and let Enterprise Manager do the work. In 11g there are three types of thresholds: fixed value, such as 80% of CPU consumed, percent of maximum (which is recalculated as the window moves) and significance level. The third is probably the least documented so let's add to the pool of information and try to explain it here.

    The basic idea behind using significance level thresholds for alerting is that Oracle is attempting to detect outliers (significant variations) in the distribution of metric values. Using the historical metric data from AWR Oracle can identify values for 25th, 50th, 75th, 90th, 95th and 99th percentiles; a curve-fitting algorithm is employed to extrapolate the 99.9th and 99.99th percentiles based on time values (hence the moving window). This is where dynamic baselines (as I call them) come into play. A dynamic baseline is generated by the moving window since the base for that window shifts over time and alters the metric averages.

    The user will specify the alert level, which equates to one of these percentile values:

    High 95th percentile
    Very High 99th percentile
    Severe 99.9th percentile
    Extreme 99.99th percentile

    Using the SYSTEM_MOVING_WINDOW baseline Oracle will automatically determine the threshold level for a metric corresponding to the selected significance level for the current period. If you set a significance level of Extreme Oracle would generate an alert on values that would only be expected to be seen once in every 10000 observations (approximately once a year for hourly thresholds).

    Significance level thresholds are designed to produce alert values for key metrics; the values that are observed above the threshold are unusual with respect to the statistics (i.e. significant) at the Nth percentile based on actual data observed for this metric over the SYSTEM_MOVING_WINDOW baseline. The alert level sets N according to the 'severity' chosen (listed above). Systems with relatively stable performance should show stability, statistically speaking, in core performance metrics and when rare, high-impact events occur these will be reflected in highly unusual observations. Significance level thresholds allow the DBA to specify how alerts are generated in terms of "how unusual" the deviations are.

    Once set the thresholds only change when the DBA chooses to change them; setting such thresholds require a knowledge of the metrics, the data and the application which accesses that data and an awareness of the normal workload. Of course expectations and thresholds vary with the application so an intimate knowledge of these values is essential in knowing how to set the significance. As installations vary with time (data skew changes, activity increases, sudden bursts of activity) andn such changes can affect the metrics and, as a result, the thresholds so careful monitoring of these changes is necessary if the alerts are to be useful and meaningful. These areas are the ones adap[tive thresholds attempt to address; the methodology is as follows:

    A) Thresholds are computed by the system based upon prior observations of this metric. Basically speaking Oracle tries to take the guesswork (and manual analysis) out of the picture by automatically recomputing metric values over time. Since the triggering values do not change without manual intervention the greater frequencey of alerts is a good sign the usage of the system has changed, alerting the DBA to consider new settings that will again fall outside of the current normal range.
    B) Thresholds are periodically recalculated with respect to the SYSTEM_MOVING_WINDOW baseline. The thresholds adapt to slowly evolving workload or demand.
    C) Metric statistics for adaptive thresholds are computed using grouping "buckets" over the common workload periods (day/night, weekday/weekend, etc.) Threshold resets can happen as frequently as every hour depending on the load and demand.

    In a nutshell Oracle 11g determines, and sets, metric alert thresholds automatically based upon actual observations of the system relative to the triggering thresholds set by the DBA. With a good knowledge of the system the DBA can set reasonable levels by which to generate alerts.

    Whether the DBA uses a fixed value methodology, a percent of maximum tact or chooses to set a significance level Oracle's adaptive thresholds can make maintaining the performance of a database system easier by automatically recalculating the metric values based upon a moving window (which is like a moving average in a way). As the window shifts and demand increases the baseline also changes, giving a new reference point for the increased performance metrics keeping the overall picture consistent with the data, making it easier to determine where the performance bottlenecks and issues are.

    Adaptive thresholds are, in my mind, a very useful addition to 11g and well worth the time spent learning how to set and use them.