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>
Leave a Reply