Database Reference
In-Depth Information
Unfortunately, the hints associated with a SQL profile can't be displayed through a data dictionary view. In fact,
the only views providing information about SQL profiles,
dba_sql_profiles
and, in a 12.1 multitenant environment,
cdb_sql_profiles
, gives all information except for hints. If you want to know which hints are used for a SQL profile,
you have two possibilities. The first is to directly query internal data dictionary tables. The following queries show how
to do that for the SQL profile created by the
profile_all_rows.sql
script. Notice that two initialization parameter
hints (
all_rows
and
optimizer_features_enable
) are used. In addition, to instruct the query optimizer to ignore the
hints present in the SQL statement (in this case the
rule
hint), the
ignore_optim_embedded_hints
hint is used.
This query works in version 10.2:
•
SQL> SELECT attr_val
2 FROM sys.sqlprof$ p, sys.sqlprof$attr a
3 WHERE p.sp_name = 'all_rows'
4 AND p.signature = a.signature
5 AND p.category = a.category;
ATTR_VAL
----------------------------------
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE(default)
IGNORE_OPTIM_EMBEDDED_HINTS
•
This query works as of version 11.1:
SQL> SELECT extractValue(value(h),'.') AS hint
2 FROM sys.sqlobj$data od, sys.sqlobj$ so,
3 table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
4 WHERE so.name = 'all_rows'
5 AND so.signature = od.signature
6 AND so.category = od.category
7 AND so.obj_type = od.obj_type
8 AND so.plan_id = od.plan_id;
HINT
----------------------------------
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE(default)
IGNORE_OPTIM_EMBEDDED_HINTS
The second possibility is to move the SQL profile into a staging table, as described in the “Moving SQL Profiles”
section. Then, with a query like the following, you can get the hints from the staging table. Note that the unnesting
through the
table
function is performed because the hints are stored in a varray of
VARCHAR2
:
SQL> SELECT *
2 FROM table(SELECT attributes
3 FROM mystgtab
4 WHERE profile_name = 'opt_estimate');
