Scalar Functions Not Behaving as Scalar Functions December 8, 2009

My thanks go to Graeme Hobbs, who I currently have the pleasure of working with, for explaining this to me.

This is all about scalar functions that do not act like scalar functions.

Where I am spending much of my time at the moment, we have an issue with a very simple sql statement. It takes on average 2 buffer gets per execution. How could that be an issue? Well, it is being executed a few thousand times an hour. By “a few thousand” read 50,000 times. Even a simple statement being executed 15 times a second is not good if it is not needed. Especially, as in this case, there is a context switch.

I quickly batted an email to my esteemed colleague Mr Hobbs saying “but this is a scalar. It will be executed once for the statement and the value passed into each execution of the whole query, it can’t be the source of the 50,000 executions an hour”.

Oh woe, for my ignorance has undone me. But it’s OK, Graeme not only took time to explain, but knocked up the following nice demo of WHY this is a problem. And said I could put it on my Blog. What a nice chap.

Like this:

Related

eh, @23:00 local time, call me simple, but…
Anything we can do to prevent the 50000 calls ?
A Deterministic or WNDS type declaration ? (not a pl/sql jockey here, and it _is_ late, but I’m sure something can be done).

Ultimately: why did you need the function there, if the input (and outcome?) is a constant ? Could you not call it before the Qry and feed the result as a bind-var ?

Taking your second point first Piet, well the developer may have thought (like I did) that as it looks like a scalar, it would be treated like a scalar and thus calculated once. And of course, having decided how we think something will work, few of us test that it works that way {why test what you know!?}. Stuff happens. As you point out, it is easy to fix from a code point of view. Not sure about using the pragmas to fix it, I am no longer a decent PL/SQL brain.

So, having found it is broke, can it be fixed? Yes, but it means changing the code…

Insert usual discussion on the issues surrounding finding and fixing the code in a complex development environment..