The Geek Diary

How to Find the SQL_ID for a SQL statement or PL/SQL block

by

How to identify the SQL_ID of a statement

The SQL_ID of a statement can be found in an AWR or ASH report or by selecting it from the database data dictionary using the V$SQL view. If the SQL can be identified with a particular identifiable string or by some kind of unique comment such as: /* TARGET SQL */ then this will make it easier to locate.

For Example:

SELECT /* TARGET SQL */ * FROM dual;

SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text  
FROM  v$sql 
WHERE sql_text like 'SELECT /* TARGET SQL */%'

SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ----------------------------------------
0xzhrtn5gkpjs       272002086 SELECT /* TARGET SQL */ * FROM dual

The plan_hash_value is included here for convenience. You can also find the SQL_ID in the V$SQL view using a substitution variable:

SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&An_Identifiable_String%';

If SQL is no longer available in v$sql, you can find it in the AWR history views: DBA_HIST_SQLTEXT and DBA_HIST_SQLSTAT :

SELECT
    s.sql_id,
    s.plan_hash_value,
    t.sql_text,
    s.snap_id
FROM
    dba_hist_sqlstat   s,
    dba_hist_sqltext   t
WHERE s.dbid = t.dbid
AND   s.sql_id = t.sql_id
AND   sql_text LIKE 'SELECT /* TARGET SQL */%'
ORDER BY
    s.sql_id

You can use the snap_id to determine when the SQL was executed along with other information from AWR.

How to identify the SQL_ID of a SQL Statement in a PL/SQL Block

If you have a PL/SQL block such as:

declare v1 number;
begin
  select /* SimpleTest */ sum(sal) into v1 from emp;
end;
/

Then if you try to find the SQL_ID from v$sql then you will see the SQL_ID of the PL/SQL block NOT the SQL itself:

SQL> select sql_id, sql_text from v$sql where sql_text like '%SimpleTest%';

SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------------------
77hjjr9qgwtzm declare v1 number; begin select /* SimpleTest */ sum(sal) into v1 from emp; end;

The SQL statement within the PL/SQL block is actually stored separately, but you cannot see it because:

  • every sql statement in a PL/SQL block is stored as capital letters
  • every comment and INTO clause are removed

Note that optimizer hints are preserved.

In other words,

select /* SimpleTest */ sum(sal) into v1 from emp

is stored as:

SELECT SUM(SAL) FROM EMP

In order to find it’s SQL_ID you would need to search on something similar to the following:

SQL> select sql_id, sql_text from v$sql where sql_text like '%SUM(SAL)%EMP%';

SQL_ID        SQL_TEXT
------------- -------------------------------
5mqhh85sm278a SELECT SUM(SAL) FROM EMP

The SQL_ID can also be determined by using the hash_value from a SQL_TRACE. The hash value can be seen in the raw trace file identified by the “hv=” string.

.................................................
PARSING IN CURSOR #1 len=24 dep=1 uid=54 oct=3 lid=54 tim=1194298465705687 hv=1899044106 ad='997aa660' 
SELECT SUM(SAL) FROM EMP
END OF STMT
..................

In this case the hash value is 1899044106. To find the SQL_ID using the hash value use the following select:

SQL> SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE  hash_value = &Hash_Value; 

SQL_ID        HASH_VALUE SQL_TEXT
------------- ---------- -------------------------------
5mqhh85sm278a 1899044106 SELECT SUM(SAL) FROM EMP

You May Also Like