SQL Plan Management – Adding a desired plan

I have just started looking into SQL Plan Management, so the next few posts will probably be focused around it. As a quick background, SQL Plan Management lets you “lock in” a plan for Oracle to use for a specific SQL statement. This way when stats change, or data is added, Oracle will continue to use the same plan as before keeping consistency.

The issue I had today is that we had a poorly performing query that had an accepted plan that I didn’t like. The query was part of application code and hints couldn’t be easily added. My hope was to use hints to create the plan that I wanted, and then apply that plan to the statement and mark it as accepted. I would then disable the existing plan and Oracle would start using the new plan for the poorly performing query. Seems easy enough.

However, adding the hints made the query different, so Oracle didn’t add my new plan to the old query. It stored this new query with the new plan that I wanted, and left the old query alone. So this didn’t work as I wanted. We were able to get the plan I wanted by creating extended statistics on correlated columns (but that is for a different post). I knew that there had to be a way to apply the plan from one query to another though, so I kept researching. I am glad I did, because it is extremely easy to do. Following is an example from an Oracle white paper:

DECLARE
   v_cnt PLS_INTEGER;
BEGIN
    v_cnt := dbms_spm.load_plans_from_cursor_cache(
                 sql_id => 'djkqjd0kvgmb5',  -- new SQL
                 plan_hash_value => 3074207202,  -- new Plan
                 sql_handle => 'SYS_SQL_bf5c9b08f72bde3e'  -- Poorly Performing SQL handle
             );
END;

This gets the plan from the cursor cache for the new query, and adds it to the Baseline of the poor performing query. The SQL_ID and PLAN_HASH_VALUE of the new hinted query can be found from V$SQL. The SQL_HANDLE can be found by looking at DBA_SQL_PLAN_BASELINES.

Once the plan is added to the Baseline, it can be accpeted, and the old plan can be rejected.