Spying on sessions

Sometimes I want to know what a session, a process, a user or a machine is doing, so I wrote a kitchen-sink query that shows me everything I want to know about what a session is running (10g and up).
Its amazing how many issues you can catch simply by running a simple query few times.

--'alter system kill session '''|| s.SID||',' || s.serial# ||'''' ,
--'kill -9 ' || p.spid,
,s.program, cpu_time,fetches,disk_reads,buffer_gets,rows_processed,
sbc.name,to_char(sbc.last_captured,'yyyy-mm-dd hh24:mi:ss'),sbc.value_string
from v$session s
join v$process p on p.ADDR = s.PADDR-- and p.SID in (27817)
left outer join v$sqlarea sa on sa.ADDRESS = s.SQL_ADDRESS
left outer join V$SQL_BIND_CAPTURE sbc on sbc.ADDRESS = s.SQL_ADDRESS
where 1=1
--and s.program like 'imp%'
and s.username ='DEV'
--AND p.SPID in (15179,8304)
--and s.SID in (1019)

The fancy code formatting looks better when Don Seiler is using it. Maybe its time for a new look for the blog.

8 Comments on “Spying on sessions”

  1. Tyler Muth says:

    If the applications running in your database use MODULE, ACTION, and CLIENT_IDENTIFIER (as Application Express does), you should add those columns to your query. In the case of APEX, they’ll tell you what Application, Page, and end-user is related to that session respectively.

  2. prodlife says:

    Nice idea. I think our app uses ACTION, and it can be useful to add.

    I also need to make it RAC compatible. Currently I run it on each node seperately, which makes no sense at all.

  3. Asif Momen says:

    Queries against V$SQLAREA view are expensive, I would suggest you to replace it by V$SQL.

    I use a similar type of query to get the essential details (10g and up):

    set line 10000
    set pagesize 500

    column Sid_Ser_User format a23
    column program format a25
    column event format a30
    column p1_text_value format a23
    column p2_text_value format a23
    column p3_text_value format a23

    select sid || ‘ – ‘ || lpad(serial#, 5, ‘ ‘) || ‘ – ‘ || username Sid_Ser_User,
    substr(program, 1, 25) program,
    p1text || ‘ – ‘ || p1 p1_text_value,
    p2text || ‘ – ‘ || p2 p2_text_value,
    p3text || ‘ – ‘ || p3 p3_text_value
    from gv$session
    where 1=1
    and wait_class# ‘6’
    and sid sys_context(‘USERENV’, ‘SID’)
    –sid = 529
    –username in( ‘EXPIMP’, ‘IMHDS’)
    order by username

    Following references should be helpful regarding V$SQLAREA:

    Metalink Note: 62143.1

  4. Asif Momen says:

    > and wait_class# ‘6′

    Not equal to sign with less than and greater than symbols has disappeared. Also, smiley’s are appearing for closing parenthesis.

    and wait_class# != ‘6′

  5. prodlife says:

    Sorry Asif, wordpress is awful about code formatting in comments.

    I suggest posting code in your blog and linking 🙂

    In any case, note the cool use of V$SQL_BIND_CAPTURE. I thought its the coolest discovery ever. Of course, Laurent Schneider was a year ahead of me: http://laurentschneider.com/wordpress/2007/05/vsql-and-bind-variable.html

    I wonder if this is the season for broken sql slowing systems down.

  6. Hi Chen,

    Nice post and useful. i have been using similar SQL in my database security scanning tools for some years to sample whats going on in the database througout an audit. I also found the bind capture view some time back and use it for forensics and also to demonstrate how flakey VPD can be (and database vault) if other hardening is not in place – i.e. prevent viewing of transient data that may include data protected by VPD for instance.

    Anyway, the reason for my comment, this is a useful SQL but dont expose it to people via the database for reasons above around critical data leakage.



  7. prodlife says:

    Thanks Pete!

    Good point. I didn’t realize that bind variables can be used to hide sensitive data.

  8. amok says:

    No worries Chen. It’s not your fault – You’re just a simple DBA. The problme is that the system is very complex.
    Move to SQL :-)))

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s