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