The troubling global nature of SQL Profiles and SPM Baselines

6Oct/103

The troubling global nature of SQL Profiles and SPM Baselines

Did you know that SQL profiles and SPM baselines collected for SQL statements in one schema can apply to “the same” SQL statements in another schema ?

They do ! And, besides, objects that these SQLs apply to do NOT need to be exactly the same … And I’m not just talking about different data values or different number of records … The objects can have different structure! Or, they can actually be of different type, i.e. views instead of tables …

The only things that matter are: SQL text and object names.

This strange behavior is summarized in the table below:

BEHAVIOR	SQL Profile	SPM Baseline
“Fire” for object in a different schema	YES	YES
“Fire” for object with a different DATA	YES	YES
“Fire” for object with a different STRUCTURE	YES	YES
“Fire” when indexes are different	YES	MAYBE, will “fire” if the same execution plan is still produced
“Fire” when indexes are missing	YES	NO
“Fire” when TABLE is replaced by VIEW	YES	NO
“Fire” when TABLE is replaced by MVIEW	YES	NO

And, if you do not believe me, read on …

Testing global nature of SQL Profiles and SPM Baselines

We have 2 schemas: TEST1 and TEST2 and a very simple table T in schema TEST1.

SQL> DESC t
Name NULL? TYPE
----------------------------------------- -------- ----------------------------
ID NUMBER
CREATED DATE

We also have a very simple index T_CREATED_IDX on the CREATED column.

SQL> CREATE INDEX T_CREATED_IDX ON t (created);

INDEX created.

And we are going to test the following 2 simple SQLs:

-- "SQL Profile test"
SELECT * FROM t WHERE created > sysdate-1;

-- "SPM Baseline test"
SELECT * FROM t
WHERE created BETWEEN DATE '2010-01-01' AND DATE '2010-01-02';

that originally ran in TEST1 and for which we collected a profile and baseline (here is how we collected them):

SQL> SELECT name, sql_text, STATUS FROM dba_sql_profiles
WHERE sql_text LIKE '%created%';

NAME SQL_TEXT STATUS
------------------------------ ------------------------------------------ -------
SYS_SQLPROF_012b7e19ee200000 SELECT * FROM t WHERE created > sysdate-1 ENABLED

SQL> SELECT sql_handle, sql_text, enabled FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%created%';

SQL_HANDLE SQL_TEXT ENABLED
------------------------------ ---------------------------------------- ---------
SYS_SQL_9edf27f784594dfd SELECT * FROM t WHERE created BETWEEN da YES
te '2010-01-01' AND DATE '2010-01-02'

SPM Baseline is directing ORACLE to use T_CREATED_IDX index, while SQL Profile is adjusting estimated cardinality.

Ready to see some spooky stuff ? Lets’ go.

Applying Profiles and Baselines to table in a different schema

The first test is very straightforward. Let’s create “the same” table in a different schema and, to make a point, fill it with “exceptional” data.

CONNECT test2

-- While in the original table, all "CREATED" values were distributed uniformly
-- In this table they are concentrated in only 2 values
SQL> CREATE TABLE t (id, created) AS
SELECT level, DATE '2010-01-01' FROM dual CONNECT BY level <= 10000
UNION ALL
SELECT level, DATE '9999-01-01' FROM dual CONNECT BY level <= 10000
/

TABLE created.

SQL> CREATE INDEX T_CREATED_IDX ON t (created);

INDEX created.

SQL> EXEC dbms_stats.gather_table_stats(USER, 't', cascade => TRUE);

PL/SQL PROCEDURE successfully completed.

Let’s test SQL Profile. In our new table, the following SQL affects half the database records (which means a FULL TABLE SCAN should be used here), but the presence of Profile (look at cardinality!), makes index access more “efficient”.

SQL> SELECT * FROM t WHERE created > sysdate-1;

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 39337072

---------------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_CREATED_IDX | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

2 - access("CREATED">SYSDATE@!-1)

Note
-----
- SQL profile "SYS_SQLPROF_012b7e19ee200000" used FOR this statement

It is the same thing exactly for SPM Baseline, the SQL below affects half the table and yet, because of SPM baseline we are doing INDEX SCAN.

SQL> SELECT * FROM t WHERE created BETWEEN DATE '2010-01-01' AND DATE '2010-01-02';

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 39337072

---------------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5001 | 60012 | 29 (4)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 5001 | 60012 | 29 (4)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_CREATED_IDX | 5001 | | 15 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

