What is sql baseline?
SQL Plan Baselines in Oracle Database are a way to maintain the performance stability of SQL statements. When an SQL statement is executed, the Oracle optimizer determines the optimal execution plan for that statement based on various factors such as statistics, system settings, and bind variables.
However, sometimes the optimizer might choose a different plan for a SQL statement even when there are no changes in the statement or the data. This can be due to reasons such as changes in the optimizer version, system statistics, initialization parameters, schema statistics, and system settings. Such changes in execution plans might lead to SQL performance regression, where the new plan performs worse than the previous one.
SQL Plan Baselines help avoid such performance regressions. It stores the accepted execution plans for all the relevant SQL statements. When an SQL statement is executed, the optimizer considers the plans in the SQL Plan Baseline first. If one of those stored plans is found to be reproducible and not regressed, then it’s chosen over newly generated plans. The newly generated plans are stored as unaccepted plans. Later on During the evolution process, the unacceptable plans are tested for performance and if they are found to be good, they are stored as accepted plans
This way, SQL Plan Baselines ensure that only known, reproducible, and non-regressing execution plans are used for SQL statements. This can greatly enhance the performance stability of your Oracle Database.
how to create sql baseline in Oracle 19c
We can create it manually or automatically.
Automatic Method
We have a parameter in the database optimizer_capture_sql_plan_baselines which is false by default. If this is set to true, then sql baselines are created for all repeatable statements.
The first plan captured for any statement is automatically accepted and becomes part of the SQL plan baseline, so enable this parameter only when you are sure that the default plans are performing well.
alter system set optimizer_capture_sql_plan_baselines=true;
Manual Method
We can create sql baseline from the Cursor cache, Sql tuning set , AWR or from the staging table
how to create sql baseline in Oracle from cursor Cache
set serveroutput on
DECLARE
x NUMBER;
BEGIN
x := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&sql_id', plan_hash_value => &plan_hash_value );
dbms_output.put_line(to_char(x) || ' plan baselines loaded');
END;
/
how to create sql baseline in Oracle from sql tuning set
set serveroutput on
DECLARE
x PLS_INTEGER;
BEGIN
x:= DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name=>'&sts_name', sqlset_owner=>'&sts_owner', basic_filter => q'# sql_id='&sql_id' and plan_hash_value=&plan_hash_value #');
dbms_output.put_line(to_char(x) || ' plan baselines loaded');
END;
/
How to create sql baseline in Oracle from AWR
variable x number
BEGIN
:x := dbms_spm.load_plans_from_awr(
begin_snap => &snap1,
end_snap => &snap2,
basic_filter => q'# sql_id='&sql_id' and plan_hash_value='&plan_hash_value' #');
END;
/
How to check if the sql baseline got created
SELECT sql_handle, plan_name, origin, enabled, accepted FROM dba_sql_plan_baselines WHERE sql_text LIKE ‘%YOUR_SQL_STATEMENT%’;
Related Articles
SQL Tuning Set in Oracle
how to create sql baseline from Sql tuning set in Oracle
How to drop the sql baseline in Oracle
how to create sql baseline from AWR repository without STS
Sql plan Management in Oracle