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        241 enq: 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;