Navigation Bar

Tuesday, July 5, 2022

To copy a SQL Plan baseline from 1 PDB to another PDB

Blessed are the poor is spirit, for theirs is the Kingdom of Heaven.

                                                                   Mathew 5: 3 


We first check if there are any SQL baselines already loaded for the query in PDB2 and drop it if it exists.

SELECT SQL_HANDLE,SQL_TEXT, PLAN_NAME, ENABLED, ACCEPTED  FROM sys.DBA_SQL_PLAN_BASELINES where SQL_TEXT LIKE '%select * from cal_cntr where cal_date%';
SQL_HANDLESQL_TEXTPLAN_NAMEENABLEDACCEPTED
SQL_937d5a47027e4114select * from cal_cntr where cal_date > sysdate - 365SQL_PLAN_96zau8w17wh8nb8af81ceYESYES
SQL_937d5a47027e4114select * from cal_cntr where cal_date > sysdate - 365SQL_PLAN_96zau8w17wh8n2c3ffc18NONO

For the given SQL we can see that there are 2 plans already loaded in the database and 1 of them is enabled. 

We drop both the existing plans as follows.

DECLARE
 ret_value pls_integer;
BEGIN
  ret_value := DBMS_SPM.drop_sql_plan_baseline(
    sql_handle => 'SQL_937d5a47027e4114',
    plan_name => 'SQL_PLAN_96zau8w17wh8n2c3ffc18'
  );
END;
/
anonymous block completed

DECLARE
 ret_value pls_integer;

BEGIN
  ret_value := DBMS_SPM.drop_sql_plan_baseline(
    sql_handle => 'SQL_937d5a47027e4114',
    plan_name => 'SQL_PLAN_96zau8w17wh8nb8af81ce'
  );
END;
/
anonymous block completed
If we now query DBA_SQL_PLAN_BASELINES table it will give "no rows selected".

The below is a scenario where I have two instances (in this case two PDBs) on the same database having a similar configuration and data volumes, (One could be the application database, the other could be a reporting database which is getting replicated using dataguard or any other data replication method).

For some reason the same query (highly unlikely in a PDB scenario on same database), is running well on 1 instance but is using a bad execution plan on the other instance. This is more likely when the setup consists of 2 different databases having similar nature of data and configuration.

The below post is more to illustrate how to transfer a SQL plan from one instance to another.

On PDB1, we baseline the good plan for the query as per the steps in link below.

To Pin the Plan Hash Value for a query using SQL Plan Baseline Below are the steps for the same. We first create the staging table. 
BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'stage');
END;
/
Next we pack the SQL plan baseline using the DBMS_SPM.PACK_STGTAB_BASELINE function. The SQL_Handle for this basline can be got by querying the DBA_SQL_PLAN_BASELINES table 
SELECT SQL_HANDLE,SQL_TEXT, PLAN_NAME, ENABLED  FROM sys.DBA_SQL_PLAN_BASELINES where SQL_TEXT LIKE '%select * from cal_cntr where cal_date%';

DECLARE
  my_plans number;
BEGIN
  my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
    table_name => 'stage',
    enabled => 'yes',
    SQL_HANDLE => 'SQL_937d5a47027e4114');
END;
/
We use the exp or expdp command to export this STAGE table 
expdp system/oracle123@xepdb1 tables=stage directory=TEST_DIR dumpfile=stage.dmp logfile=stage.log
Export: Release 21.0.0.0.0 - Production on Sat Jul 5 10:20:04 2025
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@xepdb1 tables=stage directory=TEST_DIR dumpfile=stage.dmp logfile=stage.log;
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SYSTEM"."STAGE"                            41.39 KB       2 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  E:\TECHNICAL\BLOGS\POSTS\PERFORMANCE_TUNING\STAGE.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jul 5 10:21:10 2021 elapsed 0 00:01:04
These are the tasks on PDB1. We now connect to PDB2 for importing and unpacking this baseline on PDB2 
impdp system/oracle123@xepdb2 tables=stage directory=TEST_DIR dumpfile=stage.dmp logfile=stage_imp.log
Import: Release 21.0.0.0.0 - Production on Sat Jul 5 10:44:24 2025
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/********@xepdb2 tables=stage directory=TEST_DIR dumpfile=stage.dmp logfile=stage_imp.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."STAGE"                            41.39 KB       2 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jul 5 10:45:18 2021 elapsed 0 00:00:42
Once this data is imported, we unpack the SQL plan baseline on PDB2 
DECLARE
  l_plans_unpacked  PLS_INTEGER;
BEGIN
  l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
    table_name      => 'STAGE',
    table_owner     => 'SYSTEM',
    creator         => 'SYSTEM');
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/
We now query the DBA_SQL_PLAN_BASELINES and verify that the plan has been baselined. In this scenario, since the container is the same, the sql_id for the query is the same on both the PDBs. If we now take the plan of the query on PDB2, It shows that it is using the baselined plan. 
Plan hash value: 991404550
 
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   363K|  7462K|  3701   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CAL_CNTR |   363K|  7462K|  3701   (5)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("CAL_DATE">SYSDATE@!-365)
 
Note
-----
   - SQL plan baseline "SQL_PLAN_96zau8w17wh8nb8af81ce" used for this statement
In a scenario where we have two different databases, in that case the sql_id for the queries may be different. DB1 sql_id1 plan_hash_value1 (good plan) DB2 sql_id2 plan_hash_value2 (not so good plan. To be replaced.). In such a case we will baseline the plan for sql_id 2 also, and then replace the plan hash value for this sql_id with the good plan using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE, or simply disable the bad plan and enable the good plan. 
If the good plan and the bad plan are are both loaded and both have the same SQL_HANDLE, we can disable the bad plan by giving the plan name to be disabled. 
DECLARE
cnt number;
BEGIN
cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle=>'SQL_937d5a47027e4114', plan_name=>'SQL_PLAN_96zau8w17wh8n2c3ffc18', attribute_name=>'enabled',attribute_value=>'NO');
dbms_output.put_line('cnt ' || cnt);
END;
Now when we run the query and take the explain plan for it it will use the good plan which is baselined in DBA_SQL_PLAN_BASELINES.

References
at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)