SQL Profiles and SQL Baseline what the optimizer uses


sql_baseline

Some weeks ago i posted an article which describes how SQL Baselines effects Adaptive Cursor Sharing (german). In this article i want to show what will happen when you use SQL Profiles while SQL Baselines are active.

First let us configure a database to capture and use SQL Baselines.

SQL> show parameter baselines

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE
optimizer_use_sql_plan_baselines     boolean     TRUE

Each time a SQL statement is drop off, the optimizer first uses the traditional way to build a best cost plan. If the initialization parameter OPTIMIZER_USE_SQL_PLAN_BASELINES is set to TRUE (like above), then before the cost based plan is executed the optimizer will check to see if a SQL plan baseline exists for this statement. To do this, the database creates a SQL signature (which is a unique SQL identifier, which ignores case or white spaces) and compare it with the SQL Baseline. This is the same way Oracle use to check SQL profiles or SQL patches. This sounds like an overhead? But this comparison is done as memory operation and so there should not be any  measurable overhead to any application. If the parameter OPTIMIZER_CAPTURE_SQL_PAN_BASELINES is set to true the database stores all executed Statements in the SQL Management Base. In general, SQL plan baselines are proactive, whereas SQL profiles are reactive.

sql_baseline_schema

So what will happen if SQL Baseline and SQL Profiles exists in the database? To answer this, i will show you a short sample.

I will query same rows from the emp table. For testing i have created a simple non unique index on deptno.

SQL> select empno,ename,job from emp t1 where deptno = 10;

     EMPNO ENAME      JOB
---------- ---------- ---------
      7782 CLARK      MANAGER
      7839 KING       PRESIDENT
      7934 MILLER     CLERK

SQL_ID    55utxfrbncds3, child number 0
-------------------------------------
select empno,ename,job from emp t1 where deptno = 10

Plan hash value: 1614352715

------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |          |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED     | EMP      |     3 |    63 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                       | DEPT_EMP |     3 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=10)

Note
-----
   - SQL plan baseline SQL_PLAN_g9mzurk4v9bjw9da07b3a used for this statement

Above we can see two things. First the Optimizer use an INDEX RANGE SCAN to access the rows. The second thing is that the optimizer uses a PLAN from the SQL Baseline. We can also query the Baseline to gather Information about the stored query.

select sql_handle,sql_text,origin,enabled,accepted,adaptive 
  from dba_sql_plan_baselines 
 where plan_name = 'SQL_PLAN_g9mzurk4v9bjw9da07b3a';

SQL_HANDLE            SQL_TEXT                    ORIGIN        ENABLED ACCEPTED ADAPTIVE
--------------------- --------------------------- ------------- ------- -------- --------
SQL_f4cffabc89b4ae3c  select empno,ename,job      AUTO-CAPTURE  YES     YES      NO
                      from emp where deptno = 10

Now we create a SQL Profile (for example a Developer, which does not know that Baselines are active). In my example i set the Parameter optimizer_index_cost_adj to 10000. The effect would be that the optimizer ignores the DEPT_EMP index on the emp table.

declare
ar_profile_hints sys.sqlprof_attr;
begin
ar_profile_hints := sys.sqlprof_attr('OPT_PARAM(''optimizer_index_cost_adj'' 10000)','OPT_PARAM(''optimizer_index_caching'' 80)');
dbms_sqltune.import_sql_profile(
sql_text => 'select empno,ename,job from emp t1 where deptno = 10',
profile => ar_profile_hints,
category => 'DEFAULT',
name => 'change_emp',
force_match => TRUE
);
end;
/

I re-execute the SQL Query on the emp Table and see what has changed in the execution plan.

SQL_ID    55utxfrbncds3, child number 1
-------------------------------------
select empno,ename,job from emp t1 where deptno = 10

Plan hash value: 1614352715

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time       |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |       |       |   200 (100)  |          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP      |     3 |    63 |   200     (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DEPT_EMP |     3 |       |   100     (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=10)

Note
-----
   - SQL profile change_emp used for this statement
   - SQL plan baseline SQL_PLAN_g9mzurk4v9bjw9da07b3a used for this statement

I will also check again the dba_sql_plan_baselines.

select sql_handle,sql_text,origin,enabled,accepted,adaptive 
  from dba_sql_plan_baselines 
 where plan_name = 'SQL_PLAN_g9mzurk4v9bjw9da07b3a';

SQL_HANDLE            SQL_TEXT                    ORIGIN        ENABLED ACCEPTED ADAPTIVE
--------------------- --------------------------- ------------- ------- -------- --------
SQL_f4cffabc89b4ae3c  select empno,ename,job      AUTO-CAPTURE  YES     YES      NO
                      from emp where deptno = 10

The outcome is that there is no new entry in the view and my origin is still AUTO-CAPTURE. The Value for ORIGIN means from where the Plan is loaded. In my case the Plan was automatically captured. Creating a Profile did not mean that you get a new entry with ORIGIN value MANUAL-TUNE or AUTO-TUNE. For that you must use the SQL Tuning Advisor which would create a new accepted plan and overrides the AUTO-CAPTURE entry. So back to the last execution Plan. The output can be confusing because we now see that the optimizer evaluated the SQL Profile and the SQL Baseline, but what will be used if both options returns different execution plans.

The simple test now is to deactivated the Baseline for the Optimizer and check if the Execution Plan will be changed.

SQL> alter session set optimizer_use_sql_plan_baselines=false;

Session altered

And the execution plan:

SQL_ID    55utxfrbncds3, child number 0
-------------------------------------
select empno,ename,job from emp t1 where deptno = 10

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     3 |    63 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=10)

Note
-----
   - SQL profile change_emp used for this statement

Here is now the excepted Full Table Scan (where the SQL Baseline uses an INDEX RANGE SCAN) and the optimizer use only the SQL Profile.

conclusion
SQL Baseline is a great feature which can help the DBA to get a stable performance on a database. But remember that there are also some pitfalls which the DBA should know. Automatically generated SQL profiles are not a problem, but manually created profile should be checked by a DBA. Please also remember that this features here shown also needs Options Packs for the database.

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s