Load a SQL Plan with its Plan Hash Value into SQL Plan Baseline

 As a seasoned database administrator, I’m no stranger to the quirks and challenges of maintaining a high-performing database environment. Recently, I encountered a situation that many of you might find familiar: the need to load a specific SQL execution plan into a SQL Plan Baseline using its plan hash value. Let's dive into this topic and unravel the steps together, adding a sprinkle of fun along the way!The Mystery of the SQL Plan Hash Value 
Picture this: You’ve optimized a SQL query, and it’s running like a dream. But then, out of nowhere, the database decides to change its execution plan, and performance takes a nosedive. Sounds frustrating, right? Enter SQL Plan Baselines – your secret weapon to lock in that perfect execution plan.

Step 1: Identifying the Culprit
First things first, we need to identify the SQL_ID and the plan hash value of our beloved SQL statement. Think of it as being a detective, and these are the clues you need. Run this query to gather your evidence:

SELECT sql_id, plan_hash_value, sql_text
FROM v$sql
WHERE sql_text LIKE '%your SQL text%'

Replace %your SQL text% with a snippet of your SQL statement

Step 2: Displaying the Suspect’s Profile

Now that we have our SQL_ID and plan hash value, let’s put a face to the name. Use DBMS_XPLAN.DISPLAY_CURSOR to display the execution plan:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('your_sql_id', your_plan_hash_value));

Replace 'your_sql_id' and your_plan_hash_value with the actual values. Think of this as our SQL lineup – we’re making sure we’ve got the right suspect.

Step 3: Loading the Plan into the SQL Plan Baseline

Here’s where the magic happens. We’re going to load our suspect (the execution plan) into the SQL Plan Baseline:

BEGIN
  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'your_sql_id',
    plan_hash_value => your_plan_hash_value
  );
END;
/

Step 4: Verifying the Plan’s New Home
After loading the plan, it’s time to verify that our SQL Plan Baseline has been created successfully. Run this query to check:

SELECT sql_handle, plan_name, origin, enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%your SQL text%';

Replace %your SQL text% with a part of your SQL statement. It’s like checking the guest list to make sure our VIP (Very Important Plan) has arrived.😋

Step 5: Enabling the SQL Plan Baseline
If the SQL Plan Baseline isn’t automatically taking charge, we need to ensure it’s enabled and accepted:

BEGIN
  DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle => 'your_sql_handle',
    plan_name => 'your_plan_name',
    attribute_name => 'ENABLED',
    attribute_value => 'YES'
  );

  DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle => 'your_sql_handle',
    plan_name => 'your_plan_name',
    attribute_name => 'ACCEPTED',
    attribute_value => 'YES'
  );
END;
/

Replace 'your_sql_handle' and 'your_plan_name' with the values from the previous query. 

Step 6: Evolving the SQL Plan Baseline

Sometimes, plans need a little evolution to ensure they’re the best fit. Here’s how you can evolve your SQL Plan Baseline:

DECLARE
  report CLOB;
BEGIN
  report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => 'your_sql_handle'
  );
  DBMS_OUTPUT.PUT_LINE(report);
END;
/

Wrapping It Up

And there you have it! By using these steps, you can lock in that perfect SQL execution plan and keep your database performance on point. Troubleshooting and optimizing databases might sometimes feel like a roller-coaster ride, but with SQL Plan Baselines and a bit of detective work, you’re well on your way to mastering the art of database performance.

Until next time, happy querying! ✨