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.

Without further ado – here’s the table :

CREATE TABLE cars ( 
    body_type VARCHAR2(30), 
    variant VARCHAR2(30), 
    trim VARCHAR2(10), 
    fuel VARCHAR2(10), 
    metallic_paint VARCHAR2(1), 
    description VARCHAR2(100)) 
/ 

-- 
-- Lazy insert script to populate the cars table 
-- 
DECLARE 
    PROCEDURE ins( p_body VARCHAR2, p_variant VARCHAR2, p_trim VARCHAR2, 
        p_fuel VARCHAR2, p_paint VARCHAR2, p_description VARCHAR2) IS 
    BEGIN 
        INSERT INTO cars( body_type, variant, trim, 
            fuel, metallic_paint, description) 
        VALUES( p_body, p_variant, p_trim, 
            p_fuel, p_paint, p_description); 
    END ins; 
BEGIN 
    ins( 'GOALKEEPER', 'GREEN', 'STANDARD', 
        'DIESEL', 'N', 'Popular in the USA - for when you need a helping hand'); 
    ins( 'GOALKEEPER' ,'JAMES', 'STANDARD', 
        'PETROL', 'Y', 'Enough to make your hair curl'); 
    ins( 'DEFENDER', 'JOHNSON', 'DELUXE', 
        'PETROL', 'N', 'Great going forward but finding reverse is a problem'); 
    ins( 'DEFENDER', 'TERRY', 'STANDARD', 
        'DIESEL', 'N', 'The original Chelsea Tractor'); 
    ins( 'DEFENDER', 'FERDINAND', 'STANDARD', 
        'PETROL', 'N', 'A true World Cup Wind-up'); 
    ins( 'DEFENDER', 'CASHLEY', 'DELUXE', 
        'DIESEL', 'Y', 'Always good for a laugh'); 
    ins( 'MIDFIELDER', 'GERRARD', 'DELUXE', 
        'PETROL', 'Y', 'Great engine. Packs a punch. Soon available in white'); 
    ins( 'MIDFIELDER', 'LAMPARD', 'DELUXE', 
        'PETROL', 'Y', 'The Gerrard but in blue'); 
    ins('MIDFIELDER', 'BARRY', 'STANDARD', 
        'DIESEL', 'N', 'Can be a bit fragile'); 
    ins('MIDFIELDER', 'SWP', 'DELUXE', 
        'PETROL', 'Y', 'Fast but Sat-Nav keeps directing you to cul-de-sacs'); 
    ins('FORWARD', 'ROONEY', 'DELUXE', 
        'PETROL', 'Y', 'Big and green, hangs around with a donkey'); 
    ins('FORWARD', 'HESKEY', 'STANDARD', 
        'DIESEL', 'N', 'Hangs around with Rooney'); 
END; 
/

I'm seeing the therapist on Monday.
A PL/SQL version of the mid-tier code is :

--
-- Script to mimic a mid-tier unbound dynamic query
--
set serveroutput on size 1000000
set verify off
accept body_type prompt 'Enter body_type ( Goalkeeper, Defender etc): '
accept trim prompt 'Enter trim level ( Standard or Deluxe) : '
accept fuel prompt 'Enter fuel type (Petrol or Diesel) : '
accept metallic prompt 'Metallic paint (Y/N) : '
DECLARE
    --
    -- Variables to hold the parameters passed in
    --
    l_type cars.body_type%TYPE :=  UPPER('&body_type');
    l_trim cars.trim%TYPE := UPPER('&trim');
    l_fuel cars.fuel%TYPE := UPPER('&fuel');
    l_metallic cars.metallic_paint%TYPE := UPPER('&metallic');
    --
    -- Variable for the dynamically constructed query
    --
    l_stmnt VARCHAR2(4000);
    --
    -- Variables to get the result set into
    --
    TYPE typ_rs IS RECORD (
    variant cars.variant%TYPE,
    descr cars.description%TYPE);

    TYPE typ_rs_tbl IS TABLE OF typ_rs INDEX BY BINARY_INTEGER;
    tbl_rs typ_rs_tbl;
