SELECT
CHILD_NUMBER CN,
NAME,
VALUE,
ISDEFAULT DEF
FROM
V$SQL_OPTIMIZER_ENV
WHERE
SQL_ID='f6rs5tka838kp'
AND CHILD_NUMBER=3
ORDER BY
NAME;
CN NAME VALUE DEF
-- ----------------------------------- --------------- ---
3 _pga_max_size 368640 KB NO
3 active_instance_count 1 YES
3 bitmap_merge_area_size 1048576 YES
3 cell_offload_compaction ADAPTIVE YES
3 cell_offload_plan_display AUTO YES
3 cell_offload_processing true YES
3 cpu_count 8 YES
3 cursor_sharing exact YES
3 db_file_multiblock_read_count 128 YES
3 hash_area_size 131072 YES
3 is_recur_flags 0 YES
3 optimizer_capture_sql_plan_baseline false YES
Plan Baseline :
If you don’t specify a PLAN_HASH_VALUE when loading a plan baseline from memory, then Oracle will load all plans available for a SQL query in memory. The next time the query executes, the optimizer will use the lowest cost accepted plan in the plan baseline. If multiple plans exist in the plan baseline for a query, and if you have a specific plan that you want the optimizer to always use, then consider altering the plan to a FIXED state
2.PLAN BASELINE
2.1 How To Creating a Plan Baseline for a SQL Statement in Memory
2.2 How To Creating Plan Baselines for SQL Contained in SQL Tuning Set from AWR
2.3 How To Automatically Adding Plan Baselines
2.4 How To Altering a Plan Baseline
2.5 How To Determining If Plan Baselines Exist
2.6 How To Determining if a Query is Using a Plan Baseline
2.7 How To Displaying Plan Baseline Execution Plans
2.8 How To Manually Adding a New Execution Plan to Plan Baseline (Evolving)
2.9 How To Toggling the Automatic Acceptance of New Low-Cost Execution Plans
2.10 How To Disabling Plan Baselines
2.11 How To Removing Plan Baseline Information
2.12 How To Transporting Plan Baselines
2.1 How To Creating a Plan Baseline for a SQL Statement in Memory
You’re planning a database upgrade. You know from past experience that sometimes after the database is upgraded SQL queries can perform more poorly. This is caused by the new version of the optimizer choosing a different execution plan that is less efficient than the plan used by the prior version of the optimizer. Therefore, to ensure that the optimizer consistently chooses the same execution plan before and after the upgrade, you want to establish a plan baseline for the query. You want to start by establishing plan baseline for a SQL query in memory.
The procedure for manually associating a plan baseline with a SQL statement is as follows:
1. Identify the SQL statement for which you want a plan baseline.
2. Provide an identifier such as the SQL_ID as input to the DBMS_SPM package to create a plan
baseline for the SQL statement.
For example, suppose you have a SQL statement you’ve been working with such as the following:
SQL> select first_name from emp where emp_id = 100;
Now query the V$SQL view to determine the SQL_ID for the query:
select sql_id, plan_hash_value, sql_text
from v$sql
where sql_text like 'select first_name from emp where emp_id = 100'
and sql_text not like '%v$sql';
SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ----------------------- ----------------------------
dv73f7y69ny8z 3956160932 select first_name from emp where emp_id = 100
Now that the SQL_ID has been identified, use it as input to the DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
function to create a plan baseline for the given query—for example:
DECLARE
plan1 PLS_INTEGER;
BEGIN
plan1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'dv73f7y69ny8z');
END;
/
Keep in mind that it’s possible that a single SQL statement can have more than one execution plan associated with it in memory. This can happen when the SQL executes multiple times and something in the environment changes that causes the optimizer to choose a different plan (like updated statistics, use of bind variables, changes with database initialization parameters, adding/deleting a SQL profile, and so on).You can uniquely identify a single plan via the combination of SQL_ID and the PLAN_HASH_VALUE column of V$SQL and use that as input to DBMS_SPM, for example:
DECLARE
plan1 PLS_INTEGER;
BEGIN
plan1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'dv73f7y69ny8z',
plan_hash_value => 3956160932);
END;
/
If you don’t specify a PLAN_HASH_VALUE when loading a plan baseline from memory, then Oracle will load all plans available for a SQL query in memory. The next time the query executes, the optimizer will use the lowest cost accepted plan in the plan baseline. If multiple plans exist in the plan baseline for a query, and if you have a specific plan that you want the optimizer to always use, then consider altering the plan to a FIXED
The “Solution” section described how to identify a single SQL statement for which you want to create a plan
baseline (based on the SQL_ID) using a query in the cursor cache. There are many methods for creating a plan baseline for a query, such as using the SQL text, schema, module, and so on. For example, next a plan baseline is loaded based on a partial SQL string:
DECLARE
plan1 PLS_INTEGER;
BEGIN
plan1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
attribute_name => 'sql_text'
,attribute_value => 'select emp_id from emp%');
END;
/
2.2 How To Creating Plan Baselines for SQL Contained in SQL Tuning Set from AWR
You have the following scenario:
• You’re upgrading a database to a new version.
• You know from past experience that upgrading to newer versions of Oracle can sometimes cause SQL statements to perform poorly because the optimizer in the upgraded version of the database is choosing a less efficient (worse) execution plan than the optimizer from the prior version of the database.
• You want to ensure that a set of critical SQL statements execute with acceptable performance
after the upgrade.
To deal with this problem, use the most resource-intensive SQL queries in the AWR as candidates for the creation of plan baselines. This solution uses the technique of creating an AWR baseline. An AWR baseline is a snapshot of activity in the AWR designated by begin/end snapshot IDs. Listed next are the steps for creating and populating a SQL tuning set with high resource-consuming SQL statements found in an AWR baseline and then creating plan baselines for those queries:
1. Create an AWR baseline.
2. Create a SQL tuning set object.
3. Populate the SQL tuning set with the queries found in the AWR baseline.
4. Use the tuning set as input to DBMS_SPM to create a plan baseline for each query contained in the SQL tuning set.
Step 1: Create an AWR Baseline
The first step is to create an AWR baseline. For example, suppose you knew you had high-load queries running between two snapshots in your database. The following creates an AWR baseline using two snapshot IDs:
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 2150,
end_snap_id => 2155,
baseline_name => 'peak_baseline_jun14_13');
END;
/
If you’re unsure of the available snapshots in your database, you can run an AWR report or select the SNAP_ID from DBA_HIST_SNAPSHOTS:
SQL> select snap_id, begin_interval_time from dba_hist_snapshot order by 1;
Step 2: Create a SQL Tuning Set Object
Now create a SQL tuning set. This next bit of code creates a tuning set named test1:
BEGIN
dbms_sqltune.create_sqlset(
sqlset_name => 'test1'
,description => 'STS from AWR');
END;
/
Step 3: Populate the SQL Tuning Set with High-Resource Queries Found in AWR Baseline
Now the SQL tuning set (created in step 2) is populated with any queries found within the AWR baseline
(created in step 1):
DECLARE
base_cur dbms_sqltune.sqlset_cursor;
BEGIN
OPEN base_cur FOR
SELECT value(x)
FROM table(dbms_sqltune.select_workload_repository(
'peak_baseline_jun14_13', null, null,'elapsed_time',
null, null, null, 15)) x;
dbms_sqltune.load_sqlset(
sqlset_name => 'test1',
populate_cursor => base_cur);
END;
/
In the prior lines of a code, the AWR baseline name is passed to the DBMS_SQLTUNE package. The queries within the baseline are select by the elapsed time, and the top 15 are specified. To view the queries within the SQL tuning set, query the data dictionary as follows:
SELECT sqlset_name, elapsed_time
,cpu_time, buffer_gets, disk_reads, sql_text
FROM dba_sqlset_statements
WHERE sqlset_name = 'test1';
Step 4: Use the Tuning Set As Input to DBMS_SPM to Create Plan Baselines for Each Query Contained in the SQL Tuning Set
Now the tuning set (created in step 2 and populated in step 3) is provided as input to the DBMS_SPM package:
DECLARE
test_plan1 PLS_INTEGER;
BEGIN
test_plan1 := dbms_spm.load_plans_from_sqlset(
sqlset_name=>'test1');
END;
/
Each query in the SQL tuning set should now have an entry in the DBA_SQL_PLAN_BASELINES view showing that it has an enabled plan baseline associated with it—for example:
SQL> select sql_handle, plan_name, sql_text from dba_sql_plan_baselines;
The technique shown in the “Solution” section is a very powerful method for creating plan baselines for the most resource-consuming queries running in your database. The key to this recipe is understanding that you can use as input (to the DBMS_SPM package) queries contained in a SQL tuning set. A SQL tuning set can be populated from high resource-consuming statements found in the AWR and memory. This allows you to easily create plan baselines for the most problematic queries.
Having plan baselines in place for resource-intensive queries helps ensure that the same execution plan is used after there are changes to your system, such as a database upgrades, changes in statistics, different data sets, and so on. Keep in mind that it’s possible to have more than one accepted execution plan within the plan baseline. If you have a specific plan that you want the optimizer to always use, then consider altering the plan to a FIXED state.
2.3 How To Automatically Adding Plan Baselines
■Note By default, the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is FALSE.
You want to automatically create plan baselines for every SQL query that repeatedly executes in your database.
Listed next are the steps for automatically creating plan baselines for SQL statements that execute more than once:
1. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE (either at the session
or system level).
2. Execute two times or more the queries for which you want plan baselines captured.
3. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to FALSE.
This next example illustrates the process for adding a plan baseline (for a query) using the prior steps. First, set the specified initialization parameter at the session level:
SQL> alter session set optimizer_capture_sql_plan_baselines=true;
Now a query is executed twice. Oracle will automatically create a plan baseline for a query that is run two or more times while the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is set to TRUE:
SQL> select first_name from emp where emp_id=3000;
SQL> select first_name from emp where emp_id=3000;
Now set the initialization parameter back to FALSE.
SQL> alter session set optimizer_capture_sql_plan_baselines=false;
The query now should have an entry in the DBA_SQL_PLAN_BASELINES view showing that it has an enabled plan baseline associated with it—for example:
SELECT
sql_handle, plan_name, enabled, accepted,
created, optimizer_cost, sql_text
FROM dba_sql_plan_baselines;
Here is a partial listing of the output:
SQL_HANDLE PLAN_NAME ENA ACC...
-------------------- ------------------------------ --- ---...
SQL_790bd425fe4a0125 SQL_PLAN_7k2yn4rz4n095d8a279cc YES YES...
Enabling OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES allows you to automatically capture plan baselines for queries running repeatedly (more than once) in your database. The “Solution” section described how to use this feature at the session level. You can also set the parameter so that all repeating queries in the database have plan baselines generated—for example:
SQL> alter system set optimizer_capture_sql_plan_baselines=true;
From this point, any query in the database that runs more than once will automatically have a plan baseline
created for it. We wouldn’t recommend that you do this in a production environment unless you have first carefully tested this feature and ensured that there will be no adverse side effects (from storing a plan baseline for every query executing more than once). However, you may have a test environment where you want to purposely create a plan baseline for every SQL statement that is repeatedly run.
2.4 How To Altering a Plan Baseline
You’ve been monitoring the plan baseline of a SQL query and notice there are two execution plans with an accepted and enabled status. You’ve examined at both of the execution plans and are confident that one plan is superior to the other based on the value of OPTIMIZER_COST in DBA_SQL_PLAN_BASELINES. You want to instruct the optimizer to always use the plan with the lowest cost.
The optimizer will give preference to plan baselines with a FIXED state. Use the DBMS_SPM package and
ALTER_SQL_PLAN_BASELINE function to alter a current plan baseline execution plan to FIXED. Here’s an example:
DECLARE
pf PLS_INTEGER;
BEGIN
pf := dbms_spm.alter_sql_plan_baseline(
plan_name => 'SQL_PLAN_1wskqhvrwf8g60e23be79'
,attribute_name => 'fixed'
,attribute_value => 'YES');
END;
/
You can query the FIXED column of DBA_SQL_PLAN_BASELINES to verify that it is now fixed within the baseline. Listed next is such a query:
SQL> SELECT sql_handle, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines;
Here is some sample output:
SQL_HANDLE PLAN_NAME ENA ACC FIX
-------------------- ------------------------------ --- --- ---
SQL_457bf2f82571bd38 SQL_PLAN_4ayzkz0kr3g9s90e466fd YES YES NO
SQL_790bd425fe4a0125 SQL_PLAN_7k2yn4rz4n095d8a279cc YES YES YES
2.5 How To Determining If Plan Baselines Exist
You recently implemented a plan baseline for a query. You want to verify the configuration of a plan baseline.
Run the following query to view details regarding any plan baselines that have been configured:
set pages 100 linesize 150
col sql_handle form a20
col plan_name form a30
col sql_text form a40
col created form a20
SELECT sql_handle, plan_name, enabled ,accepted, created, optimizer_cost, sql_text FROM dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENA ACC
-------------------- ------------------------------ --- ---
SQL_b98d2ae2145eec3d SQL_PLAN_bm39aw8a5xv1xae72d2f5 YES YES
CREATED OPTIMIZER_COST SQL_TEXT
-------------------- -------------- --------------------
21-MAR-11 10.53.29.0 2 select last_name from custs...
In the output, there are two key columns: the SQL_HANDLE and PLAN_NAME.
Each query has an associated SQL_HANDLE that is an identifier for a query.
Each execution plan has a unique PLAN_NAME.
The PLAN_NAME is unique within DBA_SQL_PLAN_BASELINES, whereas one query (identified by SQL_HANDLE) can have multiple execution plans (identified by PLAN_NAME).
2.6 How To Determining if a Query is Using a Plan Baseline
You’ve been assigned to investigate performance issues with a database and application. As a preliminary task of understanding the environment, you want to determine if any SQL statements are using plan baselines.
You can directly determine if a SQL statement currently in memory has utilized a plan baseline via the following query:
select sql_id, child_number, sql_plan_baseline, sql_text
from v$sql
where sql_plan_baseline is not null
and sql_text like 'select first_name%';
Here is a snippet of the output:
SQL_ID CHILD_NUMBER SQL_PLAN_BASELINE SQL_TEXT
------------- ------------ ------------------------------ -------------------------
898c46878bm3f 0 SQL_PLAN_31kcfbjxvdfaxc4b18358 select first_name from ...
2.7 How To Displaying Plan Baseline Execution Plans
You want to view any execution plans for a given plan baseline.
If you’re not sure which plan baseline you want to view, then first query the DBA_SQL_PLAN_BASELINES view:
SQL> SELECT plan_name, sql_text FROM dba_sql_plan_baselines;
After you determine the plan name, then use the DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE function to display the execution plan and corresponding plan baseline details. This example reports details for a specific plan:
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(plan_name=>'SQL_PLAN_5jjxkya4jrxv5d8a279cc'));
If you want to display all plans for a SQL statement, then use as input the SQL_HANDLE parameter—for example:
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SQL_b98d2ae2145eec3d'));
If there are multiple plans, then each plan will be displayed in the output.
2.8 How To Manually Adding a New Execution Plan to Plan Baseline (Evolving)
Note S tarting with Oracle Database 12c, new low-cost execution plans will automatically be entered into an existing plan baseline for a query in an accepted state. See Recipe 12-17 for details on how to modify this behavior.
You have the following scenario:
• You have an existing plan baseline for the query.
• You have recently added an index that the query can use.
• The optimizer determines a new lower-cost plan is now available for the query and adds the new plan to the plan history in an unaccepted state.
• You notice the new plan either from a recommendation by the SQL Tuning Advisor or by querying the DBA_SQL_PLAN_BASELINES view.
• You have examined the new execution plan, have run the query in a test environment, and are confident that the new plan will result in better performance.
You want to evolve the low-cost plan in the history so that it’s moved to an accepted plan in the baseline. You realize that once the plan is accepted in the baseline, the optimizer will use it (if it’s the lowest-cost plan in the baseline).
First verify that there are plans in the unaccepted state for the query in question. Here’s a quick example:
SELECT sql_handle, plan_name, enabled, accepted, optimizer_cost
FROM dba_sql_plan_baselines
WHERE sql_text like '%select first_name from emp where emp_id=3000%';
Here is the output indicating there are two plans, one unaccepted but with a much lower cost:
SQL_HANDLE PLAN_NAME ENA ACC OPTIMIZER_COST
------------------------- ------------------------------ --- --- -------------
SQL_58c7b2f2891bf765 SQL_PLAN_5jjxkya4jrxv5c4b18358 YES NO 2
SQL_58c7b2f2891bf765 SQL_PLAN_5jjxkya4jrxv5d8a279cc YES YES 34
Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to move a plan from the history to the baseline (evolve the plan). In this example, the SQL handle (unique SQL string associated with a SQL statement) is used to evolve a plan:
SET SERVEROUT ON SIZE 1000000
SET LONG 100000
DECLARE
rpt CLOB;
BEGIN
rpt := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SQL_58c7b2f2891bf765');
DBMS_OUTPUT.PUT_LINE(rpt);
END;
/
One key feature of SQL plan management is that when a new low-cost plan is generated by the query optimizer, if the new low-cost plan has a lower cost than the accepted plan(s) in the plan baseline, the new low-cost plan will automatically be added to the query’s plan history in an unaccepted state.
You can choose to accept this new low-cost plan, which then moves it into the plan baseline as accepted. Moving an unaccepted execution plan from the plan history to the plan baseline (ENABLED and ACCEPTED) is known as evolving the plan baseline.
Why would a new plan ever be generated by the optimizer? There are several factors that would cause the
optimizer to create a new execution plan that doesn’t match an existing one in the plan baseline:
• New statistics are available.
• A new SQL profile has been assigned to the query.
• An index has been added or dropped.
This gives you a powerful technique to manage and use new plans as they become available. We should also
point out again that starting with Oracle Database 12c, new low-cost execution plans that are added to the plan history will automatically be evolved (added to the plan baseline). See Recipe 12-17 for details on modifying this default behavior.
You can use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function in the following modes:
• Specify the name of the plan to evolve.
• Provide a list of plans to evolve.
• Run it with no value, meaning that Oracle will evolve all non-accepted plans contained within the plan baseline repository.
If Oracle determines that there is an unaccepted plan with a lower cost, then you’ll see output similar to this indicating that the plan has been moved to the accepted state (evolved):
2.9 How To Toggling the Automatic Acceptance of New Low-Cost Execution Plans
In Oracle Database 12c, if a plan baseline exists for a query, and a new lower cost execution plan is generated by the optimizer, the new execution plan will automatically be entered into the plan baseline in an accepted state. You want to change the behavior of Oracle so as to not automatically add new execution plans to the plan baseline.
You can disable the automatic acceptance of a plan added to the plan baseline via the following code:
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER('SYS_AUTO_SPM_EVOLVE_TASK',
'ACCEPT_PLANS', 'false');
END;
/
New in Oracle Database 12c, execution plans will automatically be evolved for you when the daily
SYS_AUTO_SPM_EVOLVE_TASK job runs. You can verify that plans will be automatically evaluated and potentially moved to the accepted state via this query:
select parameter_name, parameter_value
from dba_advisor_parameters
where task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
and parameter_name = 'ACCEPT_PLANS';
You can re-enable the automatic acceptance of plans with this code:
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER('SYS_AUTO_SPM_EVOLVE_TASK',
'ACCEPT_PLANS', 'true');
END;
2.10 How To Disabling Plan Baselines
You’re working with a test database that has many SQL statements with associated plan baselines. You want to determine what the performance difference would be without the plan baselines enabled and therefore want to temporarily disable the use of plan baselines.
To disable the use of any SQL plan baselines within the database, set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to FALSE:
SQL> alter system set optimizer_use_sql_plan_baselines=false scope=memory;
The prior line disables the use of the plan baselines at the SYSTEM level and records the value in memory (but not in the server parameter file). To re-enable the use of plan baselines, set the value back to TRUE.
You can also set the OPTIMIZER_USE_SQL_PLAN_BASELINES at the session level. This disables the use of plan baselines for the duration of the session for the currently connected user:
SQL> alter session set optimizer_use_sql_plan_baselines=false;
The default value for OPTIMIZER_USE_SQL_PLAN_BASELINES is TRUE, which means by default, if plan baselines are available, they will be used. When enabled, the optimizer will look for a valid plan baseline execution plan for the given SQL query and choose the one with the lowest cost. This gives you a quick and easy way to disable/enable the use of plan baselines within your entire database or specific to a session. If you want to disable the use of one specific plan baseline, then alter its state to DISABLED (by setting the ENABLED attribute to a value of NO):
DECLARE
pf PLS_INTEGER;
BEGIN
pf := dbms_spm.alter_sql_plan_baseline(
plan_name => 'SQL_PLAN_4ayzkz0kr3g9s6afbe2b3'
,attribute_name => 'ENABLED'
,attribute_value => 'NO');
END;
/
2.11 How To Removing Plan Baseline Information
You have several plan baselines that you no longer want to use and therefore want to remove them.
Solution
First, determine which plan baselines exist for your database:
SQL> select plan_name, sql_handle, optimizer_cost from dba_sql_plan_baselines;
Once you have either the PLAN_NAME or the SQL_HANDLE, you can drop a single plan baseline. This removes a single execution plan from the plan baseline using the PLAN_NAME parameter:
DECLARE
plan_name1 PLS_INTEGER;
BEGIN
plan_name1 := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
plan_name => 'SQL_PLAN_bm39aw8a5xv1x519fc7bf');
END;
/
You can also drop all plans associated with a SQL statement. This example removes all plans associated with a SQL statement using the SQL_HANDLE parameter:
DECLARE
sql_handle1 PLS_INTEGER;
BEGIN
sql_handle1 := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_b98d2ae2145eec3d');
END;
/
You may occasionally want to remove SQL plan baselines for the following reasons:
You have old plans that ar • en’t used anymore because more efficient plans (evolved)
are available for a SQL statement (see Recipe 12-14 for determining if a query is usinga plan baseline).
• You have plans that were never accepted and now want to remove them.
• You have plans that were created for testing environments that are no longer needed.
As shown in the “Solution” section, you can remove a specific plan baseline via the PLAN_NAME parameter. This will remove one specific plan. If you have several plans associated with one SQL statement, you can remove all plan baselines for that SQL statement via the SQL_HANDLE parameter.
If you have a database where you want to clear out all plans, then you can encapsulate the call
DBMS_SPM.DROP_SQL_PLAN_BASELINE within a PL/SQL block that drops all plans by looping through any plan found in DBA_SQL_PLAN_BASELINES:
SET SERVEROUT ON SIZE 1000000
DECLARE
sql_handle1 PLS_INTEGER;
CURSOR c1 IS
SELECT sql_handle
FROM dba_sql_plan_baselines;
BEGIN
FOR r1 IN c1 LOOP
sql_handle1 := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => r1.sql_handle);
DBMS_OUTPUT.PUT_LINE('PB dropped for SH: ' || r1.sql_handle);
END LOOP;
END;
/
2.12 How To Transporting Plan Baselines
You have a test environment, and you want to ensure that all of the plan baselines in the test system are moved to a production database.
Follow these steps to transport plan baselines:
1. Create a table using the DBMS_SPM package and CREATE_STGTAB_BASELINE procedure.
2. Populate the table with plan baselines using the DBMS_SPM.PACK_STGTAB_BASELINE function.
3. Copy the staging table to the destination database using a database link or Data Pump.
4. Import the plan baseline information using the DBMS_SPM.UNPACK_STGTAB_BASELINE function.
This example first uses the DBMS_SPM package to create a table named EXP_PB:
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'exp_pb');
END;
/
Next the EXP_PB table is populated with plan baselines created by the database user MV_MAINT:
DECLARE
pbs NUMBER;
BEGIN
pbs := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'exp_pb',
enabled => 'yes',
creator => 'MV_MAINT');
END;
/
The prior code populates the table with all plan baselines created by a user. You can also populate the table by PLAN_NAME, SQL_HANDLE, SQL_TEXT, or various other criteria. The only mandatory parameter is the name of the table to be populated. Now copy the staging table to the destination database. You can use a database link or Data Pump to accomplish this.
Lastly, on the destination database, use the DBMS_SPM.UNPACK_STGTAB_BASELINE function to take the contents of the EXP_PB table and create plan baselines:
DECLARE
pbs NUMBER;
BEGIN
pbs := DBMS_SPM.UNPACK_STGTAB_BASELINE(
table_name => 'exp_pb',
enabled => 'yes');
END;
/
You should now have all of the plan baselines transferred to your target database. You can query
DBA_SQL_PLAN_BASELINES to verify this.
It’s a fairly easy process to create a table, populate it with plan baseline information, copy the table, and the import its contents into the destination database. As shown in step 2 of the “Solution” section of this recipe, the PACK_STGTAB_BASELINE function is used (see Table 12-9) for details on parameters to this function). This function allows quite a bit of flexibility in what types of plan baselines you want exported. You can limit the plan baselines extracted to a specific user, or enabled, or accepted, and so on.
