oracle prevsqlid,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

2asgk01xtb6p0          9

4gd6b1r53yt88         17

3jnz9j02us7px         17

5p9r2w0f7rs03         17

7qx7wfncsqar3         17

59mm6v008wuac         17

9pnxbcs78g9v6         17

8rmkkwdygf2yn         17

8f40rh0ykpkp6         17

dhbbr2byp32sw         17

220yg2acnxq94         17https://www.cndba.cn/Dave/article/1309

4hn96ptb7q6mh         17

6gvch1xu9ca3g         17

https://www.cndba.cn/Dave/article/1309

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

https://www.cndba.cn/Dave/article/1309

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;

https://www.cndba.cn/Dave/article/1309

SID STATUS     PROGRAM                        TYPE         SQL_ID     PREV_SQL_ID     EVENT

---------- ---------- ------------------------------------------ ---------- -------------------------------------------------------

277 ACTIVE     oracle@H64AORA1(J000)         USER                                    jobq slavewaithttps://www.cndba.cn/Dave/article/1309

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 messagehttps://www.cndba.cn/Dave/article/1309

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。

https://www.cndba.cn/Dave/article/1309

对于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

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle 11g