Implicit Cursors are from Venus, Explicit Cursors are from Mars

Domestic bliss. There’s nothing like it. There’s certainly nothing like it in our house, particularly when I indulge in one of my endearing little foibles that is guaranteed to get Deb pouting like Angelina Jolie suffering a nasty reaction to a wasp-sting.
Whether it’s leaving the toilet seat up, or hanging my clothes up on the nearest floor, there are some days where I just can’t seem to do anything right.
Having said that, I must confess that I myself, am not a model of toleration. My own personal pout trigger is a query that looks something like this :

SELECT NVL(COUNT(*), 0)
FROM some_table;

I’ve seen this quite a bit recently, usually in the form of an explicit cursor.
Once I’ve got the rant about this out of my system, I’ll then look at how you might make single-row sub-queries a bit more efficient without ending up knee-deep in implicit cursors.
I’ll also ponder what it actually is that we really know about cursors.

You can count on COUNT

There is absolutely no point in using an NVL with COUNT because it will always return one row, even if the value it returns is zero.
That’s right, one row ! Count them !! ONE !!!
I realise that multiple exclamation marks are seen in some quarters as the sign of a diseased mind, so I shall take a deep breath and demonstrate via the medium of SQL*Plus

SQL> CREATE TABLE nuffin( nada VARCHAR2(1))
  2  /

Table created.

SQL> SELECT * FROM nuffin;

no rows selected

Now, if we set SQL*Plus to report the number of rows returned (even if it’s 1)…

SQL> set feedback 1
SQL> SELECT COUNT(*) FROM nuffin;

 COUNT(*)
---------
        0

1 row selected.

SQL>

NOTE – I normally resist using words like ‘always’ and ‘never’ so if you do have any instance where COUNT does not return one row, then let me know and watch me eat a large helping of humble pie.

Now I’ve got that out of my system, the next question to address is, which is the more efficient medium for a query returning a single row, an implicit cursor or an explicit one ?

What we “know” about Implicit Cursors

Conventional wisdom on implicit cursors is essentially this :

  • the cursor will fetch the first row.
  • if no data is found, the NO_DATA_FOUND exception (ORA-1403) will be raised
  • if a row is found, the cursor will then attempt to fetch a second row
  • if a second row is fetched then the TOO_MANY_ROWS exception (ORA-1422) will be raised

Surely then, this means that an explicit cursor should be used for single-row query. It only returns one row so the extraneous extra fetch is pointless, isn’t it ?
Let’s have a look…

ALTER SESSION SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER='implicit_count';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
DECLARE
        l_count PLS_INTEGER;
BEGIN
        SELECT COUNT(*) INTO l_count 
        FROM nuffin;
END;
/

If you want to play along, the trace file will be output to the USER_DUMP_DEST directory.
You can check the location by running the following query :

SELECT value
FROM v$PARAMETER
WHERE name = 'user_dump_dest';

If you then go to that directory, the tracefile we just generated will have a name ending in ‘implicit_count.trc’. In my case, the file is xe_ora_2206_implicit_count.trc.

Now run it through tkprof…

tkprof xe_ora_2206_implicit_count.trc implicit_count.prf explain=uid/pwd@db sys=no

Now we have a nice readable file called implicit_count.prf.
If we now look at the relevant bit of the file :

SELECT COUNT(*) 
FROM 
 NUFFIN 


call     count       cpu    elapsed       disk      query    current        rows 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
Parse        1      0.00       0.00          0          1          0           0 
Execute      1      0.00       0.00          0          0          0           0 
Fetch        1      0.00       0.00          0          3          0           1 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
total        3      0.00       0.00          0          4          0           1 

We can see that our implicit cursor does only a single fetch. Has the world gone mad ?

The “fact” that an implicit cursor always does a second fetch may indeed either be
(a) no longer true
or
(b) never have been true ever.

Honestly, Father Christmas, the Tooth Fairy, implicit cursors do a second fetch. I feel like all of my illusions are being shattered.

I’d better get the dustpan and brush ready to sweep them up.

So, if the implicit cursor is efficient enough not to waste time with that mythical extra fetch, where does that leave the explicit cursor ?

Meanwhile, back on Venus…

We now know that the implicit cursor is just as efficient. We also “know” that use of an explicit cursor causes a context switch because that’s what everyone says it does.
This efficiency question is offset somewhat by instances where you have to run the same query more than once.
Having the code in an explicit cursor makes it more maintainable. The query is in one place.
So, the question is, how do you balance the need for performance against the need for maintainability ?
Well, one solution is to have your cake and eat it…(just don’t get the crumbs everywhere).

The best of both worlds ?

I’ve knocked up some fairly simple examples to test three different scenarios :

  • Using an explicit cursor
  • Using multiple implicit cursors
  • Using a call to a function where an implicit cursor is executed

I’ve executed each in turn, tracing the output.
The database has been re-started between each run to make sure the results were not skewed by anything hanging around in the cache.
These tests were done on an 11gR2 Enterprise Edition Database I happen to have lying around. Yes,I did tidy it up afterwards.

Before we do anything else, let’s add a few more rows into our table (yes, I’m recycling the one I used earlier – Deb would approve) :

DECLARE
    l_val nuffin.nada%TYPE;
BEGIN
    FOR i IN 1..100000 LOOP
        IF MOD(i, 5) = 0 THEN
            l_val := 'N';
        ELSE
            l_val := 'Y';
        END IF;
        INSERT INTO nuffin(nada) VALUES(l_val);
    END LOOP;
END;
/

Now for the “traditional” explicit cursor approach :

Explicit Cursor

First the code :

CREATE OR REPLACE PACKAGE countme_pkg AS
    PROCEDURE do_something_pr(o_count OUT NUMBER);
END countme_pkg;
/

CREATE OR REPLACE PACKAGE BODY countme_pkg AS
PROCEDURE do_something_pr( o_count OUT NUMBER) IS
    l_before PLS_INTEGER;
    l_after PLS_INTEGER;
    CURSOR c_nuffin IS
        SELECT COUNT(*)
        FROM nuffin;
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    -- call the cursor
    OPEN c_nuffin;
    FETCH c_nuffin INTO l_before;
    CLOSE c_nuffin;
    -- 
    -- flimsy pretext for re-using the cursor
    --
    INSERT INTO nuffin(nada) VALUES('Y');
    --
    -- call the cursor again
    --
    OPEN c_nuffin;
    FETCH c_nuffin INTO l_after;
    CLOSE c_nuffin;
    COMMIT;
    o_count := l_after;
END do_something_pr;
END countme_pkg;
/

One explicit cursor, defined once, called twice. Nice and manageable. But how fast is it ?

ALTER SESSION SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER='explicit';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
set serveroutput on
DECLARE
    l_count NUMBER;
BEGIN
    countme_pkg.do_something_pr( l_count);
    DBMS_OUTPUT.PUT_LINE('Count is '||l_count);
END;
/

If we look at the tkprof output we can see …

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          2          2          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.06         47        396          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.04       0.06         49        398          0           2

Implicit Cursor

Now let’s change the package body so that we’re not using explicit cursors at all :

CREATE OR REPLACE PACKAGE BODY countme_pkg AS
PROCEDURE do_something_pr( o_count OUT NUMBER) IS
    l_before PLS_INTEGER;
    l_after PLS_INTEGER;

    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    --
    -- call cursor
    --
    SELECT COUNT(*) INTO l_before
    FROM nuffin;
    -- 
    -- another spurious insert
    --
    INSERT INTO nuffin(nada) VALUES('Y');
    --
    -- and again
    --
    SELECT COUNT(*) INTO l_after
    FROM nuffin;
    COMMIT;
    o_count := l_after;
END do_something_pr;
END countme_pkg;
/

Re-start the database, run the script ( this time specifying a different TRACEFILE_IDENTIFIER), do the tkprof thing and…


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          2          2          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.04         48        412          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.03       0.04         50        414          0           2

That’s a bit quicker – less cpu and less elapsed time. That’s it then. If you want quick then implicit is the way to go. Oh well, I suppose it’s hardly worth trying the third option ….

The function

Now, apparently this sort of thing would be slower because there is more code for the PL/SQL engine to interpret…

CREATE OR REPLACE PACKAGE BODY countme_pkg AS
FUNCTION get_nuffin_fn RETURN PLS_INTEGER IS
        l_count PLS_INTEGER;
BEGIN
        SELECT COUNT(*) INTO l_count
        FROM nuffin;
        RETURN l_count;
END get_nuffin_fn;

PROCEDURE do_something_pr( o_count OUT NUMBER) IS
        l_before PLS_INTEGER;
        l_after PLS_INTEGER;
        PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
        --
        -- time to get funky
        --
        l_before := get_nuffin_fn;
        -- 
        -- more random DML
        --
        INSERT INTO nuffin(nada) VALUES('Y');
        --
        -- and another function call
        --
        l_after := get_nuffin_fn;
        o_count := l_after;
        COMMIT;
END do_something_pr;
END countme_pkg;
/

Let’s find out if more code really is more work …

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          2          2          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.03         48        412          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.03       0.04         50        414          0           2

Interesting. This is just as fast as the in-line implicit example with the extra bonus of having to do one less parse.
Additionally, with a function, we can replicate the functionality you’d have with an explicit cursor and pass in parameters to be used in the predicate at runtime.
Not only is it fast, but it’s easily maintainable ( cursor code is in one place) and it’s tidy. Deb will be pleased.

Annoying habits of highly famous people

Despite their best efforts, the great and the good of the Oracle world are only human. Sometimes they will give a definite and emphatic answer to a question. They will use absolute terms such as “always” and “never”.
If it’s good enough for them, then it’s good enough for me…

Never take someone’s word for it (especially not mine). Test it yourself on your application running on your system.
The correct answer to any Oracle related performance question will always be “it depends…”

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