SELECT round(bitand(s.ownerid, 65535)) parent_session_sid,
round(bitand(s.ownerid,16711680)/65536) parent_session_instid, s.SADDR, s.SID,
s.SERIAL#, s.AUDSID, s.PADDR, s.USER#, s.USERNAME, s.COMMAND, s.OWNERID,
s.TADDR, s.LOCKWAIT, s.STATUS, s.SERVER, s.SCHEMA#, s.SCHEMANAME, s.OSUSER,
s.PROCESS, s.MACHINE, s.TERMINAL, UPPER(s.PROGRAM) PROGRAM, s.TYPE,
s.SQL_ADDRESS, s.SQL_HASH_VALUE, s.SQL_ID, s.SQL_CHILD_NUMBER, s.PREV_SQL_ADDR,
s.PREV_HASH_VALUE, s.PREV_SQL_ID, s.PREV_CHILD_NUMBER, s.PLSQL_ENTRY_OBJECT_ID,
s.PLSQL_ENTRY_SUBPROGRAM_ID, s.PLSQL_OBJECT_ID, s.PLSQL_SUBPROGRAM_ID, s.MODULE,
s.MODULE_HASH, s.ACTION, s.ACTION_HASH, s.CLIENT_INFO, s.FIXED_TABLE_SEQUENCE,
s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#,
s.LOGON_TIME, s.LAST_CALL_ET, s.PDML_ENABLED, s.FAILOVER_TYPE,
s.FAILOVER_METHOD, s.FAILED_OVER, s.RESOURCE_CONSUMER_GROUP, s.PDML_STATUS,
s.PDDL_STATUS, s.PQ_STATUS, s.CURRENT_QUEUE_DURATION, s.CLIENT_IDENTIFIER,
s.BLOCKING_SESSION_STATUS, s.BLOCKING_INSTANCE, s.BLOCKING_SESSION, s.SEQ#,
s.EVENT#, s.EVENT, s.P1TEXT, s.P1, s.P1RAW, s.P2TEXT, s.P2, s.P2RAW, s.P3TEXT,
s.P3, s.P3RAW, s.WAIT_CLASS_ID, s.WAIT_CLASS#, s.WAIT_CLASS, s.WAIT_TIME,
s.SECONDS_IN_WAIT, s.STATE, s.SERVICE_NAME, s.SQL_TRACE, s.SQL_TRACE_WAITS,
s.SQL_TRACE_BINDS, stat.cpu - stat.CPU_this_call_start cpu_this_call, stat.CPU,
stat.UGA_memory, stat.PGA_memory, stat.Commits, stat.Rollbacks, si.Block_Gets,
si.Consistent_Gets, si.Physical_Reads, si.Block_Changes, si.Consistent_Changes FROM V$SESSION S, V$SESS_IO si,
(select ss.sid stat_sid,
sum(decode(sn.name, 'CPU used when call started', ss.value, 0))
CPU_this_call_start, sum(decode(sn.name, 'CPU used by this session', ss.value, 0)) CPU,
sum(decode(sn.name, 'session uga memory', ss.value, 0)) uga_memory,
sum(decode(sn.name, 'session pga memory', ss.value, 0)) pga_memory,
sum(decode(sn.name, 'user commits', ss.value, 0)) commits,
sum(decode(sn.name, 'user rollbacks', ss.value, 0)) rollbacks
from v$sesstat ss, v$statname sn
where ss.STATISTIC# = sn.STATISTIC#
and (sn.name = 'CPU used when call started' or
sn.name = 'CPU used by this session' or
sn.name = 'session uga memory' or
sn.name = 'session pga memory' or
sn.name = 'user commits' or
sn.name = 'user rollbacks')
group by ss.sid) stat
WHERE ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and
(s.type <> 'BACKGROUND') ) and (si.sid(+)=s.sid)
and (stat.stat_sid = s.sid)
--先用上面的查询列出当前session的状况
select p.spid thread_1,s.username,
decode(nvl(p.background,0),1,bg.description,
s.program ) program,
ss.value/100 CPU,physical_reads disk_io
from v$process p,
v$session s,
v$sesstat ss,
v$sess_io si,
v$bgprocess bg
where s.paddr=p.addr
and ss.sid=s.sid
and ss.statistic#=12
and si.sid=s.sid
and bg.paddr(+)=p.addr
order by ss.value desc;
--进程消耗的CPU,IO资源
select a.sid session_id,b.spid process_id
from v$session a,v$process b
where a.paddr = b.addr and a.sid = (select sid from v$mystat where rownum = 1);
--当前的session_id
SELECT S.USERNAME, P.SPID OS_PROCESS_ID, P.PID ORACLE_PROCESS_ID FROM V$SESSION S, V$PROCESS P WHERE S.PADDR = P.ADDR AND S.SID=227
--透过SESSION_ID 找process_ID
select
o.sid, o.sql_text, o.address, o.hash_value, o.user_name, s.schemaname
from v$open_cursor o, v$session s
where o.saddr = s.saddr
and o.sid = s.sid
and ( O.SID = '275')
--定位好session ID后,可以开始查看这个sid最近所open的游标,可以找到部分cursor
SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES WHERE
HASH_VALUE=TO_NUMBER('561877498') ORDER BY PIECE
--依靠hash_value可以继续往下定位详细的sql语句.
SELECT
SID, EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, AVERAGE_WAIT
, MAX_WAIT
, TIME_WAITED_MICRO
FROM v$session_event
Where sid = '278'
ORDER BY SID, TIME_WAITED DESC
--依靠sid,可以抓取这个session最近的event时间总和
SELECT
SID, SEQ#, EVENT, WAIT_TIME, SECONDS_IN_WAIT, STATE, p1, p1text, p2,
p2text, p3, p3text FROM v$session_wait
WHERE sid = '278'
ORDER BY SID, SECONDS_IN_WAIT DESC
--这个是查找sid的wait event
SELECT LK.SID, SE.USERNAME, SE.OSUSER, SE.MACHINE, DECODE(LK.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', LK.TYPE) LOCK_TYPE, DECODE(LK.LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(LK.LMODE)) MODE_HELD, DECODE(LK.REQUEST, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(LK.REQUEST)) MODE_REQUESTED, TO_CHAR(LK.ID1) LOCK_ID1, TO_CHAR(LK.ID2) LOCK_ID2, OB.OWNER, OB.OBJECT_TYPE, OB.OBJECT_NAME, DECODE(LK.BLOCK, 0, 'No', 1, 'Yes', 2, 'Global') BLOCK, SE.LOCKWAIT
FROM V$LOCK LK
INNER JOIN V$SESSION SE ON LK.SID = SE.SID
LEFT JOIN DBA_OBJECTS OB ON LK.ID1 = OB.OBJECT_ID
WHERE LK.TYPE IN ('TM', 'UL', 'TX')
AND (LK.SID = '278');
--当前sid的lock状况,当然啦,如果把最后的条件去掉,那就是当前数据库的全局lock状况.
SELECT * FROM v$session_longops;
--表扫描状态
/*******************************************************************/
/************************下面是trace部分****************************/
/*******************************************************************/
ALTER SESSION SET timed_statistics=true;
ALTER SESSION SET max_dump_file_size=unlimited;
ALTER SESSION SET tracefile_identifier='my_trace_session';
--几个相关的参数
--正常情况,一般都是直接上10046
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
ALTER SESSION SET EVENTS '10046 trace name context off';
exec dbms_monitor.serv_mod_act_trace_enable(service_name=>' ', module_name=>' ');
--使用 SERVICE_NAME,module_name来跟踪
exec dbms_monitor.client_id_trace_enable(client_id=>' ');
-- 使用CLIENT_IDENTIFIER来跟踪
exec dbms_monitor.session_trace_enable(139);
--如果定位得到session_id的话,就可以直接依靠session_ID跟踪
CREATE OR REPLACE TRIGGER trace_test_user AFTER LOGON ON DATABASE
BEGIN
IF USER LIKE '%\_test' ESCAPE '\' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics=true';
EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size=unlimited';
EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 8'' ';
END IF;
END;
/
-- 代码来自《Optimazing Oracle Performance》 P116
--触发器来做trace,一般属于最后的杀手锏.
/***********通过OS PID,使用oradebug来做trace**************/
SELECT S.USERNAME,
P.SPID OS_PROCESS_ID,
P.PID ORACLE_PROCESS_ID
FROM V$SESSION S, V$PROCESS P
WHERE S.PADDR = P.ADDR
--先找出OS PID
oradebug setospid 9999;
--set os PID
oradebug unlimit;
-- 设置Trace文件大小
oradebug event 10046 trace name context forever ,level 12;
-- 开启级别为12的Trace
Oradebug event 10046 trace name context off;
--关闭trace
/*使用DBMS_SYSTEM.SET_EV包, 也可以进行基于SID,SERIAL的跟踪*/
EXEC SYS.DBMS_SYSTEM.SET_EV(:sid, :serial, 10046, 12, '');
-- 开启level 12的Trace
EXEC SYS.DBMS_SYSTEM.SET_EV(:sid, :serial, 10046, 0, '');
-- 关闭Trace
/*DBMS_SUPPORT包默认情况下并没有包含在数据库中,需要通过运行$ORACLE_HOME/rdbms/admin/dbmssupp.sql 安装之后才能使用。*/
--开启自身进程:
EXEC SYS.DBMS_SUPPORT.START_TRACE(true, true);
EXEC SYS.DBMS_SUPPORT.STOP_TRACE();
--开启其他的进程
EXEC SYS.DBMS_SUPPORT.START_TRACE_IN_SESSION(:sid, :serial, true, true);
EXEC SYS.DBMS_SUPPORT.STOP_TRACE_IN_SESSION(:sid, :serial);
/*******************************使用DBMS_MONITOR包************************************/
--当前
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(WAITS=>true,BINDS=>true);
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE();
--其他的进程
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(:sid, :serial, true, true);
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(:sid, :serial);
--依靠client_identifier来trace
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('client_name', true, true);
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('client_name');
/*************************************数据库级的trace**********************************/
EXEC DBMS_MONITOR.DATABASE_TRACE_ENABLE(true, true);
EXEC DBMS_MONITOR.DATABASE_TRACE_DISABLE();
round(bitand(s.ownerid,16711680)/65536) parent_session_instid, s.SADDR, s.SID,
s.SERIAL#, s.AUDSID, s.PADDR, s.USER#, s.USERNAME, s.COMMAND, s.OWNERID,
s.TADDR, s.LOCKWAIT, s.STATUS, s.SERVER, s.SCHEMA#, s.SCHEMANAME, s.OSUSER,
s.PROCESS, s.MACHINE, s.TERMINAL, UPPER(s.PROGRAM) PROGRAM, s.TYPE,
s.SQL_ADDRESS, s.SQL_HASH_VALUE, s.SQL_ID, s.SQL_CHILD_NUMBER, s.PREV_SQL_ADDR,
s.PREV_HASH_VALUE, s.PREV_SQL_ID, s.PREV_CHILD_NUMBER, s.PLSQL_ENTRY_OBJECT_ID,
s.PLSQL_ENTRY_SUBPROGRAM_ID, s.PLSQL_OBJECT_ID, s.PLSQL_SUBPROGRAM_ID, s.MODULE,
s.MODULE_HASH, s.ACTION, s.ACTION_HASH, s.CLIENT_INFO, s.FIXED_TABLE_SEQUENCE,
s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#,
s.LOGON_TIME, s.LAST_CALL_ET, s.PDML_ENABLED, s.FAILOVER_TYPE,
s.FAILOVER_METHOD, s.FAILED_OVER, s.RESOURCE_CONSUMER_GROUP, s.PDML_STATUS,
s.PDDL_STATUS, s.PQ_STATUS, s.CURRENT_QUEUE_DURATION, s.CLIENT_IDENTIFIER,
s.BLOCKING_SESSION_STATUS, s.BLOCKING_INSTANCE, s.BLOCKING_SESSION, s.SEQ#,
s.EVENT#, s.EVENT, s.P1TEXT, s.P1, s.P1RAW, s.P2TEXT, s.P2, s.P2RAW, s.P3TEXT,
s.P3, s.P3RAW, s.WAIT_CLASS_ID, s.WAIT_CLASS#, s.WAIT_CLASS, s.WAIT_TIME,
s.SECONDS_IN_WAIT, s.STATE, s.SERVICE_NAME, s.SQL_TRACE, s.SQL_TRACE_WAITS,
s.SQL_TRACE_BINDS, stat.cpu - stat.CPU_this_call_start cpu_this_call, stat.CPU,
stat.UGA_memory, stat.PGA_memory, stat.Commits, stat.Rollbacks, si.Block_Gets,
si.Consistent_Gets, si.Physical_Reads, si.Block_Changes, si.Consistent_Changes FROM V$SESSION S, V$SESS_IO si,
(select ss.sid stat_sid,
sum(decode(sn.name, 'CPU used when call started', ss.value, 0))
CPU_this_call_start, sum(decode(sn.name, 'CPU used by this session', ss.value, 0)) CPU,
sum(decode(sn.name, 'session uga memory', ss.value, 0)) uga_memory,
sum(decode(sn.name, 'session pga memory', ss.value, 0)) pga_memory,
sum(decode(sn.name, 'user commits', ss.value, 0)) commits,
sum(decode(sn.name, 'user rollbacks', ss.value, 0)) rollbacks
from v$sesstat ss, v$statname sn
where ss.STATISTIC# = sn.STATISTIC#
and (sn.name = 'CPU used when call started' or
sn.name = 'CPU used by this session' or
sn.name = 'session uga memory' or
sn.name = 'session pga memory' or
sn.name = 'user commits' or
sn.name = 'user rollbacks')
group by ss.sid) stat
WHERE ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and
(s.type <> 'BACKGROUND') ) and (si.sid(+)=s.sid)
and (stat.stat_sid = s.sid)
--先用上面的查询列出当前session的状况
select p.spid thread_1,s.username,
decode(nvl(p.background,0),1,bg.description,
s.program ) program,
ss.value/100 CPU,physical_reads disk_io
from v$process p,
v$session s,
v$sesstat ss,
v$sess_io si,
v$bgprocess bg
where s.paddr=p.addr
and ss.sid=s.sid
and ss.statistic#=12
and si.sid=s.sid
and bg.paddr(+)=p.addr
order by ss.value desc;
--进程消耗的CPU,IO资源
select a.sid session_id,b.spid process_id
from v$session a,v$process b
where a.paddr = b.addr and a.sid = (select sid from v$mystat where rownum = 1);
--当前的session_id
SELECT S.USERNAME, P.SPID OS_PROCESS_ID, P.PID ORACLE_PROCESS_ID FROM V$SESSION S, V$PROCESS P WHERE S.PADDR = P.ADDR AND S.SID=227
--透过SESSION_ID 找process_ID
select
o.sid, o.sql_text, o.address, o.hash_value, o.user_name, s.schemaname
from v$open_cursor o, v$session s
where o.saddr = s.saddr
and o.sid = s.sid
and ( O.SID = '275')
--定位好session ID后,可以开始查看这个sid最近所open的游标,可以找到部分cursor
SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES WHERE
HASH_VALUE=TO_NUMBER('561877498') ORDER BY PIECE
--依靠hash_value可以继续往下定位详细的sql语句.
SELECT
SID, EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, AVERAGE_WAIT
, MAX_WAIT
, TIME_WAITED_MICRO
FROM v$session_event
Where sid = '278'
ORDER BY SID, TIME_WAITED DESC
--依靠sid,可以抓取这个session最近的event时间总和
SELECT
SID, SEQ#, EVENT, WAIT_TIME, SECONDS_IN_WAIT, STATE, p1, p1text, p2,
p2text, p3, p3text FROM v$session_wait
WHERE sid = '278'
ORDER BY SID, SECONDS_IN_WAIT DESC
--这个是查找sid的wait event
SELECT LK.SID, SE.USERNAME, SE.OSUSER, SE.MACHINE, DECODE(LK.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', LK.TYPE) LOCK_TYPE, DECODE(LK.LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(LK.LMODE)) MODE_HELD, DECODE(LK.REQUEST, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(LK.REQUEST)) MODE_REQUESTED, TO_CHAR(LK.ID1) LOCK_ID1, TO_CHAR(LK.ID2) LOCK_ID2, OB.OWNER, OB.OBJECT_TYPE, OB.OBJECT_NAME, DECODE(LK.BLOCK, 0, 'No', 1, 'Yes', 2, 'Global') BLOCK, SE.LOCKWAIT
FROM V$LOCK LK
INNER JOIN V$SESSION SE ON LK.SID = SE.SID
LEFT JOIN DBA_OBJECTS OB ON LK.ID1 = OB.OBJECT_ID
WHERE LK.TYPE IN ('TM', 'UL', 'TX')
AND (LK.SID = '278');
--当前sid的lock状况,当然啦,如果把最后的条件去掉,那就是当前数据库的全局lock状况.
SELECT * FROM v$session_longops;
--表扫描状态
/*******************************************************************/
/************************下面是trace部分****************************/
/*******************************************************************/
ALTER SESSION SET timed_statistics=true;
ALTER SESSION SET max_dump_file_size=unlimited;
ALTER SESSION SET tracefile_identifier='my_trace_session';
--几个相关的参数
--正常情况,一般都是直接上10046
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
ALTER SESSION SET EVENTS '10046 trace name context off';
exec dbms_monitor.serv_mod_act_trace_enable(service_name=>' ', module_name=>' ');
--使用 SERVICE_NAME,module_name来跟踪
exec dbms_monitor.client_id_trace_enable(client_id=>' ');
-- 使用CLIENT_IDENTIFIER来跟踪
exec dbms_monitor.session_trace_enable(139);
--如果定位得到session_id的话,就可以直接依靠session_ID跟踪
CREATE OR REPLACE TRIGGER trace_test_user AFTER LOGON ON DATABASE
BEGIN
IF USER LIKE '%\_test' ESCAPE '\' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics=true';
EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size=unlimited';
EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 8'' ';
END IF;
END;
/
-- 代码来自《Optimazing Oracle Performance》 P116
--触发器来做trace,一般属于最后的杀手锏.
/***********通过OS PID,使用oradebug来做trace**************/
SELECT S.USERNAME,
P.SPID OS_PROCESS_ID,
P.PID ORACLE_PROCESS_ID
FROM V$SESSION S, V$PROCESS P
WHERE S.PADDR = P.ADDR
--先找出OS PID
oradebug setospid 9999;
--set os PID
oradebug unlimit;
-- 设置Trace文件大小
oradebug event 10046 trace name context forever ,level 12;
-- 开启级别为12的Trace
Oradebug event 10046 trace name context off;
--关闭trace
/*使用DBMS_SYSTEM.SET_EV包, 也可以进行基于SID,SERIAL的跟踪*/
EXEC SYS.DBMS_SYSTEM.SET_EV(:sid, :serial, 10046, 12, '');
-- 开启level 12的Trace
EXEC SYS.DBMS_SYSTEM.SET_EV(:sid, :serial, 10046, 0, '');
-- 关闭Trace
/*DBMS_SUPPORT包默认情况下并没有包含在数据库中,需要通过运行$ORACLE_HOME/rdbms/admin/dbmssupp.sql 安装之后才能使用。*/
--开启自身进程:
EXEC SYS.DBMS_SUPPORT.START_TRACE(true, true);
EXEC SYS.DBMS_SUPPORT.STOP_TRACE();
--开启其他的进程
EXEC SYS.DBMS_SUPPORT.START_TRACE_IN_SESSION(:sid, :serial, true, true);
EXEC SYS.DBMS_SUPPORT.STOP_TRACE_IN_SESSION(:sid, :serial);
/*******************************使用DBMS_MONITOR包************************************/
--当前
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(WAITS=>true,BINDS=>true);
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE();
--其他的进程
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(:sid, :serial, true, true);
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(:sid, :serial);
--依靠client_identifier来trace
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('client_name', true, true);
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('client_name');
/*************************************数据库级的trace**********************************/
EXEC DBMS_MONITOR.DATABASE_TRACE_ENABLE(true, true);
EXEC DBMS_MONITOR.DATABASE_TRACE_DISABLE();