Lutz Haake | Freelance Oracle Consultant

This is how to display the exact kind of hints included in a SQL patch or SQL profile.

select 
extractvalue(value(a), '.') hints
from SYS.SQLOBJ$ sqlo,
SYS.SQLOBJ$DATA sqld 
, table(xmlsequence(extract(xmltype(sqld.comp_data),'/outline_data/hint'))) a
where sqlo.name = 'SYS_SQLPROF_013c3de6699a0000'
and sqlo.SIGNATURE = sqld.SIGNATURE;

The output will look like this:

OPT_ESTIMATE(@"SEL$15", TABLE, "TS"@"SEL$15", SCALE_ROWS=4.545454545)
OPT_ESTIMATE(@"SEL$15", NLJ_INDEX_FILTER, "FI"@"SEL$15", ("F"@"SEL$15", "TS"@"SEL$15"), "I_FILE2", SCALE_ROWS=15)
OPT_ESTIMATE(@"SEL$15", NLJ_INDEX_FILTER, "FI"@"SEL$15", ("F"@"SEL$15"), "I_FILE2", SCALE_ROWS=15)
...

This is the exact list of hints which will be appliied to the SQL when the SQL profile is used. Undocu­mented hints like OPT_ESTIMATE are used internally, in this case reflecting the cardi­na­lities sampled during execution of the SQL tuning advisor.

To get the type of SQL object, use

select name, signature, decode(obj_type,1,'Profile',2,'Baseline',3,'Patch') type
from SYS.SQLOBJ$
where name = 'SYS_SQLPROF_013c3de6699a0000'
;

To display all of the patches, profiles and baselines present on your database, use

select o.name, o.signature, decode(o.obj_type,1,'Profile',2,'Baseline',3,'Patch', 'other') type
from SYS.SQLOBJ$ o;