[20171120]理解v$session的state字段(11G).txt
--//https://blogs.oracle.com/database4cn/vsession-%e4%bd%a0%e7%9c%8b%e5%88%b0%e7%9a%84event%e7%9c%9f%e7%9a%84%e6%98%afsession%e5%bd%93%e5%89%8d%e7%9a%84%e7%ad%89%e5%be%85%e4%ba%8b%e4%bb%b6%e4%b9%88-v2
--//v$session - 你看到的event真的是session当前的等待事件么?
--//我重复测试,其内容直接转抄作者的链接:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//sesson 1:
SCOTT@book> @ &r/spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
274 5 53785 DEDICATED 53786 21 3 alter system kill session '274,5' immediate;
--//记下sid=274.
SCOTT@test01p> exec loop null; end loop;
--//死循环.
--//sesson 2:
SCOTT@book> @ &r/wait
no rows selected
--//嗯,没有输出,看来我的检测脚本要修改看看.
SCOTT@book> select sid,serial#,status,sql_id,event,seq# from v$session where sid=274;
SID SERIAL# STATUS SQL_ID EVENT SEQ#
---------- ---------- -------- ------------- ---------------------------------------- ----------
274 5 ACTIVE 61bj68pvygxvz SQL*Net message from client 40
SCOTT@book> @ &r/sqlid 61bj68pvygxvz
SQL_ID SQLTEXT
------------- --------------------------------
61bj68pvygxvz BEGIN loop null; end loop; END;
您会发现以上这个session竟然会在 "等待" SQL*Net message from client，并且status为ACTIVE，大家都知道SQL*Net message from
client是一个空闲等待，代表server process正在等待client发出下一个sql指令。
接下来观察以上以上进程的CPU消耗情况，会发现它在持续的ON CPU
$ top -p 53786 -b
top - 11:03:32 up 711 days, 2:26, 1 user, load average: 1.21, 0.62, 0.37
Tasks: 1 total, 1 running, 0 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.2%us, 1.0%sy, 0.0%ni, 98.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 132261196k total, 113979744k used, 18281452k free, 1122908k buffers
Swap: 31455264k total, 2300k used, 31452964k free, 108723604k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
53786 oracle 20 0 849m 27m 23m R 99.8 0.0 2:07.03 oracle
--//它几乎消耗了一个的CPU.
那么问题来了，这个如此烧CPU的死循环session怎么会是处于空闲等待？答案是我们忽略了v$session.state这个非常重要的列。
一个session的状态要么是在等待，要么是在ON CPU，v$session.state这个列可以判断这个状态。有且只有v$session.state='WAITING'
的时候，才代表这个session当前正在等待这个event，否则代表这个session在ON CPU，并且观察到的event只是进程在ON CPU之前的最后
一个等待。
接下来我们将以上用于查询session等待的SQL改一下，添加上v$session.state这个列:
SCOTT@book> select sid,serial#,status,state,sql_id,event,seq# from v$session where sid=274;
SID SERIAL# STATUS STATE SQL_ID EVENT SEQ#
---------- ---------- -------- ------------------- ------------- ---------------------------------------- ----------
274 5 ACTIVE WAITED KNOWN TIME 61bj68pvygxvz SQL*Net message from client 40
可见STATE的值为"WAITED KNOWN TIME"，不是"WAITING"，这表明这个session当前在ON CPU，"SQL*Net message from client"只是这个
session在ON CPU之前的最后一个等待。因此当您查询v$session观察session的等待事件的时候，一定不要忘了v$session.state这个关键
列。
--//看来给修改我的检测脚本:
select p1raw,p2raw,p3raw,p1,p2,p3,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait
from v$session where wait_class<>'Idle'
and sid not in (select sid from v$mystat where rownum=1)
order by event ;
--//修改如下:
select p1raw,p2raw,p3raw,p1,p2,p3,sid,serial#,seq#,event,status,state,wait_time_micro,seconds_in_wait
from v$session where ( wait_class<>'Idle' or (status='ACTIVE' and STATE='WAITED KNOWN TIME'))
and sid not in (select sid from v$mystat where rownum=1)
order by event ;
SCOTT@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 00 1650815232 1 0 274 5 40 SQL*Net message from client ACTIVE WAITED KNOWN TIME 15932642 357