快速查看Oracle数据库session状态

设置-- for w.sql--set lines 178-- Purpose: Show session state-- Created: Luke(Zhaoping.Lu@oracle.com),20140920set pages 90set head off timing off feed offselect to_char(sysdate,'yymmdd hh24:mi:ss')||','

我叫农

3727人浏览 · 2021-10-13 17:54:18

我叫农 · 2021-10-13 17:54:18 发布

设置

-- for w.sql
--set lines 178
-- Purpose: Show session state

set pages 90

set head off timing off feed off
select to_char(sysdate,'yymmdd hh24:mi:ss')||','||
 instance_name||'@'||host_name||'('||utl_inaddr.get_host_address||')'||
      ','||open_mode||
      ','||decode(log_mode,'NOARCHIVELOG','NOARCHIVELOG',decode(archiver,'STOPPED','Archiver stopped','Archiver running'))||
      ','||platform_name ||
      ',v'||version ||
      ',Up:'||to_char(startup_time,'yymmdd hh24:mi:ss') info
       from v$instance,v$database;
--/* */
set head on feed on timing on

set verify off
def prog_len=16
col event format a38
col program format a28 word
col sess format a8
col obj format a33 word
col cmd format a8
col w_sec format a6
col blocker format a8
col p123 format a48 word
col sql_id format a20
col module for a15
col "SINCE/WAIT,SEQ" for a14
col "LAST/SINCE/WAIT" for a15
col client for a20
col action for a31
col tx_status for a20 heading "Trans_Ela(UBLK/REC)"

