 |
 |
***********************************************************************************************
* Description: Creating an Oracle SQL plan baseline, and export/import into different database
* Date: 05:02 PM EST, 05/28/2018
***********************************************************************************************
<1> Oracle SQL Plan Baseline:
|
|__ o. SQL plan management is a preventative mechanism that enables the optimizer to automatically manage execution plans,
| ensuring that the database uses only known or verified plans.
|
|__ o. SQL plan management uses a mechanism called a SQL plan baseline.
| A plan baseline is a set of accepted plans that the optimizer is allowed to use for a SQL statement.
| In the typical use case, the database accepts a plan into the plan baseline only after verifying that the plan performs well.
| In this context, a plan includes all plan-related information,
| For example, SQL plan identifier, set of hints, bind values, and optimizer environment, that the optimizer needs to reproduce an execution plan.
|
|
|__ o. When a SQL statement is hard parsed, the cost based optimizer produces several execution plans and selects the one with the lowest cost.
If a SQL plan baseline is present, the optimizer compares the plan it just produced with the plans in the SQL plan baseline.
If a matching plan is found that is flagged as accepted the plan is used.
If the SQL plan baseline doesn't contain an accepted plan matching the one it just created,
-- the optimizer evaluates the accepted plans in the SQL plan baseline and uses the one with the lowest cost.
If the execution plan originally produced by the optimizer has a lower cost than those present in the SQL plan baseline,
-- it is added to the baseline as a not-accepted plan, so it is not used until it is verified not to cause a reduction in performance.
If a system change affects all existing accepted plans, so they are considered non-reproducible,
-- the optimizer will use the plan with the lowest cost.
Oracle call this a "conservative plan selection strategy", as the optimizer preferentially uses a tried an tested execution plan,
even if a new plan looks like it might perform better. Only when the newer plan is proved to perform well will it be accepted for use.
The use of SQL plan baselines is controlled by the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter, which is set to TRUE by default.
Access to the DBMS_SPM package requires the ADMINISTER SQL MANAGEMENT OBJECT privilege.
<2> In some scenario, same SQL is running very fast in QA database, but much longer than expection in production database:
|
|__ o. So, we need to address the SQL id enclosed QA db, capture the good plan and save in a stage table,
finally, expdp the stage table contains the plan, and impdp into production to lock the plan down.
<3> Step 1 - Find the SQL id in QA database, which complete execution fast with good performance:
|
|__ SQL> select sql_id from v$sql where sql_text like 'WITH%' and parsing_schema_name = 'REPORTUSER';
7vkak3x230s8z
<4> Step 2 - Create the stage table in QA for containing the good plan:
|
|__ SQL> BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'crystal_issued_sql');
END;
/
<5> Step 3 - Load the plan from the shared SQL area (also called the cursor cache) into SQL plan baselines, or from SQLSET:
|
|__ SQL> DECLARE
my_plans PLS_INTEGER;
BEGIN
-- my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '7vkak3x230s8z');
-- my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name=>'ISSUED_SQL', basic_filter=>'plan_hash_value = ''3086611361''');
END;
/
<6> Step 4 - Pack SQL plan baselines, which means that it copies them from the SMB into a staging table:
|
|__ SQL> DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'crystal_issued_sql',
enabled => 'yes',
creator => 'emeralit'
);
END;
/
<7> Step 5 - Expdp the plan from QA, and impdp the baseline into production:
|
|__ CMD> expdp emeralit/emeralit tables=crystal_issued_sql directory=DATA_PUMP dumpfile=crystal_issued_sql.dmp logfile=crystal_issued_sql.log
impdp emeralit/emeralit tables=crystal_issued_sql directory=DATA_PUMP dumpfile=crystal_issued_sql.dmp logfile=crystal_issued_sql.log
<8> Step 6 - Unpack SQL plan baselines into production db, which means that it copies SQL plan baselines from a staging table into the SMB:
|
|__ CMD> DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(
table_name => 'crystal_issued_sql',
fixed => 'no'
);
END;
/
<9> Step 7 - Verify the imported baseline got recognized in prod db:
|
|__ SQL> select creator, sql_handle, plan_name, parsing_schema_name, enabled, accepted, fixed from dba_sql_plan_baselines;
<10> Step 8 - Make baseline fixed:
|
|__ SQL> DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle => 'SQL_8e8b24a01780e507',
attribute_name => 'FIXED',
attribute_value => 'YES'
);
END;
/
<11> Disable the baseline:
|
|__ SQL> DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle => 'SQL_fb54608a866d9778',
attribute_name => 'ENABLED',
attribute_value => 'NO'
);
END;
/
<12> Reference:
|
|__ o. https://docs.oracle.com/database/121/TGSQL/tgsql_spm.htm#TGSQL618
|
|__ o. http://www.oracle.com/technetwork/articles/database/create-sql-plan-baseline-2237506.html
|
|__ o. https://oracle-base.com/articles/11g/sql-plan-management-11gr1
Your Comments