$ORACLE_HOME/rdbms/admin/awrinfo.sql
awrinfo.sql을 통해 AWR에 저장된 정보를 확인 할 수 있다.
(에러 정보, 현재 설정 정보(interval, 보존 기간 등), 유저 별 SYSAUX 사용 정보 등)
전문
~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~
Report generated at 13:23:05 on Jan 02, 2016 ( Saturday ) in Timezone +09: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
———— ——— —————————————- —– —————– ———— —
* 1232455262 ORCL1 oracle.com – Linux x86 64-bit 1 12:23:21 (01/02) 201085 NO
########################################################
(I) AWR Snapshots Information
########################################################
*****************************************************
(1a) SYSAUX usage – Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size 516.8 MB ( 2% of 32,768.0 MB MAX with AUTOEXTEND ON )
|
| Schema XDB occupies 157.5 MB ( 30.5% )
| Schema SYS occupies 122.8 MB ( 23.8% )
| Schema APEX_030200 occupies 77.4 MB ( 15.0% )
| Schema MDSYS occupies 65.8 MB ( 12.7% )
| Schema SYSMAN occupies 45.3 MB ( 8.8% )
| Schema SYSTEM occupies 13.7 MB ( 2.6% )
| Schema ORDDATA occupies 13.6 MB ( 2.6% )
| Schema OLAPSYS occupies 8.8 MB ( 1.7% )
| Schema CTXSYS occupies 3.8 MB ( 0.7% )
| Schema EXFSYS occupies 3.6 MB ( 0.7% )
| Schema WMSYS occupies 3.5 MB ( 0.7% )
| 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 157.5 MB
| SDO MDSYS 65.8 MB
| EM SYSMAN 45.3 MB
| SM/AWR SYS 40.6 MB
| AO SYS 36.3 MB
| XSOQHIST SYS 36.3 MB
| ORDIM/ORDDATA ORDDATA 13.6 MB
| LOGMNR SYSTEM 12.3 MB
| SM/OPTSTAT SYS 10.6 MB
| XSAMD OLAPSYS 8.8 MB
| SM/ADVISOR SYS 7.8 MB
| SM/OTHER SYS 6.1 MB
| TEXT CTXSYS 3.8 MB
| EXPRESSION_FILTER EXFSYS 3.6 MB
| WM WMSYS 3.5 MB
| SMON_SCN_TIME SYS 2.1 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.5 MB
| ORDIM ORDSYS 0.4 MB
| JOB_SCHEDULER SYS 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) 55.7 MB
|
******************************************
(1c) SYSAUX usage – Unregistered Schemas
******************************************
| This section displays schemas that are not registered
| in V$SYSAUX_OCCUPANTS
|
| Schema APEX_030200 occupies 77.4 MB
|
| Total space 77.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 -21.8 MB
|
| Total space -21.8 MB
|
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 60 mins snapshot INTERVAL:
| AWR size/day 243.4 MB (10,384 K/snap * 24 snaps/day)
| AWR size/wk 1,703.6 MB (size_per_day * 7) per instance
|
| Estimates based on 4 snaps in past 24 hours:
| AWR size/day 60.8 MB (10,384 K/snap and 4 snaps in past 16 hours)
| AWR size/wk 425.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 21.9 54.1 5,616 32.9 230.3 51% : 49%
SQLPLAN 7.0 17.3 1,792 10.5 73.5 71% : 29%
SPACE 2.6 6.3 656 3.8 26.9 51% : 49%
SQL 1.9 4.8 496 2.9 20.3 55% : 45%
EVENTS 1.6 4.0 416 2.4 17.1 50% : 50%
SQLTEXT 1.0 2.5 256 1.5 10.5 88% : 13%
SQLBIND 0.7 1.7 176 1.0 7.2 55% : 45%
RAC 0.6 1.5 160 0.9 6.6 50% : 50%
ASH 0.5 1.2 128 0.8 5.3 63% : 38%
**********************************
(3b) Space usage within AWR Components (> 500K)
**********************************
COMPONENT MB SEGMENT_NAME – % SPACE_USED SEGMENT_TYPE
——— ——— ——————————————————————— —————
FIXED 2.0 WRH$_LATCH_CHILDREN.WRH$_LATCH__1232455262_81 – 83% TABLE PARTITION
FIXED 2.0 WRH$_LATCH_CHILDREN_PK.WRH$_LATCH__1232455262_81 – 58% INDEX PARTITION
FIXED 0.9 WRH$_SYSMETRIC_HISTORY – 19% TABLE
FIXED 0.8 WRH$_SYSMETRIC_HISTORY_INDEX – 26% INDEX
SQLPLAN 5.0 WRH$_SQL_PLAN – 78% TABLE
SQLPLAN 2.0 WRH$_SQL_PLAN_PK – 69% INDEX
SQL 0.6 WRH$_SQLSTAT.WRH$_SQLSTA_1232455262_81 – 82% TABLE PARTITION
SQLTEXT 0.9 WRH$_SQLTEXT – 84% TABLE
**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************
COMPONENT MB SEGMENT_NAME SEGMENT_TYPE
——— ——— ——————————————————————— —————
NON_AWR 53.2 XDB.SYS_LOB0000057465C00025$$ LOBSEGMENT
NON_AWR 18.0 MDSYS.SYS_LOB0000064008C00006$$ LOBSEGMENT
NON_AWR 16.1 SYS.SYS_LOB0000065858C00004$$ LOBSEGMENT
NON_AWR 8.0 XDB.XDB$RESOURCE TABLE
NON_AWR 7.2 SYS.SYS_LOB0000065843C00004$$ LOBSEGMENT
NON_AWR 7.0 MDSYS.SDO_CS_SRS TABLE
NON_AWR 5.0 APEX_030200.WWV_FLOW_STEP_ITEMS 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 4.2 SYS.SYS_LOB0000065848C00004$$ LOBSEGMENT
NON_AWR 4.0 APEX_030200.WWV_FLOW_DICTIONARY$ TABLE
NON_AWR 4.0 SYSTEM.SYS_LOB0000001147C00009$$ LOBSEGMENT
NON_AWR 3.2 SYS.SYS_LOB0000065838C00004$$ LOBSEGMENT
NON_AWR 3.0 XDB.XDB$H_INDEX TABLE
NON_AWR 3.0 APEX_030200.WWV_FLOW_DICTIONARY$SOUNDEX INDEX
NON_AWR 3.0 APEX_030200.WWV_FLOW_DICTIONARY$WORDS INDEX
NON_AWR 2.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE
NON_AWR 2.0 SYS.SYS_LOB0000006339C00038$$ LOBSEGMENT
NON_AWR 2.0 APEX_030200.WWV_FLOW_DICTIONARY$_FKIDX INDEX
NON_AWR 2.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX
NON_AWR 2.0 SYS.SMON_SCN_TO_TIME_AUX CLUSTER
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.2 ORDDATA.SYS_LOB0000059327C00005$$ LOBSEGMENT
NON_AWR 1.2 SYS.SYS_LOB0000065863C00004$$ LOBSEGMENT
NON_AWR 1.2 SYS.SYS_LOB0000065853C00004$$ LOBSEGMENT
NON_AWR 1.2 ORDDATA.SYS_LOB0000059475C00005$$ LOBSEGMENT
NON_AWR 1.0 SYS.SYS$SERVICE_METRICS_TAB TABLE
NON_AWR 1.0 XDB.XDB$H_LINK TABLE
NON_AWR 0.9 SYS.I_WRI$_OPTSTAT_HH_ST INDEX
NON_AWR 0.9 SYSMAN.MGMT_METRICS_IDX_01 INDEX
NON_AWR 0.9 SYSMAN.MGMT_METRICS_PK INDEX
NON_AWR 0.9 SYSMAN.MGMT_METRICS_IDX_03 INDEX
NON_AWR 0.8 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
NON_AWR 0.8 APEX_030200.WWV_FLOW_STEP_VALIDATIONS 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.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
COMPONENT MB SEGMENT_NAME SEGMENT_TYPE
——— ——— ——————————————————————— —————
NON_AWR 0.6 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY TABLE
NON_AWR 0.6 APEX_030200.SYS_LOB0000072729C00010$$ LOBSEGMENT
NON_AWR 0.6 SYS.SYS_LOB0000006331C00004$$ LOBSEGMENT
NON_AWR 0.6 SYSMAN.MGMT_POLICIES TABLE
NON_AWR 0.6 SYS.AW_OBJ$ TABLE
NON_AWR 0.5 SYSMAN.MGMT_JOB_STEP_PARAMS TABLE
NON_AWR 0.5 SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS TABLE
NON_AWR 0.5 APEX_030200.WWV_FLOW_LIST_OF_VALUES_DATA TABLE
NON_AWR 0.5 SYSMAN.PK_MGMT_JOB_STEP_PARAMS INDEX
NON_AWR 0.5 SYS.SYS_LOB0000005097C00005$$ LOBSEGMENT
**********************************
(5a) AWR snapshots – last 50
**********************************
Total snapshots in DB 1232455262 Instance 1 = 4
DBID SNAP_ID INST FLUSH_ELAPSED ENDTM STARTUP_TIME STATUS ERRCNT
———- ———- —– ——————– —————– —————– —— ——
1232455262 84 1 +00000 00:00:09.6 21:34:47 (01/01) 21:24:42 (01/01) 0 0
1232455262 85 1 +00000 00:00:05.7 23:00:50 (01/01) 21:41:34 (01/01) 0 0
1232455262 86 1 +00000 00:00:01.0 00:00:06 (01/02) 21:41:34 (01/01) 0 0
1232455262 87 1 +00000 00:00:05.3 12:34:16 (01/02) 12:23:21 (01/02) 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
———- —– ———- —————– —— ———–
1232455262 1 84 21:34:47 (01/01) 0 0
**********************************
(6) AWR Control Settings – interval, retention
**********************************
DBID LSNAPID LSPLITID LSNAPTIME LPURGETIME FLAG INTERVAL RETENTION VRSN
———– ——– ——– ————– ————– —– —————– —————– —-
1232455262 87 78 01/02 12:34:22 01/01 22:11:32 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
———- —– ———- ———- ———- ———- ———- ———-
84 1 10 296 523 12 103 42
85 1 175 301 835 12 123 62
86 1 42 173 452 12 128 64
87 1 37 326 878 12 109 73
**********************************
(7b) AWR Contents – average row counts per snapshot
**********************************
SNAP_COUNT INST ASH SQLSTAT SQLBIND FILES SEGSTAT SYSEVENT
———- —– ———- ———- ———- ———- ———- ———-
4 1 66 274 672 12 115.75 60.25
**********************************
(7c) AWR total item counts – names, text, plans
**********************************
SQLTEXT SQLPLAN SQLBMETA SEGOBJ DATAFILE TEMPFILE
———- ———- ———- ———- ———- ———-
1167 17898 4723 403 12 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 09:52:13 (09/17) 16 ########## AUTO COMPLETED
SYS/Segment Ad 193/SYS_AUTO_SPCADV_190022010120 22:00:19 (01/01) 170 171 CMD COMPLETED
16
SYS/ADDM 194/ADDM:1232455262_1_86 00:00:08 (01/02) 0 0 AUTO COMPLETED
SYS/Segment Ad 203/SYS_AUTO_SPCADV_192412020120 12:24:19 (01/02) 3 4 CMD COMPLETED
16
**********************************
(2) Advisor Task – Oldest 5
**********************************
OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS
————– ——————————– —————- ———- ———- —– ————
SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK 09:52:13 (09/17) 16 ########## AUTO COMPLETED
SYS/Segment Ad 193/SYS_AUTO_SPCADV_190022010120 22:00:19 (01/01) 170 171 CMD COMPLETED
16
SYS/ADDM 194/ADDM:1232455262_1_86 00:00:08 (01/02) 0 0 AUTO COMPLETED
SYS/Segment Ad 203/SYS_AUTO_SPCADV_192412020120 12:24:19 (01/02) 3 4 CMD COMPLETED
16
**********************************
(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 163
0005 – 0009 2
0010 – 0014 1
**********************************
(1b) ASH histogram (past 1 day)
**********************************
NUM_ACTIVE_SESSIONS NUM_SAMPLES
——————– ————
0000 – 0004 163
0005 – 0009 2
0010 – 0014 1
**********************************
(2a) ASH details (past 3 days)
**********************************
INST MIN_TIME MAX_TIME NUM_SAMPLES NUM_ROWS AVG_ACTIVE
—- —————- —————- ———— ———— ———-
1 21:41:40 (01/01) 12:31:24 (01/02) 872 254 0.29
**********************************
(2b) ASH details (past 1 day)
**********************************
INST MIN_TIME MAX_TIME NUM_SAMPLES NUM_ROWS AVG_ACTIVE
—- —————- —————- ———— ———— ———-
1 21:41:40 (01/01) 12:31:24 (01/02) 872 254 0.29
**********************************
(2c) ASH sessions (Fg Vs Bg) (past 1 day across all instances in RAC)
**********************************
Foreground % 24.8
Background % 75.2
MMNL % 0.0
End of Report