Viewing Details of a SQL Profile

The following query will show the details of the most recently created SQL Profiles first. Each component hint of the Profile is returned one per row.

SELECT CREATED, PROFILE_NAME, ATTR_VALUE, SQL_TEXT 
  FROM DBA_SQL_PROFILES PROF, 
       DBMSHSXP_SQL_PROFILE_ATTR ATTR
 WHERE PROF.NAME=ATTR.PROFILE_NAME
 ORDER BY CREATED DESC, NAME, ATTR_NUMBER;

Find SQL_ID for currently defined SQL Profiles

-- Find the SQL_ID and SQL_TEXT associated with a SQL Profile
-- Data must exist in DBA_HIST_* tables. Query information is located via the Matching Signature of the SQL Profile. 
WITH sqlstat as (select /*+ MATERIALIZE */ DISTINCT sql_id, force_matching_signature from dba_hist_sqlstat),
    sqltext as (select /*+ MATERIALIZE */ DISTINCT DBMS_LOB.SUBSTR(sql_text,4000)as SQL_TEXT, sql_id from dba_hist_sqltext)
SELECT prof.name,
       prof.signature, 
       sqlstat.sql_id,
       sqltext.sql_text
FROM dba_sql_profiles prof
JOIN sqlstat ON prof.signature = sqlstat.force_matching_signature
JOIN sqltext ON sqlstat.sql_id = sqltext.sql_id
--WHERE prof.name = 'SYS_SQLPROF_nnnnnnnn'
WHERE SQLTEXT.SQL_TEXT NOT LIKE '%SQL Analyze%'
ORDER BY SIGNATURE, SQL_ID;