Cursors, Confused Again!

Who Does What When?

This article takes a look at explicit cursor operations in PL/SQL, focusing particularly on what is being done when. Let me begin with a quote from the Oracle PL/SQL Reference Manual: "Opening the cursor executes the query and identifies the active set, which consists of all rows that meet the query search criteria. For cursors declared using the FOR UPDATE clause, the OPEN statement also locks these rows."
Sounds like an awful lot of work takes place at the time the cursor is opened, doesn't it? Over the past few years, a number of PL/SQL developers have sent me email expressing doubts about that "statement of work." Very specifically, they want to know if their program really does take a hit at the time the cursor opens or if the hit occurs at the first fetch.
I hate having to answer, "Gee, I am not really sure" - after all, I have a reputation to maintain, at least when it comes to PL/SQL. But I wasn't really sure. I read the manual - and I repeated those statements without plagiarizing even a little bit in my own book - but I couldn't really say that I knew for certain. I also have had enough experience with PL/SQL and Oracle software in general to realize that every piece of "book knowledge" must be backed up with "try knowledge." In other words, forget what the books say. Try it yourself to make sure.
So I decided to find out for myself precisely and conclusively which cursor operation does what when, to find out (cutting to the chase) when our programs take the hit for processing cursor-based data. The purpose of this article is twofold:

1. To report on the results of my test so that you are well informed about how PL/SQL and the underlying Oracle Server engine processes data.
2. To show you how to proceed step by step in performing this type of analysis so that you can do the same kind of thing for your own application-specific situations. What tools can you use, and what tools are widely available? What are the different situations for which you have to check?

The Explicit Cursor

Just in case you are not very familiar with explicit cursors in PL/SQL, here is a quick review:
An explicit PL/SQL cursor gives you a way to fetch and process database information in your PL/SQL program - one row at a time. When you execute a SQL statement from PL/SQL, the Oracle RDBMS assigns a private work area for that statement. This work area contains information about the SQL statement and the set of data returned or affected by that statement (the "result set" of the query). The PL/SQL cursor is a mechanism by which you can name that work area and manipulate the information within it.
In its simplest form, you can think of a cursor as a pointer into a table in the database. For example, the following cursor declaration associates the entire employee table with the cursor named employee_cur:

CURSOR employee_cur IS
SELECT * FROM employee;

Once I have declared the cursor, I can open it:

OPEN employee_cur;

And then I can fetch rows from it and, finally, close the cursor:

FETCH employee_cur
INTO employee_rec;
CLOSE employee_cur;

In this case, each record fetched from this cursor represents an entire record in the employee table. You can, however, associate any valid SELECT statement with a cursor. In the example shown in Listing 1 , I have a join of three tables in my cursor declaration.
In this example, the cursor does not act as a pointer into any actual table in the database. Instead, the cursor is a pointer into the virtual table represented by the SELECT statement (SELECT is called a virtual table because the data it produces has the same structure as a table - rows and columns - but it exists only for the duration of the execution of the SQL statement). If the triple-join returns 20 rows, then the cursor functions as a pointer into those 20 rows. PL/SQL provides three main statements to access a cursor:

OPEN

When you open a cursor, PL/SQL parses and binds the query associated with the cursor, identifying the result set of the cursor. The cursor is positioned just before the first row. There is not yet any "current row" for the set.

FETCH

The FETCH command fetches the current row from the cur- sor and moves the cursor to the next row in the result set.

CLOSE

The CLOSE statement closes the cursor and releases all memory used by the cursor. Once closed, the cursor no longer has a result set.

Figure 1 shows how these different operations are used to fetch information from the database into your PL/SQL program.
With that brief overview (for more details you can read Chapter 6 of my Oracle PL/SQL Programming book), let's move on to figuring out which of these various steps actually consumes the processing time in your program.

Setting up Test Data and Scenarios

To see meaningful results, I must run tests against cursors that take some time to process. So I took the traditional emp table and created an emp2 version with just three columns (empno, deptno, and ename) and 20,000 records, half in department 10 and half in department 20. There are no indexes on emp2. I then constructed three cursors, designed to explore the following scenarios:

1. Have the cursor take a long time to return a very small result set.
2. Have the cursor take a long time to return a large result set.
3. Apply the FOR UPDATE clause to the query to establish locks on all rows in the result set.

These three cursors are defined in the fatcur package specification, which is shown in Listing 2 . Following is an explanation of each one.
The fatcur1 cursor corresponds to the first scenario. It unions together four instances of the emp2 table, but in each case the
SELECT statement returns just the row containing the largest empno (inferred to be the last employee inserted into the table). So the result set consists of a single row. I'll call this the SELECT MAX cursor.
The fatcur2 cursor corresponds to the second scenario. It also unions together four instances of emp2, but in this case there is no WHERE clause. All rows are retrieved (20,000 in each table), but because the rows in each table are the same, only 20,000 rows (not 80,000) are returned in the result set. I'll call this the SELECT ALL cursor.
The fatcur3 cursor corresponds to the third scenario. It specifies a simple SELECT against the table for those employees in department 10, but it also specifies that I plan to update the deptno column. I'll call this the FOR UPDATE cursor.

Creating Test Code

I built the following objects to test cursor performance:

• The fatcur package. This package contains the three cursors defined in the previous section, as well as three programs to actually test performance. I explain these different programs below. Listing 2 shows the fatcur package specification. Listing 3 shows the fatcur package body.
  
• The fatcur test script. This small script sets up the SQL*Plus environment for the test and then executes each of the programs. Listing 4 shows the fatcur.tst script.

