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
begin
	dbms_session.set_identifier('your_identifier');
	/* other useful options may be */
	dbms_application_info.set_action('your_action');
	dbms_application_info.set_client_info('your_client_info');
	dbms_application_info.set_module('your_module', 'your_action');
	/* */
end;
/

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)
SELECT sid,
       client_identifier,
       module,
       action,
       client_info
  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.

About these ads

1 Comment »

  1. Session Level ASH Reports…

    I noticed a post on H.Tonguç Yılmaz’s blog about filtering ASH data to look at the actions of a specific instrumented query. There are a few strange things that I was going to comment on but the blog requires me to use a WordPress account before I can…

    Trackback by Doug's Oracle Blog — July 2, 2009 @ 2:56 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: