Copying the SQL explain plan to another database

Recently, I was reported a problem, consisting in the slow operation of the inquiry in the production database. As it turned out, the reason was the use of the wrong plan by the optimizer, which in effect led to a heavy load on the base and slowed work of the application. The same question is that it was running that in the test base it worked much faster despite the fact that both had a comparable data volume.

As the structure of both bases is identical especially in the context of objects participating in the query, I decided to copy the plan from the test environment to production environment, which was potentially to lead the optimizer to the right tracks and, as a result, fix the performance problem.

I used the procedure below thanks to which the failure was removed, which significantly enlarged the group of satisfied system users. 😉

And it was like this:

I started the copying procedure by verifying whether there is a question plan about SQL_ID on the source -based basis:

select hash_value,sql_id,plan_hash_value,timestamp,operation,optimizer,cost,parent_id
from v$sql_plan
where parent_id is null
and sql_id = '93c4e9rdek54n';


select plan_table_output from table(dbms_xplan.display_cursor('93c4e9rdek54n',0));

Then I made sure that Baseline exists in the same base. I conducted the search based on a unique fragment of the question (SQL_TEXT).

To ask, let’s assume that it was known for the Oracle training:

 SELECT EMPLOYEE_ID, FIRST_NAME FROM HR.EMPLOYEES%;

select sql_handle, sql_text, plan_name,PARSING_SCHEMA_NAME,enabled,accepted,
fixed,to_char(last_executed,'dd-mon-yy HH24:MI') 
last_executed from dba_sql_plan_baselines where upper(sql_text) like 'SELECT ID, NAME FROM HR_USERS%';

Note here: If the above query shows that Baseline is not available, you should load the Shared Pool plan to SQL Plan Management.

SQL Plan Management is a mechanism that enables the optimizer to automatically manage the implementation plans, ensuring that the database only uses known or verified query plans. The Baseline SQL plan is used here, which is a set of accepted plans, which the optimizer can safely use for specific SQL queries.

DECLARE
 my_plans pls_integer;
BEGIN
 my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id =>'93c4e9rdek54n', plan_hash_value=>'714642614', fixed=>'YES');
end;
/

Then I checked if Baseline was formed and I noted his SQL_Handle:

select sql_handle, sql_text, plan_name,PARSING_SCHEMA_NAME,enabled,accepted,
fixed,to_char(last_executed,'dd-mon-yy HH24:MI')
last_executed from dba_sql_plan_baselines where upper(sql_text) like 'SELECT EMPLOYEE_ID, FIRST_NAME FROM HR.EMPLOYEES%';


SQL_HANDLE: SQL_704a4963b4e17aes
PLAN_NAME:  SQL_PLAN_70kd8gasf46ra670644sa

I created a small table in a private scheme that was used to load and export the plan to another database:

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

DECLARE
 my_plans number;
BEGIN
 my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
 table_name => 'STGTAB',
 table_owner => 'KAROLW',
 sql_handle => 'SQL_704a4963b4e17aes',
 plan_name => 'SQL_PLAN_70kd8gasf46ra670644sa');
END;
/

The table contained only one record:

select VERSION, SQL_HANDLE, OBJ_NAME, OBJ_TYPE, PLAN_ID, SQL_TEXT from KAROLW.STGTAB;

 VERSION SQL_HANDLE             OBJ_NAME                       OBJ_TYPE              PLAN_ID SQL_TEXT                                                                        
-------- ---------------------- ------------------------------ ------------------ ---------- -----------------------------
       3 SQL_704a4963b4e17aes   SQL_PLAN_70kd8gasf46ra670644sa SQL_PLAN_BASELINE  2543962752 SELECT ID, NAME FROM HR_USERS

I exported it using the standard ExpDP tool:

create or replace directory TMP_DIR as '/oracle/tmp';
exit;

$ expdp \"/ as sysdba\" dumpfile=expdp_STGTAB.dmp parallel=1 directory=TMP_DIR logfile=expdp_STGTAB tables=KAROLW.STGTAB

In order to import it a moment later in the production base:

create or replace directory TMP_DIR as '/oracle/tmp';
exit;

$ impdp \"/ as sysdba\" dumpfile=expdp_STGTAB.dmp parallel=1 directory=TMP_DIR logfile=impdp_STGTAB.log

select VERSION, SQL_HANDLE, OBJ_NAME, OBJ_TYPE, PLAN_ID, SQL_TEXT from KAROLW.STGTAB;

 VERSION SQL_HANDLE             OBJ_NAME                       OBJ_TYPE              PLAN_ID SQL_TEXT                                                                        
-------- ---------------------- ------------------------------ ------------------ ---------- -----------------------------
       3 SQL_704a4963b4e17aes   SQL_PLAN_70kd8gasf46ra670644sa SQL_PLAN_BASELINE  2543962752 SELECT ID, NAME FROM HR_USERS

Staying in the production (target) base, I unpacked Baselinea:

declare
 l_plans_unpacked PLS_INTEGER;
begin
 l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
 table_name => 'STGTAB',
 table_owner => 'KAROLW');
 DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
end;
/

select sql_handle, sql_text, plan_name,PARSING_SCHEMA_NAME,enabled,accepted,fixed,to_char(last_executed,'dd-mon-yy HH24:MI') last_executed from dba_sql_plan_baselines where upper(sql_text) like 'SELECT EMPLOYEE_ID,FIRST_NAME FROM HR.EMPLOYEES%';

After all, I cleaned the cursor so that new queries have already used the new plan:

BEGIN
 FOR i IN (SELECT address, hash_value FROM gv$sqlarea WHERE sql_id = '93c4e9rdek54n')
 LOOP
  SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');
 END LOOP;
END;
/

After completing the above procedure, the application team in contact with users confirmed the correct operation of the system.