Oracle Labs – SQL Plan Management
1.SQL Plan Overview
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.
1.1.SQL Plan Baselines
SQL Plan Baselines are a new feature in Oracle 11g that helps to prevent repeatedly used SQL statements from regressing because a newly generated execution plan is less effective than what was originally in the library cache. It should be noted however, that SQL Plan Baselines do not help if the action taken in the database is so significant that the execution plan absolutely must change, for example if a previously used index is dropped.
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.
What is one of the most challenging aspects of performance tuning in an Oracle database? There are probably a fair number of Oracle DBAs that will tell you dealing with the performance of SQL in their databases is one of the biggest challenges they encounter. SQL statement tuning is often painstaking and can be a very time consuming activity to say the least. Then, once properly tuned, making sure the most resource intensive statements stay that way is yet another hurdle that is faced by DBAs.
The cost based optimizer will generate new execution plans for statements that already have a plan cached in the library cache because of a variety of changes in the database. Sometimes, these new plans perform worse than the plan(s) cached in memory. The list of actions or changes in the database that cause execution plans to be regenerated include upgrades or patches to the optimizer, update optimizer statistics, DDL changes that affect the objects being accessed in the statement, system setting changes and the creation of SQL profiles. All of these are actions commonly performed in regular database maintenance operations.
Oracle has introduced several major features over several releases that are, or have been, designed to help the DBA manage and control the execution plans that the optimizer generates for repetitive statements run in our databases. These features include:
- stored outlines
- SQL Profiles
- SQL Plan Baselines (most recentl)
The drawback to stored outlines and SQL profiles is that they are reactive in nature and may require maintenance and updating. SQL Plan Baselines on the other hand are much more automated, and more proactive in their behavior. New plans can only be added to the SQL Plan Baseline if they will not result in a slower performance, and only plans that are actually in the SQL Plan Baseline are used by optimizer to generate the plans to process statements.
A SQL Plan Baseline is a set of one or more “accepted” plans that contain hints, the plan hash value and other plan related data. In addition to the actual baseline, Oracle also maintains a SQL Plan History. This history is a list of all execution plans generated for a statement, including those that have and have not been moved into the SQL Plan Baseline. Acceptable execution plans are moved from the SQL Plan History into the SQL Plan Baseline, which is referred to as evolving the plan. The baseline and history are maintained in the SQL Management Base (SMB), which is kept in tables in the SYSAUX tablespace. Included in the SMB is also any related SQL profiles for the statements.
The diagram that follows shows the relationship between the SMB, Plan History and Plan Baseline. A statement has had three different execution plans generated. Plan 1 and Plan 2 have performed well, and have been accepted into the baseline. Plan 3 was also generated, however it did not perform as well as the other two, and therefore, while it is part of the history, it is not a baseline plan.
The relationship between the SMB, Plan History and Plan Baseline:
Before a plan in the SQL Plan Baseline can be used or selected by the optimizer, the SQL Baseline must be initialized with at least one accepted plan for the repeatable statements being run. The two activities that populate the SQL Plan Baselines are capturing and evolving. Capturing is the initial load of plans into the baseline, evolving is the evaluation of new plans in the SQL History to ensure they will not cause the statement to regress and then adding them to the SQL Baseline.
2.Capture SQL Plan
During the capture phase, Oracle maintains a log of the SQL ID for statements executed against the database. If a statement is parsed or executed after it was initially logged, it is considered a repeatable statement. For each of these statements, the SQL History is created and new plans (if generated) are added to it. There are two ways to initiate the capture, automatically and manually.
To do an automatic load, change the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE rather than it’s default of FALSE. This is a dynamic parameter, so the capture process can be quickly started and then stopped if necessary. During automatic capture, the first plan generated for any statement is flagged as accepted and placed into both the SQL History and the SQL Baseline. Any subsequent execution plans are placed only in the history.
Manual loading of the initial SQL Baseline is accomplished by using the DBMS_SPM package supplied with Oracle. There are two functions that load SQL plans. These are LOAD_PLANS_FROM_SQLSET and LOAD_PLANS_FROM CURSOR_CACHE. Any plans that are manually loaded are placed into the SQL Plan Baseline.
DBMS_SPM also has a procedure that will migrate stored outlines that may already exist for any of the high-load statements into the SQL Plan Baseline. This function is called MIGRATE_STORED_OUTLINE and was introduced as a part of Oracle Database 11g R2. This means, the work done previously to create stored outlines can now be used to initially seed the SQL Plan Baselines, which is a nice feature.
2.1.Automatic Plan Capture
The default value of the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is FALSE, determines if the system should automatically capture SQL plan baselines. When set to TRUE, the system records a plan history for SQL statements. The first plan for a specific statement is automatically flagged as accepted. Alternative plans generated after this point are not used until it is verified they do not cause performance degradations. Plans with acceptable performance are added to the SQL plan baseline during the evolution phase.
Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true:
alter system set optimizer_use_sql_plan_baselines = true;
or set it true for this session only:
alter session set optimizer_use_sql_plan_baselines = true;
2.2.Manual Plan Loading
Two ways to load SQL Plan:
- LOAD_PLANS_FROM_SQLSET
- LOAD_PLANS_FROM_CURSOR_CACHE
Manual plan loading can be used in conjunction with, or as an alternative to automatic plan capture. The load operations are performed using the DBMS_SPM package, which allows SQL plan baselines to be loaded from SQL tuning sets or from specific SQL statements in the cursor cache. Manually loaded statements are flagged as accepted by default. If a SQL plan baseline is present for a SQL statement, the plan is added to the baseline, otherwise a new baseline is created.
The following code uses the LOAD_PLANS_FROM_SQLSET function to load all statements in an existing SQL tuning set into SQL baselines. A filter can be applied to limit the SQL statements loaded if necessary.
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(
sqlset_name => '<name_of_sqlset>');
END;
/
The LOAD_PLANS_FROM_CURSOR_CACHE functions allow SQL statements to be loaded from the cursor cache. There are four overloads, allowing statements to be identified by a number of criteria, including: SQL_ID, SQL_TEXT, PARSING_SCHEMA_NAME, MODULE and ACTION. The following example identifies the SQL statement using the SQL_ID.
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => '9babjv8yq8ru3');
END;
/
The return value of the LOAD_PLANS_FROM_SQLSET and LOAD_PLANS_FROM_CURSOR_CACHE functions indicates the number of plan loaded by the function call.
3.Evolving a SQL plan baseline
Evolving a SQL plan baseline is the process by which the optimizer determines if non-accepted plans in the baseline should be accepted. As mentioned previously, manually loaded plans are automatically marked as accepted, so manual loading forces the evolving process. When plans are loaded automatically, the baselines are evolved using the EVOLVE_SQL_PLAN_BASELINE function, which returns a CLOB reporting its results.
If the optimizer generates a new plan for a repeated SQL statement, it is automatically added to the SQL Plan History. However, it is not automatically added to the baseline. In order for a new plan to be added to the SQL Plan Baseline, it must be “evolved” or verified first. Once again, there are several methods for evolving a plan from the history into the baseline.
The Automatic SQL Tuning job (also new in 11g) that is run as an automatic task during the maintenance window will automatically verify plans that have been added into the SQL Plan History. Verification simply means that the plans are checked to ensure that the new plan will not result in a performance degradation or regression. This is an out-of-the-box feature that focuses on high-load statements because these are the statements that the Automatic SQL Tuning job selects to analyze.
In addition, manually running the SQL Tuning Advisor may result in plans being added to the SQL Plan Baseline. If the SQL Tuning Advisor yields a recommendation to create and use a SQL Profile, if that profile is accepted, the corresponding plan is automatically added to the baseline.
The ALTER_SQL_PLAN_BASELINE function of DBMS_SPM can be used to change the status of plans in the SQL History to Accepted, which in turn moves them into the SQL Baseline and the EVOLVE_SQL_PLAN_BASELINE function of the DBMS_SPM package can be used to see which plans have been evolved.
4.Fixing SQL Plan Baselines
Another option that can be used with SQL Plan Baselines is to mark one (or more) specific plan as a fixed baseline. If a fixed plan exists in a baseline, the optimizer will give priority to that fixed plan, even if a better, lower cost plan is available.
This would enable a DBA to essentially guarantee (or at least make most likely) a very specific plan to be used by optimizer. However, there are some factors to be aware of, if this approach is used. First, the optimizer will not add new plans to the history if there is a fixed baseline and the EVOLVE_SQL_PLAN_BASELINE function will not evolve plans either. New plans in the SQL Plan Baseline would have to be added by manually loading them from the SQL Cache or a SQL Tuning set. Second, there is a possibility that a better execution plan will be ignored because it has not been marked as fixed.
A plan can be marked as fixed by using the ALTER_SQL_PLAN_BASELINE function of DBMS_SPM and changing the FIXED attribute to a value of YES.
5.DBA_SQL_PLAN_BASELINES Dictionary View
This is the main dictionary view used to see information about the SQL Plan Baselines.
Selecting SQL Baseline Plans:
Once SQL Plan Baselines are in place, additional steps are taken to evaluate whether or not an execution plan from the SQL Plan Baseline should be used for a repeatable statement run in the database. In order for Oracle to replace a generated SQL Plan with a plan from the SQL Plan Baseline, the OPTIMIZER_USE_SQL_PLAN_BASELINES must be set to TRUE (which is the default value).
For each statement that is run, the following steps take place.
6.Controlling SQL Baseline Space Usage
The SQL Management Base (SMB) area allocated in the SYSAUX tablespace is controlled by two settings, and an automatic purge activity takes place to remove any plans that have not been used within a designated period of time.
By default, the SMB is limited to no more than 10 percent of the SYSAUX tablespace. This can be adjusted by using the DBMS_SPM.CONFIGURE(‘SPACE_BUDGET_PERCENT’,nn); command. The value of nn can be from 1percent to 50percent. If the space is exceeded, warnings are generated to the alert log on a weekly basis until the space issue is resolved by adjusting the percentage, changing the size of SYSAUX, or plans are purged.
Plans are purged on a weekly basis. By default, any plan that has not been used in 53 weeks will be automatically purged. This is also an adjustable value and is changed with the DBMS_SPM.CONFIGURE(‘PLAN_RETENTION_WEEKS’,nnn); procedure. Valid values are from 5 to 523 weeks. The LAST_EXECUTED column of DBA_SQL_PLAN_BASELINES will indicate which plans might be up for purging in the near future.
In addition, plans can be manually removed from the SMB using the DBMS_SPM.DROP_SQL_PLAN_BASELINE function.
7.Example I – Automatic Plan Capture
7.1.Automatic Plan Capture
Execute a SQL Query 3 times.
Execute SQL 1st
alter session set optimizer_capture_sql_plan_baselines = true;
select /* SQLPLAN */ event_from, event_to,
departure_airport, terminal_area, area, color,
median(departure_delay_time) as med
from vw_departure_in_disaster
group by event_from, event_to, departure_airport, terminal_area, area, color
order by event_from, event_to, departure_airport;
Execution Plan
----------------------------------------------------------
Plan hash value: 2965097585
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2756K| 173M| | 54136 (1)| 00:00:03 |
| 1 | SORT GROUP BY | | 2756K| 173M| 201M| 54136 (1)| 00:00:03 |
|* 2 | HASH JOIN | | 2756K| 173M| | 10863 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DISASTER_ALARM | 2549 | 124K| | 9 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| AIRLINE_INFO | 895K| 13M| | 10765 (1)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."DEPARTURE"="D"."AIRPORT")
filter("A"."DEPTTIMEPLAN">"D"."EVENT_FROM" AND
"A"."DEPTTIMEPLAN"<"D"."EVENT_TO")
4 - filter("A"."DELAYDEPT">0)
Note
-----
- SQL plan baseline "SQL_PLAN_10v134991m17b3f27d622" used for this statement
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
38971 consistent gets
0 physical reads
0 redo size
8285 bytes sent via SQL*Net to client
685 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
119 rows processed
Execute SQL 2nd
alter session set optimizer_mode = first_rows;
select /*+ INDEX(vw_departure_in_disaster.d idx_event_from) INDEX(vw_departure_in_disaster.d idx_event_to) */
event_from, event_to, departure_airport,
terminal_area, area, color,
median(departure_delay_time) as med
from vw_departure_in_disaster
group by event_from, event_to, departure_airport, terminal_area, area, color
order by event_from, event_to, departure_airport;
Execution Plan
----------------------------------------------------------
Plan hash value: 4089385241
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2756K| 173M| | 55490 (1)| 00:00:03 |
| 1 | SORT GROUP BY | | 2756K| 173M| 201M| 55490 (1)| 00:00:03 |
|* 2 | HASH JOIN | | 2756K| 173M| | 12217 (1)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DISASTER_ALARM | 2549 | 124K| | 1363 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | IDX_EVENT_TO | 2549 | | | 8 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | AIRLINE_INFO | 895K| 13M| | 10765 (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."DEPARTURE"="D"."AIRPORT")
filter("A"."DEPTTIMEPLAN">"D"."EVENT_FROM" AND "A"."DEPTTIMEPLAN"<"D"."EVENT_TO")
5 - filter("A"."DELAYDEPT">0)
Note
-----
- SQL plan baseline "SQL_PLAN_7wuw1u1vsr7zmcfbf8b3b" used for this statement
Statistics
----------------------------------------------------------
39 recursive calls
57 db block gets
40314 consistent gets
1 physical reads
13340 redo size
8285 bytes sent via SQL*Net to client
685 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
119 rows processed
Execute SQL 3rd
-- Gather stats
begin
dbms_stats.gather_table_stats (
ownname => 'FLIGHT',
tabname => 'AIRLINE_INFO',
cascade => TRUE,
no_invalidate => FALSE,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
granularity => 'GLOBAL AND PARTITION',
estimate_percent => 10,
degree => 4
);
end;
/
select /*+ INDEX(vw_departure_in_disaster.d idx_event_from) INDEX(vw_departure_in_disaster.d idx_event_to) INDEX(vw_departure_in_disaster.a idx_depttimeplan) */
event_from, event_to, departure_airport,
terminal_area, area, color,
median(departure_delay_time) as med
from vw_departure_in_disaster
group by event_from, event_to, departure_airport, terminal_area, area, color
order by event_from, event_to, departure_airport;
Execution Plan
----------------------------------------------------------
Plan hash value: 1334732424
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2756K| 173M| | 91035 (1)| 00:00:04 |
| 1 | SORT GROUP BY | | 2756K| 173M| 201M| 91035 (1)| 00:00:04 |
|* 2 | HASH JOIN | | 2756K| 173M| | 47762 (1)| 00:00:02 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DISASTER_ALARM | 2549 | 124K| | 1363 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | IDX_EVENT_TO | 2549 | | | 8 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| AIRLINE_INFO | 895K| 13M| | 46310 (1)| 00:00:02 |
| 6 | INDEX FULL SCAN | IDX_DEPTTIMEPLAN | 1048K| | | 2790 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."DEPARTURE"="D"."AIRPORT")
filter("A"."DEPTTIMEPLAN">"D"."EVENT_FROM" AND "A"."DEPTTIMEPLAN"<"D"."EVENT_TO")
5 - filter("A"."DELAYDEPT">0)
Statistics
----------------------------------------------------------
14 recursive calls
5 db block gets
47654 consistent gets
2783 physical reads
588 redo size
8285 bytes sent via SQL*Net to client
685 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
119 rows processed
7.2.Baseline Evolution
SET LONG 10000
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9')
FROM dual;
7.3.Fixing Basline
declare
l_plans pls_integer;
begin
l_plans := dbms_spm.alter_sql_plan_baseline (
sql_handle => 'SQL_7e6b81d0778b9ff3',
plan_name => 'SYS_SQL_PLAN_7wuw1u1vsr7zmcf8b3b',
attribute_name => 'fixed',
attribute_value => 'YES'
);
end;
/
8.Example II – Manual Loading
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;
8.1.Create and populate a test table
CONN test/test
CREATE TABLE spm_test_tab (
id NUMBER,
description VARCHAR2(50)
);
DECLARE
TYPE t_tab IS TABLE OF spm_test_tab%ROWTYPE;
l_tab t_tab := t_TAB();
BEGIN
FOR i IN 1 .. 10000 LOOP
l_tab.extend;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).description := 'Description for ' || i;
END LOOP;
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO spm_test_tab VALUES l_tab(i);
COMMIT;
END;
/
EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);
8.2.Full Table Scan Query
Query the table using an unindexed column, which results in a full table scan.
SET AUTOTRACE ON
SELECT description
FROM spm_test_tab
WHERE id = 99;
Execution Plan
----------------------------------------------------------
Plan hash value: 1107868462
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SPM_TEST_TAB | 1 | 25 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=99)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47 consistent gets
0 physical reads
0 redo size
561 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
8.3.SQL_ID
Identify the SQL_ID of the SQL statement by querying the V$SQL view.
SET AUTOTRACE OFF
SELECT sql_id
FROM v$sql
WHERE sql_text LIKE '%spm_test_tab%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%'
AND sql_text NOT LIKE '%EXPLAIN%';
SQL_ID
-------------
7vdvq51j5bhpt
8.4.Manually Load the SQL Plan Baseline
Use this SQL_ID to manually load the SQL plan baseline.
SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => '7vdvq51j5bhpt');
DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
Plans Loaded: 1
PL/SQL procedure successfully completed.
8.5.Verify
The DBA_SQL_PLAN_BASELINES view provides information about the SQL plan baselines. We can see there is a single plan associated with our baseline, which is both enabled and accepted.
SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%spm_test_tab%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC
-------------------- --------------------------------- --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES
8.6.Query with Index
Flush the shared pool to force another hard parse, create an index on the ID column, then repeat the query to see the affect on the execution plan.
ALTER SYSTEM FLUSH SHARED_POOL;
CREATE INDEX spm_test_tab_idx ON spm_test_tab(id);
EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);
SET AUTOTRACE ON
SELECT description
FROM spm_test_tab
WHERE id = 99;
Execution Plan
----------------------------------------------------------
Plan hash value: 1107868462
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SPM_TEST_TAB | 1 | 25 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=99)
Note
-----
- SQL plan baseline "SQL_PLAN_7qxjk7bch8h5tb65c37c8" used for this statement
Statistics
----------------------------------------------------------
948 recursive calls
35 db block gets
1091 consistent gets
1 physical reads
9780 redo size
561 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
58 sorts (memory)
0 sorts (disk)
1 rows processed
Notice the query doesn’t use the newly created index, even though we forced a hard parse. Looking at the DBA_SQL_PLAN_BASELINES view we can see why. The SQL plan baseline now contains a second plan, but it has not yet been accepted.
SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_handle = 'SQL_7b76323ad90440b9';
SQL_HANDLE PLAN_NAME ENA ACC
----------------------- ------------------------------------ --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5t3652c362 YES NO
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES
Note: If you don’t see the new row in the DBA_SQL_PLAN_BASELINES view go back and rerun the query from “spm_test_tab” until you do. It sometimes takes the server a few attempts before it notices the need for additional plans.
8.7.Evolve the SQL Plan Baseline
The following query uses the EVOLVE_SQL_PLAN_BASELINE function to evolve the SQL plan baseline and output the associated report.
SET SERVEROUTPUT ON
SET LONG 10000
set lines 175
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => '&sql_handle', time_limit => &time);
DBMS_OUTPUT.PUT_LINE(report);
END;
/
Enter value for sql_handle:SQL_7b76323ad90440b9
Enter value for time:60
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name : TASK_13
Task Owner : SYS
Execution Name : EXEC_21
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 03/31/2021
23:33:40
Finished : 03/31/2021 23:33:40
Last Updated : 03/31/2021 23:33:40
Global Time Limit : 60
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------------------------
SUMMARY
SECTION
---------------------------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 2
Number of recommendations : 1
Number of errors : 0
---------------------------------------------------------------------------------------------
DETAILS
SECTION
---------------------------------------------------------------------------------------------
Object ID : 2
Test Plan Name : SQL_PLAN_7qxjk7bch8h5t3652c362
Base Plan Name : SQL_PLAN_7qxjk7bch8h5tb65c37c8
SQL Handle
: SQL_7b76323ad90440b9
Parsing Schema : TEST
Test Plan Creator : TEST
SQL Text : SELECT description FROM spm_test_tab WHERE id = 99
Execution Statistics:
-----------------------------
Base Plan
Test Plan
---------------------------- ----------------------------
Elapsed Time (s): .000012 .000001
CPU Time (s): .000011 0
Buffer Gets: 4 0
Optimizer
Cost: 13 2
Disk Reads: 0 0
Direct Writes: 0
0
Rows Processed: 0 0
Executions: 10 10
FINDINGS SECTION
---------------------------------------------------------------------------------------------
Findings (2):
-----------------------------
1. The plan was
verified in 0.03400 seconds. It passed the benefit criterion
because its verified performance was 15.33703 times better than that of the
baseline plan.
2. The plan was automatically accepted.
Recommendation:
-----------------------------
Consider accepting the plan.
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id
: 1
Plan Hash Value : 3059496904
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost | Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 13 |
00:00:01 |
| * 1 | TABLE ACCESS FULL | SPM_TEST_TAB | 1 | 25 | 13 | 00:00:01
|
-----------------------------------------------------------------------------
Predicate Information (identified by operation
id):
------------------------------------------
* 1 - filter("ID"=99)
Test Plan
-----------------------------
Plan Id : 2
Plan Hash Value : 911393634
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 25 | 2 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | SPM_TEST_TAB | 1 | 25 | 2 | 00:00:01 |
| * 2 | INDEX RANGE SCAN
| SPM_TEST_TAB_IDX | 1 | | 1 | 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
------------------------------------------
* 2 -
access("ID"=99)
---------------------------------------------------------------------------------------------
PL/SQL procedure successfully completed.
SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_handle = 'SQL_7b76323ad90440b9';
SQL_HANDLE PLAN_NAME ENA ACC
------------------------ --------------------------------- --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5t3652c362 YES YES
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES
Repeating the earlier test shows the more efficient plan is now available for use.
8.8.Altering Plan Baselines
The ALTER_SQL_PLAN_BASELINE function allows the following attributes of a specific plan, or all plans within a baseline to be altered:
- enabled (YES/NO) : If YES, the plan is available for the optimizer if it is also marked as accepted.
- fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans.
- autopurge (YES/NO) : If YES, the SQL plan baseline is purged automatically if it is not used for a period of time.
- plan_name : Used to amend the SQL plan name, up to a maximum of 30 character.
- description : Used to amend the SQL plan description, up to a maximum of 30 character.
The following shows a specific plan being marked as fixed.
SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => 'SQL_7b76323ad90440b9',
plan_name => 'SQL_PLAN_7qxjk7bch8h5t3652c362',
attribute_name => 'fixed',
attribute_value => 'YES');
DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
Plans Altered: 1
PL/SQL procedure successfully completed.
These attributes are present in the DBA_SQL_PLAN_BASELINES view.
8.9.Displaying SQL Plan Baselines
In addition to querying the DBA_SQL_PLAN_BASELINES view, information about SQL plan baselines is available via the DBMS_XPLAN package. The DISPLAY_SQL_PLAN_BASELINE table function displays formatted information about a specific plan, or all plans in the SQL plan baseline in one of three formats (BASIC, TYPICAL or ALL). The following example displays the default format (TYPICAL) report for a specific plan.
SET LONG 10000
SELECT *
FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_7qxjk7bch8h5t3652c362'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_7b76323ad90440b9
SQL text: SELECT description FROM spm_test_tab WHERE id = 99
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_7qxjk7bch8h5t3652c362 Plan id: 911393634
Enabled: YES Fixed: YES Accepted: YES Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2338891031
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SPM_TEST_TAB | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=99)
26 rows selected.
8.10.SQL Management Base
The SQL management base resides in the SYSAUX tablespace and stores SQL plan baselines, as well as statement logs, plan histories and SQL profiles. Space usage is controlled by altering two name-value attributes using the CONFIGURE procedure of the DBMS_SPM package.
- space_budget_percent (default 10) : Maximum size as a percentage of SYSAUX space. Allowable values 1-50.
- plan_retention_weeks (default 53) : Number of weeks unused plans are retained before being purged. Allowable values 5-523 weeks.
The current settings are visible using the DBA_SQL_MANAGEMENT_CONFIG view.
col PARAMETER_NAME for a50
col PARAMETER_VALUE for a15
SELECT parameter_name, parameter_value
FROM dba_sql_management_config;
PARAMETER_NAME PARAMETER_VALUE
-------------------------------------------------- ---------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
AUTO_CAPTURE_PARSING_SCHEMA_NAME
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_ACTION
AUTO_CAPTURE_SQL_TEXT
The following example shows both values being reset.
BEGIN
DBMS_SPM.configure('space_budget_percent', 11);
DBMS_SPM.configure('plan_retention_weeks', 54);
END;
/
PARAMETER_NAME PARAMETER_VALUE
-------------------------------------------------- ---------------
SPACE_BUDGET_PERCENT 11
PLAN_RETENTION_WEEKS 54
8.11.Transferring SQL Plan Baselines
The DBMS_SPM package provides functionality for transferring SQL plan baselines between databases. First, a staging table must be created in the source database using the CREATE_STGTAB_BASELINE procedure.
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'spm_stageing_tab',
table_owner => 'TEST',
tablespace_name => 'USERS');
END;
/
The PACK_STGTAB_BASELINE function exports the SQL plan baselines to the staging table. There are several parameters allowing you to limit amount and type of data you export. The following example exports all SQL plan baselines.
Option 1: Pack all baselines of a schema.
SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
BEGIN
l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
table_name => 'spm_stageing_tab',
table_owner => 'TEST');
DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/
Plans Packed: 2
PL/SQL procedure successfully completed.
Option2: Pack specific baselines.
SET SERVEROUTPUT ON
DECLARE
v_number_of_baselines_packed PLS_INTEGER;
BEGIN
v_number_of_baselines_packed := DBMS_SPM.pack_stgtab_baseline(
table_name => 'spm_stageing_tab',
table_owner => 'TEST',
sql_handle => 'SQL_7b76323ad90440b9',
plan_name => 'SQL_PLAN_7qxjk7bch8h5t3652c362');
v_number_of_baselines_packed := v_number_of_baselines_packed +
dbms_spm.pack_stgtab_baseline(
table_name => 'spm_stageing_tab',
table_owner => 'TEST',
sql_handle => 'SQL_7b76323ad90440b9',
plan_name => 'SQL_PLAN_7qxjk7bch8h5tb65c37c8');
DBMS_OUTPUT.put_line('Loaded ' || v_number_of_baselines_packed || ' plans');
END;
/
The staging table is then transferred to the destination database using data pump or the original export/import utilities. Once in the destination database, the SQL plan baselines are imported into the dictionary using the UNPACK_STGTAB_BASELINE function. Once again, there are several parameters allowing you to limit amount and type of data you import. The following example imports all SQL plan baselines owned by the user “TEST”.
SET SERVEROUTPUT ON
DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name => 'spm_stageing_tab',
table_owner => 'TEST');
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/
8.12.Dropping Plans and Baselines
The DROP_SQL_PLAN_BASELINE function can drop a specific plan from a baseline, or all plans if the plan name is not specified.
SET SERVEROUTPUT ON
DECLARE
l_plans_dropped PLS_INTEGER;
BEGIN
l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
sql_handle => NULL,
plan_name => 'SQL_PLAN_7qxjk7bch8h5t3652c362');
DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/
9.Example III – Fix a baseline
9.1.Check the plans of the query
v$sqlarea is an aggregate view over all child cursors and is cumulative since the server was started up. The elapsed time in that colum is for EVERY EXECUTION OF that query since the server was started.
SQL> select distinct plan_hash_value from dba_hist_sqlstat where sql_id='1f87g6ya8qpi4';
PLAN_HASH_VALUE
-------------------------
2371828357
3040966625
4276570206
* 1328550079 This is the one that query was slow.
26524423
9.2. Check snap_id
SQL> select max(snap_id), plan_hash_value from dba_hist_sqlstat where sql_id='1f87g6ya8qpi4' group by plan_hash_value;
MAX(SNAP_ID) PLAN_HASH_VALUE
-------------- -----------------
140304 2371828357
? 140950 4276570206 Query was called twice at 10:35PM 11:05PM
* 140890 1328550079
140124 2624423
140286 3040966625
9.3. Create a SQL Tuning Set
exec dbms_sqltune.create_sqlset(sqlset_name=>'AI_TRANFFIC_LIGHT_CASES', description=>'AI_TRANFFIC_LIGHT_CASES');
9.4. Load a execution plan into the SQL Tuning Set
set serveroutput on
declear
baseline_ref dbms_sqltune.sqlset_cursor;
begin
open baseline_ref for select value(p) from table(dbms_sqltune.select_workload_repository(
begin_snap => 140949,
end_snap => 140950,
basic_filter => 'sql_id='||CHR(39)||'1f87g6ya8qpi4'||CHR(39)||' and plan_hash_value=4276570206',
attribute_list => 'ALL'
))p;
dbms_sqltune.load_sqlset('AI_TRANFFIC_LIGHT_CASES', baseline_ref);
end;
/
9.5. Query the SQL Tuning Set, make sure your execution plan in there.
select name, owner, created, statement_count from dba_sqlset_ where name='AI_TRANFFIC_LIGHT_CASES';
select * from table (dbms_xplan.display_sqlset('AI_TRANFFIC_LIGHT_CASES','1f87g6ya8qpi4'));
9.6. Create baseline from the SQL Tuning Set
set serveroutput on
declare
ret PLS_INTEGER;
begin
ret := dbms_spm.load_plans_from_sqlset(
sqlset_name => 'AI_TRANFFIC_LIGHT_CASES',
sqlset_owner => 'SYS',
fixed => 'YES',
enabled => 'YES'
);
dbms_output.put_line('Number of plans loaded: '|| ret);
end;
/
9.7. Check the baseline
select * from dba_sql_plan_baselines;
select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_fmty8y8d13qmfapsd539h8', FORMAT => 'OUTLINE'));
9.8. Set baseline to no auto purge
set serveroutput on
declare
l_plans_altered PLS_INTEGER;
begin
l_plans_altered := dbms_spm.alter_sql_plan_baseline(
sql_handle => 'SQL_a7a9h4k34i33ia3u',
plan_name => 'SQL_PLAN_fmty8y8d13qmfapsd539h8'
attribute_name => 'autopurge',
attribute_value => 'NO'
);
dbms_output.put_line('Plans Altered: '|| l_plans_altered);
end;
/
9.9. Check the baseline again
select * from dba_sql_plan_baselines:
9.10. Check the baseline used
select * from v$sql where sql_id='1f87g6ya8qpi4';