You can now manually load SQL baseline of a specific sql_id from a different better performing database (e.g. SIT) to another database where you have slowness issue for that specific sql or sql_id.
Oracle notes and most other blogs
did not have exact correct steps to execute that. You can refer this step by step guide.
Migrate or Export / Import SQL Plan Baseline across Database:
In my DEV, environment I had already created SQL plan baselines for this specific sql_id to stabilize the plan. In my SIT, the execution plan for the same sql_id was not good. I could not find better execution plans for this specific sql_id either in cursor memory or historical reports in SIT. Time was running out , hence i decided to get better baseline from DEV and import in SIT to influence optimizer to perform better.
- Create staging table in
DEV as we want to export the SQL baseline from here and execute the package as SYS or SYSDBA user
exec DBMS_SPM.CREATE_STGTAB_BASELINE(table_name
=> 'SQLBASE_SIT', table_owner=>'APPS');
- Pack the SQL plan
baselines for the sql_id that you want to export from the SQL
management base into the staging table. Execute as SYS user
DECLARE
my_plans number;
BEGIN
my_plans :=
DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'SQLBASE_SIT',
table_owner=>'APPS',
plan_name
=> 'SQL_PLAN_8a6pvqdabksqh4673ac44',
sql_handle => 'SQL_851abbb354b962d0');
END;
/
- Export the table in DEV2
and import it in SIT1
exp system/***
tables='APPS.SQLBASE_SIT' file=sit_sqlbaseline01.dmp
log=expsit_sqlbaseline01.log à
DEV
imp system/****
file=sit_sqlbaseline01.dmp log=impsqlbase01.log full=y à SIT
- Unpacks the specific sql_handle and plan_name stored in the staging table 'SQLBASE_SIT'. Execute only as SYS user. Importing as SYSTEM user would not work and that's the key.
DECLARE
my_plans number;
BEGIN
my_plans :=
DBMS_SPM.UNPACK_STGTAB_BASELINE(
table_name => 'SQLBASE_SIT',
table_owner=>'APPS',
fixed => 'NO',
fixed => 'NO',
plan_name =>
'SQL_PLAN_8a6pvqdabksqh4673ac44',
sql_handle => 'SQL_851abbb354b962d0');
END;
/
- Verify if the sql_id (v$sql ) have
picked up sql plan baseline in SIT1 and it has picked up
order by last_active_time desc;
------- ---------- ------------- --------------- ----------
---------- ---------- ------------------------------
1 APPS
36qn50vfsgzjh
2709963026
38681 25
.000377 SQL_PLAN_8uwa8ckjgdkqh5557b5eb
Sometimes, you may find
it odd that optimizer has not picked up the baseline even after importing. In
those cases, you can flush the address of sql from shared pool , so that the
baselines is picked up.
- Find the SQL
address & hash_value ( not plan_hash_value)
select address, hash_value, plan_hash_value, sql_id,executions,
fetches,(SUM(elapsed_time)/power(10,6))/SUM(executions) avg_response_in_seconds
from gv$sqlarea where sql_id in ('36qn50vfsgzjh','a07y05uya8653') GROUP BY
address, hash_value,plan_hash_value,sql_id,executions, fetches;
ADDRESS HASH_VALUE
PLAN_HASH_VALUE SQL_ID EXECUTIONS
FETCHES AVG_RESPONSE_IN_SECONDS
---------------- ---------- --------------- -------------
---------- ---------- -----------------------
0700010265D3CD58 3716677168 584524445
36qn50vfsgzjh 6056 11800
.876101928
- Flush the specific SQL
from the pool
exec DBMS_SHARED_POOL.PURGE ('0700010265D3CD58,3716677168', 'C');
DROP BASELINE
If there is ever a need to drop the SQL baseline, you can use below
If there is ever a need to drop the SQL baseline, you can use below
DECLARE
my_plans number;
BEGIN
my_plans :=DBMS_SPM.DROP_SQL_PLAN_BASELINE (
plan_name => 'SQL_PLAN_8a6pvqdabksqh4673ac44',
sql_handle => 'SQL_851abbb354b962d0');
END;
/
EVOLVE BASELINE
By default the SQL baselines are evolved unless they marked as FIXED. Hence one specific SQL_Handle can have multiple plan_name as they are evolved. We can run below procedure as SYS or SYSTEM manually and if there are any better plan then baseline plan that has been evolved then it will be accepted.
SQL> SET LONG 100000;
SQL> SELECT
DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_da034ca4e16e6345') from
dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_DA034CA4E16E6345')
--------------------------------------------------------------------------------
Plan was
verified: Time used 7.506 seconds.
Plan failed
performance criterion: 1.25 times better than baseline plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status:
COMPLETE
COMPLETE
Rows Processed:
3
3
Elapsed Time(ms):
3.316
4.272 .78
CPU Time(ms):
2.043
1.891
1.08
Buffer Gets:
424 339
1.25
Physical Read
Requests: 0
0
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_DA034CA4E16E6345')
--------------------------------------------------------------------------------
Physical Write
Requests: 0
0
Physical Read
Bytes: 0
0
Physical Write
Bytes: 0
0
Executions:
1 1
Plan:
SQL_PLAN_dn0ucnmhqwsu5e36d0354
------------------------------------
Plan was
verified: Time used 8.31 seconds.
Plan passed
performance criterion: 1.58 times better than baseline plan.
Plan was changed
to an accepted plan.
- Below SQL show that same SQL_handle has 3 Baslines accepted and baseline with best optimizer cost would be picked up.
select sql_handle,
plan_name, enabled, accepted, optimizer_cost from dba_sql_plan_baselines where
sql_handle='SQL_da034ca4e16e6345' and accepted='YES' order by CREATED desc;
