SQL Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 226

Step 4
This original query has a hint, and when the optimizer behavior changes due to improvements to the optimizer after
a database upgrade, the execution plan changes. The suboptimal plan will need to be disabled. The SQL_HANDLE and
PLAN_NAME required to disable the plan can be found from DBA_SQL_PLAN_BASELINES view:
SQL> SELECT SQL_HANDLE, PLAN_NAME, ENABLED FROM DBA_SQL_PLAN_BASELINES;
SQL_HANDLE PLAN_NAME ENA
------------------------------ ------------------------------ ---
SYS_SQL_26517e6bd220dc3c SQL_PLAN_2cnbydg921r1w9dcc863f YES
Step 5
Now that the PLAN_HANDLE and SQL_HANDLE have been obtained, it can be disabled using the ALTER_SQL_PLAN_
BASELINE procedure:
SQL> variable sqlid number;
SQL> EXEC :sqlid :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (SQL_HANDLE => 'SYS_SQL_26517e6bd220dc3c',PLAN_
NAME => 'SQL_PLAN_2cnbydg921r1w9dcc863f',ATTRIBUTE_NAME=>'enabled',ATTRIBUTE_VALUE => 'NO');
PL/SQL procedure successfully completed.
Step 6
Check if the plan has been disabled by querying from DBA_SQL_PLAN_BASELINES :
SQL> SELECT SQL_HANDLE, PLAN_NAME, ENABLED FROM DBA_SQL_PLAN_BASELINES;
SQL_HANDLE PLAN_NAME ENA
------------------------------ ------------------------------ ---
SYS_SQL_26517e6bd220dc3c SQL_PLAN_2cnbydg921r1w9dcc863f NO
Step 7
Now that the optimizer has improved, execute the query without the hint:
SELECT n_name,
r_name,
p_name,
s_name,
SUM(ps_supplycost)
FROM region,
nation,
supplier,
partsupp,
part
WHERE ps_partkey = p_partkey
AND ps_suppkey = s_suppkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey