Monday, December 6, 2010

How to view sql_profiles

I am using profiles for some very stubborn queries. I wanted to know what the profile really looks like.
It looks like Oracle has significantly changed the way it stores profiles with 11g.. Here is the query I am using to view what is out there for them.. Enjoy.

select name,hint,sql_text

from (
SELECT extractValue(value(h),'.') AS hint,
od.signature
FROM sys.sqlobj$data od, sys.sqlobj$ so,
table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
WHERE so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id
) a,
dba_sql_profiles b
where a.signature=b.signature

No comments:

Post a Comment