Step-by-Step Guide to Implement a Better Execution Plan Using SQL Plan Baselines

Jul 13, 20242 min read

Updated: Jul 16, 2024

To address the issue of a dynamically changing SQL_ID resulting in a poor execution plan and to implement a better execution plan found in the SQL Tuning Advisor, you can use SQL Plan Management (SPM) in Oracle. The steps involve capturing the good execution plan as a SQL Plan Baseline and then forcing Oracle to use this baseline for future executions of the query, regardless of the changing SQL_ID.

1. Identify the Good Execution Plan

First, identify the SQL_ID and the plan hash value of the good execution plan found in the SQL Tuning Advisor.

sql

SELECT sql_id, plan_hash_value FROM dba_advisor_findings WHERE task_name = 'YOUR_TUNING_TASK_NAME';

2. Load the Good Execution Plan into SQL Plan Baselines

Use the DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE procedure to load the good execution plan into the SQL Plan Baseline.

sql

DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => 'GOOD_SQL_ID', plan_hash_value => 'GOOD_PLAN_HASH_VALUE' ); END; /

Replace GOOD_SQL_ID and GOOD_PLAN_HASH_VALUE with the appropriate values from the previous step.

3. Force Oracle to Use the SQL Plan Baseline

Ensure that Oracle uses the SQL Plan Baseline for future executions of the query by enabling the use of SQL Plan Baselines.

sql

ALTER SESSION SET optimizer_use_sql_plan_baselines = true;

You can also enable this at the system level if needed:

sql

ALTER SYSTEM SET optimizer_use_sql_plan_baselines = true;

4. Verify the SQL Plan Baseline

Check that the SQL Plan Baseline has been created and is being used.

sql

SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines WHERE sql_text LIKE '%your_query_text%';

Replace your_query_text with a unique part of your SQL query.

5. Attach the Plan to the Dynamic Query

Since your query changes dynamically, use a SQL Profile or SQL Patch to enforce the good execution plan across different SQL_IDs.

Create a SQL Profile

sql

BEGIN DBMS_SQLTUNE.ACCEPT_SQL_PROFILE( task_name => 'YOUR_TUNING_TASK_NAME', name => 'YOUR_SQL_PROFILE_NAME' ); END; /

Apply SQL Profile to Dynamic SQL

Ensure that the SQL Profile is applied to the query template, so it affects all dynamically generated versions of the query.

sql

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE( name => 'YOUR_SQL_PROFILE_NAME', attribute_name => 'FORCE_MATCH', value => 'YES' );

Replace YOUR_TUNING_TASK_NAME and YOUR_SQL_PROFILE_NAME with the appropriate values.

Conclusion

By using SQL Plan Baselines and SQL Profiles, you can ensure that the good execution plan is used for your query, even as its SQL_ID changes dynamically. This will improve the performance of your query and ensure consistent execution plans.