Summary
SQL Plan Baselines can be:
- Created automatically by setting the optimizer_capture_sql_plan_baselines initialisation parameter either at the CDB or PDB level;
- Created from a SQL Tuning Set (STS)
- Created from the Cursor Cache
Each of these options can be done through OEM or SQL*Plus
References
- Oracle ® 12.1 Database 2 Day + Performance Tuning Guide
- Oracle ® 12.1 Database SQL Tuning Guide
- Oracle ® 12.1 Database Reference
Sample Session Using SQL*Plus
This sample session is based on the tutorial described in “ Managing an Evolve Task ”.
The scripts used can be found at sql-ocm12c/Data and performance management/Create SQL Plan Baselines/ GIT repository.
Determine Current Parameter Settings
Used the following SQL*Plus command to show the relevant parameter settings for SQL Plan Baselines:
show parameter sql_plan_baselines
The result was:
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE
Capture All SQL Plan Baselines
Used the following SQL*Plus command to capture all SQL Plan Baselines:
alter system set optimizer_capture_sql_plan_baselines=true
Generate Some Test Plans
Used the following SQL*Plus command to run SQL in the SH schema of the EXAMPLES PDB:
alter session set container=examples; alter session set current_schema=sh;
Used the following SQL as the sample:
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;
Check To See If Any SQL Plans Were Captured
Used the following SQL to confirm that no SQL plans were captured after just one (1) execution of the above sample SQL:
SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED,
ACCEPTED, FIXED, AUTOPURGE
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE '%q1_group%';
The expected result is:
no rows selected
After Second Sample SQL Execution
After the second execution of the sample SQLDetermine Current Parameter Settings
Used the following SQL*Plus commands to discover that the SQL plan was captured after the second execution of the above sample 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 ON
SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED,
ACCEPTED, FIXED, AUTOPURGE
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE '%q1_group%';
The result is:
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC FIX
-------------------- -------------------- ------------------------------ ------------ --- --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES NO
y */ prod_name, sum(
quantity_sold)
FROM products p,
sales s
WHERE p.prod_id =
s.prod_id
AND p.prod_cate
gory_id =203
GROUP BY prod_name
Create Index to Improve SQL Plan
CREATE INDEX ind_prod_cat_name ON products(prod_category_id, prod_name, prod_id);SQL Plan Control page CREATE INDEX ind_sales_prod_qty_sold ON sales(prod_id, quantity_sold);
The SQL Plans now include the following entries for the sample query:
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC FIX
-------------------- -------------------- ------------------------------ ------------ --- --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES NO
y */ prod_name, sum(
quantity_sold)
FROM products p,
sales s
WHERE p.prod_id =
s.prod_id
AND p.prod_cate
gory_id =203
GROUP BY prod_name
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu2ae9b4305 AUTO-CAPTURE YES NO NO
y */ prod_name, sum(
quantity_sold)
FROM products p,
sales s
WHERE p.prod_id =
s.prod_id
AND p.prod_cate
gory_id =203
GROUP BY prod_name
SQL_a8431fd849deb87e SELECT SQL_HANDLE, S SQL_PLAN_ahhszv14xxf3y6ded1a00 AUTO-CAPTURE YES YES NO
QL_TEXT, PLAN_NAME,
ORIGIN, ENABL
ED, ACCEPTED, FIXED
FROM DBA_SQL_PLAN_
BASELINES
WHERE SQL_TEXT LIKE
'%q1_group%'
There are now two (2) SQL Plan Baselines for the sample query, and one (1) for the retrieval of the SQL Plan baselines.
Using OEM
Access SQL Plan Control
Access the SQL Plan Baselines by following the following pull-down menus on the database home page:
- Performance then
- SQL then
- Click on SQL Plan Control
You will be taken to the SQL Plan Control page.
Access SQL Plan Baseline Tab
On the SQL Plan Control page, click on the SQL Plan Baseline tab:
Captured SQL Plan Baselines
You will note that the first two (2) settings correspond to the initialisation parameters that were examined about in Determine Current Parameter Settings .
Since optimizer_capture_sql_plan_baselines is enabled, you will note that all of the plans displayed have an origin of AUTO-CAPTURE .
Finding Sample SQL Plan Baselines
In the SQL Text box under Search , I entered q1_group_by to locate all SQL Plan Baselines for the sample SQL I executed above. The result is:
Details of SQL Plan Baseline
Clicking on the first SQL Plan Baseline on the current page yields the following details of the SQL Plan Baseline.
Using SQL Tuning Sets to Create SQL Plan Baselines
I have created a shell script, called load_smb_from_sts.sh , which runs a simple scenario to:
- Disable automatic generation of SQL Plan Baselines
- Delete all existing SQL Plan Baselines
- Generate some execution plans
- Load these execution plans into a SQL Tuning Set (see load_smb_from_sts.sql ).