Oracle Database Commands and Practical Examples

Monday 11 January 2021

AWR- Managing Space Consumed by AWR Snapshots

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.

~~~~~~~~~~~~~~~

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

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

---------------------------

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.

PT - Using Session Waiting Events with Hung or Very Slow Sessions

Using Session Waiting Events with Hung or Very Slow Sessions

Scenario Preview:

We will update and check wait events of sessions

SQL> conn sys/system123@pdb as sysdba

Connected.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

C:\Users\varun>hostname

DESKTOP-H1NR6BO

C:\Users\varun>sqlplus sys/system123@pdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 6 19:39:09 2021

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn soe/soe@pdb

Connected.

Execute update command on the table salary for scenario.

SQL> UPDATE EMP SET SALARY=SALARY WHERE EMP_NO=104;

1 row updated.

SQL> SELECT SID, EVENT

2 FROM V$SESSION

3 WHERE STATE='WAITING' AND USERNAME ='SOE' AND WAIT_CLASS<>'Idle';

SID EVENT

---------- ----------------------------------------------------------------

382 log file sync

Wait event information of the hung session from the V$SESSION and V$SESSION_EVENT.

SQL> SELECT SID, EVENT

2 FROM V$SESSION

3 WHERE STATE='WAITING' AND USERNAME ='SOE' AND WAIT_CLASS<>'Idle';

SID EVENT

---------- ----------------------------------------------------------------

8 enq: TX - row lock contention

138 log file sync

260 log file sync

382 log file sync

871 log file sync

SQL> col SESSION_WAITS format a100

SQL> SELECT 'SID: '|| SID||

2 CHR(10)||'USERNAME: '|| USERNAME||

3 CHR(10)||'STATE: '|| STATE||

4 CHR(10)||'EVENT: '|| EVENT||

5 CHR(10)||'WAIT_TIME: '|| WAIT_TIME||

6 CHR(10)||'SECONDS_IN_WAIT: '|| SECONDS_IN_WAIT||

7 CHR(10)||'WAIT_CLASS: '|| WAIT_CLASS||

8 CHR(10)||'P1TEXT: '|| P1TEXT||

9 CHR(10)||'P1: '|| P1||

10 CHR(10)||'P2TEXT: '|| P2TEXT||

11 CHR(10)||'P2: '|| P2 ||

12 CHR(10)||'P3TEXT: '|| P3TEXT||

13 CHR(10)||'P3: ' || P3 AS SESSION_WAITS

14 FROM V$SESSION

15 WHERE USERNAME='SOE' AND EVENT LIKE 'enq: TX%'

16 ORDER BY WAIT_TIME;

SESSION_WAITS

--------------------------------------------------------------------------------

SID: 8

USERNAME: SOE

STATE: WAITING

EVENT: enq: TX - row lock contention

WAIT_TIME: 0

SECONDS_IN_WAIT: 87

WAIT_CLASS: Application

P1TEXT: name|mode

P1: 1415053318

P2TEXT: usn<<16 | slot

P2: 524313

SESSION_WAITS

--------------------------------------------------------------------------------

P3TEXT: sequence

P3: 9186

SQL> col EVENT for a30

SQL> SELECT E.EVENT,

2 TO_CHAR(ROUND(E.TIME_WAITED/100),'999,999,999') TIME_SECONDS

3 FROM V$SESSION_EVENT E, V$SESSION S

4 WHERE E.SID=S.SID AND S.USERNAME='SOE' AND E.EVENT LIKE 'enq: TX%'

5 ORDER BY TIME_WAITED;

EVENT TIME_SECONDS

------------------------------ ------------

enq: TX - index contention 0

enq: TX - row lock contention 468

SQL> SELECT COUNT(*) FROM V$SESSION_WAIT_HISTORY

2 WHERE EVENT LIKE 'enq: TX%';

COUNT(*)

----------

Rollback or commit statement.

SQL> rollback;

Rollback complete.

Using v$session now there is no currently waiting events.

SQL> col EVENT format a40

SQL> col WAIT_CLASS format a10

SQL> SELECT SID, EVENT, WAIT_CLASS

2 FROM V$SESSION

3 WHERE USERNAME='SOE';

SID EVENT WAIT_CLASS

---------- ---------------------------------------- ----------

8 SQL*Net message from client Idle

138 SQL*Net message from client Idle

260 SQL*Net message from client Idle

382 log file sync Commit

505 SQL*Net message to client Network

871 SQL*Net message from client Idle

6 rows selected.

View reports the wait event that has been waited by the session using V$SESSION_EVENT

SQL> SELECT E.EVENT,

2 TO_CHAR(ROUND(E.TIME_WAITED/100),'999,999,999') TIME_SECONDS

3 FROM V$SESSION_EVENT E, V$SESSION S

4 WHERE E.SID=S.SID AND S.USERNAME='SOE' AND E.EVENT LIKE 'enq: TX%'

