Intermediate SQL Color Coded SQL, UNIX and Database Essays

6Jun/104

ORACLE 11g SQL Plan Management: What exactly is SPM Baseline? Part 2

In the previous post we discussed the WHAT and WHY of ORACLE 11g SQL Plan Management, in this post will will begin discussing the HOW. In my personal experience, in order to understand (and appreciate) any new thing that comes from ORACLE you really need to go down into actual nuts and bolts and see how that thing works.

So, let’s get our hands dirty and go into the actual mechanics of SPM.

What exactly are SPM “Baselines”

If you recall from the previous post, SPM’s goal (which might sound surprising) is NOT to choose the best-cost SQL plan for the query, but rather to prevent potentially worse plans from taking over.

In essence, SPM “freezes” “good” SQL plans and will NOT allow a new plan unless it is VERIFIED to perform better than the currently accepted one (“verified” in this case means, that the new plan has been EXECUTED and its execution statistics are SUPERIOR to the current “good” plan – it is NOT a simple cost comparison).

The way SPM is making that work is by creating SQL Plan Baselines and attaching them to SQL statements. But what exactly are “SPM baselines” ?

Let’s run a test and find out. First, of all, let’s prepare the TEST table. For the later tests, we want it fairly big and so we set PCTFREE to 99 to make it big and empty …

CREATE TABLE t (n, c) NOLOGGING PARALLEL PCTFREE 99 PCTUSED 1
  AS SELECT level, CAST (to_char(level) AS varchar2(10)) FROM dual
  CONNECT BY level <= 1000000
/

SQL> ALTER TABLE t LOGGING NOPARALLEL;

SQL> EXEC dbms_stats.gather_table_stats(USER, 't', degree => 4);

Now, let’s run a simple SELECT that accesses a single record from the table. In the absence of any indexes, the most efficient (actually, the only) plan for this statement is a FULL TABLE SCAN.

SQL> SELECT * FROM t WHERE n=45;

         N C
---------- ----------
        45 45

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"=45)

Ok, now, let’s build an SPM baseline based on this plan.

-- First, let's find out SQL_ID for our statement
SQL> SELECT sql_id FROM v$sql WHERE sql_text='SELECT * FROM t WHERE n=45';

SQL_ID
-------------
1z5x9vpqr5t95

-- Then, let's build the baseline
var nRet NUMBER
EXEC :nRet := dbms_spm.load_plans_from_cursor_cache('1z5x9vpqr5t95');

-- And finally, let's double check that the baseline has really been built
SET linesize 180
colu sql_text format A30

SELECT plan_name, sql_text, optimizer_cost, accepted
FROM dba_sql_plan_baselines
WHERE to_char(sql_text) LIKE 'SELECT * FROM t WHERE n=45'
ORDER BY signature, optimizer_cost
/

PLAN_NAME                      SQL_TEXT                       OPTIMIZER_COST ACC
------------------------------ ------------------------------ -------------- ---
SQL_PLAN_01yu884fpund494ecae5c SELECT * FROM t WHERE n=45              68764 YES

You can probably see one small issue with DBMS_SPM package already – most of its routines are FUNCTIONS (not procedures) and that makes working with the package that tiny bit more complicated …

But let’s move on. Now that we have a baseline in place, let’s run the statement again and see if anything changed:

SQL> SELECT * FROM t WHERE n=45;

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"=45)

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

Aha! Notice that “Note” section at the end. SPM uses our baseline for this statement! (In other words, our FULL TABLE SCAN is a “good plan” now and SPM will protect it).

Now that our SPM baseline has finally been built, we can go ahead and see what it actually consists of. Probably the easiest way to do it is by using DBMS_SPM export facility.

-- First, let's create the "staging" table to store our baselines
SQL> EXEC dbms_spm.create_stgtab_baseline('stage1');

-- And now, let's export them into the staging table
SQL> EXEC :nRet := dbms_spm.pack_stgtab_baseline('stage1',
  sql_text => 'SELECT * FROM t WHERE n=45');

Just by looking at the list of STAGE1 columns, we can have a pretty good idea of what SPM Baseline really is:

But let’s also double check with actual data from our statement:

SET long 1000000
SET longchunksize 30
colu sql_text format a30
colu optimizer_cost format 999,999 heading 'Cost'
colu buffer_gets    format 999,999 heading 'Gets'

SELECT sql_text, OPTIMIZER_COST, CPU_TIME, BUFFER_GETS, COMP_DATA FROM stage1;

SQL_TEXT                       Cost   CPU_TIME     Gets COMP_DATA
-------------------------- -------- ---------- -------- ------------------------------
SELECT * FROM t WHERE n=45   68,764    8840000  500,028 <outline_data><hint><![CDATA[I
                                                        GNORE_OPTIM_EMBEDDED_HINTS]]><
                                                        /hint><hint><![CDATA[OPTIMIZER
                                                        _FEATURES_ENABLE('11.2.0.1')]]
                                                        ></hint><hint><![CDATA[DB_VERS
                                                        ION('11.2.0.1')]]></hint><hint
                                                        ><![CDATA[ALL_ROWS]]></hint><h
                                                        int><![CDATA[OUTLINE_LEAF(@"SE
                                                        L$1"
)]]></hint>

     <hint><![CDATA[FULL(@"SEL$1" "T"@"SEL$1")]]></hint>

                                                        </outline_data>

And here you go. As you can probably see now, SPM Baseline is really nothing more than a collection of hints (this is what “freezes” SQL statement execution) and some supporting information such as estimated optimizer cost (less important) and real execution statistics (more important). Plus, there is, of course, a number of technical details, such as when and how a baseline was built, its current status as well as what was SQL environment at the time.

Pretty simple, huh ? In the next post, we are going to kick it up a notch and see how SPM Baselines actually “get attached” to our SQL statements.

Comments (4) Trackbacks (0)
  1. Excellent Article!!!!!!!!!!!!!!

  2. Very nice article..

  3. Very good information and process made simple for beginers on SPM. Thanks a ton đŸ™‚

  4. Excellent way of describing things. Makes a reader job much simpler.


Leave a comment

No trackbacks yet.