Problem solve Get help with specific problems with your technologies, process and projects.

Using SQL Automatic Workload Repository to find Active Session History

SQL expert Karen Morton explains how to use the Automatic Workload Repository (AWR) in SQL to find the Active Session History (ASH) of a session.

The following SQL produces a very useful report of current resource utilization at the time of the query and the high water mark (HWM) since the database was started. What SQL can I add to list the time of the HWM? 

Database & OS = Oracle running on AIX 5300-09 64-bit.


col resource_name format a25 head "Resource"
col current_utilization format 999,999,999,999 head "Current"
col max_utilization format 999,999,999,999 head "HWM"
col intl format a15 head "Setting"
select resource_name, current_utilization, max_utilization, initial_allocation intl
from v$resource_limit
where resource_name in ('processes', 'sessions','enqueue_locks','enqueue_resources',
Resource                           Current              HWM Setting
------------------------- ---------------- ---------------- ---------------
processes                               21               26        150
sessions                                26               34        170
enqueue_locks                           12               23       2380
enqueue_resources                       12               40        968
ges_procs                                0                0          0
ges_ress                                 0                0          0
ges_locks                                0                0          0
ges_cache_ress                           0                0          0
ges_reg_msgs                             0                0          0
ges_big_msgs                             0                0          0
ges_rsv_msgs                             0                0          0
Resource                           Current              HWM Setting
------------------------- ---------------- ---------------- ---------------
gcs_resources                            0                0          0
dml_locks                                0               72        748
max_shared_servers                       1                1  UNLIMITED
14 rows selected.

I do not know of any way to capture a timestamp for when the max_utilization value was last updated.  Since there are many different resources displayed in the output from v$resource_limit, it would likely require that you attempt to mine multiple sources to get that kind of information (if it’s maintained anywhere).  However, I’d suggest that if you’re looking to find specific times when some of these resources are hitting their maximums, you may want to consider using the AWR (Automatic Workload Repository) to see if it can help you.

The AWR captures workload-related performance data at the user and system levels, including performance statistics by different dimensions, metrics, OS statistics, and ASH (Active Session History) data at regular predetermined intervals.  ASH represents the history of the activities of all recent active sessions captured and written to AWR. The ASH data can be rolled up by different dimensions, but since you appear to be concerned about limits that relate to session activity, you would likely want to mine this data at the session level.  You can use the ASH report that can be executed via Enterprise Manager on the Database Page under the Performance tab (or alternatively you can run it manually by executing $ORACLE_HOME/rdbms/admin/ashrpt.sql).

There may be another way to get at this information, but nothing is coming to mind other than this possibility.  If you do happen to locate a source for this information, make sure to update us and share what you learn!

Have a question for Karen Morton? Send an e-mail to editor@searchoracle.com

Next Steps

Find the right tools to make SQL performance tuning faster

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.