How to hint SQL when you can’t change the code

Sooner or later you come across a poor performing statement that you just know you could fix…if only you could add a hint.  There’s one index it should use, a join order you prefer, or perhaps you are upgrading and would prefer that THIS statement not use the new optimizer mode.

But it’s a packaged application, and you can’t touch the code.

If your desired execution plan is in the library cache, or even in Active Workload Repository, you could build a baseline, or a SQL Profile – assuming you have the additional license.

If you don’t have both of those, an option that might help is using a SQL Patch.  This is a mechanism by which you can tell the optimizer to use a particular hint when a query comes along.  In 2012 the Oracle Optimizer group discussed it in two posts, here, and here.  If you’re unfamiliar with SQL Patches, you will benefit from reading those posts before continuing.

Adding a hint is not often recommended – they can come back to bite you long after they’re forgotten – but sometimes when your options are limited they can be the best thing to do.

This was the situation I was in a few months back with a particular query – the SQL patch was my last option.  The problem was manipulating the text.  I_create_patch doesn’t accept a sql_id, it takes the sql_text.  When you’re dealing with a very long SQL statement, that can be a hassle.

To get around it, I hacked together a small stored procedure that pulls the text from V$SQL and puts it in a variable.  Since this is something I would be using just once, I also hard coded the hint I wanted to apply.

CREATE OR REPLACE PROCEDURE make_patch (i_sql_id    IN VARCHAR2,
                                        i_patchname IN VARCHAR2)
/* Author:  Charlie Callaway */
AS
   l_sql_text            CLOB;
BEGIN
   SELECT sql_fulltext INTO l_sql_text FROM sys.v_$sql WHERE sql_id = i_sql_id;
   SYS.DBMS_SQLDIAG_INTERNAL.i_create_patch(l_sql_text,'OPTIMIZER_FEATURES_ENABLE('||CHR(39)||'11.2.0.3'||CHR(39)||')',i_patchname);
END;
/

It worked, and all was well.

And then a few days later, I had an email conversation about it with a DBA I work with named Bill Pass.  He said “this would be better as an anonymous PL/SQL block so you don’t create any objects,” and then he wrote such a version.

Facepalm.

With Bill’s kind permission I’m sharing his code below.

/* Author:  Bill Pass */
DECLARE
AS
   i_sql_id   VARCHAR2(100)   := '2put627vqfjrh';
   i_hints    VARCHAR2(32000) := 'OPTIMIZER_FEATURES_ENABLE(''11.2.0.3'')';
   l_sql_text CLOB;
BEGIN
    BEGIN 
        SELECT sql_fulltext
          INTO l_sql_text
          FROM v$sql
         WHERE sql_id = i_sql_id;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            SELECT sql_fulltext
              INTO l_sql_text
              FROM dba_hist_sqltext
             WHERE sql_id = i_sql_id;
    END;
    DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH(
        sql_text    => l_sql_text,
        hint_text   => i_hints,
        name        => 'SQL_PATCH_' || i_sql_id,
        description => 'HINT INJECTION FOR SQL_ID = ' || i_sql_id,
        category    => 'DEFAULT',
        validate    => TRUE
    );
END;
/

A couple last notes about using SQL Patches.  To see if a sql_id is making use of a patch check either the SQL_PATCH column in V$SQL, or the Notes section of an execution plan.

If you need to drop a SQL Patch, use dbms_sqldiag.drop_sql_patch.

So if you find yourself needing to apply a SQL Patch in a hurry, hopefully this helps!

Until next time.