Being a DBA, we might face a question, asking for tuning an SQL statement without changing the code or adding hints or adding/removing joins.Lets see how we can achieve this.Here, I will demonstrate an example of forcing the optimizer to change the execution plan of a SQL statement without changing the SQL itself.
PROCEDURE-1) I have created two tables EMPLOYEE and DEPARTMENT having EMP_ID and DEPT_ID respectively as primary keys and I am creating an index on DEPT_ID column in EMPLOYEE table and it is foreign key to DEPARTMENT.
PROCEDURE-2) The SQL statement in the example is from a large batch jobs running daily in an Oracle database. During the tuning process it was found that adding a composite index to a big table can significantly improve the query performance. However this is a “popular” table in the database and is being used by many different modules and processes. To maintain the stability of the system we only want the new index being used by the tuned SQL, not any other SQL statements.To achieve this purpose we create the index as INVISIBLE so it is not used by the optimizer for any other SQL
statement. For this SQL statement we add USE_INVISIBLE_INDEX hint so that the index is only used by the optimizer for this particular SQL. The problem is we are not allowed to change the code.Therefore adding the hint to the original SQL is not feasible. In order to force the original SQL statement to use an execution plan in which the invisible index is used, we use an Oracle database feature named SQL Plan Baseline, which was introduced in 11g. We can create plan baseline for the original SQL statement and for the one modified with the hint added. Then we replace the plan baseline of the original SQL with the one of the modified. Next time the original SQL runs the optimizer will use the execution plan from the modified with hint. Therefore the invisible index is used for this SQL.Oracle SQL Plan Management ensures that you get the desirable plan which will evolve over time as optimizer discovers better ones.
STEP-1)creating table and inserting some records
drop table department;
create table department(dept_id number primary key, dept_name char(100));
drop table EMPLOYEE;
create table EMPLOYEE(emp_id number primary key, emp_name char(100), dept_id number references department(dept_id));
create index empidx1 on EMPLOYEE(emp_id);
insert into department select rownum, 'DEPARTMENT'||rownum from all_objects;
insert into EMPLOYEE select rownum, 'dept'||rownum, dept_id from department;
update EMPLOYEE set dept_id = 500 where dept_id > 100;
STEP-2) Gather table stats
begin dbms_stats.gather_table_stats (USER, 'EMPLOYEE', cascade=> true); end;
begin dbms_stats.gather_table_stats (USER, 'department', cascade=> true); end;
STEP-3)let us have a look at the undesirable plan which does not use the index.
select emp_name, dept_name
from EMPLOYEE c, department p
where c.dept_id = p.dept_id
and c.dept_id = :dept_id;
select * from table (dbms_xplan.display_cursor());
SQL_ID 0u676p5cvfxz4, child number 0
-------------------------------------
select emp_name, dept_name from EMPLOYEE c, department p where
c.dept_id = p.dept_id and c.dept_id = :dept_id
Plan hash value: 341203176
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 479 (100)| |
| 1 | NESTED LOOPS | | 19 | 4370 | 479 (1)| 00:00:06 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 115 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SYS_C0023547 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMPLOYEE | 19 | 2185 | 478 (1)| 00:00:06 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("P"."DEPT_ID"=TO_NUMBER(:DEPT_ID))
4 - filter("C"."DEPT_ID"=TO_NUMBER(:DEPT_ID))
Note
-----
- dynamic sampling used for this statement (level=2)
STEP-4) Load above undesirable plan into baseline to establish a SQL plan baseline for this query into which the desired plan will be loaded later.
DECLARE
n1 NUMBER;
BEGIN
n1 := dbms_spm.load_plans_from_cursor_cache(sql_id => '0u676p5cvfxz4');
END;
select sql_text, sql_handle, plan_name, enabled
from dba_sql_plan_baselines
where lower(sql_text) like '%emp_name%';
STEP-5)Disable undesirable plan so that this plan will not be used
declare
cnt number;
begin
cnt := dbms_spm.alter_sql_plan_baseline (
SQL_HANDLE => 'SQL_c17c9f7d83124502',
PLAN_NAME => 'SQL_PLAN_c2z4zgq1j4j823ed2aa92',
ATTRIBUTE_NAME => 'enabled',
ATTRIBUTE_VALUE => 'NO');
end;
check enabled is NO
select sql_text, sql_handle, plan_name, enabled
from dba_sql_plan_baselines
where lower(sql_text) like '%emp_name%';
STEP-6) Now we use hint in the above SQL to generate the optimal plan which uses index
select /*+ index(e)*/ emp_name, dept_name
from EMPLOYEE e, department d
where e.dept_id = d.dept_id
and e.dept_id = :dept_id;
select * from table (dbms_xplan.display_cursor());
STEP-7)Now we will load the hinted plan into baseline, Note that we have SQL_ID and PLAN_HASH_VALUE of the hinted statement and SQL_HANDLE for the unhinted statement i.e. we are associating hinted plan with unhinted statement
DECLARE
cnt NUMBER;
BEGIN
cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => 'dtj3d4das6a9a', plan_hash_value => 2379270125, sql_handle => 'SQL_c17c9f7d83124502');
END;
STEP-8)Verify that there are now two plans loaded for that SQL statement:
Unhinted sub-optimal plan is disabled Hinted optimal plan which even though is for a “different query,” can work with earlier unhinted query (SQL_HANDLE is same) is enabled.
select sql_text, sql_handle, plan_name, enabled
from dba_sql_plan_baselines
where lower(sql_text) like '%emp_name%';
STEP-9)Verify that hinted plan is used even though we do not use hint in the query.The note confirms that baseline has been used for this statement
select emp_name, dept_name
from EMPLOYEE c, department p
where c.dept_id = p.dept_id
and c.dept_id = :dept_id;
select * from table (dbms_xplan.display_cursor());
select * FROM table(dbms_xplan.display_cursor(format=>'typical +peeked_binds'));
select sql_text, sql_handle, plan_name, enabled
from dba_sql_plan_baselines
where lower(sql_text) like '%emp_name%';
select plan_table_output from table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));
Summary: Using this method, you can swap the plan for only a query which is fundamentally same i.e. you should get the desirable plan by adding hints, modifying an optimizer setting, playing around with statistics etc. and
then associate sub-optimally performing statement with the optimal plan.
PROCEDURE-2) Generate SQL Plan Baseline for the Original SQL
In an 11g database, by default Oracle does not collect SQL plan baselines automatically unless you set init.ora parameter optimizer_capture_sql_plan_baseline to TRUE. So if the plan baseline does not exist for the original SQL statement, we need to generate it.
1) Create a SQL tuning set. Give it a name and description that suit your situation.
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => '0u676p5cvfxz4_tuning_set',
description => ‘Shadow Process’);
END;
/
2) If the SQL statement was run recently, get the starting and ending AWR snapshot numbers for the time period when the SQL was run. Also using the SQL ID, get the plan hash value from DBA_HIST_SQLSTAT and DBA_HIST_SNAPSHOT views。
3) Load the tuning set with the execution plan extracted from AWR, using the AWR snapshot numbers, the SQL_ID and the plan hash value.
DECLARE
cur SYS_REFCURSOR;
BEGIN
OPEN cur FOR
SELECT value(p)
FROM TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
begin_snap => 469,
end_snap => 472,
basic_filter => 'sql_id = ''0u676p5cvfxz4''
and plan_hash_value = 341203176')
) p;
DBMS_SQLTUNE.LOAD_SQLSET('0u676p5cvfxz4_tuning_set', cur);
CLOSE cur;
END;
/
4) Create SQL plan baseline from the loaded SQL tuning set
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name =>'0u676p5cvfxz4_tuning_set');
END;
/
5) Check the newly created plan baseline
select sql_handle, plan_name, origin, enabled, accepted, fixed, sql_text, created, last_executed
from dba_sql_plan_baselines
where created > sysdate -1/24
order by sql_handle, plan_name;
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX CREATED
------------------------- ------------------------------ -------------- --- --- --- -------------------
SQL_ef88a476fc38c5af SQL_PLAN_fz254fvy3jjdgc4138c40 MANUAL-LOAD YES YES NO 13-MAY-16 10.57.36.000000 AM
Modify SQL Statement and Generate Its Plan Baseline
1) Add USE_INVISIBLE_INDEX hint to the original SQL statement.
2) Change session parameter to catch plan baseline automatically
ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;
3) Catch SQL plan baseline of the modified SQL by running it twice.
4) Check the plan baseline to make sure it is caught.
select sql_handle, plan_name, origin, enabled, accepted, fixed, sql_text, created, last_executed
from dba_sql_plan_baselines
where created > sysdate -1/24
order by sql_handle, plan_name;
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX CREATED
------------------------- ------------------------------ -------------- --- --- --- -------------------
SQL_a7ac813cbf25e65f SQL_PLAN_agb417kzkbtkz479e6372 AUTO-CAPTURE YES YES NO 13-MAY-16 11.06.22.000000 AM
SQL_ef88a476fc38c5af SQL_PLAN_fz254fvy3jjdgc4138c40 MANUAL-LOAD YES YES NO 13-MAY-16 10.57.36.000000 AM
5) Get SQL_ID of the modified SQL
select distinct sql_id, plan_hash_value, sql_text
from v$sql
where sql_text like ‘%USE_INVISIBLE_INDEX%';
SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- -------------------------------------------------------------------------
dtj3d4das6a9a 544808499 SELECT /*+ USE_INVISIBLE_INDEXES INDEX (OKLS IDX_COLL_OKL_S_01) USE_NL ( XICO OKLS )...
Create an Accepted Plan Baseline for the Original SQL Using that of Modified SQL
Now we have two newly created SQL plan baselines, one for the original SQL statement and the other for the modified SQL with hint. And we know the performance of the later is much better than the former. So we want Oracle to use the execution plan from the SQL with hint (modified SQL) when the original SQL is run from the application. To achieve this, we need to create a new SQL plan baseline for the original SQL and make it ACCEPTED. Following PL/SQL block will do the task. Here SQL_ID and PLAN_HASH_VALUE are from modified SQL statement. PLAN_HANDLE is the one of the original SQL, into which the plan baseline should be implemented. Note here we also make this plan baseline FIXED, meaning the optimizer will give preference to it over non-FIXED plans.
set serveroutput on
DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
SQL_ID => 'dtj3d4das6a9a ',
PLAN_HASH_VALUE => 544808499,
SQL_HANDLE => ' SQL_ef88a476fc38c5af ',
FIXED => 'YES',
ENABLED => 'YES');
DBMS_OUTPUT.PUT_LINE ('Plan loaded: '||v_cnt);
END;
/
Now check the SQL plan baselines again to verify a new baseline is indeed created for the original SQL.
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX CREATED
------------------------- ------------------------------ -------------- --- --- --- -------------------
SQL_a7ac813cbf25e65f SQL_PLAN_agb417kzkbtkz479e6372 AUTO-CAPTURE YES YES NO 13-MAY-16 11.06.22.000000 AM
SQL_ef88a476fc38c5af SQL_PLAN_fz254fvy3jjdg479e6372 MANUAL-LOAD YES YES YES 13-MAY-16 11.15.02.000000 AM
SQL_PLAN_fz254fvy3jjdgc4138c40 MANUAL-LOAD YES YES NO 13-MAY-16 10.57.36.000000 AM
Run explain plan for the original SQL statement, you should see following line included in the result:
- SQL plan baseline "SQL_PLAN_fz254fvy3jjdg479e6372" used for this statement
Summary
By using SQL Plan Baseline we can force the optimizer to use the execution plan for a modified SQL (added hint).
