Oracle AWRINFO_UTIL
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose This package is built by running the script awrinfo.sql located at $ORACLE_HOME/rdbms/admin.

The script creates the package, runs the package code to produce the AWR Space Report, then drops the package. To make the package permanent delete or comment out the line at the end of the script that reads "drop package AWRINFO_UTIL;".
AUTHID DEFINER
Dependencies
DBMS_SPACE    
Documented No
First Available 10gR1
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/awrinfo.sql
Subprograms
CLASSIFY_COUNT GET_PERC_USAGE GET_TYPE
 
CLASSIFY_COUNT
Returns the classify count awrinfo_util.classify_count(cnt IN NUMBER) RETURN VARCHAR2;
SQL> SELECT awrinfo_util.classify_count(42)
  2 FROM dual;

AWRINFO_UTIL.CLASSIFY_COUNT(42)
---------------------------------------------
0040 - 0044

SQL> SELECT awrinfo_util.classify_count(100)
  2 FROM dual;

AWRINFO_UTIL.CLASSIFY_COUNT(100)
---------------------------------------------
0100 - 0104
 
GET_PERC_USAGE
Returns the percentage of AWR space utilized awrinfo_util.get_perc_usage(
segment_owner  IN VARCHAR2,
segment_name   IN VARCHAR2,
segment_type   IN VARCHAR2,
partition_name IN VARCHAR2)
RETURN NUMBER;
SELECT awrinfo_util.get_perc_usage('SYS', 'TAB$', 'TABLE', NULL)
FROM dual;
 
GET_TYPE
Returns the AWR table type awrinfo_util.get_type( segment_name IN VARCHAR2 ) RETURN VARCHAR2;
SQL> SELECT awrinfo_util.get_type('WRH$_ACTIVE_SESSION_HISTORY')
  2  FROM dual;

AWRINFO_UTIL.GET_TYPE('WRH$_ACTIVE_SESSION_HISTORY')
-----------------------------------------------------
ASH

SQL> SELECT awrinfo_util.get_type('WRI$_TOPN_METADATA')
  2 FROM dual;

AWRINFO_UTIL.GET_TYPE('WRI$_TOPN_METADATA')
------------------------------------------------------
FIXED
 
AWRINFO.SQL Report
Report produced by running the awrinfo.sql script generated using awrinfo.sql

~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~

Report generated at 07:28:47 on Nov 28, 2010 ( Sunday ) in Timezone -08:00

Warning: Non Default AWR Setting!
--------------------------------------------------------------------------------
Snapshot interval is 60 minutes and Retention is 8 days


       DB_ID DB_NAME   HOST_PLATFORM                             INST STARTUP_TIME      LAST_ASH_SID PAR
------------ --------- ---------------------------------------- ----- ----------------- ------------ ---
* 318094786 ORABASE2 PERRITO3 - Microsoft Windows IA (32-bit)       1 02:07:25 (11/28) 4527004       NO

########################################################
(I) AWR Snapshots Information
########################################################

