Oracle v$session 中sql_id 为 null 说明
先看SQL 查询结果：
select sql_id,count(1) from v$active_session_history
where sample_time>to_timestamp('2012-10-25 09:00:00','yyyy-mm-ddhh24:mi:ss')
and sample_time<to_timestamp('2012-10-25 09:22:00','yyyy-mm-ddhh24:mi:ss')
and event='latch: library cache'
group by sql_id;
SQL_ID COUNT(1)
------------- ----------
1547 --注意这里有1547个SQL_ID 为null 的session信息
3dbdh26zbshcb 17
4cqx327x2p1av 17
9wbjxzbsvbn0g 17
08a4nkjhhu0ff 17
2g8g37mp0304q 17
1sqtxcuf8tctq 17
1kza9ykw04rhv 17
4tbtmv2aq6gba 17
a8q344u5s79n6 17
fqdndxrnd0fjm 17
2asgk01xtb6p0 9
4gd6b1r53yt88 17
3jnz9j02us7px 17
5p9r2w0f7rs03 17
7qx7wfncsqar3 17
59mm6v008wuac 17
9pnxbcs78g9v6 17
8rmkkwdygf2yn 17
8f40rh0ykpkp6 17
dhbbr2byp32sw 17
220yg2acnxq94 17
4hn96ptb7q6mh 17
6gvch1xu9ca3g 17
7cwugf152r2s3 17
cqqtc133jjuuq 17
26 rows selected.
继续查：
SQL> set lin 200
SQL> col sid for a10
SQL> col machine for a20
SQL> col program for a30
SQL> col event for a15
SQL> col sql_id for a10
SQL> col type for a15
SQL> set pagesize 500
SQL> col STATUS for a10
SQL> col PREV_SQL_ID for a15
SQL>select sid,sql_id,SQL_hash_value,prev_hash_value,prev_sql_id,event from v$session;
SID STATUS PROGRAM TYPE SQL_ID PREV_SQL_ID EVENT
---------- ---------- ------------------------------------------ ---------- -------------------------------------------------------
277 ACTIVE oracle@H64AORA1(J000) USER jobq slavewait
281 INACTIVE plsqldev.exe USER a2b6nvx99pgrn SQL*Net message from client
284 INACTIVE JDBC ThinClient USER 520mkxqpf15q8 SQL*Net message from client
293 INACTIVE oracle@H64AORA1(TNS V1-V3) USER a7s2g79u9fjpb SQL*Net message from client
297 INACTIVE JDBC ThinClient USER dkwkp258ky77j SQL*Net message from client
298 INACTIVE JDBC ThinClient USER 520mkxqpf15q8 SQL*Net message from client
299 INACTIVE JDBC ThinClient USER SQL*Net messagefrom client
302 INACTIVE JDBC ThinClient USER 3rq4s8tpx168s SQL*Net message from client
308 INACTIVE sqlplus@H64AORA1(TNS V1-V3) USER dyk4dprp70d74 SQL*Net message from client
312 INACTIVE JDBC ThinClient USER SQL*Netmessage from client
313 ACTIVE oracle@H64AORA1(q001) BACKGROUND Streams AQ: qmnslave idle wait
317 ACTIVE oracle@H64AORA1 (QMNC) BACKGROUND Streams AQ: qmncoordinator idle wait
322 ACTIVE oracle@H64AORA1(MMNL) BACKGROUND rdbms ipc message
324 ACTIVE oracle@H64AORA1(CJQ0) BACKGROUND rdbms ipc message
325 ACTIVE oracle@H64AORA1(RECO) BACKGROUND rdbms ipc message
326 ACTIVE oracle@H64AORA1(SMON) BACKGROUND smon timer
327 ACTIVE oracle@H64AORA1(CKPT) BACKGROUND rdbms ipc message
328 ACTIVE oracle@H64AORA1(LGWR) BACKGROUND rdbms ipc message
329 ACTIVE oracle@H64AORA1(DBW1) BACKGROUND rdbms ipc message
330 ACTIVE oracle@H64AORA1(DBW0) BACKGROUND rdbms ipc message
331 ACTIVE oracle@H64AORA1(MMAN) BACKGROUND rdbms ipc message
332 ACTIVE oracle@H64AORA1(PSP0) BACKGROUND rdbms ipc message
333 ACTIVE oracle@H64AORA1(PMON) BACKGROUND pmon timer
647 INACTIVE JDBC ThinClient USER 520mkxqpf15q8 SQL*Net message from client
650 INACTIVE JDBC ThinClient USER 520mkxqpf15q8 SQL*Net message from client
653 INACTIVE JDBC ThinClient USER 520mkxqpf15q8 SQL*Net message from client
656 INACTIVE JDBC ThinClient USER 5zr4cbj9m140b SQL*Net message from client
657 INACTIVE JDBC ThinClient USER 520mkxqpf15q8 SQL*Net message from client
664 INACTIVE JDBC ThinClient USER 520mkxqpf15q8 SQL*Net message from client
29 rows selected.
真相浮出水面：根据查询结果：sql_id为null 的session 有2种，一种是后台进程，还有就是inactive的session。
对于SQL_ID 为null 的session，我们可以查看v$session中的 prev_sql_id 找到之前v$session 的SQL语句，当然这里也可能为空。
如：
$ ora sqltext a2b6nvx99pgrn
Session altered.
SQL_TEXT
--------------------------------------------------------------------------------
select null from dba_synonyms where 1=0
$ ora sqltext 520mkxqpf15q8
Session altered.
SQL_TEXT
--------------------------------------------------------------------------------
select 1 from dual
---------------------------------------------------------------------------------------
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
Skype: tianlesoftware
QQ: tianlesoftware@gmail.com
Email: tianlesoftware@gmail.com
Blog: http://blog.csdn.net/tianlesoftware
Weibo: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook: http://www.facebook.com/tianlesoftware
Linkedin: http://cn.linkedin.com/in/tianlesoftware
