How to use a SQL Plan Baseline or a SQL Patch to add Optimizer hints

In a recent chat with Connor McDonald, we discussed if it is realistic to have applications that don’t contain Optimizer hints. Ideally, the answer to this question is “yes”, you don’t need hints if you have a well-written application and you have supplied a representative set of statistics and all the possible constraint information (NOT NULL, Primary keys, Foreign Keys, etc.) to the Optimizer.

But in reality even with all of this in place, there can be cases where something goes wrong with the execution plan for a critical SQL statement and you get called in to fix.

During our chat, Connor used a very apt analogy to describe this situation. He said it was like having a patient arrive in the emergency room, who is bleeding profusely. Your first priority is to stop the patient from bleeding by slapping a band-aid on the wound.

The same is true for our poorly performing SQL statement. Our initial response is to add an optimizer hint to get the SQL statement’s execution plan back to a reasonable response time or acceptable performance.

But once a patient has been stabilized in the emergency room, medical professionals normally take that patient into surgery to make a permanent fix or at the very least stitch up the wound properly.

We need to make sure we do the same thing for our SQL statements.

Rather than leaving a band-aid in the application code in the form of an optimizer hint, we should either fix the root cause or at the very least, make a permanent fix that can be easily traced and ideally can evolve over time.

That’s why you often hear me say, “if you can hint it, you can baseline or patch it”.

What do I mean by that?

I mean we should capture the hinted plan as a SQL plan baseline or at the very least insert the hints via a SQL Patch so that we know that this statement is patched (the use of a SQL patch is visible in the note section of the plan).

By using one of these techniques, we have a fast and easy method to correct the problem without requiring an application code change and ideally the fix can either evolve or be easily removed over time.

Adding a hint via a SQL Plan Baseline

-- Setup the test case 
DROP TABLE t purge;
 
TABLE dropped.
 
CREATE TABLE t(n NOT NULL) 
AS 
SELECT object_id 
FROM   all_objects;
 
TABLE created.
 
CREATE INDEX ind_t_n1 ON t(n);
 
INDEX created.
 
-- Check the default plan
 
SELECT * 
FROM   t 
WHERE  n > 0;
 
        N
----------
        18
        32
         :
     74921
 
68822 ROWS selected.
 
 
 SELECT * FROM TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  fgumtf1strwxa, child NUMBER 0
-------------------------------------
SELECT * FROM t WHERE n > 0
 
Plan hash VALUE: 2498539100
--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    42 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    | 68822 |   336K|    42  (29)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
   1 - FILTER("N">0)
 
-- The default plan is a full tables scan But we wanted 
-- an index range scan instead of the full tables scan
-- In order to get the plan we want we need to start by
-- creating a SQL plan baselines for original non-hinted SQL
-- statement and we will need the SQL_ID for that.
 
 SELECT sql_id, sql_fulltext
 FROM   v$sql
 WHERE  sql_text LIKE 'SELECT * FROM t WHERE %';
 
SQL_ID        SQL_FULLTEXT
------------- -------------------------------
fgumtf1strwxa SELECT * FROM t WHERE n > 0
 
 
 DECLARE
    cnt NUMBER;
 BEGIN
    cnt := sys.dbms_spm.load_plans_from_cursor_cache(sql_id=>'fgumtf1strwxa');
 END;
 /
 
PL/SQL PROCEDURE successfully completed.
 
-- Quickly check in dba_sql_plan_baseline to confirm the baseline exists
 
 
 SELECT b.sql_handle, b.sql_text, b.plan_name, b.enabled
 FROM   dba_sql_plan_baselines b, v$sql s
 WHERE  s.sql_id='fgumtf1strwxa'
 AND    s.exact_matching_signature = b.signature;
 
SQL_HANDLE           SQL_TEXT                       PLAN_NAME                      ENA
-------------------- ------------------------------ ------------------------------ ---
SQL_79c1d14a660634eb SELECT * FROM t WHERE n > 0    SQL_PLAN_7mhfj99m0cd7b94ecae5c YES
 
 
 -- The full table scan plan is not the plan we want, so let's disable this plan
 
DECLARE
  cnt NUMBER;
BEGIN
  cnt := sys.dbms_spm.alter_sql_plan_baseline(
               sql_handle=>'SQL_79c1d14a660634eb', 
               plan_name=>'SQL_PLAN_7mhfj99m0cd7b94ecae5c', 
               attribute_name=>'enabled', attribute_value=>'NO');
