wait_event(等待事件查询)
col event for a45
SELECT inst_id,EVENT, SUM(DECODE(WAIT_TIME, 0, 0, 1)) "Prev", SUM(DECODE(WAIT_TIME, 0, 1, 0)) "Curr", COUNT(*) "Tot" , sum(SECONDS_IN_WAIT) SECONDS_IN_WAIT
FROM GV$SESSION_WAIT
WHERE event NOT
IN ('smon timer','pmon timer','rdbms ipc message','SQL*Net message from client','gcs remote message')
AND event NOT LIKE '%idle%'
AND event NOT LIKE '%Idle%'
AND event NOT LIKE '%Streams AQ%'
GROUP BY inst_id,EVENT
ORDER BY 1,5 desc;
session by XXXX(会话查询)
session by XXXX(会话查询)
set line 199
col username format a14
col event format a35
col module format a20
col spid format a8
col machine format a15
col B_SESS for a10
--根据等待事件查会话
SELECT *+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE event='&event_name' AND s.paddr = p.addr order by 6;
--根据用户查会话
SELECT *+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE s.username='&user_name' AND s.paddr = p.addr order by 6
--根据SQL_ID查会话
SELECT *+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE s.sql_id='&sql_id' AND s.paddr = p.addr order by 6
--根据会话ID查会话详情
SELECT s.sid, s.serial#, spid, event, sql_id, PREV_SQL_ID, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, module,blocking_session b_sess,logon_time FROM v$session s, v$process p WHERE sid = '&sid' AND s.paddr = p.addr;
--查询阻塞会话
select count(*),blocking_session from v$session where blocking_session is not null group by blocking_session;
--查询会话的对象信息
col OBJECT_NAME for a30
select owner,object_name,subobject_name,object_type from dba_objects where object_id=&oid;
kill session(杀掉会话)
set line 199
col event format a35
--杀某个SID会话
SELECT *+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE sid='&sid' AND s.paddr = p.addr order by 1;
--根据SQL_ID杀会话
SELECT *+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE sql_id='&sql_id' AND s.paddr = p.addr order by 1;
--根据等待事件杀会话
SELECT *+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE event='&event' AND s.paddr = p.addr order by 1;
--根据用户杀会话
SELECT *+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE username='&username' AND s.paddr = p.addr order by 1;
--kill所有LOCAL=NO进程
ps -ef|grep LOCAL=NO|grep $ORACLE_SID|grep -v grep|awk '{print $2}' |xargs kill -9
active_session(活动的会话)
活动会话的sql语句
prompt Active session with sql text
column USERNAME format a14
set linesize 200
column EVENT format a30
select /*+rule */ distinct ses.SID, ses.sql_hash_value, ses.USERNAME, pro.SPID "OS PID", substr(stx.sql_text,1,200)
from V$SESSION ses
,V$SQL stx
,V$PROCESS pro
where ses.paddr = pro.addr
and ses.status = 'ACTIVE'
and stx.hash_value = ses.sql_hash_value ;
--活动会话的等待事件
prompt Active session with wait
select /*+rule */ sw.event,sw.wait_time,s.username,s.sid,s.serial#,s.SQL_HASH_VALUE
from v$session s, v$session_wait sw
where s.sid=sw.sid
and s.USERNAME is not null
and s.status = 'ACTIVE';
SQL 10046
alter session set tracefile_identifier='enmo10046';
alter session set events '10046 trace name context forever, level 12';
run your sql;
alter session set events '10046 trace name context off';
--如果会话已经运行了，可以用oradebug
conn / as sysdba
oradebug setospid 16835
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug event 10046 trace name context off
--systemstate dump
sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 266;
--wait for 1 min
oradebug dump systemstate 266;
--wait for 1 min
oradebug dump systemstate 266;
oradebug tracefile_name;
--hanganalyze
oradebug setmypid
oradebug unlimit;
oradebug dump hanganalyze 3
--wait for 1 min
oradebug dump hanganalyze 3
--wait for 1 min
oradebug dump hanganalyze 3
oradebug tracefile_name;
