SPM and schema virtualization

I received a lot of feedback and a fair number of good questions on my session on SQL Plan Management for the Virtual Technology Summit. Some of the questions deserved additional research and a more detailed answer than I could give in the live Q&A and I hope I can add most if not all of these issues to this blog eventually. A lot of you have also asked for the slides and i uploaded the pdf here in case you have trouble pulling them from the event site. Here is the first one:

Why do you say you need to have exactly the same objects in all schemas when doing schema level virtualization?

SPM prerequesite slideI made that point without much of an explanation. So here is how I discovered this issue: We host a number of different schemas of basically the same application in the same database in different schemas. Most of the tables will be the same but one schema may have more data than another and since we did not care about this very much when we first started this, not all tables have the same indexes. Most of them have indexes on the same columns but they may not have the same name in all schemas. So the “same” index may be called differently in different schemas. This happens easily if you don’t name your indexes, especially the ones created implicitly for primary keys or unique constraints.
SPM will be valid across all schemas. So if you collect and accept a baseline in one schema and another user issues the same SQL against their tables, they will use the baselines aswell. And this can be a good or a bad thing depending on how you see this. If you want to quarantee that SQL will use the same plans against all your schemas, this is something you want. BUT this breaks when a baseline uses an index that has a different name in a different schema. The plan hash value will be different in that case and the plan that the optimizer chose will look different from the one that is stored in the baseline. So it will fall back to one of the baseline plans that is acutally reproducable.

If my words do not make sense, here is an example. The first steps are exactly the same as described in the HOL post I wrote for OTN, except for the last step. Do not delete the baseline with the full scan, and this will leave us in a state where we have two accepted baselines for the example query. One uses a full scan, the other one uses an index range scan on the index called ‘T_IDX’. Now create another user/schema:

PDB1@ORCL> create user pmuser2 identified by oracle;

User created.

PDB1@ORCL> grant dba to pmuser2;

Grant succeeded.

Connect with that user and create a table and an index. We will give it the same name as in the original schema and observe that we will use the baseline SQL when querying the table.

[oracle@localhost ~]$ sqlplus pmuser2/oracle

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 10 12:24:44 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

PDB1@ORCL> create table t as select * from dba_objects;

Table created.

PDB1@ORCL> create index t_idx on t (owner);

Index created.

PDB1@ORCL> set autotrace on
PDB1@ORCL> variable var42 varchar2(42);
PDB1@ORCL> exec :var42 := 'PMUSER';

PL/SQL procedure successfully completed.

PDB1@ORCL> select count(*) from t where owner= :var42;

  COUNT(*)
----------
	 6


Execution Plan
----------------------------------------------------------
Plan hash value: 293504097

---------------------------------------------------------------------------
| Id  | Operation	  | Name  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	  |	1 |	6 |	8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |	  |	1 |	6 |	       |	  |
|*  2 |   INDEX RANGE SCAN| T_IDX |  3039 | 18234 |	8   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"=:VAR42)

Note
-----
   - SQL plan baseline "SQL_PLAN_arrxanznkdmsaded8ae2f" used for this statement

So now let’s drop that index, recreate it with a different name and see what happens:

PDB1@ORCL> drop index t_idx;

Index dropped.

PDB1@ORCL> create index other_t_idx on t (owner);

Index created.

PDB1@ORCL> select count(*) from t where owner= :var42;

  COUNT(*)
----------
	 6


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |	6 |   427   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	  |	1 |	6 |	       |	  |
|*  2 |   TABLE ACCESS FULL| T	  |  3039 | 18234 |   427   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"=:VAR42)

Note
-----
   - SQL plan baseline "SQL_PLAN_arrxanznkdmsa3fdbb376" used for this statement

