SQL ID IS NOT CAPTURED IN V$SQL OR V$SQLAREA

SQL ID IS NOT CAPTURED IN V$SQL OR V$SQLAREA

I was trying to identify SQL ID for a microsecond query from v$sql, v$session, v$sqlarea etc.. But none of them retained the information of the sql because of ultra fast execution of the sql.

There may be other parsing reasons of the sql when specifying the sql text in WHERE clause. If the sql text is provided in WHERE clause with either upper or lower case or with additional spaces, then the sql may not be properly retrieved due to syntax variations. But here, this is not the case.

v$SQL displays the sql query details at the end of query execution generally as per oracle doc.

But if the query executes in fraction of milliseconds, then the details are not visible in v$sql or v$sqlarea.

So, alternatively v$open_cursor captures the query details immediately after query execution and it disappears too in a while. None of the AWR tables shows the details about this fast query.

SQL> SELECT sql_id,sql_text FROM v$open_cursor WHERE sql_id='5cygq95qjwmzr';

SQL_ID        SQL_TEXT
------------- --------------------
5cygq95qjwmzr SELECT name,type fro
              m dspm where type='P
              ROCEDURE' and owner=

SQL> SELECT sql_id from v$session where sql_id IS NOT NULL;

SQL_ID
-------------
30fux3fx84xuu

SQL> SELECT sql_text from v$sql where sql_id='30fux3fx84xuu';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT sql_id from v$session where sql_id IS NOT NULL

SQL> SELECT sql_id FROM v$active_session_history WHERE sql_id='5cygq95qjwmzr';

no rows selected
SQL>  SELECT sql_id from dba_hist_sqlstat WHERE PLAN_HASH_VALUE=3837617297;

no rows selected

SQL> SELECT sql_id from dba_hist_active_sess_history where SQL_PLAN_HASH_VALUE=3837617297;

no rows selected

The sql details were kept in v$sqlstats even though the query completed faster.

SQL> col SQL_TEXT for a20
SQL> SELECT sql_id,sql_text FROM v$sqlstats WHERE sql_text like '%SELECT name,type from dspm where type=%';

SQL_ID        SQL_TEXT
------------- --------------------
5cygq95qjwmzr SELECT name,type fro
              m dspm where type='P
              ROCEDURE' and owner=
              'MDSYS'

Also, another way to identify the sql id is to use 10046 trace if nothing works.

PARSING IN CURSOR #139655058600344 len=67 dep=0 uid=0 oct=3 lid=0 tim=3227964751 hv=1830703095 ad='7f481648' sqlid='5cygq95qjwmzr'
SELECT name,type from dspm where type='PROCEDURE' and owner='MDSYS'
END OF STMT