Oracle – for when it was like that when you got there

Main menu

Post navigation

Taking the bind out of mid-tier queries

At least it wasn’t penalties. Another straw to clutch at – to paraphrase George W Bush – the Germans have no word for schadenfreude.
On top of that, I was in Sainsburys the day after the game and picked up an England branded top for a mere £4. The good news just keeps on coming.
For anyone not sharing in the English mood of …not so much mourning as a sort of resigned cynicism… this is still a post about Oracle stuff…but with a football theme.

I recently encountered that old classic of a web application in which a SQL query is dynamically constructed on the mid-tier before being fired off at the database.
Despite the query running without any problems all through testing, the effect when it got into Live was fairly dramatic with the database rapidly grinding to a halt.
Before we get to the inevitable bit about the importance of bind variables, via the evident lack of load testing prior to release, let’s get the database agnostic thing out of the way.
In this scenario, the company in question is an Oracle Shop. It’s been an Oracle shop for years and is unlikely to switch to another database in the foreseeable future, not least because of the several million lines of PL/SQL code that would have to be re-written as part of any migration.
I suppose the other thing I should get out of the way now is the fact that this was going to be a simple example using a single table containing details of cars, but got a bit….topical.

As you can see, I’ve only passed the one parameter for each execution as it should be enough to prove the point.
Now we need to look in the library cache. At this point, it’s worth checking that the initialisation parameter cursor_sharing is set to EXACT ( the default). If so, then Oracle is using the exact matching signature of statements to determine whether or not it needs to do a hard parse :

Apologies for the imperfect formatting. However, as we can see from the different exact_matching_ signatures – every time the variables change, the statement needs to be hard parsed.
For an individual statement, the parsing is not a significant overhead. However, it does mean that each statement needs to be loaded into the cache, eventually causing other statements to be aged out. Furthermore, heavy traffic that is typical on a web application causes this to happen a lot. The cache gets flooded with these one time execute statements meaning that everything else needs to get hard parsed, and the databases sinks to it’s knees like a penalty taker watching his spot-kick sail into the back of the stand.
Another point to note is that, each time the query is fired off to the database, there are between 65 and 68 characters being shot across the network. If you specified all of the search criteria, this could be up to 136 characters.
Whilst one lot of 136 bytes isn’t going to be too much of an issue, several thousand is going to start causing some congestion even on the most super of information super highways.

The solution ? Well, I’ve come up with three. There are almost certainly more but each of these demonstrate a different way of tackling the problem on the database itself. All of them address the bind variable issue, as well as offering a significant reduction in network traffic. All of these solutions return REF CURSORS so they shouldn’t require any additional messing about with the mid-tier code to accept the result set.
First up…

Overloaded Procedures

Dynamic SQL can be a real pain to read, so why don’t we just dispense with it altogether ?

