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.
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.
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.