Oracle Scratchpad

February 9, 2012

Session SQL

Filed under: Execution plans,Oracle,Performance — Jonathan Lewis @ 10:48 am BST Feb 9,2012

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.

12 Comments »

  1. 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:

    SQL> conn u1/u1
    Connected.
    SQL> create table t1 (id) as select level from dual connect by level <= 1e6;
    
    Table created.
    
    SQL> create or replace trigger trg1
      2  before update on t1
      3  for each row
      4  declare
      5    l_dummy dual.dummy%type;
      6  begin
      7    select dummy into l_dummy from dual;
      8  end;
      9  /
    
    Trigger created.
    
    SQL> begin
      2    update t1 set id = id;
      3    commit;
      4  end;
      5  /
    
    ... running ...
    

    While the first session is executing, I execute several times the query against v$session in the second session.
    I get all results bellow:

    SQL> select sq.sql_text,
      2         decode(sq.sql_id, s.sql_id, 'sql_id', 'prev_sql_id') src
      3  from v$session s
      4     , v$sql sq
      5  where s.username = 'U1'
      6    and sq.sql_id in (s.sql_id, s.prev_sql_id)
      7  /
    
    SQL_TEXT                                           SRC
    -------------------------------------------------- ------------
    SELECT DUMMY FROM DUAL                             prev_sql_id
    UPDATE T1 SET ID = ID                              sql_id
    
    SQL> /
    
    SQL_TEXT                                           SRC
    -------------------------------------------------- ------------
    SELECT DUMMY FROM DUAL                             prev_sql_id
    
    SQL> /
    
    SQL_TEXT                                           SRC
    -------------------------------------------------- ------------
    SELECT DUMMY FROM DUAL                             sql_id
    

    And after the first session has ended the execution, the result in the second sessont will be:

    SQL> /
    
    SQL_TEXT                                           SRC
    -------------------------------------------------- ------------
    begin   update t1 set id = id;   commit; end;      prev_sql_id
    

    Comment by Valentin Nikotin — February 9, 2012 @ 7:16 pm BST Feb 9,2012 | Reply

    • 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 BST Feb 10,2012 | Reply

  2. If you can get out of your room.

    Comment by Barfo Rama — February 9, 2012 @ 7:46 pm BST Feb 9,2012 | Reply

  3. 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 BST Feb 10,2012 | Reply

  4. [...] 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 BST Feb 10,2012 | Reply

  5. 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 BST Feb 13,2012 | Reply

  6. 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 BST Feb 13,2012 | Reply

    • 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 BST Feb 15,2012 | Reply

  7. [...] can find the answer out on my blog; Oracle Scratchpad. Jonathan [...]

    Pingback by Session SQL – All Things Oracle — February 17, 2012 @ 3:18 pm BST Feb 17,2012 | Reply

  8. [...] 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 BST Feb 21,2012 | Reply

  9. 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:

    select   address || ':  ' || sql_text sql_text
    from v$sqltext
    where address in
            (select  oc.address
            from    v$session        s,
                    v$process        p,
                    v$open_cursor    oc
            where   p.addr     = s.paddr
            and     p.spid = &amp;&amp;unix_pid
            and     s.saddr = oc.saddr)
    

    Comment by Tom Robbins — March 6, 2012 @ 10:05 pm BST Mar 6,2012 | Reply

  10. Tom,

    If you re-read the article you’ll see the statement:


    “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”

    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:

    SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------
    SQL_ID  gcmw0ht7u5xm8, child number 0
    -------------------------------------
    select  /*+ gather_plan_statistics */  address || ':  ' || sql_text
    sql_text from  v$sqltext where  address in  (   select  oc.address
       from v$session        s,                 v$process        p,
            v$open_cursor    oc         where   p.addr     = s.paddr
     and     p.spid = 2840         and     s.saddr = oc.saddr )
    
    Plan hash value: 1481537409
    
    --------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name            | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                 |      1 |        |    115 |00:00:00.01 |       |       |          |
    |*  1 |  FILTER                     |                 |      1 |        |    115 |00:00:00.01 |       |       |          |
    |*  2 |   FIXED TABLE FULL          | X$KGLNA         |      1 |      1 |   6487 |00:00:00.01 |       |       |          |
    |   3 |   NESTED LOOPS              |                 |    931 |      1 |     31 |00:00:00.01 |       |       |          |
    |   4 |    MERGE JOIN CARTESIAN     |                 |    931 |      1 |   2541 |00:00:00.01 |       |       |          |
    |   5 |     NESTED LOOPS            |                 |    931 |      1 |   2541 |00:00:00.01 |       |       |          |
    |   6 |      MERGE JOIN CARTESIAN   |                 |    931 |      1 |   2541 |00:00:00.01 |       |       |          |
    |*  7 |       FIXED TABLE FULL      | X$KGLLK         |    931 |      1 |    121 |00:00:00.01 |       |       |          |
    |   8 |       BUFFER SORT           |                 |    121 |    100 |   2541 |00:00:00.01 |  2048 |  2048 | 2048  (0)|
    |   9 |        FIXED TABLE FULL     | X$KSLWT         |    102 |    100 |   2142 |00:00:00.01 |       |       |          |
    |* 10 |      FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |   2541 |      1 |   2541 |00:00:00.01 |       |       |          |
    |  11 |     BUFFER SORT             |                 |   2541 |      1 |   2541 |00:00:00.01 |  2048 |  2048 | 2048  (0)|
    |* 12 |      FIXED TABLE FULL       | X$KSUPR         |    102 |      1 |    102 |00:00:00.01 |       |       |          |
    |* 13 |    FIXED TABLE FIXED INDEX  | X$KSUSE (ind:1) |   2541 |      1 |     31 |00:00:00.01 |       |       |          |
    --------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter( IS NOT NULL)
       2 - filter(("KGLOBOCT"<>0 AND "INST_ID"=USERENV('INSTANCE')))
       7 - filter(("KGLHDPAR"=:B1 AND "KGLHDNSP"=0 AND "KGLLKHDL"<>:B2 AND "INST_ID"=USERENV('INSTANCE') AND
                  "KGLHDPAR"<>"KGLLKHDL"))
      10 - filter("W"."KSLWTEVT"="E"."INDX")
      12 - filter((TO_NUMBER("KSUPRPID")=2840 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0))
      13 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0
                  AND "ADDR"="S"."KSUSEPRO" AND "S"."ADDR"="KGLLKUSE" AND "S"."INDX"="W"."KSLWTSID"))
    
    41 rows selected.
    
    

    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 BST Mar 7,2012 | 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. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,528 other followers