In a recent migration, a customer had millions of SQL plan baselines. The customer performed a Full Transportable Export/Import, but the export of the SQL plan baselines took a lot of time. I suggested moving the SQL plan baselines the day before the downtime window.
But can you move SQL plan baselines into an empty database; before you move the data? Do SQL plan baselines care about the underlying schema objects the SQL accesses?
SQL Plan Baselines and Underlying Schema Objects
The optimizer matches a SQL statement with a SQL plan baseline using the signature of the SQL (see appendix). Originally, I thought that it was the SQL ID, but I was wrong:
SQL statements are matched to SQL plan baselines using the signature of the SQL statement. A signature is a unique SQL identifier generated from the normalized SQL text (uncased and with whitespaces removed).
SQL Plan Management uses the signature only to match SQLs to SQL plan baselines. There is no reference to the underlying schema objects, which is evident since the same SQL from different schemas share the same signature.
The conclusion is that importing SQL plan baselines into an empty database is safe before you move the data.
Empty Database and Plan Evolution
But there is a catch. You don’t want plan evolution to happen in a database with no data.
Plan evolution is the process that validates whether a plan performs better than existing ones and, if so, marks the plans as accepted. This can have undesired side effects in an empty database.
There are two ways to avoid this:
-
Disable the SPM Evolve Advisor Task:
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
parameter => 'ALTERNATE_PLAN_BASELINE',
value => '');
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
parameter => 'ALTERNATE_PLAN_SOURCE',
value => '');
END;
/
-
Don’t allow plans to auto-evolve using the accept_plans parameter:
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,
parameter => 'ACCEPT_PLANS',
value => 'FALSE');
END;
/
How to Migrate SQL Plan Baselines
DBMS_SPM
The easiest way you can move SQL plan baselines is using DBMS_SPM. You can find a good example of using it in the documentation.
Data Pump
Data Pump exports SQL plan baselines as part of the SQL Management Base (SMB). Data Pump includes the SMB as part of a full export only. To export the SMB only:
expdp ... full=y include=SMB
Please note that the SMB includes the following as well:
- SQL Profiles
- SQL Patches
- SQL Plan Directives
How to Exclude SQL Plan Baselines
If you migrate SQL plan baselines in advance, you should exclude them from the production migration. As described above, the SQL plan baselines are part of the SQL Management Base. You exclude it using:
expdp ... exclude=SMB
That will exclude the items below. Depending on how you move the SQL plan baselines and if you need the other items in the target database, you might need to move these items manually:
- SQL Profiles
You can move SQL Profiles using
DBMS_SQLTUNE.
- SQL Patches
You can move SQL Patches using
DBM_SQLDIAG.
- SQL Plan Directives
You can move SQL plan directives using
DBMS_SPD. Only relevant if you use Adaptive Statistics.
Appendix
SQL Signature
The signature of an SQL is:
A numeric hash value computed using a SQL statement text that has been normalized for case insensitivity and white space. It uniquely identifies a SQL statement. The database uses this signature as a key to maintain SQL management objects such as SQL profiles, SQL plan baselines, and SQL patches.
Let me use an example. If you have the following SQL text:
select * from dual
The signature is:
14103420975540283355
There is a function that calculates the signature from the SQL text:
col signature format 99999999999999999999999
select dbms_sqltune.sqltext_to_signature ('select * from dual') as signature from dual;
Acknowledgment
Thanks to Nigel Bayliss, optimizer product manager, for valuable assistance.
Databases Are Fun 