BEGIN
    --
    -- body_type is mandatory
    --
    l_stmnt := 
        'SELECT variant, description FROM cars WHERE body_type = '||CHR(39)||l_type||CHR(39);
    --
    -- Now add any of the optional parameters that may have been passed in
    --
    IF l_trim IS NOT NULL THEN
        l_stmnt := l_stmnt||' AND trim = '||CHR(39)||l_trim||CHR(39);
    END IF;
    IF l_fuel IS NOT NULL THEN
        l_stmnt := l_stmnt||' AND fuel = '||CHR(39)||l_fuel||CHR(39);
    END IF;
    IF l_metallic IS NOT NULL THEN
        l_stmnt := l_stmnt||' AND metallic_paint = '||CHR(39)||l_metallic||CHR(39);
    END IF;
    EXECUTE IMMEDIATE l_stmnt BULK COLLECT INTO tbl_rs;
    FOR i IN 1..tbl_rs.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Variant - '||tbl_rs(i).variant
            ||' Description - '||tbl_rs(i).descr);
    END LOOP;
END;
/   

Now, lets run it a few times and find out what's happening in the Library Cache :

SQL> ALTER SYSTEM FLUSH shared_pool;

System altered.

SQL> @no_bind.sql
Enter body_type ( Goalkeeper, Defender etc): GOALKEEPER
Enter trim level ( Standard or Deluxe) : 
Enter fuel type (Petrol or Diesel) : 
Metallic paint (Y/N) : 

Variant - GREEN Description - Popular in the USA - for when you need a helping
hand
Variant - JAMES Description - Enough to make your hair curl

PL/SQL procedure successfully completed.

SQL> @no_bind.sql  
Enter body_type ( Goalkeeper, Defender etc): DEFENDER
Enter trim level ( Standard or Deluxe) : 
Enter fuel type (Petrol or Diesel) : 
Metallic paint (Y/N) : 

Variant - JOHNSON Description - Great going forward but finding reverse is a
problem
Variant - TERRY Description - The original Chelsea Tractor
Variant - FERDINAND Description - A true World Cup Wind-up
Variant - CASHLEY Description - Always good for a laugh

PL/SQL procedure successfully completed.

SQL> @no_bind.sql
Enter body_type ( Goalkeeper, Defender etc): MIDFIELDER
Enter trim level ( Standard or Deluxe) : 
Enter fuel type (Petrol or Diesel) : 
Metallic paint (Y/N) : 

Variant - GERRARD Description - Great engine. Packs a punch. Soon available in
white
Variant - LAMPARD Description - The Gerrard but in blue
Variant - BARRY Description - Can be a bit fragile
Variant - SWP Description - Fast but Sat-Nav keeps directing you to cul-de-sacs

PL/SQL procedure successfully completed.

SQL> @no_bind.sql
Enter body_type ( Goalkeeper, Defender etc): FORWARD
Enter trim level ( Standard or Deluxe) : 
Enter fuel type (Petrol or Diesel) : 
Metallic paint (Y/N) : 

Variant - ROONEY Description - Big and green, hangs around with a donkey
Variant - HESKEY Description - Hangs around with Rooney

PL/SQL procedure successfully completed.

SQL> 

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 :

SQL> select value
  2  from v$parameter
  3  where name = 'cursor_sharing';
VALUE
--------------------------------------------------------------------------------
EXACT

SQL> 
SQL> select sql_text, exact_matching_signature
2 from v$sqlarea
3 where sql_text like 'SELECT variant, description FROM cars%';
SQL_TEXT								 EXACT_MATCHING_SIGNATURE
-------------------------------- -----------------------------
SELECT variant, description FROM cars WHERE body_type = 'FORWARD'	17524930329570265193
SELECT variant, description FROM cars WHERE body_type = 'GOALKEEPER'	13687690451161646546
SELECT variant, description FROM cars WHERE body_type = 'DEFENDER'	10182794615026421829
SELECT variant, description FROM cars WHERE body_type = 'MIDFIELDER'	8429543095841042516
SQL>

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) :

