Sessions with details – Keep Learning Keep Living

Hi,

Today I just want to share a small select statement that I used (my original one is a little bit longer). I use this to get as much as “important” data from sessions in the database. By saying “important”, I mean the data that I use to identify the session and what it does. Especially while tracking active session, it becomes handy for me.

with proc as (select  owner,object_name,procedure_name,object_id,subprogram_id from cdb_procedures)
select s.sid, s.serial#, s.username, s.osuser, s.client_identifier, s.module, s.action, s.program, s.blocking_session, 
       s.event, s.wait_class, s.state, s.last_call_et, s.wait_time_micro,
       Decode(obj_ent.owner , Null, Null, obj_ent.owner || '.'||obj_ent.object_name || Nvl2(obj_ent.procedure_name, '.' || obj_ent.procedure_name, null)) entry_plsql_object,
       Decode(obj_act.owner, Null, Null, obj_act.owner || '.'||obj_act.object_name || Nvl2(obj_act.procedure_name, '.' || obj_act.procedure_name, null)) active_plsql_object,
       round(used_ublk * to_number(par.value) /1024/1024, 3) undo_size_mb,
       p.spid os_pid, round(pga_alloc_mem/1024/1024, 2) alloc_mem_mb, round(pga_freeable_mem/1024/1024, 2)free_mem_mb ,round(pga_used_mem/1024/1024, 2) used_mem_mb,
       sql.sql_text,
       s.machine, 
       s.client_info,
       s.*,
       'alter system kill session ''' || s.sid || ','|| s.serial# || ',@'|| s.INST_ID||''' immediate;' kill_script 
from   gv$session s
         join v$parameter par on par.name ='db_block_size'
         left join gv$transaction t on s.taddr = t.addr and t.inst_id = s.inst_id
         left join gv$process p on s.paddr = p.addr and p.inst_id = s.inst_id
         left join gv$sqlarea sql on s.sql_id = sql.sql_id and sql.inst_id = s.inst_id and sql.con_id = s.con_id
         --top plsql object if it is an plsql
         left join proc obj_ent on s.plsql_entry_object_id     = obj_ent.object_id and 
                                             s.plsql_entry_subprogram_id = obj_ent.subprogram_id and
                                             s.con_id                    = obj_ent.con_id
         --plsql object that currently run, null if an sql is running.
         left join proc obj_act on s.plsql_object_id     = obj_act.object_id and 
                                             s.plsql_subprogram_id = obj_act.subprogram_id and
                                             s.con_id              = obj_act.con_id
where 1=1
  --and s.wait_class <> 'Idle'
  --and s.type <> 'BACKGROUND'
  --and s.status = 'ACTIVE'
order by s.wait_time_micro desc nulls last;

with proc as (select owner,object_name,procedure_name,object_id,subprogram_id from cdb_procedures)

select s.sid, s.serial#, s.username, s.osuser, s.client_identifier, s.module, s.action, s.program, s.blocking_session,

s.event, s.wait_class, s.state, s.last_call_et, s.wait_time_micro,

Decode(obj_ent.owner , Null, Null, obj_ent.owner || '.'||obj_ent.object_name || Nvl2(obj_ent.procedure_name, '.' || obj_ent.procedure_name, null)) entry_plsql_object,

Decode(obj_act.owner, Null, Null, obj_act.owner || '.'||obj_act.object_name || Nvl2(obj_act.procedure_name, '.' || obj_act.procedure_name, null)) active_plsql_object,

round(used_ublk * to_number(par.value) /1024/1024, 3) undo_size_mb,

p.spid os_pid, round(pga_alloc_mem/1024/1024, 2) alloc_mem_mb, round(pga_freeable_mem/1024/1024, 2)free_mem_mb ,round(pga_used_mem/1024/1024, 2) used_mem_mb,

sql.sql_text,

s.machine,

s.client_info,

s.*,

'alter system kill session ''' || s.sid || ','|| s.serial# || ',@'|| s.INST_ID||''' immediate;' kill_script

from gv$session s

join v$parameter par on par.name ='db_block_size'

left join gv$transaction t on s.taddr = t.addr and t.inst_id = s.inst_id

left join gv$process p on s.paddr = p.addr and p.inst_id = s.inst_id

left join gv$sqlarea sql on s.sql_id = sql.sql_id and sql.inst_id = s.inst_id and sql.con_id = s.con_id

--top plsql object if it is an plsql

left join proc obj_ent on s.plsql_entry_object_id = obj_ent.object_id and

s.plsql_entry_subprogram_id = obj_ent.subprogram_id and

s.con_id = obj_ent.con_id

--plsql object that currently run, null if an sql is running.

left join proc obj_act on s.plsql_object_id = obj_act.object_id and

s.plsql_subprogram_id = obj_act.subprogram_id and

s.con_id = obj_act.con_id

where 1=1

--and s.wait_class <> 'Idle'

--and s.type <> 'BACKGROUND'

--and s.status = 'ACTIVE'

order by s.wait_time_micro desc nulls last;

it is just a basic gv$session and some other view joins. When I want to check my active sessions, I uncomment “s.status = ‘ACTIVE'” line. I mostly uncomment type<> ‘BACKGROUND’ condition too because probably I am checking some user issues.

for the first columns, I am trying to get information about the session:

sid, serial#: unique identifier for a session
username: 🙂
osuser: Operating user name of the session user
client_identifier: this is set by session itself. by default many application set information about user. for example Oracle APEX applications set application username and apex session id.
module: also set by session itself, Oracle, for example, sets “DBMS_SCHEDULER” for scheduler job session. that way you can separate sessions or APEX applications set active application and page number.
action: also set by session itself, Oracle, for example, sets job name for scheduler jobs.
program: is the information about the connected applicaiton, like if you use sql developer, it shows “SQL Developer” or “Oracle REST Data Services” if this is an apex session.
blocking_session: SID of the blocker session of this session.
event: what this session is doing right now.
wait_class: group name for the event. not all events are actions, for example “pipe get” event is an “Idle” event, you can filter Idle events, since they are not actually doing something right now.
state: obvious (for detail check v$session in documentation.) a session could be in WAITING or WAITED SHORT TIME etc states. for a short brief, WAITING doesn’t mean that session is waiting a job or task! for example “jobq slave wait” event is for idle scheduler job processes that wait to a job to run. in the state of this session is WAITING but event is “jobq slave wait” so it does not waiting for a resource or something. it is just waiting to be assigned to a task.
last_call_et: last call execution time, if the session is active then this column shows you for how long the session is active.
wait_time_micro: amount of time that session is waiting if it is in a wait.

these information are important to me. next I wanted to see what is the PLSQL object that this session runs if it is a plsql. “entry_plsql_object” column shows the top plsql object if session started a plsql object at the first place. if session started a SQL statement then this column will be empty. “active_plsql_object” column is for currently running plsql object. for example a session runs a select statement and this select statement has a plsql function in select list. while the plsql function is running, this column will be showing its name.

undo_size_mb is how much undo space is being used by the session in unit of megabytes. if session has a transaction (doing an insert/update/delete), you can track the amount of undo size.

next column is SQL_TEXT, what is that session is running now. of course this should be one of the first columns that I want to see but mostly sqls are long and that column consume too much space in the grid, so I put it after session basic information.

machine column is the hostname for session. I can understand if this session is connected from an application server or a developer laptop etc.

client_info might have additional data about session. this is set by session itself.

then “s.*” whatever gv$session has.

and finally my “kill script” just incase I wanted to kill a session for an emergency.

I created view based on this query and add my filters (like status = ‘ACTIVE’) on it but to do that you must replace “s.*” with actual column names because some of them will be duplicated since I selected those columns by their name at the begining.

what else you can add? many things of course for example “statistics”. Statistics are important information for a session like how much cpu is used or how much physical read has been done or how much sort operation on disk has been done by this session etc. There are around 2000 statistics for a session. Let me show some and you can join the query below to query above if you wish:

select inst_id, sid, 
       Min(Decode(name, 'consistent gets',                value)) consistent_gets               ,
       Min(Decode(name, 'CPU used by this session',       value)) CPU_used_by_this_session      ,
       Min(Decode(name, 'CPU used when call started',     value)) CPU_used_when_call_started    ,
       Min(Decode(name, 'cumulative DB time in requests', value)) cumulative_DB_time_in_requests,
       Min(Decode(name, 'DB time',                        value)) DB_time                       ,
       Min(Decode(name, 'lob reads',                      value)) lob_reads                     ,
       Min(Decode(name, 'lob writes',                     value)) lob_writes                    ,
       Min(Decode(name, 'parse time cpu',                 value)) parse_time_cpu                ,
       Min(Decode(name, 'parse time elapsed',             value)) parse_time_elapsed            ,
       Min(Decode(name, 'physical read bytes',            value)) physical_read_bytes           ,
       Min(Decode(name, 'physical write bytes',           value)) physical_write_bytes          ,
       Min(Decode(name, 'redo size',                      value)) redo_size                     ,
       Min(Decode(name, 'session pga memory',             value)) session_pga_memory            ,
       Min(Decode(name, 'session pga memory max',         value)) session_pga_memory_max        ,
       Min(Decode(name, 'session uga memory',             value)) session_uga_memory            ,
       Min(Decode(name, 'session uga memory max',         value)) session_uga_memory_max        ,
       Min(Decode(name, 'sorts (disk)',                   value)) sorts_disk                    ,
       Min(Decode(name, 'sorts (memory)',                 value)) sorts_memory                  ,
       Min(Decode(name, 'table fetch continued row',      value)) table_fetch_continued_row     ,
       Min(Decode(name, 'user commits',                   value)) user_commits
from (select n.name, n.class, s."INST_ID",s."SID",s."STATISTIC#",s."VALUE",s."CON_ID"
      from gV$sesstat s
             join v$statname n on s.statistic#=n.statistic#)
where name in (
  'consistent gets',
  'CPU used by this session', 'CPU used when call started',
  'cumulative DB time in requests', 'DB time',
  'lob reads', 'lob writes',
  'parse time cpu', 'parse time elapsed',
  'physical read bytes', 'physical write bytes',
  'redo size',
  'session pga memory', 'session pga memory max',
  'session uga memory', 'session uga memory max',
  'sorts (disk)', 'sorts (memory)',
  'table fetch continued row',
  'user commits'
  )
group by inst_id, sid;

select inst_id, sid,

Min(Decode(name, 'consistent gets', value)) consistent_gets ,

Min(Decode(name, 'CPU used by this session', value)) CPU_used_by_this_session ,

Min(Decode(name, 'CPU used when call started', value)) CPU_used_when_call_started ,

Min(Decode(name, 'cumulative DB time in requests', value)) cumulative_DB_time_in_requests,

Min(Decode(name, 'DB time', value)) DB_time ,

Min(Decode(name, 'lob reads', value)) lob_reads ,

Min(Decode(name, 'lob writes', value)) lob_writes ,

Min(Decode(name, 'parse time cpu', value)) parse_time_cpu ,

Min(Decode(name, 'parse time elapsed', value)) parse_time_elapsed ,

Min(Decode(name, 'physical read bytes', value)) physical_read_bytes ,

Min(Decode(name, 'physical write bytes', value)) physical_write_bytes ,

Min(Decode(name, 'redo size', value)) redo_size ,

Min(Decode(name, 'session pga memory', value)) session_pga_memory ,

Min(Decode(name, 'session pga memory max', value)) session_pga_memory_max ,

Min(Decode(name, 'session uga memory', value)) session_uga_memory ,

Min(Decode(name, 'session uga memory max', value)) session_uga_memory_max ,

Min(Decode(name, 'sorts (disk)', value)) sorts_disk ,

Min(Decode(name, 'sorts (memory)', value)) sorts_memory ,

Min(Decode(name, 'table fetch continued row', value)) table_fetch_continued_row ,

Min(Decode(name, 'user commits', value)) user_commits

from (select n.name, n.class, s."INST_ID",s."SID",s."STATISTIC#",s."VALUE",s."CON_ID"

from gV$sesstat s

join v$statname n on s.statistic#=n.statistic#)

where name in (

'consistent gets',

'CPU used by this session', 'CPU used when call started',

'cumulative DB time in requests', 'DB time',

'lob reads', 'lob writes',

'parse time cpu', 'parse time elapsed',

'physical read bytes', 'physical write bytes',

'redo size',

'session pga memory', 'session pga memory max',

'session uga memory', 'session uga memory max',

'sorts (disk)', 'sorts (memory)',

'table fetch continued row',

'user commits'

)

group by inst_id, sid;

Some statistics per session and statistics are listed as column (I know I can use PIVOT but basically these are the same).

I hope this statements will be useful for you and give some new idea to develop. I would be glad if you share your ideas too 🙂

Wish you all healthy and happy days.

edit: I made a small change on the first statement. wait_class can be queried from v$session no need to join v$event_name.

edit2: I love sharing staff. It lets me find my own errors. if you run the session select in the CDB then you might see some sessions are duplicated because same sql_id might be exist in PDB too so a con_id condition must be added to gv$sqlarea join.

Leave a Reply Cancel reply