Viewing your profile hints in 11g

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

Viewing your profile hints in 11g

Postby bgrenn » Mon Dec 06, 2010 11:23 am

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
bgrenn
 
Posts: 91
Joined: Mon Mar 16, 2009 11:47 am
Location: Rochester, NY

Return to DBA Topics

Who is online

Users browsing this forum: No registered users and 1 guest

cron