Spying on sessions
Posted: May 14, 2008 Filed under: tips, Uncategorized 8 CommentsSometimes 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.
select --'alter system kill session '''|| s.SID||',' || s.serial# ||'''' , --'kill -9 ' || p.spid, p.SPID UnixProcess ,s.SID,s.serial#,s.USERNAME,s.COMMAND,s.MACHINE,s.SQL_ADDRESS,s.SQL_HASH_VALUE ,s.program, cpu_time,fetches,disk_reads,buffer_gets,rows_processed, executions,child_latch,event,sql_text,COMMAND_TYPE, 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.
Advertisements
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.
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.
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,
–wait_class,
event,
sql_id,
–blocking_session,
p1text || ‘ – ‘ || p1 p1_text_value,
p2text || ‘ – ‘ || p2 p2_text_value,
p3text || ‘ – ‘ || p3 p3_text_value
–,wait_time,
,seconds_in_wait,
state
from gv$session
where 1=1
and wait_class# ‘6’
and sid sys_context(‘USERENV’, ‘SID’)
–and
–sid = 529
–and
–username in( ‘EXPIMP’, ‘IMHDS’)
order by username
;
Following references should be helpful regarding V$SQLAREA:
Metalink Note: 62143.1
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5180609822543
> 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′
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.
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.
cheers
Pete
Thanks Pete!
Good point. I didn’t realize that bind variables can be used to hide sensitive data.
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 :-)))