Using Cursors for More Than Queries

4. Using Cursors for More Than Queries

As the Oracle database has evolved over time, so has the humble cursor. In addition to providing the performance improvements described in earlier sections, cursor functionality now extends beyond queries, and it is integral to the design and building of applications. This section describes a variety of additional cursor capabilities not explored in previous sections.

Bulk fetching, REF cursors, cursor parameters, and cursor expressions are especially handy tools for DBAs who need ways to investigate and improve the performance of applications. The features discussed here are particularly helpful if you are working with very busy databases where keeping the number of records touched to an absolute minimum is very important. For example, REF cursors can be used to control data access from client applications that may not even be aware of the way that tables are structured. Cursor parameters allow data access to be spread out. (Chapter 3 discusses additional tools for accomplishing this goal.) And cursor expressions (nested cursors) go a long way toward ensuring that only the work that needs to be done actually is done.

4.1. Bulking Up

If you fetch records , one by one, via a PL/SQL loop, you will incur the overhead of context switching between SQL and PL/SQL once per record. This will dramatically increase the elapsed time if you are processing a large number of records. You can reduce the number of context switches by using a bulk fetch (BULK COLLECT INTO) to query records in sets or all at once.

First, here is an example of what I mean by fetching one record at a time:

CREATE OR REPLACE PROCEDURE one_at_a_time AS

CURSOR curs_get_ord IS

SELECT order_number,

order_date

FROM orders

ORDER BY order_number;

v_order_number NUMBER;

v_order_date DATE;

BEGIN

do_something;

END LOOP;

END;
If the ORDERS table contains 100 records, then 100 context switches would occur. Here is the bulk fetch version of the code.

CREATE OR REPLACE PROCEDURE all_at_once AS

CURSOR curs_get_ord IS

SELECT order_number,

order_date

FROM orders

ORDER BY order_number;

TYPE v_number_t IS TABLE OF NUMBER;

TYPE v_date_t IS TABLE OF DATE;

v_order_number v_number_t;

v_order_date v_date_t;
BEGIN

-- get all orders at once

OPEN curs_get_ord;

FETCH curs_get_ord BULK COLLECT INTO v_order_number, v_order_date;

CLOSE curs_get_ord;

-- if any orders were found then loop through the local

-- collections to process them

IF NVL(v_order_number.COUNT,0) > 0 THEN

FOR counter IN v_order_number.FIRST..v_order_number.LAST LOOP

do_something;

END LOOP;

END IF;

There is also another, less obvious advantage to performing bulk fetches: the database does not have to maintain a read-consistent view of the data while the records it retrieves are processed. Let's look again at the previous example. If the mythical DO_SOMETHING procedure took five seconds to process each of the 100 records retrieved from the ORDERS table, Oracle would have to maintain a read-consistent copy of the records in the result set for more than eight minutes. If the ORDERS table is busy with many other DML operations, then the database's rollback segments will be busy keeping a view of the data in synch for the long operation.

The alternative is to query all records into memory right away with a bulk fetch and then process them. This operation also drastically reduces the chances of getting the troublesome ORA-01555 - Snapshot Too Old (Rollback Segment Too Small) error.

Because the bulk fetch feature brings records into session memory, a balance must be struck with session memory limits. If session memory is a concern for your application, then you can use the LIMIT clause to restrict the number of entries queried at one time. For example:

OPEN curs_get_ord;

LOOP

-- get next 1,000 orders

-- if any more orders found then loop through them

IF NVL(v_order_number.COUNT,0) > 0 THEN

FOR counter IN v_order_number.FIRST..v_order_number.LAST LOOP

do_something;

END LOOP;

ELSE

EXIT;

END LOOP;

CLOSE curs_get_ord;
I make frequent use of the bulk fetch feature when querying Oracle's performance (V$) tables because the last thing I want is for the database to do extra work just so I can see, for example, how many reads and writes each session did. Here is the algorithm I follow:

BEGIN

for each session

query session stats for reads and writes

end if

4.2. REF Cursors

REF cursors provide placeholders for eventual real cursors. Using REF cursors, a program may utilize Oracle's cursor features without being terribly explicit about what data is to be accessed until runtime. Here is a really simple example:

CREATE OR REPLACE PROCEDURE ref_curs AS

v_curs SYS_REFCURSOR;

BEGIN

OPEN v_curs FOR 'SELECT order_number ' ||

CLOSE v_curs;

END;
At compile time, Oracle has no idea what the query text will beall it sees is a string variable. But the REF cursor tells it to be ready to provide cursor functionality in some manner.

The most useful application of REF cursors is to provide "black box" data access to other applications with functions building and returning REF cursors as shown here:

CREATE OR REPLACE FUNCTION all_orders ( p_id NUMBER )

RETURN SYS_REFCURSOR IS

v_curs SYS_REFCURSOR;

BEGIN

' FROM orders ' ||

' WHERE order_number = ' || p_id;

RETURN v_curs;

END;
The calling program simply passes an order_number value to the function and is returned access to the underlying data without having to know anything about it beforehand. External applications, such as Microsoft's .NET, can interrogate the returned REF cursor to determine attributes such as column names and datatypes to decide how to display them.

Here is how the all_orders function might be issued from PL/SQL:

DECLARE

v_curs SYS_REFCURSOR;

BEGIN

FETCH v_curs INTO v_order_rec;

IF v_curs%FOUND THEN

DBMS_OUTPUT.PUT_LINE('Found It');

END IF;

CLOSE v_curs;

Strong vs. weak REF cursors

Weakly typed REF cursors can be reused by almost any query because they are not tied to an explicit return structure.

DECLARE

v_curs SYS_REFCURSOR;

OPEN v_curs FOR 'SELECT order_number ' ||

' FROM orders';

CLOSE v_curs;

OPEN v_curs FOR 'SELECT * ' ||

' FROM orders';

CLOSE v_curs;

END;
The actual query provided for the REF cursor winds up being validated, parsed, and held in the System Global Area just like any other cursor.

SQL_TEXT PARSE_CALLS EXECUTIONS

------------------------------ ----------- ----------

SELECT * FROM orders 1 1

SELECT order_number FROM orde 1 1

rs
Note, however, that REF cursors are not soft-closed, so they cannot take advantage of being opened extra-super-duper quickly later on. Thus, REF cursors will not work as fast as normal cursors.

Weakly typed REF cursors will also incur overhead when Oracle figures out the structure of the return set on the fly. So, for the best performance, strongly typed REF cursors should be used whenever possible. Here are some examples of strongly typed REF cursors:

DECLARE

-- type for order records

v_oc v_order_curs;

-- type for order numbers only

TYPE v_order_number_t IS RECORD ( order_number orders.order_number%TYPE );

TYPE v_order_number_curs IS REF CURSOR RETURN v_order_number_t;

v_ocn v_order_number_curs;

OPEN v_ocn FOR SELECT * FROM ORDERS;

*

ERROR at line 10:

PLS-00382: expression is of wrong type

REF cursor attributes

DECLARE

v_on NUMBER;

BEGIN

OPEN v_curs FOR 'SELECT order_number ' ||

FETCH v_curs INTO v_on;

LOOP

EXIT when v_curs%NOTFOUND;

NULL;

FETCH v_curs INTO v_on;

END LOOP;

CLOSE v_curs;

END;

Dynamic data access

CREATE OR REPLACE PROCEDURE order_cancel ( p_sql VARCHAR2 ) IS

v_curs SYS_REFCURSOR;

BEGIN

CLOSE v_curs;

END IF;

BEGIN

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(-20000,'Unable to open cursor

');

END;

CLOSE v_curs;

END;
The order_cancel function could then be executed like this:

BEGIN

WHERE due_date <= TRUNC(SYSDATE)');

END;

4.3. Cursor Parameters

As the examples in the previous section suggest, it is possible to pass cursors as parameters using straight SQL. This can also be done in a SELECT statement using the CURSOR keyword .

SELECT count_valid(CURSOR(SELECT order_number

FROM orders

WHERE processed IS NULL))

FROM dual;

CREATE OR REPLACE FUNCTION count_valid( p_curs SYS_REFCURSOR )

RETURN NUMBER IS

v_on NUMBER;

v_ret_val NUMBER := 0;

BEGIN

LOOP

IF extensive_validation(v_on) THEN

v_ret_val := v_ret_val + 1;

END IF;

END LOOP;

RETURN(v_ret_val);

END;
The SELECT statement is passed right into the function that then loops through the records it returns, validating them and then returning a count of those deemed valid. This results in two cursors in the shared pool and the soft-closed list for the user.

SQL_TEXT

----------------------------------------

SELECT "A2"."ORDER_NUMBER" "ORDER_NUMBER

" FROM "ORDERS" "A2" WHERE "A2"."PROCESS

ED" IS NULL
SELECT count_valid(CURSOR(SELECT order_n

umber FROM o

rders WHERE p

rocessed IS NULL)) FROM dual

4.4. Cursor Expressions

Cursor expressions are essentially nested cursors . When I refer to a "cursor expression," I am not talking about nested subqueries that determine a result set; instead, I am talking about nested queries that return nested result sets. Let me explain with an example.

SELECT order_number,

CURSOR ( SELECT order_line_amt

FROM order_lines ol

WHERE ol.order_number = orders.order_number )

FROM orders;
This query returns a list of orders plus a cursor to find the lines of that order later. Here's how it might be used in a PL/SQL procedure:

/* File on web: nested_cursor.sql */

CREATE OR REPLACE PROCEDURE nested AS
-- cursor to get orders plus a nested cursor

-- to its line amounts

CURSOR curs_orders IS

SELECT order_number,

CURSOR ( SELECT order_line_amt

FROM order_lines ol

WHERE ol.order_number = orders.order_number )

FROM orders;

lines_curs SYS_REFCURSOR; -- for order lines

v_order_id NUMBER;

-- local variables for bulk fetch of lines

TYPE v_number_t IS TABLE OF NUMBER;

v_line_amt v_number_t;
BEGIN
OPEN curs_orders;

FETCH curs_orders INTO v_order_id, lines_curs;

-- for every order...

LOOP

EXIT WHEN curs_orders%NOTFOUND;
-- only process even numbered orders

IF MOD(v_order_id,2) = 0 THEN

-- get all lines for the order at once

FETCH lines_curs BULK COLLECT INTO v_line_amt;

-- loop through the order lines

IF NVL(v_line_amt.COUNT,0) > 0 THEN

FOR counter IN v_line_amt.FIRST..v_line_amt.LAST LOOP

process_lines;

END LOOP;

END IF;
END IF; -- only even numbered orders

FETCH curs_orders INTO v_order_id, lines_curs;

END LOOP; -- every order

CLOSE curs_orders;
END;
Cursor expressions have slightly esoteric syntax, but they offer some advantages. The main advantage is they provide a direct link between logical and physical processing for both the Oracle optimizer and the code itself. The optimizer benefits from being explicitly informed of the physical link between the two tables (ORDERS and ORDER_LINES), so it can make better decisions when it is eventually asked to get order lines. The code itself limits physical work by logically deciding whether or not to even get certain order lines. This avoids querying records only to ignore them later.

What's even more interesting is what is loaded into the SGA after executing the nested procedure against 1,000 orders.

SQL_TEXT PARSE_CALLS EXECUTIONS

------------------------------ ----------- ----------

SELECT ORDER_NUMBER, CURSOR ( 1 1

SELECT ORDER_LINE_AMT FROM ORD

ER_LINES WHERE ORDER_NUMBER =

ORDERS.ORDER_NUMBER ) FROM ORD

ERS
SELECT "A2"."ORDER_LINE_AMT" " 500 500

ORDER_LINE_AMT" FROM "ORDER_LI

NES" "A2" WHERE "A2"."ORDER_NU

MBER"=:CV1$

Notice that the right side of the nested query's WHERE clause was changed to a cursor bind variable. That's how it is linked back to the main cursor. Also notice that the parse and execution counts are at 500 for the second cursorthat's because it executed only the absolutely required 500 times. More importantly, the underlying data was accessed only 500 times.

After the procedure has been run, the only cursor left open for the session is the main one. However, there are actually many more open during execution. You can expose this fact by adding a sleep of 10 seconds to the code and checking V$OPEN_CURSORS while the sleep occurs.

SQL_TEXT

------------------------------

SELECT ORDER_NUMBER, CURSOR (

SELECT ORDER_LINE_AMT FROM ORD

ER_LINES WHERE ORDER_NUMBER =

ORDERS.ORDER_NUMBER ) FROM ORD

ERS
SELECT "A2"."ORDER_LINE_AMT" "

ORDER_LINE_AMT" FROM "ORDER_LI

NES" "A2" WHERE "A2"."ORDER_NU

MBER"=:CV1$

It turns out that 500 of the second cursor will actually be listed as being open before the procedure finishes and closes them (because they have gone out of scope). All 500 of the nested cursors will make use of the already compiled version in the SGA, as you can see by the cursors' ever-increasing parse and execution counts after six runs of the nested procedure.

SQL_TEXT PARSE_CALLS EXECUTIONS

------------------------------ ----------- ----------

SELECT "A2"."ORDER_LINE_AMT" " 3000 3000

ORDER_LINE_AMT" FROM "ORDER_LI

NES" "A2" WHERE :CV1$=:CV1$

As written, however, all 500 will not take advantage of soft-closed cursors. As a matter of fact, they bring me unnecessarily close to my session's maximum OPEN_CURSORS limit. Thus, it's best in such cases to explicitly close the nested cursor when you are finished with it. (This may not be obvious because the nested cursor does not have an explicit open to associate with.) Here is the changed section of code:

-- process only even numbered orders

IF MOD(v_order_id,2) = 0 THEN
-- implcitly opened

FETCH lines_curs BULK COLLECT INTO v_line_amt;

IF NVL(v_line_amt.COUNT,0) > 0 THEN

FOR counter IN v_line_amt.FIRST..v_line_amt.LAST LOOP

Process_lines;

END LOOP;

END IF; -- only even numbered orders
-- close the nested cursor

CLOSE lines_curs;

END IF;
At this point, I'm sure you are wondering if the example I'm using would not be better written as a single cursorperhaps something like this:

SELECT o.order_number,

order_line_amt

FROM orders o,

order_lines ol

WHERE ol.order_number = o.order_number;

Then I could check to see whether the order number is evenly divisible by two in the PL/SQL code. The difference between these two approaches is the number of rows processed during the query. The nested cursor approach shows these values:

SQL_TEXT ROWS_PROCESSED

------------------------------ --------------

SELECT ORDER_NUMBER, CURSOR ( 1000

SELECT ORDER_LINE_AMT FROM ORD

ER_LINES OL WHERE OL.ORDER_NUM

BER = ORDERS.ORDER_NUMBER ) FR

OM ORDERS

SELECT "A2"."ORDER_LINE_AMT" " 5000

ORDER_LINE_AMT" FROM "ORDER_LI

NES" "A2" WHERE "A2"."ORDER_NU

MBER"=:CV1$

On the other hand, the single cursor approach shows these numbers:

SQL_TEXT ROWS_PROCESSED

------------------------------ --------------

SELECT O.ORDER_NUMBER, ORDER_L 10000

INE_AMT FROM ORDERS O, ORDER_L

INES OL WHERE OL.ORDER_NUMBER

= O.ORDER_NUMBER
Four thousand fewer rows had to be processed by Oracle to build the results set(s). That may seem like a small number, but in a busy system, that is 4,000 fewer records for which Oracle has to keep a read-consistent copy for the duration of the query.

Another option would be to add "MOD(order_number,2) = 0" directly to the query, and that is perfectly viable syntactically. However, the Oracle optimizer may choose a query plan geared to returning all of the order lines and then weed out the odd ones in memory. Of course, you could use a function-based index to work around that, but there is overhead there, as well.

Changing it to a single query negates the further benefit of bulk fetching the order lines, as well.

Oracle does not maintain read-committed result sets across nested cursors. Result sets are maintained only between the implicit open and the subsequent close of the nested cursor. The main cursor, however, still experiences full read-committed consistency.

Another equally viable alternative would be two cursors, one to get the orders and one to get the lines. However the optimizer is then forced to treat them as two separate cursors because it has not been told of the link.

1^ Steven Feuerstein, Arup Nanda. Oracle PI/SQl for DBAs, 2005.

Directory: 2015
2015 -> 587 Return function, r i(X) r i(0) r i(1) r i(2) r i(3) 1 0 2 4 6 Thermal Station, I 2 0 1 5 6 3 0 3 5 6 10
2015 -> Fall 2013 Spring 2014 Program Data: Standard 1 Exhibit 4d
2015 -> Police Militarization
2015 -> Medical Radiography Program
2015 -> 2015 ihbb championships: hs history Bowl Round 4 – Prelims First Quarter
2015 -> Desk review yemen
2015 -> Wrtp/big step · 3841 W. Wisconsin Ave., Milwaukee, wi 53208
2015 -> 1. naclo 2015 – North American Computational Linguistics Olympiad
2015 -> Rao bulletin 1 December 2015 html edition this bulletin contains the following articles
2015 -> Volume Ⅴ (2015) On The 2015 China Victory Day Parade

Download 392.79 Kb.

Share with your friends: