Monthly Archives: February 2012

Script Sharing – Active Session Info

Just thought I would post a script that I use very frequently to see active sessions and a little bit about them, mostly so I don’t ever lose it.

First, some session info for active sessions:

logon_time, inst_id, sid, serial#,
username, module, action,
sql_id, sql_child_number,
event, state, seconds_in_wait seconds, wait_class,
p1text, p1, p2text, p2,
from gv$session vs
where status = 'ACTIVE'
order by vs.username, vs.logon_time desc;

I like to use dbms_application_info.set_module in my code to set the module and action columns of gv$session to something meaningful. I will post about that later this week, as instrumenting your code makes troubleshooting orders of magnitude easier. The other things I pay attention to are typically event and state. From there, I can see what I need to look into further.

I will commonly move from session info to a level deeper, and look further into the SQL statement (found by using data in SQL_ID and CHILD_NUMBER columns from above):

select vs.disk_reads, vs.direct_writes, vs.buffer_gets, vs.cpu_time, vs.elapsed_time, vs.*
from gv$sql vs
where sql_id = :sql_id
and child_number = :child_num;

And then I may take a look at the execution plan for that query:

select *
from table(dbms_xplan.display_cursor(:sql_id,:child_num,'IOSTATS'));