SQL Plan Management and Bind Variables

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Tue, 18 Apr 2017 13:10:16 +0800
Message-ID: <CABx0cSXF2R3Mpjwg+K37m0V5C0ZXQ4HcpSLk0=0pnYK0Utv8iA_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 18 2017 - 07:10:16 CEST