//
you're reading...
Administration, Oracle, Performance Tuning

Tuning Query without Changing Code by Using SQL Plan Baseline

As a DBA most of us have had such experiences, when tuning a SQL statement we found the only way to improve the performance of the SQL is to change the code. The change can be adding/removing hints, changing the order of joins, removing unnecessary function calls, etc. However in reality we all know the ordeal of changing code in the application, not to mention sometimes it is impossible to change at all (vendor packages). In this post I will describe a working example of forcing the optimizer to change the execution plan of a SQL statement without changing the SQL itself.

The SQL statement in the example is from a large batch process running daily in an Oracle EBS 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. In the following section I will provide step-by-step procedures on how to do this.

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 => '6f8gjyt96an1k_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 = ''6f8gjyt96an1k'' 
                            and plan_hash_value = 1648619337')
           ) p;
DBMS_SQLTUNE.LOAD_SQLSET('6f8gjyt96an1k_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 =>'6f8gjyt96an1k_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
------------- --------------- -------------------------------------------------------------------------

80csy98hrtngm       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 => '80csy98hrtngm ',
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).

Advertisements

About Hong Wang

I am an Oracle DBA, working in Oracle database since version 7.3. Worked in both application development and production support. Many experiences in real world complicate problems and database projects. This blog serves as a collections of notes I write on my database studies as well as issues I encountered/solved. Your comments are well welcome.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: