HAVING & Bind Variables
Had an odd one come across my desk today. I have seen Bind variables used in the WHERE clause thousands of times. I have seen Binds in the SELECT lists hundreds of times. I have seen it used in the HAVING clause zero times... until today when it turns out it was causing a performance issue.
With the Bind variable in the HAVING clause we were seeing an execution plan with lots of Full Table Scans and 12+ second runtimes. When editing the query and using a literal in the HAVING clause we saw 100ms or less performance and very efficient Execution Plans.
I chased down Bind Variable datatypes to make sure no implicit conversions were sneaking in and wrecking my execution plans. Found nothing there so continued with a 10053 trace. The 10053 trace for the Bind in HAVING was 4X larger than the one with the literal in the HAVING. I won't go into a full description here, but I can tell you the Bind/HAVING scenario was causing the optimizer to at least CONSIDER a lot more possibilities when it parsed the query.
The only difference in HAVING processing I could find was below (see highlighted line):
HAVING clause with bind variable in expression:
HAVING selectivity: 6.9706e-05 -> GROUPS: 1 SORT resource Sort statistics Sort width: 178 Area size: 156672 Max Area size: 31457280 Degree: 1 Blocks to Sort: 16 Row size: 258 Total Rows: 496 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 10995455 Total Temp space used: 0
HAVING clause with literal expression:
HAVING selectivity: 6.9706e-05 -> GROUPS: 1 SORT resource Sort statistics Sort width: 178 Area size: 156672 Max Area size: 31457280 Degree: 1 Blocks to Sort: 1 Row size: 271 Total Rows: 1 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 10795355 Total Temp space used: 0
I rewrote the statements to logical equivalents that kept the 2 bind variables but eliminated the HAVING clause. They all now run in < 100ms.
I had hoped to find a nice big fat smoking gun in the 10053 trace files but at least I did confirm that the anaysis of the HAVING with and without the Bind variable was different and the CBO expected to handle them differently in each case.
Update 1: 30-NOV-2011: I opened an SR with Oracle to see if they can shed any light on the subject. The only bugs I found on Metalink had to do with the formatting of expressions in the HAVING clause... not the presence of bind variables. Will report back what I find.
Update 2: 30-NOV-2011: Looks like the CBO is broken is a new exciting way. Bug 13448126 verified on 10.2.0.4 and 220.127.116.11 for issues when Bind variable in HAVING clause.