Copy SQL Execution Plan from One Database to Another in Oracle 19c

Performance tuning is a critical responsibility for Oracle database administrators, ensuring that SQL queries run efficiently across various environments. This guide details how to copy an SQL execution plan from one Oracle 19c database to another, a practical solution when a query performs inconsistently across environments.

For example, if a query runs efficiently in a staging environment but poorly in production, transferring the execution plan can resolve performance issues without modifying the SQL code. Below are the steps to copying SQL execution plans.

Source Database Operations

Step 1: Identify the Plan Hash Value

To begin, identify the PLAN_HASH_VALUE of the SQL query in the source database where it performs well.

SELECT DISTINCT plan_hash_value 
FROM v$sql 
WHERE sql_id = 'abcd1234xyz';

Example output:

Performance validation:

• Query execution time in staging: ~0.5 seconds.
• Query execution time in production: ~3.2 seconds.

Step 2: Load the Plan into SQL Plan Management (SPM)

Load the execution plan into the SPM repository using the DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE procedure.

DECLARE
    ret binary_integer;
BEGIN
    ret := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
        sql_id          => 'abcd1234xyz',
        plan_hash_value => 3456789012,
        fixed           => 'YES',
        enabled         => 'YES'
    );
END;
/

Verify the loaded plan:

SELECT sql_handle, plan_name FROM dba_sql_plan_baselines;

Example output:

Step 3: Create a Staging Table

Create a table to store the plan for export.

BEGIN
    DBMS_SPM.CREATE_STGTAB_BASELINE(
        table_name      => 'STAGING_PLAN_TABLE',
        table_owner     => 'APPUSER',
        tablespace_name => 'USERS'
    );
END;
/

Step 4: Pack the Execution Plan

Pack the plan into the staging table.

DECLARE
    my_plans NUMBER;
BEGIN
    my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
        table_name  => 'STAGING_PLAN_TABLE',
        table_owner => 'APPUSER',
        plan_name   => 'SQL_PLAN_xyz09876abcd',
        sql_handle  => 'SQL_12345abcde67890'
    );
END;
/

Step 5: Export the Staging Table

Export the staging table using Oracle Data Pump:

expdp appuser/password@source_db \
    tables=APPUSER.STAGING_PLAN_TABLE \
    dumpfile=plan_export.dmp \
    logfile=plan_export.log

Transfer the plan_export.dmp file to the target database.

Target Database Operations

Step 6: Import the Staging Table

Import the table into the target database.

impdp appuser/password@target_db \
    tables=APPUSER.STAGING_PLAN_TABLE \
    dumpfile=plan_export.dmp \
    logfile=plan_import.log

Step 7: Unpack the Execution Plan

Unpack the execution plan into the SPM repository in the target database.

DECLARE
    plans_unpacked PLS_INTEGER;
BEGIN
    plans_unpacked := DBMS_SPM.UNPACK_STGTAB_BASELINE(
        table_name  => 'STAGING_PLAN_TABLE',
        table_owner => 'APPUSER'
    );
    DBMS_OUTPUT.PUT_LINE('Plans Unpacked: ' || plans_unpacked);
END;
/

Verify the unpacked plan:

SELECT sql_handle, plan_name, enabled, accepted, fixed 
FROM dba_sql_plan_baselines;

Step 8: Fix the Execution Plan in Oracle 19c

Ensure the SQL optimizer consistently uses the imported execution plan by marking it as FIXED. A fixed plan tells the optimizer to prioritize it over other plans for the same SQL query, ensuring stable and predictable performance.

Code for Fixing the Plan

DECLARE
    plans_altered PLS_INTEGER;
BEGIN
    plans_altered := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
        sql_handle      => 'SQL_12345abcde67890',
        plan_name       => 'SQL_PLAN_xyz09876abcd',
        attribute_name  => 'fixed',
        attribute_value => 'YES'
    );
    DBMS_OUTPUT.PUT_LINE('Plans Altered: ' || plans_altered);
END;
/

Explanation of Input Parameters

1. sql_handle

• Definition: A unique identifier for the SQL statement associated with the execution plan in the SPM repository.
• Example value: 'SQL_12345abcde67890'

This corresponds to the SQL query whose execution plan you imported.

• How to find it: Query the DBA_SQL_PLAN_BASELINES table to get the SQL_HANDLE:

SELECT sql_handle, sql_text
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%<your_query>%';

2. plan_name

• Definition: The unique identifier for the specific execution plan you want to fix.
• Example value: 'SQL_PLAN_xyz09876abcd'

This corresponds to the imported execution plan.

• How to find it: Query the DBA_SQL_PLAN_BASELINES table to get the PLAN_NAME:

SELECT plan_name, sql_handle, enabled, accepted, fixed
FROM dba_sql_plan_baselines
WHERE sql_handle = 'SQL_12345abcde67890';

3. attribute_name

• Definition: The attribute of the plan that you want to modify.
• Allowed values: 'fixed', 'enabled', 'accepted', etc.
• Example value: 'fixed'

In this context, it specifies that you want to modify the fixed status of the plan.

4. attribute_value

• Definition: The new value for the attribute being modified.
• Allowed values: 'YES', 'NO'.
• Example value: 'YES'

This marks the execution plan as fixed, prioritizing it over other plans for the same query.

Expected Output

When executed, the procedure updates the specified plan's fixed attribute and outputs the number of plans altered. For example:

Plans Altered: 1

Verification

To confirm the plan is fixed, run the following query:

SELECT sql_handle, plan_name, fixed
FROM dba_sql_plan_baselines
WHERE sql_handle = 'SQL_12345abcde67890';

Expected output:

When to Use Fixing Plans

Fixing a plan is particularly useful in scenarios like:

• Stabilizing query performance in production environments.
• Ensuring the optimizer does not deviate from a known efficient plan.
• Addressing inconsistent performance across environments.

By carefully fixing execution plans, you can maintain predictable query behavior while mitigating performance risks.

Step 9: Test the Query

Run the query in the target database to confirm the plan is applied:

SELECT DISTINCT plan_hash_value 
FROM v$sql 
WHERE sql_id = 'abcd1234xyz';

Example output:

Performance validation:

• Query execution time after plan transfer: ~0.5 seconds.
• Performance improvement: ~84.4% faster.

Conclusion

By following these steps, you can transfer SQL execution plans between Oracle 19c databases to address performance issues without altering SQL code. This method ensures consistent query behavior across environments, significantly improving performance.

Summary of Results