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
