Session SQL

From time to time a request for a query to “show the current SQL” for each session appears on the internet. It’s not really difficult to write something suitable, but this particular request is an interesting demonstration of how much inertia there is in the Oracle world. Here’s a recent suggestion of what the query should look like:

select
        sess.sid, sess.serial#, sess.username, sess.status,
        sess.osuser, sess.machine, sess.module, sess.event,
        sess.logon_time, ss.sql_id, ss.sql_text
from
        v$session sess,
        v$sql     ss
where
        sess.sql_id   = ss.sql_id
and     sess.username = 'EFTUSER'
;

The query has an error in it that appears extremely frequently in response to this request. Can you spot what it is ? (On the plus side, the query references v$sql rather than v$sqlarea – so it’s not too nasty.)

To force a little break between my question and my answer – here’s a picture of the view from my hotel window at the moment. I took this with my mobile phone so the quality isn’t all that good – if you can’t grasp quite what the image is, it’s the snow piled up against my window to a depth of 2 feet (60 cm) at the lowest. The second picture explains why the view looks the way it does – my room was on the top floor, in the attic – the picture isn’t all that good because my mobile phone didn’t like the cold.

I’m in Sarajevo, and they had over 3 feet (90cm) of snow fall here the day before I arrived; the temperature was down to -23 centigrade (celsius) this morning – luckily it was up to a bracing -10 degrees by the time I got up.

The error is that the join is only on the sql_id and it’s possible that there are many child cursors for the same statement in v$sql – and they’ll all have the same sql_id. Since 10g v$session has made the child number available as sql_child_number, so the query ought to include a join on that column too.

and     sess.sql_child_number = ss.child_number

Enhancement

You may want to do more than just report the current SQL statement for each session, of course and v$session actually has information about two different SQL statements; as well as the sql_id the view also reports the prev_sql_id (and child number) – and you may find it useful to report this statemement as well since (for example) the current sql_id might report a pl/sql block while the prev_sql_id reports the SQL being executed in that block.

A simple enhancement to the query might then be to change the join predicate to something like the following:

and     (
            (ss.sql_id = sess.sql_id      and ss.child_number = sess.sql_child_number)
         or (ss.sql_id = sess.prev_sql_id and ss.child_number = sess.prev_child_number)
        )

I’ll leave it (temporarily) as an exercise to the reader to explain why this is probably a bad strategy – but right now I’m off to the airport to see if my flight is still going to happen. The weather is much better than when I arrived so I don’t expect any problems, but the Austrian Airways wouldn’t let me check-in online last night, and doesn’t even show the second leg of the journey.

Update

(13th Feb early morning Minneapolis, which is warm and mild at present (hardly below freezing at all))

Valentin Nikotin has covered all the important points in his comments below. The most important one, to my mind, is the one about the execution path. When running any “got it off the Internet” SQL against the dynamic performance views, don’t forget to check the execution path and the performance threats before you use it on production. If you modify the original query to use the more complex predicate then you’ll find that the optimizer has to do a “full tablescan” of v$sql – which means thrashing its way through the entire library cache hammering at the library cache latches (or mutexes, in 11g) as it goes. This is not a nice thing to do on a busy system. As Valentin points out, you also don’t know which statement is the “current” and which the “prev” – and even then, as his example shows, Oracle won’t be telling you everything you might want to know anyway (even if you do a very fast repeated sample (a la Tanel Poder latchprofx) of v$session.

Given the warning about missing data, the type of query you need (to get an efficient acces path) will involve a union, something of this sort:

select
	ses.sid, ses.serial#, ses.username, ses.status,
--	ses.osuser, ses.machine, ses.module, 
	ses.EVENT, ses.logon_time, ses.curr_prev,
	sql.sql_id, sql.child_number, sql.sql_text
from	
	(
	select
		ses1.sid, ses1.serial#, ses1.username, ses1.status,
	--	ses1.osuser, ses1.machine, ses1.module, 
		'Current' curr_prev,
		ses1.event, ses1.logon_time,
		ses1.sql_id, ses1.sql_child_number, ses1.sql_address
	from
		v$session	ses1
	union all
	select
		ses2.sid, ses2.serial#, ses2.username, ses2.status,
	--	ses2.osuser, ses2.machine, ses2.module, 
		'Previous' curr_prev,
		ses2.event, ses2.logon_time,
		ses2.prev_sql_id, ses2.prev_child_number, ses2.prev_sql_addr
	from
		v$session	ses2
	)		ses,
	v$sql		sql
where
	ses.username	 = 'TEST_USER'
and	ses.sql_address != '00'
and	ses.status	 = 'ACTIVE'
and	sql.sql_id	 = ses.sql_id
and	sql.child_number = ses.sql_child_number
order by
	ses.sid, ses.curr_prev
;

Footnote: The usual proviso applies – this is just a quick script I hacked together to make a point, it’s not “production-ready” code.