--prompt LAST: time elapsed since session become inactive/active. 
--prompt SINCE: Time elapsed since the end of the last wait. If the session is currently in a wait, then the value is 0.
--prompt WAIT: Amount of time waited. If the session is currently waiting, then the value is the time spent in the current wait. 
--prompt       If the session is currently not in a wait, then the value is the amount of time waited in the last wait.
prompt TX_TM(UBLK/REC): tx ela(undo block/rec). LAST/SINCE/WAIT: LAST:ela from last status; SINCE: ela from last wait,0 means still waiting;  WAIT: ela in last/curr wait.
select
lpad('.',(lev-1),'.')||decode(lev,1,'','^')||
decode(substr(s.program,length(s.program)-5,6),'V1-V3)',substr(s.program,1,&prog_len-decode(lev,1,0,lev)),substr(s.program,1,&prog_len-8-decode(lev,1,0,lev))||'..'||substr(s.program,length(s.program)-5,6))||','
||substr(status,1,1)||',' 
||inst_id||','||sid program,
tx_status,
last_call_et||'s' ||'/'|| case when TIME_SINCE_LAST_WAIT_MICRO>1000000 then round(TIME_SINCE_LAST_WAIT_MICRO/1000000)||'s' when  TIME_SINCE_LAST_WAIT_MICRO>1000 then round(TIME_SINCE_LAST_WAIT_MICRO/1000)||'m' else TIME_SINCE_LAST_WAIT_MICRO||'u' end ||'/'||case when WAIT_TIME_MICRO>1000000 then round(WAIT_TIME_MICRO/1000000)||'s' when  WAIT_TIME_MICRO>1000 then round(WAIT_TIME_MICRO/1000)||'m' else WAIT_TIME_MICRO||'u' end "LAST/SINCE/WAIT",
decode(to_char(s.command),'0',null, 
      '189','MRG','1', 'C-TAB', '2', 'INS', '3', 'SEL', '4', 'C-CLU', '5', 'A-CLU', 
      '6', 'UPD', '7', 'DEL', '8', 'D-CLU', '9', 'C-IND', '10', 'D-IND', 
      '11', 'A-IND', '12', 'D-TAB', '13', 'C-SEQ', '14', 'A-SEQ', '15', 'A-TAB', 
      '16', 'D-SEQ', '17', 'GRANT OBJ', '18', 'REVOKE OBJ', '19', 'C-SYNONYM', '20', 'D-SYN', 
      '21', 'C-VIEW', '22', 'D-VIEW', '23', 'VALIDATE INDEX', '24', 'C-PROC', '25', 'A-PROC', 
      '26', 'LOCK', '27', 'NO-OP', '28', 'RENAME', '29', 'COMMENT', '30', 'AUDIT OBJ', 
      '31', 'NOAUDIT OBJECT', '32', 'C-DBLINK', '33', 'D-DBLINK', '34', 'C-DB', '35', 'A-DB', 
      '36', 'C-RBSEG', '37', 'A-RBSEG', '38', 'D-RBSEG', '39', 'C-TBS', '40', 'A-TBS', 
      '41', 'D-TBS', '42', 'A-SESS', '43', 'A-USER', '44', 'COMMIT', '45', 'RB', 
      '46', 'SAVEPOINT', '47', 'P/L', '48', 'SET TRANS', '49', 'A-SYSTEM', '50', 'EXPLN', 
      '51', 'C-USER', '52', 'C-ROLE', '53', 'D-USER', '54', 'D-ROLE', '55', 'SET ROLE', 
      '56', 'C-SCHEMA', '57', 'C-CTRL FILE', '59', 'C-TRG', '60', 'A-TRG', 
      '61', 'D-TRG', '62', 'ANA-TAB', '63', 'ANA-IND', '64', 'ANA-CLU', '65', 'C-PROF', 
      '66', 'D-PROF', '67', 'A-PROF', '68', 'D-PROC', '77', 'C-TYPE', '78', 'D-TYPE', '79', 'A-ROLE', '80', 'A-TYPE', 
      '81', 'C-TYB', '82', 'A-TYB', '83', 'D-TYB', '84', 'D-LIB', '85', 'TRU-T', 
      '86', 'T-CLU', '91', 'C-FUNC', '92', 'A-FUNC', '93', 'D-FUNC', '94', 'C-PKG', '95', 'A-PKG', 
      '96', 'D-PK', '97', 'C-PKB', '98', 'A-PKB', '99', 'D-PKB', '100', 'LOGON', 
      '101', 'LOGOFF', '102', 'LOGOFF BY CLEANUP', '103', 'SESSION REC', '104', 'SYSTEM AUDIT', '105', 'SYSTEM NOAUDIT', 
      '106', 'AUDIT DEFAULT', '107', 'NOAUDIT DEFAULT', '108', 'SYSTEM GRANT', '109', 'SYSTEM REVOKE', '110', 'C-PSYN', 
      '111', 'D-PSYN', '112', 'C-PDBL', '113', 'D-PDBL', '114', 'GRANT ROLE', '115', 'REVOKE ROLE', 
      '116', 'E-PROC', '117', 'USER COMMENT', '118', 'ENA TRI', '119', 'DIS TRI', 
to_char(s.command))||decode(s.command,0,'','-')||decode(s.SQL_ID,null,s.PREV_SQL_ID,s.sql_id) sql_id,
s.event,
case when s.row_wait_obj#<=0 then
    to_char(s.row_wait_obj#)
  else
    user_name||'.'||object_name||
    ','||
      --s.ROW_WAIT_OBJ#||','||
             decode(type#, 1, 'I', 2, 'T', 3, 'C', 4, 'V', 5, 'SYN', 6, 'SEQ', 7, 'P', 8, 'F', 9, 'PK', 11, 'PKB', 12, 'TRG', 13, 'TYP', 14, 'TYB',
                      19, 'TP', 20, 'IP', 21, 'LOB', 22, 'LIB', 23, 'DIR', 24, 'QUE', 28, 'JS', 29, 'JC', 30, 'JR', 32, 'INDEXTYPE', 33, 'OPE',
                      34, 'TSP', 35, 'ISP', 40, 'LP', 41, 'LSP', 42, 'MV', 43, 'DIM', 44, 'CON', 46, 'RS', 47, 'RP', 48, 'CG', 51, 'SS', 52, 'LOC',
                      55, 'XML', 56, 'JD', 57, 'SEC', 59, 'RUL', 62, 'EVA', '?') end  obj,
--ACTION,
decode(s.p1text,'address','A',
  'file#','f#',
  'requests','Req',
  'count','Cnt',
  'name|mode','n|m',
  'files','fil',
  'buffer#','Buf#', 
  'handle address','hdlA',--library cache lock
  'object address','ObjA',--library cache load lock
  'file number','f#', --direct path read
  'cellhash#','c#', -- cell single block physical read
  'sleeptime/senderid','slptm/send#', --PX Deq: Table Q Sample
  'driver id','drv id', --SQL*Net message from/to client
  'channel context','chn ctx', --reliable message
  'type|mode','t|m',
  s.p1text) -- p1text
||':'|| 
case 
	when s.event = 'enqueue' then chr(bitand(s.P1,-16777216)/16777215)||chr(bitand(s.P1,16711680)/65535)||'|'||mod(s.P1, 65536)
	when s.p1text in ('file#','file number') and s.p1 > (select value from v$parameter where name ='db_files') then mod(s.p1,(select value from v$parameter where name ='db_files'))||'(tmp)'
	else s.p1||''
end 
--decode(s.event, 'enqueue',chr(bitand(s.P1,-16777216)/16777215)||chr(bitand(s.P1,16711680)/65535)||'|'||mod(s.P1, 65536),
--	decode(s.p1text,'file#',mod(s.p1,(select value from v$parameter where name ='db_files')),p1)
--)  --p1
||';'|| 
decode(s.p2text,'block#','bk# ',
  'number','nbr ',
  null,'',
  'blocks','blks',
  'interrupt','itrp',
  'pin address','PinAdr', --library cache pin
  'lock address','LckAdr', --library cache lock
  'first dba','FrtDba', --direct path read
  'diskhash#','d#', -- cell single block physical read
  'value','v#', --library cache: mutex X
 'channel handle','ChnHdl', --reliable message
  'buffer length','BufLen',
  s.p2text) --p2text
||':'|| s.p2 
||';'||
decode(s.p3text,'blocks','bks',
  'timeout','tot',
  'tries','try',
  'le','le ',
  'id','id ',
  'requests','req',
  null,'   ',
  '100*mode+namespace','100*mode+NS', --library cache pin / library cache lock
  '100*mask+namespace','100*mask+NS', --library cache load lock
  'sequence','seq', -- enq: ...
  'broadcast message','BcMsg', --reliable message
  'bytes','Byt',
  'block cnt','BkCnt',
  s.p3text)
||':'|| s.p3 p123
--,substr(s.machine,1,13)||'/'||s.osuser client
--,s.username, ,substr(s.module,1,15) module
--s.action,
--to_char(s.logon_time,'mmdd hh24:mi') logon_tm
from (
	select 
  level lev,connect_by_isleaf isleaf, connect_by_root sid root_sid,decode(level*connect_by_isleaf,1,-1,connect_by_root sid) hierarchy,
  s.*,o.name object_name,u.name user_name,o.type#,        
	(select DECODE(t.STATUS,'ACTIVE','','INACTIVE','IA,',t.STATUS)||
           decode(bitand(flag,power(2,7)),0,'','RB,')||                                -- DML OR ROLLBACK
           --decode(bitand(flag,power(2,28)),0,'NS,','S,')||                              -- SERIALIZABLE OR NON-SERIALIZABLE
           --decode(space,'YES','ST,','')||                                                               -- SPACE TRANS OR NO
           decode(recursive,'YES','RC,','')||                                                -- RECURSIVE TRANS OR NO
           decode(NOUNDO,'YES','NOUNDO,','')||                                                  -- NO UNDO OR NO
           decode(PTX,'YES','PTX,','')                                                                  -- PARALLEL TRANS OR NO
           || case when (SYSDATE-TO_DATE(t.start_time,'MM/DD/YY HH24:MI:SS'))*24*60>1 then round((SYSDATE-TO_DATE(t.start_time,'MM/DD/YY HH24:MI:SS'))*24*60,1)||'m'
	   else round((SYSDATE-TO_DATE(t.start_time,'MM/DD/YY HH24:MI:SS'))*24*3600)||'s' end 
	   ||'('||case when t.USED_UBLK>1000000 then round(used_ublk/1000000,1)||'M' when used_ublk>1000 then round(used_ublk/1000,1)||'K' else used_ublk||'' end
	   ||'/'||case when t.USED_UREC>1000000 then round(used_uREC/1000000,1)||'M' when used_uREC>1000 then round(used_uREC/1000,1)||'K' else used_uREC||'' end ||')'
	from gv$transaction t where t.inst_id=s.inst_id and t.addr=s.taddr) TX_STATUS
  from gv$session s , sys.obj$ o ,sys.user$ u --, gv$transaction t
	where s.row_wait_obj#=o.obj#(+) and o.owner#=u.user#(+)
	--and s.inst_id=t.inst_id(+) and s.taddr=t.addr(+)
	--start with blocking_session_status !='VALID'
	start with blocking_session is null
	connect by  prior s.inst_id=blocking_instance and prior sid=blocking_session
	--keep blocking hierarchy
--	order by decode(level*connect_by_isleaf,1,-1,connect_by_root sid),level
) s
where hierarchy != -1 
or tx_status is not null
or (
lower(s.event) not like '% idle'  and s.event not in ('jobq slave wait')
and (	(s.status ='ACTIVE' and s.wait_class <>'Idle' and s.type='BACKGROUND' )
	OR (s.status ='ACTIVE' and s.type !='BACKGROUND' and s.event not in ('Streams AQ: waiting for messages in the queue'))
	OR s.event not IN (
       'smon timer',    'pmon timer',    'rdbms ipc message',    'Null event',    'parallel query dequeue',    'pipe get',
       'client message',    
	'SQL*Net message from client',   
	'SQL*Net message to client',    
     	--'SQL*Net more data from client',
       'dispatcher timer',    'virtual circuit status',    'lock manager wait for remote message',    'PX Idle Wait',
       'PX Deq: Execution Msg',    'PX Deq: Table Q Normal',  'PX Deq: reap credit',
       'wakeup time manager',    'slave wait',    'i/o slave wait',    'jobq slave wait',
       'null event',    'gcs remote message',    'gcs for action',    'ges remote message',    'queue messages',
      	--'SQL*Net more data to client','control file sequential read',
     	'Streams AQ: waiting for time management or cleanup tasks','Streams AQ: qmn slave idle wait','Streams AQ: qmn coordinator idle wait',
	'Streams AQ: waiting for messages in the queue','DIAG idle wait','PX Deq: Execute Reply','wait for unread message on broadcast channel'  --11g
     ,'VKTM Logical Idle Wait','Space Manager: slave idle wait','GCR sleep'
     ,'PING','EMON slave idle wait','ASM background timer','Streams AQ: emn coordinator idle wait','class slave wait'
	,'lreg timer','heartbeat redo informer'
	,'REPL Capture/Apply: RAC AQ qmn coordinator'
	,'wait for unread message on broadcast channel'
	,'watchdog main loop'
	,'Data Guard: Gap Manager'
	,'Data Guard: Timer'
	,'pman timer'
	))
)
order by hierarchy,lev,5,last_call_et --event,last_call_et,p123,obj,event,sql_id,1
;