oracle实用sql(5)--session相关信息

最新推荐文章于 2023-02-23 18:38:35 发布

congse3359

最新推荐文章于 2023-02-23 18:38:35 发布

阅读量134

点赞数

点击(此处)折叠或打开

--Sessions
SELECT round(bitand(s.ownerid, 65535)) parent_session_sid, round(bitand(s.ownerid,16711680)/65536) parent_session_instid, rawtohex(SADDR) as saddr,s.SID, s.SERIAL#, s.AUDSID, rawtohex(PADDR) as 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.SQL_EXEC_START, s.SQL_EXEC_ID, s.PREV_SQL_ADDR, s.PREV_HASH_VALUE, s.PREV_SQL_ID, s.PREV_CHILD_NUMBER, s.PREV_EXEC_START, s.PREV_EXEC_ID, 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.TOP_LEVEL_CALL#, 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.FINAL_BLOCKING_SESSION_STATUS, s.FINAL_BLOCKING_INSTANCE, s.FINAL_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.WAIT_TIME_MICRO, s.TIME_REMAINING_MICRO, s.TIME_SINCE_LAST_WAIT_MICRO, s.SERVICE_NAME, s.SQL_TRACE, s.SQL_TRACE_WAITS, s.SQL_TRACE_BINDS, s.SQL_TRACE_PLAN_STATS, s.SESSION_EDITION_ID, s.CREATOR_ADDR, s.CREATOR_SERIAL#, s.ECID
FROM V$SESSION S
WHERE ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') )
order by "PROGRAM", OWNERID;
--从上面前台sessions中找到某个session id,替换下面所有SID的值,然后执行下方所有语句,了解session对应的相关信息
--Session
SELECT rawtohex(s.SADDR) as saddr, s.AUDSID, s.Client_Info, s.Command, s.Last_Call_ET, s.LockWait, s.Logon_Time, s.Machine, s.Module,
s.OSUser, rawtohex(s.PADDR) as paddr, s.Process, s.Program, s.SchemaName, s.Serial#, s.Server, s.SID, s.Status, s.Terminal, s.Type,
s.Action, s.UserName, s.Failover_Method, s.Failed_Over, s.Failover_Type, s.PDML_Enabled, s.PDDL_Status, s.PDML_Status, s.PQ_Status,
s.Resource_Consumer_Group, s.Client_Identifier, s.SQL_Child_Number, s.SQL_ID, s.Service_Name, s.State, s.Event, s.Seconds_In_Wait,
s.Wait_Time, s.Wait_Class_ID, s.Wait_Class#, s.Wait_Class, s.P1Text, s.P1, s.P2Text, s.P2, s.P3Text, s.P3, s.SQL_Trace,
sci.Authentication_type, sci.Client_Charset, sci.Client_Version
FROM V$SESSION S,
(SELECT DISTINCT SID, SERIAL#, AUTHENTICATION_TYPE, CLIENT_CHARSET, CLIENT_VERSION FROM V$SESSION_CONNECT_INFO where SID = 32) SCI
WHERE ( S.SID = 32 )
AND ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') )
and s.sid = sci.sid (+)
and s.serial# = sci.serial# (+);
--Process
SELECT * FROM V$PROCESS WHERE addr=(select paddr from v$session where sid=32);
--IO
SELECT * FROM V$SESS_IO WHERE SID = 32;
--Current Waits
SELECT SID, SEQ#, EVENT, WAIT_TIME, SECONDS_IN_WAIT, STATE, p1, p1text, p2, p2text, p3, p3text, wait_time_micro, time_since_last_wait_micro
FROM v$session_wait
WHERE SID = 32
ORDER BY SID, SECONDS_IN_WAIT desc;
--Total Waits
SELECT SID, EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, AVERAGE_WAIT
, MAX_WAIT
, TIME_WAITED_MICRO
FROM v$session_event
Where SID = 32
ORDER BY SID, TIME_WAITED desc;
--Current Sql
select *
from v$sql
where (sql_id,child_number) =
(select decode(sql_id, null, prev_sql_id, sql_id) sql_id,
decode(sql_child_number,null,prev_child_number,sql_child_number) sql_child_number
from v$session
where sid = '32');
--Open Cursors
select o.sid, o.sql_text, o.address, o.hash_value, o.user_name, s.schemaname, o.sql_id
from v$open_cursor o, v$session s
where o.saddr = s.saddr
and o.sid = s.sid
and ( O.SID = 32);
--Access
SELECT sid, owner, type, object FROM v$access WHERE SID = 32;
--Locks
SELECT SID, DECODE(lk.TYPE, 'MR', 'Media Recovery', 'RT', 'Redo Thread', 'UN', 'User Name', 'DX', 'Distributed Xaction',
'CF', 'Control File', 'IS', 'Instance State', 'FS', 'File Set', 'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction',
'TS', 'Temp Segment', 'IV', 'Library Cache Invalidation', 'LS', 'Log Start or Switch', 'RW', 'Row Wait', 'SQ', 'Sequence Number',
'TE', 'Extend Table', 'TT', 'Temp Table', 'BL','Buffer hash table instance', 'CI','Cross-instance function invocation instance',
'CU','Cursor bind', 'DF','Data file instance', 'DL','Direct loader parallel index create', 'DM','Mount/startup db primary/secondary instance',
'DR','Distributed recovery process', 'HW','Space management operations on a specific segment', 'IN','Instance number', 'JQ','Job queue',
'KK','Thread kick', 'LA','Library cache lock instance lock namespace A', 'LB','Library cache lock instance lock namespace B',
'LC','Library cache lock instance lock namespace C', 'LD','Library cache lock instance lock namespace D',
'LE','Library cache lock instance lock namespace E', 'LF','Library cache lock instance lock namespace F',
'LG','Library cache lock instance lock namespace G', 'LH','Library cache lock instance lock namespace H',
'LI','Library cache lock instance lock namespace I', 'LJ','Library cache lock instance lock namespace J',
'LK','Library cache lock instance lock namespace K', 'LL','Library cache lock instance lock namespace L',
'LM','Library cache lock instance lock namespace M', 'LN','Library cache lock instance lock namespace N',
'LO','Library cache lock instance lock namespace O', 'LP','Library cache lock instance lock namespace P',
'MM','Mount definition global enqueue', 'NA','Library cache pin instance A', 'NB','Library cache pin instance B',
'NC','Library cache pin instance C', 'ND','Library cache pin instance D', 'NE','Library cache pin instance E',
'NF','Library cache pin instance F', 'NG','Library cache pin instance G', 'NH','Library cache pin instance H',
'NI','Library cache pin instance I', 'NJ','Library cache pin instance J', 'NK','Library cache pin instance K',
'NL','Library cache pin instance L', 'NM','Library cache pin instance M', 'NN','Library cache pin instance N',
'NO','Library cache pin instance O', 'NP','Library cache pin instance P', 'NQ','Library cache pin instance Q',
'NR','Library cache pin instance R', 'NS','Library cache pin instance S', 'NT','Library cache pin instance T',
'NU','Library cache pin instance U', 'NV','Library cache pin instance V', 'NW','Library cache pin instance W',
'NX','Library cache pin instance X', 'NY','Library cache pin instance Y', 'NZ','Library cache pin instance Z',
'PF','Password File', 'PI',' PS Parallel operation', 'PR','Process startup', 'QA','Row cache instance A', 'QB',
'Row cache instance B', 'QC','Row cache instance C', 'QD','Row cache instance D', 'QE','Row cache instance E',
'QF','Row cache instance F', 'QG','Row cache instance G', 'QH','Row cache instance H', 'QI','Row cache instance I',
'QJ','Row cache instance J', 'QK','Row cache instance K', 'QL','Row cache instance L', 'QM','Row cache instance M',
'QN','Row cache instance N', 'QO','Row cache instance O', 'QP','Row cache instance P', 'QQ','Row cache instance Q',
'QR','Row cache instance R', 'QS','Row cache instance S', 'QT','Row cache instance T', 'QU','Row cache instance U',
'QV','Row cache instance V', 'QW','Row cache instance W', 'QX','Row cache instance X', 'QY','Row cache instance Y',
'QZ','Row cache instance Z', 'SC','System commit number instance', 'SM','SMON', 'SN','Sequence number instance',
'SS','Sort segment', 'SV','Sequence number value', 'TA','Generic enqueue', 'US','Undo segment DDL', 'WL','Being-written redo log instance', 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,
lk.id1 lock_id1, lk.id2 lock_id2
FROM v$lock lk
WHERE SID = 32
ORDER BY SID;
--Rollback Segment Usage
SELECT r.segment_name, r.tablespace_name, t.status, t.cr_get, t.phy_io, t.used_ublk, t.noundo, t.start_time
FROM sys.v_$transaction t, dba_rollback_segs r
WHERE t.xidusn = r.segment_id
and t.addr =(select taddr from v$session where sid=32)
ORDER BY t.start_time;
--Long Ops
SELECT SID, decode(totalwork, 0, 0, round(100 * sofar/totalwork, 2)) "Percent", message "Message", start_time, elapsed_seconds, time_remaining
from v$Session_longops
where (SID = 32 )
ORDER BY SID;
--Statistics
SELECT a.sid,
decode(b.class,
1,
'User',
2,
'Redo',
4,
'Enqueue',
8,
'Cache',
16,
'OS',
32,
'ParallelServer',
64,
'SQL',
128,
'Debug',
72,
'SQL & Cache',
40,
'ParallelServer & Cache') class,
b.name,
a.value
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name in ('table scans (long tables)',
'bytes received via SQL*Net from client',
'bytes received via SQL*Net from dblink',
'consistent changes',
'consistent gets',
'CPU used by this session',
'physical reads',
'physical writes',
'session pga memory',
'session pga memory max',
'session uga memory',
'session logical reads',
'session uga memory max',
'sorts (disk)',
'sorts (memory)',
'table fetch continued row',
'opened cursors cumulative',
'opened cursors current',
'DDL statements parallelized',
'DML statements parallelized',
'queries parallelized',
'buffer is not pinned count',
'parse time cpu',
'redo blocks written',
'redo buffer allocation retries',
'redo entries',
'redo log space requests',
'redo size',
'redo synch time',
'redo synch writes',
'redo wastage',
'redo write time',
'redo writer latching time',
'redo writes')
and (SID = 32)
ORDER BY a.sid;