Find SQL ID and Hash value from SQL Statement in Oracle

Find SQL ID and Hash value from SQL Statement


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 as
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%';

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.