v$session/v$process檢視涉及的相關會話資訊的查詢

關於伺服器程式與使用者程式概念及v$session/V$PROCESS檢視簡介 ，詳見：http://blog.csdn.net/q947817003/article/details/16922457
本文根據效能分析的一般思路來進行：
先看OS資源，確定CPU/記憶體消耗最多的程式，從程式找到會話，再檢視會話詳細資訊：包括會話型別、執行的SQL等等。
各種查詢

1.查當前會話SID

BYS@ bys3>select sid from v$mystat where rownum=1;
       SID
----------
        44
查詢當前資料庫有哪些會話，有哪些使用者，從哪些終端連線的
select username,machine,program from v$session where type='USER';  
查詢當前資料庫內會話的SPID,PID,使用者名稱，客戶端主機名，客戶端程式

select  b.spid,a.sid,a.username,a.program,a.machine  from v$session a,v$process b where a.paddr=b.addr and a.type='USER';

查詢當前資料庫內會話的SPID,PID,使用者名稱，客戶端主機名，客戶端程式以及使用者會話是活動的--正在執行SQL或被阻塞

select  b.spid,a.sid,a.username,a.program,a.machine  from v$session a,v$process b where a.paddr=b.addr and a.type='USER'  and  a.status='ACTIVE';

############################################################################

2.作業系統上查程式PID，以及伺服器程式所佔用CPU 內在資源。LOCAL=NO表示是通過網路連線。

ps -ef顯示的列依次是：1.UID   使用者ID  2.PID    程式ID  3.PPID   父程式ID  4.C      CPU佔用率  5.STIME     開始時間  6.TTY       開始此程式的TTY  7.TIME     此程式執行的總時間  8.CMD       命令名
UID        PID  PPID  C STIME TTY          TIME CMD
[oracle@bys3 ~]$ ps -ef|grep LOCAL|grep -v grep   ---查當前有哪些會話程式
oracle   25868     1  0 14:24 ?        00:00:04 oraclebys3 (LOCAL=NO)
oracle   29048 29045  0 18:52 ?        00:00:00 oraclebys3 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
ps aux顯示的列依次是：
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
[oracle@bys3 dbs]$ ps aux|grep LOCAL |grep -v grep
oracle   29313  0.0  2.0 326852 42996 ?        Ss   19:14   0:00 oraclebys3 (LOCAL=NO)
oracle   29392  0.1  1.9 326796 40992 ?        Ss   19:19   0:01 oraclebys3 (LOCAL=NO)
oracle   29550  0.0  1.7 326984 35148 ?        Ss   19:28   0:00 oraclebys3 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
############################################################################

3.通過程式PID查詢會話SID及通過會話SID找程式PID語句：

通過程式SPID找會話SID
BYS@ bys3>select s.sid from v$session s,v$process p where s.paddr=p.addr and p.spid=29550;
       SID
----------
        44
通過當前會話SID找程式PID：
BYS@ bys3>select p.spid from v$session s,v$process p where s.paddr=p.addr and s.sid=(select sid from v$mystat where rownum=1);   如果查其它會話SID，直接輸入相應SID即可
SPID
------------------------

29550

使用OSPID或者SID之一來查詢：

BYS@ bys3> select p.spid as ospid,s.sid from v$session s,v$process p where s.paddr=p.addr and (s.sid=&sid or p.spid=&ospid);
Enter value for sid: 888
Enter value for ospid: 29887
old   1:  select p.spid as ospid,s.sid from v$session s,v$process p where s.paddr=p.addr and (s.sid=&sid or p.spid=&ospid)
new   1:  select p.spid as ospid,s.sid from v$session s,v$process p where s.paddr=p.addr and (s.sid=888 or p.spid=29887)

OSPID                           SID
------------------------ ----------
29887                            42

查詢當前會話使用的PGA的大小資訊
select p.spid,p.PGA_USED_MEM/1024/1024 MB,p.PGA_ALLOC_MEM/1024/1024 MB,p.PGA_MAX_MEM/1024/1024 MB from v$session s,v$process p where s.paddr=p.addr and s.sid=(select sid from v$mystat where rownum=1);

############################################################################

4.通過會話SID檢視會話詳細的資訊

示例1：查會話使用的ORACEL資料庫使用者名稱、此會話最近一次執行的SQL命令（需要通過COMMAND欄位的數字查詢相應表選出，如這裡3是select),當前會話狀態是ACTIVE活動的，表示有SQL語句正在執行（SQL正執行或補阻塞），以及當前伺服器連線模式（專有還是共享）

BYS@ bys3>col username for a10
BYS@ bys3>select sid,user#,username,command,ownerid,status,server from v$session where sid=44;
       SID      USER# USERNAME      COMMAND    OWNERID STATUS   SERVER
---------- ---------- ---------- ---------- ---------- -------- ---------
        44         32 BYS                 3 2147483644 ACTIVE   DEDICATED
############################################################################

示例2：檢視44號使用者程式的客戶端相關資訊：客戶端的OS使用者名稱、客戶端程式號、客戶端主機名、客戶端程式、登陸時間等。

col sid for 99999
col osuser for a10
col machine for a10
col program for a20
col service_name for a10
col logon_time for a10
col process for a10
set linesize 200
BYS@ bys3>select sid,osuser,process,machine,program,type,service_name,sql_trace,logon_time,resource_consumer_group from v$session where sid=44;
 SID OSUSER     PROCESS    MACHINE    PROGRAM              TYPE       SERVICE_NA SQL_TRAC LOGON_TIME RESOURCE_CONSUMER_GROUP
---- ---------- ---------- ---------- -------------------- ---------- ---------- -------- ---------- --------------------------------
  44 oracle     29547      bys3.bys.c sqlplus@bys3.bys.com USER       SYS$USERS  DISABLED 2013/11/24 OTHER_GROUPS
                           om          (TNS V1-V3)                                         19:28:22
############################################################################

示例3：查詢會話當前執行的SQL語句及上一條SQL語句的sql_id從而查出SQL_TEXT--注意要關閉serveroutput：set serveroutput off

會話47：刪除不提交
BYS@ bys3>delete a;
1 row deleted.
會話34：先刪除一行新插入的值8，可以刪除。刪除整張表，則發生等待-
21:19:11 BYS@ bys3>select sid from v$mystat where rownum=1;
       SID
----------
        34
21:19:18 BYS@ bys3>insert into a values(8);
1 row created.
21:19:23 BYS@ bys3>select * from a;
B
----------
8
55
7
3
21:19:27 BYS@ bys3>delete a where b=8;
1 row deleted.
21:19:32 BYS@ bys3>delete a;   ----此時會話一直在等待執行
會話32：查詢會話34的SQL語句資訊
BYS@ bys3>col sql_text for a30
BYS@ bys3>select sql_id,sql_text from v$sql where sql_id in('96vz122hk9mtq','dmmj3tuc9ynbj');

SQL_ID        SQL_TEXT
------------- ------------------------------
96vz122hk9mtq delete a     ----可以查出當前SQL語句是delete a
dmmj3tuc9ynbj delete a where b=8          --可以查出上一條SQL語句是delete a where b=8
注意事項：要關閉serveroutput：set serveroutput off ，如果set serveroutput on,則查出的上一條SQL語句就成了：BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; 關於 DBMS_OUTPUT官方文件介紹如下：http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_output.htm   其中有一節是關閉DBMS_OUTPUT的描述： DISABLE Procedure：
This procedure disables calls to PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES, and purges the buffer of any remaining information.
As with the ENABLE Procedure, you do not need to call this procedure if you are using the SERVEROUTPUT option of SQL*Plus.   如果開啟SQLPLUS中的SERVEROUTPUT選項，不需要呼叫這個程式。即開啟SERVEROUTPUT選項呼叫此過程。故set serveroutput off即可。
############################################################################

示例4：檢視某個會話的鎖/阻塞等情況及簡單解決思路--查當前系統中存在'TM','TX')等待的SQL語句：

同時開啟兩個會話，在會話SID 38，刪除表B不提交；在會話SID為43，同時刪除B表，此時會話43處於等待狀態。從會話38查詢會話43的會話的阻塞資訊：
BYS@ bys3>col event for a20
BYS@ bys3>select sid,row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#,blocking_session_status,blocking_session,event#,event from v$session where sid=43;
       SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# BLOCKING_SE BLOCKING_SESSION     EVENT# EVENT
