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:
select 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, vs.* 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'));