Viewing your profile hints in 11g

Topics about Databases, Backups, Tuning, Architecture, Systems Management, etc etc.

Post Reply
bgrenn
Posts: 91
Joined: Mon Mar 16, 2009 11:47 am
Location: Rochester, NY
Contact:

Viewing your profile hints in 11g

Post by bgrenn »

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

This is the same information for baselines


select plan_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_plan_baselines b
where a.signature=b.signature
Post Reply