Re: Questions about SQL Plan Management
- From: Dominic Brooks <dombrooks@xxxxxxxxxxx>
- To: "iggy_fernandez@xxxxxxxxxxx" <iggy_fernandez@xxxxxxxxxxx>
- Date: Thu, 3 Jul 2014 21:19:29 +0100
In SPM we have the set of outline hints and the plan id which translates to the
plan_hash_2 from v$sql_plan.other_xml.
The optimise process is along the lines of
1. Do normal optimisation process
Is SPM plan phv2?
If yes, stop
If not then store then
2. Use outline hints for plan (see underlying outline in sqlobj$*)
Is SPM plan phv2?
If yes stop
If not then
3. Use OFE hint only
Is SPM plan phv2 ?
If yes, stop
If not, go with best cost plan generated in step 1
> On 3 Jul 2014, at 21:01, "Iggy Fernandez" <iggy_fernandez@xxxxxxxxxxx> wrote:
>
> 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 ACCOUNTING
> KING 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 ACCOUNTING
> KING ACCOUNTING
> MILLER 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 ACCOUNTING
> MILLER 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 ACCOUNTING
> KING ACCOUNTING
> MILLER 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: 3945286981
> Enabled: 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 ACCOUNTING
> MILLER 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 ACCOUNTING
> KING ACCOUNTING
> MILLER 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 3945286981
> db0aubrsu3a9b 2213692374 4 3945286981
> db0aubrsu3a9b 2213692374 3 3945286981
> db0aubrsu3a9b 2213692374 2 3945286981
> db0aubrsu3a9b 2213692374 1 3945286981
> db0aubrsu3a9b 2213692374 0 3945286981
>
> Bottom Line : it depends
>
> Best regards
> Mohamed Houri
> www.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 - Blog
>
> Let's Connect - Linkedin Profile
>
> My Twitter - MohamedHouri
>
Other related posts:
