Wednesday, May 03, 2006

Active Session History in Oracle 10gR1+2

This article has been published in the Swiss Oracle User Group's Newsletter 4/2006 (October 2006).

This article descibes the new on Oracle Database 10g Active Session History, a kind of data warehouse for session statistics.

A lot of the information in this post I have taken from the book Oracle Wait Interface by the
authors R.Shee, K.Deshpande and K.Gopalakrishnan
which I can strongly recommed to everybody
who has to deal with performance tuning for ORACLE of which release ever.
It covers the Oracle versions until 10gR1 and tells us everything we have ever wanted to know about latches, enqueues and all the wait events.


Starting with the version 10gR1 Oracle stores information about session activity on disk in the Automatic Workload Repository (AWR) which sits, amoung other things, in the new mandatory SYSAUX tablespace.
This information comes from statistics about active sessions which the Oracle Kernel collects automatically.
The active session history is enabled by default( influenced by the hidden parameter _ASH_ENABLE=true).

The main components of the ASH are
- two new background procresses, MMON and MMNL,
- a rolling buffer, which sits in the SGA fixed area and holds the historical statistical information of active sessions

This buffer has a minimum size of 1Mb in ORACLE 10gR1 and can grow to a maximum of either 30Mb, or 5% of SHARED_POOL_SIZE, or Numer of CPUs * 2Mb (whichever is smaller!).

This formula has been changed in 10gR2 to not linearly scale with
the number of CPUs.
All we get to know about the sizing is that "...the ASH will
auto-configure itself to try and hold at least one-hour worth of
instance activity without wasting too much value SGA space and will
never be bigger than 2% of SGA_TARGET or 5% of SHARED_POOL (if AutoSGA
is not used)".


The contents of the rolling buffer get written down to disk on a regular basis by MMNL
( with every snapshot created and when the buffer gets full).
It gets flushed to the table WRH$_ACTIVE_SESSION_HISTORY in the AWR which is implemented like a facts table in a DWH with its columns as dimensions.
This is enbled with the hidden parameter _ASH_DISK_WRITE_ENABLE=true.

The information in the ASH is used, among others, by automatic tuning utilities like teh SQLTUNING Advisor (dbms_sqltune) for the Automatic Tuning Optimizer (ATO). This one will look for historical data about sql-statements and their exetutions in the ASH (see buttom) as well, anong other activities it can perform in order top find a potentially better execution plan.

There are a lot of DBA_ views we can use to read the historical information from the AWR:
SELECT table_name
FROM dictionary
WHERE table_name like 'DBA/_HIST/_%' ESCAPE '/'
ORDER BY table_name;


TABLE_NAME
-----------------------------
DBA_HIST_ACTIVE_SESS_HISTORY
DBA_HIST_BASELINE
DBA_HIST_BG_EVENT_SUMMARY

DBA_HIST_BUFFERED_QUEUES

DBA_HIST_BUFFERED_SUBSCRIBERS

DBA_HIST_BUFFER_POOL_STAT

DBA_HIST_COMP_IOSTAT

DBA_HIST_CR_BLOCK_SERVER

DBA_HIST_CURRENT_BLOCK_SERVER

DBA_HIST_DATABASE_INSTANCE

DBA_HIST_DATAFILE

DBA_HIST_DB_CACHE_ADVICE

DBA_HIST_DLM_MISC

DBA_HIST_ENQUEUE_STAT

DBA_HIST_EVENT_NAME

DBA_HIST_FILEMETRIC_HISTORY

DBA_HIST_FILESTATXS

DBA_HIST_INSTANCE_RECOVERY

DBA_HIST_INST_CACHE_TRANSFER

DBA_HIST_JAVA_POOL_ADVICE

DBA_HIST_LATCH

DBA_HIST_LATCH_CHILDREN

DBA_HIST_LATCH_MISSES_SUMMARY

DBA_HIST_LATCH_NAME

DBA_HIST_LATCH_PARENT

DBA_HIST_LIBRARYCACHE

DBA_HIST_LOG

DBA_HIST_METRIC_NAME

DBA_HIST_MTTR_TARGET_ADVICE

DBA_HIST_OPTIMIZER_ENV

DBA_HIST_OSSTAT

DBA_HIST_OSSTAT_NAME

