Techdata VINA 지식 공유

$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