set serveroutput on size 1000000
set verify off
accept body_type prompt 'Enter type (Goalkeeper, Defender etc.) : '
accept trim prompt 'Enter trim level ( Standard or Deluxe) : '
DECLARE
    l_type cars.body_type%TYPE := UPPER('&body_type');
    l_trim cars.trim%TYPE := UPPER('&trim');
    l_rs SYS_REFCURSOR;
    --
    -- Variables for the ref cursor elements
    --
    l_variant cars.variant%TYPE;
    l_description cars.description%TYPE;
BEGIN
    pkg_cars.carlist(p_car_type => l_type, p_trim => l_trim,
        p_rs_cars => l_rs);
    LOOP
        FETCH l_rs INTO l_variant, l_description;
        EXIT WHEN l_rs%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE( l_variant||' - '||l_description);
    END LOOP;
END;
/

Now we can flush the shared pool again and re-test :

SQL> ALTER SYSTEM FLUSH shared_pool;

System altered.

SQL> @cartest.sql
Enter type (Goalkeeper, Defender etc.) : GOALKEEPER

GREEN - Popular in the USA - for when you need a helping hand
JAMES - Enough to make your hair curl

PL/SQL procedure successfully completed.

SQL> @cartest.sql

Enter type (Goalkeeper, Defender etc.) : DEFENDER

JOHNSON - Great going forward but finding reverse is a problem
TERRY - The original Chelsea Tractor
FERDINAND - A true World Cup Wind-up
CASHLEY - Always good for a laugh

PL/SQL procedure successfully completed.

SQL> @cartest.sql
Enter type (Goalkeeper, Defender etc.) : MIDFIELDER

GERRARD - Great engine. Packs a punch. Soon available in white
LAMPARD - The Gerrard but in blue
BARRY - Can be a bit fragile
SWP - Fast but Sat-Nav keeps directing you to cul-de-sacs

PL/SQL procedure successfully completed.

SQL> @cartest.sql
Enter type (Goalkeeper, Defender etc.) : FORWARD

ROONEY - Big and green, hangs around with a donkey
HESKEY - Hangs around with Rooney

PL/SQL procedure successfully completed.

SQL> 

What's going on in the cache. Yes, you can probably guess :

SQL> SELECT sql_text,  exact_matching_signature
FROM v$sqlarea
WHERE UPPER(sql_text) like UPPER('SELECT variant, description FROM cars%');

SELECT VARIANT, DESCRIPTION FROM CARS WHERE BODY_TYPE = :B1 	7230859341136377591

If we now invoke another of the overloaded procedures ( the one that accepts trim as a parameter) we'll get :

SELECT VARIANT, DESCRIPTION FROM CARS WHERE BODY_TYPE = :B2 AND TRIM = :B1 	4257494564033239168

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 :

pkg_cars.car_list( p_car_type => l_type, p_trim => l_trim, 
	p_fuel => l_fuel, p_metallic => l_metallic,
            p_rs_cars => l_rs);

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 :

Single Query

Looks like our package is going on a diet :

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_trim IN VARCHAR2 DEFAULT NULL,
        p_fuel IN VARCHAR2 DEFAULT NULL, p_metallic IN VARCHAR2 DEFAULT NULL, p_rs_cars IN OUT ref_cars);
END pkg_cars;
/

CREATE OR REPLACE PACKAGE BODY pkg_cars AS

PROCEDURE carlist( p_car_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 ref_cars) IS
BEGIN
    OPEN p_rs_cars FOR
        SELECT variant, description
        FROM cars
        WHERE body_type = p_car_type
        AND NVL( p_trim, trim) = trim
        AND NVL( p_fuel, fuel) = fuel
        AND NVL( p_metallic, metallic_paint) = metallic_paint;
END;
END pkg_cars;
/

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.

That was easy. Now, to sort out England's midfield conundrum...

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s