Missing sql_id from Active Session

Coskan has a very nice post: >> Where is the sql_id of active session?

Tanel has a nice post: >> What the heck is the SQL Execution ID – SQL_EXEC_ID?

>> V$OPEN_CURSOR lists cursors that each user session currently has opened and parsed.

Just another method.

SQL> @snapper ash=user+program+sqlid+child+event 5 1 200
Sampling SID 200 with interval 5 seconds, taking 1 snapshots...

-- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )

---------------------------------------------------------------------------------------------------------------------
Active% | USERNAME             | PROGRAM                   | SQL_ID          | SQL_CHILD_ | EVENT
---------------------------------------------------------------------------------------------------------------------
   100% | XXXXX_WH             | sqlplus@prodrpt02 (TNS V1 |                 |            | ON CPU

--  End of ASH snap 1, end=2012-05-31 10:44:31, seconds=5, samples_taken=50

PL/SQL procedure successfully completed.

SQL> select sql_id, cursor_type from v$open_cursor where sid = 200 and sql_exec_id is not null;

SQL_ID        CURSOR_TYPE
------------- ----------------------------------------------------------------
fht2wtmnn0ru4 OPEN

SQL> @sqlid fht2wtmnn0ru4
Show SQL text, child cursors and execution stats for SQLID fht2wtmnn0ru4 child 5

no rows selected

no rows selected

SQL> @sqlid fht2wtmnn0ru4 0
Show SQL text, child cursors and execution stats for SQLID fht2wtmnn0ru4 child 0

HASH_VALUE  CH# SQL_TEXT
---------- ---- ------------------------------------------------------------------------------------------------------------------------------------------------------
3913310020    0 SELECT /*+ monitor */ /* sqlt_s79277 */    

 CH# PARENT_HANDLE    OBJECT_HANDLE     PLAN_HASH     PARSES   H_PARSES EXECUTIONS    FETCHES ROWS_PROCESSED     CPU_MS     ELA_MS       LIOS       PIOS      SORTS USERS_EXECUTING
---- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------------
   0 00000005BA44C990 0000000397B44478 2413951034          1          1          1          1              1    5004440 5029771.22   19020425      48760          0               1

SQL>

About these ads