Find the SQL Queries from Literal Values in Oracle

Find the SQL Queries from Literal Values

Find the SQL plan hash value using literal values

select * from (
select plan_hash_value, count(distinct(hash_value)), sum(executions),
sum(parse_calls)
from gv$sql
group by plan_hash_value
having count(distinct(hash_value)) > 10
order by 2 desc
) where rownum<21;

Find the SQL Queries using literal values

select sql_text from v$sql where plan_hash_value in (
select plan_hash_value from (
select plan_hash_value, count(distinct(hash_value)), sum(executions),
sum(parse_calls)
from gv$sql
group by plan_hash_value
having count(distinct(hash_value)) > 10
order by 2 desc
) where rownum<21 );

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.