SQL Plan Management and Bind Variables

  • From: Patrick Jolliffe <jolliffe@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 18 Apr 2017 13:10:16 +0800

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_baseline(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_baseline(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: