RE: Questions about SQL Plan Management

  • From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
  • To: Dominic Brooks <dombrooks@xxxxxxxxxxx>
  • Date: Thu, 3 Jul 2014 12:59:19 -0700

re: For baselines, reinvoking the optimisation process makes sense because if 
the optimizer comes up with a different plan, it will store in for future 
evaluation/evolution.
The introduction to Maria's paper does say that SPM has two goals (1) plan 
stability (2) finding better plans.
re: It's not until 12c that the actual plan is stored but even then it's just 
for reference I think.
So how does Oracle reproduce the approved plan? Remember that the optimization 
process has already failed to reproduce the approved plan. 
Iggy

Subject: Re: Questions about SQL Plan Management
From: dombrooks@xxxxxxxxxxx
Date: Thu, 3 Jul 2014 20:43:26 +0100
To: iggy_fernandez@xxxxxxxxxxx
CC: mohamed.houri@xxxxxxxxx; exriscer@xxxxxxxxx; jonathan@xxxxxxxxxxxxxxxxxx; 
oracle-l@xxxxxxxxxxxxx

It's not until 12c that the actual plan is stored but even then it's just for 
reference I think.Both baselines and profiles are nothing more than a set of 
hints.The difference being that baselines have to reproduce a specific plan 
hash otherwise they are are not used by the optimizer.Both are stored in 
underlying sys.sqlobj$*
For baselines, reinvoking the optimisation process makes sense because if the 
optimizer comes up with a different plan, it will store in for future 
evaluation/evolution.
For both optimisation makes sense because, for example, an index for the plan 
might have been dropped etc, etc.
At this point, you face an interesting choice between the two mechanisms.For 
the profile, you've got some hints applied to the SQL statement and some not 
(because the index has been dropped) - could make for a particularly ugly 
execution.Whereas the baseline will ignore the plan it couldn't reproduce 
(marking it as not reproducible), go with its normal best cost optimisation and 
store that.





On 3 Jul 2014, at 20:27, "Iggy Fernandez" <iggy_fernandez@xxxxxxxxxxx> wrote:




Thanks for all the insights.
If SPM stores the approved plan in the SPM baseline, then why are the 
transformation and optimization procedures invoked (the 10053 trace proves 
this) every time the query is hard-parsed? Why not simply reuse the approved 
plan; in other words, why not bypass the transformation and optimization 
procedures? (The same question might be asked about stored outlines.)
This suggests that the SPM baseline does not contain all elements of the 
approved plan. If SPM baselines are basically stored outlines, then they don't 
contain access and filter predicates; this could cause the stability guarantee 
to fail. There may be other scenarios which lead to a failure of the stability 
guarantee.
Iggy
P.S. I could not figure out where SPM stores plan details.

From: dombrooks@xxxxxxxxxxx
To: mohamed.houri@xxxxxxxxx; exriscer@xxxxxxxxx; iggy_fernandez@xxxxxxxxxxx; 
jonathan@xxxxxxxxxxxxxxxxxx
CC: oracle-l@xxxxxxxxxxxxx
Subject: RE: Questions about SQL Plan Management
Date: Thu, 3 Jul 2014 12:08:41 +0000




From the evidence below, I would say that the baselined plan is used.

But you could try to argue it both ways.
Is this the exact same plan that we wanted baselined?
Because of the difference in predicates, you could argue not.

What is a baselined plan - a set of hints which reproduce a specific phv2.

Did the hints produce the desired plan id? Yes.

Are the predicates part of that phv2? No.
Does the baselined plan know anything about predicates? No.

Regards,
Dominic

Date: Thu, 3 Jul 2014 13:21:20 +0200
Subject: Re: Questions about SQL Plan Management
From: mohamed.houri@xxxxxxxxx
To: exriscer@xxxxxxxxx
CC: jonathan@xxxxxxxxxxxxxxxxxx; iggy_fernandez@xxxxxxxxxxx; 
oracle-l@xxxxxxxxxxxxx

Iggy,
It all depends on what optimizer parameters the CBO will use when it will be 
reproducing the SPM plan. Will it use the optimizer parameters that corresponds 
to the SPM plan capture time or the optimizer parameters of the current 
execution environments?

The tendency is that the CBO will used the optimizer parameters stored during 
the SPM plan capture time as I have shown in the following blog articles
http://hourim.wordpress.com/2014/02/14/what-optimizer-mode-is-used-during-the-re-cost-of-accepted-spm-plans/
http://hourim.wordpress.com/2014/03/17/spm-reproducibility-changing-the-_optimizer_skip_scan_enabled-value/

But not for the NLS_SORT parameter as shown in the following article
http://hourim.wordpress.com/?s=NLS_LANG

Let me show you another example with transitive closure and 
optimizer_features_enable

SQL> select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SQL> SELECT e.ename,d.dname
  2  FROM emp e,dept d  3  WHERE d.deptno=10  4  AND e.deptno = d.deptno
  5  AND e.deptno = d.deptno; -- I forget to get rid of this
ENAME      DNAME
---------- --------------CLARK      ACCOUNTINGKING       ACCOUNTING
MILLER     ACCOUNTING
SQL> start xsimp

SQL_ID  db0aubrsu3a9b, child number 0-------------------------------------Plan 
hash value: 2213692374
----------------------------------------------------------------------------------------|
 Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| 
Time     |
----------------------------------------------------------------------------------------|
   0 | SELECT STATEMENT             |         |       |       |    14 (100)|    
      |
|   1 |  NESTED LOOPS                |         |     5 |   110 |    14   (0)| 
00:00:01 ||   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     
1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0   (0)|   
       ||*  4 |   TABLE ACCESS FULL          | EMP     |     5 |    45 |    13  
 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------   3 - 
access("D"."DEPTNO"=10)   4 - filter("E"."DEPTNO"=10)

I am going to capture the above plan where transitive closure occurs into a SPM 
plan baseline
SQL> alter session set optimizer_capture_sql_plan_baselines=TRUE;

Session altered.
SQL> SELECT e.ename,d.dname
  2  FROM emp e,dept d  3  WHERE d.deptno=10  4  AND e.deptno = d.deptno
  5  AND e.deptno = d.deptno;
ENAME      DNAME---------- --------------
CLARK      ACCOUNTINGKING       ACCOUNTINGMILLER     ACCOUNTING

SQL> SELECT e.ename,d.dname  2  FROM emp e,dept d
  3  WHERE d.deptno=10  4  AND e.deptno = d.deptno  5  AND e.deptno = d.deptno;

ENAME      DNAME---------- --------------CLARK      ACCOUNTING
KING       ACCOUNTINGMILLER     ACCOUNTING
SQL> alter session set optimizer_capture_sql_plan_baselines=FALSE;

Session altered.
SQL> SELECT e.ename,d.dname
  2  FROM emp e,dept d  3  WHERE d.deptno=10  4  AND e.deptno = d.deptno
  5  AND e.deptno = d.deptno;
ENAME      DNAME---------- --------------
CLARK      ACCOUNTINGKING       ACCOUNTINGMILLER     ACCOUNTING

SQL> start xsimp
SQL_ID  db0aubrsu3a9b, child number 3
-------------------------------------SELECT e.ename,d.dname FROM emp e,dept d 
WHERE d.deptno=10 AND e.deptno
= d.deptno AND e.deptno = d.deptno
Plan hash value: 2213692374

----------------------------------------------------------------------------------------|
 Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| 
Time     |
----------------------------------------------------------------------------------------|
   0 | SELECT STATEMENT             |         |       |       |    14 (100)|    
      |
|   1 |  NESTED LOOPS                |         |     5 |   110 |    14   (0)| 
00:00:01 ||   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     
1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0   (0)|   
       ||*  4 |   TABLE ACCESS FULL          | EMP     |     5 |    45 |    13  
 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------   3 - 
access("D"."DEPTNO"=10)   4 - filter("E"."DEPTNO"=10)

Note-----   - SQL plan baseline SQL_PLAN_bj1xj5ps1505deb284d45 used for this 
statement

We see that the query is protected against a plan change using a SPM baseline. 
The execution plan of the stored SPM plan is:
select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 
'SQL_PLAN_bj1xj5ps1505deb284d45'));

--------------------------------------------------------------------------------SQL
 handle: SQL_b887b12d701280ad
SQL text: SELECT e.ename,d.dname FROM emp e,dept d WHERE d.deptno=10 AND 
e.deptno           = d.deptno AND e.deptno = d.deptno
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_bj1xj5ps1505deb284d45         Plan id: 3945286981Enabled: 
YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
 Plan hash value: 2213692374
 
----------------------------------------------------------------------------------------|
 Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| 
Time     |
----------------------------------------------------------------------------------------|
   0 | SELECT STATEMENT             |         |     5 |   110 |    14   (0)| 
00:00:01 |
|   1 |  NESTED LOOPS                |         |     5 |   110 |    14   (0)| 
00:00:01 ||   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     
1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0   (0)| 
00:00:01 ||*  4 |   TABLE ACCESS FULL          | EMP     |     5 |    45 |    
13   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 Predicate Information (identified by operation id):
---------------------------------------------------   3 - 
access("D"."DEPTNO"=10)   4 - filter("E"."DEPTNO"=10)  

Let me now set the optimizer features to '9.0.1' where transitive closure was 
not possible and see what happens
SQL> alter session set optimizer_features_enable='9.0.1';

Session altered.
And I will show first the plan that the CBO will come up when the SPM is 
disabled

SQL> alter session set optimizer_use_sql_plan_baselines=FALSE;
Session altered.

SQL> SELECT e.ename,d.dname  2  FROM emp e,dept d
  3  WHERE d.deptno=10  4  AND e.deptno = d.deptno  5  AND e.deptno = d.deptno;

ENAME      DNAME---------- --------------CLARK      ACCOUNTING
KING       ACCOUNTINGMILLER     ACCOUNTING
SQL> start xsimp

SQL_ID  db0aubrsu3a9b, child number 4-------------------------------------
Plan hash value: 
2213692374----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| 
Time     
|----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |    14 (100)|   
       ||   1 |  NESTED LOOPS                |         |     2 |    44 |    14  
 (8)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     2  (50)| 
00:00:01 ||*  3 |    INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     
1 (100)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | EMP     |     2 |    18 |    13   (8)| 
00:00:01 
|----------------------------------------------------------------------------------------

Predicate Information (identified by operation 
id):---------------------------------------------------
   3 - access("D"."DEPTNO"=10)   4 - filter(("E"."DEPTNO"=10 AND 
"E"."DEPTNO"="D"."DEPTNO"))

The predicate part shows that transitive closure didn't occur. Now I will 
enable again the SPM 
SQL> alter session set optimizer_use_sql_plan_baselines=TRUE;

Session altered.
SQL> SELECT e.ename,d.dname
  2  FROM emp e,dept d  3  WHERE d.deptno=10  4  AND e.deptno = d.deptno
  5  AND e.deptno = d.deptno;
ENAME      DNAME---------- --------------
CLARK      ACCOUNTINGKING       ACCOUNTINGMILLER     ACCOUNTING

SQL> start xsimp
SQL_ID  db0aubrsu3a9b, child number 5
-------------------------------------
Plan hash value: 2213692374
----------------------------------------------------------------------------------------|
 Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| 
Time     |
----------------------------------------------------------------------------------------|
   0 | SELECT STATEMENT             |         |       |       |    14 (100)|    
      |
