fix_control
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...
NOTE: This doesn't work from the command line#!/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