Intermediate SQL Color Coded SQL, UNIX and Database Essays

8Jun/102

ORACLE 11g SQL Plan Management: Creating and Using SPM Baselines. Part 3

In the previous post we learned what SPM building blocks or baselines are. In this post, we are going to see how we can get them to work to provide plan stability for our SQL statements.

In a nutshell, SQL Baselines will be used with any SQL statement when the following 3 conditions are met:

  1. SQL Plan Management must be active
  2. SPM Baseline for the SQL must exists (duh!)
  3. SPM Baseline must be ACCEPTED

Let’s look at these conditions in detail.

SQL Plan Management must be active

This is the easiest condition to check:

SQL> SHOW parameter baseline

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
optimizer_capture_sql_plan_baselines BOOLEAN     FALSE
optimizer_use_sql_plan_baselines     BOOLEAN     TRUE

Look at optimizer_use_sql_plan_baselines parameter here. This is the one that enables the use of SQL Plan Management and it is TRUE by default. So, unless, somebody was messing with default parameters in your database, you should be good to go.

SPM Baseline for the SQL must exists

While SPM Baselines are used by default in ORACLE 11g, they are NOT created by default. To create them, you need to do some work first.

There are essentially two ways how SPM baselines can be created.

  • You can either create them explicitly
  • Or you can configure ORACLE to capture them implicitly

In addition to that you can transfer ready made baselines from another database or you can migrate your existing outlines to SPM baselines.

So, let’s get to work and create some baselines.

Creating SPM Baselines: Explicit method

In EXPLICIT method, we tell ORACLE to build SPM Baselines for some specific SQLs (we can select them from the pool of available SQLs).

Here we are dealing with historical data: all SQLs that are available to pick from have already been run and their plan and execution statistics exist and are recorded somewhere (the most common source being the shared pool).

So, now, we can simply take the existing information and make it into SPM Baseline. The following is essentially a repeat from the previous post, but here we go:

SQL> SELECT * FROM t WHERE n=50;
         N C
---------- ----------
        50 50

SQL> SELECT sql_id FROM v$sql
WHERE substr(sql_text, 1, 80) = 'SELECT * FROM t WHERE n=50';

SQL_ID
-------------
5xc932br7nr03

SQL> var nRet NUMBER
SQL> EXEC :nRet := dbms_spm.load_plans_from_cursor_cache('5xc932br7nr03');

PL/SQL PROCEDURE successfully completed.

SQL> SELECT plan_name, sql_text, optimizer_cost, accepted
FROM dba_sql_plan_baselines
WHERE substr(to_char(sql_text), 1, 100) = 'SELECT * FROM t WHERE n=50'
/

PLAN_NAME                      SQL_TEXT                           Cost ACC
------------------------------ ------------------------------ -------- ---
SQL_PLAN_f4mjcchwvatjx94ecae5c SELECT * FROM t WHERE n=50       68,764 YES

Notice a very important piece of information reported by the last query – SPM baseline was created as ACCEPTED (which, in SPM lingo means: IT WILL BE USED). This is true for ALL explicit methods:

EXPLICITLY BUILT baselines are ACCEPTED by default

and therefore future executions of SQLs will use them (more on this later).

Of course, as you might imagine, building SPM baselines “one SQL at a time” is a pain, so ORACLE provided shortcuts to build baselines for:

  • The entire schema
  • Particular module/action
  • All similar SQLs

The way to do it is slightly unorthodox as you need to supply this information in the form of attributes and values:

DECLARE
  nRet NUMBER;
BEGIN
  nRet := dbms_spm.load_plans_from_cursor_cache(
    attribute_name => 'PARSING_SCHEMA_NAME',
    attribute_value => 'TEST_SCHEMA'
  );
END;
/

-- OR
EXEC :nRet := dbms_spm.load_plans_from_cursor_cache('SQL_TEXT', 'SELECT * FROM t%');

Beyond looking at shared pool, you can also (explicitly) build baselines for SQLs in SQL Tuning Set with dbms_spm.load_plans_from_sqlset function.

Creating SPM Baselines: Implicit method

In IMPLICIT method, we configure ORACLE to start building baselines for all new repeatable SQLs. If EXPLICIT method was looking back (at the statements that have already been executed), IMPLICIT method is looking forward (at all new SQLs).

Let’s see how it works.

First of all, to enable implicit SPM baseline “capture”, you need to set parameter optimizer_capture_sql_plan_baselines (which is FALSE by default). You can do it either on system or session level:

SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines=TRUE;

SESSION altered.

After that, SPM baselines will be built on all new SQLs that are repeated at least twice:

SQL> SELECT * FROM t WHERE n=100;

-- This is the FIRST execution of this statement
-- No SPM Baseline is built as SQL is not (yet) repeatable
SQL> SELECT plan_name, sql_text, optimizer_cost, accepted
FROM dba_sql_plan_baselines
WHERE to_char(sql_text) LIKE 'SELECT * FROM t WHERE n=100'
/

no ROWS selected

-- SECOND execution
SQL> SELECT * FROM t WHERE n=100;

-- And now the baseline is built
SQL> SELECT plan_name, sql_text, optimizer_cost, accepted
FROM dba_sql_plan_baselines
WHERE to_char(sql_text) LIKE 'SELECT * FROM t WHERE n=100'
/

PLAN_NAME                      SQL_TEXT                         Cost ACC
------------------------------ ---------------------------- -------- ---
SQL_PLAN_bzj23b6nqwahn94ecae5c SELECT * FROM t WHERE n=100    68,764 YES

-- If we run our statement again, it will use the baseline
SQL> SET autotrace traceonly
SQL> SELECT * FROM t WHERE n=100;

Execution Plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    12 | 68764   (1)| 00:13:46 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    12 | 68764   (1)| 00:13:46 |
--------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   1 - FILTER("N"=100)

Note
-----
   - SQL plan baseline "SQL_PLAN_bzj23b6nqwahn94ecae5c" used FOR this statement

From what we can see here, SPM baseline, as in the case with EXPLICIT method, was also created as ACCEPTED. However there is a twist here, that you need to be aware of:

IMPLICITLY CAPTURED baselines are ACCEPTED only if they are the FIRST baselines for the statement

If SQL baseline already exists and the same SQL is generating a new plan for any reason, a new SPM baseline will be created, but with NOT ACCEPTED status. That means that it WILL NOT BE USED, unless we do something to explicitly enable it.

In the next post I’ll show how this (relatively little known) fact might seriously affect ORACLE behavior with completely surprising consequences.

Comments (2) Trackbacks (0)
  1. As usual really clear article concerning SPM.. this is a good moment since I am in training “New features 11gR2” at Oracle in Paris, the training is awfull, so i am not really into it and i do you example which is much clearer and brighter..

    The way they try to explain keep sending the message that “the CBO” is a mystic beast that no one can understand but we think it works OK so trust me..

    Cheers

  2. JC, I’m not sure that I would be really into ORACLE Training if it was in Paris … Just saying đŸ™‚

    But, you are completely right, of course – there is a tendency (among many developers that I’m talking to) to view ORACLE Optimizer as a kind of a “smart black box” that can take even the most crappy query and magically transform it into a well performing piece of code (which means that you do NOT need to “waste” your time learning how it works). This doesn’t usually work, of course, but this view is nevertheless very persistent …

    I’m surprised though that the training you are attending (is it from ORACLE?) seems to promote it as well …


Leave a comment

No trackbacks yet.