Find SQL ID and Hash value from SQL Statement in Oracle

Leave a reply

The content explains methods for retrieving SQL IDs and hash values. Users can find SQL IDs from the V$SQL view using specific queries or from historical data if not available. Additionally, it outlines a method using explain plans to acquire the plan hash value and subsequently locate the SQL_ID.

How to Retrieve SQL ID and Hash Values Easily

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

Example:

Query: Select * from hr.emp where employee_id = 10;

Find SQL ID for the query

SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text
FROM v$sql
WHERE sql_text like 'SELECT * from hr.emp%'

If SQL is not available then found from snapshot id.

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 and t.sql_text like  '%&An_Identifiable_String%';

Find SQL from history table if not able to find in V$SQL or V$SQLAREA view.

select * from dba_hist_sqltext where sql_text like '%some_text_from_query%';

Method 2(without executing SQL Query)

1. Fetch explain plan as below

-- USE EXPLAIN PLAN STATEMENT
EXPLAIN PLAN FOR
SELECT * FROM HR.EMP WHERE EMPLOYEE_NO - 100;

@?\RDBMS\ADMIN\utlxpls.sql;

--USE OF SET AUTOTRACE ON EXPLAIN STATEMENT
SET AUTOTRACE ON EXPLAIN
SELECT * FROM HR.EMP WHERE EMPLOYEE_NO - 100;

2. Find plan hash id in first line of SQL Plan you got from above methods.

PLAN_TABLE_OUTPUT
-------------------------------------
Plan hash value: 1578910895


3. Find the SQL_ID from V$SQLAREA by using plan_hash_value.

SELECT SQL_ID,SQL_TEXT FROM V$SQLAREA WHERE PLAN_HASH_VALUE='1578910895' AND SQL_TEXT LIKE '%SELECT * FROM HR.EMP%';




Leave a Reply