Manage SQL Plan baselines

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] About SQL Plan Management (DBMS_SPM)
[2] How to enable this feature.
[3] Configuring the Automatic SPM Evolve Advisor Task
[4] Display the plan using DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE.
[5] Using SQL Tuning and SQL Tuning Sets with Baseline Plans
[6] Evolving a plan from history to baseline.
[7] How to accept/enable/fix a plan.
[8] Managing the SQL Management Base
[9] How to use DBMS_SPM and EM to manage it.
[10] Querying the dictionary views (specially dba_sql_plan_baselines).


SQL Plan Baselines is a feature introduced with Oracle DB 11g that enables 
the system to automatically control SQL plan evolution. With this enabled, 
a newly generated SQL plan can integrate a SQL plan baseline only if it 
has been proven that doing so will not result in performance regression. 
During execution of a SQL statement, only a plan that is part of the 
corresponding SQL plan baseline can be used.

So this feature very useful when you upgrade your Oracle version as you 
can guarantee that no performance regression will ever happen.


[1] About SQL Plan Management (DBMS_SPM)

-> Purpose of SQL Plan Management
The primary goal of SQL plan management is to prevent performance regressions 
caused by plan changes. A secondary goal is to gracefully adapt to changes
such as new optimizer statistics or indexes by verifying
and accepting only plan changes that improve performance.

Note:
SQL plan baselines cannot help when an event has caused irreversible 
execution plan changes, such as dropping an index.

-> Benefits of SQL Plan Management
SQL plan management can improve or preserve SQL performance in database 
upgrades and system and data changes.

-> Differences Between SQL Plan Baselines and SQL Profiles
In general, SQL plan baselines are proactive, whereas SQL profiles are 
reactive.

Typically, you create SQL plan baselines before significant performance 
problems occur. SQL plan baselines prevent the optimizer from using 
suboptimal plans in the future.




The database creates SQL profiles when you invoke SQL Tuning Advisor, 
which you do typically only after a SQL statement has shown high-load 
symptoms. SQL profiles are primarily useful by providing the ongoing 
resolution of optimizer mistakes that have led to suboptimal plans. 
Because the SQL profile mechanism is reactive, it cannot guarantee 
stable performance as drastic database changes occur.

SQL plan baselines reproduce a specific plan, whereas SQL profiles correct 
optimizer cost estimates.

Plan Capture

-> You enable automatic initial plan capture by setting the initialization 
parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true (the default is false). 
When enabled, the database automatically creates a SQL plan baseline for any 
repeatable SQL statement executed on the database.
-> Manual Plan Capture
In SQL plan management, manual plan capture refers to the user-initiated 
bulk load of existing plans into a SQL plan baseline.

-> Manually loaded plans are always marked accepted because the optimizer 
assumes that any plan loaded manually by the administrator has acceptable 
performance.

Plan Selection
-> SQL plan selection is the optimizer ability to detect plan changes based 
on stored plan history, and the use of SQL plan baselines to select plans 
to avoid potential performance regressions.

-> When the database performs a hard parse of a SQL statement, the optimizer 
generates a best-cost plan. By default, the optimizer then attempts to find 
a matching plan in the SQL plan baseline for the statement. If no plan baseline 
exists, then the database runs the statement with the best-cost plan.

If a plan baseline exists, then the optimizer behavior depends on whether 
the newly generated plan is in the plan baseline:

1. If the new plan is in the baseline, then the database executes the 
   statement using the found plan.
2. If the new plan is not in the baseline, then the optimizer marks the newly 
   generated plan as unaccepted and adds it to the plan history. Optimizer 
   behavior depends on the contents of the plan baseline:

-> If fixed plans exist in the plan baseline, then the optimizer uses the 
   fixed plan with the lowest cost.
-> If no fixed plans exist in the plan baseline, then the optimizer uses the 
   baseline plan with the lowest cost.
-> If no reproducible plans exist in the plan baseline, which could happen 
   if every plan in the baseline referred to a dropped index, then the 
   optimizer uses the newly generated cost-based plan.

Plan Evolution
-> In general, SQL plan evolution is the process by which the optimizer 
verifies new plans and adds them to an existing SQL plan baseline.
-> Specifically, plan evolution consists of the following distinct steps:
1. Verifying that unaccepted plans perform at least as well as accepted 
plans in a SQL plan baseline (known as plan verification)
2. Adding unaccepted plans to the plan baseline as accepted plans after 
the database has proved that they perform as well as accepted plans

-> Purpose of Plan Evolution
Typically, a SQL plan baseline for a SQL statement starts with a single 
accepted plan. 
However, some SQL statements perform well when executed with different 
plans under different conditions.

For example, a SQL statement with bind variables whose values result in 
different selectivities may have several optimal plans. Creating a materialized 
view or an index or repartitioning a table may make current plans more expensive 
than other plans.

If new plans were never added to SQL plan baselines, then the performance 
of some SQL statements might degrade. Thus, it is sometimes necessary to 
evolve newly accepted plans into SQL plan baselines. Plan evolution prevents 
performance regressions by verifying the performance of a new plan before 
including it in a SQL plan baseline.

The DBMS_SPM package provides procedures and functions for plan evolution.

These subprograms use the task infrastructure. For example, CREATE_EVOLVE_TASK 
creates an evolution task, whereas EXECUTE_EVOLVE_TASK executes it. All task 
evolution subprograms have the string EVOLVE_TASK in the name.

Use the evolve procedures on demand, or configure the subprograms to run 
automatically. The automatic maintenance task SYS_AUTO_SPM_EVOLVE_TASK 
executes daily in the scheduled maintenance window. The task perform the 
following actions automatically:

