SYSAUX TBS - AWR INFO Report 检查问题点
SQL> conn / as sysdba
SQL> @?/rdbms/admin/awrinfo.sql
~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~
Report generated at
15:27:36 on Dec 14, 2017 ( Thursday ) in Timezone +08:00
Warning: Non Default AWR Setting!
--------------------------------------------------------------------------------
Snapshot interval is 60 minutes and Retention is 8 days
Interesting Info: Significant Number of ASH On-demand Flushes!
--------------------------------------------------------------------------------
ASH On-demand Flushing % = ( 684/5680 ) = 12.0%
DB_ID DB_NAME HOST_PLATFORM INST STARTUP_TIME LAST_ASH_SID PAR
------------ --------- ---------------------------------------- ----- ----------------- ------------ ---
* 235923594 ZMOSDEV zmosdev1 - HP-UX IA (64-bit) 1 11:27:47 (05/20) 85934381 NO
########################################################
(I) AWR Snapshots Information
########################################################
*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size 7,860.4 MB ( 98% of 8,059.0 MB MAX with AUTOEXTEND OFF )
|
| Schema SYS occupies 7,484.1 MB ( 95.2% )
| Schema XDB occupies 125.9 MB ( 1.6% )
| Schema APEX_030200 occupies 84.4 MB ( 1.1% )
| Schema MDSYS occupies 74.3 MB ( 0.9% )
| Schema SYSMAN occupies 46.0 MB ( 0.6% )
| Schema SYSTEM occupies 14.8 MB ( 0.2% )
| Schema ORDDATA occupies 13.6 MB ( 0.2% )
| Schema OLAPSYS occupies 5.1 MB ( 0.1% )
| Schema EXFSYS occupies 3.6 MB ( 0.0% )
| Schema CTXSYS occupies 3.6 MB ( 0.0% )
| Schema WMSYS occupies 3.5 MB ( 0.0% )
| Schema DBSNMP occupies 0.9 MB ( 0.0% )
| 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 7,112.9 MB
| XDB XDB 125.9 MB
| SMON_SCN_TIME SYS 124.0 MB
| SM/OPTSTAT SYS 97.1 MB
| SDO MDSYS 74.3 MB
| SM/ADVISOR SYS 60.6 MB
| EM SYSMAN 46.0 MB
| AO SYS 38.3 MB
| XSOQHIST SYS 38.3 MB
| JOB_SCHEDULER SYS 17.0 MB
| ORDIM/ORDDATA ORDDATA 13.6 MB
| LOGMNR SYSTEM 13.4 MB
| SM/OTHER SYS 7.7 MB
| XSAMD OLAPSYS 5.1 MB
| EXPRESSION_FILTER EXFSYS 3.6 MB
| TEXT CTXSYS 3.6 MB
| WM WMSYS 3.5 MB
| SQL_MANAGEMENT_BASE SYS 1.7 MB
| PL/SCOPE SYS 1.6 MB
| LOGSTDBY SYSTEM 1.4 MB
| STREAMS SYS 1.0 MB
| EM_MONITORING_USER DBSNMP 0.9 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) 68.1 MB
|
******************************************
(1c) SYSAUX usage - Unregistered Schemas
******************************************
| This section displays schemas that are not registered
| in V$SYSAUX_OCCUPANTS
|
| Schema APEX_030200 occupies 84.4 MB
|
| Total space 84.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 -16.4 MB
|
| Total space -16.4 MB
|
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 60 mins snapshot INTERVAL:
| AWR size/day 857.8 MB (36,601 K/snap * 24 snaps/day)
| AWR size/wk 6,004.9 MB (size_per_day * 7) per instance
|
| Estimates based on 24 snaps in past 24 hours:
| AWR size/day 857.8 MB (36,601 K/snap and 24 snaps in past 24 hours)
| AWR size/wk 6,004.9 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 4,116.4 57.9 21,182 496.5 3,475.2 52% : 48%
EVENTS 1,359.9 19.1 6,998 164.0 1,148.1 51% : 49%
SQL 777.5 10.9 4,001 93.8 656.4 63% : 37%
SPACE 432.9 6.1 2,227 52.2 365.4 71% : 29%
ASH 390.3 5.5 2,008 47.1 329.5 84% : 16%
SQLPLAN 14.0 0.2 72 1.7 11.8 71% : 29%
SQLBIND 4.0 0.1 21 0.5 3.4 50% : 50%
SQLTEXT 2.1 0.0 11 0.3 1.8 94% : 6%
RAC 0.6 0.0 3 0.1 0.5 50% : 50%
**********************************
(3b) Space usage within AWR Components (> 500K)
**********************************
COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
FIXED 744.0 WRH$_LATCH.WRH$_LATCH_235923594_0 - 99% TABLE PARTITION
FIXED 512.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_235923594_0 - 99% INDEX PARTITION
FIXED 488.0 WRH$_SYSSTAT.WRH$_SYSSTA_235923594_0 - 99% TABLE PARTITION
FIXED 456.0 WRH$_PARAMETER_PK.WRH$_PARAME_235923594_0 - 71% INDEX PARTITION
FIXED 445.0 WRH$_LATCH_PK.WRH$_LATCH_235923594_0 - 99% INDEX PARTITION
FIXED 376.0 WRH$_PARAMETER.WRH$_PARAME_235923594_0 - 98% TABLE PARTITION
FIXED 152.0 WRH$_SERVICE_STAT_PK.WRH$_SERVIC_235923594_0 - 64% INDEX PARTITION
FIXED 104.0 WRH$_LATCH_MISSES_SUMMARY_PK.WRH$_LATCH__235923594_0 - 99% INDEX PARTITION
FIXED 104.0 WRH$_LATCH_MISSES_SUMMARY.WRH$_LATCH__235923594_0 - 99% TABLE PARTITION
FIXED 96.0 WRH$_ROWCACHE_SUMMARY.WRH$_ROWCAC_235923594_0 - 93% TABLE PARTITION
FIXED 80.0 WRH$_MVPARAMETER.WRH$_MVPARA_235923594_0 - 93% TABLE PARTITION
FIXED 80.0 WRH$_SERVICE_STAT.WRH$_SERVIC_235923594_0 - 95% TABLE PARTITION
FIXED 60.0 WRH$_MVPARAMETER_PK.WRH$_MVPARA_235923594_0 - 97% INDEX PARTITION
FIXED 60.0 WRH$_ROWCACHE_SUMMARY_PK.WRH$_ROWCAC_235923594_0 - 98% INDEX PARTITION
FIXED 48.0 WRH$_SERVICE_WAIT_CLASS_PK.WRH$_SERVIC_235923594_0 - 65% INDEX PARTITION
FIXED 38.0 WRH$_DB_CACHE_ADVICE.WRH$_DB_CAC_235923594_0 - 97% TABLE PARTITION
FIXED 36.0 WRH$_SERVICE_WAIT_CLASS.WRH$_SERVIC_235923594_0 - 98% TABLE PARTITION
FIXED 25.0 WRH$_DB_CACHE_ADVICE_PK.WRH$_DB_CAC_235923594_0 - 75% INDEX PARTITION
FIXED 24.0 WRH$_SGASTAT_U.WRH$_SGASTA_235923594_0 - 98% INDEX PARTITION
FIXED 24.0 WRH$_SGASTAT.WRH$_SGASTA_235923594_0 - 97% TABLE PARTITION
FIXED 18.0 WRH$_OSSTAT.WRH$_OSSTAT_235923594_0 - 94% TABLE PARTITION
FIXED 17.0 WRH$_WAITSTAT_PK.WRH$_WAITST_235923594_0 - 98% INDEX PARTITION
FIXED 17.0 WRH$_WAITSTAT.WRH$_WAITST_235923594_0 - 96% TABLE PARTITION
FIXED 17.0 WRH$_SYS_TIME_MODEL.WRH$_SYS_TI_235923594_0 - 96% TABLE PARTITION
FIXED 17.0 WRH$_SYSMETRIC_HISTORY - 76% TABLE
FIXED 17.0 WRH$_OSSTAT_PK.WRH$_OSSTAT_235923594_0 - 98% INDEX PARTITION
FIXED 15.0 WRH$_SYS_TIME_MODEL_PK.WRH$_SYS_TI_235923594_0 - 98% INDEX PARTITION
FIXED 12.0 WRH$_SYSMETRIC_HISTORY_INDEX - 83% INDEX
FIXED 8.0 WRH$_SYSMETRIC_SUMMARY - 34% TABLE
FIXED 2.0 WRH$_SYSMETRIC_SUMMARY_INDEX - 57% INDEX
EVENTS 568.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__235923594_0 - 99% INDEX PARTITION
EVENTS 544.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__235923594_0 - 99% TABLE PARTITION
EVENTS 144.0 WRH$_SYSTEM_EVENT.WRH$_SYSTEM_235923594_0 - 97% TABLE PARTITION
EVENTS 96.0 WRH$_SYSTEM_EVENT_PK.WRH$_SYSTEM_235923594_0 - 99% INDEX PARTITION
EVENTS 2.0 WRH$_BG_EVENT_SUMMARY - 39% TABLE
EVENTS 2.0 WRH$_ENQUEUE_STAT - 52% TABLE
EVENTS 1.0 WRH$_ENQUEUE_STAT_PK - 76% INDEX
EVENTS 0.9 WRH$_LIBRARYCACHE - 48% TABLE
EVENTS 0.7 WRH$_BG_EVENT_SUMMARY_PK - 83% INDEX
EVENTS 0.5 WRH$_LIBRARYCACHE_PK - 50% INDEX
SQL 488.0 WRH$_SQLSTAT.WRH$_SQLSTA_235923594_0 - 99% TABLE PARTITION
SQL 176.0 WRH$_SQLSTAT_PK.WRH$_SQLSTA_235923594_0 - 65% INDEX PARTITION
SQL 112.0 WRH$_SQLSTAT_INDEX.WRH$_SQLSTA_235923594_0 - 70% INDEX PARTITION
SPACE 272.0 WRH$_SEG_STAT.WRH$_SEG_ST_235923594_0 - 99% TABLE PARTITION
SPACE 112.0 WRH$_SEG_STAT_PK.WRH$_SEG_ST_235923594_0 - 80% INDEX PARTITION
SPACE 31.0 WRH$_TABLESPACE_STAT.WRH$_TABLES_235923594_0 - 96% TABLE PARTITION
SPACE 12.0 WRH$_TABLESPACE_STAT_PK.WRH$_TABLES_235923594_0 - 98% INDEX PARTITION
COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
SPACE 0.8 WRH$_IOSTAT_FILETYPE - 33% TABLE
SPACE 0.7 WRH$_TABLESPACE_SPACE_USAGE - 45% TABLE
ASH 328.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_235923594_0 - 97% TABLE PARTITION
ASH 62.0 WRH$_ACTIVE_SESSION_HISTORY_PK.WRH$_ACTIVE_235923594_0 - 72% INDEX PARTITION
SQLPLAN 10.0 WRH$_SQL_PLAN - 92% TABLE
SQLPLAN 4.0 WRH$_SQL_PLAN_PK - 53% INDEX
SQLBIND 2.0 WRH$_SQL_BIND_METADATA - 54% TABLE
SQLBIND 2.0 WRH$_SQL_BIND_METADATA_PK - 42% INDEX
SQLTEXT 2.0 WRH$_SQLTEXT - 86% TABLE
**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************
COMPONENT MB SEGMENT_NAME SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR 120.0 SYS.SMON_SCN_TO_TIME_AUX CLUSTER
NON_AWR 53.2 XDB.SYS_LOB0000069494C00025$$ LOBSEGMENT
NON_AWR 35.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
NON_AWR 26.0 MDSYS.SYS_LOB0000075534C00006$$ LOBSEGMENT
NON_AWR 24.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY TABLE
NON_AWR 16.2 SYS.SYS_LOB0000077409C00004$$ LOBSEGMENT
NON_AWR 16.0 SYS.I_WRI$_OPTSTAT_H_ST INDEX
NON_AWR 14.0 SYS.SCHEDULER$_EVENT_LOG TABLE
NON_AWR 12.0 SYS.SYS_LOB0000006417C00038$$ LOBSEGMENT
NON_AWR 9.0 SYS.WRI$_ADV_SQLT_PLANS TABLE
NON_AWR 8.0 XDB.XDB$RESOURCE TABLE
NON_AWR 7.2 SYS.SYS_LOB0000077394C00004$$ LOBSEGMENT
NON_AWR 7.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE
NON_AWR 7.0 MDSYS.SDO_CS_SRS TABLE
NON_AWR 6.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX
NON_AWR 6.0 APEX_030200.WWV_FLOW_PAGE_PLUGS TABLE
NON_AWR 6.0 SYS.SYS_LOB0000005977C00040$$ LOBSEGMENT
NON_AWR 6.0 SYS.WRI$_ADV_MSG_GRPS_IDX_01 INDEX
NON_AWR 6.0 SYS.WRI$_ADV_MESSAGE_GROUPS TABLE
NON_AWR 6.0 SYS.WRI$_ADV_RATIONALE TABLE
NON_AWR 5.0 SYS.WRI$_ADV_MESSAGE_GROUPS_PK INDEX
NON_AWR 5.0 APEX_030200.WWV_FLOW_DICTIONARY$CAPED INDEX
NON_AWR 5.0 APEX_030200.WWV_FLOW_STEP_ITEMS TABLE
NON_AWR 4.2 SYS.SYS_LOB0000077399C00004$$ LOBSEGMENT
NON_AWR 4.0 SYSTEM.SYS_LOB0000001180C00009$$ LOBSEGMENT
NON_AWR 4.0 APEX_030200.WWV_FLOW_DICTIONARY$ TABLE
NON_AWR 4.0 SYS.WRI$_ADV_PARAMETERS_PK INDEX
NON_AWR 3.2 SYS.SYS_LOB0000077389C00004$$ LOBSEGMENT
NON_AWR 3.0 SYS.I_WRI$_OPTSTAT_HH_ST INDEX
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 APEX_030200.SYS_LOB0000084289C00010$$ LOBSEGMENT
NON_AWR 3.0 APEX_030200.WWV_FLOW_STEP_PROCESSING TABLE
NON_AWR 3.0 XDB.XDB$H_INDEX TABLE
NON_AWR 3.0 SYS.SYS_LOB0000006409C00004$$ LOBSEGMENT
NON_AWR 3.0 SYS.WRI$_ADV_PARAMETERS TABLE
NON_AWR 2.2 SYS.SYS_LOB0000077404C00004$$ LOBSEGMENT
NON_AWR 2.2 SYS.SYS_LOB0000077414C00004$$ LOBSEGMENT
NON_AWR 2.0 SYS.SMON_SCN_TIME_TIM_IDX INDEX
NON_AWR 2.0 APEX_030200.WWV_FLOW_DICTIONARY$_FKIDX INDEX
NON_AWR 2.0 APEX_030200.WWV_FLOW_REGION_REPORT_COLUMN TABLE
NON_AWR 2.0 APEX_030200.SYS_LOB0000084235C00009$$ LOBSEGMENT
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 2.0 SYS.I_SCHEDULER_JOB_RUN_DETAILS INDEX
COMPONENT MB SEGMENT_NAME SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR 2.0 SYS.SCHEDULER$_INSTANCE_PK INDEX
NON_AWR 2.0 SYS.WRI$_ADV_SQLT_PLAN_STATS TABLE
NON_AWR 2.0 SYS.SYS_LOB0000005160C00005$$ LOBSEGMENT
NON_AWR 2.0 SYS.SMON_SCN_TIME_SCN_IDX INDEX
NON_AWR 1.2 ORDDATA.SYS_LOB0000070829C00005$$ LOBSEGMENT
NON_AWR 1.2 ORDDATA.SYS_LOB0000070977C00005$$ LOBSEGMENT
NON_AWR 1.0 SYS.SYS$SERVICE_METRICS_TAB TABLE
NON_AWR 1.0 SYSMAN.MGMT_METRICS_IDX_01 INDEX
NON_AWR 1.0 SYSMAN.MGMT_METRICS_PK INDEX
NON_AWR 1.0 XDB.XDB$H_LINK TABLE
NON_AWR 0.9 SYSMAN.MGMT_METRICS_IDX_03 INDEX
NON_AWR 0.9 APEX_030200.WWV_FLOW_STEPS TABLE
NON_AWR 0.8 SYS.SCHEDULER$_JOB_RUN_DETAILS TABLE
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.7 SYS.WRI$_OPTSTAT_IND_HISTORY TABLE
NON_AWR 0.7 SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST INDEX
NON_AWR 0.6 SYS.I_WRI$_OPTSTAT_TAB_OBJ#_ST INDEX
NON_AWR 0.6 APEX_030200.WWV_FLOW_STEP_ITEMS_U_NAME 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 XDB.XDB$COMPLEX_TYPE TABLE
NON_AWR 0.6 XDB.XDB_PK_H_LINK INDEX
NON_AWR 0.6 SYS.WRI$_ADV_SQLT_RTN_PLAN_PK INDEX
NON_AWR 0.6 SYS.WRI$_ADV_SQLT_PLAN_HASH_01 INDEX
NON_AWR 0.6 SYS.AW_OBJ$ TABLE
NON_AWR 0.6 SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS TABLE
NON_AWR 0.6 SYSMAN.MGMT_POLICIES TABLE
NON_AWR 0.6 SYS.WRI$_ADV_OBJECTS TABLE
NON_AWR 0.5 SYS.WRI$_ADV_RATIONALE_PK INDEX
NON_AWR 0.5 SYS.WRI$_ADV_SQLT_PLAN_HASH_PK INDEX
NON_AWR 0.5 SYS.WRI$_ADV_SQLT_RTN_PLAN TABLE
NON_AWR 0.5 SYSMAN.MGMT_JOB_STEP_PARAMS TABLE
NON_AWR 0.5 SYSMAN.SYS_LOB0000081538C00004$$ LOBSEGMENT
NON_AWR 0.5 APEX_030200.WWV_FLOW_LIST_OF_VALUES_DATA TABLE
**********************************
(5a) AWR snapshots - last 50
**********************************
Total snapshots in DB 235923594 Instance 1 = 199
DBID SNAP_ID INST FLUSH_ELAPSED ENDTM STARTUP_TIME STATUS ERRCNT
---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------
235923594 24091 1 +00000 00:00:03.1 14:00:32 (12/12) 11:27:47 (05/20) 0 0
235923594 24092 1 +00000 00:00:03.2 15:00:45 (12/12) 11:27:47 (05/20) 0 0
235923594 24093 1 +00000 00:00:03.4 16:00:58 (12/12) 11:27:47 (05/20) 0 0
235923594 24094 1 +00000 00:00:01.9 17:00:11 (12/12) 11:27:47 (05/20) 0 0
235923594 24095 1 +00000 00:00:02.1 18:00:23 (12/12) 11:27:47 (05/20) 0 0
235923594 24096 1 +00000 00:00:03.3 19:00:36 (12/12) 11:27:47 (05/20) 0 0
235923594 24097 1 +00000 00:00:02.6 20:00:49 (12/12) 11:27:47 (05/20) 0 0
235923594 24098 1 +00000 00:00:03.1 21:00:01 (12/12) 11:27:47 (05/20) 0 0
235923594 24099 1 +00000 00:00:03.3 22:00:14 (12/12) 11:27:47 (05/20) 0 0
235923594 24100 1 +00000 00:00:03.6 23:00:27 (12/12) 11:27:47 (05/20) 0 0
235923594 24101 1 +00000 00:00:03.9 00:00:40 (12/13) 11:27:47 (05/20) 0 0
235923594 24102 1 +00000 00:00:03.1 01:00:52 (12/13) 11:27:47 (05/20) 0 0
235923594 24103 1 +00000 00:00:03.5 02:00:05 (12/13) 11:27:47 (05/20) 0 0
235923594 24104 1 +00000 00:00:03.6 03:00:18 (12/13) 11:27:47 (05/20) 0 0
235923594 24105 1 +00000 00:00:02.8 04:00:31 (12/13) 11:27:47 (05/20) 0 0
235923594 24106 1 +00000 00:00:03.1 05:00:43 (12/13) 11:27:47 (05/20) 0 0
235923594 24107 1 +00000 00:00:03.3 06:00:56 (12/13) 11:27:47 (05/20) 0 0
235923594 24108 1 +00000 00:00:02.8 07:00:09 (12/13) 11:27:47 (05/20) 0 0
235923594 24109 1 +00000 00:00:03.1 08:00:21 (12/13) 11:27:47 (05/20) 0 0
235923594 24110 1 +00000 00:00:02.3 09:00:34 (12/13) 11:27:47 (05/20) 0 0
235923594 24111 1 +00000 00:00:03.5 10:00:47 (12/13) 11:27:47 (05/20) 0 0
235923594 24112 1 +00000 00:00:02.7 11:01:00 (12/13) 11:27:47 (05/20) 0 0
235923594 24113 1 +00000 00:00:03.1 12:00:12 (12/13) 11:27:47 (05/20) 0 0
235923594 24114 1 +00000 00:00:03.2 13:00:25 (12/13) 11:27:47 (05/20) 0 0
235923594 24115 1 +00000 00:00:03.4 14:00:38 (12/13) 11:27:47 (05/20) 0 0
235923594 24116 1 +00000 00:00:02.7 15:00:51 (12/13) 11:27:47 (05/20) 0 0
235923594 24117 1 +00000 00:00:03.1 16:00:03 (12/13) 11:27:47 (05/20) 0 0
235923594 24118 1 +00000 00:00:03.3 17:00:16 (12/13) 11:27:47 (05/20) 0 0
235923594 24119 1 +00000 00:00:02.6 18:00:29 (12/13) 11:27:47 (05/20) 0 0
235923594 24120 1 +00000 00:00:02.8 19:00:42 (12/13) 11:27:47 (05/20) 0 0
235923594 24121 1 +00000 00:00:03.0 20:00:54 (12/13) 11:27:47 (05/20) 0 0
235923594 24122 1 +00000 00:00:02.5 21:00:07 (12/13) 11:27:47 (05/20) 0 0
235923594 24123 1 +00000 00:00:03.7 22:00:20 (12/13) 11:27:47 (05/20) 0 0
235923594 24124 1 +00000 00:00:03.9 23:00:33 (12/13) 11:27:47 (05/20) 0 0
235923594 24125 1 +00000 00:00:03.2 00:00:45 (12/14) 11:27:47 (05/20) 0 0
235923594 24126 1 +00000 00:00:02.5 01:00:58 (12/14) 11:27:47 (05/20) 0 0
235923594 24127 1 +00000 00:00:02.9 02:00:11 (12/14) 11:27:47 (05/20) 0 0
235923594 24128 1 +00000 00:00:03.0 03:00:23 (12/14) 11:27:47 (05/20) 0 0
235923594 24129 1 +00000 00:00:03.2 04:00:36 (12/14) 11:27:47 (05/20) 0 0
235923594 24130 1 +00000 00:00:03.5 05:00:49 (12/14) 11:27:47 (05/20) 0 0
235923594 24131 1 +00000 00:00:02.9 06:00:02 (12/14) 11:27:47 (05/20) 0 0
235923594 24132 1 +00000 00:00:03.2 07:00:14 (12/14) 11:27:47 (05/20) 0 0
235923594 24133 1 +00000 00:00:03.4 08:00:27 (12/14) 11:27:47 (05/20) 0 0
235923594 24134 1 +00000 00:00:03.6 09:00:40 (12/14) 11:27:47 (05/20) 0 0
235923594 24135 1 +00000 00:00:02.9 10:00:53 (12/14) 11:27:47 (05/20) 0 0
235923594 24136 1 +00000 00:00:03.3 11:00:05 (12/14) 11:27:47 (05/20) 0 0
235923594 24137 1 +00000 00:00:02.5 12:00:18 (12/14) 11:27:47 (05/20) 0 0
DBID SNAP_ID INST FLUSH_ELAPSED ENDTM STARTUP_TIME STATUS ERRCNT
---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------
235923594 24138 1 +00000 00:00:02.8 13:00:31 (12/14) 11:27:47 (05/20) 0 0
235923594 24139 1 +00000 00:00:03.2 14:00:43 (12/14) 11:27:47 (05/20) 0 0
235923594 24140 1 +00000 00:00:03.3 15:00:56 (12/14) 11:27:47 (05/20) 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
---------- ----- ---------- ----------------- ------ -----------
235923594 1 23942 09:00:53 (12/06) 0 0
**********************************
(6) AWR Control Settings - interval, retention
**********************************
DBID LSNAPID LSPLITID LSNAPTIME LPURGETIME FLAG INTERVAL RETENTION VRSN
----------- -------- -------- -------------- -------------- ----- ----------------- ----------------- ----
235923594 24140 0 12/14 15:01:00 12/14 08:06:07 2 +00000 01:00:00.0 +00008 00:00:00.0 5
**********************************
(7a) AWR Contents - row counts for each snapshots
**********************************
SNAP_ID INST ASH SQL SQBND FILES SEGST SYSEVT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
24091 1 27 88 259 54 90 148
24092 1 48 88 271 54 87 148
24093 1 30 88 491 54 93 148
24094 1 28 83 482 54 83 148
24095 1 14 83 171 54 85 148
24096 1 24 89 170 54 89 148
24097 1 28 83 176 54 89 148
24098 1 25 87 172 54 91 148
24099 1 30 97 643 54 85 148
24100 1 104 110 426 54 88 148
24101 1 31 87 214 54 93 148
24102 1 23 82 178 54 95 148
24103 1 29 83 186 54 95 148
24104 1 26 82 158 54 98 148
24105 1 27 84 159 54 97 148
24106 1 22 83 161 54 96 148
24107 1 31 83 166 54 92 148
24108 1 29 84 167 54 98 148
24109 1 33 91 283 54 92 148
24110 1 25 97 295 54 86 148
24111 1 26 92 659 54 94 148
24112 1 25 88 344 54 95 148
24113 1 35 82 235 54 91 148
24114 1 37 91 432 54 102 148
24115 1 29 89 318 54 95 148
24116 1 33 86 204 54 88 148
24117 1 32 99 566 54 86 148
24118 1 28 91 372 54 90 148
24119 1 27 84 185 54 85 148
24120 1 33 88 194 54 88 148
24121 1 30 83 177 54 88 148
24122 1 21 84 182 54 91 148
24123 1 31 98 695 54 86 148
24124 1 91 120 854 54 94 148
24125 1 34 84 229 54 83 148
24126 1 41 80 185 54 90 148
24127 1 19 85 196 54 92 148
24128 1 21 82 180 54 90 148
24129 1 30 80 159 54 88 148
24130 1 30 82 156 54 90 148
24131 1 27 85 184 54 87 148
24132 1 28 84 182 54 89 148
24133 1 26 91 547 54 90 148
24134 1 37 101 319 54 87 148
24135 1 31 90 272 54 91 148
24136 1 24 87 376 54 87 148
24137 1 30 82 250 54 88 148
SNAP_ID INST ASH SQL SQBND FILES SEGST SYSEVT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
24138 1 31 90 351 54 90 148
24139 1 31 95 538 54 90 148
24140 1 29 93 478 54 99 148
**********************************
(7b) AWR Contents - average row counts per snapshot
**********************************
SNAP_COUNT INST ASH SQLSTAT SQLBIND FILES SEGSTAT SYSEVENT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
199 1 40.45 90.2 302.06 54 88.97 143.34
**********************************
(7c) AWR total item counts - names, text, plans
**********************************
SQLTEXT SQLPLAN SQLBMETA SEGOBJ DATAFILE TEMPFILE
---------- ---------- ---------- ---------- ---------- ----------
1674 41448 13237 1188 54 3
########################################################
(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 20:41:35 (09/29) 441 ########## AUTO COMPLETED
SYS/ADDM 25500/ADDM:235923594_1_24094 17:00:13 (12/12) 1 1 AUTO COMPLETED
SYS/ADDM 25501/ADDM:235923594_1_24095 18:00:26 (12/12) 0 0 AUTO COMPLETED
SYS/ADDM 25502/ADDM:235923594_1_24096 19:00:40 (12/12) 0 0 AUTO COMPLETED
SYS/ADDM 25503/ADDM:235923594_1_24097 20:00:52 (12/12) 0 0 AUTO COMPLETED
SYS/ADDM 25504/ADDM:235923594_1_24098 21:00:05 (12/12) 0 0 AUTO COMPLETED
SYS/ADDM 25505/ADDM:235923594_1_24099 22:00:18 (12/12) 0 0 AUTO COMPLETED
SYS/Segment Ad 25506/SYS_AUTO_SPCADV_5703221212 22:03:57 (12/12) 25 25 CMD COMPLETED
2017
SYS/ADDM 25507/ADDM:235923594_1_24100 23:00:31 (12/12) 1 1 AUTO COMPLETED
SYS/ADDM 25508/ADDM:235923594_1_24101 00:00:44 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25509/ADDM:235923594_1_24102 01:00:56 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25510/ADDM:235923594_1_24103 02:00:09 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25511/ADDM:235923594_1_24104 03:00:22 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25512/ADDM:235923594_1_24105 04:00:34 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25513/ADDM:235923594_1_24106 05:00:47 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25514/ADDM:235923594_1_24107 06:01:00 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25515/ADDM:235923594_1_24108 07:00:12 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25516/ADDM:235923594_1_24109 08:00:25 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25517/ADDM:235923594_1_24110 09:00:37 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25518/ADDM:235923594_1_24111 10:00:51 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25519/ADDM:235923594_1_24112 11:01:04 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25520/ADDM:235923594_1_24113 12:00:16 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25521/ADDM:235923594_1_24114 13:00:29 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25522/ADDM:235923594_1_24115 14:00:42 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25523/ADDM:235923594_1_24116 15:00:55 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25524/ADDM:235923594_1_24117 16:00:07 (12/13) 1 1 AUTO COMPLETED
SYS/ADDM 25525/ADDM:235923594_1_24118 17:00:20 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25526/ADDM:235923594_1_24119 18:00:33 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25527/ADDM:235923594_1_24120 19:00:45 (12/13) 1 1 AUTO COMPLETED
SYS/ADDM 25528/ADDM:235923594_1_24121 20:00:58 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25529/ADDM:235923594_1_24122 21:00:10 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25530/ADDM:235923594_1_24123 22:00:24 (12/13) 0 0 AUTO COMPLETED
SYS/Segment Ad 25531/SYS_AUTO_SPCADV_0804221312 22:04:09 (12/13) 41 41 CMD COMPLETED
2017
SYS/ADDM 25532/ADDM:235923594_1_24124 23:00:37 (12/13) 0 0 AUTO COMPLETED
SYS/ADDM 25533/ADDM:235923594_1_24125 00:00:49 (12/14) 0 0 AUTO COMPLETED
SYS/ADDM 25534/ADDM:235923594_1_24126 01:01:01 (12/14) 0 0 AUTO COMPLETED
SYS/ADDM 25535/ADDM:235923594_1_24127 02:00:14 (12/14) 1 1 AUTO COMPLETED
SYS/ADDM 25536/ADDM:235923594_1_24128 03:00:27 (12/14) 0 0 AUTO COMPLETED
SYS/ADDM 25537/ADDM:235923594_1_24129 04:00:40 (12/14) 0 0 AUTO COMPLETED
SYS/ADDM 25538/ADDM:235923594_1_24130 05:00:53 (12/14) 0 0 AUTO COMPLETED
SYS/ADDM 25539/ADDM:235923594_1_24131 06:00:05 (12/14) 0 0 AUTO COMPLETED
SYS/ADDM 25540/ADDM:235923594_1_24132 07:00:18 (12/14) 0 0 AUTO COMPLETED
SYS/ADDM 25541/ADDM:235923594_1_24133 08:00:31 (12/14) 0 0 AUTO COMPLETED
SYS/ADDM 25542/ADDM:235923594_1_24134 09:00:44 (12/14) 0 0 AUTO COMPLETED
SYS/ADDM 25543/ADDM:235923594_1_24135 10:00:56 (12/14) 0 0 AUTO COMPLETED
SYS/ADDM 25544/ADDM:235923594_1_24136 11:00:09 (12/14) 0 0 AUTO COMPLETED
SYS/ADDM 25545/ADDM:235923594_1_24137 12:00:22 (12/14) 0 0 AUTO COMPLETED
SYS/ADDM 25546/ADDM:235923594_1_24138 13:00:34 (12/14) 1 1 AUTO COMPLETED
SYS/ADDM 25547/ADDM:235923594_1_24139 14:00:47 (12/14) 0 0 AUTO COMPLETED
SYS/ADDM 25548/ADDM:235923594_1_24140 15:01:00 (12/14) 0 0 AUTO COMPLETED
**********************************
(2) Advisor Task - Oldest 5
**********************************
OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
SYS/ADDM 24774/ADDM:235923594_1_23414 09:00:44 (11/14) 0 0 AUTO COMPLETED
SYS/ADDM 24775/ADDM:235923594_1_23415 10:00:57 (11/14) 0 0 AUTO COMPLETED
SYS/ADDM 24776/ADDM:235923594_1_23416 11:00:09 (11/14) 0 0 AUTO COMPLETED
SYS/ADDM 24777/ADDM:235923594_1_23417 12:00:22 (11/14) 0 0 AUTO COMPLETED
SYS/ADDM 24778/ADDM:235923594_1_23418 13:00:36 (11/14) 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 2,172
0020 - 0024 1
**********************************
(1b) ASH histogram (past 1 day)
**********************************
NUM_ACTIVE_SESSIONS NUM_SAMPLES
-------------------- ------------
0000 - 0004 682
**********************************
(2a) ASH details (past 3 days)
**********************************
INST MIN_TIME MAX_TIME NUM_SAMPLES NUM_ROWS AVG_ACTIVE
---- ---------------- ---------------- ------------ ------------ ----------
1 16:00:41 (12/11) 15:00:17 (12/14) 25,270 2,408 0.10
**********************************
(2b) ASH details (past 1 day)
**********************************
INST MIN_TIME MAX_TIME NUM_SAMPLES NUM_ROWS AVG_ACTIVE
---- ---------------- ---------------- ------------ ------------ ----------
1 16:00:12 (12/13) 15:00:17 (12/14) 8,188 730 0.09
**********************************
(2c) ASH sessions (Fg Vs Bg) (past 1 day across all instances in RAC)
**********************************
Foreground % 13.2
Background % 86.8
MMNL % 0.0
End of Report
检查（2）部分信息：AWR报告一天的数据量和一周的数据量；
检查（3b）部分信息：SYSAUX空间里哪些表占用比较多，然后在MOS里面输入表名查看是否存在bug；
检查（6）部分信息：AWR报告保留的周期。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31401608/viewspace-2148712/ ，如需转载，请注明出处，否则将追究法律责任。
