Dealing with hints in 3rd party apps Oracle 10g

Maria Colgan from the Oracle Optimizer Group wrote an excellent blog post explaining how to deal with applications that have embedded hints leading to poor execution plans. The very same day, coincidentally, one of my customers was having a problem with a SQL that started to perform badly after a new index was added. The problem was caused by the first_rows hint that made the optimizer choose the wrong index. 

Query without hint, uses the correct index I_062INVOICEACCOUNTREFNUMIDX

SQL> select * from table(Dbms_Xplan.display);
 
PLAN_TABLE_OUTPUT--------------------------------
Plan hash value: 3005292771
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                              |     1 |   712 |     6  (17)| 00:00:01 |
|   1 |  SORT ORDER BY               |                              |     1 |   712 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTINVOICEJOUR              |     1 |   712 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I_062INVOICEACCOUNTREFNUMIDX |     1 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT----------------------------------
Plan hash value: 2733576580
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |   712 | 51096   (1)| 00:10:14 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | CUSTINVOICEJOUR     |     1 |   712 | 51096   (1)| 00:10:14 |
|*  2 |   INDEX RANGE SCAN DESCENDING| I_062SALESIDDATEIDX |   338K|       |  1819   (1)| 00:00:22 |
----------------------------------------------------------------------------------------------------

In 11g we can use SQL Plan Management (SPM) to make Oracle choose the correct execution plan, as Maria Colgan described in her blog post. In 10g and 9i we must use stored outlines. With the help of stored outlines the database can lock one execution plan for a SQL. 

In order to generate an outline we must be able to run the query with the correct plan and the correct hash value. This means we must run the SQL as it is run from the application with the hint, and get the execution plan without the hint. 

The "magic" here is the hidden parameter _optimizer_ignore_hints. This way we can execute the SQL with the hint, generate the same plan as we do without the hint, and lock this with an outline.

I'm running this on an account with system privileges:

SQL> alter session set current_schema=username;

Session altered.

SQL> alter session set "_optimizer_ignore_hints" = true;

Session altered.

SQL> create outline custinvoicejour_firstrow on SELECT /*+ FIRST_ROWS */
a.custgroup, ...snip...

Outline created.

SQL> SELECT name, category, sql_text FROM dba_outlines;

NAME                           CATEGORY                       SQL_TEXT
------------------------------ ------------------------------ ---------------------------------------
CUSTINVOICEJOUR_FIRSTROW       DEFAULT                        SELECT /*+ first_rows */a .custgroup,...


SQL> SELECT name, category, used FROM dba_outlines;

NAME                           CATEGORY                       USED
------------------------------ ------------------------------ ------
CUSTINVOICEJOUR_FIRSTROW       DEFAULT                        UNUSED

To make the database follow the outline, the parameter use_stored_outlines must be set. When the outline is put in use, the status in dba_outlines will change. It might be a good idea to recreate the outline after it has been tested, so that we know the users are taking advantage of the plan in the outline.

SQL>  alter system set use_stored_outlines=DEFAULT ;

System altered.

SQL> SELECT name, category, used FROM dba_outlines

NAME                           CATEGORY                       USED
------------------------------ ------------------------------ ------
CUSTINVOICEJOUR_FIRSTROW       DEFAULT                        USED