Section 2.3. Soft-Closing Cursors


2.3. Soft-Closing Cursors

When I am building applications, I am a firm believer in extending assumptions in order to squeeze out every little bit of extra performance or throughput. I'm forever asking questionsfor example, if the same Oracle session executes my PL/SQL function repeatedly, why don't I cache some of the setup information instead of querying it each time? Or, if I know that underlying data changes only hourly, why can't I cache the result sets and avoid requerying the database until 60 minutes pass?

One assumption that Oracle itself extends is that once a session uses a cursor, it will eventually reuse iteven if it is explicitly closed. The trickery used to accomplish this feat is known as soft closing, or what I refer to as "the close that's not a close."

Consider this simple example of an implicit cursor.

     SQL> SELECT NULL       2    FROM DUAL;     N     -     1 row selected

The implicit cursor was created, opened, fetched, and closedall in the SELECT statement. So now it should be disassociated with the session that executed it, right? Not so fast. In order to take better advantage of potential reuse, the cursor is soft-closed only within the session to make subsequent reuse by that session faster.

The cursors associated with a particular session are shown in the V$OPEN_CURSOR view . That view includes any cursors currently open as well as those that have been soft-closed. Here's what the view shows for the session querying the DUAL table.

     SQL> SELECT sql_text       2    FROM v$open_cursor       3   WHERE sid = 43;     SQL_TEXT     -------------------------     SELECT NULL   FROM DUAL

This soft-closing occurs whenever cursors are closed explicitly via the CLOSE statement or implicitly when they go out of scope.

To avoid keeping every single cursor in this soft-closed state forever, you can specify a per-session limit in the OPEN_CURSORS database initialization parameter. The list stays current by flushing the least-recently-used entries whenever space is needed for new entries. However, if a session attempts to actually open more than this limit at one time, it will encounter the ORA-01000: maximum open cursors exceeded error.

2.3.1. Explicit and Implicit Open Cursors

The list of open cursors is another area where explicit and implicit cursors are treated differently, as shown in the following example with OPEN_CURSORS set to 20. First, I'll execute several implicit cursors:

     DECLARE       v_dummy varchar2(10);     BEGIN       SELECT 'A' INTO v_dummy  FROM orders;       SELECT 'B' INTO v_dummy  FROM orders;     ...and so on through lower and upper case alphabets...       SELECT 'x' INTO v_dummy  FROM orders;       SELECT 'y' INTO v_dummy  FROM orders;       SELECT 'z' INTO v_dummy  FROM orders;     END;

The session's list of associated cursors now looks like this:

     SQL> SELECT oc.sql_text       2    FROM v$open_cursor oc,       3         v$sql         sq       4   WHERE user_name = 'DRH'       5     AND oc.sql_id = sq.sql_id       6     AND command_type = 3;     SQL_TEXT     ---------------------------------     SELECT 'n' FROM ORDERS     SELECT 'z' FROM ORDERS     SELECT 'o' FROM ORDERS     SELECT 'q' FROM ORDERS     SELECT 'x' FROM ORDERS     SELECT 'l' FROM ORDERS     SELECT 'v' FROM ORDERS     SELECT 's' FROM ORDERS     SELECT 'p' FROM ORDERS     SELECT 'w' FROM ORDERS     SELECT 'm' FROM ORDERS     SELECT 'u' FROM ORDERS     SELECT 'k' FROM ORDERS     SELECT 'j' FROM ORDERS     SELECT 'i' FROM ORDERS     SELECT 'y' FROM ORDERS     SELECT 'r' FROM ORDERS     SELECT 't' FROM ORDERS     SELECT 'h' FROM ORDERS     19 rows selected.

Only the last cursors remained behind as soft-closed. The others were flushed out to make room for newer ones.

Now, I'll execute several explicit cursors by opening and closing each one.

     DECLARE       CURSOR curs_65 IS SELECT 'A' FROM orders;       CURSOR curs_66 IS SELECT 'B' FROM orders;     ...and so on through the lowercase and uppercase alphabets     ...ASCII 65 through 122       CURSOR curs_122 IS SELECT 'z' FROM orders;     BEGIN       OPEN curs_65;       CLOSE curs_65;     ...and so on...       OPEN curs_122;       CLOSE curs_122;     END;

I'll wind up with a list of associated cursors that is very similar to the list I had for the implicit run. But imagine what would happen if I were lazy and decided not to close the explicit cursors, as in this code.

     DECLARE       CURSOR curs_65 IS SELECT 'A' FROM orders;       CURSOR curs_66 IS SELECT 'B' FROM orders;     ...and so on through the lowercase and uppercase case alphabets     ...ASCII 65 through 122       CURSOR curs_122 IS SELECT 'z' FROM orders;     BEGIN       OPEN curs_65;       OPEN curs_66;     ...and so on...       OPEN curs_122;     END;

At or near the 20th cursor, I get this error because the session tried to go beyond its limit of 20 open cursors.

     ERROR at line 1:     ORA-01000: maximum open cursors exceeded     ORA-06512: at line 21     ORA-06512: at line 80

This is one of the reasons why it's important to always close explicit cursors.

So, what's a good setting for the OPEN_CURSORS parameter? That's easy: set it to whatever you need plus one. That may not seem to be a very helpful answer, but there is no tried-and-true method for setting this parameter. If the value is too low, the code won't run because of the ORA-1000 error. If the value is too high, then cursors (explicit and implicit) may remain soft-closed forever. The good news is that the space is not pre-allocated, so setting this value high does not have to factor into your overall memory sizing.

This query provides a good value to use by determining the maximum number of cursors (open or soft-closed) associated with a current session. I run this at regular intervals early in the application's life cycle.

     SYS> SELECT *       2    FROM ( SELECT sid,       3                  COUNT(*)       4             FROM v$open_cursor       5           GROUP BY sid       6           ORDER BY COUNT(*) DESC)       7  WHERE ROWNUM = 1;            SID   COUNT(*)     ---------- ----------             46         20

I then set OPEN_CURSORS to the highest maximum plus a safety buffer of 10 or 20.

2.3.2. Native Dynamic SQL

Native Dynamic SQL (NDS) is also generally able to take advantage of soft-closed cursors and cursor reuse, but it does best when bind variables are used. Consider the following two procedures; they do the same thing except that one uses bind variables while the other uses concatenation.

     CREATE OR REPLACE PROCEDURE bind ( p_on NUMBER ) AS       v_od DATE;     BEGIN       EXECUTE IMMEDIATE 'SELECT order_date ' ||                          ' FROM orders '     ||                         ' WHERE order_number = :v_on'               INTO v_od               USING p_on;     END;     CREATE OR REPLACE PROCEDURE concatenate ( p_on NUMBER ) AS       v_od DATE;     BEGIN       EXECUTE IMMEDIATE 'SELECT order_date ' ||                          ' FROM orders '     ||                         ' WHERE order_number = ' || p_on               INTO v_od;     END;

First, I'll execute the bind version three times:

     SQL> BEGIN       2    FOR counter IN 1..3 LOOP       3      bind(counter);       4    END LOOP;       5  END;       6  /     PL/SQL procedure successfully completed.

The open cursors list shows one very familiar cursor:

     SELECT order_date  FROM orders       WHERE order_number = :v_on

The parse and execution counts are, as expected, 1 and 3:

     SQL_TEXT                       PARSE_CALLS EXECUTIONS     ------------------------------ ----------- ----------     SELECT order_date  FROM orders           1          3       WHERE order_number = :v_on

Now I'll run the concatenation version three times.

     SQL> BEGIN       2    FOR counter IN 1..3 LOOP       3      concatenate(counter);       4    END LOOP;       5  END;       6  /     PL/SQL procedure successfully completed.

The open cursor list looks like this because only the very last one is kept around, hoping for re-execution.

     SQL_TEXT     ------------------------------     SELECT order_date  FROM orders       WHERE order_number = 3

The parse calls and execution counts are interesting. Three separate cursors all parsed once and executed once. That's just plain wasteful.

     SQL_TEXT                       PARSE_CALLS EXECUTIONS     ------------------------------ ----------- ----------     SELECT order_date  FROM orders           1          1       WHERE order_number = 3     SELECT order_date  FROM orders           1          1       WHERE order_number = 1     SELECT order_date  FROM orders           1          1       WHERE order_number = 2

Dynamic SQL is a powerful and handy tool. With just a little forethought, you can ensure that it takes advantage of all of the cursor reuse features of normal PL/SQL.




Oracle PL(s)SQL For DBAs
Oracle PL(s)SQL For DBAs
ISBN: N/A
EAN: N/A
Year: 2005
Pages: 122

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net