RE: Questions about SQL Plan Management (answered)

From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Thu, 3 Jul 2014 14:37:23 -0700
Message-ID: <BLU179-W20F572D04FD7AF63FEE92CEB010_at_phx.gbl>



I totally get Dom's explanation :1. Do normal optimisation processIs SPM plan phv2?If yes, stopIf not then store then2. Use outline hints for plan (see underlying outline in sqlobj$*)Is SPM plan phv2?If yes stop If not then3. Use OFE hint onlyIs SPM plan phv2 ? If yes, stopIf not, go with best cost plan generated in step 1Per the above explanation, we can assert that SPM does not guarantee plan stability and that the "guarantee" that it offers is no better than was offered by Oracle 8 stored outlines. Subject: Re: Questions about SQL Plan Management From: kerry.osborne_at_enkitec.com
Date: Thu, 3 Jul 2014 16:24:08 -0500
CC: dombrooks_at_hotmail.com; mohamed.houri_at_gmail.com; exriscer_at_gmail.com; jonathan_at_jlcomp.demon.co.uk; oracle-l_at_freelists.org To: iggy_fernandez_at_hotmail.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 OsborneEnkitecblog: kerryosborne.oracle-guy.comtwitter: 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 ACCOUNTINGKING ACCOUNTINGMILLER 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 ACCOUNTINGKING 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_b887b12d701280adSQL 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 ACCOUNTINGKING 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> _at_phv2 db0aubrsu3a9b
SQL_ID PLAN_HASH_VALUE CHILD_NUMBER PHV2------------- --------------- ------------ ----------db0aubrsu3a9b 2213692374 5 3945286981db0aubrsu3a9b 2213692374 4 3945286981db0aubrsu3a9b 2213692374 3 3945286981db0aubrsu3a9b 2213692374 2 3945286981db0aubrsu3a9b 2213692374 1 3945286981db0aubrsu3a9b 2213692374 0 3945286981 Bottom Line : it depends
Best regardsMohamed Houriwww.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).

RegardsJonathan 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-teamVisit My - BlogLet's Connect - Linkedin ProfileMy Twitter - MohamedHouri                                                

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jul 03 2014 - 23:37:23 CEST

Original text of this message