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
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.
(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.