A Cool Way to Get Active SQLs in Oracle Database

 During the course of Oracle database healthchecks or day to day monitoring, or during the trouble shooting of any performance issue, the following query can be very handy to get active SQLs in the Oracle database:

set pagesize 999

set lines 250

col inst_id format 9

col username format a13

col prog format a10 trunc

col sql_text format a41 trunc

col sid format 9999

col child for 99999

col avg_etime for 999,999.99

select a.inst_id, sid, substr(program,1,19) prog, address, hash_value, b.sql_id, child_number child, plan_hash_value, executions execs,

(elapsed_time/decode(nvl(executions,0),0,1,executions))/1000000 avg_etime,

sql_text

from gv$session a, gv$sql b

where status = 'ACTIVE'

and username is not null

and a.sql_id = b.sql_id

and a.sql_child_number = b.child_number

and a.inst_id = b.inst_id

and sql_text not like 'select a.inst_id, sid, substr(program,1,19) prog, address, hash_value, b.sql_id, child_number child,%' -- don't show this query

order by sql_id, sql_child_number

/