Hints from an SQL Profile

A SQL profile is a set of auxiliary information specific to a SQL statement. It is an object containing information that helps the query optimizer find an efficient execution plan for a specific SQL statement. It provides information about the execution environment, object statistics, and corrections related to the estimations performed by the query optimizer.

One of its main advantages is the ability to influence the query optimizer without modifying the SQL statement or the execution environment of the session executing it. In other words, it is transparent to the application connected to the database engine.

Conceptually, a SQL profile is to a SQL statement what statistics are to a table or
index. The database can use the auxiliary information to improve execution plans.


For more information on SQL Profiles check the Oracle Performance Tuning Guide.

If you want to see the hints produced by an SQL profile you can do it with the queries described in this post by Christian Antognini.

Update (30/08/2014):
(In this post by Tony Hasler, there is an even more elegant way to find the hints of an SQL Profile. It is based on querying the OTHER_XML column of tables such as DBA_SQLTUNE_PLANS, PLAN_TABLE/SYS.PLAN_TABLE$, V$SQL_PLAN, and DBA_HIST_SQL_PLAN and then simply using REGEXP_SUBSTR for extracting the outline hints from the OTHER_XML column.)

Here is an example:

The following is an SQL Profile generated for a specific sql id.

nkarag@DWHPRD> l
  1  select NAME, SIGNATURE, SQL_TEXT, CREATED, DESCRIPTION, STATUS, FORCE_MATCHING
  2* from dba_sql_profiles
nkarag@DWHPRD> /

NAME                                  SIGNATURE SQL_TEXT                                           CREATED                                                                     DESCRIPTION          STATUS   FORCE_MATCHING
------------------------- --------------------- -------------------------------------------------- --------------------------------------------------------------------------- -------------------- -------- ---------------
sqlprof_6rzycrm804xwt      12587407990969490608 SELECT SPOBJN, SPN, SPNAME, LAST_ANALYZED, PN, PNA 23-APR-13 03.31.24.000000 PM                                                sqlprof 6rzycrm804xw ENABLED  NO

The hints stored for this SQL Profile can be obtained like this

nkarag@DWHPRD> 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 = 'sqlprof_6rzycrm804xwt'
  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
  9  /

HINT
------------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS

We just had to specify the name of the sql profile ('sqlprof_6rzycrm804xwt').

Enjoy,
Oracle Learner.