CREATE OR REPLACE PACKAGE pkg_cars AS
TYPE rec_cars IS RECORD (
variant cars.variant%TYPE,
description cars.description%TYPE);
TYPE ref_cars IS REF CURSOR RETURN rec_cars;
PROCEDURE carlist( p_car_type IN VARCHAR2, p_rs_cars IN OUT ref_cars);
PROCEDURE carlist( p_car_type IN VARCHAR2, p_trim IN VARCHAR2,
p_rs_cars IN OUT ref_cars);
PROCEDURE carlist( p_car_type IN VARCHAR2, p_trim IN VARCHAR2,
p_fuel IN VARCHAR2, p_rs_cars IN OUT ref_cars);
PROCEDURE carlist( p_car_type IN VARCHAR2, p_trim IN VARCHAR2,
p_fuel IN VARCHAR2, p_metallic IN VARCHAR2, p_rs_cars IN OUT ref_cars);
PROCEDURE carlist( p_car_type IN VARCHAR2, p_trim IN VARCHAR2,
p_metallic IN VARCHAR2, p_rs_cars IN OUT ref_cars);
PROCEDURE carlist( p_car_type IN VARCHAR2, p_fuel IN VARCHAR2,
p_metallic IN VARCHAR2, p_rs_cars IN OUT ref_cars);
PROCEDURE carlist( p_car_type IN VARCHAR2, p_fuel IN VARCHAR2,
p_rs_cars IN OUT ref_cars);
PROCEDURE carlist( p_car_type IN VARCHAR2, p_metallic IN VARCHAR2,
p_rs_cars IN OUT ref_cars);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_cars AS
--
-- Package containing 8 overloaded procedures to account for
-- all possible parameter combinations
--
PROCEDURE carlist( p_car_type IN VARCHAR2, p_rs_cars IN OUT ref_cars) IS
BEGIN
OPEN p_rs_cars FOR
SELECT variant, description
FROM cars
WHERE body_type = p_car_type;
END carlist;
PROCEDURE carlist( p_car_type IN VARCHAR2, p_trim IN VARCHAR2,
p_rs_cars IN OUT ref_cars) IS
BEGIN
OPEN p_rs_cars FOR
SELECT variant, description
FROM cars
WHERE body_type = p_car_type
AND trim = p_trim;
END carlist;
PROCEDURE carlist( p_car_type IN VARCHAR2, p_trim IN VARCHAR2,
p_fuel IN VARCHAR2, p_rs_cars IN OUT ref_cars) IS
BEGIN
OPEN p_rs_cars FOR
SELECT variant, description
FROM cars
WHERE body_type = p_car_type
AND trim = p_trim
AND fuel = p_fuel;
END carlist;
PROCEDURE carlist( p_car_type IN VARCHAR2, p_trim IN VARCHAR2,
p_fuel IN VARCHAR2, p_metallic IN VARCHAR2, p_rs_cars IN OUT ref_cars) IS
BEGIN
OPEN p_rs_cars FOR
SELECT variant, description
FROM cars
WHERE body_type = p_car_type
AND trim = p_trim
AND fuel = p_fuel
AND metallic_paint = p_metallic;
END carlist;
PROCEDURE carlist( p_car_type IN VARCHAR2, p_trim IN VARCHAR2,
p_metallic IN VARCHAR2, p_rs_cars IN OUT ref_cars) IS
BEGIN
OPEN p_rs_cars FOR
SELECT variant, description
FROM cars
WHERE body_type = p_car_type
AND trim = p_trim
AND metallic_paint = p_metallic;
END carlist;
PROCEDURE carlist( p_car_type IN VARCHAR2, p_fuel IN VARCHAR2,
p_metallic IN VARCHAR2, p_rs_cars IN OUT ref_cars) IS
BEGIN
OPEN p_rs_cars FOR
SELECT variant, description
FROM cars
WHERE body_type = p_car_type
AND fuel = p_fuel
AND metallic_paint = p_metallic;
END carlist;
PROCEDURE carlist( p_car_type IN VARCHAR2, p_fuel IN VARCHAR2,
p_rs_cars IN OUT ref_cars) IS
BEGIN
OPEN p_rs_cars FOR
SELECT variant, description
FROM cars
WHERE body_type = p_car_type
AND fuel = p_fuel;
END carlist;
PROCEDURE carlist( p_car_type IN VARCHAR2, p_metallic IN VARCHAR2,
p_rs_cars IN OUT ref_cars) IS
BEGIN
OPEN p_rs_cars FOR
SELECT variant, description
FROM cars
WHERE body_type = p_car_type
AND metallic_paint = p_metallic;
END carlist;
END pkg_cars;
/

Now let’s have a look at what the “mid-tier” code might look like ( shortened to two parameters because I really want to just show the mechanism for collecting data from a REF CURSOR) :

Oh look, only one version of each statement in the cache because it’s got a bind variable. However many times you invoke this package, you’ll only ever get a maximum of eight queries in the cache for it.
Additionally, note the call we now need to make from the mid-tier – 58 characters for the basic query and a maximum of 121 for :

Once again, we’re only saving a few bytes per call, but with thousands of calls, it’s going to make a difference.

