Some times , to trouble shoot the performance problem we alter snapshot interval of AWR and leave it,this may some times impact the storage. To get the AWR storage info , we can run awrinfo.sql package.
Based on the output we need to take appropriate action.
SQL> @?/rdbms/admin/awrinfo.sql
This script will report general AWR information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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: text
Using the report name text
No errors.
No errors.
~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~
Report generated at 19:01:56 on Jul 20, 2015 ( Monday ) in Timezone +05:30
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
------------ --------- ---------------------------------------- ----- ----------------- ------------ ---
* 1409006025 ORCL NAVAKANTH - Microsoft Windows x86 64-bit 1 18:50:28 (07/08) 369107 NO
########################################################
(I) AWR Snapshots Information
########################################################
*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size 833.9 MB ( 3% of 32,768.0 MB MAX with AUTOEXTEND ON )
|
| Schema SYS occupies 338.5 MB ( 40.6% )
| Schema XDB occupies 187.8 MB ( 22.5% )
| Schema APEX_040200 occupies 178.5 MB ( 21.4% )
| Schema MDSYS occupies 78.6 MB ( 9.4% )
| Schema ORDDATA occupies 16.0 MB ( 1.9% )
| Schema SYSTEM occupies 15.7 MB ( 1.9% )
| Schema WMSYS occupies 7.1 MB ( 0.9% )
| Schema DVSYS occupies 4.4 MB ( 0.5% )
| Schema CTXSYS occupies 3.8 MB ( 0.5% )
| Schema AUDSYS occupies 1.6 MB ( 0.2% )
| Schema GSMADMIN_INT occupies 0.9 MB ( 0.1% )
| Schema DBSNMP occupies 0.5 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 187.8 MB
| SM/AWR SYS 119.8 MB
| SDO MDSYS 78.6 MB
| SM/OPTSTAT SYS 68.6 MB
| SM/OTHER SYS 45.9 MB
| AO SYS 40.7 MB
| XSOQHIST SYS 40.7 MB
| ORDIM/ORDDATA ORDDATA 16.0 MB
| LOGMNR SYSTEM 13.8 MB
| SM/ADVISOR SYS 11.1 MB
| WM WMSYS 7.1 MB
| TEXT CTXSYS 3.8 MB
| SQL_MANAGEMENT_BASE SYS 2.4 MB
| SMON_SCN_TIME SYS 2.1 MB
| PL/SCOPE SYS 1.6 MB
| LOGSTDBY SYSTEM 1.5 MB
| JOB_SCHEDULER SYS 1.3 MB
| STREAMS SYS 1.0 MB
| EM_MONITORING_USER DBSNMP 0.5 MB
| ORDIM ORDSYS 0.4 MB
| AUTO_TASK SYS 0.3 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) 188.9 MB
|
******************************************
(1c) SYSAUX usage - Unregistered Schemas
******************************************
| This section displays schemas that are not registered
| in V$SYSAUX_OCCUPANTS
|
| Schema APEX_040200 occupies 178.5 MB
| Schema DVSYS occupies 4.4 MB
| Schema AUDSYS occupies 1.6 MB
| Schema GSMADMIN_INT occupies 0.9 MB
|
| Total space 185.4 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 3.4 MB
|
| Total space 3.4 MB
|
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 60 mins snapshot INTERVAL:
| AWR size/day 169.1 MB (7,217 K/snap * 24 snaps/day)
| AWR size/wk 1,184.0 MB (size_per_day * 7) per instance
|
| Estimates based on 1 snaps in past 24 hours:
| AWR size/day 7.0 MB (7,217 K/snap and 1 snaps in past 24 hours)
| AWR size/wk 49.3 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 65.6 54.7 3,949 3.9 27.0 46% : 54%
EVENTS 15.4 12.9 930 0.9 6.4 39% : 61%
SQLPLAN 9.0 7.5 542 0.5 3.7 67% : 33%
SQL 9.0 7.5 542 0.5 3.7 61% : 39%
SPACE 5.9 5.0 358 0.3 2.4 59% : 41%
ASH 3.6 3.0 215 0.2 1.5 88% : 12%
SQLBIND 0.6 0.5 38 0.0 0.3 50% : 50%
RAC 0.6 0.5 38 0.0 0.3 50% : 50%
SQLTEXT 0.6 0.5 34 0.0 0.2 89% : 11%
**********************************
(3b) Space usage within AWR Components (> 500K)
**********************************
COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
FIXED 8.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_1409006025_0 - 73% INDEX PARTITION
FIXED 7.0 WRH$_LATCH.WRH$_LATCH_1409006025_0 - 93% TABLE PARTITION
FIXED 7.0 WRH$_LATCH_PK.WRH$_LATCH_1409006025_0 - 77% INDEX PARTITION
FIXED 5.0 WRH$_SYSSTAT.WRH$_SYSSTA_1409006025_0 - 93% TABLE PARTITION
FIXED 4.0 WRH$_PARAMETER_PK.WRH$_PARAME_1409006025_0 - 77% INDEX PARTITION
FIXED 3.0 WRH$_SYSMETRIC_HISTORY - 32% TABLE
FIXED 3.0 WRH$_SYSMETRIC_HISTORY_INDEX - 38% INDEX
FIXED 3.0 WRH$_PARAMETER.WRH$_PARAME_1409006025_0 - 96% TABLE PARTITION
FIXED 2.0 WRH$_SERVICE_STAT_PK.WRH$_SERVIC_1409006025_0 - 68% INDEX PARTITION
FIXED 0.9 WRH$_MVPARAMETER_PK.WRH$_MVPARA_1409006025_0 - 64% INDEX PARTITION
FIXED 0.9 WRH$_SERVICE_STAT.WRH$_SERVIC_1409006025_0 - 85% TABLE PARTITION
FIXED 0.8 WRH$_SYSMETRIC_SUMMARY - 32% TABLE
FIXED 0.8 WRH$_ROWCACHE_SUMMARY.WRH$_ROWCAC_1409006025_0 - 85% TABLE PARTITION
FIXED 0.7 WRH$_MVPARAMETER.WRH$_MVPARA_1409006025_0 - 86% TABLE PARTITION
FIXED 0.6 WRH$_LATCH_MISSES_SUMMARY_PK.WRH$_LATCH__1409006025_0 - 59% INDEX PARTITION
FIXED 0.5 WRH$_ROWCACHE_SUMMARY_PK.WRH$_ROWCAC_1409006025_0 - 89% INDEX PARTITION
FIXED 0.5 WRH$_SGASTAT_U.WRH$_SGASTA_1409006025_0 - 60% INDEX PARTITION
EVENTS 6.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__1409006025_0 - 74% INDEX PARTITION
EVENTS 4.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__1409006025_0 - 81% TABLE PARTITION
EVENTS 2.0 WRH$_SYSTEM_EVENT_PK.WRH$_SYSTEM_1409006025_0 - 62% INDEX PARTITION
EVENTS 0.9 WRH$_SYSTEM_EVENT.WRH$_SYSTEM_1409006025_0 - 87% TABLE PARTITION
EVENTS 0.5 WRH$_ENQUEUE_STAT_PK - 24% INDEX
SQL 5.0 WRH$_SQLSTAT.WRH$_SQLSTA_1409006025_0 - 84% TABLE PARTITION
SQL 2.0 WRH$_SQLSTAT_PK.WRH$_SQLSTA_1409006025_0 - 70% INDEX PARTITION
SQL 0.9 WRH$_SQLSTAT_INDEX.WRH$_SQLSTA_1409006025_0 - 73% INDEX PARTITION
SQLPLAN 6.0 WRH$_SQL_PLAN - 35% TABLE
SQLPLAN 3.0 WRH$_SQL_PLAN_PK - 34% INDEX
SPACE 2.0 WRH$_SEG_STAT.WRH$_SEG_ST_1409006025_0 - 81% TABLE PARTITION
SPACE 1.0 WRH$_SEG_STAT_PK.WRH$_SEG_ST_1409006025_0 - 65% INDEX PARTITION
ASH 3.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_1409006025_0 - 36% TABLE PARTITION
SQLTEXT 0.5 WRH$_SQLTEXT - 68% TABLE
**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************
COMPONENT MB SEGMENT_NAME SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR 96.1 XDB.SYS_LOB0000018452C00025$$ LOBSEGMENT
NON_AWR 36.2 SYS.SYS_LOB0000005825C00005$$ LOBSEGMENT
NON_AWR 36.1 MDSYS.SYS_LOB0000083911C00006$$ LOBSEGMENT
NON_AWR 20.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
NON_AWR 17.2 SYS.SYS_LOB0000085671C00004$$ LOBSEGMENT
NON_AWR 13.0 APEX_040200.WWV_FLOW_PAGE_PLUGS TABLE
NON_AWR 12.0 XDB.XDB$RESOURCE TABLE
NON_AWR 11.0 SYS.I_WRI$_OPTSTAT_H_ST INDEX
NON_AWR 10.0 APEX_040200.WWV_FLOW_STEP_ITEMS TABLE
NON_AWR 9.0 MDSYS.SDO_CS_SRS TABLE
NON_AWR 8.2 SYS.SYS_LOB0000085656C00004$$ LOBSEGMENT
NON_AWR 8.2 APEX_040200.SYS_LOB0000088387C00007$$ LOBSEGMENT
NON_AWR 8.0 SYS.HEATMAP SYSTEM STATISTI
CS
NON_AWR 7.0 XDB.XDB$H_INDEX TABLE
NON_AWR 6.2 SYS.SYS_LOB0000007458C00038$$ LOBSEGMENT
NON_AWR 4.1 SYS.SYS_LOB0000085651C00004$$ LOBSEGMENT
NON_AWR 4.0 APEX_040200.WWV_FLOW_TEMPLATES TABLE
NON_AWR 4.0 SYSTEM.SYS_LOB0000001329C00009$$ LOBSEGMENT
NON_AWR 4.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX
NON_AWR 4.0 APEX_040200.WWV_FLOW_REGION_REPORT_COLUMN TABLE
NON_AWR 4.0 APEX_040200.WWV_FLOW_STEP_PROCESSING TABLE
NON_AWR 3.2 SYS.SYS_LOB0000007450C00004$$ LOBSEGMENT
NON_AWR 3.2 SYS.SYS_LOB0000085661C00004$$ LOBSEGMENT
NON_AWR 3.0 APEX_040200.WWV_FLOW_STEPS TABLE
NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P1184 TABLE PARTITION
NON_AWR 3.0 APEX_040200.WWV_FLOW_WORKSHEET_COLUMNS TABLE
NON_AWR 3.0 APEX_040200.WWV_FLOW_STEP_BUTTONS TABLE
NON_AWR 2.2 SYS.SYS_LOB0000005825C00004$$ LOBSEGMENT
NON_AWR 2.2 APEX_040200.SYS_LOB0000088621C00009$$ LOBSEGMENT
NON_AWR 2.2 APEX_040200.SYS_LOB0000088375C00005$$ LOBSEGMENT
NON_AWR 2.2 APEX_040200.SYS_LOB0000087805C00016$$ LOBSEGMENT
NON_AWR 2.2 SYS.SYS_LOB0000085676C00004$$ LOBSEGMENT
NON_AWR 2.2 SYS.SYS_LOB0000085666C00004$$ LOBSEGMENT
NON_AWR 2.0 SYS.WRI$_OPTSTAT_OPR_TASKS TABLE
NON_AWR 2.0 APEX_040200.WWV_FLOW_STEP_ITEMS_U_NAME INDEX
NON_AWR 2.0 APEX_040200.WWV_FLOW_PAGE_PLUGS_IDX_U1 INDEX
NON_AWR 2.0 XDB.XDB$RESOURCE_ACLOID_IDX INDEX
NON_AWR 2.0 SYS.I_WRI$_OPTSTAT_HH_ST INDEX
NON_AWR 2.0 SYS.SMON_SCN_TO_TIME_AUX CLUSTER
NON_AWR 2.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P1024 TABLE PARTITION
NON_AWR 2.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P881 TABLE PARTITION
NON_AWR 2.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P641 TABLE PARTITION
NON_AWR 2.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P441 TABLE PARTITION
NON_AWR 2.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P352 TABLE PARTITION
NON_AWR 2.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P325 TABLE PARTITION
COMPONENT MB SEGMENT_NAME SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR 2.0 APEX_040200.WWV_FLOW_WORKSHEETS TABLE
NON_AWR 2.0 APEX_040200.WWV_FLOW_FLASH_MAP_REGIONS TABLE
NON_AWR 2.0 APEX_040200.WWV_FLOW_LIST_ITEMS TABLE
NON_AWR 2.0 APEX_040200.WWV_FLOW_PAGE_PLUG_TEMPLATES TABLE
NON_AWR 2.0 APEX_040200.WWV_FLOW_STEP_VALIDATIONS TABLE
NON_AWR 2.0 APEX_040200.WWV_FLOW_STEP_ITEM_HELP TABLE
NON_AWR 2.0 APEX_040200.WWV_FLOW_STEP_BRANCHES TABLE
NON_AWR 2.0 MDSYS.SDO_COORD_REF_SYS TABLE
NON_AWR 2.0 XDB.XDB$ELEMENT TABLE
NON_AWR 2.0 XDB.XDB$H_LINK TABLE
NON_AWR 1.2 SYS.SYS_LOB0000006989C00040$$ LOBSEGMENT
NON_AWR 1.2 APEX_040200.SYS_LOB0000090006C00010$$ LOBSEGMENT
NON_AWR 1.2 APEX_040200.SYS_LOB0000088637C00007$$ LOBSEGMENT
NON_AWR 1.2 APEX_040200.SYS_LOB0000088498C00006$$ LOBSEGMENT
NON_AWR 1.2 APEX_040200.SYS_LOB0000088473C00019$$ LOBSEGMENT
NON_AWR 1.2 APEX_040200.SYS_LOB0000088381C00021$$ LOBSEGMENT
NON_AWR 1.2 APEX_040200.SYS_LOB0000087787C00025$$ LOBSEGMENT
NON_AWR 1.2 APEX_040200.SYS_LOB0000087787C00022$$ LOBSEGMENT
NON_AWR 1.2 APEX_040200.SYS_LOB0000087716C00024$$ LOBSEGMENT
NON_AWR 1.2 MDSYS.SYS_LOB0000086671C00003$$ LOBSEGMENT
NON_AWR 1.2 MDSYS.SYS_LOB0000084476C00003$$ LOBSEGMENT
NON_AWR 1.2 SYS.SYS_LOB0000007264C00008$$ LOBSEGMENT
NON_AWR 1.1 XDB.SYS_LOB0000018398C00023$$ LOBSEGMENT
NON_AWR 1.1 SYS.SYS_LOB0000005854C00009$$.SYS_LOB_P1163 LOB PARTITION
NON_AWR 1.1 SYS.SYS_LOB0000005854C00009$$.SYS_LOB_P1043 LOB PARTITION
NON_AWR 1.1 AUDSYS.SYS_LOB0000091751C00014$$.SYS_LOB_P186 LOB PARTITION
NON_AWR 1.1 APEX_040200.SYS_LOB0000088484C00007$$ LOBSEGMENT
NON_AWR 1.1 APEX_040200.SYS_LOB0000087859C00010$$ LOBSEGMENT
NON_AWR 1.1 APEX_040200.SYS_LOB0000087749C00007$$ LOBSEGMENT
NON_AWR 1.1 ORDDATA.SYS_LOB0000078094C00005$$ LOBSEGMENT
NON_AWR 1.1 ORDDATA.SYS_LOB0000077911C00005$$ LOBSEGMENT
NON_AWR 1.0 SYS.SYS$SERVICE_METRICS_TAB TABLE
NON_AWR 1.0 SYS.LTXID_TRANS.LTXID_TRANS_1 TABLE PARTITION
NON_AWR 1.0 APEX_040200.WWV_FLOW_INSTALL_SCRIPTS TABLE
NON_AWR 0.9 APEX_040200.WWV_FLOW_LIST_TEMPLATES TABLE
NON_AWR 0.9 APEX_040200.WWV_FLOW_PAGE_PLUGS_IDX3 INDEX
NON_AWR 0.9 APEX_040200.WWV_FLOW_LIST_OF_VALUES_DATA TABLE
NON_AWR 0.9 APEX_040200.WWV_FLOW_STEP_ITEM_IDX3 INDEX
NON_AWR 0.9 XDB.XDB_PK_H_LINK INDEX
NON_AWR 0.8 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P338 TABLE PARTITION
NON_AWR 0.8 APEX_040200.WWV_FLOW_ROW_TEMPLATES TABLE
NON_AWR 0.8 APEX_040200.WWV_FLOW_STEP_ITEM_IDX4 INDEX
NON_AWR 0.8 APEX_040200.WWV_FLOW_STEP_ITEMS_PK INDEX
NON_AWR 0.8 APEX_040200.WWV_FLOW_PAGE_PLUGS_PK INDEX
NON_AWR 0.8 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P404 TABLE PARTITION
NON_AWR 0.8 APEX_040200.WWV_FLOW_MESSAGES$ TABLE
NON_AWR 0.8 APEX_040200.WWV_FLOW_CAL_TEMPLATES TABLE
COMPONENT MB SEGMENT_NAME SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR 0.7 SYS.WRI$_ADV_SQLT_PLANS TABLE
NON_AWR 0.7 APEX_040200.WWV_FLOW_PAGE_PLUGS_FK INDEX
NON_AWR 0.7 APEX_040200.WWV_FLOW_STEP_BUTTONS_FKIDX INDEX
NON_AWR 0.7 APEX_040200.WWV_FLOW_STEP_BUTTONS_IDX2 INDEX
NON_AWR 0.7 SYS.I_WRI$_OPTSTAT_OPR_TASKS_TGST INDEX
NON_AWR 0.7 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P764 TABLE PARTITION
NON_AWR 0.7 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P664 TABLE PARTITION
NON_AWR 0.6 MDSYS.SDO_COORD_OPS TABLE
**********************************
(5a) AWR snapshots - last 50
**********************************
Total snapshots in DB 1409006025 Instance 1 = 17
DBID SNAP_ID INST FLUSH_ELAPSED ENDTM STARTUP_TIME STATUS ERRCNT
---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------
1409006025 145 1 +00000 00:00:05.6 18:30:04 (07/11) 18:50:28 (07/08) 0 0
1409006025 146 1 +00000 00:00:39.9 19:15:19 (07/13) 18:50:28 (07/08) 0 0
1409006025 147 1 +00000 00:00:03.7 20:30:15 (07/13) 18:50:28 (07/08) 0 0
1409006025 148 1 +00000 00:00:30.3 17:14:46 (07/14) 18:50:28 (07/08) 0 0
1409006025 149 1 +00000 00:00:07.2 18:58:34 (07/14) 18:50:28 (07/08) 0 0
1409006025 150 1 +00000 00:00:02.6 19:30:36 (07/14) 18:50:28 (07/08) 0 0
1409006025 151 1 +00000 00:00:02.2 20:30:41 (07/14) 18:50:28 (07/08) 0 0
1409006025 152 1 +00000 00:00:15.7 17:33:56 (07/15) 18:50:28 (07/08) 0 0
1409006025 153 1 +00000 00:00:03.1 18:30:59 (07/15) 18:50:28 (07/08) 0 0
1409006025 154 1 +00000 00:00:01.1 19:30:05 (07/15) 18:50:28 (07/08) 0 0
1409006025 155 1 +00000 00:00:14.2 20:12:36 (07/17) 18:50:28 (07/08) 0 0
1409006025 156 1 +00000 00:00:11.5 22:41:56 (07/17) 18:50:28 (07/08) 0 0
1409006025 157 1 +00000 00:01:08.9 09:10:22 (07/18) 18:50:28 (07/08) 0 0
1409006025 158 1 +00000 00:00:20.5 13:04:56 (07/18) 18:50:28 (07/08) 0 0
1409006025 159 1 +00000 00:00:22.8 19:12:11 (07/18) 18:50:28 (07/08) 0 0
1409006025 160 1 18:07:32 (07/19) 18:50:28 (07/08) 1 0
1409006025 161 1 +00000 00:00:08.6 18:45:12 (07/20) 18:50:28 (07/08) 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
---------- ----- ---------- ----------------- ------ -----------
1409006025 1 145 18:30:04 (07/11) 0 0
**********************************
(6) AWR Control Settings - interval, retention
**********************************
DBID LSNAPID LSPLITID LSNAPTIME LPURGETIME FLAG INTERVAL RETENTION VRSN
----------- -------- -------- -------------- -------------- ----- ----------------- ----------------- ----
1409006025 161 0 07/20 18:45:21 07/19 18:07:34 2 +00000 01:00:00.0 +00008 00:00:00.0 10
**********************************
(7a) AWR Contents - row counts for each snapshots
**********************************
SNAP_ID INST ASH SQL SQBND FILES SEGST SYSEVT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
145 1 35 102 407 8 89 110
146 1 7 93 270 8 87 112
147 1 70 94 268 8 86 112
148 1 31 99 290 8 90 112
149 1 61 94 594 8 88 112
150 1 10 96 260 8 89 113
151 1 9 100 295 8 87 113
152 1 2 97 333 8 90 113
153 1 17 93 588 8 88 113
154 1 5 99 269 8 89 113
155 1 4 104 308 8 87 113
156 1 22 93 676 8 89 114
157 1 14 94 621 8 87 115
158 1 105 108 494 8 86 115
159 1 35 90 269 8 88 115
160 1 45 0 0 8 0 115
161 1 48 99 285 8 91 116
**********************************
(7b) AWR Contents - average row counts per snapshot
**********************************
SNAP_COUNT INST ASH SQLSTAT SQLBIND FILES SEGSTAT SYSEVENT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
17 1 30.59 91.47 366.29 8 83 113.29
**********************************
(7c) AWR total item counts - names, text, plans
**********************************
SQLTEXT SQLPLAN SQLBMETA SEGOBJ DATAFILE TEMPFILE
---------- ---------- ---------- ---------- ---------- ----------
529 7203 2124 404 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/SPM Evolve 2/SYS_AUTO_SPM_EVOLVE_TASK 09:15:50 (06/28) 0 ########## AUTO COMPLETED
SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK 09:15:50 (06/28) 53 ########## AUTO COMPLETED
SYS/ADDM 313/ADDM:1409006025_1_118 23:30:58 (07/02) 2 2 AUTO COMPLETED
SYS/ADDM 314/ADDM:1409006025_1_119 00:30:05 (07/03) 1 1 AUTO COMPLETED
SYS/ADDM 315/ADDM:1409006025_1_120 01:30:09 (07/03) 1 1 AUTO COMPLETED
SYS/ADDM 316/ADDM:1409006025_1_121 21:37:10 (07/03) 1 1 AUTO COMPLETED
SYS/Segment Ad 322/SYS_AUTO_SPCADV_120022030720 22:00:12 (07/03) 10 11 CMD COMPLETED
15
SYS/ADDM 332/ADDM:1409006025_1_122 22:30:09 (07/03) 4 5 AUTO COMPLETED
SYS/ADDM 342/ADDM:1409006025_1_125 17:31:01 (07/04) 2 2 AUTO COMPLETED
SYS/ADDM 352/ADDM:1409006025_1_126 18:30:09 (07/04) 1 1 AUTO COMPLETED
SYS/ADDM 362/ADDM:1409006025_1_127 21:02:02 (07/04) 1 2 AUTO COMPLETED
SYS/ADDM 372/ADDM:1409006025_1_128 18:54:00 (07/06) 1 1 AUTO COMPLETED
SYS/ADDM 382/ADDM:1409006025_1_129 19:30:43 (07/06) 1 1 AUTO COMPLETED
SYS/ADDM 392/ADDM:1409006025_1_130 20:30:46 (07/06) 0 0 AUTO COMPLETED
SYS/ADDM 402/ADDM:1409006025_1_131 21:41:45 (07/06) 2 2 AUTO COMPLETED
SYS/Segment Ad 412/SYS_AUTO_SPCADV_100022060720 22:00:11 (07/06) 2 2 CMD COMPLETED
15
SYS/ADDM 422/ADDM:1409006025_1_132 18:15:03 (07/07) 4 5 AUTO COMPLETED
SYS/ADDM 432/ADDM:1409006025_1_133 19:30:54 (07/07) 0 0 AUTO COMPLETED
SYS/ADDM 433/ADDM:1409006025_1_134 18:29:02 (07/08) 0 0 AUTO COMPLETED
SYS/ADDM 442/ADDM:1409006025_1_136 20:35:31 (07/08) 5 5 AUTO COMPLETED
SYS/ADDM 452/ADDM:1409006025_1_137 21:30:31 (07/08) 1 1 AUTO COMPLETED
SYS/Segment Ad 462/SYS_AUTO_SPCADV_110022080720 22:00:12 (07/08) 1 1 CMD COMPLETED
15
SYS/ADDM 472/ADDM:1409006025_1_138 16:40:14 (07/09) 7 8 AUTO COMPLETED
SYS/ADDM 482/ADDM:1409006025_1_139 17:30:42 (07/09) 1 1 AUTO COMPLETED
SYS/ADDM 492/ADDM:1409006025_1_140 18:30:43 (07/09) 1 1 AUTO COMPLETED
SYS/ADDM 502/ADDM:1409006025_1_141 19:30:50 (07/09) 1 1 AUTO COMPLETED
SYS/ADDM 512/ADDM:1409006025_1_142 19:49:18 (07/10) 1 1 AUTO COMPLETED
SYS/ADDM 522/ADDM:1409006025_1_143 10:48:44 (07/11) 1 2 AUTO COMPLETED
SYS/Segment Ad 523/SYS_AUTO_SPCADV_524810110720 10:48:52 (07/11) 4 5 CMD COMPLETED
15
SYS/Segment Ad 532/SYS_AUTO_SPCADV_120517110720 17:05:13 (07/11) 1 1 CMD COMPLETED
15
SYS/ADDM 533/ADDM:1409006025_1_144 17:05:19 (07/11) 6 6 AUTO COMPLETED
SYS/ADDM 542/ADDM:1409006025_1_145 18:30:10 (07/11) 0 1 AUTO COMPLETED
SYS/ADDM 543/ADDM:1409006025_1_146 19:16:02 (07/13) 7 7 AUTO COMPLETED
SYS/ADDM 552/ADDM:1409006025_1_147 20:30:19 (07/13) 1 1 AUTO COMPLETED
SYS/ADDM 553/ADDM:1409006025_1_148 17:15:17 (07/14) 1 1 AUTO COMPLETED
SYS/ADDM 554/ADDM:1409006025_1_149 18:58:42 (07/14) 2 2 AUTO COMPLETED
SYS/ADDM 562/ADDM:1409006025_1_150 19:30:40 (07/14) 0 0 AUTO COMPLETED
SYS/ADDM 563/ADDM:1409006025_1_151 20:30:44 (07/14) 0 0 AUTO COMPLETED
SYS/ADDM 564/ADDM:1409006025_1_152 17:34:12 (07/15) 2 3 AUTO COMPLETED
SYS/ADDM 565/ADDM:1409006025_1_153 18:31:03 (07/15) 0 1 AUTO COMPLETED
SYS/ADDM 566/ADDM:1409006025_1_154 19:30:07 (07/15) 0 1 AUTO COMPLETED
SYS/ADDM 567/ADDM:1409006025_1_155 20:12:52 (07/17) 1 2 AUTO COMPLETED
SYS/ADDM 572/ADDM:1409006025_1_156 22:42:08 (07/17) 1 1 AUTO COMPLETED
SYS/Segment Ad 573/SYS_AUTO_SPCADV_124222170720 22:42:12 (07/17) CMD EXECUTING
15
SYS/Segment Ad 582/SYS_AUTO_SPCADV_081109180720 09:11:08 (07/18) 38 39 CMD COMPLETED
15
SYS/ADDM 583/ADDM:1409006025_1_157 09:11:32 (07/18) 13 14 AUTO COMPLETED
SYS/ADDM 592/ADDM:1409006025_1_158 13:05:18 (07/18) 10 10 AUTO COMPLETED
SYS/Segment Ad 593/SYS_AUTO_SPCADV_021513180720 13:15:03 (07/18) 1 1 CMD COMPLETED
15
SYS/ADDM 602/ADDM:1409006025_1_159 19:12:35 (07/18) 1 2 AUTO COMPLETED
SYS/ADDM 612/ADDM:1409006025_1_161 18:45:22 (07/20) 1 2 AUTO FATAL ERROR
**********************************
(2) Advisor Task - Oldest 5
**********************************
OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
SYS/ADDM 69/ADDM:1409006025_1_52 15:12:30 (06/18) 0 0 AUTO COMPLETED
SYS/ADDM 70/ADDM:1409006025_1_53 22:40:48 (06/18) 0 0 AUTO COMPLETED
SYS/Segment Ad 71/SYS_AUTO_SPCADV_5340221806201 22:40:53 (06/18) 0 0 CMD COMPLETED
5
SYS/ADDM 79/ADDM:1409006025_1_56 07:47:22 (06/20) 4 4 AUTO COMPLETED
SYS/Segment Ad 80/SYS_AUTO_SPCADV_3147072006201 07:47:32 (06/20) 1 1 CMD COMPLETED
5
**********************************
(3) Advisor Tasks With Errors - Last 50
**********************************
OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
TASK_DESC
--------------------------------------------------------------------------------------------------------------
ERROR_MSG
--------------------------------------------------------------------------------------------------------------
SYS/Segment Ad 573/SYS_AUTO_SPCADV_124222170720 22:42:12 (07/17) CMD EXECUTING
15
Description: Auto Space Advisor
Error Msg :
SYS/ADDM 612/ADDM:1409006025_1_161 18:45:22 (07/20) 1 2 AUTO FATAL ERROR
Description: ADDM auto run: snapshots [160, 161], instance 1, database id 1409006025
Error Msg : ORA-13707: Either the start snapshot or the end snapshot is incomplete or missing key statistic
s.
########################################################
(III) ASH Usage Info
########################################################
**********************************
(1a) ASH histogram (past 3 days)
**********************************
NUM_ACTIVE_SESSIONS NUM_SAMPLES
-------------------- ------------
0000 - 0004 89
0005 - 0009 14
0010 - 0014 4
**********************************
(1b) ASH histogram (past 1 day)
**********************************
**********************************
(2a) ASH details (past 3 days)
**********************************
INST MIN_TIME MAX_TIME NUM_SAMPLES NUM_ROWS AVG_ACTIVE
---- ---------------- ---------------- ------------ ------------ ----------
1 20:12:37 (07/17) 18:44:37 (07/20) 1,172 269 0.23
**********************************
(2b) ASH details (past 1 day)
**********************************
**********************************
(2c) ASH sessions (Fg Vs Bg) (past 1 day across all instances in RAC)
**********************************
Foreground %
Background %
MMNL %
End of Report
Report written to text
Hi,
ReplyDeleteWhat was the solution for Error Msg : ORA-13707: Either the start snapshot or the end snapshot is incomplete or missing key statistic?