V$session 视图的小运用
我对这个视图没什么好感，因为这个视图又长又臭，对oracle真是爱并痛恨着。
一、kill session 这个是有些危险的操作如果不小心kill 掉了后台进程那就只能哭了，很可能导致数据库的崩溃或者运行时出现故障。为了确保你所kill 的那个session 确实是你所要kill 掉的。应该通过 V$session 视图来获得足够的信息。
SQL> select sid,serial#,username,type,program
2 from V$session
3 where type != 'BACKGROUND';
SID SERIAL# USERNAME TYPE PROGRAM
---------- ---------- ---------- ---------- -------------------------
1 5 SYS USER sqlplus@zeng (TNS V1-V3)
36 10 HR USER sqlplus@zeng (TNS V1-V3)
我通过使用上面的查询就足够让我确定我要kill 掉的会话了。在kill 掉sid = 36 的会话之前我在这个会话中执行如下的update 语句。
SQL> select salary from employees where employee_id = 100;
SALARY
----------
24000
SQL> update employees
2 set salary = salary + 100
3 where employee_id = 100;
1 row updated.
下面把sid = 36,serial# = 10 的会话kill 掉。接着马上查询V$session 视图。
SQL> alter system kill session '36,10';
System altered.
SQL> select sid,serial#,username,type,status,program
2 from V$session
3 where type != 'BACKGROUND';
SID SERIAL# USERNAME TYPE STATUS PROGRAM
---------- ---------- ---------- ---------- -------- -------------------------
1 5 SYS USER ACTIVE sqlplus@zeng (TNS V1-V3)
36 10 HR USER KILLED sqlplus@zeng (TNS V1-V3)
实际上当发出Kill 命令的时候并不是马上的kill 掉某个 session ,而是要等会话中的操作的完成（等待数据库的响应或者事务的回滚）由会话“自杀”。所以发出kill 命令会马上把指定的那个sesion 标记成 killed 但是session 不一定马上就会被kill 掉。再来看看hr 那个会话，在其中随便的执行一条SQL 命令,会报如下的错误。虽然session 已经被
kill 掉了,但是要是保持着一个会话的“尊严”,并不退出sqlplus 。把hr 再次连入数据库，执行下面的查询，我们可以知道被kill 掉的会话中没有提交的事务，oracle 自动的把他回滚了。
SQL> select count(*) from employees;
select count(*) from employees
*
ERROR at line 1:
ORA-00028: your session has been killed
SQL> conn hr/hr
Connected.
SQL> select salary from employees where employee_id = 100;
SALARY
----------
24000
二、会话阻塞的查询。
现在来确定下数据库中用户会话的状态。把sid = 36 的这个hr 的会话标记为session 1.
SQL> select sid,serial#,username,type,program
2 from V$session
3 where type != 'BACKGROUND';
SID SERIAL# USERNAME TYPE PROGRAM
---------- ---------- ---------- ---------- -------------------------
1 5 SYS USER sqlplus@zeng (TNS V1-V3)
36 14 HR USER sqlplus@zeng (TNS V1-V3)
在新增一个hr 用户的会话。把sid = 32 的这个hr 的会话标记为session 2.
SQL> select sid,serial#,username,type,program
2 from V$session
3 where type != 'BACKGROUND';
SID SERIAL# USERNAME TYPE PROGRAM
---------- ---------- ---------- ---------- -------------------------
1 5 SYS USER sqlplus@zeng (TNS V1-V3)
32 23 HR USER sqlplus@zeng (TNS V1-V3)
36 14 HR USER sqlplus@zeng (TNS V1-V3)
在session 1 中执行如下的update 语句。
SQL> update employees
2 set salary = salary + 100
3 where employee_id = 100;
1 row updated.
在session 2 中执行同样的update 语句。现在来确定这个两个会话的相关信息。
SQL> set linesize 200
SQL> select sid,serial#,username,type,
2 blocking_session_status,blocking_session,blocking_instance
3 from V$session
4 where sid in (32,36);
SID SERIAL# USERNAME TYPE BLOCKING_SE BLOCKING_SESSION BLOCKING_INSTANCE
---------- ---------- ---------- ---------- ----------- ---------------- -----------------
32 23 HR USER VALID 36 1
36 14 HR USER NO HOLDER
我们可以知道session 1 也就是sid = 36 的会话把session 也就是sid = 32 的会话阻塞了。更加本质的原因是锁的问题。
SQL> select sid,type,lmode,request,block
2 from V$lock
3 where sid in (32,36);
SID TY LMODE REQUEST BLOCK
---------- -- ---------- ---------- ----------
36 AE 4 0 0
32 AE 4 0 0
32 TX 0 6 0
32 TM 3 0 0
36 TM 3 0 0
36 TX 6 0 1
6 rows selected.
我们从查询可以知道sid = 32 的会话请求一个 lmode 为 6 的锁，但是该锁已经被sid = 36 的会话持有了。所以 sid = 32 的会话被阻塞了。如果想知道会话究竟是因为执行了什么语句而阻塞的可以通过下面的查询获知。
SQL> select sql_text from V$sqltext t1
2 where t1.hash_value = ( select sql_hash_value
3 from V$session t2
4 where t2.sid = 32);
SQL_TEXT
----------------------------------------------------------------
update employees set salary = salary + 100 where employee_id = 100
------------------------------
v$session 表中比较常用的几个字段说明^_^
select machine,username,count(*) from v$session
group by machine,username
order by machine
统计每个客户端，每个用户的链接数
ccc023 - 193
server - 169
ecard-web - 179
1. sid,serial#
通过sid我们可以查询与这个session相关的各种统计信息,处理信息.
a. select * from v$sesstat where sid = :sid;
查询用户相关的各种统计信息.
select a.sid,a.statistic#,b.name,a.value
from v$sesstat a,v$statname b
where a.statistic# = b.statistic#
and a.sid = :sid;
b. 查询用户相关的各种io统计信息
select * from v$sess_io where sid = :sid;
c. 查询用户想在正在打开着的游标变量.
select * from v$open_cursor where sid = :sid;
d. 查询用户当前的等待信息. 以查看当前的语句为什么这么慢/在等待什么资源.
select * from v$session_wait where sid = :sid ;
e. 查询用户在一段时间内所等待的各种事件的信息. 以了解这个session所遇到的瓶颈^_^
select * from v$session_event where sid = :sid;
f. 还有, 就是当我们想kill当前session的时候可以通过sid,serial#来处理.
alter system kill session ':sid,:serail#';
2. paddr.字段, process addr, 通过这个字段我们可以查看当前进程的相关信息, 系统进程id,操作系统用户信息等等.
select a.pid,a.spid,b.name,b.description,a.latchwait,a.latchspin,a.pga_used_mem,a.pga_alloc_mem,a.pga_freeable_mem,a.pga_max_mem
from v$process a,v$bgprocess b
where a.addr = b.paddr(+)
and a.addr = :paddr
3. command 字段, 表明当前session正在执行的语句的类型.请参考reference.
4. taddr 当前事务的地址,可以通过这个字段查看当前session正在执行的事务信息, 使用的回滚段信息等^_^
select b.name rollname,a.*
from v$transaction a,v$rollname b
where a.xidusn = b.usn
and a.addr = '585EC18C';
5. lockwait字段, 可以通过这个字段查询出当前正在等待的锁的相关信息.
select *
from v$lock
where (id1,id2) = (
select id1,id2 from v$lock where kaddr = '57C68C48'
)
6. (sql_address,sql_hash_value) (prev_sql_addr,prev_hash_value) 根据这两组字段, 我们可以查询到当前session正在执行的sql语句的详细信息.
select * from v$sqltext where address = :sql_address and hash_value = :sql_hash_value;
7.ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
可以通过这几个字段查询现在正在被锁的表的相关信息.^_^
a. 首先得到被锁的的信息
select * from dba_objects where object_id = :row_wait_obj#;
b. 根据row_wait_file#可以找出对应的文件的信息.
select * from v$datafile where file# = :row_wait_file#.
c. 在根据以上四个字段构造出被锁的字段的rowid信息.
select dbms_rowid.ROWID_CREATE(1,:row_wait_obj#,:row_wait_file#,:row_wait_block#,:row_wait_row#) from dual;
8. logon_time 当前session的登录时间.
9. last_call_et 该session idle的时间, 每3秒中更新一次^_^
10.status 值有两个：active，inactive。
acitve表示会话的sql没有执行完。
11、记录客户端登录ip
创建触发器
create or replace trigger on_logon_trigger
after logon on database
begin
dbms_application_info.set_client_info(sys_context( 'userenv', 'ip_address' ) );
end;
用select client_info from v$session;查询客户端ip
(1)-V$SESSION_WAIT
这是一个寻找性能瓶颈的关键视图。它提供了任何情况下session在数据库中当前正在等待什么(如果session当前什么也没在做，则显示它最后的等待事件)。当系统存在性能问题时，本视图可以做为一个起点指明探寻问题的方向。
V$SESSION_WAIT中，每一个连接到实例的session都对应一条记录。
V$SESSION_WAIT中的常用列
l SID: session标识
l EVENT: session当前等待的事件，或者最后一次等待事件。
l WAIT_TIME: session等待事件的时间(单位，百分之一秒)如果本列为0，说明session当前session还未有任何等待。
l SEQ#: session等待事件将触发其值自增长
l P1, P2, P3: 等待事件中等待的详细资料
l P1TEXT, P2TEXT, P3TEXT: 解释说明p1,p2,p3事件
附注：
1.State字段有四种含义﹕
(1)Waiting：SESSION正等待这个事件。
(2)Waited unknown time：由于设置了timed_statistics值为false，导致不能得到时间信息。表示发生了等待，但时间很短。
(3)Wait short time：表示发生了等待，但由于时间非常短不超过一个时间单位，所以没有记录。
(4)Waited knnow time：如果session等待然后得到了所需资源，那么将从waiting进入本状态。
2.Wait_time值也有四种含义：
(1)值>0：最后一次等待时间(单位：10ms)，当前未在等待状态。
(2)值=0：session正在等待当前的事件。
(3)值=-1：最后一次等待时间小于1个统计单位，当前未在等待状态。
(4)值=-2：时间统计状态未置为可用，当前未在等待状态。
3.Wait_time和Second_in_wait字段值与state相关:
(1)如果state值为Waiting，那么wait_time值无用。Second_in_wait值是实际的等待时间(单位：秒)。
(2)如果state值为Wait unknow time，那么wait_time值和Second_in_wait值都无用。
(3)如果state值为Wait short time，那么wait_time值和Second_in_wait值都无用。
(4)如果state值为Waiting known time，那么wait_time值就是实际等待时间(单位：秒)，Second_in_wait值无用。
V$SESSION_WAIT中的连接列
Column View Joined Column(s)
SID V$SESSION SID
示例：
1.列出当前系统的等待事件
SELECT event,
sum(decode(wait_time,0,1,0)) "Curr",
sum(decode(wait_time,0,0,1)) "Prev",
count(*)"Total"
FROM v$session_wait GROUPBY event ORDERBYcount(*);
EVENT Prev Curr Tot
--------------------------------------------- ---- ----- -----
PL/SQL lock timer 0 1 1
SQL*Net more data from client 0 1 1
smon timer 0 1 1
pmon timer 0 1 1
SQL*Net message to client 2 0 2
db file scattered read 2 0 2
rdbms ipc message 0 7 7
Enqueue 0 12 12
pipe get 0 12 12
db file sequential read 3 10 13
latch free 9 6 15
SQL*Net message from client 835 1380 2215
这个按事件和wait_time的分组查询列出下列的信息：
l 多数的session都是空闲事件如：SQL*Net message from client, pipe get, PMON timer等。
l session的cpu占用可以通过上次session的非等待事件大致算出，除此问题外：看起来多数session没有在等待什么事情(难道他们都在干活？)但其最后等待事件都是SQL*Net message from client。
2.列出指定ID的等待事件
select * from v$session_wait where sid=100;
3.应用p1,p2,p3进行等待事件的分析
v$session_wait视图的列代表的缓冲区忙等待事件如下：
P1—与等待相关的数据文件的全部文件数量。
P2—P1中的数据文件的块数量。
P3—描述等待产生原因的代码。
例：select p1 "File #", p2 "Block #", p3 "Reason Code"
from v$session_wait
where event = 'buffer busy waits';
如果以上查询的结果显示一个块在忙等待，以下的查询将显示这一块的名称和类型：
select owner, segment_name, segment_type
from dba_extents
where file_id = &P1 and &P2 between block_id and block_id + blocks -1;
我们也可以查询dba_data_files以确定等待的文件的file_name，方法是使用v$session_wait中的P1。
从v$session_wait中查询P3(原因编码)的值可以知道session等待的原因。原因编码的范围从0到300，下列为部分编码所代表的事项：
0 块被读入缓冲区。
100 我们想要NEW(创建)一个块，但这一块当前被另一session读入。
110 我们想将当前块设为共享，但这一块被另一session读入，所以我们必须等待read()结束。
120 我们想获得当前的块，但其他人已经将这一块读入缓冲区，所以我们只能等待他人的读入结束。
130 块被另一session读入，而且没有找到其它协调的块，所以我们必须等待读的结束。缓冲区死锁后这种情况也有可能产生。所以必须读入块的CR。
200 我们想新创建一个block，但其他人在使用，所以我们只好等待他人使用结束。
210 Session想读入SCUR或XCUR中的块，如果块交换或者session处于非连续的TX模式，所以等待可能需要很长的时间。
220 在缓冲区查询一个块的当前版本，但有人以不合法的模式使用这一块，所以我们只能等待。
230 以CR/CRX方式获得一个块，但块中的更改开始并且没有结束。
231 CR/CRX扫描找到当前块，但块中的更改开始并且没有结束。
(2)-V$SESSION_EVENT
本视图记录了每个session的每一项等待事件。由上文所知V$SESSION_WAIT显示了session的当前等待事件，而V$SESSION_EVENT则记录了session自启动起所有的事件。
V$SESSION_EVENT中的常用列
l SID：session标识
l EVENT：session等待的事件
l TOTAL_WAITS：此session当前事件的总等待数
l TIME_WAITED：此session总等待时间(单位，百分之一秒)
l AVERAGE_WAIT：此session当前事件平均等待时间(单位，百分之一秒)
l TOTAL_TIMEOUTS：等待超时次数
其它用法与V$SESSION_WAIT相似，不详述了
附注：
Oracle的等待事件是衡量Oracle运行状况的重要依据及指标。等待事件的概念是在Oracle7.0.1.2中引入的，大致有100个等待事件。在Oracle 8.0中这个数目增加到了大约150个，在Oracle8i中大约有200个事件,在Oracle9i中大约有360个等待事件。主要有两种类别的等待事件，即空闲(idle)等待事件和非空闲(non-idle)等待事件。
select machine,username,count(*) from v$session
group by machine,username
order by machine
统计每个客户端，每个用户的链接数
ccc023 - 193
server - 169
ecard-web - 179
1. sid,serial#
通过sid我们可以查询与这个session相关的各种统计信息,处理信息.
a. select * from v$sesstat where sid = :sid;
查询用户相关的各种统计信息.
select a.sid,a.statistic#,b.name,a.value
from v$sesstat a,v$statname b
where a.statistic# = b.statistic#
and a.sid = :sid;
b. 查询用户相关的各种io统计信息
select * from v$sess_io where sid = :sid;
c. 查询用户想在正在打开着的游标变量.
select * from v$open_cursor where sid = :sid;
d. 查询用户当前的等待信息. 以查看当前的语句为什么这么慢/在等待什么资源.
select * from v$session_wait where sid = :sid ;
e. 查询用户在一段时间内所等待的各种事件的信息. 以了解这个session所遇到的瓶颈^_^
select * from v$session_event where sid = :sid;
f. 还有, 就是当我们想kill当前session的时候可以通过sid,serial#来处理.
alter system kill session ':sid,:serail#';
2. paddr.字段, process addr, 通过这个字段我们可以查看当前进程的相关信息, 系统进程id,操作系统用户信息等等.
select a.pid,a.spid,b.name,b.description,a.latchwait,a.latchspin,a.pga_used_mem,a.pga_alloc_mem,a.pga_freeable_mem,a.pga_max_mem
from v$process a,v$bgprocess b
where a.addr = b.paddr(+)
and a.addr = :paddr
3. command 字段, 表明当前session正在执行的语句的类型.请参考reference.
4. taddr 当前事务的地址,可以通过这个字段查看当前session正在执行的事务信息, 使用的回滚段信息等^_^
select b.name rollname,a.*
from v$transaction a,v$rollname b
where a.xidusn = b.usn
and a.addr = '585EC18C';
5. lockwait字段, 可以通过这个字段查询出当前正在等待的锁的相关信息.
select *
from v$lock
where (id1,id2) = (
select id1,id2 from v$lock where kaddr = '57C68C48'
)
6. (sql_address,sql_hash_value) (prev_sql_addr,prev_hash_value) 根据这两组字段, 我们可以查询到当前session正在执行的sql语句的详细信息.
select * from v$sqltext where address = :sql_address and hash_value = :sql_hash_value;
7.ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
可以通过这几个字段查询现在正在被锁的表的相关信息.^_^
a. 首先得到被锁的的信息
select * from dba_objects where object_id = :row_wait_obj#;
b. 根据row_wait_file#可以找出对应的文件的信息.
select * from v$datafile where file# = :row_wait_file#.
c. 在根据以上四个字段构造出被锁的字段的rowid信息.
select dbms_rowid.ROWID_CREATE(1,:row_wait_obj#,:row_wait_file#,:row_wait_block#,:row_wait_row#) from dual;
8. logon_time 当前session的登录时间.
9. last_call_et 该session idle的时间, 每3秒中更新一次^_^
10.status 值有两个：active，inactive。
acitve表示会话的sql没有执行完。
11、记录客户端登录ip
创建触发器
create or replace trigger on_logon_trigger
after logon on database
begin
dbms_application_info.set_client_info(sys_context( 'userenv', 'ip_address' ) );
end;
用select client_info from v$session;查询客户端ip
(1)-V$SESSION_WAIT
这是一个寻找性能瓶颈的关键视图。它提供了任何情况下session在数据库中当前正在等待什么(如果session当前什么也没在做，则显示它最后的等待事件)。当系统存在性能问题时，本视图可以做为一个起点指明探寻问题的方向。
V$SESSION_WAIT中，每一个连接到实例的session都对应一条记录。
V$SESSION_WAIT中的常用列
l SID: session标识
l EVENT: session当前等待的事件，或者最后一次等待事件。
l WAIT_TIME: session等待事件的时间(单位，百分之一秒)如果本列为0，说明session当前session还未有任何等待。
l SEQ#: session等待事件将触发其值自增长
l P1, P2, P3: 等待事件中等待的详细资料
l P1TEXT, P2TEXT, P3TEXT: 解释说明p1,p2,p3事件
附注：
1.State字段有四种含义﹕
(1)Waiting：SESSION正等待这个事件。
(2)Waited unknown time：由于设置了timed_statistics值为false，导致不能得到时间信息。表示发生了等待，但时间很短。
(3)Wait short time：表示发生了等待，但由于时间非常短不超过一个时间单位，所以没有记录。
(4)Waited knnow time：如果session等待然后得到了所需资源，那么将从waiting进入本状态。
2.Wait_time值也有四种含义：
(1)值>0：最后一次等待时间(单位：10ms)，当前未在等待状态。
(2)值=0：session正在等待当前的事件。
(3)值=-1：最后一次等待时间小于1个统计单位，当前未在等待状态。
(4)值=-2：时间统计状态未置为可用，当前未在等待状态。
3.Wait_time和Second_in_wait字段值与state相关:
(1)如果state值为Waiting，那么wait_time值无用。Second_in_wait值是实际的等待时间(单位：秒)。
(2)如果state值为Wait unknow time，那么wait_time值和Second_in_wait值都无用。
(3)如果state值为Wait short time，那么wait_time值和Second_in_wait值都无用。
(4)如果state值为Waiting known time，那么wait_time值就是实际等待时间(单位：秒)，Second_in_wait值无用。
V$SESSION_WAIT中的连接列
Column View Joined Column(s)
SID V$SESSION SID
示例：
1.列出当前系统的等待事件
SELECT event,
sum(decode(wait_time,0,1,0)) "Curr",
sum(decode(wait_time,0,0,1)) "Prev",
count(*)"Total"
FROM v$session_wait GROUPBY event ORDERBYcount(*);
EVENT Prev Curr Tot
--------------------------------------------- ---- ----- -----
PL/SQL lock timer 0 1 1
SQL*Net more data from client 0 1 1
smon timer 0 1 1
pmon timer 0 1 1
SQL*Net message to client 2 0 2
db file scattered read 2 0 2
rdbms ipc message 0 7 7
Enqueue 0 12 12
pipe get 0 12 12
db file sequential read 3 10 13
latch free 9 6 15
SQL*Net message from client 835 1380 2215
这个按事件和wait_time的分组查询列出下列的信息：
l 多数的session都是空闲事件如：SQL*Net message from client, pipe get, PMON timer等。
l session的cpu占用可以通过上次session的非等待事件大致算出，除此问题外：看起来多数session没有在等待什么事情(难道他们都在干活？)但其最后等待事件都是SQL*Net message from client。
2.列出指定ID的等待事件
select * from v$session_wait where sid=100;
3.应用p1,p2,p3进行等待事件的分析
v$session_wait视图的列代表的缓冲区忙等待事件如下：
P1—与等待相关的数据文件的全部文件数量。
P2—P1中的数据文件的块数量。
P3—描述等待产生原因的代码。
例：select p1 "File #", p2 "Block #", p3 "Reason Code"
from v$session_wait
where event = 'buffer busy waits';
如果以上查询的结果显示一个块在忙等待，以下的查询将显示这一块的名称和类型：
select owner, segment_name, segment_type
from dba_extents
where file_id = &P1 and &P2 between block_id and block_id + blocks -1;
我们也可以查询dba_data_files以确定等待的文件的file_name，方法是使用v$session_wait中的P1。
从v$session_wait中查询P3(原因编码)的值可以知道session等待的原因。原因编码的范围从0到300，下列为部分编码所代表的事项：
0 块被读入缓冲区。
100 我们想要NEW(创建)一个块，但这一块当前被另一session读入。
110 我们想将当前块设为共享，但这一块被另一session读入，所以我们必须等待read()结束。
120 我们想获得当前的块，但其他人已经将这一块读入缓冲区，所以我们只能等待他人的读入结束。
130 块被另一session读入，而且没有找到其它协调的块，所以我们必须等待读的结束。缓冲区死锁后这种情况也有可能产生。所以必须读入块的CR。
200 我们想新创建一个block，但其他人在使用，所以我们只好等待他人使用结束。
210 Session想读入SCUR或XCUR中的块，如果块交换或者session处于非连续的TX模式，所以等待可能需要很长的时间。
220 在缓冲区查询一个块的当前版本，但有人以不合法的模式使用这一块，所以我们只能等待。
230 以CR/CRX方式获得一个块，但块中的更改开始并且没有结束。
231 CR/CRX扫描找到当前块，但块中的更改开始并且没有结束。
(2)-V$SESSION_EVENT
本视图记录了每个session的每一项等待事件。由上文所知V$SESSION_WAIT显示了session的当前等待事件，而V$SESSION_EVENT则记录了session自启动起所有的事件。
V$SESSION_EVENT中的常用列
l SID：session标识
l EVENT：session等待的事件
l TOTAL_WAITS：此session当前事件的总等待数
l TIME_WAITED：此session总等待时间(单位，百分之一秒)
l AVERAGE_WAIT：此session当前事件平均等待时间(单位，百分之一秒)
l TOTAL_TIMEOUTS：等待超时次数
其它用法与V$SESSION_WAIT相似，不详述了
附注：
Oracle的等待事件是衡量Oracle运行状况的重要依据及指标。等待事件的概念是在Oracle7.0.1.2中引入的，大致有100个等待事件。在Oracle 8.0中这个数目增加到了大约150个，在Oracle8i中大约有200个事件,在Oracle9i中大约有360个等待事件。主要有两种类别的等待事件，即空闲(idle)等待事件和非空闲(non-idle)等待事件。