|   1 |  NESTED LOOPS                |         |     2 |    44 |    14   (8)| 
00:00:01 ||   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     
2  (50)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     1 (100)| 
00:00:01 ||*  4 |   TABLE ACCESS FULL          | EMP     |     2 |    18 |    
13   (8)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------   3 - 
access("D"."DEPTNO"=10)   4 - filter(("E"."DEPTNO"=10 AND 
"E"."DEPTNO"="D"."DEPTNO"))

Note-----   - SQL plan baseline SQL_PLAN_bj1xj5ps1505deb284d45 used for this 
statement
   It is reporting that the SQL plan baseline has been used while the plan 
shown above doesn't corresponds to the SPM plan where transitive closure occur. 
This is what Jonathan Lewis said earlier (and with a report that the baseline 
was used)

Spot by the way how many child cursor has been produced. And spot also the same 
PHV2 for all the cursors while the predicate part is not the same for all the 
cursors
SQL> @phv2 db0aubrsu3a9b

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER       PHV2------------- 
--------------- ------------ ----------
db0aubrsu3a9b      2213692374            5 3945286981db0aubrsu3a9b      
2213692374            4 3945286981
db0aubrsu3a9b      2213692374            3 3945286981db0aubrsu3a9b      
2213692374            2 3945286981db0aubrsu3a9b      2213692374            1 
3945286981
db0aubrsu3a9b      2213692374            0 3945286981
Bottom Line : it depends

Best regardsMohamed Houriwww.hourim.wordpress.com



2014-07-02 22:01 GMT+02:00 Ls Cheng <exriscer@xxxxxxxxx>:

Hi

NLS_LANG also can  disable SPM. I have captured 10g plan for a few queries with 
ORDER BY and NLS_LANG set to SPANISH, the query with SPM enabled in 11.2.0.4 
with NLS_LANG set to america and the plan was not reproduced, it was because 
NLS_SORT changed from spanish to binary






On Wed, Jul 2, 2014 at 9:49 PM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> 
wrote:















As the old joke goes - it's not that simple.



Query transformation is NOT exclusively done before "the optimization phase", 
there's a continuous feedback loop between transformation and optimization; and 
the plans in the SPM do capture the transformations - though not necessarily in 
a way that is
 immediately visible to the programmer eye, sometimes the transformational 
hints are actually visible, sometimes they are implied by the outline() and 
outline_leaf() hints which show how query blocks have been combined.





I can guarantee that SPM is not foolproof as I have had examples where a query 
has generated an SQL baseline, but the baseline doesn't reproduce the execution 
plan when it is enabled - and that's on the same version, with the same 
statistics, within 30
 seconds, and with a report that the baseline was used.  



In principle I think there are two 'valid' reasons for failure to reproduce: 
(1) bugs, (2) upgrades which introduce a new transformation that has not been 
blocked by the previous SPM - which would allow the SPM to be applied and still 
produce a change
 in plan (this is probably why 12c captures the actual plan, rather than just 
the list of hints, to check if the plan has reproduced).









Regards


Jonathan Lewis

http://jonathanlewis.wordpress.com

@jloracle 






From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf 
of Iggy Fernandez [iggy_fernandez@xxxxxxxxxxx]



Sent: 02 July 2014 19:48

To: oracle-l@xxxxxxxxxxxxx

Subject: Questions about SQL Plan Management







Since cost-based query transformation and rewrites are done before the 
optimization phase and since the plans stored in SQL Plan Management do not 
capture the transformations and rewrites, can we assert
 that SQL Plan Management is not a foolproof way of guaranteeing query plan 
stability?



Also, is query plan stability guaranteed in the absence of transformation and 
rewrites; that is, in the absence of query transformation and rewrites, can we 
assert that the collection of hints stored in SQL Plan Management
 (or in a stored outline) are always sufficient to reproduce the original query 
plan in
all cases?



Iggy














-- 
Houri Mohamed 
Oracle
DBA-Developer-Performance & Tuning 



Member
of Oraworld-team

Visit My      
  - BlogLet's
Connect - Linkedin
Profile



My Twitter      - MohamedHouri

                                                                                
  
                                          

Other related posts: