DBMS_OPTIM_BUNDLE

Enable fix for specifc SQL only

Use a Hint like this...

SELECT /*+ OPT_PARAM('_fix_control' '16391176:1') */ ...

Troubleshooting

Invalid fix_control setting

If you see lines like this in the alert.log...

Invalid fix_control setting 26664361:7 found in hint: sql_id=4jp340b34dnwk

Then...

The likely root cause is a faulty baseline, probably caused by use of the fix_control parameter in a previous version of the database where baselines were captured in an attempt to have plan stability after the upgrade.

To fix it...

• Run a SQLT XTRACT Report for the sql_id shown in the message. 

• It is possible that the sql_id does not match anything, in which case you should run the SQLT XTRACT Report at other times using the latest sql_id reported in the alert.log until you are lucky.

• Copy the zip file created by SQLT XTRACT to a computer with a web browser and unzip it.

• In the resultant directory there should be a file named something like sqlt_s42663_main.html; open this file.

• Use the menu at the top of the page to navigate to SQL Plan Baselines (you should see this under the "Plan Control" heading).

• Look at the Outline Data from SMB, there is likely to be a line similar to this...

      OPT_PARAM('_fix_control' '21509656:1 22159570:1 21802552:1 21971099:1 21099502:1 22518491:1 19475484:1

              22258300:1 22077191:1 22123025:1 20243268:1 16732417:1 26664361:7 18302923:1 18182018:1 18558952:1 20129763:1

              19563657:1 17973658:1 21833220:1 18876528:1 23197730:1 25476149:1 24010030:1 22090662:1')

Note the SQL Handle and the Plan Name associated with this Outline Data. fo example...

SQL handle: SQL_e0543d6b3c24b0b8

Plan name: SQL_PLAN_f0p1xdcy29c5scf6642d3

Drop the baseline...

SET SERVEROUTPUT ON

DECLARE

  l_plans_dropped  PLS_INTEGER;

BEGIN

  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (

    sql_handle => '&SQL_HANDLE',

    plan_name  => '&PLAN_NAME'); 

  DBMS_OUTPUT.put_line(l_plans_dropped);

END;

/

Alternate Fix...

Get the Outlines for all stored Baselines...

SET TRIMSPOOL ON

SET LINESIZE 250

SET PAGESIZE 0

SPOOL /tmp/outline.txt

SELECT t.* 

FROM ( SELECT DISTINCT plan_name

       FROM   dba_sql_plan_baselines ) pb,

     TABLE(dbms_xplan.display_sql_plan_baseline(PLAN_NAME => pb.plan_name, FORMAT => 'OUTLINE')) t;

SPOOL OFF

exit

Create a file called /u01/dba/spm/drop_sql_plan_baseline_by_plan_name.sql with the following content...

SET SERVEROUTPUT ON

SET ECHO OFF

SET FEEDBACK OFF

SET VERIFY OFF

DECLARE

  l_plans_dropped  PLS_INTEGER;

BEGIN

  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (plan_name  => '&1'); 

  DBMS_OUTPUT.put_line(l_plans_dropped);

END;

/

Create a file called /tmp/delnrl.sh with the following content...

#!/bin/sh

grep -E 'Plan name:|26664361:7|20243268:1' /tmp/outline.txt >/tmp/dropbase.tmp

sed '

/Plan/ {

        N

        /\n.*Plan/  D

}' /tmp/dropbase.tmp | sed '/^ /d' | awk '{ print $3 }' | sed 's/^/@/u01/dba/spm/drop_sql_plan_baseline_by_plan_name '

Run it...

sh /tmp/delnrl.sh >/tmp/dropbase.sql

sqlplus / as sysdba @/tmp/dropbase.sql

Cleanup...

rm /tmp/dropbase.sql

rm /tmp/dropbase.tmp

rm /tmp/outline.txt

Bibliography