1. Selects and ranks unaccepted plans for verification
2. Accepts each plan if it satisfies the performance threshold

#-> Storage Architecture for SQL Plan Management
The SQL plan management infrastructure records the signatures of parsed 
statements, and both accepted and unaccepted plans.

This section contains the following topics:

SQL Management Base
The SQL management base (SMB) is a logical repository in the data dictionary.

The SMB contains the following:

SQL statement log, which contains only SQL IDs
SQL plan history, which includes the SQL plan baselines
SQL profiles
SQL patches

The SMB stores information that the optimizer can use to maintain or 
improve SQL performance. The SMB resides in the SYSAUX tablespace and 
uses automatic segment-space management. Because the SMB is located 
entirely within the SYSAUX tablespace, the database does not use SQL 
plan management and SQL tuning features when this tablespace is unavailable.

#SQL Statement Log

When automatic SQL plan capture is enabled, the SQL statement log contains 
the signature of statements that the optimizer has evaluated over time.

A SQL signature is a numeric hash value computed using a SQL statement 
text that has been normalized for case insensitivity and white space. 
When the optimizer parses a statement, it creates signature.
During automatic capture, the database matches this signature against 
the SQL statement log (SQLLOG$) to determine whether the signature has 
been observed before. If it has not, then the database adds the signature 
to the log. If the signature is already in the log, then the database has 
confirmation that the statement is a repeatable SQL statement.


Example 23-1 Logging SQL Statements
SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

System altered.

SQL> SELECT * FROM SQLLOG$;

SIGNATURE BATCH#
---------- ----------
9.8456E+17 1

SQL> SELECT job_title FROM hr.jobs WHERE job_id = 'AD_PRES';

JOB_TITLE
-----------------------------------
President

SQL> SELECT * FROM SQLLOG$;

SIGNATURE BATCH#
---------- ----------
9.8456E+17 1
1.8096E+19 1

SQL> SELECT job_title FROM hr.jobs WHERE job_id='PR_REP';

JOB_TITLE
-----------------------------------
Public Relations Representative

SQL> SELECT * FROM SQLLOG$;

SIGNATURE BATCH#
---------- ----------
9.8456E+17 1
1.7971E+19 1
1.8096E+19 1

SQL> SELECT SQL_HANDLE, SQL_TEXT
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE 'SELECT job_title%';

no rows selected

A query of DBA_SQL_PLAN_BASELINES shows that no baseline for either 
statement exists because neither statement is repeatable:


SQL> SELECT job_title FROM hr.jobs WHERE job_id='PR_REP';

JOB_TITLE
-----------------------------------
Public Relations Representative

SQL> SELECT SQL_HANDLE, SQL_TEXT
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE 'SELECT job_title%';

SQL_HANDLE SQL_TEXT
--------------------------------------------------------------------------------
SQL_f9676a330f972dd5 SELECT job_title FROM hr.jobs WHERE job_id='PR_REP'

#SQL Plan History
The SQL plan history is the set of captured SQL execution plans. 
The history contains both SQL plan baselines and unaccepted plans.

In SQL plan management, the database detects new SQL execution plans for 
existing SQL plan baselines and records the new plan in the history so that 
they can be evolved (verified). Evolution is initiated automatically by the 
database or manually by the DBA.

Starting in Oracle Database 12c, the SMB stores the execution plans for 
all SQL statements in the SQL plan history. 

The DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE function fetches and displays 
the plan from the SMB. For plans created before Oracle Database 12c, 
the function must compile the SQL statement and generate the plan because 
the SMB does not store it.

Enabled Plans
An enabled plan is a plan that is eligible for use by the optimizer.

When plans are loaded with the enabled parameter set to YES (default), 
the database automatically marks the resulting SQL plan baselines as enabled, 
even if they are unaccepted. You can manually change an enabled plan to a 
disabled plan, which means the optimizer can no longer use the plan even 
if it is accepted.

Accepted Plans
An accepted plan is a plan that is in a SQL plan baseline for a SQL statement 
and thus available for use by the optimizer. An accepted plan contains a 
set of hints, a plan hash value, and other plan-related information.

The SQL plan history for a statement contains all plans, both accepted 
and unaccepted. After the optimizer generates the first accepted plan 
in a plan baseline, every subsequent unaccepted plan is added to the plan 
history, awaiting verification, but is not in the SQL plan baseline.

Fixed Plans
A fixed plan is an accepted plan that is marked as preferred, so that the 
optimizer considers only the fixed plans in the baseline. Fixed plans 
influence the plan selection process of the optimizer.

If new plans are added to a baseline that contains at least one enabled 
fixed plan, then the optimizer cannot use the new plans until you manually 
declare them as fixed.

What you need to practice here is:

[2] How to enable this feature.

Documentation -> Performance
Database SQL Tuning Guide
23 Managing SQL Plan Baselines
Configuring SQL Plan Management


The default values are as follows:

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false
-> For any repeatable SQL statement that does not already exist in the 
plan history, the database does not automatically create an initial SQL 
plan baseline for the statement.

-> This parameter does not control the automatic addition of newly 
discovered plans to a previously created SQL plan baseline.

-> To enable the automatic recognition of repeatable SQL statements and 
the generation of SQL plan baselines for these statements, enter the 
following statement:

SQL> SHOW PARAMETER SQL_PLAN
SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;


OPTIMIZER_USE_SQL_PLAN_BASELINES=true

-> For any SQL statement that has an existing SQL plan baseline, 
the database automatically adds new plans to the SQL plan baseline as 
nonaccepted plans.

-> When you set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization 
parameter to false, the database does not use any plan baselines in the database.

