Use DBMS_SQLTUNE.IMPORT_SQL_PROFILE to force a hint to a query

DBMS_SQLTUNE.IMPORT_SQL_PROFILE is actually the procedure used when you implement a SQL Tuning Advisor's recommendation involving a better execution plan. But the SQL Tuning Advisor may not recommend the execution plan you know is the correct one or may even not recommend a new execution plan. If you know the hint that will improve your query's performance, then you may want to use DBMS_SQLTUNE.IMPORT_SQL_PROFILE to force it.

Let's create a scenario. Firstly, create 2 tables and 1 index in each one.

sqlplus system

SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 15 20:32:10 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter password: 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE SYSTEM.OBJECTS AS SELECT * FROM DBA_OBJECTS;
Table created.

SQL> CREATE INDEX SYSTEM.OBJ_NAME ON SYSTEM.OBJECTS(OBJECT_NAME);
Index created.

SQL> CREATE TABLE SYSTEM.SEGMENTS AS SELECT * FROM DBA_SEGMENTS;
Table created.

SQL> CREATE INDEX SYSTEM.SEG_NAME ON SYSTEM.SEGMENTS(SEGMENT_NAME);
Index created.

We will use a query that joins those tables using the indexed columns.

SQL> SET AUTOT TRACEONLY
SQL> SET TIMING ON
SQL> SET LINESIZE 200
SQL> SELECT OBJECT_ID,
       OBJECT_NAME,
       SEGMENT_TYPE,
       TABLESPACE_NAME
  FROM SYSTEM.OBJECTS, SYSTEM.SEGMENTS
 WHERE OBJECT_NAME = SEGMENT_NAME AND SEGMENT_NAME LIKE 'DBA%';

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 98178177

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   895 |   130K|     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN                   |          |   895 |   130K|     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SEGMENTS |   784 | 54880 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SEG_NAME |   141 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| OBJECTS  |  4970 |   383K|     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | OBJ_NAME |   895 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_NAME"="SEGMENT_NAME")
   3 - access("SEGMENT_NAME" LIKE 'DBA%')
       filter("SEGMENT_NAME" LIKE 'DBA%')
   5 - access("OBJECT_NAME" LIKE 'DBA%')
       filter("OBJECT_NAME" LIKE 'DBA%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        892  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

Now, let's find our query's SQL_ID.

SQL> SELECT SQL_ID, SQL_TEXT
FROM V$SQLAREA
WHERE SQL_TEXT LIKE 'SELECT OBJECT_ID%';

SQL_ID	SQL_TEXT
gy6fj888vt27y	SELECT OBJECT_ID, OBJECT_NAME, SEGMENT_TYPE, TABLESPACE_NAME FROM SYSTEM.OBJECTS, SYSTEM.SEGMENTS WHERE OBJECT_NAME = SEGMENT_NAME AND SEGMENT_NAME LIKE 'DBA%'

Both indexes are used, but let's assume we want to force full table scans on both tables.

SQL> DECLARE
  2  SQL_FTEXT CLOB;
BEGIN
SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = 'gy6fj888vt27y';

DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
  SQL_TEXT => SQL_FTEXT,
  PROFILE => SQLPROF_ATTR('FULL(@"SEL$1" "OBJECTS"@"SEL$1") FULL(@"SEL$1" "SEGMENTS"@"SEL$1")'),
  NAME => 'PROFILE_gy6fj888vt27y',
  REPLACE => TRUE,
  FORCE_MATCH => TRUE
);
END;
/
PL/SQL procedure successfully completed.

Let's see the new execution plan.

Execution Plan
----------------------------------------------------------
Plan hash value: 1823853794 [New plan hash value]

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |   895 |   130K|   388   (2)| 00:00:05 |
|*  1 |  HASH JOIN         |          |   895 |   130K|   388   (2)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL| SEGMENTS |   784 | 54880 |    53   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| OBJECTS  |  4970 |   383K|   334   (1)| 00:00:05 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_NAME"="SEGMENT_NAME")
   2 - filter("SEGMENT_NAME" LIKE 'DBA%')
   3 - filter("OBJECT_NAME" LIKE 'DBA%')

Note
-----
   - SQL profile "PROFILE_gy6fj888vt27y" used for this statement [The SQL profile we created, is used]


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1419  consistent gets
        192  physical reads
          0  redo size
        892  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

You've may noticed the hint's format.
/*+ FULL(OBJECTS) FULL(SEGMENTS) */ is equal to 'FULL(@"SEL$1" "OBJECTS"@"SEL$1") FULL(@"SEL$1" "SEGMENTS"@"SEL$1")'.
Similarly, /*+ INDEX(OBJECTS OBJ_NAME) */ is equal to 'INDEX(@"SEL$1" "OBJECTS"@"SEL$1" "OBJ_NAME")'.