END;
/
 
PL/SQL PROCEDURE successfully completed.
 
 SELECT b.sql_handle, b.sql_text, b.plan_name, b.enabled
 FROM   dba_sql_plan_baselines b, v$sql s
 WHERE  s.sql_id='fgumtf1strwxa'
 AND    s.exact_matching_signature = b.signature;
 
SQL_HANDLE           SQL_TEXT                       PLAN_NAME                      ENA
-------------------- ------------------------------ ------------------------------ ---
SQL_79c1d14a660634eb SELECT * FROM t WHERE n > 0    SQL_PLAN_7mhfj99m0cd7b94ecae5c NO
 
 
 -- At this point even though the full table scan plan is disabled it's still going to be used
 -- because we haven't given the optimizer an alternative yet
 
 -- Now we need to run the original SQL text with an INDEX hint to force the plan we want
 
 SELECT /*+ INDEX(t) */ * 
FROM    t 
WHERE   n > 0;
 
         N
----------
         2
         3
         4
         5
         :
     75047
 
68822 ROWS selected.
 
 
 SELECT * FROM TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID  7n3uxkxg626tj, child NUMBER 0
-------------------------------------
SELECT /*+ INDEX(t) */ * FROM t WHERE n > 0
 
Plan hash VALUE: 3190934474
-----------------------------------------------------------------------------
| Id  | Operation        | Name     | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |       |       |   165 (100)|          |
|*  1 |  INDEX RANGE SCAN| IND_T_N1 | 68822 |   336K|   165   (8)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
   1 - access("N">0)
 
 
-- Great, we now have the plan we want but we don't want a hint in the SQL
-- We need to find the SQL_ID & PLAN_HASH_VALUE for hinted SQL stmt in V$SQL
 
SELECT sql_id, plan_hash_value, sql_fulltext
FROM   v$sql
WHERE  sql_text LIKE 'SELECT /*+ INDEX(t) */ * FROM t WHERE %';
 
SQL_ID        PLAN_HASH_VALUE SQL_FULLTEXT
------------- --------------- -----------------------------------------------
7n3uxkxg626tj      3190934474 SELECT /*+ INDEX(t) */ * FROM t WHERE n > 0
 
-- Now let's add the hinted plan to the non-hinted SQL stmts SQL plan baseline
-- Using the SQL_ID and PLAN_HASH_VALUE of the hinted SQL stmt we can add
-- the hinted plan to the SQL plan baseline of the non-hinted stmt using its
-- SQL_HANDLE
 
DECLARE
 cnt NUMBER;
BEGIN
 cnt := sys.dbms_spm.load_plans_from_cursor_cache(
               sql_id=>'7n3uxkxg626tj', 
               plan_hash_value=>'3190934474', 
               sql_handle=>'SQL_79c1d14a660634eb');
END;
/
 
PL/SQL PROCEDURE successfully completed.
 
-- if we check DBA_SQL_PLAN_BASELINES we now see two plans for our non-hinted 
-- SQL stmt, the full table scan, which is disabled and the new hinted plan
 
 SELECT b.sql_handle, b.sql_text, b.plan_name, b.enabled
  2  FROM   dba_sql_plan_baselines b, v$sql s
  3  WHERE  s.sql_id='fgumtf1strwxa'
  4  AND    s.exact_matching_signature = b.signature;
 
SQL_HANDLE           SQL_TEXT                       PLAN_NAME                      ENA
-------------------- ------------------------------ ------------------------------ ---
SQL_79c1d14a660634eb SELECT * FROM t WHERE n > 0    SQL_PLAN_7mhfj99m0cd7b94ecae5c NO
SQL_79c1d14a660634eb SELECT * FROM t WHERE n > 0    SQL_PLAN_7mhfj99m0cd7bbe31cbca YES
 
 
 -- Lets now check the index plan is actually used
 
EXPLAIN PLAN FOR
SELECT *
FROM   t
WHERE  n > 0;
 
