Create SQL Plan baselines

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
  • 12 Tuning SQL Statements
    • Managing SQL Plan Baselines
      • Capturing SQL Plan Baselines Automatically
• Oracle^® 12.1 Database SQL Tuning Guide
  • 23 Managing SQL Plan Baselines
    • Configuring SQL Plan Management
    • Loading SQL Plan Baselines
    • Evolving SQL Plan Baselines Manually
      • Managing an Evolve Task
• Oracle^® 12.1 Database Reference
  • optimizer_capture_sql_plan_baselines
  • optimizer_use_sql_plan_baselines
  • DBA_SQL_PLAN_BASELINES

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:

1. Disable automatic generation of SQL Plan Baselines
2. Delete all existing SQL Plan Baselines
3. Generate some execution plans
4. Load these execution plans into a SQL Tuning Set (see load_smb_from_sts.sql).