Add new comment

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 11.2.0.3 for issues when Bind variable in HAVING clause.