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.

AWR interval could be changed using API :
execute dbms_workload_repository.modify_snapshot_settings (
interval => 60,
retention => 43200);
Interval : How often snaps are taken
Retention: Duration in Minutes

Values updated at “dba_hist_wr_control”

AWR Reports

AWR reports are very similar to STATSPACK reports. They typically show:
SQL> @?/rdbms/admin/awrrpt.sql

A nice feature is that an HTML version of the report can be generated. Just follow the prompts. SELECT_ANY_DICTIONARY privilege is required to run a report.

Managing Snapshots
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
retention_period => 0
interval => 0

AWR Views

DBA_HIST_SNAPSHOT # show all snapshots
DBA_HIST_WR_CONTROL # show AWR settings

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;


The different resources recorded are as below :

RESOURCE_NAME
——————————
gcs_resources
processes
enqueue_locks
max_rollback_segments
parallel_max_servers
ges_procs
sessions
gcs_shadows

AWR_SNAP_ID INSTANCE_NUMBER AWR_START_TIME AWR_END_TIME RESOURCE_NAME MAX_UTILIZATION
7964 1 28/05/2010 16:00 28/05/2010 16:56 processes 146
7963 1 28/05/2010 15:00 28/05/2010 16:00 processes 146
7962 1 28/05/2010 14:00 28/05/2010 15:00 processes 146
7961 1 28/05/2010 13:00 28/05/2010 14:00 processes 146
7960 1 28/05/2010 12:00 28/05/2010 13:00 processes 146
7959 1 28/05/2010 11:00 28/05/2010 12:00 processes 146
7958 1 28/05/2010 10:00 28/05/2010 11:00 processes 146
7957 1 28/05/2010 09:00 28/05/2010 10:00 processes 146
7956 1 28/05/2010 08:00 28/05/2010 09:00 processes 146
7955 1 28/05/2010 07:00 28/05/2010 08:00 processes 146
7954 1 28/05/2010 06:00 28/05/2010 07:00 processes 146
7953 1 28/05/2010 05:00 28/05/2010 06:00 processes 146

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
2> Which user is waiting the most?
select  sesion.sid
 , sesion.username
 , sum ( active_session_history.wait_time + active_session_history.time_waited ) ttl_wait_time
 from v$active_session_history active_session_history
 , v$session sesion
 where active_session_history.sample_time between
sysdate - 60 / 2880
 and sysdate and active_session_history.session_id = sesion.sid
 group by sesion.sid
 , sesion.username
 order by 3

SID User TTL_WAIT_TIME
—– ———- ————-
135 SCOTT 91167481
149 SCOTT 107409491
153 SCOTT 110796799

3> What SQL is currently using the most resources?
select  active_session_history.user_id
 , dba_users.username
 , sqlarea.sql_text
 , sum ( active_session_history.wait_time + active_session_history.time_waited ) ttl_wait_time
 from v$active_session_history active_session_history
 , v$sqlarea sqlarea
 , dba_users
 where active_session_history.sample_time between
sysdate - 60 / 2880
 and sysdate and active_session_history.sql_id = sqlarea.sql_id
 and active_session_history.user_id = dba_users.user_id
 group by active_session_history.user_id
 , sqlarea.sql_text
 , dba_users.username
 order by 4;

USER_ID User SQL_TEXT TTL_WAIT_TIME
——- —— —————————————————– ————-
57 SCOTT insert into sys.sourcetable (select * from sys.source$) 304169752

4> What object is currently causing the highest resource waits?

select  dba_objects.object_name
 , dba_objects.object_type
 , 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
 , dba_objects
 where active_session_history.sample_time between
sysdate - 60 / 2880
 and sysdate and active_session_history.current_obj# = dba_objects.object_id
 group by dba_objects.object_name
 , dba_objects.object_type
 , active_session_history.event
 order by 4

AWR : How to purge old Snapshots?

Posted by Sagar Patil

Read more…

Top of Page

Top menu