Toad World® Forums

Add custom queries for Session browser

I like to run my own queries for current and active sessions.  But I would like run this in Toad's session window cause I like the ability to use the kill session trace button from there.  PL/SQL Developer has this option.

This is the query I like to run that gives me a ton of information.

select
pxs.qcsid as px_parent
, se.sid
, se.serial#
, blocking_session
, username
, osuser
, logon_time
, program
, status
, module
, action
, client_identifier
, client_info
, command
, state
, wait_class
, current_wait_secs
, secs_since_last_wait
, current_wait
, wait_object
, lockwait
, entry_procedure
, current_procedure
, row_wait_obj#
, pdml_enabled
, pdml_status
, pq_status
, current_queue_duration
, service_name
, se.saddr
, paddr
, audsid
, taddr
, machine
, terminal
, sql_address
, sql_hash_value
, sql_id
, sql_child_number
, prev_sql_addr
, prev_sql_id
, prev_child_number
, user#
, cpu
, physical_reads
, logical_reads
, pga_memory_mb
, hard_parses
, soft_parses
, physical_read_pct
, logical_read_pct
, rank_by_cpu
, rank_by_physical_reads
, rank_by_logical_reads
, rank_by_pga
, ranks_combined
, se.inst_id as instance
from ( select ses.inst_id
, sid
, serial#
, blocking_session
, username
, osuser
, program
, status
, module
, action
, client_info
, ( select aa.name from audit_actions aa where aa.action = ses.command ) as command
, state
, wait_class
-- From docs for v$session:
-- If WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the current wait condition.
-- If WAIT_TIME > 0, then SECONDS_IN_WAIT is the seconds since the start of the last wait, and SECONDS_IN_WAIT - WAIT_TIME / 100 is the active seconds since the last wait ended.
, case when ses.wait_time = 0 then ses.seconds_in_wait end as current_wait_secs
, case when ses.wait_time > 0 then ses.seconds_in_wait - ses.wait_time / 100 end as secs_since_last_wait
, event as current_wait
, ( select rtrim(owner || '.' || object_name || '.' || o.subobject_name,'.') from dba_objects o
where o.object_id = row_wait_obj# ) as wait_object
, lockwait
, ( select rtrim(pro_entry.object_name || '.' || pro_entry.procedure_name,'.')
from dba_procedures pro_entry
where pro_entry.object_id = ses.plsql_entry_object_id
and pro_entry.subprogram_id = ses.plsql_entry_subprogram_id ) as entry_procedure
, ( select rtrim(pro_current.object_name || '.' || pro_current.procedure_name,'.')
from dba_procedures pro_current
where pro_current.object_id = ses.plsql_object_id
and pro_current.subprogram_id = ses.plsql_subprogram_id ) as current_procedure
--, row_wait_file#, row_wait_block#, row_wait_row#
, row_wait_obj# -- Needed for 'Current wait' detail query
, logon_time
--, last_call_et
, pdml_enabled
, pdml_status
--, pddl_status
, pq_status
-- , resource_consumer_group -- Uncomment if using Oracle Resource manager
, current_queue_duration
, client_identifier
, service_name
-- , sql_trace, sql_trace_waits, sql_trace_binds
, saddr
--, process -- client process not normally useful
, paddr
, audsid
, taddr
, machine
, terminal
, sql_address
, sql_hash_value
, sql_id
, sql_child_number
--, ses.sql_exec_id -- 11g
--, ses.sql_exec_start -- 11g
, prev_sql_addr
--, prev_hash_value
, prev_sql_id
, prev_child_number
--, failover_type, failover_method, failed_over
--, blocking_session_status, blocking_instance
--, server
, user#
, round(sm.cpu,1) as "CPU"
, sm.physical_reads
, sm.logical_reads
, round(sm.pga_memory/1048576,1) as pga_memory_mb
, sm.hard_parses, sm.soft_parses
, round(sm.physical_read_pct,1) as physical_read_pct
, round(sm.logical_read_pct,1) as logical_read_pct
, row_number() over (order by sm.cpu desc nulls last) as rank_by_cpu
, row_number() over (order by sm.physical_reads desc nulls last) as rank_by_physical_reads
, row_number() over (order by sm.logical_reads desc nulls last) as rank_by_logical_reads
, row_number() over (order by sm.pga_memory desc nulls last) as rank_by_pga
, row_number() over (order by sm.cpu desc nulls last) +
row_number() over (order by sm.physical_reads desc nulls last) +
row_number() over (order by sm.logical_reads desc nulls last) +
row_number() over (order by sm.pga_memory desc nulls last) as ranks_combined
from gv$session ses
left join gv$sessmetric sm
on sm.session_id = ses.sid
and sm.serial_num = ses.serial#
and sm.inst_id = ses.inst_id
where username is not null
) se
left join gv$px_session pxs
on pxs.sid = se.sid
and pxs.serial# = se.serial#
and pxs.inst_id = se.inst_id