2 - access("CREATED">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "CREATED"<=TO_DATE(' 2010-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

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

Table with a different structure

Let’s now make things more interesting. What if we replaced our simple table T with, say, a copy of ALL_OBJECTS ? You can be pretty sure that ALL_OBJECTS is nothing like our original T, but it does have one thing in common: CREATED column.

SQL> DROP TABLE t;

TABLE dropped.

SQL> CREATE TABLE t AS SELECT * FROM all_objects;

TABLE created.

SQL> CREATE INDEX T_CREATED_IDX ON T (created);

INDEX created.

SQL> EXEC dbms_stats.gather_table_stats(USER, 't', cascade => TRUE);

Let’s see if we can still use our SQL Profile and SPM Baseline …

-- Profile
SQL> SELECT * FROM t WHERE created > sysdate-1;

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 39337072

---------------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 88 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 88 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_CREATED_IDX | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

2 - access("CREATED">SYSDATE@!-1)

Note
-----
- SQL profile "SYS_SQLPROF_012b7e19ee200000" used FOR this statement

-- Baseline
SQL> SELECT * FROM t WHERE created BETWEEN DATE '2010-01-01' AND DATE '2010-01-02';

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 39337072

---------------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 440 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 5 | 440 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_CREATED_IDX | 5 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

2 - access("CREATED">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "CREATED"<=TO_DATE(' 2010-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

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

And what do you know ?! Both, profile and baseline ARE still used.

Index with different structure

How about tweaking some indexes ? We have T_CREATED_IDX index on a single date column: CREATED, but nothing prevents us from, say, adding a few more columns to it.

SQL> DROP INDEX T_CREATED_IDX;

INDEX dropped.

SQL> CREATE INDEX T_CREATED_IDX ON t(created, object_name, object_type);

INDEX created.

Can we still use Profiles and Baselines ?

Yes we can ! (c – you know who :0 )

What if we drop CREATED column from this index altogether but still keep index name the same ?

SQL> DROP INDEX T_CREATED_IDX;

INDEX dropped.

SQL> CREATE INDEX T_CREATED_IDX ON t (object_name, object_type);

INDEX created.

SQL> SELECT * FROM t WHERE created > sysdate-1;

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 2153619298

--------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 88 | 23 (9)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 88 | 23 (9)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - FILTER("CREATED">SYSDATE@!-1)

Note
-----
- SQL profile "SYS_SQLPROF_012b7e19ee200000" used FOR this statement

SQL> SELECT * FROM t WHERE created BETWEEN DATE '2010-01-01' AND DATE '2010-01-02';

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 2153619298

--------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 440 | 22 (5)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 5 | 440 | 22 (5)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - FILTER("CREATED"<=TO_DATE(' 2010-01-02 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "CREATED">=TO_DATE(' 2010-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))

And this is the first point of divergence: SQL Profile is still used, while SPM Baseline is not.

The reason for the latter becomes clear when we look at 10053 trace: optimizer, came up with a different plan and it does not match the plan prescribed by the baseline, so it is ignored.

SPM: planId IN plan baseline = 2346181073, planId OF reproduced plan = 2498539100
SPM: failed TO reproduce the plan USING the following info: ...
SPM: generated non-matching plan

Not existing indexes

How about we drop the index altogether ?

SQL> DROP INDEX T_CREATED_IDX;

INDEX dropped.

Can we still use Profile or Baseline ?

SQL> SELECT * FROM t WHERE created > sysdate-1;

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 2153619298

--------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 88 | 23 (9)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 88 | 23 (9)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - FILTER("CREATED">SYSDATE@!-1)

Note
-----
- SQL profile "SYS_SQLPROF_012b7e19ee200000" used FOR this statement

SQL> SELECT * FROM t WHERE created BETWEEN DATE '2010-01-01' AND DATE '2010-01-02';

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 2153619298

--------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 440 | 22 (5)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 5 | 440 | 22 (5)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - FILTER("CREATED"<=TO_DATE(' 2010-01-02 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "CREATED">=TO_DATE(' 2010-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))

As expected, Profile – YES, Baseline – NO.

Replacing table with VIEW

-- First, let's rebuild the index to be "normal" again
SQL> DROP INDEX T_CREATED_IDX;

INDEX dropped.

SQL> CREATE INDEX T_CREATED_IDX ON t (created);

INDEX created.

SQL> ALTER TABLE t RENAME TO t_t;

TABLE altered.

SQL> CREATE VIEW t AS SELECT owner, object_name, object_type, created FROM t_t;

VIEW created.

Can we use Profiles or Baselines ?

SQL> SELECT * FROM t WHERE created > sysdate-1;

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 596770181

---------------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_T | 1 | 40 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_CREATED_IDX | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

2 - access("CREATED">SYSDATE@!-1)

Note
-----
- SQL profile "SYS_SQLPROF_012b7e19ee200000" used FOR this statement

SQL> SELECT * FROM t WHERE created BETWEEN DATE '2010-01-01' AND DATE '2010-01-02';

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 596770181

---------------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 200 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_T | 5 | 200 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_CREATED_IDX | 5 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

2 - access("CREATED">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "CREATED"<=TO_DATE(' 2010-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

And yet again, Profiles – YES, Baselines – NO (same reason, again – Optimizer generated non-matching execution plan).

Replacing table with MVIEW

And finally, let’s replace TABLE with MATERIALIZED VIEW (with the same name).

SQL> DROP VIEW t;

VIEW dropped.

SQL> CREATE MATERIALIZED VIEW t AS SELECT * FROM t_t WHERE rownum <= 10;

Materialized VIEW created.

-- We need to recreate this index on MVIEW now
SQL> DROP INDEX T_CREATED_IDX;

INDEX dropped.

SQL> CREATE INDEX T_CREATED_IDX ON t(created);

INDEX created.

SQL> EXEC dbms_stats.gather_table_stats(USER, 't', cascade => TRUE);

PL/SQL PROCEDURE successfully completed.

What can still be used ?

SQL> SELECT * FROM t WHERE created > sysdate-1;

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 880570111

------------------------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 2 (0)| 00:00:01 |
| 1 | MAT_VIEW ACCESS BY INDEX ROWID| T | 1 | 86 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_CREATED_IDX | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

2 - access("CREATED">SYSDATE@!-1)

Note
-----
- SQL profile "SYS_SQLPROF_012b7e19ee200000" used FOR this statement

SQL> SELECT * FROM t WHERE created BETWEEN DATE '2010-01-01' AND DATE '2010-01-02';

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 205419966

-----------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 172 | 2 (0)| 00:00:01 |
|* 1 | MAT_VIEW ACCESS FULL| T | 2 | 172 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

1 - FILTER("CREATED"<=TO_DATE(' 2010-01-02 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "CREATED">=TO_DATE(' 2010-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))

And once again, Profiles CAN be used while Baselines CAN’T.

The Bottom Line

As we have seen, both Profiles and Baselines do not care about the contents of the object or even the structure of the object when they are “attached”. The only thing that seems to matter is: TEXT OF THE QUERY (by the way, upper/lower case or different number of “spaces” are irrelevant).

As for different supporting objects (namely, indexes), SPM Baselines seem to be a bit smarter than SQL Profiles as they, at least, run some sanity checks before attaching, while SQL Profiles do not seem to do that: As long as Profiles can affect at least some cardinality – they will.

These exercises, of course, seem academic – who in their right mind would, say, create index on completely different columns and give it the same name ?

But the problem is very real – i.e. you can imagine a situation, when, many similar schemas exist in the same database (it could be i.e. TEST and DEVELOPMENT environments or, say many different QA environments). These schemas might have (sometimes wildly) different sizes, but, at the same time, they usually have mostly “the same” objects.

And guess what, Profiles and Baselines collected in one schema DO attach to objects in many unrelated schemas (we have observed this behavior). And the worst part is – they do so silently, triggering a lot of support calls from testers and developers ! (we have observed that behavior as well) …

Just something that you might want to keep an eye on …

Tagged as: 10g, 11g, ORACLE, Problem, SPM, SQL, SQL Profile Leave a comment

Comments (3) Trackbacks (1) ( subscribe to comments on this post )

Jakub
April 27th, 2011 - 08:56

Hi,

i met your post and frankly speaking i am not big fan either profiles or baselines, i find hints as very good solution for a lot of performance troubles.

But one note: there is possible to influence if a session use profile or not using category.

Look for DBMS_SQLTUNE.ALTER_SQL_PROFILE or SQLTUNE_CATEGORY parameter.

Best regards

Jakub.
Maxym Kharchenko
April 27th, 2011 - 09:58

Hello Jakub,

Thanks. Yes, you “categorize” profiles. Unfortunately, there is no such thing for baselines yet.
Jakub
April 28th, 2011 - 08:30

Hello Maxim,

unfortunately there isn’t. I will prefer behaving of baselines more likely as child cursors. Text matching mapping is way into the hell. Btw categorires for profile are just workaround from my point of view but it’s still the way if someone like it (as i said i prefer hints – working well, visible for everyone, delivered with code).

Intermediate SQL Color Coded SQL, UNIX and Database Essays