What exactly is SPM baseline?

Intermediate SQL has a very good article on what constitutes SPM baseline. Reproduced here for your benefit.

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.

Source : http://intermediatesql.com/oracle/what-is-the-difference-between-sql-profile-and-spm-baseline/

Comment