---------- ------------- -------------- --------------- ------------- ----------- ---------------- ---------- --------------------
        43         21428              4             167             0 VALID                     38        241enq: TX - row lock contention
可以看到ROW_WAIT_OBJ#為21428，可以通過DBA_OBJECTS檢視的OBJECT_ID查出。blocking_session_status欄位值為VALID 表明有阻塞，BLOCKING_SESSION欄位可以看到43會話是被38號會話阻塞，EVENT事件是：行鎖爭用。
BYS@ bys3>select object_name from dba_objects where object_id=21428;
OBJECT_NAME
-----------------------------
B
可以從v$lock中查詢38及43會話的相關的鎖狀態：38號會話阻塞了43號-BLOCK列，關於鎖，詳見：http://blog.csdn.net/q947817003/article/details/13274845
BYS@ bys3>select * from v$lock where sid in(38,43);
ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
28635B24 28635B50         38 AE        100          0          4          0        818          0
28635E88 28635EB4         43 AE        100          0          4          0       1232          0
28636550 2863657C         43 TX     655393       1107          0          6        810          0
B69AC7D8 B69AC808         43 TM      21428          0          3          0        810          0
B69AC7D8 B69AC808         38 TM      21428          0          3          0        815          0

276BBFB8 276BBFF8         38 TX     655393       1107          6          0        815          1

查當前系統中存在'TM','TX')等待的SQL語句：

BYS@ bys3>select a.*,b.sid,b.username from v$sqltext a,v$session b,v$lock c where  a.sql_id=b.sql_id and b.sid=c.sid and c.type in('TM','TX');
ADDRESS  HASH_VALUE SQL_ID        COMMAND_TYPE      PIECE SQL_TEXT                              SID USERNAME
-------- ---------- ------------- ------------ ---------- ------------------------------ ---------- ----------
2461AC9C 2848502917 gbxry2ynwjd45            6          0 update test set status=99              44 BYS

############################################################################

如果查詢到有阻塞並遲遲不能釋放進而影響了業務正常執行，可以手工KILL掉。方法如下：
BYS@ bys3>alter system kill session '38,1521';   --不能KILL掉當前會話38--38,1521對應SID/serial#
alter system kill session '38,1521'
*
ERROR at line 1:
ORA-00027: cannot kill current session
新開啟一會話：
BYS@ bys3>alter system kill session '38,1521';
System altered.
此時返回原會話38：執行查詢返回錯誤：ORA-00028: your session has been killed
BYS@ bys3>select sid,serial# from v$session where sid=38;
select sid,serial# from v$session where sid=38
*
ERROR at line 1:
ORA-00028: your session has been killed
此時檢視43號會話，原來被阻塞的SQL語句得以執行：
BYS@ bys3>delete b;    ---因為阻塞，所以語句執行時間顯示是17分鐘
1 row deleted.
Elapsed: 00:17:24.84

能解決物件被鎖定問題

1、select * from v$locked_object 查出被鎖定的物件，其中object_id是物件的ID,session_id是被鎖定物件有session ID；
2、select object_name, object_type from dba_objects where object_id = 剛才查出來的object_id;（根據v$locked_object裡的object_id提出來的）
這樣來查被鎖定這個物件的名字，如果能確定是哪個TABLE被鎖並且要解鎖，則再執行
3、select sid, serial#, machine, program from v$session where sid =第1步中查出來的session_id;(是根據v$locked_object對應鎖定記錄的session_id找出來的) 然後
4、alter system kill session ‘sid,serial#’;用來殺死這個會話；

--找出互相阻塞的會話：

select a.sid blocker_sid,a.serial#,a.username as blocker,
       b.type,decode(b.lmode,0,'None',1,'Null',2,'Row share',
       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,
       b.ctime as time_held,c.sid as waiter_sid,
       decode(c.request,0,'None',1,'Null',2,'Row share',
              3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,
       c.ctime time_waited
  from  v$lock b, v$enqueue_lock c, v$session a
 where a.sid = b.sid  
   and b.id1 = c.id1(+)
   and b.id2 = c.id2(+)
   and c.type(+) = 'TX'
   and b.type = 'TX'
   and b.block = 1
   order by time_held, time_waited desc;