Database/ORACLE
ORACLE SESSION Monitoring SQL
하이주니
2011. 1. 5. 17:54
반응형
SELECT ROUND (BITAND (s.ownerid, 65535)) parent_session_sid,
ROUND (BITAND (s.ownerid, 16711680) / 65536) parent_session_instid,
s.INST_ID,
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.PORT,
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,
s.ECID,
stat.cpu - stat.CPU_this_call_start cpu_this_call,
stat.CPU,
stat.UGA_memory,
stat.PGA_memory,
stat.Commits,
stat.Rollbacks
FROM GV$SESSION S,
( SELECT ss.inst_id stat_inst_id,
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 GV$sesstat ss, GV$statname sn
WHERE ss.STATISTIC# = sn.STATISTIC# AND ss.inst_id = sn.inst_id
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.inst_id, ss.sid) stat
WHERE ( (s.USERNAME IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND'))
AND ( (stat.stat_inst_id = s.inst_id) AND (stat.stat_sid = s.sid))
ORDER BY "PROGRAM", OWNERID
반응형