Intermediate SQL Color Coded SQL, UNIX and Database Essays


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:

“Fire” for object in a different schemaYESYES
“Fire” for object with a different DATAYESYES
“Fire” for object with a different STRUCTUREYESYES
“Fire” when indexes are differentYESMAYBE, will “fire” if the same execution plan is still produced
“Fire” when indexes are missingYESNO
“Fire” when TABLE is replaced by VIEWYESNO
“Fire” when TABLE is replaced by MVIEWYESNO

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


What is the difference between SQL Profile and SPM Baseline ?

SQL Profiles

   - SQL profile "SYS_SQLPROF_012ad8267d9c0000" used FOR this statement

and SPM Baselines

   - SQL plan baseline "SQL_PLAN_01yu884fpund494ecae5c" used FOR this statement

are both relatively new features of ORACLE Optimizer with Profiles first appearing in version 10 and SPM Baselines in version 11.

Both SQL Profiles and SPM Baselines are designed to deal with the same problem: Optimizer may sometimes produce a very inefficient execution plan, and they are both doing it by essentially abandoning the idea that “all SQLs are created equal”. Instead, another idea is put forward: “Some SQLs are special and deserve individual treatment”.