Saturday, December 16, 2017

How to Determine the SQL_ID of a SQL Statement

Let's fetch details from v$sql for sql_id and PHV:

select sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text from v$sql where sql_text LIKE '%&Identifiable_String%';

If SQL is not available in v$sql, DBA_HIST_SQLTEXT and DBA_HIST_SQLSTAT can be used:

select t.sql_id,t.sql_text,s.executions_total,s.elapsed_time_total
   from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t 
  where
   s.snap_id between 200 and 240;   <====use snapid in which the query was ran

If you have a PL/SQL block such as:

declare v1 number;
 begin
  select /* Test */ 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 '%Test%';
 SQL_ID        SQL_TEXT
 ------------- ----------------------------------------------------------------------------------
 55htre9qgwtzm declare v1 number; begin select /* Test */ 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 /* Test */ sum(sal) into v1 from emp is stored as SELECT SUM(SAL) FROM EMP

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
 -----------------------------------------------------------------------------
 3hhde98jk458a SELECT SUM(SAL) FROM EMP



No comments:

Post a Comment