Session Info ...
-- session.sql
DEFINE _br=chr(10)
DEFINE _trcsid_session_serial="NA"
COLUMN trcsid_session_serial NEW_VALUE _trcsid_session_serial
DEFINE _trcsid_session_serial="NA"
SET TERMOUT ON VERIFY OFF HEADING OFF LINESIZE 155 SQLBL ON ECHO OFF TRIMSPOOL ON TRIMOUT ON FEEDBACK ON
--
-- Show information about the SESSION
SELECT distinct
' alter system kill Session ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;' || &_br ||
' INST_ID# ......... ' || RPAD(s.INST_ID , 50, ' ') || 'SID ............ ' || s.sid || &_br ||
' SERIAL# .......... ' || RPAD(s.serial# , 50, ' ') || 'USERNAME ......... ' || s.username || &_br ||
' SPID ............ ' || RPAD(p.spid , 50, ' ') || 'OPID ............. ' || p.pid || &_br ||
' PADDR ........... ' || RPAD(s.paddr , 50, ' ') || 'SADDR ........... ' || s.saddr || &_br ||
' AUDSID .......... ' || RPAD(s.audsid , 50, ' ') || 'OSUSER .......... ' || s.osuser || &_br ||
' PROCESS ......... ' || RPAD(s.process , 50, ' ') || 'PROGRAM ......... ' || s.program || &_br ||
' MACHINE ......... ' || RPAD(s.machine , 50, ' ') || 'MODULE .......... ' || s.module || &_br ||
' HASH_VALUE. ...... ' || RPAD(s.sql_hash_value , 50, ' ') || 'PREV_HASH_VALUE .. ' || s.prev_hash_value || &_br ||
' SQL_ID ........... ' || rpad(s.sql_id , 50, ' ') || 'CHILD_NUMBER# .... ' || s.sql_child_number || &_br ||
' PREV_SQL_ID ...... ' || rpad(s.prev_sql_id , 50, ' ') || 'PREV_CHILD# ...... ' || s.prev_child_number || &_br ||
' WAIT_CLASS ....... ' || rpad(w.WAIT_CLASS , 50, ' ') || 'WAIT_TIME ...... ' || w.WAIT_TIME || &_br ||
' STATE ........' || rpad(s.STATE , 50, ' ') || 'SERVICE NAME ......' || s.SERVICE_NAME || &_br ||
' LOGON_TIME ........' || rpad(TO_CHAR(s.LOGON_TIME,'DD-MM-YY HH24:MI') , 50, ' ')|| 'PROCESS..........'||s.PROCESS|| &_br ||
' P1.................' ||rpad(w.p1 , 50, ' ') || 'p1text..........'||w.p1text||&_br ||
' P2.................' ||rpad(w.p2 , 50, ' ') || 'p2text..........'||w.p2text||&_br ||
' P3.................' ||rpad(w.p3 , 50, ' ') || 'p3text..........'||w.p3text||&_br ||
' EVENT#..............'||rpad(s.EVENT# , 50, ' ') || 'EVENT.......... '||s.EVENT||&_br ||
' Status..............'||rpad(s.status , 50, ' ') ||&_br ||
' Sql_text............'||substr(sql_text,1,80)||&_br||
' p1 raw..............'||rpad(s.P1RAW , 50, ' ')||' ***last call et.......... '||s.last_call_et|| &_br ||
--|| 'Service Name........'||s.SERVICE_NAME
' EXECUTE dbms_monitor.serv_mod_act_trace_enable (service_name=>'||''''||SERVICE_NAME||''''||',binds=>true,waits=>true);' || &_br ||
' EXECUTE DBMS_MONITOR.session_trace_enable(session_id =>'||s.sid||','|| 'serial_num=>'||s.serial#||','||' waits=>TRUE, binds=>TRUE);'|| &_br ||
' alter system set events '||''''|| 'sql_trace [sql:'||s.sql_id||'] level 12'||''''||';'
FROM gv$session s, gv$session_wait w, gv$process p,gv$sql sq
WHERE 1=1
-- and s.sid IN (1)
and s.paddr = p.addr
AND s.sid = w.sid
and s.sql_id is not null
-- and s.sid =714
and s.INST_ID=p.INST_ID
and w.INST_ID=s.INST_ID
and sq.INST_ID=s.INST_ID
and sq.sql_id=s.sql_id
-- and s.USERNAME='CA'
-- and s.SERVICE_NAME not in ('SYS$USERS','SYS$BACKGROUND')
--and s.SERVICE_NAME in ('CAV')
and s.sid >9 ;
==================================================================================
DEFINE _br=chr(10)
DEFINE _trcsid_session_serial="NA"
COLUMN trcsid_session_serial NEW_VALUE _trcsid_session_serial
DEFINE _trcsid_session_serial="NA"
set termout on verify off heading off linesize 155 sqlbl on echo off trimspool on trimout on feedback on
--
-- Show information about the SESSION
SELECT distinct
' alter system kill Session ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;' || &_br ||
' INST_ID# ......... ' || RPAD(s.INST_ID , 50, ' ') || 'SID ............ ' || s.sid || &_br ||
' SERIAL# .......... ' || RPAD(s.serial# , 50, ' ') || 'USERNAME ......... ' || s.username || &_br ||
' SPID ............ ' || RPAD(p.spid , 50, ' ') || 'OPID ............. ' || p.pid || &_br ||
' PADDR ........... ' || RPAD(s.paddr , 50, ' ') || 'SADDR ........... ' || s.saddr || &_br ||
' AUDSID .......... ' || RPAD(s.audsid , 50, ' ') || 'OSUSER .......... ' || s.osuser || &_br ||
' PROCESS ......... ' || RPAD(s.process , 50, ' ') || 'PROGRAM ......... ' || s.program || &_br ||
' MACHINE ......... ' || RPAD(s.machine , 50, ' ') || 'MODULE .......... ' || s.module || &_br ||
' HASH_VALUE. ...... ' || RPAD(s.sql_hash_value , 50, ' ') || 'PREV_HASH_VALUE .. ' || s.prev_hash_value || &_br ||
' SQL_ID ........... ' || rpad(s.sql_id , 50, ' ') || 'CHILD_NUMBER# .... ' || s.sql_child_number || &_br ||
' PREV_SQL_ID ...... ' || rpad(s.prev_sql_id , 50, ' ') || 'PREV_CHILD# ...... ' || s.prev_child_number || &_br ||
' SQL EXEC ID........'|| rpad(s.SQL_EXEC_ID , 40, ' ') || 'Tracefile ........' || p.TRACEFILE || &_br || ----- comment 10g
' WAIT_CLASS ....... ' || rpad(w.WAIT_CLASS , 50, ' ') || 'WAIT_TIME ...... ' || w.WAIT_TIME || &_br ||
' STATE ........' || rpad(s.STATE , 50, ' ') || 'SERVICE NAME ......' || s.SERVICE_NAME || &_br ||
' LOGON_TIME ........' || rpad(TO_CHAR(s.LOGON_TIME,'DD-MM-YY HH24:MI') , 50, ' ')||'PROCESS..........'||s.PROCESS|| &_br ||
' P1.................' ||rpad(w.p1 , 50, ' ') || 'p1text..........'||w.p1text||&_br ||
' P2.................' ||rpad(w.p2 , 50, ' ') || 'p2text..........'||w.p2text||&_br ||
' P3.................' ||rpad(w.p3 , 50, ' ') || 'p3text..........'||w.p3text||&_br ||
' EVENT#..............'||rpad(s.EVENT# , 50, ' ') || 'EVENT.......... '||s.EVENT ||&_br ||
' Status..............'||rpad(s.status , 50, ' ') ||&_br ||
' Sql_text............'||substr(sql_text,1,80)||&_br||
' p1 raw..............'||rpad(s.P1RAW , 50, ' ')||' ***last call et.......... '||to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '99999.99')|| &_br ||
--|| 'Service Name........'||s.SERVICE_NAME
-- 'SQL EXEC ID........'||rpad(s.SQL_EXEC_ID , 50, ' ') ||'CHILD_NUMBER# .... ' || s.sql_child_number || &_br ||
' EXECUTE dbms_monitor.serv_mod_act_trace_enable (service_name=>'||''''||SERVICE_NAME||''''||',binds=>true,waits=>true);' || &_br ||
' EXECUTE DBMS_MONITOR.session_trace_enable(session_id =>'||s.sid||','|| 'serial_num=>'||s.serial#||','||' waits=>TRUE, binds=>TRUE);'|| &_br ||
' alter system set events '||''''|| 'sql_trace [sql:'||s.sql_id||'] level 12'||''''||';'|| &_br ||
' select * from table(dbms_xplan.display_cursor('||''''||s.sql_id ||''''||',null,''ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS''));' || &_br ||
' select * from table(dbms_xplan.display_awr('||''''||s.sql_id ||''''||',null,null,''ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS''));' || &_br ||
' alter system set events ''trace[RDBMS.SQL_Optimizer.*][sql:'||s.sql_id ||']'';' || &_br ||
' alter system set events ''trace[RDBMS.SQL_Optimizer.*][sql:'||s.sql_id ||'] off'';'
FROM gv$session s, gv$session_wait w, gv$process p,gv$sql sq
where 1=1
and s.paddr = p.addr
and s.inst_id = p.inst_id
and w.inst_id = s.inst_id
and sq.inst_id = s.inst_id
and sq.sql_id = s.sql_id
and s.sid = w.sid
-- and s.machine='S0017'
-- and s.sql_id is not null
-- nad s.sid!=sys_context('userenv','sid')
-- and sq.sql_id in ('1jwzgruw1z6s3' )
-- and s.EVENT not like 'SQL%'
-- and s.username='SYS'
-- and s.service_name in ('USER')
-- and s.sid in (23826)
-- and ROUND((SYSDATE - s.LOGON_TIME)*(24*60),1) > 60 ---- 1hr
and s.type != 'BACKGROUND'
and s.username is not null
-- and s.status ='INACTIVE'
and s.USERNAME Not in ( 'SYS ' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
/
==========
define sid=1473
set lines 200 pagesize 300 echo off
col sess format a10
col stime format a08
col sta format a04 head 'Sta'
col event format a24 trunc
col seq# format 99999
col wtime format 9999999
col twaited format 99999999
col p1p2p3 format a18 head ' P1:P2:P3'
col wclass format a11 trunc
col sqlid_cn format a16 head 'SqlId:ChildNo'
col blk format a01 head 'X'
col oc format 999
col cn format 99
col flags format a12 head 'CPH S PRXJBZ'
col mod format a20 head 'Module' trunc
col sqlstart format a05 head 'SQL|Start'
col sqlxid format 99999999 head 'SqlExecId'
col sidser format a15 head 'Sid,Serial<Blk'
col dur format 999999 head 'Dur|ms'
col cpu format 9999 head 'CPU|ms'
col dbtime format 999999 head 'DB Time|ms'
break on sidser skip 1 on mod on sqlid_cn on sqlxid on sqlstart
select
''''||SESSION_ID ||','|| session_serial#||',@'||inst_id ||''''||decode(substr(blocking_session_status,1,1),'V','<',' ')||lpad(blocking_session,4,' ') sidser
,to_char(sample_time, 'HH24:MI:SS') stime
,module mod
,sql_id||decode(sql_child_number,-1,' ',':'||lpad(sql_child_number,2,' ')) sqlid_cn
,sql_exec_id sqlxid
,to_char(sql_exec_start,'MI:SS') sqlstart
,in_connection_mgmt||in_parse||in_hard_parse||' '||in_sql_execution||' '||in_plsql_execution||in_plsql_rpc||in_java_execution||in_bind||in_cursor_close flags
,decode(session_state,'WAITING','Wait','ON CPU','cpu',' ? ') sta
,seq# seq#
,event event
,lpad(least(p1,9999999),7,' ')||':'||lpad(least(p2,999999),6,' ')||':'||lpad(least(p3,9),3,' ') p1p2p3
,wait_class wclass
,tm_delta_time/1000 dur
,tm_delta_cpu_time/1000 cpu
,tm_delta_db_time/1000 dbtime
from gv$active_session_history
where 1=1
and session_id = '&sid'
--and sample_time between sysdate-70/1440 and sysdate-30/1440
and sample_time > sysdate -1
and sample_time > sysdate - interval '30' minute
order by sample_time
;
=========================
set linesize 2000 pagesize 500 col LONGOPS_MESSAGE for a20 col PREV_SQL_TEXT for a50 wrap col SQL_TEXT for a50 wrap col P1TEXT for a10 col P2TEXT for a10 col P3TEXT for a10 col USERNAME for a10 col PROGRAM for a10 col EVENT for a12 col HOST_NAME for a12 col LATCHNAME for a12 col MACHINE for a12 col kill for a15 select/*+ rule*/ distinct --because of multiple records in v$sql may exist to_char(gv$session.LOGON_TIME,'YYYY.MM.DD HH24:MI:SS') as LOGON_TIME ,''''||gv$session.sid ||','|| gv$session.serial#||',@'||gv$session.inst_id ||'''' kill , gv$session.STATUS , gv$session.USERNAME , gv$session.PROGRAM --, gv$session.INST_ID --, gv$session.SID --, gv$session.SERIAL# , gv$transaction.START_TIME as TX_STIME , gv$session.LAST_CALL_ET as LAST_CALL_ET , gv$session_wait.EVENT , v$latchname.NAME as LATCHNAME , gv$session_wait.SECONDS_IN_WAIT , dba_objects.owner||'.'||dba_objects.object_name req_object , decode(nvl(gv$session.ROW_WAIT_OBJ#,-1),-1,'NONE',DBMS_ROWID.ROWID_CREATE( 1, gv$session.ROW_WAIT_OBJ#, gv$session.ROW_WAIT_FILE#, gv$session.ROW_WAIT_BLOCK#, gv$session.ROW_WAIT_ROW# )) req_rowid , lockhold.inst_id as BLOCKING_INSTANCE , lockhold.sid as BLOCKING_SESSION , gv$session.COMMAND , gv$session_longops.TIME_REMAINING as LONGOPS_CALL_RT , gv$session_longops.MESSAGE as LONGOPS_MESSAGE , gv$session_wait.P1 , gv$session_wait.P1TEXT , gv$session_wait.P2 P2 , gv$session_wait.P2TEXT , gv$session_wait.P3 , gv$session_wait.P3TEXT , gv$sql.ADDRESS as SQL_ADDRESS , gv$sql.HASH_VALUE as HASH_VALUE , gv$sql.PLAN_HASH_VALUE as PLAN_HASH_VALUE , gv$sql.OPTIMIZER_MODE as SQL_OPTIMIZER_MODE , gv$sql.sql_text as SQL_TEXT , sql1.ADDRESS as PREV_SQL_ADDRESS , sql1.HASH_VALUE as PREV_HASH_VALUE , sql1.PLAN_HASH_VALUE as PREV_PLAN_HASH_VALUE , sql1.OPTIMIZER_MODE as PREV_SQL_OPTIMIZER_MODE , sql1.sql_text as PREV_sql_text --Network connection properties , gv$session.SERVER , gv$session.FAILOVER_TYPE , gv$session.FAILOVER_METHOD , gv$session.FAILED_OVER , gv$session.FIXED_TABLE_SEQUENCE --OS properties , gv$session.MACHINE , gv$session.MODULE , gv$session.OSUSER , gv$session.OWNERID , gv$session.TERMINAL -- , 'Alter system kill session '''||gv$session.SID||','||gv$session.SERIAL#||''';' as KILL_SESSION ,'alter system kill session ' || '''' || gv$session.sid || ',' || gv$session.serial# ||',@'|| gv$session.inst_id || '''' || ' immediate;' as KILL_SESSION , gv$instance.HOST_NAME , 'kill '||gv$process.SPID as KILL_SPID from gv$session , gv$instance , gv$process , gv$session_wait , gv$sql , gv$sql sql1 , gv$transaction , v$latchname , dba_objects , gv$session_longops , gv$lock lockwait , gv$lock lockhold where gv$session.INST_ID = gv$instance.INST_ID and gv$session.PADDR=gv$process.ADDR(+) and gv$session.INST_ID = gv$process.INST_ID(+) and gv$session.sql_address=gv$sql.address(+) and gv$session.INST_ID = gv$sql.INST_ID(+) and gv$session.sql_hash_value=gv$sql.hash_value(+) and gv$session.PREV_SQL_ADDR = sql1.address(+) and gv$session.PREV_HASH_VALUE = sql1.hash_value(+) and gv$session.SID=gv$session_wait.SID(+) and gv$session.INST_ID = gv$session_wait.INST_ID(+) and gv$session.SADDR=gv$transaction.SES_ADDR(+) and gv$session.INST_ID = gv$transaction.INST_ID(+) and (gv$session.SERIAL# <> 1 or upper(gv$session.PROGRAM) like '%LGWR%' or upper(gv$session.PROGRAM) like '%DBW%' )--System session excluded,exc. DBWR and upper(gv$session.PROGRAM) not like '%QMNC%'--Queue Monitor Coordinator excluded and upper(gv$session.PROGRAM) not like '%Q0%'--Queue monitor processes excluded and gv$session_wait.P2=v$latchname.LATCH#(+) and gv$session.ROW_WAIT_OBJ# = dba_objects.object_id(+) and gv$session.SID = gv$session_longops.sid(+) and gv$session.INST_ID = gv$session_longops.INST_ID(+) and gv$session.SERIAL# = gv$session_longops.SERIAL#(+) and gv$session.sql_address = gv$session_longops.SQL_ADDRESS(+) and gv$session.sql_hash_value = gv$session_longops.SQL_HASH_VALUE(+) and nvl(gv$session_longops.SOFAR,0) <> nvl(gv$session_longops.TOTALWORK,1) and gv$session.LOCKWAIT = lockwait.KADDR(+) and lockwait.id1 = lockhold.id1(+) and lockwait.id2 = lockhold.id2(+) and nvl(lockwait.REQUEST,1) > 0 and nvl(lockwait.LMODE,0) = 0 and nvl(lockhold.REQUEST,0) = 0 and nvl(lockhold.LMODE,1) > 0 and nvl(lockwait.SID,0) <> nvl(lockhold.SID,1) --and gv$session.schemaname not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' ,'WK_TEST', 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','DP_MON','FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL','SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','SI_INFORMTN_SCHEMA','LBACSYS','DBVISIT7') order by tx_stime, status, LOGON_TIME, username, machine asc;
=============
Session Info ....SET PAGES 0 - NEWP 0 - LINES 150 - TERM Off - HEAD Off Col X form a150 --spool all_session.log TTitle T Skip 2 select ' ' x , lpad( 'SADDR', 24, ' ') ||' : '||s.saddr x , lpad( '_', 43, '_') x , lpad( 'SID', 24, ' ') ||' : '||s.sid x , lpad( 'Sid_serial', 24, ' ') ||' : '||''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' x , lpad( 'SERIAL#', 24, ' ') ||' : '||s.serial# x , lpad( 'AUDSID', 24, ' ') ||' : '||s.audsid x , lpad( 'PADDR', 24, ' ') ||' : '||s.paddr x , lpad( 'USER#', 24, ' ') ||' : '||s.user# x , lpad( 'USERNAME', 24, ' ') ||' : '||s.username x , lpad( 'COMMAND', 24, ' ') ||' : '||s.command x , lpad( 'TADDR', 24, ' ') ||' : '||s.taddr x , lpad( 'LOCKWAIT', 24, ' ') ||' : '||s.lockwait x , lpad( 'STATUS', 24, ' ') ||' : '||s.status x , lpad( 'SERVER', 24, ' ') ||' : '||s.server x , lpad( 'SCHEMA#', 24, ' ') ||' : '||s.schema# x , lpad( 'SCHEMANAME', 24, ' ') ||' : '||s.schemaname x , lpad( 'OSUSER', 24, ' ') ||' : '||s.osuser x , lpad( 'Client Process', 24, ' ') ||' : '||s.process x , lpad( 'Server Process', 24, ' ') ||' : '||p.spid x , lpad( 'MACHINE', 24, ' ') ||' : '||s.machine x , lpad( 'TERMINAL', 24, ' ') ||' : '||s.terminal x , lpad( 'PROGRAM', 24, ' ') ||' : '||s.program x , lpad( 'TYPE', 24, ' ') ||' : '||s.type x , lpad( 'SQL_ID', 24, ' ') ||' : '||s.sql_id x , lpad( 'PREV_SQL_ID', 24, ' ') ||' : '||s.prev_sql_id x , lpad( 'SQL_ADDRESS', 24, ' ') ||' : '||s.sql_address x , lpad( 'SQL_HASH_VALUE', 24, ' ') ||' : '||s.sql_hash_value x , lpad( 'PREV_SQL_ADDR', 24, ' ') ||' : '||s.prev_sql_addr x , lpad( 'PREV_HASH_VALUE', 24, ' ') ||' : '||s.prev_hash_value x , lpad( 'MODULE', 24, ' ') ||' : '||s.module x , lpad( 'MODULE_HASH', 24, ' ') ||' : '||s.module_hash x , lpad( 'ACTION', 24, ' ') ||' : '||s.action x , lpad( 'ACTION_HASH', 24, ' ') ||' : '||s.action_hash x , lpad( 'CLIENT_INFO', 24, ' ') ||' : '||s.client_info x , lpad( 'FIXED_TABLE_SEQUENCE', 24, ' ') ||' : '||s.fixed_table_sequence x , lpad( 'ROW_WAIT_OBJ#', 24, ' ') ||' : '||s.row_wait_obj# x , lpad( 'ROW_WAIT_FILE#', 24, ' ') ||' : '||s.row_wait_file# x , lpad( 'ROW_WAIT_BLOCK#', 24, ' ') ||' : '||s.row_wait_block# x , lpad( 'ROW_WAIT_ROW#', 24, ' ') ||' : '||s.row_wait_row# x , lpad( 'LOGON_TIME', 24, ' ') ||' : '||to_char( s.logon_time, 'dd mon yyyy hh24:mi:ss') x , lpad( 'LAST_CALL_ET', 24, ' ') ||' : '||s.last_call_et x , lpad( 'SQL FIRST_LOAD_TIME', 24, ' ') ||' : '||sql.first_load_time x , lpad( 'FINAL_BLOCKING* ..', 24, ' ') ||' : '||s.final_blocking_session_status x , lpad( 'EVENT ..', 24, ' ') ||' : '||s.event x , lpad( 'SQL', 24, ' ') ||' : '||sql.sql_text x from gv$sqlarea sql,gv$process p,gv$session s where 1=1 and p.addr = s.paddr and p.inst_id = s.inst_id and nvl( s.sql_address, 'FFFFFFFF') = sql.Address and s.inst_id = sql.inst_id And Nvl( s.SERIAL#, 0) > 1 --and schemaname not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' ,'WK_TEST', 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','DP_MON','FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', -- 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','SI_INFORMTN_SCHEMA','LBACSYS','DBVISIT7') /***********************************--- with Con_id DEFINE _br=chr(10) DEFINE _trcsid_session_serial="NA" COLUMN trcsid_session_serial NEW_VALUE _trcsid_session_serial DEFINE _trcsid_session_serial="NA" SET TERMOUT ON VERIFY OFF HEADING OFF LINESIZE 155 SQLBL ON ECHO OFF TRIMSPOOL ON TRIMOUT ON FEEDBACK ON -- -- Show information about the SESSION SELECT distinct ' alter system kill Session ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;' || &_br || ' CON_ID..............'||rpad(s.CON_ID , 50, ' ') ||&_br || ' INST_ID# ......... ' || RPAD(s.INST_ID , 50, ' ') || 'SID ............ ' || s.sid || &_br || ' SERIAL# .......... ' || RPAD(s.serial# , 50, ' ') || 'USERNAME ......... ' || s.username || &_br || ' SPID ............ ' || RPAD(p.spid , 50, ' ') || 'OPID ............. ' || p.pid || &_br || ' PADDR ........... ' || RPAD(s.paddr , 50, ' ') || 'SADDR ........... ' || s.saddr || &_br || ' AUDSID .......... ' || RPAD(s.audsid , 50, ' ') || 'OSUSER .......... ' || s.osuser || &_br || ' PROCESS ......... ' || RPAD(s.process , 50, ' ') || 'PROGRAM ......... ' || s.program || &_br || ' MACHINE ......... ' || RPAD(s.machine , 50, ' ') || 'MODULE .......... ' || s.module || &_br || ' HASH_VALUE. ...... ' || RPAD(s.sql_hash_value , 50, ' ') || 'PREV_HASH_VALUE .. ' || s.prev_hash_value || &_br || ' SQL_ID ........... ' || rpad(s.sql_id , 50, ' ') || 'CHILD_NUMBER# .... ' || s.sql_child_number || &_br || ' PREV_SQL_ID ...... ' || rpad(s.prev_sql_id , 50, ' ') || 'PREV_CHILD# ...... ' || s.prev_child_number || &_br || ' WAIT_CLASS ....... ' || rpad(w.WAIT_CLASS , 50, ' ') || 'WAIT_TIME ...... ' || w.WAIT_TIME || &_br || ' STATE ........' || rpad(s.STATE , 50, ' ') || 'SERVICE NAME ......' || s.SERVICE_NAME || &_br || ' LOGON_TIME ........' || rpad(TO_CHAR(s.LOGON_TIME,'DD-MM-YY HH24:MI') , 50, ' ')|| 'PROCESS..........'||s.PROCESS|| &_br || ' P1.................' ||rpad(w.p1 , 50, ' ') || 'p1text..........'||w.p1text||&_br || ' P2.................' ||rpad(w.p2 , 50, ' ') || 'p2text..........'||w.p2text||&_br || ' P3.................' ||rpad(w.p3 , 50, ' ') || 'p3text..........'||w.p3text||&_br || ' EVENT#..............'||rpad(s.EVENT# , 50, ' ') || 'EVENT.......... '||s.EVENT||&_br || ' Status..............'||rpad(s.status , 50, ' ') ||&_br || ' Sql_text............'||substr(sql_text,1,80)||&_br|| ' p1 raw..............'||rpad(s.P1RAW , 50, ' ')||' ***last call et.......... '||s.last_call_et|| &_br || --|| 'Service Name........'||s.SERVICE_NAME ' EXECUTE dbms_monitor.serv_mod_act_trace_enable (service_name=>'||''''||SERVICE_NAME||''''||',binds=>true,waits=>true);' || &_br || ' EXECUTE DBMS_MONITOR.session_trace_enable(session_id =>'||s.sid||','|| 'serial_num=>'||s.serial#||','||' waits=>TRUE, binds=>TRUE);'|| &_br || ' alter system set events '||''''|| 'sql_trace [sql:'||s.sql_id||'] level 12'||''''||';' FROM gv$session s, gv$session_wait w, gv$process p,gv$sql sq WHERE 1=1 -- and s.sid IN (1) and s.paddr = p.addr AND s.sid = w.sid and s.sql_id is not null -- and s.sid =714 and s.INST_ID=p.INST_ID and w.INST_ID=s.INST_ID and sq.INST_ID=s.INST_ID and sq.sql_id=s.sql_id -- and s.USERNAME='CA' -- and s.SERVICE_NAME not in ('SYS$USERS','SYS$BACKGROUND') --and s.SERVICE_NAME in ('CAV') and s.CON_ID=3 and s.sid >9 ;--- with Con_idSET PAGES 0 - NEWP 0 - LINES 150 - TERM Off - HEAD Off Col X form a150 --spool all_session.log TTitle T Skip 2 select ' ' x , lpad( 'SADDR', 24, ' ') ||' : '||s.saddr x , lpad( '_', 43, '_') x , lpad( 'SID', 24, ' ') ||' : '||s.sid x , lpad( 'Sid_serial', 24, ' ') ||' : '||''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' x , lpad( 'Con _id', 24, ' ') ||' : '||s.Con_id , lpad( 'SERIAL#', 24, ' ') ||' : '||s.serial# x , lpad( 'AUDSID', 24, ' ') ||' : '||s.audsid x , lpad( 'PADDR', 24, ' ') ||' : '||s.paddr x , lpad( 'USER#', 24, ' ') ||' : '||s.user# x , lpad( 'USERNAME', 24, ' ') ||' : '||s.username x , lpad( 'COMMAND', 24, ' ') ||' : '||s.command x , lpad( 'TADDR', 24, ' ') ||' : '||s.taddr x , lpad( 'LOCKWAIT', 24, ' ') ||' : '||s.lockwait x , lpad( 'STATUS', 24, ' ') ||' : '||s.status x , lpad( 'SERVER', 24, ' ') ||' : '||s.server x , lpad( 'SCHEMA#', 24, ' ') ||' : '||s.schema# x , lpad( 'SCHEMANAME', 24, ' ') ||' : '||s.schemaname x , lpad( 'OSUSER', 24, ' ') ||' : '||s.osuser x , lpad( 'Client Process', 24, ' ') ||' : '||s.process x , lpad( 'Server Process', 24, ' ') ||' : '||p.spid x , lpad( 'MACHINE', 24, ' ') ||' : '||s.machine x , lpad( 'TERMINAL', 24, ' ') ||' : '||s.terminal x , lpad( 'PROGRAM', 24, ' ') ||' : '||s.program x , lpad( 'TYPE', 24, ' ') ||' : '||s.type x , lpad( 'SQL_ID', 24, ' ') ||' : '||s.sql_id x , lpad( 'PREV_SQL_ID', 24, ' ') ||' : '||s.prev_sql_id x , lpad( 'SQL_ADDRESS', 24, ' ') ||' : '||s.sql_address x , lpad( 'SQL_HASH_VALUE', 24, ' ') ||' : '||s.sql_hash_value x , lpad( 'PREV_SQL_ADDR', 24, ' ') ||' : '||s.prev_sql_addr x , lpad( 'PREV_HASH_VALUE', 24, ' ') ||' : '||s.prev_hash_value x , lpad( 'MODULE', 24, ' ') ||' : '||s.module x , lpad( 'MODULE_HASH', 24, ' ') ||' : '||s.module_hash x , lpad( 'ACTION', 24, ' ') ||' : '||s.action x , lpad( 'ACTION_HASH', 24, ' ') ||' : '||s.action_hash x , lpad( 'CLIENT_INFO', 24, ' ') ||' : '||s.client_info x , lpad( 'FIXED_TABLE_SEQUENCE', 24, ' ') ||' : '||s.fixed_table_sequence x , lpad( 'ROW_WAIT_OBJ#', 24, ' ') ||' : '||s.row_wait_obj# x , lpad( 'ROW_WAIT_FILE#', 24, ' ') ||' : '||s.row_wait_file# x , lpad( 'ROW_WAIT_BLOCK#', 24, ' ') ||' : '||s.row_wait_block# x , lpad( 'ROW_WAIT_ROW#', 24, ' ') ||' : '||s.row_wait_row# x , lpad( 'LOGON_TIME', 24, ' ') ||' : '||to_char( s.logon_time, 'dd mon yyyy hh24:mi:ss') x , lpad( 'LAST_CALL_ET', 24, ' ') ||' : '||s.last_call_et x , lpad( 'SQL FIRST_LOAD_TIME', 24, ' ') ||' : '||sql.first_load_time x , lpad( 'FINAL_BLOCKING* ..', 24, ' ') ||' : '||s.final_blocking_session_status x , lpad( 'EVENT ..', 24, ' ') ||' : '||s.event x , lpad( 'SQL', 24, ' ') ||' : '||sql.sql_text x from gv$sqlarea sql,gv$process p,gv$session s where 1=1 and p.addr = s.paddr and p.inst_id = s.inst_id and nvl( s.sql_address, 'FFFFFFFF') = sql.Address and s.inst_id = sql.inst_id And Nvl( s.SERIAL#, 0) > 1 --and schemaname not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' ,'WK_TEST', 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','DP_MON','FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', -- 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','SI_INFORMTN_SCHEMA','LBACSYS','DBVISIT7') /=====SELECT COUNT (*), CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END AS state, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END AS sw_event FROM gv$session_wait GROUP BY CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END ORDER BY 1 DESC, 2 DESC SELECT COUNT (*), CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END AS state, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END AS sw_event FROM gv$session WHERE TYPE = 'USER' AND status = 'ACTIVE' GROUP BY CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END ORDER BY 1 DESC, 2 DESC define sid='2594' set pagesize 300 linesize 300 col sw_event head EVENT for a40 truncate col sw_p1transl head P1TRANSL for a42 col sw_sid head SID for 999999 col sw_p1 head P1 for a18 justify left word_wrap col sw_p2 head P2 for a18 justify left word_wrap col sw_p3 head P3 for a18 justify left word_wrap select sid sw_sid, CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END AS state, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END AS sw_event, seq#, seconds_in_wait sec_in_wait, CASE state WHEN 'WAITING' THEN NVL2(p1text,p1text||'= ',null)||CASE WHEN P1 < 536870912 THEN to_char(P1) ELSE '0x'||rawtohex(P1RAW) END ELSE null END SW_P1, CASE state WHEN 'WAITING' THEN NVL2(p2text,p2text||'= ',null)||CASE WHEN P2 < 536870912 THEN to_char(P2) ELSE '0x'||rawtohex(P2RAW) END ELSE null END SW_P2, CASE state WHEN 'WAITING' THEN NVL2(p3text,p3text||'= ',null)||CASE WHEN P3 < 536870912 THEN to_char(P3) ELSE '0x'||rawtohex(P3RAW) END ELSE null END SW_P3, CASE state WHEN 'WAITING' THEN CASE WHEN event like 'cursor:%' THEN '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX')) WHEN event like 'enq%' AND state = 'WAITING' THEN '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))||': '|| chr(bitand(p1, -16777216)/16777215)|| chr(bitand(p1,16711680)/65535)|| ' mode '||bitand(p1, power(2,14)-1) WHEN event like 'latch%' AND state = 'WAITING' THEN '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))||': '||( select name||'[par' from v$latch_parent where addr = hextoraw(trim(to_char(p1,rpad('0',length(rawtohex(addr)),'X')))) union all select name||'[c'||child#||']' from v$latch_children where addr = hextoraw(trim(to_char(p1,rpad('0',length(rawtohex(addr)),'X')))) ) WHEN event like 'library cache pin' THEN '0x'||RAWTOHEX(p1raw) ELSE NULL END ELSE NULL END AS sw_p1transl FROM gv$session_wait WHERE 1=1 and sid IN ('&sid') ORDER BY state, sw_event, p1, p2, p3====define sid='' set time on timing on set lines 500 pages 60 column sid format 9999 column state format a10 trunc column username format a20 wrap column osuser format a8 column seq for 99999 column et for 99999999999 heading 'Minu|Elaps' column wait_time heading 'Sec.|Wtd' format 999 column wis heading 'Sec.|Wtng' format 9999999999999 col event for a25 trunc col mod for a36 wrap col P1_P2_P3_TEXT for a40 wrap col process for a8 col pid for a6 heading DBP col sql_hash_value for 9999999999 col serial# heading 'Sr.|No.' for 99999 col inst_id for 99 heading 'IN|ST' col kill1 for a17 col kill for a17 --set feed on pause on term on select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,s.con_id -- , to_char(p.spid,'9999999′) SPID ,'kill -9 '||to_char(p.spid) kill1 , s.sql_id ,decode(w.event,'latch free', w.event||'-p2='||to_char(w.p2), substr(w.event,1,28)) event , s.username username ,substr(s.osuser, 1,10) osuser ,w.state,w.wait_time, w.seconds_in_wait wis, w.seq# seq, s.sql_hash_value, s.last_call_et/60 et,s.module ||'~'||s.action mod,s.status, substr(w.p1text||' '||to_char(s.P1)||'-'||w.p2text||' '||to_char(s.P2)||'-'|| w.p3text||' '||to_char(s.P3), 1,40) P1_P2_P3_TEXT from gv$session_wait w, gv$session s, gv$process p where s.sid=w.sid and p.addr = s.paddr --and s.status in ( 'ACTIVE', 'KILLED') and s.username is not null and w.inst_id = s.inst_id and s.inst_id = p.inst_id and s.username not in ('SYS','SYSTEM') --and s.sid = &sid --order by 14 /set termout off verify off trimspool on linesize 300 longchunksize 200000 long 200000 pages 0 column txt format a200 COL dummy_value NOPRINT for a300 -- SELECT 'dummy_value' dummy_value, 'USERNAME : ' || s.username || CHR(10) || 'SCHEMA : ' || s.schemaname || CHR(10) || 'OSUSER : ' || s.osuser || CHR(10) || 'MODULE : ' || s.program || CHR(10) || 'ACTION : ' || s.schemaname || CHR(10) || 'CLIENT INFO : ' || s.osuser || CHR(10) || 'PROGRAM : ' || s.program || CHR(10) || 'kill -9 : ' || p.spid || CHR(10) || --'SPID : ' || p.spid || CHR(10) || --'KILL STRING : ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' || CHR(10) || 'KILL STRING : ' || 'alter system kill session ' || '''' || s.sid || ',' || s.serial# ||',@'|| s.inst_id || '''' || ' immediate;'|| CHR(10) || 'Con Id : ' || s.con_id || CHR(10) || 'MACHINE : ' || s.machine || CHR(10) || 'TYPE : ' || s.type || CHR(10) || 'TERMINAL : ' || s.terminal || CHR(10) || 'CPU : ' || q.cpu_time/1000000 || CHR(10) || 'ELAPSED_TIME: ' || q.elapsed_time/1000000 || CHR(10) || 'BUFFER_GETS : ' || q.buffer_gets || CHR(10) || 'SQL_ID : ' || q.sql_id || CHR(10) || 'CHILD_NUM : ' || q.child_number || CHR(10) || 'START_TIME : ' || TO_CHAR(s.sql_exec_start,'dd-mon-yy hh24:mi') || CHR(10) || 'STATUS : ' || s.status || CHR(10) || 'P1 : ' || s.p1text || ' ' || s.p1 || chr(10)|| 'P2 : ' || s.p2text || ' ' || s.p2 || chr(10)|| 'P3 : ' || s.p3text || ' ' || s.p3 || chr(10)|| 'FINAL_BLOCKING_SESSION : ' ||s.FINAL_BLOCKING_SESSION || chr(10)|| --w'dbms_xplan :' ||' select * from table(dbms_xplan.display_cursor('||''''||s.sql_id ||''''||',null,''ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS''));' ||CHR(10) || 'SQL_TEXT : ' || q.sql_fulltext||CHR(10)|| 'dbms_xplan :' ||' SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id =>'''||s.sql_id||''''||','|| 'cursor_child_no =>' ||child_number||','|| 'FORMAT =>'|| '''TYPICAL -ROWS -BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'' ));' ||CHR(10) FROM gv$session s,gv$process p,gv$sql q where 1=1 and s.username IS NOT NULL and s.paddr = p.addr and s.inst_id = p.inst_id and s.inst_id = q.inst_id and s.sql_id = q.sql_id AND NVL(q.sql_text,'x') NOT LIKE '%dummy_value%' --AND s.status != DECODE('&&active','n','xyz','N','xyz','INACTIVE') and s.username IS NOT NULL ORDER BY q.cpu_time;
7 comments:
http://anuj-singh.blogspot.co.uk/2012/06/oracle-10g-active-session-set-pagesize.html
http://anuj-singh.blogspot.co.uk/2011/07/oracle-top-sql-spent-more-on-cpuwaitio.html
http://anuj-singh.blogspot.co.uk/2011/11/oracle-top-sql.html
http://anuj-singh.blogspot.co.uk/2010/05/oracle-top-sql-given-time.html
http://anuj-singh.blogspot.co.uk/2013/11/oracle-top-10-sql-waits.html
http://anuj-singh.blogspot.co.uk/2011_11_14_archive.html
http://anuj-singh.blogspot.com/2014/10/show-sql-for-users-in-rac-database-and.html
Post a Comment