Hi,

I will explain SQL Plan Management Using SQL Plan Baselines in this article.

 

SQL Plan Management in Oracle

Most important point in the Performance Tuning is that maintain the SQL performance in spite of the changes. There are lots of changes and factors that influence the Optimizer and execution plan as follows.

• SQL Profile creation
• Database upgrade and using new Optimizer
• Stale statistics
• Changes on Optimizer parameters
• Changes on Database settings
• Changes to schema and metadata

 

 

 

 

If you don’t know What is the Optimizer and how optimizer works, you should read the following article to learn it detail.

Optimizer Access Paths & Join Operators ( Hash Join, Nested Loop ) in Oracle | Oracle Database Performance Tuning Tutorial -8

 

 

The main task of SQL Plan Management is to prevent performance regressions because of unverifed change to SQL Execution plans. So If the SQL Plan management is active, then there will not be Performance problems even if drastic changes perform.

 

Optimizer generates alternative plans for each SQL statement, but they are not used immediately. Optimizer try to verify if it produces better performance than current plan, then Optimizer consider it to use, because Only known and verified execution plans are used.

 

SQL Plan Baselines in Oracle

SQL Plan Baselines are the vital objects to prevent performance regressions. Architecture of SQL Plan baseline is as follows.

 

An Automatic SQL Tuning task collects only TOP SQL statements that are executed more than once. Plan verification is done by the  Automatic SQL Tuning task running as an automated task in a maintenance window.

 

If you don’t know what is the Automatic SQL Tuning task, you should read the following article.

What is the Automatic SQL Tuning and How to Automated SQL Tuning in Oracle | Oracle Database Performance Tuning Tutorial -12

 

 

 

There are lots of different plans in the plan history generated by the optimizer for any SQL statement. But only some plans in the plan history can be accepted and used which are known and verified.

 

Loading SQL Plan Baselines

There are two ways  load SQL plan baselines.

Firstly, you can set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE to use automatic plan capture.

 

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter is FALSE by default, so set it as TRUE as follows.

SQL> SHOW PARAMETER OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE

SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE SCOPE=BOTH;

System altered.

SQL>

Secondly, You can use the DBMS_SPM package to enable manually managing SQL plan baselines.

 

 

You can load plans from a SQL Tuning Set as follows.

DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'MSD_STS_TEST');
END;
/

 

 

If you know better plan which is not used by a specific SQL, You can load SQL Plan baseline manually from Cursor Cache as follows.

 

DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => '091dz4fc0jj47',plan_hash_value=>4162141870,fixed=>'YES');
END;
/

 

If Optimizer considers any SQL Plan, its ENABLED and ACCEPTED  attributes must be set to YES. When any Plan is enabled for use of Optimizer, then ENABLED attribute is set YES. When ACCEPTED attribute is YES, then Optimizer validate this Plan as a good plan.

If FIXED attributes is SET, then Opimizer considers only these plans instead of other plans.

So I have used FIXED=YES in my above example to force using it manually.

 

 

You can find Plan_hash_value of any SQL_id with the following Script. This script displays all execution plans, so if your query is not using better, then you can load better plan as SQL Plan Baseline.

select inst_id,SQL_PLAN_BASELINE ,sql_fulltext,sql_profile ,trunc(elapsed_time/decode(executions, 0, 1, executions)/1000000) elapsed_time, plan_hash_value,hash_value, child_number, first_load_time,
last_load_time, executions, buffer_gets, trunc(buffer_gets/decode(executions, 0, 1, executions)) gets_per_exec, trunc(rows_processed/decode(executions, 0, 1,
executions)) rows_return_per_exec
from gv$sql where sql_id='091dz4fc0jj47' order by last_load_time desc;

 

After loading SQL Plan Baseline, you can execute the SQL and see if SQL Plan Baseline is used or not.

 

You can query the SQL Plan Baselines by using SQL_TEXT and Plan name as follows.

SELECT * FROM dba_sql_plan_baselines where sql_text like '%SELECT /*+ ALL_ROWS */ T36.CONFLICT_ID,%';

SELECT * FROM dba_sql_plan_baselines where plan_name='SQL_PLAN_046zt02dnhtm8c6bdd030';

 

 

 

You can display SQL Plan Baseline as follows.

SELECT *
FROM TABLE (
DBMS_XPLAN.display_sql_plan_baseline (
plan_name => 'SQL_PLAN_3q32rfc81889n74986091'));

 

You can alter the SQL Plan baseline parameters as follows.

declare
l_plans pls_integer;
begin
l_plans := dbms_spm.alter_sql_plan_baseline (
sql_handle => 'SQL_6600fe4df5fa16a8',
plan_name => 'SQL_PLAN_6c07y9ruzn5p8dcab14db',
attribute_name => 'fixed',
attribute_value => 'YES'
);
end;
/

 

You can drop the SQL Plan baseline as follows.

Firstly, query the SQL Plan Baselines by using SQL_TEXT and Plan name as follows. Specify which SQL Plan Baseline will be dropped, and what is the sql_handle,plan_name of SQL Plan baseline by executing the following scripts.

SELECT * FROM dba_sql_plan_baselines where sql_text like '%SELECT /*+ ALL_ROWS */ T36.CONFLICT_ID,%';

select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
 
SQL_HANDLE                PLAN_NAME                      ENA  ACC    FIX
------------------------------------------------------------------------
SYS_SQL_159d10fcdfedc124  SYS_SQL_PLAN_tyedc741a57b5fc2  YES  NO     NO
SYS_SQL_159d10fcdfedc124  SYS_SQL_PLAN_vbedc741f554c408  YES  YES    NO

Then If you find the sql_handle,plan_name of SQL Plan baseline, you can drop it as follows.

set serveroutput on
declare
my_int pls_integer;
begin
my_int := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
sql_handle => 'SQL_3a19fe140b9d4869',
plan_name =>'SQL_PLAN_2m81syzy7x63x7aa1fbf6');
DBMS_OUTPUT.PUT_line(my_int);
end;
/

 

 

 

 

I will continue to explain Performance tuning tutorial in the next articles.

 

Next post link about Performance Tuning Tutorial is as follows.

How to Create SQL Plan Baselines Using Enterprise Manager Cloud Control | Oracle Database Performance Tuning Tutorial -15

 

 

 

 

Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.

SQL Tuning Tips and Tricks Tutorial in Oracle -3