Let's look at the fatcur package body in more detail now; the big question in your minds should be: "How is Steven going to perform the timings?"
PL/SQL provides a procedure in the built-in DBMS_UTILITY package called GET_TIME, which returns the number of hundredths of seconds that have elapsed since an arbitrary point in time. I have built into PL/Vision a package called PLVtmr that usesDBMS_UTILITY.GET_TIME to construct a performance-timing mechanism. You can read about PLVtmr in detail in Chapter 14 of my Advanced Oracle PL/SQL Programming with Packages book, but the short version is easy to understand: Call PLVtmr.capture to capture the start time of your test. Call PLVtmr.show_elapsed to display the number of hundredths of seconds that have elapsed since the last call to PLVtmr.capture.

With this in mind, then, the basic idea behind my performance analysis of cursors goes like this:

/* time opening of cursor */
PLVtmr.capture;
OPEN fatcur;
PLVtmr.show_elapsed;

/* time fetch of first record */
PLVtmr.capture;
FETCH fatcur INTO fatrec;
PLVtmr.show_elapsed;

/* time fetch of second record */
PLVtmr.capture;
FETCH fatcur INTO fatrec;
PLVtmr.show_elapsed;

/* time closing of cursor */
PLVtmr.capture;
CLOSE fatcur;
PLVtmr.show_elapsed;

With some minor variations, the previous steps are what you will find in each of the two following test programs:

• The open_vs_fetch procedure is the basic test for comparing the difference in performance between the open and the fetch steps. You should be able to see easily whether the time to identify the result set (large or small) is taken in the open or the fetch.
• The read_consistency_impact procedure examines the impact of the read consistency model on cursor management. After opening the cursor, I delete all of the rows in emp2 and then fetch the first record. The delete should not affect the rows returned by the cursor, because it was opened before the delete. Does this action, however, affect the performance of the fetch?

Test Results

Once the various code elements were built (and tested!), I executed them as follows:

1. Modify the fatcur.spp file so that the cursor I want to test is named fatcur; rename both of the others to anything, as long as it is not "fatcur." The body of the fatcur package always references the fatcur cursor. I can change just the cursor names without modifying the body in order to test a different cursor.
2. Recompile fatcur.spp with this command:

SQL> @d:\articles\oreview\fatcur.spp

Now the test programs will work with the cursor just renamed to fatcur.
3. Execute the fatcur.tst script with this command:

SQL> @d:\articles\oreview\fatcur.tst

This script runs fatcur.open_vs_fetch and fatcur.read_consistency_
impact and sends the results to the screen via DBMS_OUTPUT.
PUT_LINE.
4. Clip out the results from the SQL*Plus session, deposit the results in a file, and analyze them. The output follows.

With the SELECT MAX cursor:

OPEN VS. FETCH
open Elapsed: 0 seconds.
first fetch Elapsed: 5.3 seconds.
Second fetch Elapsed: 0 seconds.
close Elapsed: 0 seconds.

IMPACT OF READ CONSISTENCY
open Elapsed: 0 seconds.
first fetch 20001 Elapsed: 19.8 seconds.
second fetch Elapsed: 0 seconds.
Close Elapsed: 0 seconds.

With the SELECT ALL cursor (more records in result set):

OPEN VS. FETCH
open Elapsed: .02 seconds.
first fetch Elapsed: 12.81 seconds.
Second fetch Elapsed: 0 seconds.
close Elapsed: .21 seconds.

IMPACT OF READ CONSISTENCY
open Elapsed: 0 seconds.
First fetch 10001 Elapsed: 24.51 seconds.
Second fetch Elapsed: 0 seconds.
Close Elapsed: .13 seconds.

With the SELECT FOR UPDATE cursor (locking on open):

OPEN VS. FETCH
open Elapsed: 77.77 seconds.
first fetch Elapsed: .04 seconds.
second fetch Elapsed: 0 seconds.
close Elapsed: 0 seconds.

IMPACT OF READ CONSISTENCY
open Elapsed: 6.33 seconds.
first fetch 10001 Elapsed: .07 seconds.
second fetch Elapsed: 0 seconds.
close Elapsed: 0 seconds.

Final Analysis

Those are some very interesting numbers generated by PLVtmr! Although I did not show you the results of all of my test runs, let me assure you that I did execute each of those tests several times to ensure that these numbers are at least representative of the behavior. Thus I can rather easily draw the following conclusions:

• For queries that do not have the FOR UPDATE clause, the Oracle server does not, strictly speaking, identify the result set at the time of open. Instead, it waits until the first record is fetched. Almost all of the processing time is concentrated in the first fetch. Both the open and the subsequent fetches occur very rapidly.
• If you use a FOR UPDATE clause in your query, the Oracle server does in fact identify the result set at the time of open - and it issues locks on each of the rows identified.

Do these results mean that the read consistency model is not in effect with explicit cursors? Absolutely not. This model is strictly enforced by Oracle. Any changes made by any session, including your own, that take place after the cursor was opened are not reflected in the result set of that cursor.
The Oracle Server may not perform the required processing at the time of cursor opening, but it has established timestamps so that when the first record is fetched it can tell whether any of the rows in the result set must be obtained from the "before image" (the way the record looked before any changes, committed or otherwise).
I hope that this article accomplishes two objectives: first, to dispel any false notions about when you can expect to "take the hit" on cursor processing; second, to make you more comfortable with the steps and techniques you need to analyze problems and come up with solutions. I don't really mind if you send me an email asking if I know the answer to this or that question (heck, I'll probably get an article out of it). You will be much better off, however, if you can answer your own question (and, perhaps, write your own article).