So what happened here? The optimizer _wanted_ to use a plan that was doing a range scan on the new index other_t_idx but the hash value of that plan did not match with the ones in the accepted baselines. It then had to fall back to one of the accepted baselines of which only one was actually reproducable because the index T_IDX does not exist in this schema. The new plan with the range scan on OTHER_T_IDX was added as a new baseline but has a status of unaccapted. You should be aware of this. Of course you can evolve the new baseline, but before we try that I want to show you another little oddity. Let’s drop the baseline with the full scan because we really don’t want to use it anyway, then re-run the query and see what happens:

PDB1@ORCL> declare 
 drop_result pls_integer;
 begin 
 drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE( 
 sql_handle => 'SQL_abdfaaa7e926cf0a',  
 plan_name => 'SQL_PLAN_arrxanznkdmsa3fdbb376'); 
 dbms_output.put_line(drop_result);    
 end; 
/  

PL/SQL procedure successfully completed.

PDB1@ORCL> select count(*) from t where owner= :var42;

  COUNT(*)
----------
	 6


Execution Plan
----------------------------------------------------------
Plan hash value: 2948344740

--------------------------------------------------------------------------------
| Id  | Operation	  | Name	| Rows	| Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |		|     1 |     6 |     8   (0)| 00:00:01|
|   1 |  SORT AGGREGATE   |		|     1 |     6 |	     |         |
|*  2 |   INDEX RANGE SCAN| OTHER_T_IDX |  3039 | 18234 |     8   (0)| 00:00:01|
---------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"=:VAR42)

So in this case, no baseline was used at all even though there is an accepted baseline for this SQL. But since it cannot be used, the system falls back to not using a baseline at all.

PDB1@ORCL> set serveroutput on
declare evolve_out CLOB;
begin
evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_abdfaaa7e926cf0a', COMMIT => 'YES' ); 
dbms_output.put_line(evolve_out); 
end; 
/

GENERAL INFORMATION
SECTION
------------------------------------------------------------------------


 Task Information:			       

--------------------------------------------- 
 Task Name	      : TASK_81
 Task Owner	      : PMUSER2 	    
 Execution Name       : EXEC_191
 Execution Type       : SPM EVOLVE	    
 Scope		      : COMPREHENSIVE	    
 Status 	      : COMPLETED	    
 Started              : 07/10/2014 12:36:44 
 Finished	      : 07/10/2014 12:36:45 
 Last Updated         : 07/10/2014 12:36:45 
 Global Time Limit    : 2147483646
 Per-Plan Time Limit  : UNUSED		    
 Number of Errors     : 0

--------------------------------------------------------------------------


SUMMARY SECTION
------------------------------------------------------------------------

  Number of plans processed  : 1  
  Number of findings         : 2  
  Number of recommendations  : 1  
  Number of errors	     : 0

------------------------------------------------------------------------


DETAILS
SECTION
------------------------------------------------------------------------

 Object ID	    : 2
 Test Plan Name     : SQL_PLAN_arrxanznkdmsae8cba257
 Base Plan Name     : Cost-based plan
 SQL Handle	    : SQL_abdfaaa7e926cf0a
 Parsing Schema     : PMUSER2
 Test Plan Creator  : PMUSER2
 SQL Text	    : select count(*) from t where owner= :var42


Bind Variables:
-----------------------------
 1  -  (VARCHAR2(128)):  PMUSER

FINDINGS
SECTION
--------------------------------------------------------------------------

Findings (2):
-----------------------------
 1. None of the accepted plans were reproducible.				    
 2. The plan was automatically accepted.

Recommendation:
-----------------------------
 Consider accepting the plan.

----------------------------------------------------------------------------

So since the baseline plan was not reproducible in this context (schema pmuser2), the new one was accepted automatically.

If this all sounds confusing, remember two things:
– If you have tables with the same names in different schemas, SPM will be used across those schemas for the same SQL
– If you want to save yourself from some extra SPM complexity, just name all indexes across the schemas the same

One thought on “SPM and schema virtualization

Leave a Reply

Your email address will not be published. Required fields are marked *