Re: SQL Plan Management and Bind Variables

  • From: Patrick Jolliffe <jolliffe@xxxxxxxxx>
  • To: Stefan Knecht <knecht.stefan@xxxxxxxxx>
  • Date: Tue, 18 Apr 2017 16:19:57 +0800

My bad.  System is running 12.1.0.2.160719 PSU.
Thanks for those document references.
I had a quick scan, nothing jumped out as being relevant to my concern that
it appears that the bind variables seen when plan was discovered are what
are used when it is compared with base plan.
I guess this is a fundamental design decision, it just seems flawed to me,
Regards
Patrick



On 18 April 2017 at 15:20, Stefan Knecht <knecht.stefan@xxxxxxxxx> wrote:

You didn't say which version you are on, but there are notes for most
recent ones, similar to this one:

Things to Consider for 11.2.0.4 to Avoid Problems with SQL Plan Management
(SPM) (Doc ID 2034706.1)

or this one:

Things to Consider for 12.1.0.2 to Avoid Problems with SQL Plan Management
(SPM) (Doc ID 2035898.1)

There are several known issues with SPM

Stefan




On Tue, Apr 18, 2017 at 12:10 PM, Patrick Jolliffe <jolliffe@xxxxxxxxx>
wrote:

Is the whole process of evolution of SQL Plan Baseline evolution
fundamentally flawed with respect to Bind Variables?
As an example, currently I am attempting to evolve two discovered plans
for the same SQL Handle.
I paste below some extracts from the output I am getting, note the base
plan is the same in both cases,
and initially it is calculated as having a cost of 2332 and performing
657 buffer gets,
whereas next time the same plan has a cost of 553, and does 32 buffer
gets.

------------------------------------------------------------
----------------------------------------------------------------
exec :result := dbms_spm.evolve_sql_plan_basel
ine(plan_name=>'SQL_PLAN_328z76rkwtr2k2b06e1fe', verify=>'YES',
commit=>'NO');
-----------------------------




 Base Plan Name     : SQL_PLAN_328z76rkwtr2k6b701ffa
 Baseline Plan


 Plan Id          : 5351


 Plan Hash Value  : 1802510330




-----------------------------


 1  -  (VARCHAR2(32)):  LBG


 2  -  (VARCHAR2(32)):  N


 3  -  (VARCHAR2(32)):  HEUR


 4  -  (VARCHAR2(32)):  PADTEX


 5  -  (NUMBER):        14292401


                    Base Plan                     Test
Plan

                    ----------------------------
----------------------------

 Elapsed Time (s):  .002599
.002271

 CPU Time (s):      .00152
.001293

 Buffer Gets:       657
658

 Optimizer Cost:    2332
2213

 Disk Reads:        0
0

 Direct Writes:     0
0

 Rows Processed:    0
0

 Executions:        10
10

------------------------------------------------------------
----------------------------------------------------------------
------------------------------------------------------------
----------------------------------------------------------------
exec :result := dbms_spm.evolve_sql_plan_basel
ine(plan_name=>'SQL_PLAN_328z76rkwtr2ke9dae8d1', verify=>'YES',
commit=>'NO');
-----------------------------


 Base Plan Name     : SQL_PLAN_328z76rkwtr2k6b701ffa


Baseline Plan


-----------------------------


 Plan Id          : 5353


 Plan Hash Value  : 1802510330


-----------------------------
 1  -  (VARCHAR2(32)):  BEU


 2  -  (VARCHAR2(32)):  H


 3  -  (VARCHAR2(32)):  JFEU


 4  -  (VARCHAR2(32)):  TURRIS


 5  -  (NUMBER):        14284172





-----------------------------
                    Base Plan                     Test
Plan

                    ----------------------------
----------------------------

 Elapsed Time (s):  .000143
.000126

 CPU Time (s):      .000083
.000075

 Buffer Gets:       32
32

 Optimizer Cost:    553
543

 Disk Reads:        0
0

 Direct Writes:     0
0

 Rows Processed:    0
0

 Executions:        10
10

------------------------------------------------------------
----------------------------------------------------------------

My assumption is that the bind variables used for comparison are the bind
variables used when the test plan was captured
(I need to try to find time to reproduce this if someone has not already
done so).
Considering that a frequent use case SQL Plan Management is used to try
to lock in an optimal plan irrespective of bind variable values how is it
then
possible to evaluate whether a discovered plan is better or not?  From
the above observations, it seem that dbms_spm.evolve_sql_plan_baseline
it not suitable
- we need to fall back to either trying to judge manually whether it is
better, or just accepting the plan temporarily and monitoring to see if is
better.
Or am I missing something here?
Regards
Patrick



Other related posts: