Hi,
On a 11.2.0.1 database our developers ran a SELECT statement in two sessions (from SQLDeveloper). This SELECT statement is currently being executed for more than 4 hours, and if I check v$session (3 times), I see this:
SQL>select sid, status, sql_id, sql_address, sql_hash_value, sql_child_number, sql_exec_start, prev_sql_id, last_call_et, event
2 from v$session
3 where sid in (1169,38)
4 /
SID STATUS SQL_ID SQL_ADDRESS SQL_HASH_VALUE SQL_CHILD_NUMBER SQL_EXEC PREV_SQL_ID LAST_CALL_ET EVENT
---------- -------- ------------- ---------------- -------------- ---------------- -------- ------------- ------------ ------------------
38 ACTIVE a38wzrs19h8a2 07000001924E8838 43524418 0 28.06.11 gyytuvyfffj9c 15343 direct path read
1169 ACTIVE 00 0 gt39fjdj5z9za 15430 direct path read
SQL>/
SID STATUS SQL_ID SQL_ADDRESS SQL_HASH_VALUE SQL_CHILD_NUMBER SQL_EXEC PREV_SQL_ID LAST_CALL_ET EVENT
---------- -------- ------------- ---------------- -------------- ---------------- -------- ------------- ------------ ------------------
38 ACTIVE a38wzrs19h8a2 07000001924E8838 43524418 0 28.06.11 gyytuvyfffj9c 15471 direct path read
1169 ACTIVE 00 0 gt39fjdj5z9za 15558 kfk: async disk IO
SQL>/
SID STATUS SQL_ID SQL_ADDRESS SQL_HASH_VALUE SQL_CHILD_NUMBER SQL_EXEC PREV_SQL_ID LAST_CALL_ET EVENT
---------- -------- ------------- ---------------- -------------- ---------------- -------- ------------- ------------ -------------------
38 ACTIVE a38wzrs19h8a2 07000001924E8838 43524418 0 28.06.11 gyytuvyfffj9c 15499 kfk: async disk IO
1169 ACTIVE 00 0 gt39fjdj5z9za 15586 kfk: async disk IO
I checked V$SESSION several times and you can see that the wait events are changing over time, so both sessions are really ACTIVE.
The question I have is this: how is it possible that session 1169 has STATUS='ACTIVE' and at the same time a null SQL_ID (or if I turn the question around: how can I get the SQL the session 1169 is currently executing, since its status is ACTIVE)?
Thank you in advance for any comments.
Regards,
Jure