Catching a session
waiting on a resource used to be hit or miss. Let’s take a look at how Oracle
has helped give us a better mousetrap for seeing the waits experienced by
It used to be that a DBA
would sit in front of the terminal screen and continually hit the ENTER key to
watch activity in the V$SESSION_WAIT view to try and get a glimpse of the
bottleneck and wait activity for connected sessions. This in itself was a very
tedious task and honestly I would often get paranoid that I would miss some
valuable information if I did not hit the ENTER key fast enough and thus I
would never see the important wait event. Yes there was the V$SESSION_EVENT
view that gave us a total for all waits but we never knew when the wait might
have occurred. DBAs very quickly got smarter and started to sample the data in
the V$SESSION_WAIT table and write it out to another table or dump it to an
external list file. This was fine but the overhead of actually creating and
then running the task that would populate these statistics could get high, and
management of the task could get tedious.
To the Rescue
With Oracle 10g this has
all been nicely taken over for us. Oracle will now sample data through internal
kernel code for statistics similar to what was seen in the V$SESSION_WAIT view and
store it in a limited amount of buffer space, 2 Meg per CPU. We can view these
statistics by querying the new V$ACTIVE_SESSION_HISTORY view. The
V$ACTIVE_SESSION_HISTORY view contains one row of sampled activity for each
session that was active, either on the CPU or actually waiting for a resource. As
this buffer gets full, because of the activity on the system, Oracle will move
the captured statistics to disk as part of the Automatic Workload Repository
(AWR) snapshot mechanism and it will now be available through the
DBA_HIST_ACTIVE_SESS_HISTORY view. When querying for current information,
depending on the activity of your system, you may need to go to both views to
construct a valid picture of what has happened. The important thing to remember
here is that this is historical activity of the waits that have occurred for
SQL executed and we can now go back in time and look at what was the true cause
of performance bottlenecks. Be forewarned that since the statistics are stored
in the rotating buffer and are only moved to the DBA_HIST_ACTIVE_SESS_HISTORY
through a snapshot, you may loose statistics. In addition, when
the snapshot mechanism (AWR) does kick in and does its work to move database
statistics between V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY,
it only takes a sampling of the data in V$ACTIVE_SESS_HISTORY for the snapshot.
Furthermore, when AWR does kick in, it
only takes a sampling of the data in V$ACTIVE_SESS_HISTORY for the snapshot.
What this means is that you need to either catch what you want in the
V$ACTIVE_SESS_HISTORY view before a snapshot or have your own scrapping utility
again. For this reason, this article will only be concerned with querying off
of the V$ACTIVE_SESS_HISTORY view to show a few ways you might want to query
for information that had not been available before.
What resource is currently in high demand?
While we have always been able to look at the system or a
currently running session as a whole and see the resources that are in most use
through the V$SYSTEM_EVENT and V$SESSTAT views, we really have not ever had a
good way to determine for a current time frame what the resources being used
were. This query will give you for the last 30 minutes those resources that are
in high demand on your system.
What user is waiting the most?
Again, the power in this SQL query is that we are now able
to determine what user is consuming the most resource at a point in time,
independent of the total resources that the user has used. You can now, with
this query, answer the question of who is waiting the most for resources at a
point in time. If a user calls you up on the phone and says they are
experiencing delays, you can use this query to verify that they are actually
waiting in the database for a result set for a given time period. This SQL is
written for a 30-minute interval from current system time so you may need to
What SQL is currently using the most resources?
This query will get you started in the proper direction of
zeroing in on what SQL statement is consuming the most resource wait times on
your system. No longer do you have to go to the V$SQLAREA and try to pick out
the top 10 or 20 SQL hogs on your system by disk reads or executions. Now you
really know what SQL statements are consuming resources and waiting the most.
These are the SQL that you really need to tune because the fact that a SQL
statement performs 20,000 reads does not mean that it is a bottleneck in your
What object is currently causing the highest resource waits?
This is a great query. Now we can actually see which objects
a SQL statement is hitting. Moreover, if you take a further look at the V$ACTIVE_SESSION_HISTORY
view you will see that you can tailor this query to report on the actual blocks
that are being accessed within the objects for the SQL statement. This is great
help in determining if you may need to reorg your object or redistribute to
reduce the contention on that object.
The new information that Oracle has given us through the
V$ACTIVE_SESSION_HISTORY view is invaluable. Now we can really zero in on those
resources, session, users, SQL statements, and objects that are causing our
systems to be resource intensive. By reducing the resource usage, you can in
fact tune your systems better.