5 ORDER BY TIME_WAITED;

EVENT TIME_SECONDS

---------------------------------------- ------------

enq: TX - index contention 0

enq: TX - row lock contention 497

SQL>

Information about the wait event that has just expired from the V$SESSION_WAIT_HISTORY

SQL> col SESSION_WAITS format a100

SQL> SELECT 'SID: '|| SID||

2 CHR(10)||'EVENT: '|| EVENT||

3 CHR(10)||'WAIT_TIME: '|| WAIT_TIME||

4 CHR(10)||'P1TEXT: '|| P1TEXT||

5 CHR(10)||'P1: '|| P1||

6 CHR(10)||'P2TEXT: '|| P2TEXT||

7 CHR(10)||'P2: '|| P2 ||

8 CHR(10)||'P3TEXT: '|| P3TEXT||

9 CHR(10)||'P3: ' || P3 AS SESSION_WAITS

10 FROM V$SESSION_WAIT_HISTORY

11 WHERE EVENT LIKE 'enq: TX%'

12 ORDER BY WAIT_TIME;

SESSION_WAITS

--------------------------------------------------------------------------------

SID: 8

EVENT: enq: TX - row lock contention

WAIT_TIME: 49718

P1TEXT: name|mode

P1: 1415053318

P2TEXT: usn<<16 | slot

P2: 524313

P3TEXT: sequence

P3: 9186

SQL>

SELECT COUNT(*) FROM V$SESSION_WAIT_HISTORY WHERE EVENT LIKE 'enq: TX%';

ORA-00382: 32768 not a valid block size, valid range [2048..16384]

Scenario Preview:

Recently we perform some row migration and row chaining activity as we find out on window operating system we are unable to increase block size 32kb might be the reason due to page size in window.

Error:

ORA-00382: 32768 not a valid block size, valid range [2048..16384]

C:\Users\varun>sqlplus sys/system123@orcl as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 4 23:07:56 2021

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> ALTER SYSTEM SET DB_32K_CACHE_SIZE= 160M;

ALTER SYSTEM SET DB_32K_CACHE_SIZE= 160M

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-00382: 32768 not a valid block size, valid range [2048..16384]

SQL> CREATE TABLESPACE TBS16K111 DATAFILE 'C:\APP\VARUN\ORADATA\ORCL\PDB\tbs16k.dbf' SIZE 160M AUTOEXTEND ON MAXSIZE 1000M BLOCKSIZE 32K;

CREATE TABLESPACE TBS16K111 DATAFILE 'C:\APP\VARUN\ORADATA\ORCL\PDB\tbs16k.dbf' SIZE 160M AUTOEXTEND ON MAXSIZE 1000M BLOCKSIZE 32K

ERROR at line 1:

ORA-00382: 32768 not a valid block size, valid range [2048..16384]

SQL>

SQL> ALTER SYSTEM SET DB_16K_CACHE_SIZE= 160M;

System altered.

SQL> SELECT NAME, BLOCK_SIZE, CURRENT_SIZE FROM V$BUFFER_POOL;

NAME BLOCK_SIZE CURRENT_SIZE

-------------------- ---------- ------------

DEFAULT 8192 3552

DEFAULT 16384 160

SQL>

SQL> CREATE TABLESPACE TBS16K DATAFILE 'C:\APP\VARUN\ORADATA\ORCL\PDB\tbs16k.dbf' SIZE 160M AUTOEXTEND ON MAXSIZE 1000M BLOCKSIZE 16K;

Tablespace created.

SQL>

Solution Testing :

Set DB_16K_CACHE_SIZE scope is memory

SQL> ALTER SYSTEM SET DB_16K_CACHE_SIZE= 160M;

System altered.

SQL> SELECT NAME, BLOCK_SIZE, CURRENT_SIZE FROM V$BUFFER_POOL;

NAME BLOCK_SIZE CURRENT_SIZE

-------------------- ---------- ------------

DEFAULT 8192 3552

DEFAULT 16384 160

Create Tablespace TBS16K with block size 16K

SQL> CREATE TABLESPACE TBS16K DATAFILE 'C:\APP\VARUN\ORADATA\ORCL\PDB\tbs16k.dbf' SIZE 160M AUTOEXTEND ON MAXSIZE 1000M BLOCKSIZE 16K;

Tablespace created.

Set Quota to schema on tablespace

SQL> ALTER USER SOE QUOTA UNLIMITED ON TBS16K;

User altered.

SQL> conn soe/soe@pdb

Connected.

Move table cust to new tablespace

SQL> ALTER TABLE CUST MOVE TABLESPACE tbs16k;

Table altered.

HOME

Monday 11 January 2021

AWR- Managing Space Consumed by AWR Snapshots

PT - Using Session Waiting Events with Hung or Very Slow Sessions

ORA-00382: 32768 not a valid block size, valid range [2048..16384]