SQL> SHOW PARAMETER SQL_PLAN
SQL> ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES=false

Managing the SPM Evolve Advisor Task
No separate scheduler client exists for the Automatic SPM Evolve Advisor task.
One client controls both Automatic SQL Tuning Advisor and Automatic SPM 
Evolve Advisor. Thus, the same task enables or disables both.

[3] Configuring the Automatic SPM Evolve Advisor Task

The ACCEPT_PLANS tuning task parameter specifies whether to accept recommended 
plans automatically. When ACCEPT_PLANS is true (default), SQL plan management 
automatically accepts all plans recommended by the task. When set to false, 
the task verifies the plans and generates a report if its findings, but 
does not evolve the plans.

Assumptions

The tutorial in this section assumes the following:
You do not want the database to evolve plans automatically.
You want the task to time out after 1200 seconds per execution.

To set automatic evolution task parameters:
COL PARAMETER_NAME FORMAT a25
COL VALUE FORMAT a10
SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM DBA_ADVISOR_PARAMETERS
WHERE ( (TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK') AND
( (PARAMETER_NAME = 'ACCEPT_PLANS') OR
(PARAMETER_NAME = 'TIME_LIMIT') ) );


BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
, parameter => 'LOCAL_TIME_LIMIT'
, value => 1200
);
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
, parameter => 'ACCEPT_PLANS'
, value => 'false'
);
END;
/

[4] Display the plan using DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE.

Displaying Plans in a SQL Plan Baseline

select * from hr.employees where employee_id=100


SELECT SQL_HANDLE, SQL_TEXT
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE '%employees%';

select * from hr.employees where employee_id=100;

SQL> SELECT SQL_HANDLE, SQL_TEXT,SQL_ID
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE '%employees%';

SQL_HANDLE SQL_TEXT
--------------------------------------------------------------------------------
SQL_513f7f8a91177b1a select * from hr.employees where employee_id=100

select sql_id,sql_text from v$sqlarea where sql_text like '%employees%';


SELECT PLAN_TABLE_OUTPUT
FROM V$SQL s, DBA_SQL_PLAN_BASELINES b, 
TABLE(
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle,b.plan_name,'basic') 
) t
WHERE s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE
AND b.PLAN_NAME=s.SQL_PLAN_BASELINE
AND s.SQL_ID='31d96zzzpcys9';

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_513f7f8a91177b1a
SQL text: select * from hr.employees where employee_id=100
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_52gvzja8jfysuc0e983c6 Plan id: 3236529094
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 1833546154

-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES |
| 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK |
-----------------------------------------------------

21 rows selected.

SQL> select * from hr.employees where employee_id=100;

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE
------------------------- -------------------- -----------------------------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
100 Steven King
SKING 515.123.4567 Jun 17 2003 00:00:00
AD_PRES 24000 90


SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 31d96zzzpcys9, child number 1
-------------------------------------
select * from hr.employees where employee_id=100
Plan hash value: 1833546154
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPLOYEE_ID"=100)


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- SQL plan baseline SQL_PLAN_52gvzja8jfysuc0e983c6 used for this statement


23 rows selected.


[5] Using SQL Tuning and SQL Tuning Sets with Baseline Plans

To load sql plan baselines from SQL Tuning Sets from CLI
DBMS_SPM.LOAD_PLANS_FROM_SQLSET 
To load sql plan baselines from cursor cache using CLI
DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE

--> Loading SQL Plan Baselines

#Loading Plans from a SQL Tuning Set

SQL> CONNECT SH/SH@pdb2
Connected.
SQL> SELECT /*LOAD_STS*/ *
FROM 2 sales WHERE quantity_sold > 40
3 ORDER BY prod_id;

no rows selected


->Used OEM to create STS for that SQL.

SELECT SQL_TEXT
FROM DBA_SQLSET_STATEMENTS
WHERE SQLSET_NAME = 'STS_SPM';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT /*LOAD_STS*/ *
FROM sales WHERE quantity_sold > 40
ORDER BY prod_id

declare cnt number; 
BEGIN cnt:=DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'STS_SPM',sqlset_owner => 'SYS'); 
END;
/

->From OEM
--------------------------------------------------------------------------------
SQL handle: SQL_a8fcac1887e06264
SQL text: SELECT /*LOAD_STS*/ * FROM sales WHERE quantity_sold > 40 ORDER BY 
prod_id
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_ajz5c323y0sm454bc8843 Plan id: 1421641795
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 3803407550

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 518 (100)| | | |
| 1 | SORT ORDER BY | | 1 | 29 | 518 (2)| 00:00:01 | | |
| 2 | PARTITION RANGE ALL| | 1 | 29 | 517 (2)| 00:00:01 | 1 | 28 |
| 3 | TABLE ACCESS FULL | SALES | 1 | 29 | 517 (2)| 00:00:01 | 1 | 28 |
----------------------------------------------------------------------------------------------
Return


SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
ORIGIN, ENABLED, ACCEPTED
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_HANDLE='SQL_a8fcac1887e06264';

SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------- --- ----------------------------------------------------------------------------------------------
SQL_a8fcac1887e06264 SELECT /*LOAD_STS*/ *
FROM sales WHERE quantity_sold > 40
ORDER BY prod_id
SQL_PLAN_ajz5c323y0sm454bc8843 MANUAL-LOAD YES YES

#Dropping SQL Plan Baselines
EXEC SYS.DBMS_SQLTUNE.DROP_SQLSET( sqlset_name=>'STS_SPM', sqlset_owner=>'SYS' );

#Loading Plans from the Shared SQL Area

