oracle session sqlid,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

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