RE: Questions about SQL Plan Management
Date: Thu, 3 Jul 2014 12:08:41 +0000
Message-ID: <DUB131-W72735F283140C53CBCD351A1010_at_phx.gbl>
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 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: 2213692374Note----- - 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'));
----------------------------------------------------------------------------------------| 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)
--------------------------------------------------------------------------------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> _at_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_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
- BlogLet's
Connect - Linkedin
Profile
My Twitter - MohamedHouri
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jul 03 2014 - 14:08:41 CEST