On the downside, we have a procedure which is overloaded eight times. Apart from being quite a lot of typing, it’s not exactly that easy to support either. Additionally, if you have more than a few possible parameters, it’s going to mean an awful lot of code.
OK, let’s have a look at the next option :

Note that I’ve put the parameters on the left side of the predicate for emphasis. It’ll work just as well on with them on the right.
It doesn’t matter what parameters you enter, the cost of the query will be the same. Much neater than all that overloading.

What’s that I hear you say ? This is just a silly little example and what happens if things get complex with parameters interacting with each other and you needing to have sub-queries, in-line views and the rest ?
Fair point. Sometimes, the only practical way to go is, indeed ….

Dynamic SQL Solution

At this point, it’s probably relevant to note that we have to use a “weakly typed” REF CURSOR as the IN OUT parameter in the procedure as using a strongly typed one – as in the previous example – gives us a PLS-00455 error at compile time.

CREATE OR REPLACE PACKAGE pkg_cars AS
PROCEDURE carlist( p_body_type IN VARCHAR2, p_trim IN VARCHAR2 DEFAULT NULL,
p_fuel IN VARCHAR2 DEFAULT NULL, p_metallic IN VARCHAR2 DEFAULT NULL, p_rs_cars IN OUT SYS_REFCURSOR);
END pkg_cars;
/
CREATE OR REPLACE PACKAGE BODY pkg_cars IS
PROCEDURE carlist( p_body_type IN VARCHAR2, p_trim IN VARCHAR2 DEFAULT NULL,
p_fuel IN VARCHAR2 DEFAULT NULL, p_metallic IN VARCHAR2 DEFAULT NULL, p_rs_cars IN OUT SYS_REFCURSOR) IS
l_stmnt VARCHAR2( 4000) := 'SELECT variant, description FROM cars WHERE body_type = :type';
l_trim BOOLEAN := FALSE;
l_fuel BOOLEAN := FALSE;
l_metallic BOOLEAN := FALSE;
BEGIN
--
-- Construct the statement
--
IF p_trim IS NOT NULL THEN
l_trim := TRUE;
l_stmnt := l_stmnt||' AND trim = :trim';
END IF;
IF p_fuel IS NOT NULL THEN
l_fuel := TRUE;
l_stmnt := l_stmnt||' AND fuel = :fuel';
END IF;
IF p_metallic IS NOT NULL THEN
l_metallic := TRUE;
l_stmnt := l_stmnt||' AND metallic_paint = :paint';
END IF;
--
-- Now we need to pass the correct parameters as bind variables to the
-- dynamic statement
--
IF l_trim AND l_fuel AND l_metallic THEN
OPEN p_rs_cars FOR l_stmnt USING p_body_type, p_trim, p_fuel, p_metallic;
ELSIF l_trim AND l_fuel AND NOT l_metallic THEN
OPEN p_rs_cars FOR l_stmnt
USING p_body_type, p_trim, p_fuel;
ELSIF l_trim AND l_metallic AND NOT l_fuel THEN
OPEN p_rs_cars FOR l_stmnt
USING p_body_type, p_trim, p_metallic;
ELSIF l_trim AND NOT l_fuel AND NOT l_metallic THEN
OPEN p_rs_cars FOR l_stmnt
USING p_body_type, p_trim;
ELSIF l_fuel AND l_metallic AND NOT l_trim THEN
OPEN p_rs_cars FOR l_stmnt
USING p_body_type, p_fuel, p_metallic;
ELSIF l_fuel AND NOT l_metallic AND NOT l_trim THEN
OPEN p_rs_cars FOR l_stmnt
USING p_body_type, p_fuel;
ELSIF l_metallic AND NOT l_trim AND NOT l_fuel THEN
OPEN p_rs_cars FOR l_stmnt
USING p_body_type, p_metallic;
END IF;
END carlist;
END pkg_cars;
/

So, we’re back to where we started with a bunch of dynamic SQL. The difference is

it’s using bind variables

the network traffic is significantly reduced

the code on the mid-tier is significantly simpler

the database is now admiring the ball nestling in the bottom corner rather than landing in Row Z.