Sql profile content

Extract sql profiles info and hints:

SELECT CREATED, PROFILE_NAME, SQL_TEXT, 
  XMLtransform(XMLTYPE(h.comp_data), '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="*">
<xsl:for-each select="/outline_data/hint">
<xsl:value-of select="."/>
<xsl:text>&#xa;</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>').getStringVal()
FROM DBMSHSXP_SQL_PROFILE_ATTR h, DBA_SQL_PROFILES p
WHERE p.name = h.profile_name;

This query puts all hints in one field. If multiply lines is good for you you can choose simplier implementation

SELECT CREATED, PROFILE_NAME, SQL_TEXT, extractvalue(VALUE(hint), '.') AS hint
FROM DBMSHSXP_SQL_PROFILE_ATTR h, DBA_SQL_PROFILES p, TABLE(xmlsequence(extract(xmltype(h.comp_data), '/outline_data/hint'))) hint
WHERE p.name = h.profile_name;

Also found solution on JL site, but this query valid for 10g only

select
       sp.sp_name, sa.attr#, sa.attr_val
from
       sqlprof$      sp,
       sqlprof$attr  sa
where
       sp.signature = sa.signature
and     sp.category  = sp.category
order by
       sp.sp_name,
       sa.attr#
;

The further investigations give me the link to Christian’s Antognini site where he recommends the following query for 11g:

SELECT so.name, extractvalue(VALUE(h), '.') AS hint
FROM sys.sqlobj$data od,
     sys.sqlobj$ so,
     TABLE(xmlsequence(extract(xmltype(od.comp_data), '/outline_data/hint'))) h
WHERE so.name = 'opt_estimate'
AND so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;

Test shows me, that this query is incorrect. Correct query is

SELECT so.name,extractvalue(VALUE(h), '.') AS hint
FROM sys.sqlobj$data od,
     sys.sqlobj$ so,
     TABLE(xmlsequence(extract(xmltype(od.comp_data), '/outline_data/hint'))) h
WHERE so.name IN (SELECT name FROM DBA_SQL_PROFILES)
AND so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;

But it should be rewritten to show sql-query and creation time