SQL> SELECT /*LOAD_CC*/ *
FROM sh.sales
WHERE quantity_sold > 40
ORDER BY prod_id; 2 3 4

no rows selected

SQL> SELECT SQL_ID, CHILD_NUMBER AS "Child Num",
PLAN_HASH_VALUE AS "Plan Hash",
OPTIMIZER_ENV_HASH_VALUE AS "Opt Env Hash"
FROM V$SQL
WHERE SQL_TEXT LIKE 'SELECT /*LOAD_CC*/%';

SQL_ID Child Num Plan Hash Opt Env Hash
------------- ---------- ---------- ------------
g90jw3usqn4rg 0 3803407550 2297060407

SQL> VARIABLE cnt NUMBER
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( -
sql_id => 'g90jw3usqn4rg');

PL/SQL procedure successfully completed.

SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
ORIGIN, ENABLED, ACCEPTED
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE 'SELECT /*LOAD_CC*/%';


SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------- --- ------------------------------
SQL_f6cb7f742ef93547 SELECT /*LOAD_CC*/ *
FROM sh.sales
WHERE quantity_sold > 40
ORDER BY prod_id SQL_PLAN_gdkvzfhrgkda754bc8843 MANUAL-LOAD YES YES



[6] Evolving a plan from history to baseline.

#Evolving SQL Plan Baselines Manually

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

System altered.

SQL> CONNECT SH/SH@pdb2
Connected.
SQL>
SQL> SET PAGES 10000 LINES 140
SET SERVEROUTPUT ON
COL SQL_TEXT FORMAT A20
COL SQL_HANDLE FORMAT A20
COL PLAN_NAME FORMAT A30
COL ORIGIN FORMAT A12
SET LONGC 60535
SET LONG 60535
SET ECHO ONSQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
SQL>
SQL> SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
FROM products p, sales s
WHERE p.prod_id = s.prod_id
AND p.prod_category_id =203
GROUP BY prod_name; 2 3 4 5

PROD_NAME SUM(QUANTITY_SOLD)
-------------------------------------------------- ------------------
Envoy External 6X CD-ROM 13008
Model SM26273 Black Ink Cartridge 19233
PCMCIA modem/fax 28800 baud 20948
Multimedia speakers- 3" cones 12163
Internal 8X CD-ROM 13319
Deluxe Mouse 12837
Model CD13272 Tricolor Ink Cartridge 15984
Model NM500X High Yield Toner Cartridge 7557
18" Flat Panel Graphics Monitor 5205
External 8X CD-ROM 16494
SIMM- 8MB PCMCIAII card 19557
PCMCIA modem/fax 19200 baud 22768
Envoy External 8X CD-ROM 17430
Envoy External Keyboard 3441
External 6X CD-ROM 13043
Model A3827H Black Image Cartridge 20490
Internal 6X CD-ROM 9523
17" LCD w/built-in HDTV Tuner 6010
SIMM- 16MB PCMCIAII card 15950
Multimedia speakers- 5" cones 11253
Standard Mouse 10156

21 rows selected.

SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED,
ACCEPTED, FIXED, AUTOPURGE
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE '%q1_group%'; 2 3 4

no rows selected

SQL> SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
FROM products p, sales s
WHERE p.prod_id = s.prod_id
AND p.prod_category_id =203
GROUP BY prod_name; 2 3 4 5

PROD_NAME SUM(QUANTITY_SOLD)
-------------------------------------------------- ------------------
Envoy External 6X CD-ROM 13008
Model SM26273 Black Ink Cartridge 19233
PCMCIA modem/fax 28800 baud 20948
Multimedia speakers- 3" cones 12163
Internal 8X CD-ROM 13319
Deluxe Mouse 12837
Model CD13272 Tricolor Ink Cartridge 15984
Model NM500X High Yield Toner Cartridge 7557
18" Flat Panel Graphics Monitor 5205
External 8X CD-ROM 16494
SIMM- 8MB PCMCIAII card 19557
PCMCIA modem/fax 19200 baud 22768
Envoy External 8X CD-ROM 17430
Envoy External Keyboard 3441
External 6X CD-ROM 13043
Model A3827H Black Image Cartridge 20490
Internal 6X CD-ROM 9523
17" LCD w/built-in HDTV Tuner 6010
SIMM- 16MB PCMCIAII card 15950
Multimedia speakers- 5" cones 11253
Standard Mouse 10156

21 rows selected.

SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED,
ACCEPTED, FIXED, AUTOPURGE
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE '%q1_group%'; 2 3 4

SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC FIX AUT
-------------------- -------------------- ------------------------------ ------------ --- --- --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES NO YES
y */ prod_name, sum(
quantity_sold)
FROM products p, s
ales s
WHERE p.prod_id = s
.prod_id
AND p.prod_catego
ry_id =203
GROUP BY prod_name


SQL> EXPLAIN PLAN FOR
SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
FROM products p, sales s
WHERE p.prod_id = s.prod_id
AND p.prod_category_id =203
GROUP BY prod_name;
2 3 4 5 6
Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic +note'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
Plan hash value: 3535171836

------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH GROUP BY | |
| 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL | PRODUCTS |
| 4 | PARTITION RANGE ALL| |
| 5 | TABLE ACCESS FULL | SALES |
------------------------------------------

Note
-----
- SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement

16 rows selected.

SQL>

SQL> CREATE INDEX ind_prod_cat_name
ON products(prod_category_id, prod_name, prod_id); 2

Index created.

SQL> CREATE INDEX ind_sales_prod_qty_sold
ON sales(quantity_sold); 2

Index created.

