AWR : How to run Reports

Posted by Sagar Patil

The architecture of the AWR is quite simple. The MMON background process polls the x$ fixed tables from the SGA region and stores them in the AWR tables. From there, the performance data is instantly available for analysis. The Enterprise Manager can be used for graphical data display. Alternatively, the Automatic Database Diagnostic Monitor (ADDM) can be used for automated tuning analysis, or SQL*Plus can be used if customized Oracle tuning size reports are desired. Read more…

Compare 9i Statspack & 10g AWR Views

Posted by Sagar Patil
Statspack AWR
STATS$DATABASE_INSTANCE Tables store historical data or snapshots
STATS$IDLE_EVENT
STATS$LEVEL_DESCRIPTION
STATS$ROLLSTAT
STATS$SESSION_EVENT
STATS$SNAPSHOT
STATS$SQL_PLAN_USAGE
STATS$SQL_SUMMARY WRH$_ACTIVE_SESSION_HISTORY WRI$_ADV_ACTIONS
STATS$STATSPACK_PARAMETER WRH$_ACTIVE_SESSION_HISTORY_BL WRI$_ADV_ASA_RECO_DATA
STATS$BG_EVENT_SUMMARY WRH$_BG_EVENT_SUMMARY WRI$_ADV_DEFINITIONS
WRH$_BUFFERED_QUEUES WRI$_ADV_DEF_PARAMETERS
WRH$_BUFFERED_SUBSCRIBERS WRI$_ADV_DIRECTIVES
STATS$BUFFER_POOL_STATISTICS WRH$_BUFFER_POOL_STATISTICS WRI$_ADV_FINDINGS
WRH$_COMP_IOSTAT WRI$_ADV_JOURNAL
WRH$_CR_BLOCK_SERVER WRI$_ADV_MESSAGE_GROUPS
WRH$_CURRENT_BLOCK_SERVER WRI$_ADV_OBJECTS
WRH$_DATAFILE WRI$_ADV_PARAMETERS
STATS$DB_CACHE_ADVICE WRH$_DB_CACHE_ADVICE WRI$_ADV_RATIONALE
WRH$_DB_CACHE_ADVICE_BL WRI$_ADV_RECOMMENDATIONS
STATS$DLM_MISC WRH$_DLM_MISC WRI$_ADV_REC_ACTIONS
WRH$_DLM_MISC_BL WRI$_ADV_SQLA_FAKE_REG
STATS$ENQUEUE_STAT WRH$_ENQUEUE_STAT WRI$_ADV_SQLA_MAP
WRH$_EVENT_NAME WRI$_ADV_SQLA_STMTS
WRH$_FILEMETRIC_HISTORY WRI$_ADV_SQLA_TMP
STATS$FILESTATXS WRH$_FILESTATXS WRI$_ADV_SQLT_BINDS
WRH$_FILESTATXS_BL WRI$_ADV_SQLT_PLANS
STATS$INSTANCE_RECOVERY WRH$_INSTANCE_RECOVERY WRI$_ADV_SQLT_RTN_PLAN
WRH$_INST_CACHE_TRANSFER WRI$_ADV_SQLT_STATISTICS
WRH$_INST_CACHE_TRANSFER_BL WRI$_ADV_SQLW_COLVOL
WRH$_JAVA_POOL_ADVICE WRI$_ADV_SQLW_STMTS
STATS$LATCH WRH$_LATCH WRI$_ADV_SQLW_SUM
WRH$_LATCH_BL WRI$_ADV_SQLW_TABLES
STATS$LATCH_CHILDREN WRH$_LATCH_CHILDREN WRI$_ADV_SQLW_TABVOL
WRH$_LATCH_CHILDREN_BL WRI$_ADV_TASKS
STATS$LATCH_MISSES_SUMMARY WRH$_LATCH_MISSES_SUMMARY WRI$_ADV_USAGE
WRH$_LATCH_MISSES_SUMMARY_BL WRI$_AGGREGATION_ENABLED
WRH$_LATCH_NAME WRI$_ALERT_HISTORY
STATS$LATCH_PARENT WRH$_LATCH_PARENT WRI$_ALERT_OUTSTANDING
WRH$_LATCH_PARENT_BL WRI$_ALERT_THRESHOLD
STATS$LIBRARYCACHE WRH$_LIBRARYCACHE WRI$_ALERT_THRESHOLD_LOG
WRH$_LOG WRI$_DBU_CPU_USAGE
WRH$_METRIC_NAME WRI$_DBU_CPU_USAGE_SAMPLE
WRH$_MTTR_TARGET_ADVICE WRI$_DBU_FEATURE_METADATA
WRH$_OPTIMIZER_ENV WRI$_DBU_FEATURE_USAGE
WRH$_OSSTAT WRI$_DBU_HIGH_WATER_MARK
WRH$_OSSTAT_BL WRI$_DBU_HWM_METADATA
WRH$_OSSTAT_NAME WRI$_DBU_USAGE_SAMPLE
STATS$PARAMETER WRH$_PARAMETER WRI$_OPTSTAT_AUX_HISTORY
WRH$_PARAMETER_BL WRI$_OPTSTAT_HISTGRM_HISTORY
WRH$_PARAMETER_NAME WRI$_OPTSTAT_HISTHEAD_HISTORY
STATS$PGASTAT WRH$_PGASTAT WRI$_OPTSTAT_IND_HISTORY
STATS$PGA_TARGET_ADVICE WRH$_PGA_TARGET_ADVICE WRI$_OPTSTAT_OPR
WRH$_PROCESS_MEMORY_SUMMARY WRI$_OPTSTAT_TAB_HISTORY
STATS$RESOURCE_LIMIT WRH$_RESOURCE_LIMIT WRI$_SCH_CONTROL
STATS$ROWCACHE_SUMMARY WRH$_ROWCACHE_SUMMARY WRI$_SCH_VOTES
WRH$_ROWCACHE_SUMMARY_BL WRI$_SEGADV_CNTRLTAB
WRH$_RULE_SET WRI$_SEGADV_OBJLIST
WRH$_SEG_STAT WRI$_SQLSET_BINDS
WRH$_SEG_STAT_BL WRI$_SQLSET_DEFINITIONS
STATS$SEG_STAT_OBJ WRH$_SEG_STAT_OBJ WRI$_SQLSET_MASK
WRH$_SERVICE_NAME WRI$_SQLSET_PLANS
WRH$_SERVICE_STAT WRI$_SQLSET_PLANS_TOCAP
WRH$_SERVICE_STAT_BL WRI$_SQLSET_PLAN_LINES
WRH$_SERVICE_WAIT_CLASS WRI$_SQLSET_REFERENCES
WRH$_SERVICE_WAIT_CLASS_BL WRI$_SQLSET_STATEMENTS
WRH$_SESSMETRIC_HISTORY WRI$_SQLSET_STATISTICS
STATS$SESSTAT WRH$_SESS_TIME_STATS WRI$_SQLSET_WORKSPACE
STATS$SGA WRH$_SGA WRI$_TRACING_ENABLED
STATS$SEG_STAT , STATS$SGASTAT WRH$_SGASTAT
WRH$_SGASTAT_BL
WRH$_SGA_TARGET_ADVICE
STATS$SHARED_POOL_ADVICE WRH$_SHARED_POOL_ADVICE
STATS$SQL_STATISTICS WRH$_SQLSTAT
WRH$_SQLSTAT_BL
STATS$SQLTEXT WRH$_SQLTEXT
WRH$_SQL_BIND_METADATA
STATS$SQL_PLAN WRH$_SQL_PLAN
WRH$_SQL_SUMMARY
STATS$SQL_WORKAREA_HISTOGRAM WRH$_SQL_WORKAREA_HISTOGRAM
WRH$_STAT_NAME
WRH$_STREAMS_APPLY_SUM
WRH$_STREAMS_CAPTURE
WRH$_STREAMS_POOL_ADVICE
WRH$_SYSMETRIC_HISTORY
WRH$_SYSMETRIC_SUMMARY
STATS$SYSSTAT WRH$_SYSSTAT
WRH$_SYSSTAT_BL
STATS$SYSTEM_EVENT WRH$_SYSTEM_EVENT
WRH$_SYSTEM_EVENT_BL
WRH$_SYS_TIME_MODEL
WRH$_SYS_TIME_MODEL_BL
WRH$_TABLESPACE_SPACE_USAGE
WRH$_TABLESPACE_STAT
WRH$_TABLESPACE_STAT_BL
WRH$_TEMPFILE
STATS$TEMPSTATXS WRH$_TEMPSTATXS
WRH$_THREAD
STATS$UNDOSTAT WRH$_UNDOSTAT
WRH$_WAITCLASSMETRIC_HISTORY
STATS$WAITSTAT WRH$_WAITSTAT
WRH$_WAITSTAT_BL

AWR : How to locate resource limits

Posted by Sagar Patil

select A.snap_id AWR_SNAP_ID, A.INSTANCE_NUMBER,
to_char(B.BEGIN_INTERVAL_TIME,’DD-MON-YYYY HH24:MI:SS’) AWR_START_TIME,
to_char(B.END_INTERVAL_TIME,’DD-MON-YYYY HH24:MI:SS’) AWR_END_TIME,
A.RESOURCE_NAME, MAX_UTILIZATION
from sys.wrh$_resource_limit A, sys.wrm$_snapshot B
where A.resource_name like ‘%processes%’
and A.snap_id=b.snap_id
and A.INSTANCE_NUMBER= B.INSTANCE_NUMBER
and A.INSTANCE_NUMBER= 1
and B.BEGIN_INTERVAL_TIME > sysdate – 12/24;

Read more…

10G ASH script :True Session Wait Activity in Oracle 10g

Posted by Sagar Patil

1> What resource is currently in high demand?

select  active_session_history.event
 , sum ( active_session_history.wait_time + active_session_history.time_waited ) ttl_wait_time
 from v$active_session_history active_session_history
 where active_session_history.sample_time between
sysdate – 60 / 2880
 and sysdate group by active_session_history.event
 order by 2

Read more…

AWR : How to purge old Snapshots?

Posted by Sagar Patil

Read more…

Top of Page

Top menu