How to Determine the SQL_ID for a SQL Statement
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 SQL_TEXT
------------- ----------------------------------------
0xzhrtn5gkpjs SELECT /* TARGET SQL */ * FROM dual
The 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 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 333 and 350; <====use snapid in which the query was ran
How to Determine the SQL_ID for a SQL Statement:
ReplyDeleteIf 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