DBA_HIST_PARAMETER

DBA_HIST_PARAMETER_NAME

DBA_HIST_PGASTAT

DBA_HIST_PGA_TARGET_ADVICE

DBA_HIST_PROCESS_MEM_SUMMARY

DBA_HIST_RESOURCE_LIMIT

DBA_HIST_ROWCACHE_SUMMARY

DBA_HIST_RULE_SET

DBA_HIST_SEG_STAT

DBA_HIST_SEG_STAT_OBJ

DBA_HIST_SERVICE_NAME

DBA_HIST_SERVICE_STAT

DBA_HIST_SERVICE_WAIT_CLASS

DBA_HIST_SESSMETRIC_HISTORY

DBA_HIST_SESS_TIME_STATS

DBA_HIST_SGA

DBA_HIST_SGASTAT

DBA_HIST_SGA_TARGET_ADVICE

DBA_HIST_SHARED_POOL_ADVICE

DBA_HIST_SNAPSHOT

DBA_HIST_SNAP_ERROR

DBA_HIST_SQLBIND

DBA_HIST_SQLSTAT

DBA_HIST_SQLTEXT

DBA_HIST_SQL_BIND_METADATA

DBA_HIST_SQL_PLAN

DBA_HIST_SQL_SUMMARY

DBA_HIST_SQL_WORKAREA_HSTGRM

DBA_HIST_STAT_NAME

DBA_HIST_STREAMS_APPLY_SUM

DBA_HIST_STREAMS_CAPTURE

DBA_HIST_STREAMS_POOL_ADVICE

DBA_HIST_SYSMETRIC_HISTORY

DBA_HIST_SYSMETRIC_SUMMARY

DBA_HIST_SYSSTAT

DBA_HIST_SYSTEM_EVENT

DBA_HIST_SYS_TIME_MODEL

DBA_HIST_TABLESPACE_STAT

DBA_HIST_TBSPC_SPACE_USAGE

DBA_HIST_TEMPFILE

DBA_HIST_TEMPSTATXS

DBA_HIST_THREAD

DBA_HIST_UNDOSTAT

DBA_HIST_WAITCLASSMET_HISTORY

DBA_HIST_WAITSTAT

DBA_HIST_WR_CONTROL

78 rows selected.

The rolling buffer can be viewed by querying the relevant v$view:
SELECT table_name
FROM dictionary
WHERE table_name like 'V$%SESSION%HISTORY'
ORDER BY table_name;

TABLE_NAME
------------------------------
V$ACTIVE_SESSION_HISTORY
V$SESSION_WAIT_HISTORY

2 rows selected.
You can use a query like this to look at the session activity in a certain peride of time with a query like this:
SELECT session_id, event, COUNT(*), SUM(time_waited)
FROM v$active_session_history
WHERE session_state= 'WAITING'
AND time_waited > 0
AND sample_time >= (sysdate-&MinutesAgo/(24*60))
GROUP BY session_id, event;

Enter value for minutesago: 5
old 5: AND sample_time >= (sysdate-&MinutesAgo/(24*60))
new 5: AND sample_time >= (sysdate-5/(24*60))


SESSION_ID EVENT
COUNT(*) SUM(TIME_WAITED)
---------------- -------- ------------- ------------------------------
149 db file sequential read
3 40857
149 db file scattered read 1 1530
164 db file sequential read
1 12208

The autors also describe how to dump the contents of the rolling buffer to a trace file in USER_DUMP_DEST in order to use sqlldr to load the information into another database.

ALTER SESSION SET events 'immediate trace name ashdump, level 10';

You can also use the utility oradebug to dump the rolling buffer:


SYS @10gR2 SQL > oradebug setmypid
Statement processed
SYS @10gR2 SQL > oradebug unlimit
Statement processed
SYS @10gR2 SQL > oradebug dump ashdump 10
Statement processed
SYS @10gR2 SQL > oradebug tracefile_name
Statement processed
SYS@10gR2 SQL > oradebug tracefile_name
/u01/app/oracle/admin/orcl/udump/orcl_ora_5851.trc

-- first lines: column names for data
-- trace data displayed as comma separated list of values
-- can use sqlldr to load the data into another DB for further analysis
-- can be helpfull in cases of hanging system