SQL> SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
FROM products p, sales s
WHERE p.prod_id = s.prod_id
AND p.prod_category_id =203
GROUP BY prod_name; 2 3 4 5

PROD_NAME SUM(QUANTITY_SOLD)
-------------------------------------------------- ------------------
Envoy External 6X CD-ROM 13008
Model SM26273 Black Ink Cartridge 19233
PCMCIA modem/fax 28800 baud 20948
Multimedia speakers- 3" cones 12163
Internal 8X CD-ROM 13319
Deluxe Mouse 12837
Model CD13272 Tricolor Ink Cartridge 15984
Model NM500X High Yield Toner Cartridge 7557
18" Flat Panel Graphics Monitor 5205
External 8X CD-ROM 16494
SIMM- 8MB PCMCIAII card 19557
PCMCIA modem/fax 19200 baud 22768
Envoy External 8X CD-ROM 17430
Envoy External Keyboard 3441
External 6X CD-ROM 13043
Model A3827H Black Image Cartridge 20490
Internal 6X CD-ROM 9523
17" LCD w/built-in HDTV Tuner 6010
SIMM- 16MB PCMCIAII card 15950
Multimedia speakers- 5" cones 11253
Standard Mouse 10156

21 rows selected.

SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_HANDLE IN ('SQL_07f16c76ff893342')
ORDER BY SQL_HANDLE, ACCEPTED; 2 3 4

SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ ------------ --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu2ae9b4305 AUTO-CAPTURE YES NO
y */ prod_name, sum(
quantity_sold)
FROM products p, s
ales s
WHERE p.prod_id = s
.prod_id
AND p.prod_catego
ry_id =203
GROUP BY prod_name

SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES
y */ prod_name, sum(
quantity_sold)
FROM products p, s
ales s
WHERE p.prod_id = s
.prod_id
AND p.prod_catego
ry_id =203
GROUP BY prod_name


SQL> EXPLAIN PLAN FOR
SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
FROM products p, sales s
WHERE p.prod_id = s.prod_id
AND p.prod_category_id =203
GROUP BY prod_name;
Explained.

SQL>
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic +note'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
Plan hash value: 3535171836

------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH GROUP BY | |
| 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL | PRODUCTS |
| 4 | PARTITION RANGE ALL| |
| 5 | TABLE ACCESS FULL | SALES |
------------------------------------------

Note
-----
- SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement

16 rows selected.

SQL>

VARIABLE cnt NUMBER
VARIABLE tk_name VARCHAR2(50)
VARIABLE exe_name VARCHAR2(50)
VARIABLE evol_out CLOB

SQL> EXECUTE :tk_name := DBMS_SPM.CREATE_EVOLVE_TASK( sql_handle => 'SQL_07f16c76ff893342', 
plan_name => 'SQL_PLAN_0gwbcfvzskcu2ae9b4305');

PL/SQL procedure successfully completed.

SQL> SELECT :tk_name FROM DUAL
2 ;


:TK_NAME
-------------
TASK_71

SQL> EXECUTE :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:tk_name);

PL/SQL procedure successfully completed.

SQL> SELECT :exe_name FROM DUAL;

:EXE_NAME
-------------
EXEC_251

SQL> EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:tk_name, 
execution_name=>:exe_name );

PL/SQL procedure successfully completed.

SQL> SELECT :evol_out FROM DUAL;

:EVOL_OUT
--------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------

Task Information:
---------------------------------------------
Task Name : TASK_71
Task Owner : SH
Execution Name : EXEC_251
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 10/06/2019 15:23:13
Finished : 10/06/2019 15:23:15
Last Updated : 10/06/2019 15:23:15
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------------------------

SUMMARY SECTION
---------------------------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 1
Number of recommendations : 0
Number of errors : 0
---------------------------------------------------------------------------------------------

DETAILS SECTION
---------------------------------------------------------------------------------------------
Object ID : 2
Test Plan Name : SQL_PLAN_0gwbcfvzskcu2ae9b4305
Base Plan Name : SQL_PLAN_0gwbcfvzskcu242949306
SQL Handle : SQL_07f16c76ff893342
Parsing Schema : SH
Test Plan Creator : SH
SQL Text : SELECT /* q1_group_by */ prod_name, sum(quantity_sold)
FROM products p, sales s WHERE p.prod_id = s.prod_id AND
p.prod_category_id =203 GROUP BY prod_name

Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ----------------------------
Elapsed Time (s): .114062 .127074
CPU Time (s): .112167 .085667
Buffer Gets: 541 540
Optimizer Cost: 526 524
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 7 7
Executions: 3 3


FINDINGS SECTION
---------------------------------------------------------------------------------------------

Findings (1):
-----------------------------
1. The plan was verified in 2.15000 seconds. It failed the benefit criterion
because its verified performance was only 1.00665 times better than that of
the baseline plan.


EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------

Baseline Plan
-----------------------------
Plan Id : 201
Plan Hash Value : 1117033222

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 574 | 526 | 00:00:01 |
| 1 | HASH GROUP BY | | 14 | 574 | 526 | 00:00:01 |
| * 2 | HASH JOIN | | 183769 | 7534529 | 521 | 00:00:01 |
| * 3 | TABLE ACCESS FULL | PRODUCTS | 14 | 476 | 3 | 00:00:01 |
| 4 | PARTITION RANGE ALL | | 918843 | 6431901 | 516 | 00:00:01 |
| 5 | TABLE ACCESS FULL | SALES | 918843 | 6431901 | 516 | 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 3 - filter("P"."PROD_CATEGORY_ID"=203)


Test Plan
-----------------------------
Plan Id : 202
Plan Hash Value : 2929410821

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 574 | 524 | 00:00:01 |
| 1 | HASH GROUP BY | | 14 | 574 | 524 | 00:00:01 |
| * 2 | HASH JOIN | | 183769 | 7534529 | 519 | 00:00:01 |
| * 3 | INDEX RANGE SCAN | IND_PROD_CAT_NAME | 14 | 476 | 1 | 00:00:01 |
| 4 | PARTITION RANGE ALL | | 918843 | 6431901 | 516 | 00:00:01 |
| 5 | TABLE ACCESS FULL | SALES | 918843 | 6431901 | 516 | 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 3 - access("P"."PROD_CATEGORY_ID"=203)

---------------------------------------------------------------------------------------------


EXECUTE :cnt := DBMS_SPM.IMPLEMENT_EVOLVE_TASK( task_name=>:tk_name, execution_name=>:exe_name );

SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_HANDLE IN ('SQL_07f16c76ff893342')
ORDER BY SQL_HANDLE, ACCEPTED;


[7] How to accept/enable/fix a plan.
===
HOL
===

SQL> connect system/oracle_4U@pdb2
Connected.
SQL>
SQL> create table tbl_spm_test tablespace users as select * from dba_objects;

Table created.

SQL> show parameter baseline

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL>
SQL> select count(*) from dba_sql_plan_baselines;

COUNT(*)
----------
0

SQL> alter system set optimizer_capture_sql_plan_baselines=true;

System altered.

SQL> select count(*) from dba_sql_plan_baselines;

COUNT(*)
----------
0

SQL> select count(*) from dba_sql_plan_baselines;

COUNT(*)
----------
0

SQL> /

COUNT(*)
----------
1

SQL> /

COUNT(*)
----------
1

SQL> select count(*) from tbl_spm_test where object_name='TEST';

COUNT(*)
----------
2

SQL> SET LINESIZE 300
SET PAGESIZE 30000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);SQL> SQL>

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 6wyjchgt2z3j8, child number 0
-------------------------------------
select count(*) from tbl_spm_test where object_name='TEST'

Plan hash value: 3652148371

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 433 (100)| |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
|* 2 | TABLE ACCESS FULL| TBL_SPM_TEST | 50 | 3300 | 433 (1)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_NAME"='TEST')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)


23 rows selected.

SQL> select count(*) from tbl_spm_test where object_name='TEST';

COUNT(*)
----------
2

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 6wyjchgt2z3j8, child number 0
-------------------------------------
select count(*) from tbl_spm_test where object_name='TEST'

Plan hash value: 3652148371

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 433 (100)| |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
|* 2 | TABLE ACCESS FULL| TBL_SPM_TEST | 50 | 3300 | 433 (1)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_NAME"='TEST')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)


23 rows selected.

SQL> select count(*) from tbl_spm_test where object_name='TEST';

COUNT(*)
----------
2

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 6wyjchgt2z3j8, child number 1
-------------------------------------
select count(*) from tbl_spm_test where object_name='TEST'

Plan hash value: 3652148371

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 433 (100)| |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
|* 2 | TABLE ACCESS FULL| TBL_SPM_TEST | 50 | 3300 | 433 (1)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_NAME"='TEST')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline SQL_PLAN_f6ajnb8tgtja5d77370a7 used for this statement


24 rows selected.

SQL> SET LINESIZE 150
COL SQL_HANDLE FORMAT A30
COL PLAN_NAME FORMAT A30
COL SQL_TEXT FROMAT A40
COL SQL_TEXT FORMAT A40
SELECT sql_handle,plan_name,enabled,accepted,fixed,sql_text from dba_sql_plan_baselines
where sql_text like '%spm_test%';

SQL> SQL> SQL> SP2-0158: unknown COLUMN option "FROMAT"
SQL> SQL> 2
SQL_HANDLE PLAN_NAME ENA ACC FIX SQL_TEXT
------------------------------ ------------------------------ --- --- --- ----------------------------------------
SQL_e32a345a32fcc545 SQL_PLAN_f6ajnb8tgtja5d77370a7 YES YES NO select count(*) from tbl_spm_test where object_name='TEST'


SQL> SQL> create index idx_tbl_spm_obj_name on tbl_spm_test(object_name)
2 ;

Index created.

SQL> select count(*) from tbl_spm_test where object_name='TEST';

COUNT(*)
----------
2

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6wyjchgt2z3j8, child number 2

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: 6wyjchgt2z3j8, CHILD_NUMBER: 2
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)


8 rows selected.

SQL> SELECT sql_handle,plan_name,enabled,accepted,fixed,sql_text from dba_sql_plan_baselines
where sql_text like '%spm_test%';
2
SQL_HANDLE PLAN_NAME ENA ACC FIX SQL_TEXT
------------------------------ ------------------------------ --- --- --- ----------------------------------------
SQL_e32a345a32fcc545 SQL_PLAN_f6ajnb8tgtja57082e0c0 YES NO NO select count(*) from tbl_spm_test where object_name='TEST'


SQL_e32a345a32fcc545 SQL_PLAN_f6ajnb8tgtja5d77370a7 YES YES NO select count(*) from tbl_spm_test where object_name='TEST'


SQL> select count(*) from tbl_spm_test where object_name='TEST';

COUNT(*)
----------
2

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gk543ur32q8dt, child number 1
-------------------------------------
select count(*) from tbl_spm_test where object_name='TEST'

Plan hash value: 3652148371

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 433 (100)| |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
|* 2 | TABLE ACCESS FULL| TBL_SPM_TEST | 2 | 132 | 433 (1)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_NAME"='TEST')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline SQL_PLAN_f6ajnb8tgtja5d77370a7 used for this statement


24 rows selected.

SQL> select * from table(dbms_xplan.display_sql_plan_baseline('SQL_e32a345a32fcc545'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_e32a345a32fcc545
SQL text: select count(*) from tbl_spm_test where object_name='TEST'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_f6ajnb8tgtja57082e0c0 Plan id: 1887625408
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 3770309939

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
|* 2 | INDEX RANGE SCAN| IDX_TBL_SPM_OBJ_NAME | 2 | 132 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_NAME"='TEST')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_f6ajnb8tgtja5d77370a7 Plan id: 3614666919
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 3652148371

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 433 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
|* 2 | TABLE ACCESS FULL| TBL_SPM_TEST | 50 | 3300 | 433 (1)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_NAME"='TEST')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

55 rows selected.

SET SERVEROUTPUT ON
SET LONG 10000
DECLARE 
report CLOB;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'SQL_e32a345a32fcc545');
DBMS_OUTPUT.PUT_LINE(report);
END;
/

GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------

Task Information:

---------------------------------------------
Task Name : TASK_75
Task Owner : SYSTEM
Execution
Name : EXEC_255
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status :
COMPLETED
Started : 10/06/2019 15:55:40
Finished : 10/06/2019 15:55:41
Last Updated : 10/06/2019
15:55:41
Global Time Limit : 2147483646
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_f6ajnb8tgtja57082e0c0
Base Plan Name : SQL_PLAN_f6ajnb8tgtja5d77370a7

SQL Handle : SQL_e32a345a32fcc545
Parsing Schema : SYSTEM

Test Plan Creator : SYSTEM
SQL Text : select count(*) from tbl_spm_test where

object_name='TEST'

Execution Statistics:
-----------------------------


Base Plan Test Plan
---------------------------- ----------------------------
Elapsed
Time (s): .000531 .000002
CPU Time (s): .000511 0

Buffer Gets: 155 0
Optimizer Cost: 433 3

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.07000 seconds. It passed the benefit criterion
because its verified
performance was 519.83703 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 : 209
Plan Hash Value : 3614666919


------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 |433 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
| * 2 | TABLE ACCESS FULL | TBL_SPM_TEST | 2 |132 | 433 | 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation
id):
------------------------------------------
* 2 - filter("OBJECT_NAME"='TEST')


Note
-----
- dynamic sampling used for this statement

Test
Plan
-----------------------------
Plan Id : 210
Plan Hash Value : 1887625408


-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes | Cost | Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 3 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
| * 2 | INDEX RANGE SCAN |IDX_TBL_SPM_OBJ_NAME | 2 | 132 | 3 | 00:00:01|
-------------------------------------------------------------------------------------

Predicate Information (identified by operation
id):
------------------------------------------
* 2 - access("OBJECT_NAME"='TEST')


Note
-----
- dynamic sampling used for this
statement

---------------------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

SQL> select count(*) from tbl_spm_test where object_name='TEST';

COUNT(*)
----------
2

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6wyjchgt2z3j8, child number 1
-------------------------------------
select count(*) from tbl_spm_test where object_name='TEST'

Plan hash value: 3770309939
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
|* 2 | INDEX RANGE SCAN| IDX_TBL_SPM_OBJ_NAME | 2 | 132 | 3 (0)|00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_NAME"='TEST')


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline SQL_PLAN_f6ajnb8tgtja57082e0c0 used for this statement


24 rows selected.

SQL>


SQL> select count(*) from tbl_spm_test where owner = 'TEST_OWNER';

COUNT(*)
----------
0

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3wq8dk3y3ah2f, child number 0
-------------------------------------
select count(*) from tbl_spm_test where owner = 'TEST_OWNER'

Plan hash value: 3652148371

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 433 (100)| |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
|* 2 | TABLE ACCESS FULL| TBL_SPM_TEST | 17 | 1122 | 433 (1)| 00:00:0

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='TEST_OWNER')


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)


23 rows selected.

SQL> set linesize 150
SQL> set pagesize 100
SQL> select count(*) from tbl_spm_test where owner = 'TEST_OWNER';

COUNT(*)
----------
0

SQL> select count(*) from tbl_spm_test where owner = 'TEST_OWNER';

COUNT(*)
----------
0

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 3wq8dk3y3ah2f, child number 1
-------------------------------------
select count(*) from tbl_spm_test where owner = 'TEST_OWNER'

Plan hash value: 3652148371

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 433 (100)| |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
|* 2 | TABLE ACCESS FULL| TBL_SPM_TEST | 17 | 1122 | 433 (1)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='TEST_OWNER')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline SQL_PLAN_85u8kwh77f96sd77370a7 used for this statement


24 rows selected.

SQL> CREATE INDEX idx_tbl_spm_owner ON TBL_SPM_TEST(OWNER);

Index created.

SQL> select count(*) from tbl_spm_test where owner = 'TEST_OWNER';

COUNT(*)
----------
0

SQL> /

COUNT(*)
----------
0

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 3wq8dk3y3ah2f, child number 2
-------------------------------------
select count(*) from tbl_spm_test where owner = 'TEST_OWNER'

Plan hash value: 3652148371

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 433 (100)| |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
|* 2 | TABLE ACCESS FULL| TBL_SPM_TEST | 1 | 66 | 433 (1)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='TEST_OWNER')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline SQL_PLAN_85u8kwh77f96sd77370a7 used for this statement


24 rows selected.


SET LINESIZE 150
COL SQL_HANDLE FORMAT A30
COL PLAN_NAME FORMAT A30
COL SQL_TEXT FROMAT A40
COL SQL_TEXT FORMAT A40
SELECT sql_handle,plan_name,enabled,accepted,fixed,sql_text from dba_sql_plan_baselines
where sql_text like '%TEST_OWNER%';

SQL_HANDLE PLAN_NAME ENA ACC FIX SQL_TEXT
------------------------------ ------------------------------ --- --- --- ----------------------------------------
SQL_82e912e40e7724d8 SQL_PLAN_85u8kwh77f96s6348ee17 YES NO NO select count(*) from tbl_spm_test where owner = 'TEST_OWNER'


SQL_82e912e40e7724d8 SQL_PLAN_85u8kwh77f96sd77370a7 YES YES NO select count(*) from tbl_spm_test where owner = 'TEST_OWNER'


select * from table(dbms_xplan.display_sql_plan_baseline('&SQL_HANDLE'));

SET SERVEROUTPUT ON
SET LONG 10000
DECLARE 
report CLOB;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'&1');
DBMS_OUTPUT.PUT_LINE(report);
END;
/

SQL> SET LINESIZE 100 PAGESIZE 100
SQL> select count(*) from tbl_spm_test where owner = 'TEST_OWNER';

COUNT(*)
----------
0

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 3wq8dk3y3ah2f, child number 1
-------------------------------------
select count(*) from tbl_spm_test where owner = 'TEST_OWNER'

Plan hash value: 3012519075

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
|* 2 | INDEX RANGE SCAN| IDX_TBL_SPM_OWNER | 1 | 66 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='TEST_OWNER')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline SQL_PLAN_85u8kwh77f96s6348ee17 used for this statement


24 rows selected.

SQL> select count(*) from dba_sql_plan_baselines;

COUNT(*)
----------
9

SQL> alter system set optimizer_capture_sql_plan_baselines=false;

SQL> alter session set container=cdb$root;

SQL> select count(*) from dba_sql_plan_baselines;

COUNT(*)
----------
23

SQL> alter system set optimizer_capture_sql_plan_baselines=false container=all;

System altered.


[8] Managing the SQL Management Base
Configure Plan retention and space in SYSAUX that it’s allowed to use.

-> To change the percentage limit of the SMB:

SELECT PARAMETER_NAME, PARAMETER_VALUE AS "%_LIMIT", 
( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES 
WHERE TABLESPACE_NAME = 'SYSAUX' ) AS SYSAUX_SIZE_IN_MB,
PARAMETER_VALUE/100 * 
( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES 
WHERE TABLESPACE_NAME = 'SYSAUX' ) AS "CURRENT_LIMIT_IN_MB"
FROM DBA_SQL_MANAGEMENT_CONFIG
WHERE PARAMETER_NAME = 'SPACE_BUDGET_PERCENT';

PARAMETER_NAME %_LIMIT SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB
------------------------- ---------- ----------------- -------------------
SPACE_BUDGET_PERCENT 10 810 81

EXECUTE DBMS_SPM.CONFIGURE('space_budget_percent',30);

PARAMETER_NAME %_LIMIT SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB
------------------------------------ ----------------- -------------------
SPACE_BUDGET_PERCENT 30 810 243

-> To change the plan retention period for the SMB:

SELECT PARAMETER_NAME, PARAMETER_VALUE
FROM DBA_SQL_MANAGEMENT_CONFIG
WHERE PARAMETER_NAME = 'PLAN_RETENTION_WEEKS';

PARAMETER_NAME PARAMETER_VALUE
---------------------------- ---------------
PLAN_RETENTION_WEEKS 53

SQL> EXECUTE DBMS_SPM.CONFIGURE('plan_retention_weeks',105);

PL/SQL procedure successfully completed.

SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE
FROM DBA_SQL_MANAGEMENT_CONFIG
WHERE PARAMETER_NAME = 'PLAN_RETENTION_WEEKS';

PARAMETER_NAME PARAMETER_VALUE
---------------------------- ---------------
PLAN_RETENTION_WEEKS 105


[9] How to use DBMS_SPM and EM to manage it.

DBMS_SPM

DBMS_SPM.SET_EVOLVE_TASK_PARAMETER
DBMS_SPM.LOAD_PLANS_FROM_SQLSET 
DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE
DBMS_SPM.CREATE_EVOLVE_TASK( sql_handle => 'SQL_07f16c76ff893342', plan_name => 'SQL_PLAN_0gwbcfvzskcu2ae9b4305');
DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:tk_name);
DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:tk_name, execution_name=>:exe_name );
DBMS_SPM.IMPLEMENT_EVOLVE_TASK( task_name=>:tk_name, execution_name=>:exe_name );
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'&1');
DBMS_SPM.CONFIGURE('space_budget_percent',30);
DBMS_SPM.CONFIGURE('plan_retention_weeks',105);


Cloud Control


To list and manage SQL Plan baseline from OEM.
go to the server tab and check query optimizer section.
-> SQL PLAN CONTROL
move to sql plan baseline tab to list all baselines

[10] Querying the dictionary views (specially dba_sql_plan_baselines).

SELECT * FROM SQLLOG$;
select count(*) from dba_sql_plan_baselines;
DBA_SQL_MANAGEMENT_CONFIG
DBA_ADVISOR_PARAMETERS

Note:
1. It is possible that single statement having multiple SQL Plans in todays application infrastructure
2. It is mandatory to execute same statement twice to capture in baseline.

Enterprise Manager is the best for that. Doing everything described here in SQL*Plus mode is hard,
but not impossible. So use EM whenever possible for this topic.

======================
Path to Documentation:
======================
SQL Tuning Guide -> 23 Managing SQL Plan Baselines

Thank you for visiting this blog 🙂