Skip to content

Find SQL_ID of a SQL Statement in a PL/SQL Block

Finding the SQL_ID for a SQL Statement is straight forward task. You can use query like below to find the SQL_ID

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

To Determine the SQL_ID of a SQL Statement in a PL/SQL Block below steps can be followed:

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

 

[Post Views: 338]

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

One Comment

  1. Akshay Akshay

    we can also get the sql_id of the proc/pkg using:

    select * from dba_hist_sqltext where upper(sql_text) like ‘PRC_NAME’
    once we get the sql_id from the above that will be the top_level_sql_id which can be used as below:

    select sql_id,count(*) from dba_hist_active_sess_history where top_level_sql_id = ‘4km0b2fvvqjff’
    group by sql_id order by 2 desc;

    the above sql will list all the sql_ids of the individual sqls part of the procedure/pkg.

Leave a Reply

Required fields are marked *