1. Viewing Active session Information
[SQL] View plain copy
SELECT *
From V$session
WHERE USERNAME is not NULL
and STATUS = ' ACTIVE '
ORDER by Logon_time, SID;
Note: status is used to determine session state
Active: Executing SQL statement (waiting for/using a Resource)
Inactive: Wait for operation (that is, wait for the SQL statement to be executed)
Killed: Marked for deletion
2. View the SQL statements and process information executed by the current session
[SQL] View plain copy
SELECT P.spid,
S.sid,
S.osuser,
S.machine,
S.program,
S.logon_time,
s.serial#,
Q.module,
Q.SQL_ID,
Q.sql_text,
Q.sql_fulltext
From V$session S, v$process P, V$sqlarea Q
WHERE s.paddr = p.addr (+)
and s.sql_id = q.sql_id (+)
and S.audsid = USERENV (' SESSIONID ');
else: Gets the SID of the current session
[SQL] View plain copy
SELECT * from v$session WHERE audsid = USERENV (' SESSIONID ');
SELECT * from V$mystat A WHERE rownum=1;
SELECT USERENV (' SID ') from DUAL;
3. The query session waits for its corresponding SQL
[SQL] View plain copy
SELECT P.pid,
S.sid,
s.serial#,
S.username,
Q.SQL_ID,
Q.sql_text,
Q.sql_fulltext,
W.event,
W.wait_time,
W.state,
Case
When w.state = ' waiting ' Then
W.seconds_in_wait
When w.state = ' waiting known time ' then
W.wait_time
END as Sec_in_wait
From V$session S, v$session_wait W, V$sqlarea Q, v$process P
WHERE S.sid = W.sid
and s.sql_id = q.sql_id
and p.addr = S.paddr
and w.event not like ' sql*net% '
And S.username is not NULL
and W.wait_time >= 0
ORDER by W.seconds_in_wait DESC;
Note: If the state value is waiting, then the wait_time value is useless. The second_in_wait value is the actual wait time (in seconds).
If the state value is wait unknow time, then both the Wait_time value and the second_in_wait value are useless.
If the state value is wait short time, then both the Wait_time value and the second_in_wait value are useless.
If the state value is waiting known time, then the Wait_time value is the actual wait (in seconds) and the second_in_wait value is useless.
4. Query the session wait event and the corresponding SQL
[SQL] View plain copy
SELECT P.pid,
S.sid,
s.serial#,
S.username,
Q.SQL_ID,
Q.sql_text,
Q.sql_fulltext,
E.event,
E.total_waits,
E.time_waited,
E.average_wait
From V$session S, V$session_event E, V$sqlarea Q, v$process P
WHERE S.sid = E.sid
and s.sql_id = q.sql_id
and p.addr = S.paddr
and e.event not like ' sql*net% '
and s.status = ' ACTIVE '
And S.username is not NULL;
5. Querying for long-running sessions with corresponding SQL
[SQL] View plain copy
SELECT S.sid,
Opname,
TRUNC (l.sofar/l.totalwork * 100, 2) | | '% ' as pct_work,
L.elapsed_seconds ELAPSED,
ROUND (L.elapsed_seconds * (L.TOTALWORK-L.SOFAR)/L.sofar) Remain_time,
Q.sql_text
From V$session_longops L, V$sqlarea Q, V$session S
WHERE L.sql_hash_value = Q.hash_value
and L.sid = S.sid
--and L.sofar! = L.totalwork
and l.elapsed_seconds>6
ORDER by L.start_time
6. Query session and lock with corresponding SQL
[SQL] View plain copy
SELECT A.owner scheme name,
A.object_name table name,
B.XIDUSN rollback segment number,
B.xidslot slot number,
B.XIDSQN serial number,
b.session_id Lock Table session_id,
B.oracle_username Lock table user name,
DECODE (D.type,
' XR ',
' NULL ',
' RS ',
' SS (row-s) ',
' CF ',
' SS (row-s) ',
' TM ',
' TABLE LOCK ',
' PW ',
' TABLE LOCK ',
' To ',
' TABLE LOCK ',
' TS ',
' TABLE LOCK ',
' RT ',
' ROW LOCK ',
' TX ',
' ROW LOCK ',
' MR ',
' S (Share) ',
NULL) Locking method,
C.machine User Group,
C.terminal machine Name,
B.os_user_name System User Name,
b.process System process ID,
DECODE (c.status, ' INACTIVE ', ' inactive ', ' active ', ' active ') activity,
C.server,
C.sid,
E.sql_text,
c.serial#,
C.program connection mode,
C.logon_time
From All_objects A,
V$locked_object B,
SYS. Gv_$session C,
V$lock D,
V$sqltext E
WHERE (a.object_id = b.object_id)
and (b.process = c.process)
and C.sid = D.sid
and B.locked_mode = D.lmode
and c.sql_id = e.sql_id (+)
ORDER by 1, 2;
7. Querying the corresponding SQL for blocked sessions and blocked sessions
[SQL] View plain copy
SELECT S1. USERNAME "Waiting USER",
S1. Osuser "OS User"
,
S1. Logon_time "Logon Time",
w.session_id "Sid",
P1. SPID "PID",
Q1. Sql_text "SQLText",
S2. USERNAME "HOLDING User",
S2. Osuser "OS User"
,
S2. Logon_time "Logon Time",
h.session_id "Sid",
P2. SPID "PID",
Q2. Sql_text "SQLText"
From SYS. V_$process P1,
SYS. V_$process P2,
SYS. V_$session S1,
SYS. V_$session S2,
Dba_locks W,
Dba_locks H,
V$sql Q1,
V$sql Q2
WHERE H.mode_held! = ' None '
and H.mode_held! = ' Null '
and w.mode_requested! = ' None '
and W.lock_type (+) = H.lock_type
and W.LOCK_ID1 (+) = H.lock_id1
and W.lock_id2 (+) = H.lock_id2
and w.session_id = S1. SID (+)
and h.session_id = S2. SID (+)
and S1. PADDR = P1. ADDR (+)
and S2. PADDR = P2. ADDR (+)
and S1. sql_id = Q1. SQL_ID (+)
and S2. sql_id = Q2. SQL_ID (+)
ORDER by h.session_id;
1. By following SQL you can query what your application is waiting for, and adjust the performance of the database for that information.
Select s.username,s.program,s.status,se.event,se.total_waits,se.total_timeouts,se.time_waited,se.average_wait
From v$session s, v$session_event se
Where S.sid=se.sid and se.event not like ' sql*net% ' and s.status = ' ACTIVE ' and s.username are NOT null
Query the locked table in 2.oracle and release the session
SELECT A.owner,a.object_name,b.xidusn,b.xidslot,b.xidsqn,b.session_id,b.oracle_username, B.OS_USER_NAME,B. PROCESS, B.locked_mode, C.machine,c.status,c.server,c.sid,c.serial#,c.program
From All_objects A,v$locked_object B,sys. Gv_$session C
WHERE (a.object_id = b.object_id) and (b.process = c.process) ORDER by
Release session SQL:
Alter system kill session ' SID, Serial# '
Alter system kill session ' 379, 21132 '
Alter system kill session ' 374, 6938 '
3. View the session that occupies the larger system IO
SELECT SE.SID,SE.SERIAL#,PR. Spid,se.username,se.status,se.terminal,se.program,se. Module,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes
From V$session SE, v$session_wait st,v$sess_io si,v$process PR
WHERE st.sid=se.sid and St.sid=si.sid and SE. Paddr=pr. ADDR and Se.sid>6 and st.wait_time=0 and st.event not as '%sql% ' ORDER by Physical_reads DESC
4. Find the session that consumes more CPU
Select A.sid,spid,status,substr (a.program,1,40) prog,a.terminal,osuser,value/60/100 value
From V$session a,v$process B,v$sesstat C
where c.statistic#=12 and C.sid=a.sid and a.paddr=b.addr order BY value DESC
5. Query the session is locked SQL can use the statement
Select Sys.v_$session.osuser,sys.v_$session.machine,v$lock.sid,
sys.v_$session.serial#,
Decode (V$lock.type,
' MR ', ' Media Recovery ',
' RT ', ' Redo Thread ',
' UN ', ' User Name ',
' TX ', ' Transaction ',
' TM ', ' DML ',
' UL ', ' PL/SQL User Lock ',
' 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 invalida-tion ',
' LS ', ' Log Start or Switch ',
' RW ', ' Row Wait ',
' SQ ', ' Sequence number ',
' TE ', ' Extend Table ',
' TT ', ' Temp Table ',
' Unknown ') LockType,
RTrim (object_type) | | "| | RTrim (owner) | | '. ' | | object_name object_name,
Decode (lmode, 0, ' None ',
1, ' Null ',
2, ' row-s ',
3, ' Row-x ',
4, ' Share ',
5, ' S/row-x ',
6, ' Exclusive ', ' Unknown ') Lockmode,
Decode (Request, 0, ' None ',
1, ' Null ',
2, ' row-s ',
3, ' Row-x ',
4, ' Share ',
5, ' S/row-x ',
6, ' Exclusive ', ' Unknown ') Requestmode,
CTime, Block B
From V$lock, All_objects, sys.v_$session
where V$lock.sid > 6
and Sys.v_$session.sid = V$lock.sid
and v$lock.id1 = all_objects.object_id;
Oracle viewing session level information
