Coskan Gundogar
Where is the sql_id of active session?
In this post I will give information on quick ways to find the sql_id’s of sessions for which you can’t see any entry when you look at v$session.
I usually see this behavior when sql is burning CPU in a nested loop and after a long time of running sql_id column suddenly not updated anymore. When I tweeted about it , ,Tanel Poder answered (If I a recall it right damn twitter does not have a proper search and history) something like ” this happens when oracle does too many recursive calls and loses track of the sql_id” (Tanel, if you are reading, please correct me if I am wrong about what you said)
On Metalink there are two notes I could find
Reason for Apparently Incorrect or Null Sql_id in v$sql [ID 406452.1]
This is caused by an architectural change in the 10g release: from 10.2 onwards, the v$sql columns (sql_id, sql_address and so on) will only show the top level user cursor, if any (ie it will not display the recursive cursors),. When using a remote procedure call, such as when a rpc call from a client (eg forms client) requests that a plsql object is executed on the server, there is no top level sql and, consequently, there is no sql_id in v$session. BUG:5146994 - CANNOT FIND RECURSIVE SQL IN V$SQLAREA FOR A RUNNING SNAPSHOT REFRESH JOB BUG 5724661 Abstract: WRONG VALUES FOR SQL_ID IN V$SESSION
On Null SQL_ID For Active Sessions In V$Session [ID 1343503.1]
From 10.2 onwards sql_id will only show the top level user cursor ( if any). Sometimes,Session does a plsql rpc call ie a client ( eg forms client) can request a plsql object to be executed at the server. In this case there is no top level sql and as such we don't see the sql_id in v$session. Currently, oracle don't store the current sql thru any v$ views. The only way to find that is thru event 10046. This is covered in Unpublished bug 5528670: "NULL VALUES FOR SQL_ID, SQL_HASH_VALUE, SQL_ADDRESS AND OTHERS IN V$SESSION", Which was closed as "Not a Bug".
When I have this issues I sometimes get it even on a sql which I run from sql_plus without a pl/sql call so I think limitation on these notes is a bit misleading or I am interpreting the information wrong. Anyway finding the reasons needs too much tracing research bla bla bla which you will never have time to go through when you hit the issue on a production system so best I cut the chase and start giving information how to find the sql_id
Notes says there is no v$ view which is a a bit misleading and notes also say 10046 is the only way to find it which is also another documentation error ignoring method 2 below.
On 10G there are 2 ways that I know will usualls work one of which needs tuning and diagnostics pack license other does not
On 11G there is one more way which again needs tuning and diagnostics pack license.
Method 1- using ASH/AWR data + v$sql combination (10g-11g license needed)
1a- Find the latest not null sql_id column for the given session and serial in v$active_session_history
1b- If you cant find it at first step, due to the reason sql_id information already lost in v$active_session_history, then you need to check similar way using dba_hist_active_sess_history
2- Once you find the sql_id,plan_hash_value double check it on v$sql if number on users_executing column is matching in current v$session then you have what you looking for
—Method-1 on Action
DATABASE1> @swact
SID USERN STATE EVENT SEQ# SEC_IN_STATE P1 P2 P3 SQL_ID CHN
------- ----- ------- ---------------------- ---------- ------------ ---------- ---------- ---------- ------------- ----
1352 XXWEB WAITING db file scattered read 40906 0 67 2638509 32 --->sql_id empty
294 XXWEB WAITING db file scattered read 16727 0 82 234468 32 --->sql_id empy
1263 COSKA WORKING On CPU / runqueue 44 0 1413697536 1 0 f5cqythck1gfz 1
DATABASE1> @last_sql 1352 %
SAMPLE_TIME SESSION_ID SESSION_SERIAL# SQL_ID PLAN_HASH_VALUE
------------------------------------- ---------- --------------- ------------- ---------------
31-JAN-11 11.00.35.524 1352 72 3p1b93fq81x0f 2118159443 ----> very likely our sql
31-JAN-11 11.00.34.514 1352 72 3p1b93fq81x0f 2118159443
31-JAN-11 11.00.33.514 1352 72 3p1b93fq81x0f 2118159443
31-JAN-11 11.00.32.514 1352 72 3p1b93fq81x0f 2118159443
31-JAN-11 11.00.31.514 1352 72 3p1b93fq81x0f 2118159443
31-JAN-11 11.00.30.514 1352 72 3p1b93fq81x0f 2118159443
31-JAN-11 11.00.29.504 1352 72 3p1b93fq81x0f 2118159443
31-JAN-11 11.00.28.494 1352 72 3p1b93fq81x0f 2118159443
31-JAN-11 11.00.27.494 1352 72 3p1b93fq81x0f 2118159443
9 rows selected.
DATABASE1> @last_sql 294
SAMPLE_TIME SESSION_ID SESSION_SERIAL# SQL_ID PLAN_HASH_VALUE
-------------------------------------- ---------- --------------- ------------- ---------------
31-JAN-11 10.50.40.071 294 39 3p1b93fq81x0f 2118159443 ----> very likely our sql
31-JAN-11 10.50.39.061 294 39 3p1b93fq81x0f 2118159443
31-JAN-11 10.50.38.061 294 39 3p1b93fq81x0f 2118159443
31-JAN-11 10.50.37.061 294 39 3p1b93fq81x0f 2118159443
31-JAN-11 10.50.36.061 294 39 3p1b93fq81x0f 2118159443
31-JAN-11 10.50.35.061 294 39 3p1b93fq81x0f 2118159443
31-JAN-11 10.50.34.051 294 39 3p1b93fq81x0f 2118159443
31-JAN-11 10.50.33.051 294 39 3p1b93fq81x0f 2118159443
31-JAN-11 10.50.32.051 294 39 3p1b93fq81x0f 2118159443
9 rows selected.
DATABASE1>-----LETS CHECK THE SQL
DATABASE1> @sqlid 3p1b93fq81x0f
SQL_ID HASH_VALUE SQL_TEXT
------------- ---------- --------------------------------------------------------------------------------------------------------
3p1b93fq81x0f 2894132238 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
CH# PLAN_HASH OPT_COST EXECUTIONS FETCHES ROWS_PROCESSED CPU_MS ELA_MS AVG_ELA LIOS PIOS USERS_EXECUTING
---- ---------- -------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- --------------
0 2118159443 956322 3 1 0 861533.028 5876120 1958.70667 21851873 21739212 2
Elapsed times + users_executing columns saying that these sqls has been running for a very long time and they are %99.999 the ones we are looking for
There are tiny challenges with this approach but not that much challeging.
I used it very much in RAC env with a system where multiple users and child cursors are also running
so if you are careful enough to compare apples with apples (same instance, same plan hash value,right session serial, longer elapsed time cursor) you are very unlikely to be mislead
code used for last_sql is like below (modified a bit for rac aware)
select inst_id,sample_time,session_id,session_serial#,sql_id from ( select inst_id,sample_time,session_id,session_serial#,sql_id from gv$active_session_history where sql_id is not null and session_id=&1 and inst_id=&2 order by 1 desc ) where rownum
Method 2- Oradebug dump error stack (10g-11g no license needed)- Most reliable and certain way it is %100 accurate and good part is it does not need any input from me since Tanel Poder already covered how to go through error stack on his post how to read errorstack output
I tried 10046 and never seen any sql_id information written for the 10 mins period I gathered the trace so I’m not %100 sure if it needs whole call to be finished before it writes the sql_id information. I personally won’t use 10046 for this case.
Method-3- SQL Monitoring (11g onwards-license needed). This is my favourite and its damn quick and %99.9 it works if the sql was running long enough to get monitored and then lost the track. It is damn simple like this piece of code
select distinct inst_id,sid,status,sql_id,sql_plan_hash_value,sql_child_address,sql_exec_id from gv$sql_plan_monitor where sid in (&1) and status='EXECUTING';
—Method 3 On Action
DATABASE1> @swact
SID USERN STATE EVENT SEQ# SEC_IN_STATE P1 P2 P3 SQL_ID CHN
------- ----- ------- ---------------------- ---------- ------------ ---------- ---------- ---------- ------------- ----
1352 XXWEB WAITING db file scattered read 40906 0 67 2638509 32 --->sql_id empty
294 XXWEB WAITING db file scattered read 16727 0 82 234468 32 --->sql_id empy
1263 COSKA WORKING On CPU / runqueue 44 0 1413697536 1 0 f5cqythck1gfz 1
---Check sql_monitoring
SQL> @mon_sql 1352,294
INST_ID SID STATUS SQL_ID SQL_PLAN_HASH_VALUE SQL_CHILD_ADDRES SQL_EXEC_ID
------- ---- ------------------- ------------- ------------------- ---------------- -----------
1 1352 EXECUTING 3p1b93fq81x0f 2118159443 00000002D88378E8 16777216
1 1352 EXECUTING 2kgnch9h5hbkz 1874321290 00000002D6223418 16777216 ---paren pl/sql
1 294 EXECUTING 3p1b93fq81x0f 2118159443 00000002D88378E8 16777217
1 294 EXECUTING 2kgnch9h5hbkz 1874321290 00000002D6223418 16777217
The second sql_id 2kgnch9h5hbkz is from pl_sql parent call which was calling 3p1b93fq81x0f so they both get monitored by v$sql_monitor
Apart from all these again if you have diagnostics pack license you can always use OEM/Grid Control to check back the sessions history but comparing with querying v$sql_monitor it is as slow as driving an electric car when you have ferrari.
As you can see there are more ways then listed if your session lost track of the sql_ids. If you have more ways (especially with tracing -like dump sql if there is an option like that) I would really like to hear.
Update after first comment
Method-4 Using v$open_cursor
First comment on which Michael Dinh mentioned he is using toad open cursors option to hunt down the sqls but he was not sure for the sqlplus.
I had a chance to to check with one of our systems. Looks like v$open_cursor is giving us information which not as clear as the other methods but still can be very
useful for standart edition or users without diagnostics pack and also can be easier than error stack which usually needs a change request on PRD systems.
SQL> @sw 207,980
SID USERN STATE EVENT SEQ# SEC_IN_STATE SQL_ID CHN P1 P2 P3
------- ----- ------- ------------------ ---------- ------------ ------------- ---- ------------------------- ------------------------- -------------------------
207 XXXXX WORKING On CPU / runqueue 62033 3 cellhash#=203CB5D5 diskhash#= 349428727 bytes= 16384
980 XXXXX WORKING On CPU / runqueue 759 1 cellhash#=D84BEC46 diskhash#=56AC941E bytes= 16384
SQL> @mon_sql 207,980
INST_ID SID STATUS SQL_ID SQL_PLAN_HASH_VALUE SQL_CHILD_ADDRES SQL_EXEC_ID
---------- ---------- ------------------- ------------- ------------------- ---------------- -----------
2 980 EXECUTING 91uc7cvw2rqab 4166729024 000000040D4560F8 33554458
2 207 EXECUTING 91uc7cvw2rqab 4166729024 000000040D4560F8 33554456
SQL> select * from v$open_cursor where sid in (207,980) order by 2;
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- ---------- ---------------- ---------- ------------- -------- --------- ----------- -----------------------------------
00000004508BA640 207 XXXXXXX 00000003A72340A0 2292456266 gw771824a86ua Begin PK 33554456 OPEN
00000004508BA640 207 XXXXXXX 00000003E6F0C230 2181403846 5bvw0ka10b566 Begin PK OPEN
00000004508BA640 207 XXXXXXX 000000040E81D128 4111302712 6k03snbuhv01s Begin PK OPEN
00000004508BA640 207 XXXXXXX 00000003EA88E4F0 1535077424 68u7tx5dryv1h Begin PK OPEN
00000004508BA640 207 XXXXXXX 00000004592E3690 914163366 4vs91dcv7u1p6 insert i OPEN-RECURSIVE
00000004508BA640 207 XXXXXXX 0000000411FC64C0 1407509578 8nwyam59y9t2a Begin PK OPEN
00000004508BA640 207 XXXXXXX 00000004117E1340 2399958818 62h75aq7hsxt2 Begin PK OPEN
00000004508BA640 207 XXXXXXX 00000004125C3DC0 4163623243 91uc7cvw2rqab INSERT I 33554456 OPEN-PL/SQL
0000000452AA1BB8 980 XXXXXXX 00000004125C3DC0 4163623243 91uc7cvw2rqab INSERT I 33554458 OPEN-PL/SQL
0000000452AA1BB8 980 XXXXXXX 00000004592E3690 914163366 4vs91dcv7u1p6 insert i OPEN-RECURSIVE
0000000452AA1BB8 980 XXXXXXX 000000040E81D128 4111302712 6k03snbuhv01s Begin PK OPEN
0000000452AA1BB8 980 XXXXXXX 0000000411FC64C0 1407509578 8nwyam59y9t2a Begin PK OPEN
0000000452AA1BB8 980 XXXXXXX 00000003E6F0C230 2181403846 5bvw0ka10b566 Begin PK OPEN
0000000452AA1BB8 980 XXXXXXX 00000004117E1340 2399958818 62h75aq7hsxt2 Begin PK OPEN
0000000452AA1BB8 980 XXXXXXX 00000003EA88E4F0 1535077424 68u7tx5dryv1h Begin PK OPEN
0000000452AA1BB8 980 XXXXXXX 00000003A72340A0 2292456266 gw771824a86ua Begin PK 33554458 OPEN
Once we spot the sqls we need to check all of them one by one on v$sql where if users_executing column (UEX) populated
SQL> @sqlid gw771824a86ua ---->bingo with two users and high ELAPSED TIMES. this is a pl/sql package calling the sql below SQL_ID HASH_VALUE SQL_TEXT ------------- ---------- -------------------------------------------------------------------------------------------------------------------------------- gw771824a86ua 2292456266 xXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX INS CH# UEX PLAN_HASH OPT_COST CPU_MS ELA_MS AVG_ELA OFFLOAD IO_SAVED_% IO_ELIG_GB IO_INTER_GB LIOS PIOS --- ---- ---- ---------- ---------- - ---------- ---------- ---------- ------- ---------- ---------- ----------- ---------- ---------- 2 0 2 0 0 412784696 414481976 16579.279 No .00 .00 58.007 2653518789 3248794 3 0 0 0 0 3215639.15 3307820.05 1653.91002 No .00 .00 3.212 1480568056 184192 ---- ---- Tried for all other SQLS but thet do not have users_executing column populated ---- SQL> @sqlid 91uc7cvw2rqab -->two users and high ELAPSED TIMES on 4th child SQL_ID HASH_VALUE SQL_TEXT ------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------- 91uc7cvw2rqab 4163623243 xXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX INS CH# UEX PLAN_HASH OPT_COST CPU_MS ELA_MS AVG_ELA OFFLOAD IO_SAVED_% IO_ELIG_GB IO_INTER_GB LIOS PIOS --- ---- ---- ---------- ---------- ---------- ---------- ---------- ------- ---------- ---------- ----------- ---------- ---------- 2 0 0 4166729024 13081 91245882.5 91690487.2 18338.0974 No .00 .00 11.644 3739546208 657923 2 1 0 4166729024 13081 120056449 120491925 15061.4907 No .00 .00 15.889 1250311783 883494 2 2 0 4166729024 13081 64616814.8 64899824.1 12979.9648 No .00 .00 10.564 424413836 587066 2 3 0 4166729024 13081 58282307.7 58454183.2 19484.7277 No .00 .00 5.442 1809526416 303954 2 4 2 4166729024 13081 78886102.5 79233558.3 19808.3896 No .00 .00 13.555 4163654789 756589
Thanks Michael for the information so we have 1 more method which does not need license
