load sql plan baseline from cursor cache and making sql plan baseline of query with hints

It is possible to load plans for statements directly from the cursor cache into the SQL Management Base. By applying a filter - on the module name, the schema, or the SQL_ID - you can identify the SQL statement or set of SQL statement you wish to capture.

The plans can be loaded using the PL/SQL procedure DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE or through Oracle Enterprise Manager. The
next time these statements are executed their SQL plan baselines will be used. Loading plans directly from the cursor cache can be extremely useful if application SQL has beentuned by hand using hints. Since it is unlikely the application SQL can be changed to include the hint, by capturing the tuned execution plan as a SQL plan baseline you can ensure that the application SQL will use that plan in the future. By using the simple steps below you can use SPM to capture the hinted execution plan and associate it with the non-hinted SQL statement.You begin by capturing a SQL plan baseline for the non-hinted SQL statement.

1. In a SQL*Plus session run the non-hinted SQL statement so we can begin the SQL plan
baseline capture

SQL> SELECT prod_name, SUM(amount_sold)
FROM Sales s, Products p
WHERE s.prod_id=p.prod_id
AND prod_category = :ctgy
GROUP BY prod_name;

2. Then find the SQL_ID for the statement in the V$SQL view.

SQL> SELECT sql_id, sql_fulltext
FROM V$SQL
WHERE sql_text LIKE '%SELECT prod_name, SUM(%';
SQL_ID SQL_FULLTEXT
------------- ---------------------------------------
74hnd835n81yv select SQL_ID, SQL_FULLTEXT from v$SQL
chj6q8z7ykbyy SELECT PROD_NAME, SUM(AMOUNT_SOLD)

3. Using the SQL_ID create a SQL plan baseline for the statement.

SQL> variable cnt number;
SQL> EXECUTE :cnt :=DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE(
sql_id=>'chj6q8z7ykbyy');

4. The plan that was captured is the sub-optimal plan and it will need to be disabled. The
SQL_HANDLE & PLAN_NAME are required to disable the plan. These can found by
looking in DBA_SQL_PLAN_BASELINE view.

SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM
dba_sql_plan_baselines;

SQL_HANDLE SQL_TEXT PLAN_NAME ENABLE
------------------------ ---------------------- ----------------------- ------
SYS_SQL_bf5c9b08f72bde3e SELECTPROD_NAME,SUM SQL_PLAN_byr4v13vkrrjy42949306 Y

5. Using DBMS_SPM.ALTER_SQL_PLAN_BASELINE disable the bad plan

SQL> variable cnt number;
SQL> exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
SQL_HANDLE => 'SYS_SQL_bf5c9b08f72bde3e',
PLAN_NAME
=> 'SQL_PLAN_byr4v13vkrrjy42949306',
ATTRIBUTE_NAME => 'enabled',
ATTRIBUTE_VALUE => 'NO');
SQL> SELECT sql_handle, sql_text, plan_name, enabled
FROM dba_sql_plan_baselines;
SQL_HANDLE SQL_TEXT PLAN_NAME ENABLE
------------------------ ---------------------- ----------------------- ------
SYS_SQL_bf5c9b08f72bde3e SELECTPROD_NAME,SUM SQL_PLAN_byr4v13vkrrjy42949306 N

6. Now you need to modify the SQL statement using the necessary hints & execute the
modified statement.

SQL> SELECT /*+ INDEX(p) */ prod_name, SUM(amount_sold)
FROM Sales s, Products p
WHERE s.prod_id=p.prod_id
AND prod_category = :ctgy
GROUP BY prod_name;

7. Find the SQL_ID and PLAN_HASH_VALUE for the hinted SQL statement in the V$SQL
view.

SQL> SELECT sql_id, plan_hash_value, fulltext
FROM V$SQL
WHERE sql_text LIKE '%SELECT /*+ INDEX(p) */
prod_na%';
SQL_ID PLAN_HASH_VALUE SQL_FULLTEXT
------------- --------------- ---------------------------
9t5v8swp79svs 3262214722 select SQL_ID, SQL_FULLTEXT
djkqjd0kvgmb5 3074207202 SELECT /*+ INDEX(p) */

8. Using the SQL_ID and PLAN_HASH_VALUE for the modified plan, create a new
accepted plan for original SQL statement by associating the modified plan to the
original statement's SQL_HANDLE.

exec :cnt:=dbms_spm.load_plans_from_cursor_cache(
sql_id => 'djkqjd0kvgmb5',
plan_hash_value => 3074207202,
sql_handle => 'SYS_SQL_bf5c9b08f72bde3e‘);