Explained.
 
 SELECT * FROM TABLE(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash VALUE: 3190934474
-----------------------------------------------------------------------------
| Id  | Operation        | Name     | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          | 68822 |   336K|   165   (8)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_T_N1 | 68822 |   336K|   165   (8)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
   1 - access("N">0)
Note
-----
   - SQL plan baseline "SQL_PLAN_7mhfj99m0cd7bbe31cbca" used FOR this statement
 
-- So, there you have it. The SQL plan baseline is used and it is using the ENABLED
-- index range scan plan. 
-- Over time if a new plan is found for this statement, it will be added to the baseline
-- and it can be adopted if it proves to be better than the existing hinted plan.

Adding a hint via a SQL Patch

An alternative approach to adding a SQL plan baseline would be to add a hint via a SQL Patch. A SQL patch is a SQL manageability object that can be generated by the SQL Repair Advisor, in order to circumvent a plan which causes a failure.  In essence, a SQL patch tells the optimizer to change the plan in some way or other, so that the failure does not occur.

-- Setup the test case 
 
DROP TABLE t purge;
 
TABLE dropped.
 
CREATE TABLE t(n NOT NULL) 
AS 
SELECT object_id 
FROM   all_objects;
 
TABLE created.
 
CREATE INDEX ind_t_n1 ON t(n);
 
INDEX created.
 
-- Check the default plan
 
EXPLAIN PLAN FOR
SELECT * 
FROM   t 
WHERE  n > 0;
 
Explained.
 
SELECT * FROM TABLE(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
 
-----------------------------------------------------------------------
| Id | Operation        | Name | ROWS | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT  |      | 70187 | 891K|     42 (29) | 00:00:01 |
|* 1 | TABLE ACCESS FULL|   T  | 70187 | 891K|     42 (29) | 00:00:01 |
-----------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
1 - FILTER("N">0)
 
-- But we wanted an index range scan instead of the full tables scan
-- Let's create a SQL Patch containing our index hint to force the plan we want
 
DECLARE
   patch_name varchar2(100);
BEGIN
   patch_name := sys.dbms_sqldiag.create_sql_patch(
                 sql_text=>'select * from t where n > 0', 
                 hint_text=>'INDEX(@"SEL$1" "T")', 
                 name=>'TEST_PATCH');
END;
/
 
PL/SQL PROCEDURE successfully completed.
 
-- Now that the SQL Patch exists let's check the execution plan again to see if 
-- our index hint is being used and the plan has changed
 
EXPLAIN PLAN FOR 
SELECT * 
FROM   t 
WHERE  n > 0;
 
Explained.
 
SELECT * FROM TABLE(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash VALUE: 3190934474
 
---------------------------------------------------------------------------
| Id | Operation       | Name     |  ROWS | Bytes | Cost (%CPU)| TIME     |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |          | 70187 |  891K |    166 (8) | 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_T_N1 | 70187 |  891K |    166 (8) | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
1 - access("N">0)
 
Note
-----
- SQL patch "TEST_PATCH" used FOR this statement
 
-- So now we have the plan we want the note section under the plan shows 
-- we are using a SQL Patch.

So now you have two different methods for getting the plan you want without adding hints directly into the application.

More information on using SQL Plan Management and SQL Patches can be found on the official Optimizer blog maintained by Nigel Bayliss the Optimizer Product Manager.

18 thoughts on “How to use a SQL Plan Baseline or a SQL Patch to add Optimizer hints”

  1. Maria,

    thanks for the details post along with test cases.

    question is when you tell this in ” hint_text=>’INDEX(@”SEL$1″ “T”)’ ” in sqlpatch – how come you know the value supplied for the hint_text parameter? from where did you get/derive that?

    what if the table(T) got multiple indexes say i1,i2,i3 and i want the sql to use index i2 – then what value should be passed for the hint_text parameter

    Reply

    1. Rajesh,

      If you want to use a specific index you would simply specify that in the hint itself.

      The hint syntax is the same as you would use in the SQL statement itself.

      For example, the index hint is /*+ INDEX (@queryblock table index)*/

      So, the create patch command to force index i2 would be:

      DECLARE
      patch_name varchar2(100);
      BEGIN
      patch_name := sys.dbms_sqldiag.create_sql_patch(
      sql_text=>’select * from t where n > 0′,
      hint_text=>’INDEX(@”SEL$1″ “T” “I2”)’,
      name=>’TEST_PATCH’);
      END;
      /

      if you don’t know the query block name, you can get it from the OUTLINE for the statement using DBMS_XPLAN.DISPLAY or DBMS_XPLAIN.DISPLAY_CURSOR functions with the format parameter set to ‘+outline’.

      Thanks,
      Maria

      Reply

  2. Hi Maria,

    Thanks for the good article. By the way, if DBAs have to work on more complicated SQLs that may run several hours in extreme case due to inefficiently written application codes that cannot be modified right away, then I think we may not get the output from DBMS_XPLAN.DISPLAY_CURSOR due to excessively long SQL run time, and also running the SQL with EXPLAIN PLAN FOR’ may not be useful as it does not really run SQL or has all the information necessary to give an accurate plan and it also has NO knowledge of what the BIND VARIABLE data will look like.

    In this kind of real situation, what can be the best way to apply SQL profile, SQL baseline or SQL patch using the right index with query block names?

    I think the SQL status can be monitored on SQL Monitor or Toad Session Monitor, but sometimes it’s hard to know what should be checked for fix on them.
    If you already have some articles that shows detail steps and tips please let me know.

    Thanks!!

    Thanks,
    Chris

    Reply

    1. Hi Chris,

      Remember, even if it’s a long running query, once it starts executing the execution plan will be available in the cursor cache and therefore can be viewed by supplying the SQL_ID and CHILD CURSOR NUMBER to the DBMS_XPLAIN.DIPLAY function. So, it will be possible to see the plan and the Query block names and therefore create a SQL PATCH.

      A real-time SQL Monitor report will allow you to monitor the execution of SQL statement and see exactly which operation is currently executing in that plan. However, a SQL Monitor report will not display the Query Block names. So you will need some other mechanism like DBMS_XPLAN or SQL Developer to see this information in the plan.

      I’ll publish a blog post on this tomorrow and share more details.

      Thanks,
      Maria

      Reply

      1. Hi Maria,

        An application SQL query that has been running fast both in the PROD and the DEV databases started showing slowness in the DEV database when there has been no code change or anything. Now, the same SQL in the DEV is 10 times slower with worse SQL execution plan.
        I’ve applied a SQL profile but it just gives me about 50% improvement in response time.

        I see the PLAN_HASH_VALUE of the SQL in PROD does not exist the DEV for the same SQL_ID.
        In this case, pleas let me know if there is a way to let the SQL in the DEV use the PLAN_HASH_VALUE (that does not exist in the DEV) of the PROD so that it has the same plan as the PROD.

        Thanks,
        Chris

        Reply

        1. Hi Chris,

          Is it possible to capture a SQL Plan Baseline for the SQL statement is PROD and then export that baseline and import it into DEV?

          Hopefully, this will allow you to get the same plan on both PROD and DEV.

          Thanks,
          Maria

          Reply

          1. Hi Maria,

            Right, we can load the SQL baseline of the SQL using “dbms_spm.load_plans_from_cursor_cache” in the PROD and import into the lower environment. I forgot this.

            Thanks,
            Chris

  3. Hi Maria ,
    This is a good feature to add to optimizer is this available in RDBMS 18c ? I am thinking it should.
    Quick question: In ERP world for e.g as you know we have complex SQL Text which has sub query , in-line views etc. How can apply such repairs using sqlpatch with out making much code changes ?

    But concept and idea is good we need to adopt it to real world problem.
    Thanks ,
    Arvind-

    Reply

    1. Hi Arvind,

      This approach of adding hints via a SQL Patches has actually been available from Oracle Database 12c Release 2, using the public API DBMS_SQLDIAG.CREATE_SQL_PATCH. It’s also possible to do it in 11g using an undocumented procedure called DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH.

      Regarding how you can prepare your application to make it easier to apply SQL Patches in the future, the best thing you can do is name your query blocks using the QB_NAME hint. This is a good way to document your code and it will make it a lot easier to use a SQL PATCH to add a specific hint, to a particular query block in the future.

      Here is an example of using the QB_NAME and then use the specified Query Block name in a SQL Patch.

      SELECT e.ename, e.deptno
      FROM emp e
      WHERE e.deptno in (SELECT /*+QB_NAME(MY_DEPT_SQ) */ d.deptno
      FROM dept d
      WHERE d.loc=’DALLAS’);

      DECLARE
      patch_name varchar2(100);
      BEGIN
      patch_name := sys.dbms_sqldiag.create_sql_patch(
      sql_id=>’bah0gcbrvm1gk’,
      hint_text=>’INDEX(@”MY_DEPT_SQ” “D” “DEPT_LOC_IDX”)’,
      name=>’TEST_MYQB_PATCH2′);
      END;
      /

      Thanks,
      Maria

      Reply

  4. hi Maria,

    Thanks for the article. Just want to know any spfile parameter needs to set other than default values in order to get query block outline while using DBMS_XPLAN.DISPLAY_CURSOR for implementing sql patch fix

    Reply

    1. Hi Sivakrishna,

      No there is no specific initialization parameter setting required to view QUERY BLOCK names in an execution plan.

      Perhaps you are thinking about the parameter ‘statistics_level’, which needs to be set to ‘ALL’, at session or system level if you want the execution statistics to appear in the execution plan of a SQL statement even when you don’t add the ‘gather_plan_statistics’ hint.

      There is no such parameter for QUERY BLOCK names.

      Thanks,

      Reply

  5. Hi Maria,
    I have tried to get a particular sql to use sql plan baseline, but it never seems to use the plan contained in the baseline and always uses another one that isn’t contained in the baseline. I’ve checked the sql_plan_baseline column in v$sql and indeed the sqlid is not using the baseline as it is blank. I’m not sure what else to check…

    Thanks

    Reply

    1. I’ve tried everything, the optimizer will not use the sql plan baseline.

      The plan is there when I query the dba_sql_plan_baselines and enable is YES, fixed is YES, but will not work.

      SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED REPRODUCED
      —————————— ——————————— ——– ——– —— ———-
      SQL_f2a23d2514aa7ded SQL_PLAN_g58jx4naanzgd828af0c2 YES YES NO YES
      1 row selected.

      select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>’SQL_f2a23d2514aa7ded’,format=>’basic’));

      PLAN_TABLE_OUTPUT
      ——————————————————————————–

      ——————————————————————————–
      SQL handle: SQL_f2a23d2514aa7ded
      SQL text: select * from testspm where ret_id = 5001721
      ——————————————————————————–

      ——————————————————————————–
      Plan name: SQL_PLAN_g58jx4naanzgd828af0c2 Plan id: 2190143682
      Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD-FROM-CURSO
      R-CACHE

      Plan rows: From dictionary
      ——————————————————————————–

      Plan hash value: 2853028963

      ————————————-
      | Id | Operation | Name |
      ————————————-
      | 0 | SELECT STATEMENT | |
      | 1 | TABLE ACCESS FULL| TESTSPM |

      PLAN_TABLE_OUTPUT
      ——————————————————————————–
      ————————————-

      20 rows selected.

      select * from testspm where ret_id = 5001721;
      SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

      SQL_ID fzxu7p5vx5q0y, child number 0
      ————————————-
      select * from testspm where ret_id = 5001721

      Plan hash value: 1971963228

      ——————————————————————————————
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ——————————————————————————————
      | 0 | SELECT STATEMENT | | | | 2 (100)| |
      | 1 | TABLE ACCESS BY INDEX ROWID| TESTSPM | 1 | 38 | 2 (0)| 00:00:01 |
      |* 2 | INDEX RANGE SCAN | RTN_ID_IDX | 1 | | 1 (0)| 00:00:01 |
      ——————————————————————————————

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

      2 – access(“RETURN_ID”=5001721)

      Note
      —–
      – dynamic statistics used: dynamic sampling (level=4)

      Question:
      Please any reason why this is so?
      Anything I need to do?

      Reply

  6. Thank you so much Maria.

    Correct me if I’m wrong, what you did here was generating an optimized plan and added it to the SQL baseline of an unoptimized one while keeping the original SQL text (I mean not to use the statement with a hint).

    Thanks,
    Ziad

    Reply

  7. Hi Maria,

    I have about 20 SQL profiles in a PROD database, and the STATUS column of them in the DBA_SQL_PROFILES show all ‘ENABLE’ when their referenced tables or views were dropped or altered by new columns added and/or existing columns removed.

    Would you please let me know why the STATUS column still shows ‘ENABLE’ when column description shows 3 different values such as ENABLED, DISABLED, and VOID.

    If the STATUS is not supposed to be updated by underlying objects’ structural changes then how we do know which SQL profiles are invalidated and so dropped.

    Thanks!

    Reply

  8. Maria,
    thanks for the detail post with test cases. your time and effort goes a long way in helping folks to better understand oracle kernel, sql optimizer and performance tuning, regards

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: