Skip to content

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

Published June 17, 2015 By Brijesh Gogia

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

 

Brijesh Gogia
Latest posts by Brijesh Gogia (see all)
Print 🖨
Post Views: 431

Related posts:

  1. Create Tuning Task Manually
  2. Create SQL Baseline for SQL_ID
  3. Performance Tuning Basics 14 : Active Sessions History (ASH) Basics
  4. Flush Bad SQL Plan from Shared Pool
  5. Move SQL Plan Baseline to other Instance
  6. Move SQL PROFILE to other database

Categories:SCRIPTS SQL Tuning TUNING Tuning Scripts

2 Comments

  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.

    August 24, 2017 Reply
    • Smita S. Smita S.

      Thank You Akshay..

      September 2, 2018 Reply
Leave a Reply