Scalar Functions Not Behaving as Scalar Functions December 8, 2009
Posted by mwidlake in performance, Uncategorized.Tags: performance, PL/SQL
trackback
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 got a look at the code and it is doing something like this:
HAVING SUM( col.vol_gbp ) >=
my_user.pkg_application_settings.get_number_value( ‘rp157_eric’ )
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.
From Graeme:
******************************************************
Consider package:
CREATE OR REPLACE PACKAGE BODY ghtest IS l_called_count NUMBER := 0; -- Function and procedure implementations FUNCTION f_get_called_count RETURN NUMBER IS BEGIN RETURN( l_called_count ); END; FUNCTION f_call RETURN NUMBER IS BEGIN l_called_count := l_called_count + 1; RETURN 1; END; FUNCTION f_reset RETURN NUMBER IS BEGIN l_called_count := 0; RETURN 1; END; END ghtest;
— nothing up my sleeve
SELECT ghtest.f_get_called_count
FROM DUAL;
F_GET_CALLED_COUNT
——————
0
ie no calls made yet
SQL> SELECT id , COUNT( * ) FROM ( SELECT * FROM MY_USR.tbl_SOME_DATA WHERE ROWNUM < 10 ) GROUP BY id HAVING COUNT( * ) > ghtest.f_call; ID COUNT(*) ---------- ---------- 1624361594 3 SELECT ghtest.f_get_called_count FROM DUAL; F_GET_CALLED_COUNT ------------------ 7
NOTE the counter has gone up to 7 as there were 7 IDs found from that code -6 IDs with one record and one with 3.
SELECT ghtest.f_reset
FROM DUAL;
F_RESET
———-
1
That just blanks the counter (see the package).
-- nothing up my sleeve SELECT ghtest.f_get_called_count FROM DUAL; F_GET_CALLED_COUNT ------------------ 0 SELECT id , COUNT( * ) FROM ( SELECT * FROM my_usr.tbl_some_data WHERE ROWNUM < 10 ) GROUP BY id HAVING COUNT( * ) > (SELECT ghtest.f_call FROM dual); ID COUNT(*) ---------- ---------- 1624361594 3 SELECT ghtest.f_get_called_count FROM DUAL; F_GET_CALLED_COUNT ------------------ 1
You can see that the scalar subquery
HAVING COUNT( * ) > (SELECT ghtest.f_call FROM dual);
calls f_call once.
Whereas
HAVING COUNT( * ) > ghtest.f_call;
results in it being called 7 times – the number of rows returned from the group by
That is precisely the problem.
******************************************************
Apparently our developers should know this, as it is a “known issue”. But not known to me until now (unless I knew it and forgot it)
So, hats of to Graeme.
{apologies, wordpress utterly screwed the layout/truncated text when I pasted in the example, have fixed now}
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..
[…] Martin Widlake-Scalar Functions Not Behaving as Scalar Functions […]