Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

July 1, 2009

Instrumented Code is Better: An Example on How to Generate Session Level ASH Reports

Filed under: Oracle How To,Oracle Performance — H.Tonguç Yılmaz @ 9:08 pm

After 10g if you have its license ASH based performance monitoring is an alternative to the traditional SQL Tracing in my opinion. You may either use EM to generate ASH reports or directly query ASH dictionary. The handy additional hint here is that you can set filters to ASH data in order to focus on a specific session, the filter options at ASH report screen at EM can be used for this purpose or below is an example to monitor the waits from v$active_session_history view.

-- at session 1
	/* other useful options may be */
	dbms_application_info.set_module('your_module', 'your_action');
	/* */

select ... ;

-- while query at session 1 continues to execute switch to session 2
-- note that no commit is needed to see the client_identifier set from the first session(autonomous transaction)
  FROM v$session
 WHERE client_identifier = 'your_identifier';
       SID CLIENT_IDENTIFIER                                                MODULE                                           ACTION                           CLIENT_INFO
---------- ---------------------------------------------------------------- ------------------------------------------------ -------------------------------- ----------------------------------------------------------------
       273 your_identifier                                                  your_module                                      your_action                      your_client_info

-- filtered ASH information 
-- Now query v$active_session_history columns you may be interested with client_id = 'your_identifier' and additional filters you may like 

dbms_session and dbms_application_info supplied packages’ set options can be very handy like in the above example, credits goes to Mark Rittman for this hint.

[Update on July 2 2009 ]
After Doug Burns’s comments(check out the pingback below) on this post I updated the header and the example.

With this short post I wanted to share the idea of setting client identifier in an application may help especially during monitoring/troubleshooting and wanted to give an example on ASH dictionary. For example using database services even you are running on single node environment also helps as filters with the instance activity reports. Additionally I wanted to highlight the effort to get the ASH information over 10046 SQL Tracing efforts for a database developer, since with 10046 tracing a typical developer will need a DBA or unix admin assistance to ftp the produced trace files where as simple SQL queries to ASH dictionary will guide you to understand the waits for example specific to a session.

So anyway, thanks for Doug Burns’s time to correct the ASH example on this post and provided details on his blog.

Create a free website or blog at WordPress.com.


Get every new post delivered to your Inbox.

Join 89 other followers