Space consumed by AWR snapshots
SQL> @d
NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
RAC rac1 rac READ WRITE PRIMARY YES 0
- Execute command to check Space consumed by AWR snapshots
SQL> SELECT OCCUPANT_NAME, SPACE_USAGE_KBYTES/1024 MB, MOVE_PROCEDURE FROM V$SYSAUX_OCCUPANTS WHERE OCCUPANT_NAME = 'SM/AWR';
OCCUPANT_NAME MB MOVE_PROCEDURE
---------------------------------------------------------------- ---------- ----------------------------------------------------------------
SM/AWR 814.75
- This script will report general AWR information
SQL> @?/rdbms/admin/awrinfo.sql
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrinfo.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: test.txt
Using the report name test.txt
No errors.
No errors.
~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~
Report generated at
12:06:14 on Jan 11, 2021 ( Monday ) in Timezone +01: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
------------ --------- ---------------------------------------- ----- ----------------- ------------ ---
* 4255065810 SMRRAC sgdcplm02 - Linux x86 64-bit 1 07:34:38 (01/09) 39017973 YES
4255065810 SMRRAC sgdcplm01 - Linux x86 64-bit 2 07:34:07 (01/09) 39653620 YES
########################################################
(I) AWR Snapshots Information
########################################################
*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size 2,175.6 MB ( 7% of 32,768.0 MB MAX with AUTOEXTEND ON )
|
| Schema SYS occupies 1,711.9 MB ( 78.7% )
| Schema APEX_040200 occupies 248.4 MB ( 11.4% )
| Schema MDSYS occupies 93.8 MB ( 4.3% )
| Schema XDB occupies 68.6 MB ( 3.2% )
| Schema ORDDATA occupies 16.1 MB ( 0.7% )
| Schema SYSTEM occupies 15.8 MB ( 0.7% )
| Schema WMSYS occupies 7.2 MB ( 0.3% )
| Schema DVSYS occupies 4.4 MB ( 0.2% )
| Schema CTXSYS occupies 3.8 MB ( 0.2% )
| Schema AUDSYS occupies 2.6 MB ( 0.1% )
| Schema GSMADMIN_INT occupies 1.4 MB ( 0.1% )
| Schema DBSNMP occupies 1.3 MB ( 0.1% )
| Schema ORDSYS occupies 0.4 MB ( 0.0% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name Schema Name Space Usage
| -------------------- -------------------- ----------------
| SM/AWR SYS 814.8 MB
| SM/OPTSTAT SYS 477.5 MB
| SDO MDSYS 93.8 MB
| SM/ADVISOR SYS 89.1 MB
| XDB XDB 68.6 MB
| SM/OTHER SYS 66.9 MB
| AO SYS 38.9 MB
| XSOQHIST SYS 38.9 MB
| JOB_SCHEDULER SYS 16.6 MB
| ORDIM/ORDDATA ORDDATA 16.1 MB
| LOGMNR SYSTEM 13.9 MB
| WM WMSYS 7.2 MB
| TEXT CTXSYS 3.8 MB
| SMON_SCN_TIME SYS 3.3 MB
| AUDSYS AUDSYS 2.6 MB
| SQL_MANAGEMENT_BASE SYS 2.4 MB
| PL/SCOPE SYS 1.6 MB
| LOGSTDBY SYSTEM 1.5 MB
| EM_MONITORING_USER DBSNMP 1.3 MB
| STREAMS SYS 1.0 MB
| ORDIM ORDSYS 0.4 MB
| AUTO_TASK SYS 0.4 MB
| AUDIT_TABLES SYS 0.0 MB
| EM SYSMAN 0.0 MB
| EXPRESSION_FILTER EXFSYS 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
| XSAMD OLAPSYS 0.0 MB
|
| Others (Unaccounted space) 415.1 MB
|
******************************************
(1c) SYSAUX usage - Unregistered Schemas
******************************************
| This section displays schemas that are not registered
| in V$SYSAUX_OCCUPANTS
|
| Schema APEX_040200 occupies 248.4 MB
| Schema DVSYS occupies 4.4 MB
| Schema GSMADMIN_INT occupies 1.4 MB
|
| Total space 254.3 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 160.9 MB
|
| Total space 160.9 MB
|
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 60 mins snapshot INTERVAL:
| AWR size/day 46.3 MB (1,977 K/snap * 24 snaps/day)
| AWR size/wk 324.4 MB (size_per_day * 7) per instance
| AWR size/wk 648.7 MB (size_per_day * 7) per database
|
| Estimates based on 24 snaps in past 24 hours:
| AWR size/day 46.3 MB (1,977 K/snap and 24 snaps in past 24 hours)
| AWR size/wk 324.4 MB (size_per_day * 7) per instance
| AWR size/wk 648.7 MB (size_per_day * 7) per database
|
**********************************
(3a) Space usage by AWR components (per database)
**********************************
COMPONENT MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%
--------- --------- ------ ------------ ---------- ----------- ----------------
FIXED 407.1 50.0 988 23.2 162.1 51% : 49%
EVENTS 123.7 15.2 300 7.0 49.2 46% : 54%
SQLPLAN 67.0 8.2 163 3.8 26.7 67% : 33%
SQL 32.2 4.0 78 1.8 12.8 75% : 25%
SPACE 22.0 2.7 53 1.3 8.8 63% : 37%
ASH 10.0 1.2 24 0.6 4.0 78% : 23%
SQLBIND 10.0 1.2 24 0.6 4.0 40% : 60%
RAC 8.8 1.1 21 0.5 3.5 52% : 48%
SQLTEXT 5.4 0.7 13 0.3 2.1 93% : 7%
**********************************
(3b) Space usage within AWR Components (> 500K)
**********************************
COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
FIXED 57.0 WRH$_SYSMETRIC_HISTORY_INDEX - 60% INDEX
FIXED 55.0 WRH$_SYSMETRIC_HISTORY - 66% TABLE
FIXED 11.0 WRH$_SYSMETRIC_SUMMARY - 57% TABLE
FIXED 7.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_4255065810_11226 - 79% INDEX PARTITION
FIXED 6.0 WRH$_LATCH_PK.WRH$_LATCH_4255065810_11226 - 64% INDEX PARTITION
FIXED 6.0 WRH$_SYSSTAT.WRH$_SYSSTA_4255065810_11226 - 66% TABLE PARTITION
FIXED 6.0 WRH$_LATCH.WRH$_LATCH_4255065810_11226 - 71% TABLE PARTITION
FIXED 5.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_4255065810_11202 - 78% INDEX PARTITION
FIXED 5.0 WRH$_SYSMETRIC_SUMMARY_INDEX - 61% INDEX
FIXED 4.0 WRH$_SYSSTAT.WRH$_SYSSTA_4255065810_11082 - 52% TABLE PARTITION
FIXED 4.0 WRH$_RSRC_CONSUMER_GROUP - 44% TABLE
FIXED 4.0 WRH$_PARAMETER.WRH$_PARAME_4255065810_11106 - 25% TABLE PARTITION
FIXED 4.0 WRH$_LATCH_PK.WRH$_LATCH_4255065810_11202 - 70% INDEX PARTITION
FIXED 4.0 WRH$_LATCH.WRH$_LATCH_4255065810_11082 - 56% TABLE PARTITION
FIXED 4.0 WRH$_SYSSTAT.WRH$_SYSSTA_4255065810_11154 - 52% TABLE PARTITION
FIXED 4.0 WRH$_SYSSTAT.WRH$_SYSSTA_4255065810_11272 - 60% TABLE PARTITION
FIXED 4.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_4255065810_11082 - 78% INDEX PARTITION
FIXED 4.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_4255065810_11106 - 79% INDEX PARTITION
FIXED 4.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_4255065810_11130 - 79% INDEX PARTITION
FIXED 4.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_4255065810_11154 - 79% INDEX PARTITION
FIXED 4.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_4255065810_11178 - 79% INDEX PARTITION
FIXED 4.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_4255065810_11272 - 77% INDEX PARTITION
FIXED 4.0 WRM$_SNAPSHOT_DETAILS - 56% TABLE
FIXED 4.0 WRH$_SYSSTAT.WRH$_SYSSTA_4255065810_11202 - 52% TABLE PARTITION
FIXED 3.0 WRH$_LATCH.WRH$_LATCH_4255065810_11130 - 74% TABLE PARTITION
FIXED 3.0 WRH$_LATCH.WRH$_LATCH_4255065810_11154 - 74% TABLE PARTITION
FIXED 3.0 WRH$_LATCH.WRH$_LATCH_4255065810_11178 - 74% TABLE PARTITION
FIXED 3.0 WRH$_LATCH.WRH$_LATCH_4255065810_11202 - 74% TABLE PARTITION
FIXED 3.0 WRH$_LATCH.WRH$_LATCH_4255065810_11272 - 86% TABLE PARTITION
FIXED 3.0 WRH$_LATCH.WRH$_LATCH_4255065810_11106 - 74% TABLE PARTITION
EVENTS 11.0 WRH$_BG_EVENT_SUMMARY - 42% TABLE
EVENTS 8.0 WRH$_BG_EVENT_SUMMARY_PK - 56% INDEX
EVENTS 7.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__4255065810_11226 - 72% INDEX PARTITION
EVENTS 6.0 WRH$_ENQUEUE_STAT - 51% TABLE
EVENTS 6.0 WRH$_ENQUEUE_STAT_PK - 49% INDEX
EVENTS 6.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__4255065810_11202 - 65% INDEX PARTITION
EVENTS 5.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__4255065810_11226 - 68% TABLE PARTITION
EVENTS 5.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__4255065810_11154 - 66% INDEX PARTITION
EVENTS 5.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__4255065810_11178 - 61% INDEX PARTITION
EVENTS 5.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__4255065810_11272 - 68% INDEX PARTITION
EVENTS 4.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__4255065810_11130 - 66% INDEX PARTITION
EVENTS 4.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__4255065810_11106 - 48% TABLE PARTITION
EVENTS 4.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__4255065810_11178 - 52% TABLE PARTITION
EVENTS 4.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__4255065810_11272 - 53% TABLE PARTITION
EVENTS 4.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__4255065810_11082 - 67% INDEX PARTITION
EVENTS 4.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__4255065810_11106 - 78% INDEX PARTITION
EVENTS 3.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__4255065810_11154 - 67% TABLE PARTITION
COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
EVENTS 3.0 WRH$_SYSTEM_EVENT_PK.WRH$_SYSTEM_4255065810_11226 - 78% INDEX PARTITION
EVENTS 3.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__4255065810_11202 - 69% TABLE PARTITION
EVENTS 3.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__4255065810_11130 - 65% TABLE PARTITION
EVENTS 3.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__4255065810_11082 - 61% TABLE PARTITION
EVENTS 2.0 WRH$_SYSTEM_EVENT.WRH$_SYSTEM_4255065810_11226 - 64% TABLE PARTITION
EVENTS 2.0 WRH$_LIBRARYCACHE - 48% TABLE
EVENTS 2.0 WRH$_SYSTEM_EVENT_PK.WRH$_SYSTEM_4255065810_11272 - 51% INDEX PARTITION
EVENTS 2.0 WRH$_LIBRARYCACHE_PK - 37% INDEX
EVENTS 1.0 WRH$_SYSTEM_EVENT.WRH$_SYSTEM_4255065810_11272 - 77% TABLE PARTITION
EVENTS 0.9 WRH$_SYSTEM_EVENT_PK.WRH$_SYSTEM_4255065810_11130 - 66% INDEX PARTITION
EVENTS 0.9 WRH$_SYSTEM_EVENT_PK.WRH$_SYSTEM_4255065810_11106 - 67% INDEX PARTITION
EVENTS 0.9 WRH$_SYSTEM_EVENT_PK.WRH$_SYSTEM_4255065810_11082 - 66% INDEX PARTITION
EVENTS 0.9 WRH$_SYSTEM_EVENT_PK.WRH$_SYSTEM_4255065810_11154 - 67% INDEX PARTITION
SQLPLAN 45.0 WRH$_SQL_PLAN - 40% TABLE
SQLPLAN 22.0 WRH$_SQL_PLAN_PK - 28% INDEX
SQL 4.0 WRH$_SQLSTAT.WRH$_SQLSTA_4255065810_11202 - 36% TABLE PARTITION
SQL 4.0 WRH$_SQLSTAT.WRH$_SQLSTA_4255065810_11082 - 33% TABLE PARTITION
SQL 3.0 WRH$_SQLSTAT.WRH$_SQLSTA_4255065810_11130 - 45% TABLE PARTITION
SQL 3.0 WRH$_SQLSTAT.WRH$_SQLSTA_4255065810_11178 - 47% TABLE PARTITION
SQL 3.0 WRH$_SQLSTAT.WRH$_SQLSTA_4255065810_11226 - 83% TABLE PARTITION
SQL 2.0 WRH$_SQLSTAT.WRH$_SQLSTA_4255065810_11154 - 68% TABLE PARTITION
SQL 2.0 WRH$_SQLSTAT.WRH$_SQLSTA_4255065810_11106 - 61% TABLE PARTITION
SQL 2.0 WRH$_SQLSTAT.WRH$_SQLSTA_4255065810_11272 - 68% TABLE PARTITION
SQL 0.8 WRH$_SQLSTAT_PK.WRH$_SQLSTA_4255065810_11226 - 61% INDEX PARTITION
SQL 0.6 WRH$_SQLSTAT_INDEX.WRH$_SQLSTA_4255065810_11226 - 70% INDEX PARTITION
SQL 0.5 WRH$_SQL_WORKAREA_HIST_PK - 50% INDEX
SQL 0.5 WRH$_SQLSTAT_PK.WRH$_SQLSTA_4255065810_11154 - 61% INDEX PARTITION
SQL 0.5 WRH$_SQLSTAT_PK.WRH$_SQLSTA_4255065810_11178 - 63% INDEX PARTITION
SQL 0.5 WRH$_SQLSTAT_PK.WRH$_SQLSTA_4255065810_11202 - 63% INDEX PARTITION
SPACE 2.0 WRH$_SEG_STAT.WRH$_SEG_ST_4255065810_11226 - 62% TABLE PARTITION
SPACE 1.0 WRH$_SEG_STAT.WRH$_SEG_ST_4255065810_11272 - 78% TABLE PARTITION
SPACE 0.9 WRH$_SEG_STAT.WRH$_SEG_ST_4255065810_11082 - 78% TABLE PARTITION
SPACE 0.9 WRH$_SEG_STAT.WRH$_SEG_ST_4255065810_11106 - 76% TABLE PARTITION
SPACE 0.9 WRH$_SEG_STAT.WRH$_SEG_ST_4255065810_11130 - 78% TABLE PARTITION
SPACE 0.9 WRH$_SEG_STAT.WRH$_SEG_ST_4255065810_11154 - 74% TABLE PARTITION
SPACE 0.9 WRH$_SEG_STAT.WRH$_SEG_ST_4255065810_11178 - 78% TABLE PARTITION
SPACE 0.9 WRH$_SEG_STAT.WRH$_SEG_ST_4255065810_11202 - 78% TABLE PARTITION
SPACE 0.8 WRH$_SEG_STAT_PK.WRH$_SEG_ST_4255065810_11226 - 64% INDEX PARTITION
SPACE 0.8 WRH$_IOSTAT_FILETYPE - 64% TABLE
SPACE 0.6 WRH$_SEG_STAT_PK.WRH$_SEG_ST_4255065810_11130 - 65% INDEX PARTITION
SPACE 0.6 WRH$_SEG_STAT_PK.WRH$_SEG_ST_4255065810_11272 - 65% INDEX PARTITION
SPACE 0.5 WRH$_SEG_STAT_OBJ_PK - 24% INDEX
SPACE 0.5 WRH$_SEG_STAT_PK.WRH$_SEG_ST_4255065810_11082 - 65% INDEX PARTITION
SPACE 0.5 WRH$_SEG_STAT_PK.WRH$_SEG_ST_4255065810_11178 - 66% INDEX PARTITION
SPACE 0.5 WRH$_SEG_STAT_PK.WRH$_SEG_ST_4255065810_11202 - 64% INDEX PARTITION
ASH 2.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_4255065810_11226 - 57% TABLE PARTITION
ASH 0.9 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_4255065810_11272 - 69% TABLE PARTITION
COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
ASH 0.9 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_4255065810_11154 - 74% TABLE PARTITION
ASH 0.9 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_4255065810_11202 - 76% TABLE PARTITION
ASH 0.8 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_4255065810_11130 - 76% TABLE PARTITION
ASH 0.8 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_4255065810_11178 - 82% TABLE PARTITION
ASH 0.7 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_4255065810_11082 - 71% TABLE PARTITION
ASH 0.7 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_4255065810_11106 - 72% TABLE PARTITION
SQLBIND 6.0 WRH$_SQL_BIND_METADATA_PK - 27% INDEX
SQLBIND 4.0 WRH$_SQL_BIND_METADATA - 28% TABLE
RAC 0.8 WRH$_DLM_MISC.WRH$_DLM_MI_4255065810_11226 - 87% TABLE PARTITION
RAC 0.8 WRH$_DLM_MISC_PK.WRH$_DLM_MI_4255065810_11226 - 58% INDEX PARTITION
RAC 0.6 WRH$_DLM_MISC.WRH$_DLM_MI_4255065810_11272 - 78% TABLE PARTITION
RAC 0.6 WRH$_DLM_MISC_PK.WRH$_DLM_MI_4255065810_11272 - 61% INDEX PARTITION
RAC 0.5 WRH$_DLM_MISC.WRH$_DLM_MI_4255065810_11154 - 76% TABLE PARTITION
SQLTEXT 5.0 WRH$_SQLTEXT - 35% TABLE
**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************
COMPONENT MB SEGMENT_NAME SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR 168.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
NON_AWR 96.0 SYS.I_WRI$_OPTSTAT_H_ST INDEX
NON_AWR 94.1 SYS.SYS_LOB0000008312C00038$$ LOBSEGMENT
NON_AWR 51.2 MDSYS.SYS_LOB0000083702C00006$$ LOBSEGMENT
NON_AWR 42.2 SYS.SYS_LOB0000006421C00005$$ LOBSEGMENT
NON_AWR 42.1 SYS.SYS_LOB0000010986C00001$$ LOBSEGMENT
NON_AWR 34.2 SYS.SYS_LOB0000008304C00004$$ LOBSEGMENT
NON_AWR 24.0 SYS.HEATMAP SYSTEM STATISTI
CS
NON_AWR 23.2 APEX_040200.SYS_LOB0000088338C00007$$ LOBSEGMENT
NON_AWR 19.0 APEX_040200.WWV_FLOW_PAGE_PLUGS TABLE
NON_AWR 17.2 SYS.SYS_LOB0000085557C00004$$ LOBSEGMENT
NON_AWR 15.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX
NON_AWR 14.1 SYS.SYS_LOB0000007777C00040$$ LOBSEGMENT
NON_AWR 11.0 APEX_040200.WWV_FLOW_STEP_ITEMS TABLE
NON_AWR 10.0 SYS.WRI$_ADV_SQLT_PLANS TABLE
NON_AWR 10.0 APEX_040200.WWV_FLOW_TEMPLATES TABLE
NON_AWR 9.2 SYS.SYS_LOB0000008106C00008$$ LOBSEGMENT
NON_AWR 9.2 APEX_040200.SYS_LOB0000088569C00009$$ LOBSEGMENT
NON_AWR 9.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10618 TABLE PARTITION
NON_AWR 9.0 MDSYS.SDO_CS_SRS TABLE
NON_AWR 9.0 SYS.WRI$_ADV_PARAMETERS_PK INDEX
NON_AWR 9.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10181 TABLE PARTITION
NON_AWR 8.1 APEX_040200.SYS_LOB0000087756C00016$$ LOBSEGMENT
NON_AWR 8.0 SYS.WRI$_OPTSTAT_OPR_TASKS TABLE
NON_AWR 8.0 SYS.I_WRI$_OPTSTAT_HH_ST INDEX
NON_AWR 8.0 SYS.WRI$_ADV_MESSAGE_GROUPS_PK INDEX
NON_AWR 8.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10433 TABLE PARTITION
NON_AWR 8.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10526 TABLE PARTITION
NON_AWR 8.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10261 TABLE PARTITION
NON_AWR 8.0 SYS.SCHEDULER$_EVENT_LOG TABLE
NON_AWR 7.2 SYS.SYS_LOB0000085542C00004$$ LOBSEGMENT
NON_AWR 7.0 SYS.WRI$_ADV_PARAMETERS TABLE
NON_AWR 7.0 SYS.I_SCHEDULER_JOB_RUN_DETAILS INDEX
NON_AWR 6.0 SYS.WRI$_ADV_MESSAGE_GROUPS TABLE
NON_AWR 6.0 APEX_040200.WWV_FLOW_STEP_PROCESSING TABLE
NON_AWR 6.0 SYS.SCHEDULER$_INSTANCE_PK INDEX
NON_AWR 6.0 SYS.WRI$_ADV_MSG_GRPS_IDX_01 INDEX
NON_AWR 6.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10365 TABLE PARTITION
NON_AWR 6.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10167 TABLE PARTITION
NON_AWR 6.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10287 TABLE PARTITION
NON_AWR 5.2 APEX_040200.SYS_LOB0000087810C00010$$ LOBSEGMENT
NON_AWR 5.1 SYS.SYS_LOB0000006421C00004$$ LOBSEGMENT
NON_AWR 5.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10505 TABLE PARTITION
NON_AWR 5.0 SYS.I_WRI$_OPTSTAT_OPR_TASKS_TGST INDEX
NON_AWR 4.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10613 TABLE PARTITION
COMPONENT MB SEGMENT_NAME SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR 4.0 APEX_040200.WWV_FLOW_REGION_REPORT_COLUMN TABLE
NON_AWR 4.0 APEX_040200.WWV_FLOW_STEPS TABLE
NON_AWR 4.0 SYSTEM.SYS_LOB0000001340C00009$$ LOBSEGMENT
NON_AWR 4.0 SYS.I_WRI$_OPTSTAT_OPR_TASKS_STIME INDEX
NON_AWR 3.2 SYS.SYS_LOB0000085537C00004$$ LOBSEGMENT
NON_AWR 3.2 APEX_040200.SYS_LOB0000088421C00019$$ LOBSEGMENT
NON_AWR 3.2 SYS.SYS_LOB0000085547C00004$$ LOBSEGMENT
NON_AWR 3.1 APEX_040200.SYS_LOB0000087667C00011$$ LOBSEGMENT
NON_AWR 3.0 SYS.WRI$_OPTSTAT_TAB_HISTORY TABLE
NON_AWR 3.0 APEX_040200.WWV_FLOW_WORKSHEET_COLUMNS TABLE
NON_AWR 3.0 APEX_040200.WWV_FLOW_INSTALL_SCRIPTS TABLE
NON_AWR 3.0 APEX_040200.WWV_FLOW_PAGE_PLUG_TEMPLATES TABLE
NON_AWR 3.0 APEX_040200.WWV_FLOW_STEP_BUTTONS TABLE
NON_AWR 3.0 SYS.I_WRI$_OPTSTAT_OPR_TASKS_OPOBJ INDEX
NON_AWR 3.0 SYS.I_WRI$_OPTSTAT_OPR_TASKS_OPID INDEX
NON_AWR 3.0 SYS.SMON_SCN_TO_TIME_AUX CLUSTER
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SYS_P10615 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10577 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10357 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10173 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10573 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10373 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10454 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10198 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10253 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10482 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10185 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10493 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10213 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10501 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10278 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10487 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10535 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10295 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10509 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10333 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10544 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10313 TABLE PARTITION
NON_AWR 3.0 SYS.WRI$_ADV_SQLT_PLAN_STATS TABLE
NON_AWR 3.0 SYS.WRI$_ADV_RATIONALE TABLE
NON_AWR 2.2 SYS.SYS_LOB0000085552C00004$$ LOBSEGMENT
NON_AWR 2.2 APEX_040200.SYS_LOB0000088585C00007$$ LOBSEGMENT
NON_AWR 2.2 APEX_040200.SYS_LOB0000087738C00028$$ LOBSEGMENT
NON_AWR 2.2 APEX_040200.SYS_LOB0000087738C00022$$ LOBSEGMENT
NON_AWR 2.2 SYS.SYS_LOB0000085562C00004$$ LOBSEGMENT
NON_AWR 2.1 SYS.SYS_LOB0000007741C00014$$ LOBSEGMENT
NON_AWR 2.1 AUDSYS.SYS_LOB0000091784C00014$$.SYS_LOB_P186 LOB PARTITION
COMPONENT MB SEGMENT_NAME SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR 2.1 SYS.SYS_LOB0000006450C00009$$.SYS_LOB_P10621 LOB PARTITION
NON_AWR 2.1 SYS.SYS_LOB0000006450C00009$$.SYS_LOB_P10558 LOB PARTITION
NON_AWR 2.1 SYS.SYS_LOB0000006450C00009$$.SYS_LOB_P10548 LOB PARTITION
NON_AWR 2.1 SYS.SYS_LOB0000006450C00009$$.SYS_LOB_P10553 LOB PARTITION
NON_AWR 2.0 SYS.WRI$_OPTSTAT_IND_HISTORY TABLE
NON_AWR 2.0 SYS.LTXID_TRANS.LTXID_TRANS_2 TABLE PARTITION
NON_AWR 2.0 SYS.SCHEDULER$_JOB_RUN_DETAILS TABLE
NON_AWR 2.0 SYS.WRI$_SEGADV_OBJLIST TABLE
**********************************
(5a) AWR snapshots - last 50
**********************************
Total snapshots in DB 4255065810 Instance 2 = 211
Total snapshots in DB 4255065810 Instance 1 = 211
DBID SNAP_ID INST FLUSH_ELAPSED ENDTM STARTUP_TIME STATUS ERRCNT
---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------
4255065810 11250 1 +00000 00:00:01.1 11:00:19 (01/09) 07:34:38 (01/09) 0 0
4255065810 11250 2 +00000 00:00:00.6 11:00:20 (01/09) 07:34:07 (01/09) 0 0
4255065810 11251 1 +00000 00:00:00.6 12:00:23 (01/09) 07:34:38 (01/09) 0 0
4255065810 11251 2 +00000 00:00:00.2 12:00:23 (01/09) 07:34:07 (01/09) 0 0
4255065810 11252 1 +00000 00:00:00.6 13:00:08 (01/09) 07:34:38 (01/09) 0 0
4255065810 11252 2 +00000 00:00:01.1 13:00:08 (01/09) 07:34:07 (01/09) 0 0
4255065810 11253 1 +00000 00:00:00.2 14:00:11 (01/09) 07:34:38 (01/09) 0 0
4255065810 11253 2 +00000 00:00:00.8 14:00:12 (01/09) 07:34:07 (01/09) 0 0
4255065810 11254 1 +00000 00:00:00.6 15:00:36 (01/09) 07:34:38 (01/09) 0 0
4255065810 11254 2 +00000 00:00:00.1 15:00:36 (01/09) 07:34:07 (01/09) 0 0
4255065810 11255 1 +00000 00:00:00.8 16:00:21 (01/09) 07:34:38 (01/09) 0 0
4255065810 11255 2 +00000 00:00:01.1 16:00:21 (01/09) 07:34:07 (01/09) 0 0
4255065810 11256 1 +00000 00:00:01.1 17:00:23 (01/09) 07:34:38 (01/09) 0 0
4255065810 11256 2 +00000 00:00:00.5 17:00:24 (01/09) 07:34:07 (01/09) 0 0
4255065810 11257 1 +00000 00:00:00.5 18:00:08 (01/09) 07:34:38 (01/09) 0 0
4255065810 11257 2 +00000 00:00:00.8 18:00:09 (01/09) 07:34:07 (01/09) 0 0
4255065810 11258 1 +00000 00:00:00.3 19:00:11 (01/09) 07:34:38 (01/09) 0 0
4255065810 11258 2 +00000 00:00:00.6 19:00:12 (01/09) 07:34:07 (01/09) 0 0
4255065810 11259 1 +00000 00:00:00.6 20:00:36 (01/09) 07:34:38 (01/09) 0 0
4255065810 11259 2 +00000 00:00:00.9 20:00:37 (01/09) 07:34:07 (01/09) 0 0
4255065810 11260 1 +00000 00:00:00.6 21:00:21 (01/09) 07:34:38 (01/09) 0 0
4255065810 11260 2 +00000 00:00:00.8 21:00:22 (01/09) 07:34:07 (01/09) 0 0
4255065810 11261 1 +00000 00:00:00.2 22:00:24 (01/09) 07:34:38 (01/09) 0 0
4255065810 11261 2 +00000 00:00:00.4 22:00:25 (01/09) 07:34:07 (01/09) 0 0
4255065810 11262 1 +00000 00:00:00.1 23:00:09 (01/09) 07:34:38 (01/09) 0 0
4255065810 11262 2 +00000 00:00:00.2 23:00:10 (01/09) 07:34:07 (01/09) 0 0
4255065810 11263 1 +00000 00:00:00.1 00:00:12 (01/10) 07:34:38 (01/09) 0 0
4255065810 11263 2 +00000 00:00:00.1 00:00:13 (01/10) 07:34:07 (01/09) 0 0
4255065810 11264 1 +00000 00:00:01.0 01:00:38 (01/10) 07:34:38 (01/09) 0 0
4255065810 11264 2 +00000 00:00:00.1 01:00:37 (01/10) 07:34:07 (01/09) 0 0
4255065810 11265 1 +00000 00:00:00.7 02:00:01 (01/10) 07:34:38 (01/09) 0 0
4255065810 11265 2 +00000 00:00:00.7 02:00:01 (01/10) 07:34:07 (01/09) 0 0
4255065810 11266 1 +00000 00:00:00.8 03:00:26 (01/10) 07:34:38 (01/09) 0 0
4255065810 11266 2 +00000 00:00:00.7 03:00:26 (01/10) 07:34:07 (01/09) 0 0
4255065810 11267 1 +00000 00:00:00.7 04:00:11 (01/10) 07:34:38 (01/09) 0 0
4255065810 11267 2 +00000 00:00:00.4 04:00:11 (01/10) 07:34:07 (01/09) 0 0
4255065810 11268 1 +00000 00:00:00.3 05:00:14 (01/10) 07:34:38 (01/09) 0 0
4255065810 11268 2 +00000 00:00:00.3 05:00:14 (01/10) 07:34:07 (01/09) 0 0
4255065810 11269 1 +00000 00:00:01.0 06:00:38 (01/10) 07:34:38 (01/09) 0 0
4255065810 11269 2 +00000 00:00:00.9 06:00:39 (01/10) 07:34:07 (01/09) 0 0
4255065810 11270 1 +00000 00:00:00.9 07:00:02 (01/10) 07:34:38 (01/09) 0 0
4255065810 11270 2 +00000 00:00:00.8 07:00:02 (01/10) 07:34:07 (01/09) 0 0
4255065810 11271 1 +00000 00:00:00.1 08:00:26 (01/10) 07:34:38 (01/09) 0 0
4255065810 11271 2 +00000 00:00:00.9 08:00:27 (01/10) 07:34:07 (01/09) 0 0
4255065810 11272 1 +00000 00:00:01.1 09:00:11 (01/10) 07:34:38 (01/09) 0 0
4255065810 11272 2 +00000 00:00:00.8 09:00:12 (01/10) 07:34:07 (01/09) 0 0
4255065810 11273 1 +00000 00:00:00.6 10:00:15 (01/10) 07:34:38 (01/09) 0 0
DBID SNAP_ID INST FLUSH_ELAPSED ENDTM STARTUP_TIME STATUS ERRCNT
---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------
4255065810 11273 2 +00000 00:00:00.4 10:00:15 (01/10) 07:34:07 (01/09) 0 0
4255065810 11274 1 +00000 00:00:00.6 11:00:00 (01/10) 07:34:38 (01/09) 0 0
4255065810 11274 2 +00000 00:00:01.2 11:00:00 (01/10) 07:34:07 (01/09) 0 0
4255065810 11275 1 +00000 00:00:00.3 12:00:03 (01/10) 07:34:38 (01/09) 0 0
4255065810 11275 2 +00000 00:00:00.9 12:00:04 (01/10) 07:34:07 (01/09) 0 0
4255065810 11276 1 +00000 00:00:00.6 13:00:27 (01/10) 07:34:38 (01/09) 0 0
4255065810 11276 2 +00000 00:00:01.2 13:00:27 (01/10) 07:34:07 (01/09) 0 0
4255065810 11277 1 +00000 00:00:00.7 14:00:12 (01/10) 07:34:38 (01/09) 0 0
4255065810 11277 2 +00000 00:00:01.2 14:00:12 (01/10) 07:34:07 (01/09) 0 0
4255065810 11278 1 +00000 00:00:00.4 15:00:15 (01/10) 07:34:38 (01/09) 0 0
4255065810 11278 2 +00000 00:00:01.0 15:00:16 (01/10) 07:34:07 (01/09) 0 0
4255065810 11279 1 +00000 00:00:00.5 16:00:40 (01/10) 07:34:38 (01/09) 0 0
4255065810 11279 2 +00000 00:00:01.0 16:00:40 (01/10) 07:34:07 (01/09) 0 0
4255065810 11280 1 +00000 00:00:01.1 17:00:03 (01/10) 07:34:38 (01/09) 0 0
4255065810 11280 2 +00000 00:00:00.5 17:00:04 (01/10) 07:34:07 (01/09) 0 0
4255065810 11281 1 +00000 00:00:00.2 18:00:28 (01/10) 07:34:38 (01/09) 0 0
4255065810 11281 2 +00000 00:00:00.6 18:00:29 (01/10) 07:34:07 (01/09) 0 0
4255065810 11282 1 +00000 00:00:00.3 19:00:13 (01/10) 07:34:38 (01/09) 0 0
4255065810 11282 2 +00000 00:00:00.7 19:00:14 (01/10) 07:34:07 (01/09) 0 0
4255065810 11283 1 +00000 00:00:00.9 20:00:17 (01/10) 07:34:38 (01/09) 0 0
4255065810 11283 2 +00000 00:00:00.3 20:00:17 (01/10) 07:34:07 (01/09) 0 0
4255065810 11284 1 +00000 00:00:00.1 21:00:02 (01/10) 07:34:38 (01/09) 0 0
4255065810 11284 2 +00000 00:00:01.1 21:00:02 (01/10) 07:34:07 (01/09) 0 0
4255065810 11285 1 +00000 00:00:00.6 22:00:05 (01/10) 07:34:38 (01/09) 0 0
4255065810 11285 2 +00000 00:00:00.9 22:00:06 (01/10) 07:34:07 (01/09) 0 0
4255065810 11286 1 +00000 00:00:00.7 23:00:30 (01/10) 07:34:38 (01/09) 0 0
4255065810 11286 2 +00000 00:00:00.9 23:00:31 (01/10) 07:34:07 (01/09) 0 0
4255065810 11287 1 +00000 00:00:00.1 00:00:16 (01/11) 07:34:38 (01/09) 0 0
4255065810 11287 2 +00000 00:00:00.8 00:00:16 (01/11) 07:34:07 (01/09) 0 0
4255065810 11288 1 +00000 00:00:00.8 01:00:19 (01/11) 07:34:38 (01/09) 0 0
4255065810 11288 2 +00000 00:00:00.8 01:00:19 (01/11) 07:34:07 (01/09) 0 0
4255065810 11289 1 +00000 00:00:00.1 02:00:04 (01/11) 07:34:38 (01/09) 0 0
4255065810 11289 2 +00000 00:00:00.8 02:00:04 (01/11) 07:34:07 (01/09) 0 0
4255065810 11290 1 +00000 00:00:00.7 03:00:07 (01/11) 07:34:38 (01/09) 0 0
4255065810 11290 2 +00000 00:00:00.7 03:00:07 (01/11) 07:34:07 (01/09) 0 0
4255065810 11291 1 +00000 00:00:00.8 04:00:32 (01/11) 07:34:38 (01/09) 0 0
4255065810 11291 2 +00000 00:00:00.7 04:00:32 (01/11) 07:34:07 (01/09) 0 0
4255065810 11292 1 +00000 00:00:00.7 05:00:17 (01/11) 07:34:38 (01/09) 0 0
4255065810 11292 2 +00000 00:00:00.6 05:00:17 (01/11) 07:34:07 (01/09) 0 0
4255065810 11293 1 +00000 00:00:01.0 06:00:20 (01/11) 07:34:38 (01/09) 0 0
4255065810 11293 2 +00000 00:00:00.8 06:00:20 (01/11) 07:34:07 (01/09) 0 0
4255065810 11294 1 +00000 00:00:00.5 07:00:04 (01/11) 07:34:38 (01/09) 0 0
4255065810 11294 2 +00000 00:00:00.3 07:00:04 (01/11) 07:34:07 (01/09) 0 0
4255065810 11295 1 +00000 00:00:00.1 08:00:06 (01/11) 07:34:38 (01/09) 0 0
4255065810 11295 2 +00000 00:00:00.9 08:00:07 (01/11) 07:34:07 (01/09) 0 0
4255065810 11296 1 +00000 00:00:00.9 09:00:31 (01/11) 07:34:38 (01/09) 0 0
4255065810 11296 2 +00000 00:00:00.6 09:00:31 (01/11) 07:34:07 (01/09) 0 0
DBID SNAP_ID INST FLUSH_ELAPSED ENDTM STARTUP_TIME STATUS ERRCNT
---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------
4255065810 11297 1 +00000 00:00:00.4 10:00:16 (01/11) 07:34:38 (01/09) 0 0
4255065810 11297 2 +00000 00:00:01.1 10:00:16 (01/11) 07:34:07 (01/09) 0 0
4255065810 11298 1 +00000 00:00:00.4 11:00:13 (01/11) 07:34:38 (01/09) 0 0
4255065810 11298 2 +00000 00:00:01.0 11:00:13 (01/11) 07:34:07 (01/09) 0 0
4255065810 11299 1 +00000 00:00:00.1 12:00:05 (01/11) 07:34:38 (01/09) 0 0
4255065810 11299 2 +00000 00:00:00.5 12:00:05 (01/11) 07:34:07 (01/09) 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
---------- ----- ---------- ----------------- ------ -----------
4255065810 1 11089 09:00:12 (01/02) 0 0
**********************************
(6) AWR Control Settings - interval, retention
**********************************
DBID LSNAPID LSPLITID LSNAPTIME LPURGETIME FLAG INTERVAL RETENTION VRSN
----------- -------- -------- -------------- -------------- ----- ----------------- ----------------- ----
4255065810 11299 11272 01/11 12:00:06 01/10 08:05:00 2 +00000 01:00:00.0 +00008 00:00:00.0 11
**********************************
(7a) AWR Contents - row counts for each snapshots
**********************************
SNAP_ID INST ASH SQL SQBND FILES SEGST SYSEVT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
11275 1 32 70 277 8 101 242
11275 2 43 90 221 8 104 267
11276 1 37 72 544 8 99 242
11276 2 66 102 547 8 104 267
11277 1 40 75 240 8 100 242
11277 2 37 67 182 8 105 267
11278 1 32 71 250 8 99 242
11278 2 44 81 751 8 102 267
11279 1 36 69 245 8 97 242
11279 2 35 72 488 8 114 268
11280 1 41 68 238 8 90 242
11280 2 38 75 194 8 110 268
11281 1 33 69 243 8 96 242
11281 2 47 94 168 8 105 268
11282 1 34 66 521 8 101 242
11282 2 61 99 1344 8 102 268
11283 1 37 72 258 8 101 242
11283 2 33 70 499 8 112 269
11284 1 33 69 247 8 103 243
11284 2 42 69 247 8 110 269
11285 1 36 74 274 8 99 244
11285 2 37 79 527 8 111 269
11286 1 30 70 237 8 98 244
11286 2 32 89 950 8 102 269
11287 1 33 66 242 8 103 244
11287 2 41 96 151 8 109 269
11288 1 27 69 565 8 98 244
11288 2 63 100 540 8 113 269
11289 1 35 72 255 8 103 244
11289 2 38 86 362 8 111 269
11290 1 24 64 250 8 99 244
11290 2 38 83 489 8 112 269
11291 1 26 65 240 8 98 244
11291 2 40 79 144 8 113 269
11292 1 40 64 237 8 102 244
11292 2 44 77 431 8 110 269
11293 1 36 68 263 8 96 244
11293 2 36 100 207 8 109 269
11294 1 115 75 960 8 100 244
11294 2 78 103 548 8 108 269
11295 1 87 77 672 8 103 244
11295 2 44 85 605 8 109 269
11296 1 47 70 283 8 95 244
11296 2 53 81 781 8 105 269
11297 1 26 64 238 8 100 244
11297 2 54 83 525 8 110 269
11298 1 32 70 261 8 98 244
SNAP_ID INST ASH SQL SQBND FILES SEGST SYSEVT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
11298 2 70 80 453 8 111 269
11299 1 35 67 244 8 104 244
11299 2 58 103 1074 8 115 269
**********************************
(7b) AWR Contents - average row counts per snapshot
**********************************
SNAP_COUNT INST ASH SQLSTAT SQLBIND FILES SEGSTAT SYSEVENT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
211 1 35.73 73.31 360.45 8 98.32 249.05
211 2 53.12 97.62 581.24 8 108.15 273.06
**********************************
(7c) AWR total item counts - names, text, plans
**********************************
SQLTEXT SQLPLAN SQLBMETA SEGOBJ DATAFILE TEMPFILE
---------- ---------- ---------- ---------- ---------- ----------
1355 64775 9153 1765 8 1
########################################################
(II) Advisor Framework Info
########################################################
**********************************
(1) Advisor Tasks - Last 50
**********************************
OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
SYS/ADDM 34834/ADDM:4255065810_2_11283 20:00:18 (01/10) 0 0 AUTO COMPLETED
SYS/ADDM 34825/ADDM:4255065810_1_11283 20:00:18 (01/10) 0 0 AUTO COMPLETED
SYS/ADDM 34835/ADDM:4255065810_11283 20:00:18 (01/10) 0 0 AUTO COMPLETED
SYS/ADDM 34827/ADDM:4255065810_11284 21:00:03 (01/10) 0 0 AUTO COMPLETED
SYS/ADDM 34826/ADDM:4255065810_1_11284 21:00:03 (01/10) 0 0 AUTO COMPLETED
SYS/ADDM 34836/ADDM:4255065810_2_11284 21:00:04 (01/10) 0 0 AUTO COMPLETED
SYS/ADDM 34838/ADDM:4255065810_1_11285 22:00:06 (01/10) 0 0 AUTO COMPLETED
SYS/ADDM 34837/ADDM:4255065810_2_11285 22:00:07 (01/10) 0 0 AUTO COMPLETED
SYS/ADDM 34848/ADDM:4255065810_11285 22:00:07 (01/10) 0 0 AUTO COMPLETED
SYS/Segment Ad 34849/SYS_AUTO_SPCADV_0412221001 22:12:04 (01/10) 1 1 CMD COMPLETED
2021
SYS/ADDM 34839/ADDM:4255065810_1_11286 23:00:31 (01/10) 0 0 AUTO COMPLETED
SYS/ADDM 34851/ADDM:4255065810_11286 23:00:32 (01/10) 0 0 AUTO COMPLETED
SYS/ADDM 34850/ADDM:4255065810_2_11286 23:00:32 (01/10) 0 0 AUTO COMPLETED
SYS/ADDM 34840/ADDM:4255065810_1_11287 00:00:17 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34852/ADDM:4255065810_2_11287 00:00:17 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34841/ADDM:4255065810_11287 00:00:17 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34853/ADDM:4255065810_2_11288 01:00:20 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34842/ADDM:4255065810_1_11288 01:00:20 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34854/ADDM:4255065810_11288 01:00:20 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34855/ADDM:4255065810_2_11289 02:00:05 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34844/ADDM:4255065810_11289 02:00:05 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34843/ADDM:4255065810_1_11289 02:00:05 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34857/ADDM:4255065810_11290 03:00:08 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34845/ADDM:4255065810_1_11290 03:00:08 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34856/ADDM:4255065810_2_11290 03:00:08 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34858/ADDM:4255065810_2_11291 04:00:33 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34846/ADDM:4255065810_1_11291 04:00:33 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34859/ADDM:4255065810_11291 04:00:33 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34860/ADDM:4255065810_2_11292 05:00:18 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34847/ADDM:4255065810_1_11292 05:00:18 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34868/ADDM:4255065810_11292 05:00:18 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34861/ADDM:4255065810_2_11293 06:00:21 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34862/ADDM:4255065810_11293 06:00:21 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34869/ADDM:4255065810_1_11293 06:00:21 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34870/ADDM:4255065810_1_11294 07:00:05 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34863/ADDM:4255065810_2_11294 07:00:05 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34871/ADDM:4255065810_11294 07:00:05 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34865/ADDM:4255065810_11295 08:00:08 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34872/ADDM:4255065810_1_11295 08:00:08 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34864/ADDM:4255065810_2_11295 08:00:08 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34866/ADDM:4255065810_2_11296 09:00:32 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34873/ADDM:4255065810_1_11296 09:00:32 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34867/ADDM:4255065810_11296 09:00:32 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34874/ADDM:4255065810_1_11297 10:00:17 (01/11) 1 1 AUTO COMPLETED
SYS/ADDM 34875/ADDM:4255065810_11297 10:00:18 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34878/ADDM:4255065810_2_11297 10:00:18 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34876/ADDM:4255065810_1_11298 11:00:14 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34880/ADDM:4255065810_11298 11:00:15 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34879/ADDM:4255065810_2_11298 11:00:15 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34888/ADDM:4255065810_11299 12:00:06 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34881/ADDM:4255065810_2_11299 12:00:06 (01/11) 0 0 AUTO COMPLETED
SYS/ADDM 34877/ADDM:4255065810_1_11299 12:00:06 (01/11) 0 0 AUTO COMPLETED
**********************************
(2) Advisor Task - Oldest 5
**********************************
OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
SYS/ADDM 32586/ADDM:4255065810_1_10561 09:00:15 (12/11) 0 0 AUTO COMPLETED
SYS/ADDM 32593/ADDM:4255065810_10561 09:00:15 (12/11) 0 0 AUTO COMPLETED
SYS/ADDM 32592/ADDM:4255065810_2_10561 09:00:15 (12/11) 0 0 AUTO COMPLETED
SYS/ADDM 32594/ADDM:4255065810_2_10562 10:00:06 (12/11) 0 0 AUTO COMPLETED
SYS/ADDM 32587/ADDM:4255065810_1_10562 10:00:07 (12/11) 0 0 AUTO COMPLETED
SYS/ADDM 32598/ADDM:4255065810_10562 10:00:07 (12/11) 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 4,240
0005 - 0009 14
0010 - 0014 1
**********************************
(1b) ASH histogram (past 1 day)
**********************************
NUM_ACTIVE_SESSIONS NUM_SAMPLES
-------------------- ------------
0000 - 0004 1,573
0005 - 0009 6
**********************************
(2a) ASH details (past 3 days)
**********************************
INST MIN_TIME MAX_TIME NUM_SAMPLES NUM_ROWS AVG_ACTIVE
---- ---------------- ---------------- ------------ ------------ ----------
2 12:59:57 (01/08) 11:55:51 (01/11) 21,559 2,992 0.14
1 12:59:55 (01/08) 11:56:27 (01/11) 21,562 2,304 0.11
**********************************
(2b) ASH details (past 1 day)
**********************************
INST MIN_TIME MAX_TIME NUM_SAMPLES NUM_ROWS AVG_ACTIVE
---- ---------------- ---------------- ------------ ------------ ----------
2 13:08:15 (01/10) 11:55:51 (01/11) 8,009 1,063 0.13
1 13:01:40 (01/10) 11:56:27 (01/11) 8,051 915 0.11
**********************************
(2c) ASH sessions (Fg Vs Bg) (past 1 day across all instances in RAC)
**********************************
Foreground % 7.7
Background % 92.3
MMNL % 0.0
End of Report
Report written to test.txt
- Space consumed by the Optimizer backup statistics
SQL> SELECT OCCUPANT_NAME, SPACE_USAGE_KBYTES/1024 MB, MOVE_PROCEDURE
2 FROM V$SYSAUX_OCCUPANTS
3 WHERE OCCUPANT_NAME = 'SM/OPTSTAT';
OCCUPANT_NAME MB
---------------------------------------------------------------- ----------
MOVE_PROCEDURE
----------------------------------------------------------------
SM/OPTSTAT 12.625
- Execute statement to get information about retention period of snapshots
SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;
GET_STATS_HISTORY_RETENTION
---------------------------
31
- Set Snapshot Retention period to 7 days
SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7);
PL/SQL procedure successfully completed.
- It will purge old stats then current retention period
SQL> exec DBMS_STATS.PURGE_STATS(NULL);
PL/SQL procedure successfully completed.