*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size            598.9 MB ( 2% of 32,768.0 MB MAX with AUTOEXTEND ON )
|
| Schema  SYS occupies         250.7 MB ( 41.9% )
| Schema  XDB occupies         128.1 MB ( 21.4% )
| Schema  APEX_030200 occupies  77.1 MB ( 12.9% )
| Schema  SYSMAN occupies       59.1 MB ( 9.9% )
| Schema  MDSYS occupies        44.9 MB ( 7.5% )
| Schema  ORDDATA occupies      11.3 MB ( 1.9% )
| Schema  SYSTEM occupies        9.3 MB ( 1.6% )
| Schema  OLAPSYS occupies       7.3 MB ( 1.2% )
| Schema  EXFSYS occupies        3.6 MB ( 0.6% )
| Schema  CTXSYS occupies        3.5 MB ( 0.6% )
| Schema  WMSYS occupies         3.1 MB ( 0.5% )
| Schema  DBSNMP occupies        0.6 MB ( 0.1% )
| Schema  ORDSYS occupies        0.4 MB ( 0.1% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name        Schema Name          Space Usage
| -------------------- -------------------- -----------
| XDB                  XDB                     128.1 MB
| SM/AWR               SYS                      98.4 MB
| SM/OPTSTAT           SYS                     68.5 MB
| EM                   SYSMAN                     59.1 MB
| SDO                  MDSYS                     44.9 MB
| AO                   SYS                     35.7 MB
| XSOQHIST             SYS                     7 MB
| SM/ADVISOR           SYS                     17.5 MB
| ORDIM/ORDDATA        ORDDATA                 11.3 MB
| LOGMNR               SYSTEM                   7.9 MB
| XSAMD                OLAPSYS                  7.3 MB
| SM/OTHER             SYS                      6.0 MB
| EXPRESSION_FILTER    EXFSYS                   3.6 MB
| TEXT                 CTXSYS                   3.5 MB
| SMON_SCN_TIME        SYS                      3.3 MB
| WM                   WMSYS                    3.1 MB
| SQL_MANAGEMENT_BASE  SYS                      1.7 MB
| PL/SCOPE             SYS                      1.6 MB
| JOB_SCHEDULER        SYS                      1.5 MB
| LOGSTDBY             SYSTEM                   1.4 MB
| STREAMS              SYS                      1.0 MB
| EM_MONITORING_USER   DBSNMP                   0.6 MB
| ORDIM                ORDSYS                   0.4 MB
| AUTO_TASK            SYS                      0.3 MB
| AUDIT_TABLES         SYS                      0.0 MB
| ORDIM/ORDPLUGINS     ORDPLUGINS               0.0 MB
| ORDIM/SI_INFORMTN_SC SI_INFORMTN_SCHEMA       0.0 MB
| STATSPACK            PERFSTAT                 0.0 MB
| TSM                  TSMSYS                   0.0 MB
| ULTRASEARCH          WKSYS                    0.0 MB
| ULTRASEARCH_DEMO_USE WK_TEST                  0.0 MB
|
| Others (Unaccounted space)                   56.8 MB
|

******************************************
(1c) SYSAUX usage - Unregistered Schemas
******************************************

| This section displays schemas that are not registered
| in V$SYSAUX_OCCUPANTS
|
| Schema  APEX_030200  occupies             77.1 MB
|
| Total space                               77.1 MB
|
*************************************************************
(1d) SYSAUX usage - Unaccounted space in registered schemas
*************************************************************
|
| This section displays unaccounted space in the registered
| schemas of V$SYSAUX_OCCUPANTS.
|
| Unaccounted space in SYS/SYSTEM          -20.3 MB
|
| Total space                              -20.3 MB
|
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 60 mins snapshot INTERVAL:
| AWR size/day                              19.5 MB (833 K/snap * 24 snaps/day)
| AWR size/wk                              136.6 MB (size_per_day * 7) per instance
|
| Estimates based on 17 snaps in past 24 hours:
| AWR size/day                              13.8 MB (833 K/snap and 17 snaps in past 24 hours)
| AWR size/wk                               96.7 MB (size_per_day * 7) per instance
|

**********************************
(3a) Space usage by AWR components (per database)
**********************************

COMPONENT        MB  % AWR  KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%
--------- --------- ------ ------------ ---------- ----------- ----------------
FIXED          60.5   61.5          512        8.5        59.5    54% : 46%
EVENTS         10.0   10.2           85        1.4         9.8    51% : 49%
SQL             7.7    7.8           65        1.1         7.6    60% : 40%
SQLPLAN         7.0    7.1           59        1.0         6.9    71% : 29%
SPACE           6.3    6.4           53        0.9         6.2    59% : 41%
ASH             1.2    1.2           10        0.2         1.2    58% : 42%
SQLTEXT         0.7    0.7            6        0.1         0.7    91% : 9%
RAC             0.6    0.6            5        0.1         0.6    50% : 50%
SQLBIND         0.5    0.5            4        0.1         0.5    50% : 50%

**********************************
(3b) Space usage within AWR Components (> 500K)
**********************************

COMPONENT   MB SEGMENT_NAME - % SPACE_USED                              SEGMENT_TYPE
--------- ---- -------------------------------------------------------- ---------------
FIXED      9.0 WRH$_SYSMETRIC_HISTORY                             - 68% TABLE
FIXED      7.0 WRH$_SYSMETRIC_HISTORY_INDEX                       - 70% INDEX
FIXED      2.0 WRH$_SYSMETRIC_SUMMARY                             - 75% TABLE
FIXED      1.0 WRH$_LATCH.WRH$_LATCH_318094786_1411               - 90% TABLE PARTITION
FIXED      0.8 WRH$_SYSMETRIC_SUMMARY_INDEX                       - 76% INDEX
FIXED      0.8 WRH$_LATCH.WRH$_LATCH_318094786_1360               - 91% TABLE PARTITION
FIXED      0.8 WRH$_LATCH.WRH$_LATCH_318094786_1387               - 87% TABLE PARTITION
FIXED      0.8 WRH$_LATCH.WRH$_LATCH_318094786_1467               - 84% TABLE PARTITION
FIXED      0.8 WRH$_LATCH.WRH$_LATCH_318094786_1444               - 84% TABLE PARTITION
FIXED      0.7 WRH$_SYSSTAT_PK.WRH$_SYSSTA_318094786_1411         - 89% INDEX PARTITION
FIXED      0.6 WRH$_LATCH_PK.WRH$_LATCH_318094786_1411            - 90% INDEX PARTITION
FIXED      0.6 WRH$_SYSSTAT.WRH$_SYSSTA_318094786_1411            - 88% TABLE PARTITION
FIXED      0.6 WRH$_PARAMETER.WRH$_PARAME_318094786_1411          - 86% TABLE PARTITION
FIXED      0.6 WRH$_SYSSTAT_PK.WRH$_SYSSTA_318094786_1360         - 89% INDEX PARTITION
FIXED      0.5 WRH$_PARAMETER.WRH$_PARAME_318094786_1360          - 80% TABLE PARTITION
FIXED      0.5 WRH$_SYSSTAT.WRH$_SYSSTA_318094786_1360            - 90% TABLE PARTITION
FIXED      0.5 WRH$_SYSSTAT.WRH$_SYSSTA_318094786_1387            - 80% TABLE PARTITION
FIXED      0.5 WRH$_SYSSTAT_PK.WRH$_SYSSTA_318094786_1387         - 89% INDEX PARTITION
FIXED      0.5 WRH$_SYSSTAT_PK.WRH$_SYSSTA_318094786_1444         - 89% INDEX PARTITION
FIXED      0.5 WRH$_LATCH_PK.WRH$_LATCH_318094786_1387            - 89% INDEX PARTITION
FIXED      0.5 WRH$_SYSSTAT_PK.WRH$_SYSSTA_318094786_1467         - 89% INDEX PARTITION
FIXED      0.5 WRH$_LATCH.WRH$_LATCH_318094786_1490               - 87% TABLE PARTITION
FIXED      0.5 WRH$_LATCH_PK.WRH$_LATCH_318094786_1360            - 89% INDEX PARTITION
EVENTS     0.8 WRH$_ENQUEUE_STAT                                  - 66% TABLE
EVENTS     0.6 WRH$_ENQUEUE_STAT_PK                               - 69% INDEX
EVENTS     0.5 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__318094786_1387 - 89% INDEX PARTITION
EVENTS     0.5 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__318094786_1360 - 89% INDEX PARTITION
EVENTS     0.5 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__318094786_1411    - 82% TABLE PARTITION
EVENTS     0.5 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__318094786_1387    - 80% TABLE PARTITION
EVENTS     0.5 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__318094786_1411 - 90% INDEX PARTITION
SQL        0.9 WRH$_SQLSTAT.WRH$_SQLSTA_318094786_1411            - 90% TABLE PARTITION
SQL        0.8 WRH$_SQLSTAT.WRH$_SQLSTA_318094786_1360            - 85% TABLE PARTITION
SQL        0.8 WRH$_SQLSTAT.WRH$_SQLSTA_318094786_1387            - 90% TABLE PARTITION
SQL        0.6 WRH$_SQLSTAT.WRH$_SQLSTA_318094786_1467            - 88% TABLE PARTITION
SQL        0.6 WRH$_SQLSTAT.WRH$_SQLSTA_318094786_1444            - 87% TABLE PARTITION
SQL        0.5 WRH$_SQLSTAT.WRH$_SQLSTA_318094786_1490            - 81% TABLE PARTITION
SQLPLAN    5.0 WRH$_SQL_PLAN                                      - 52% TABLE
SQLPLAN    2.0 WRH$_SQL_PLAN_PK                                   - 45% INDEX
SQLTEXT    0.6 WRH$_SQLTEXT                                       - 69% TABLE

**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************

COMPONENT   MB SEGMENT_NAME                                 SEGMENT_TYPE
--------- ---- ------------------------------------------   ------------
NON_AWR   57.1 XDB.SYS_LOB0000056500C00025$$                LOBSEGMENT
NON_AWR   27.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST           INDEX
NON_AWR   18.0 MDSYS.SYS_LOB0000062563C00006$$              LOBSEGMENT
NON_AWR   17.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY             TABLE
NON_AWR   15.1 SYS.SYS_LOB0000064040C00004$$                LOBSEGMENT
NON_AWR   12.0 SYS.I_WRI$_OPTSTAT_H_ST                      INDEX
NON_AWR    7.1 SYS.SYS_LOB0000064025C00004$$                LOBSEGMENT
NON_AWR    7.0 MDSYS.SDO_CS_SRS                             TABLE
NON_AWR    6.0 XDB.XDB$RESOURCE                             TABLE
NON_AWR    5.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY            TABLE
NON_AWR    5.0 APEX_030200.WWV_FLOW_DICTIONARY$CAPED        INDEX
NON_AWR    5.0 APEX_030200.WWV_FLOW_PAGE_PLUGS              TABLE
NON_AWR    5.0 APEX_030200.WWV_FLOW_STEP_ITEMS              TABLE
NON_AWR    5.0 SYSMAN.MGMT_MESSAGES                         TABLE
NON_AWR    4.1 SYS.SYS_LOB0000064030C00004$$                LOBSEGMENT
NON_AWR    4.0 APEX_030200.WWV_FLOW_DICTIONARY$             TABLE
NON_AWR    3.2 SYS.SYS_LOB0000064020C00004$$                LOBSEGMENT
NON_AWR    3.0 SYS.WRI$_ADV_SQLT_PLANS                      TABLE
NON_AWR    3.0 SYS.SYS_LOB0000006213C00038$$                LOBSEGMENT
NON_AWR    3.0 APEX_030200.WWV_FLOW_DICTIONARY$SOUNDEX      INDEX
NON_AWR    3.0 APEX_030200.WWV_FLOW_DICTIONARY$WORDS        INDEX
NON_AWR    3.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST            INDEX
NON_AWR    3.0 SYS.SMON_SCN_TO_TIME_AUX                     CLUSTER
NON_AWR    3.0 XDB.XDB$H_INDEX                              TABLE
NON_AWR    2.0 SYS.WRI$_ADV_PARAMETERS                      TABLE
NON_AWR    2.0 APEX_030200.WWV_FLOW_DICTIONARY$_FKIDX       INDEX
NON_AWR    2.0 SYSMAN.MGMT_MESSAGES_PK                      INDEX
NON_AWR    2.0 SYS.WRI$_ADV_PARAMETERS_PK                   INDEX
NON_AWR    2.0 SYS.I_WRI$_OPTSTAT_HH_ST                     INDEX
NON_AWR    2.0 APEX_030200.WWV_FLOW_REGION_REPORT_COLUMN    TABLE
NON_AWR    2.0 APEX_030200.WWV_FLOW_STEP_PROCESSING         TABLE
NON_AWR    2.0 APEX_030200.WWV_FLOW_STEP_ITEM_HELP          TABLE
NON_AWR    2.0 SYSMAN.MGMT_METRICS                          TABLE
NON_AWR    2.0 MDSYS.SDO_COORD_REF_SYS                      TABLE
NON_AWR    2.0 XDB.XDB$ELEMENT                              TABLE
NON_AWR    1.1 SYS.SYS_LOB0000064035C00004$$                LOBSEGMENT
NON_AWR    1.1 SYS.SYS_LOB0000064045C00004$$                LOBSEGMENT
NON_AWR    1.0 SYS.SYS$SERVICE_METRICS_TAB                  TABLE
NON_AWR    1.0 XDB.XDB$H_LINK                               TABLE
NON_AWR    0.9 SYSMAN.MGMT_METRICS_PK                       INDEX
NON_AWR    0.9 SYSMAN.MGMT_METRICS_IDX_01                   INDEX
NON_AWR    0.9 SYS.SCHEDULER$_EVENT_LOG                     TABLE
NON_AWR    0.9 SYSMAN.MGMT_METRICS_IDX_03                   INDEX
NON_AWR    0.8 SYS.AW_OBJ$                                  TABLE
NON_AWR    0.8 SYS.SYS_LOB0000006207C00004$$                LOBSEGMENT
NON_AWR    0.8 XDB.XDB$RESOURCE_ACLOID_IDX                  INDEX
NON_AWR    0.8 APEX_030200.WWV_FLOW_LIST_ITEMS              TABLE
NON_AWR    0.8 APEX_030200.WWV_FLOW_STEP_VALIDATIONS        TABLE
NON_AWR    0.8 SYSMAN.MGMT_SWLIB_ENTITY_DOCUMENTS           TABLE
NON_AWR    0.8 SYS.WRI$_ADV_RATIONALE                       TABLE
NON_AWR    0.7 SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS            TABLE
NON_AWR    0.7 SYS.SYS_LOB0000005778C00040$$                LOBSEGMENT
NON_AWR    0.7 APEX_030200.WWV_FLOW_STEPS                   TABLE
NON_AWR    0.6 XDB.XDB$COMPLEX_TYPE                         TABLE
NON_AWR    0.6 APEX_030200.WWV_FLOW_STEP_ITEMS_U_NAME       INDEX
NON_AWR    0.6 XDB.XDB_PK_H_LINK                            INDEX
NON_AWR    0.6 APEX_030200.WWV_FLOW_STEP_BRANCHES           TABLE
NON_AWR    0.6 APEX_030200.WWV_FLOW_STEP_BUTTONS            TABLE
NON_AWR    0.6 SYSMAN.MGMT_JOB_STEP_PARAMS                  TABLE
NON_AWR    0.6 SYSMAN.MGMT_POLICIES                         TABLE
NON_AWR    0.6 APEX_030200.SYS_LOB0000070597C00010$$        LOBSEGMENT
NON_AWR    0.6 ORDDATA.SYS_LOB0000057989C00005$$            LOBSEGMENT
NON_AWR    0.6 ORDDATA.SYS_LOB0000057841C00005$$            LOBSEGMENT
NON_AWR    0.6 SYSMAN.PK_MGMT_JOB_STEP_PARAMS               INDEX
NON_AWR    0.5 APEX_030200.WWV_FLOW_LIST_OF_VALUES_DATA     TABLE
NON_AWR    0.5 SYS.SYS_LOB0000004976C00005$$                LOBSEGMENT

**********************************
(5a) AWR snapshots - last 50
**********************************

Total snapshots in DB 318094786 Instance 1 = 121

      DBID  SNAP_ID  INST FLUSH_ELAPSED      ENDTM            STARTUP_TIME     STATUS ERRCNT
---------- -------- ----- ------------------ ---------------- ---------------- ------ ------
 318094786     1456     1 +00000 00:00:00.2  19:00:03 (11/25) 03:32:07 (11/25)      0      0
 318094786     1457     1 +00000 00:00:00.3  20:00:23 (11/25) 03:32:07 (11/25)      0      0
 318094786     1458     1 +00000 00:00:00.1  21:00:28 (11/25) 03:32:07 (11/25)      0      0
 318094786     1459     1 +00000 00:00:00.2  22:00:14 (11/25) 03:32:07 (11/25)      0      0
 318094786     1460     1 +00000 00:00:08.0  00:03:57 (11/26) 03:32:07 (11/25)      0      0
 318094786     1461     1 +00000 00:00:00.3  01:00:31 (11/26) 03:32:07 (11/25)      0      0
 318094786     1462     1 +00000 00:00:00.7  02:00:24 (11/26) 03:32:07 (11/25)      0      0
 318094786     1463     1 +00000 00:00:00.3  03:00:52 (11/26) 03:32:07 (11/25)      0      0
 318094786     1464     1 +00000 00:00:00.8  04:00:39 (11/26) 03:32:07 (11/25)      0      0
 318094786     1465     1 +00000 00:00:00.1  05:00:19 (11/26) 03:32:07 (11/25)      0      0
 318094786     1466     1 +00000 00:00:00.6  06:00:06 (11/26) 03:32:07 (11/25)      0      0
 318094786     1467     1 +00000 00:00:00.2  07:00:37 (11/26) 03:32:07 (11/25)      0      0
 318094786     1468     1 +00000 00:00:00.6  08:00:36 (11/26) 03:32:07 (11/25)      0      0
 318094786     1469     1 +00000 00:00:00.1  09:00:19 (11/26) 03:32:07 (11/25)      0      0
 318094786     1470     1 +00000 00:00:03.3  10:34:06 (11/26) 03:32:07 (11/25)      0      0
 318094786     1471     1 +00000 00:00:01.5  11:02:44 (11/26) 03:32:07 (11/25)      0      0
 318094786     1472     1 +00000 00:00:00.6  12:00:10 (11/26) 03:32:07 (11/25)      0      0
 318094786     1473     1 +00000 00:00:00.1  13:00:48 (11/26) 03:32:07 (11/25)      0      0
 318094786     1474     1 +00000 00:00:00.1  14:00:33 (11/26) 03:32:07 (11/25)      0      0
 318094786     1475     1 +00000 00:00:00.3  15:00:25 (11/26) 03:32:07 (11/25)      0      0
 318094786     1476     1 +00000 00:00:00.1  16:00:00 (11/26) 03:32:07 (11/25)      0      0
 318094786     1477     1 +00000 00:00:00.4  17:00:03 (11/26) 03:32:07 (11/25)      0      0
 318094786     1478     1 +00000 00:00:00.1  18:00:47 (11/26) 03:32:07 (11/25)      0      0
 318094786     1479     1 +00000 00:00:07.0  19:46:37 (11/26) 03:32:07 (11/25)      0      0
 318094786     1480     1 +00000 00:00:00.1  20:00:46 (11/26) 03:32:07 (11/25)      0      0
 318094786     1481     1 +00000 00:00:00.1  21:00:30 (11/26) 03:32:07 (11/25)      0      0
 318094786     1482     1 +00000 00:00:00.3  22:00:10 (11/26) 03:32:07 (11/25)      0      0
 318094786     1483     1 +00000 00:00:00.7  23:00:58 (11/26) 03:32:07 (11/25)      0      0
 318094786     1484     1 +00000 00:00:00.1  00:00:36 (11/27) 03:32:07 (11/25)      0      0
 318094786     1485     1 +00000 00:00:00.2  01:00:01 (11/27) 03:32:07 (11/25)      0      0
 318094786     1486     1 +00000 00:00:04.8  04:47:57 (11/27) 03:32:07 (11/25)      0      0
 318094786     1487     1 +00000 00:00:10.9  06:28:32 (11/27) 03:32:07 (11/25)      0      0
 318094786     1488     1 +00000 00:00:00.7  07:00:54 (11/27) 03:32:07 (11/25)      0      0
 318094786     1489     1 +00000 00:00:00.1  08:00:31 (11/27) 03:32:07 (11/25)      0      0
 318094786     1490     1 +00000 00:00:00.1  09:00:54 (11/27) 03:32:07 (11/25)      0      0
 318094786     1491     1 +00000 00:00:00.2  10:00:36 (11/27) 03:32:07 (11/25)      0      0
 318094786     1492     1 +00000 00:00:00.4  11:00:05 (11/27) 03:32:07 (11/25)      0      0
 318094786     1493     1 +00000 00:00:00.1  12:00:49 (11/27) 03:32:07 (11/25)      0      0
 318094786     1494     1 +00000 00:00:00.3  13:00:26 (11/27) 03:32:07 (11/25)      0      0
 318094786     1495     1 +00000 00:00:00.7  14:00:07 (11/27) 03:32:07 (11/25)      0      0
 318094786     1496     1 +00000 00:00:00.5  15:01:40 (11/27) 03:32:07 (11/25)      0      0
 318094786     1497     1 +00000 00:00:00.1  16:00:30 (11/27) 03:32:07 (11/25)      0      0
 318094786     1498     1 +00000 00:00:00.2  17:00:25 (11/27) 03:32:07 (11/25)      0      0
 318094786     1499     1 +00000 00:00:00.6  18:00:57 (11/27) 03:32:07 (11/25)      0      0
 318094786     1500     1 +00000 00:00:00.7  19:00:24 (11/27) 03:32:07 (11/25)      0      0
 318094786     1501     1 +00000 00:00:00.1  20:00:08 (11/27) 03:32:07 (11/25)      0      0
 318094786     1502     1 +00000 00:00:04.5  02:18:39 (11/28) 02:07:25 (11/28)      0      0
 318094786     1503     1 +00000 00:00:00.8  03:00:07 (11/28) 02:07:25 (11/28)      0      0
 318094786     1504     1 +00000 00:00:00.2  04:00:53 (11/28) 02:07:25 (11/28)      0      0
 318094786     1505     1 +00000 00:00:10.6  06:54:11 (11/28) 02:07:25 (11/28)      0      0

**********************************
(5b) AWR snapshots with errors or invalid
**********************************

no rows selected

**********************************
(5c) AWR snapshots -- OLDEST Non-Baselined snapshots
**********************************

      DBID  INST    SNAP_ID ENDTM             STATUS ERROR_COUNT
---------- ----- ---------- ----------------- ------ -----------
 318094786     1       1385 07:25:05 (11/20)       0           0

**********************************
(6) AWR Control Settings - interval, retention
**********************************

      DBID LSNAPID LSPLITID LSNAPTIME      LPURGETIME     FLAG INTERVAL          RETENTION         VRSN
---------- ------- -------- -------------- -------------- ---- ----------------- ----------------- ----
 318094786    1505     1487 11/28 06:54:22 11/28 06:54:11    2 +00000 01:00:00.0 +00008 00:00:00.0    4

**********************************
(7a) AWR Contents - row counts for each snapshots
**********************************

 SNAP_ID  INST  ASH  SQL  SQBND  FILES  SEGST  SYSEVT
-------- ----- ---- ---- ------ ------ ------ -------
    1456     1    0   69    191     11     80      78
    1457     1    3   69    178     11     81      78
    1458     1    3   70    180     11     80      78
    1459     1    7  105    375     11     83      82
    1460     1   12  106    465     11     79      82
    1461     1    9   85    239     11     80      83
    1462     1    1   85    243     11     80      84
    1463     1   17   88    259     11     82      84
    1464     1    1   88    268     11     84      84
    1465     1    3   99    452     11     76      85
    1466     1    1   88    257     11     86      85
    1467     1    1   78    207     11     82      85
    1468     1    4   80    247     11     89      85
    1469     1    0   77    212     11     89      85
    1470     1    2   95    253     11     86      85
    1471     1    6   88    249     11     88      85
    1472     1    3   79    235     11     89      85
    1473     1    1   81    242     11     88      85
    1474     1    1   81    214     11     89      85
    1475     1    2   82    215     11     90      86
    1476     1    2   82    253     11     89      86
    1477     1    8   82    211     11     90      86
    1478     1    5   81    232     11     90      86
    1479     1    1  91     257     11     86      87
    1480     1    6  74     222     11     88      87
    1481     1    0  77     220     11     90      87
    1482     1    2  97     378     11     89      87
    1483     1    8 104     384     11     84      87
    1484     1    4  88     253     11     90      87
    1485     1    4 106     320     11     90      88
    1486     1    5 101     304     11     90      88
    1487     1   14 101     305     11     89      92
    1488     1   36 114     561     11     79      93
    1489     1    6  92     288     11     90      93
    1490     1    7  90     304     11     90      93
    1491     1    3  90     291     11     90      93
    1492     1    1  96     290     11     90      93
    1493     1    5  87     261     11     89      93
    1494     1    3  93     308     11     89      93
    1495     1    8 102     294     11     90      93
    1496     1    8  91     297     11     88      93
    1497     1    5  86     282     11     90      93
    1498     1    3  91     271     11     90      93
    1499     1    5  91     275     11     88      93
    1500     1    6  92     281     11     90      93
    1501     1    2  83     262     11     90      93
    1502     1   18 109     434     11     56      69
    1503     1    6  88     316     11     57      70
    1504     1   10  94     206     11     60      73
    1505     1    2 103     280     11     62      76

**********************************
(7b) AWR Contents - average row counts per snapshot
**********************************

SNAP_COUNT  INST   ASH  SQLSTAT  SQLBIND  FILES  SEGSTAT  SYSEVENT
---------- ----- ----- -------- -------- ------ -------- ---------
       121     1  8.21     89.8   293.75     11    83.21     84.87

**********************************
(7c) AWR total item counts - names, text, plans
**********************************

 SQLTEXT  SQLPLAN  SQLBMETA  SEGOBJ  DATAFILE  TEMPFILE
-------- -------- --------- ------- --------- ---------
     741     9403      2257     557        11         1


########################################################
(II) Advisor Framework Info
########################################################

**********************************
(1) Advisor Tasks - Last 50
**********************************

OWNER/ADVISOR  TASK_ID/NAME                     CREATED             EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ----------------    ---------- ---------- ----- ------------
SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK          13:27:02 (04/02)         36  ########## AUTO  COMPLETED
SYS/ADDM       1772/ADDM:318094786_1_1459          22:00:15 (11/25)          1           1 AUTO  COMPLETED
SYS/ADDM       1773/ADDM:318094786_1_1460          00:04:05 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1774/ADDM:318094786_1_1461          01:00:32 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1775/ADDM:318094786_1_1462          02:00:25 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1776/ADDM:318094786_1_1463          03:00:53 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1777/ADDM:318094786_1_1464          04:00:40 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1778/ADDM:318094786_1_1465          05:00:20 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1779/ADDM:318094786_1_1466          06:00:07 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1780/ADDM:318094786_1_1467          07:00:38 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1781/ADDM:318094786_1_1468          08:00:37 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1782/ADDM:318094786_1_1469          09:00:20 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1783/ADDM:318094786_1_1470          10:34:10 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1784/ADDM:318094786_1_1471          11:02:46 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1785/ADDM:318094786_1_1472          12:00:11 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1786/ADDM:318094786_1_1473          13:00:48 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1787/ADDM:318094786_1_1474          14:00:33 (11/26)          1           1 AUTO  COMPLETED
SYS/ADDM       1788/ADDM:318094786_1_1475          15:00:26 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1789/ADDM:318094786_1_1476          16:00:00 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1790/ADDM:318094786_1_1477          17:00:04 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1791/ADDM:318094786_1_1478          18:00:47 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1792/ADDM:318094786_1_1479          19:46:44 (11/26)          1           1 AUTO  COMPLETED
SYS/ADDM       1793/ADDM:318094786_1_1480          20:00:47 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1794/ADDM:318094786_1_1481          21:00:30 (11/26)          0           0 AUTO  COMPLETED
SYS/Segment Ad 1795/SYS_AUTO_SPCADV_04002226112010 22:00:04 (11/26)          0           0 CMD   COMPLETED
SYS/ADDM       1796/ADDM:318094786_1_1482          22:00:11 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1797/ADDM:318094786_1_1483          23:00:59 (11/26)          0           0 AUTO  COMPLETED
SYS/ADDM       1798/ADDM:318094786_1_1484          00:00:36 (11/27)          0           1 AUTO  COMPLETED
SYS/ADDM       1799/ADDM:318094786_1_1485          01:00:02 (11/27)          0           0 AUTO  COMPLETED
SYS/ADDM       1800/ADDM:318094786_1_1486          04:48:02 (11/27)          0           0 AUTO  COMPLETED
SYS/ADDM       1801/ADDM:318094786_1_1487          06:28:43 (11/27)          0           0 AUTO  COMPLETED
SYS/Segment Ad 1802/SYS_AUTO_SPCADV_46280627112010 06:28:46 (11/27)          3           3 CMD   COMPLETED
SYS/ADDM       1803/ADDM:318094786_1_1488          07:00:55 (11/27)          0           0 AUTO  COMPLETED
SYS/ADDM       1804/ADDM:318094786_1_1489          08:00:32 (11/27)          0           0 AUTO  COMPLETED
SYS/ADDM       1805/ADDM:318094786_1_1490          09:00:55 (11/27)          0           0 AUTO  COMPLETED
SYS/ADDM       1806/ADDM:318094786_1_1491          10:00:37 (11/27)          0           0 AUTO  COMPLETED
SYS/ADDM       1807/ADDM:318094786_1_1492          11:00:06 (11/27)          0           0 AUTO  COMPLETED
SYS/ADDM       1808/ADDM:318094786_1_1493          12:00:49 (11/27)          0           0 AUTO  COMPLETED
SYS/ADDM       1809/ADDM:318094786_1_1494          13:00:27 (11/27)          0           0 AUTO  COMPLETED
SYS/ADDM       1810/ADDM:318094786_1_1495          14:00:08 (11/27)          0           0 AUTO  COMPLETED
SYS/ADDM       1811/ADDM:318094786_1_1496          15:01:41 (11/27)          0           0 AUTO  COMPLETED
SYS/ADDM       1812/ADDM:318094786_1_1497          16:00:30 (11/27)          0           0 AUTO  COMPLETED
SYS/ADDM       1813/ADDM:318094786_1_1498          17:00:26 (11/27)          0           0 AUTO  COMPLETED
SYS/ADDM       1814/ADDM:318094786_1_1499          18:00:58 (11/27)          0           0 AUTO  COMPLETED
SYS/ADDM       1815/ADDM:318094786_1_1500          19:00:25 (11/27)          0           0 AUTO  COMPLETED
SYS/ADDM       1816/ADDM:318094786_1_1501          20:00:09 (11/27)          0           0 AUTO  COMPLETED
SYS/ADDM       1824/ADDM:318094786_1_1503          03:00:08 (11/28)          0           0 AUTO  COMPLETED
SYS/ADDM       1825/ADDM:318094786_1_1504          04:00:54 (11/28)          0           0 AUTO  COMPLETED
SYS/ADDM       1826/ADDM:318094786_1_1505          06:54:22 (11/28)          1           1 AUTO  COMPLETED
SYS/Segment Ad 1827/SYS_AUTO_SPCADV_34540628112    06:54:34 (11/28)         26          27 CMD   COMPLETED
010

**********************************
(2) Advisor Task - Oldest 5
**********************************

OWNER/ADVISOR  TASK_ID/NAME                CREATED          EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- --------------------------- ---------------- ---------- ---------- ----- ----------
SYS/ADDM       1398/ADDM:318094786_1_1138  09:35:57 (10/29)          1          2 AUTO  COMPLETED
SYS/ADDM       1399/ADDM:318094786_1_1139  11:00:03 (10/29)          0          0 AUTO  COMPLETED
SYS/ADDM       1400/ADDM:318094786_1_1140  12:00:34 (10/29)          0          0 AUTO  COMPLETED
SYS/ADDM       1401/ADDM:318094786_1_1141  13:00:56 (10/29)          0          0 AUTO  COMPLETED
SYS/ADDM       1402/ADDM:318094786_1_1142  14:01:01 (10/29)          0          0 AUTO  COMPLETED

**********************************
(3) Advisor Tasks With Errors - Last 50
**********************************

no rows selected


########################################################
(III) ASH Usage Info
########################################################

**********************************
(1a) ASH histogram (past 3 days)
**********************************

NUM_ACTIVE_SESSIONS   NUM_SAMPLES
-------------------- ------------
0000 - 0004                   212
0005 - 0009                     7

**********************************
(1b) ASH histogram (past 1 day)
**********************************

NUM_ACTIVE_SESSIONS   NUM_SAMPLES
-------------------- ------------
0000 - 0004                    69
0005 - 0009                     2

**********************************
(2a) ASH details (past 3 days)
**********************************

INST MIN_TIME         MAX_TIME          NUM_SAMPLES  NUM_ROWS AVG_ACTIVE
---- ---------------- ---------------- ------------ --------- ----------
   1 08:04:41 (11/25) 04:53:32 (11/28)       19,229       309       0.02

**********************************
(2b) ASH details (past 1 day)
**********************************

INST MIN_TIME         MAX_TIME          NUM_SAMPLES  NUM_ROWS AVG_ACTIVE
---- ---------------- ---------------- ------------ --------- ----------
1 08:05:41 (11/27)    04:53:32 (11/28)        5,251        92       0.02

**********************************
(2c) ASH sessions (Fg Vs Bg) (past 1 day across all instances in RAC)
**********************************

Foreground %           13.0
Background %           87.0
MMNL %                  0.0
End of Report

Related Topics
AWR Report
DBMS_AWR_REPORT_LAYOUT
DBMS_WORKLOAD_REPOSITORY
Packages
UTLSYXSZ_UTIL

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved