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.
In the complex cases it’s not possible to see what is going on.
I create an example to show what I have meant.
In the first session I create the table and the trigger on it, and then run the PL/SQL block with an update statement:
While the first session is executing, I execute several times the query against v$session in the second session.
I get all results bellow:
And after the first session has ended the execution, the result in the second sessont will be:
Comment by Valentin Nikotin — February 9, 2012 @ 7:16 pm GMT Feb 9,2012 |
Here are two more observations about the using predicate with “OR” :
1. You can not determine what it was actually – SQL_ID or PREV_SQL_ID;
2. The query will have worse plan with NL + FIXED TABLE FULL instead of FIXED TABLE FIXED INDEX;
Comment by Valentin Nikotin — February 10, 2012 @ 3:29 pm GMT Feb 10,2012 |
If you can get out of your room.
Comment by Barfo Rama — February 9, 2012 @ 7:46 pm GMT Feb 9,2012 |
I’ll leave it (temporarily) as an exercise to the reader to explain why this is probably a bad strategy
Is it because the query is accessing memory structures (and not tables) which are not guaranteed to be read-consistent? So a join (between v$session and v$sql) will not necessarily give correct results. I remember Tanel poder using this to his advantage when he wrote his cool session snapper toolkits.
Comment by Narendra — February 10, 2012 @ 9:25 am GMT Feb 10,2012 |
[…] From time to time a request for a query to “show the current SQL” for each session appears on the internet. Jonathan answers. […]
Pingback by Log Buffer #258, A Carnival of the Vanities for DBAs | The Pythian Blog — February 10, 2012 @ 9:56 am GMT Feb 10,2012 |
It makes sense to remeber of the using gv$ versions of views on the RAC environment.
Comment by Valentin Nikotin — February 13, 2012 @ 3:54 pm GMT Feb 13,2012 |
Though it’s very expensive to use gv$, and it’ll be better to query v$ having connected to the proper node.
Comment by Valentin Nikotin — February 13, 2012 @ 8:29 pm GMT Feb 13,2012 |
Valentin,
True on both counts.
It’s also important to remember to include the join on “instance number” when using the gv$ – I’ve seen people forget that occasionally.
Comment by Jonathan Lewis — February 15, 2012 @ 2:33 am GMT Feb 15,2012 |
[…] can find the answer out on my blog; Oracle Scratchpad. Jonathan […]
Pingback by Session SQL – All Things Oracle — February 17, 2012 @ 3:18 pm GMT Feb 17,2012 |
[…] thought it was pretty cold in Sarajevo a couple of weeks ago, and therefore fairly mild in Minneapolis and Denver when the temperature was […]
Pingback by Weather « Oracle Scratchpad — February 21, 2012 @ 9:24 pm GMT Feb 21,2012 |
If you also want to see the open cursors, including recursive sql, you can add a select on v$open_cursor.
This query uses unix PID to find the appropriate sql:
Comment by Tom Robbins — March 6, 2012 @ 10:05 pm GMT Mar 6,2012 |
Tom,
If you re-read the article you’ll see the statement:
Running your query and pulling the plan from memory, this is what I got shortly after startup on a small 11.1 system I had at hand:
Your query converts the IN subquery to an EXISTS subquery and operates it as a filter subquery that is called (in theory) for every row in x$kglna (v$sqltext). This is not a path you would want to see on a large, busy, production system.
Comment by Jonathan Lewis — March 7, 2012 @ 11:02 am GMT Mar 7,2012 |