 |
|
Oracle
Active Session History (ASH)
Oracle Tips by Burleson Consulting |
This
is an excerpt from "Oracle 10g New Features for Administrators" by
Ahmed Baraka.
Oracle Database 10g now collects the Active Session
History (ASH) statistics (mostly the wait statistics for different
events) for all active sessions every second, and stores them in a
circular buffer in the SGA.
The ASH feature uses about 2MB of SGA memory per
CPU.
Current Active Session Data
V$ACTIVE_SESSION_HISTORY enables you to access the
ASH statistics. A database session is considered active if it was on
the CPU or was waiting for an event that didn’t belong to the Idle
wait class (indicated by SESSION_STATE column).
DBA_HIST_ACTIVE_SESSION_HISTORY View
This view in fact is a collection of snapshots from
the V$ACTIVE_SESSION_HISTORY view. It is populated either by MMON
during its regular snapshot capturing or by MMNL when the memory
buffer is full.
Generate ASH Reports
In Oracle Release 2, you can generate ASH Report.
This is a digest of the ASH samples that were taken during a time
period. Some of the information it shows are top wait events, top
SQL, top SQL command types, and top sessions, among others.
On Database Control:
Performance -> Run ASH Report button
On SQL*Plus:
Run the following script
$ORACLE_HOME/rdbms/admin/ashrpt.sql
 |
If you like Oracle tuning, see the
book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
