Re: Questions about SQL Plan Management
Date: Thu, 3 Jul 2014 16:24:08 -0500
Message-Id: <96068ABA-110D-4C66-87FC-2EDC0D0DD0CA_at_enkitec.com>
Maria's paper is overly simplified. What Dominic and others are telling you is correct.
Baselines are essentially a set of hints that the optimizer may apply in order to get a specific plan hash value. If the PHV can't be reproduced, the optimizer goes with a plan generated without the outline hints. Since the sql plan hash value does not depend on predicates, baselines won't help if the PHV remains constant but the predicates are changing. The plan stored in 12c is not used to reproduce the plan, but is used to show what the plan looked like when the baseline was created (in 11g, you couldn't see what the plan looked like when the baseline was created after it becomes non-reproducible)
Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com
twitter: https://twitter.com/KerryOracleGuy
On Jul 3, 2014, at 2:47 PM, Iggy Fernandez wrote:
> Refer to figure 6 on page 11 of Maria Colgan's white paper (http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf).
>
> Step 1: First the CBO is invoked (presumably using the baseline as an input)
> Step 2: The generated plan is compared with the approved plan. If the generated plan is different then the approved plan is executed
>
> The question is whether an SMP baseline is the complete plan, not just a collection of hints. If it was complete, then why perform Step 1 And why should Step 1 fail to reproduce the approved plan?
>
> Iggy
>
>
> From: iggy_fernandez_at_hotmail.com
> To: dombrooks_at_hotmail.com; mohamed.houri_at_gmail.com; exriscer_at_gmail.com; jonathan_at_jlcomp.demon.co.uk
> CC: oracle-l_at_freelists.org
> Subject: RE: Questions about SQL Plan Management
> Date: Thu, 3 Jul 2014 12:27:18 -0700
>
> 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_at_hotmail.com
> To: mohamed.houri_at_gmail.com; exriscer_at_gmail.com; iggy_fernandez_at_hotmail.com; jonathan_at_jlcomp.demon.co.uk
> CC: oracle-l_at_freelists.org
> 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_at_gmail.com
> To: exriscer_at_gmail.com
> CC: jonathan_at_jlcomp.demon.co.uk; iggy_fernandez_at_hotmail.com; oracle-l_at_freelists.org
>
> 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> _at_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_at_gmail.com>:
> 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_at_jlcomp.demon.co.uk> 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
> _at_jloracle
> From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Iggy Fernandez [iggy_fernandez_at_hotmail.com]
> Sent: 02 July 2014 19:48
> To: oracle-l_at_freelists.org
> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 03 2014 - 23:24:08 CEST