Description of null SQL _id in Oracle v $ session

 

 

 

First look at the SQL query results:

Select SQL _id, count (1) from V $ active_session_history

Where sample_time> to_timestamp ('2017-10-25 09:00:00 ', 'yyyy-mm-ddhh24: MI: ss ')

And sample_time <to_timestamp ('2017-10-25 09:22:00 ', 'yyyy-mm-ddhh24: MI: ss ')

And event = 'latch: Library cache'

Group by SQL _id;

SQL _id count (1)

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

1547 -- note that there are 1547 sessions whose SQL _id is null.

3dbdh26zbshcb 17

4cqx327x2p1av 17

9wbjxzbsvbn0g 17

08a4nkjhhu0ff 17

2g8g37mp0304q 17

1sqtxcuf8tctq 17

1kza9ykw04rhv 17

4tbtmv2aq6gba 17

A81694u5s79n6 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.

 

 

Continue query:

SQL & gt; 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.

 

Truth surfaced: According to the query results, there are two types of sessions with SQL _id being null, one is the background process and the other is the inactive session.

For a session whose SQL _id is null, we can check the prev_ SQL _id in V $ session to find the SQL statement of the previous v $ session. Of course, it may be null here.

 

For example:

$ 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

 

 

 

 

 

 

Bytes ---------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Skype: tianlesoftware

QQ: tianlesoftware@gmail.com

Email: tianlesoftware@gmail.com

Blog: http://blog.csdn.net/tianlesoftware

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware