Section 2.1. Reusing Cursors


2.1. Reusing Cursors

Cursor reuse is basically a very simple concepta cursor can be used and then used again. More specifically, the compiled version of the cursor can be reused to avoid a costly parse and recompilation.

2.1.1. Hard Parsing and Soft Parsing

The process of compiling a new cursor is referred to as hard parsing (and is worthy of a book all its own); within the context of this chapter it can be simplified to four steps:


Validation

The SQL syntax of the cursor is validated for correctness, and the objects (tables and columns) to which it refers are verified.


Compilation

The cursor is compiled into executable form and is loaded into the database's shared pool. Its location in the shared pool is referred to as its address.


Execution plan calculation

Oracle's cost-based optimizer (CBO) determines the best execution plan for the cursor and attaches it to the cursor.


Hash calculation

The ASCII value of each character in the cursor is totaled up and sent to a hash algorithm. The algorithm calculates a value to make the cursor easy to find for subsequent reuse. This value is referred to as the cursor's hash value. We'll look more at ASCII values later in this section.

A good deal of database latch activity takes place during these operations because Oracle cannot allow any underlying objects (tables and columns) to change while the cursor is being validated and compiled. The activity is almost entirely CPU-driven, so a portion of your database server's CPU will be consumed while compilation occurs. More importantly, the really important work of fetching records will be delayed. Subsequent executions of the same cursor (either by the same program or by another) can avoid incurring the costly hard-parse process and instead simply ensure that they have the required access to the objects (tables, views, etc.) involved and proceed directly to fetching records. This saves valuable time.

A running total of the hard parses a database has performed can be gleaned from the V$SYSSTAT table as follows:

     SQL> SELECT name,       2         value       3    FROM v$sysstat       4   WHERE name = 'parse count (hard)';     NAME                                VALUE     ------------------------------ ----------     parse count (hard)                    676

If the number of hard parses in an application is steadily increasing, that is an indication that the application is not taking proper advantage of cursor reuse.

Even after a cursor has undergone a hard parse, it may require some amount of parsing during the course of reuse. However, that process will be a much less intrusive parse consisting of little more than a security check to ensure that the user attempting to reuse the cursor has the required access to its underlying objects. This somewhat limited work is referred to as a soft parse.

The running total of soft parses a database has performed may be calculated by taking total parses and subtracting hard parses.

     SQL> SELECT ( SELECT value       2             FROM v$sysstat       3            WHERE name = 'parse count (total)' )       4       - ( SELECT value       5             FROM v$sysstat       6            WHERE name = 'parse count (hard)' ) soft_parse       7    FROM dual;     SOFT_PARSE     ----------           4439

The time a database has spent parsing (CPU and elapsed) is also available from the V$SYSSTAT table.

     SQL> SELECT name,       2         value       3    FROM v$sysstat       4  WHERE name LIKE 'parse time%';     NAME                                VALUE     ------------------------------ ----------     parse time cpu                        381     parse time elapsed                   5933

Ideally, neither of these values should increase markedly while an application is running. They will, however, almost always increase somewhat because even the simplest soft parse requires some CPU.

2.1.2. Planning Cursor Use

It is good practice to limit the number of times a cursor has to be parsedthe optimal number of parses is one, of course. One option to achieve this ideal would be to preparse every possible cursor your application might ever execute. That way, every cursor would already be waiting in the shared pool when your application starts up. However, this approach would be extremely difficult to maintain in a large application or in one that allows ad hoc queries. Thus, it is better to take the hit the first time a cursor executes and then ensure that it is reused whenever possible later on.

Except where explicitly stated otherwise in this chapter, the CURSOR_SHARING parameter for all examples is set to EXACT. See the later section "Matching Algorithms" for a discussion of exact matching versus similar matching.


The following subsections explain how Oracle decides whether to reuse a cursor. This is the most important information to have when devising a plan for cursor reuse. Many PL/SQL developers are, unfortunately, blissfully unaware of this whole concept, so it is doubly important for DBAs to understand the use and consequences of cursor reuse. First, we'll look at some details of Oracle's hash algorithm, and then we'll cover the nuances of cursor reuse. I encourage you to read this whole section before tackling any cursor reuse issues (real or perceived) in your applications.

2.1.3. How Oracle Decides To Share

Oracle uses a complex algorithm to decide whether a cursor that is about to be executed may reuse an already compiled version from the shared pool. Here is a simplified presentation of that algorithm:

  1. Calculate the sum of the ASCII values of all characters in the cursor (excluding bind variables). For example, the total ASCII value of the following cursor is 2556:

     SELECT order_date FROM orders

    This is calculated as ASCII(S) + ASCII(E) + ASCII(L)...or 83 + 69 + 76 and so on.

  2. Apply a hash algorithm to the ASCII total.

  3. Peruse the shared pool for a cursor with the same hash value.

  4. If one is found, it may be reused.

Note that I say "may" be reused. Most of the time, a matching ASCII hash is enough to allow reuse, but not always, as I'll explain later in this section.


In item 1, I state that the ASCII value of every character is used. Thus, something as low level as mixing uppercase and lowercase has to be considered when planning for cursor reuse. Consider these two cursors run directly in SQL*Plus.

     SQL> SELECT order_date       2    FROM orders       3   WHERE order_number = 11;     ORDER_DAT     ---------     03-MAY-05     SQL> SELECT order_date       2    FROM orders       3   WHERE order_numbeR = 11;     ORDER_DAT     ---------     03-MAY-05

The human eye can see that both of these examples do exactly the same thingselect the order date for order number 11. The only difference is that an uppercase R was used in the second one. That's just enough for Oracle to consider them different, though, so two cursors wind up in the shared pool.

     SQL> SELECT sql_text,       2         parse_calls,       3         executions       4    FROM v$sql       5   WHERE INSTR(UPPER(sql_text),'ORDERS')  > 0       6     AND INSTR(UPPER(sql_text),'SQL_TEXT') = 0       7     AND command_type = 3;     SQL_TEXT                       PARSE_CALLS EXECUTIONS     ------------------------------ ----------- ----------     SELECT order_date   FROM order           1          1     s  WHERE order_numbeR = 11     SELECT order_date   FROM order           1          1     s  WHERE order_number = 11

The EXECUTIONS column shows the number of times the particular cursor has been executed, and the PARSE_CALLS column shows the number of times the cursor has been parsed. Both cursors required a hard parse because the ASCII totals did not match up.

You may find the requirement for exact matching a little stern and unforgiving, but it has to be that way because the database can't afford to spend a lot of time preparsing or reformatting cursor textit has better things to do, like running your application. Recent versions of Oracle have introduced some reformatting of literal values in cursors to aid in cursor reuse (see the "Matching Algorithms" section later in this chapter), but that reformatting introduces some overhead to each and every query containing a literal value.

One of the best ways to take advantage of automatically reformatting cursors to promote reuse is to put them into PL/SQL, as shown in this standalone block:

     DECLARE       CURSOR one IS       SELECT order_date         FROM orders        WHERE order_number = 11;       CURSOR two IS       SELECT order_date         FROM orders        WHERE order_numbeR = 11;       v_date DATE;     BEGIN       -- open and close correct cursor       OPEN one;       FETCH one INTO v_date;       CLOSE one;       -- open and close misspelled cursor       OPEN two;       FETCH two INTO v_date;       CLOSE two;     END;

The PL/SQL compiler reformats the mismatched cursors, thus producing a single cursor in the shared pool. On the surface, this may seem to be a very small improvement, but implemented across an application, it can produce big performance gains because the shared pool latches have fewer cursors to keep track of.

     SQL_TEXT                       PARSE_CALLS EXECUTIONS     ------------------------------ ----------- ----------     SELECT ORDER_DATE FROM ORDERS            2          2     WHERE ORDER_NUMBER = 11

This behavior is available in Oracle8i Database through Oracle Database 10g Release 2, with an unfortunate lapse in Oracle9i Database Release 2.

I like to think of the PL/SQL compiler as being a little more forgiving than the SQL one. Nevertheless, every execution of my standalone block necessitates that each cursor be validated and parsed. So, even though I'm down to a single cursor, my hard parse count is steadily increasing. Ten executions of my standalone block cause 20 hard parses:

     SQL_TEXT                       PARSE_CALLS EXECUTIONS     ------------------------------ ----------- ----------     SELECT ORDER_DATE FROM ORDERS           20         20     WHERE ORDER_NUMBER = 11

The good news is that PL/SQL can virtually eliminate all hard parses just by moving the cursors into a stored procedure, as follows:

     CREATE OR REPLACE PROCEDURE simple_demo AS       CURSOR one IS       SELECT order_date         FROM orders        WHERE order_number = 11;       CURSOR two IS       SELECT order_date          FROM orders        WHERE order_numbeR = 11;       v_date DATE;     BEGIN       -- open and close correct cursor       OPEN one;       FETCH one INTO v_date;       CLOSE one;       -- open and close misspelled one allowing PL/SQL       -- to reformat for me!       OPEN two;       FETCH two INTO v_date;       CLOSE two;     END;

After executing the stored procedure 10 times in SQL*Plus like this:

     SQL> BEGIN       2   simple_demo;       3  END;

the SGA shows these numbers:

     SQL_TEXT                       PARSE_CALLS EXECUTIONS     ------------------------------ ----------- ----------     SELECT ORDER_DATE FROM ORDERS            2         20     WHERE ORDER_NUMBER = 11

The two parses occurred when the procedure was first compiled. Once that happens, the procedure and all of its contents (including the cursors) are considered valid and do not require reparsing.

Simply by moving these two cursors into PL/SQL, you'll take advantage of two key features of PL/SQL that are particularly helpful for DBAs:

  • The PL/SQL compiler promotes reuse by being more forgiving regarding the structure of cursors. The degree of forgiveness is discussed in the next section.

  • Once a cursor is compiled into a PL/SQL procedure, package, or function, it is automatically considered valid and parsed as long as the procedure, package, or function remains valid.

2.1.4. PL/SQL Cursor Reformatting

As I mentioned, the PL/SQL compiler makes some extra effort to facilitate cursor reuse by checking for small differences like extra whitespace, uppercase versus lowercase, and line breaks. For example, only a single compiled cursor is created and reused in the shared pool for the following procedure:

     CREATE OR REPLACE PROCEDURE forgiveness IS       -- define two poorly structured cursors       CURSOR curs_x IS       SELECT order_date FROM orders;       CURSOR curs_y IS       SELECT order_date         FROM     orders;     BEGIN       -- let PL/SQL work its reformatting magic       OPEN curs_x;       CLOSE curs_x;       OPEN curs_y;       CLOSE curs_y;     END;

This preparsing is done to all cursors in PL/SQL to help with matching among all stored code. For example, the following cursor would reuse the compiled cursor from the forgiveness procedure.

       CURSOR curs_x IS       SELECT order_date FROM ORDers;

2.1.5. Literal Values

Another factor to consider when planning for cursor reuse is the use of literal values. Consider the following simple code snippet that performs two straightforward queries. Pay close attention to the fact that the text of each query differs only in the specified literal order number.

     CREATE OR REPLACE PROCEDURE two_queries     AS       v_order_date DATE;     BEGIN       -- get order 100       SELECT order_date         INTO v_order_date         FROM orders        WHERE order_number = 100;       -- get order 200       SELECT order_date         INTO v_order_date         FROM orders        WHERE order_number = 200;     END;

After an initial execution, the shared pool contains two cursors.

     SQL_TEXT                       PARSE_CALLS EXECUTIONS     ------------------------------ ----------- ----------     SELECT ORDER_DATE FROM ORDERS            1          1     WHERE ORDER_NUMBER = 100     SELECT ORDER_DATE FROM ORDERS            1          1     WHERE ORDER_NUMBER = 200

Each cursor was parsed and executed once because the ASCII totals did not match up. In addition, their only chance of further reuse will be when order 100 or 200 is explicitly queried. That's hardly optimal because, if there are tens of thousands of orders, there will be tens of thousands of hard parses to retrieve them.

Within PL/SQL, the easiest way to make these cursors available for reuse is to parameterize them as shown in this code:

     CREATE OR REPLACE PROCEDURE two_queries AS       -- define a parameterized cursor       CURSOR get_date ( cp_order NUMBER ) IS       SELECT order_date         FROM orders        WHERE order_number = cp_order;       v_order_date DATE;     BEGIN       -- get order 100       OPEN get_date(100);       FETCH get_date INTO v_order_date;       CLOSE get_date;       -- get order 200       OPEN get_date(200);       FETCH get_date INTO v_order_date;       CLOSE get_date;     END;

After flushing the shared pool and executing the new function, here's what's in the shared pool.

     SQL_TEXT                       PARSE_CALLS EXECUTIONS     ------------------------------ ----------- ----------     SELECT ORDER_DATE FROM ORDERS            1          2     WHERE ORDER_NUMBER = :B1

Only one cursor with an execution count of 2 exists in the shared pool, indicating that the cursor has already been reused.

At this point, any other code executing the same cursor to get the order date for an order can reuse the already compiled version. For example, the following procedure will reuse the compiled version of the cursor:

     CREATE OR REPLACE PROCEDURE another_two_queries AS       -- same cursor as above, just different parameter name       CURSOR get_date ( cp_oid NUMBER ) IS       SELECT order_date         FROM orders        WHERE order_number = cp_oid;       v_order_date DATE;     BEGIN       -- get order 300       OPEN get_date(300);       FETCH get_date INTO v_order_date;       CLOSE get_date;       -- get order 400       OPEN get_date(400);       FETCH get_date INTO v_order_date;       CLOSE get_date;     END;

After running the new procedure, the shared pool holds this:

     SQL_TEXT                       PARSE_CALLS EXECUTIONS     ------------------------------ ----------- ----------     SELECT ORDER_DATE FROM ORDERS            2          4     WHERE ORDER_NUMBER = :B1

The compiled version of the cursor was reused by the second procedure. The parse required was only a soft one. It's clear from this example that it is good practice to keep from using literals in cursors whenever possible.

But what if the application is not adaptable because it's an off-the-shelf package or there just isn't enough time or money to change it? Oracle offers some help for such situations, as discussed in the next section.

2.1.6. Matching Algorithms

The default behavior for cursor reuse in the database is referred to as exact matching . Outside of PL/SQL this exactness is quite unforgivingthe ASCII values must match precisely. There is only black and white, no gray area at all. A cursor either matches or it does not. Within PL/SQL, the compiler does what it can by reformatting cursors to promote reuse, but it can only do so much. This limitation is especially maddening when cursors differ only in the text contained within literals. Consider these two cursors:

     SELECT order_date FROM orders WHERE order_number = '1';     SELECT order_date FROM orders WHERE order_number = '4';

They both perform the same function of getting the order_date for a specific order, and their total ASCII values differ by a measly four digits. But under the exact matching algorithm, they both merit their own hard parse and space in the shared pooleven when embedded in PL/SQL.

     SQL_TEXT                       PARSE_CALLS EXECUTIONS     ------------------------------ ----------- ----------     SELECT order_date FROM orders            1          1     WHERE order_number = '4'     SELECT order_date FROM orders            1          1     WHERE order_number = '1'

Eventually, the shared pool may end up flooded with cursors like this that are so close to being reusedand yet so far. Some commercial off-the-shelf applications exhibit this behavior of using literals in cursors, and some older versions of ODBC rely on it, as well. To allow for this behavior, Oracle introduced a second algorithm for cursor sharing known as similar matching . "Similar" here means that cursors only need to match ASCII-wise outside of any literals.

The sharing algorithm applied is determined by the database initialization parameter CURSOR_SHARING:

     SQL> SELECT name,       2         value       3    FROM v$parameter       4   WHERE name = 'cursor_sharing';     NAME                 VALUE     -------------------- ---------------     cursor_sharing       EXACT

In addition to setting this parameter at the database level, you can set it for a single session using the ALTER SESSION command .

     SQL> ALTER SESSION SET cursor_sharing = SIMILAR;     Session altered.


Here's what's in the shared pool after the cursors are executed with differing literals under the similar matching algorithm.

     SQL> SELECT name,       2         value       3    FROM v$parameter       4   WHERE name = 'cursor_sharing';     NAME                 VALUE     -------------------- ---------------     cursor_sharing       EXACT

In addition to setting this parameter at the database level, you can set it for a single session using the ALTER SESSION command.

     SQL> ALTER SESSION SET cursor_sharing = SIMILAR;     Session altered.

Here's what's in the shared pool after the cursors are executed with differing literals under the similar matching algorithm.

     SQL_TEXT              PARSE_CALLS EXECUTIONS     --------------------- ----------- ----------     SELECT order_date FRO           2          2     M orders WHERE order_     number = :"SYS_B_0"

The literal value was translated into a bind variable, which means that it is now far more likely to be reused.

The SIMILAR setting does not mean that Oracle will blindly substitute bind variables for every single literal it encounters. For example, it won't do so if the execution plan chosen by the cost-based optimizer would change drastically, as shown in this example with some very skewed orders by region.

     SQL> SELECT region_id,       2         count(*)       3    FROM orders       4  GROUP BY region_id;      REGION_ID   COUNT(*)     ---------- ----------              1       9999              2          1

If the optimizer statistics are up to date, Oracle will be aware of this skew and will utilize different execution plans for getting records from each region. For region 1, it will choose a full table scan because it has to touch all but one record anyway. For region 2, it will hone in on a single row using an index on the REGION_ID field. I'll execute the queries with AUTOTRACE to show this behavior.

     SQL> SELECT COUNT(*)       2    FROM ( SELECT *       3             FROM orders       4            WHERE region_id = 1 );       COUNT(*)     ----------           9999     Execution Plan     ----------------------------------------------------------        0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=2)        1    0   SORT (AGGREGATE)        2    1     TABLE ACCESS (FULL) OF 'ORDERS' (TABLE) (Cost=3 Card=21 Bytes=42)     SQL> SELECT COUNT(*)       2    FROM ( SELECT *       3             FROM orders       4            WHERE region_id = 2 );       COUNT(*)     ----------              1     Execution Plan     ----------------------------------------------------------        0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=2)        1    0   SORT (AGGREGATE)        2    1     INDEX (RANGE SCAN) OF 'ORDER_REGION' (INDEX) (Cost=1 Card=1 Bytes=2)

The really interesting thing is the resulting cursors in the shared pool.

     SQL_TEXT              PARSE_CALLS EXECUTIONS     --------------------- ----------- ----------     SELECT COUNT(*)   FRO           1          1     M ( SELECT *        FROM orders         WHERE region_id =      :"SYS_B_0" )     SELECT COUNT(*)   FRO           1          1     M ( SELECT *        FROM orders         WHERE region_id =      :"SYS_B_0" )

Even though the text of the cursors is exactly the same after making the substitution, another cursor was created because Oracle recognized that the execution plan would change too drastically. This behavior ensures that performance is not adversely affected when you choose the SIMILAR setting for cursor sharing.

A third setting is available for the CURSOR_SHARING parameter : FORCE. And it does just thatforce cursors to be shared strictly on the basis of the text they contain after translating literals into bind variables. This method blindly performs literal substitution and forces the CBO to create an execution plan based on it. This is not always optimal, because the CBO might make a better decision if it knew the explicit value, as shown in the preceding REGION_ID.

After executing the count of region 1 and 2 records using the FORCE setting , the shared pool contains a single cursor.

     SQL_TEXT              PARSE_CALLS EXECUTIONS     --------------------- ----------- ----------     SELECT COUNT(*)   FRO           2          2     M ( SELECT *        FROM orders         WHERE region_id =      :"SYS_B_0" )

The FORCE setting was introduced in Oracle8i Database (8.1.6) before the SIMILAR setting was made available in Oracle9i Database. SIMILAR was introduced because many DBAs and developers viewed the FORCE setting as being an overly brute-force method with regard to query performance (as shown in the examples above). If you decide to implement either of these settings, I strongly encourage you to perform a substantial amount of performance testing to make sure that the benefit of cursor sharing is not being outweighed by query performance degradation.

Remember: although the SIMILAR and FORCE settings may come in handy, there remains no substitute for well-thought-out, consistent use of cursors in your code.

2.1.7. Text Matching Might Not Be Enough

Beyond matching the text of a cursor, there are several other factors influencing cursor reusefor example, optimizer statistics and Globalization Support (previously National Language Support, or NLS) setting mismatches. In such cases, it's not enough to simply match ASCII values.

Let's look at the example of setting the optimizer mode in this example.

     SQL> ALTER SESSION SET optimizer_mode = FIRST_ROWS;     Session altered.     SQL> SELECT COUNT(*)       2    FROM orders;       COUNT(*)     ----------          10000     SQL> ALTER SESSION SET optimizer_mode = ALL_ROWS;     Session altered.     SQL> SELECT COUNT(*)       2    FROM orders;       COUNT(*)     ----------          10000

Astute DBAs know that two cursors will be created in this case: even though the text matched, the optimizer mode used was different, so Oracle built a different execution plan for each cursor. Here's what's in the shared pool.

     SQL> SELECT sql_id,       2         sql_text,       3         parse_calls,       4         executions       5    FROM v$sql       6   WHERE INSTR(UPPER(sql_text),'ORDERS')  > 0       7     AND INSTR(UPPER(sql_text),'SQL_TEXT') = 0       8     AND command_type = 3;     SQL_ID        SQL_TEXT              PARSE_CALLS EXECUTIONS     ------------- --------------------- ----------- ----------     d8ksp6aaxa26d SELECT COUNT(*)   FRO           1          1                   M orders     d8ksp6aaxa26d SELECT COUNT(*)   FRO           1          1                   M orders

Starting with Oracle Database 10g Release 1, the column SQL_ID is used to uniquely identify a cursor in views such as V$SQL and V$OPEN_CURSOR. Earlier releases use a combination of the HASH_VALUE and ADDRESS columns for this purpose.


Oracle obviously recognized that the cursors were exactly the same because it assigned them the same ID but made the second one a child of the first.

We know in this case that two cursors were required because the optimizer mode changed, but what if we didn't have that information? How could we figure out why a child cursor was required? Enter the V$SQL_SHARED_CURSOR view .

     SQL> SELECT sql_id,       2         child_number,       3         optimizer_mismatch       4    FROM v$sql_shared_cursor       5   WHERE sql_id = 'd8ksp6aaxa26d';     SQL_ID        CHILD_NUMBER O     ------------- ------------ -     d8ksp6aaxa26d            0 N     d8ksp6aaxa26d            1 Y

The "Y" in the second column means that Yes, the optimizer mismatch necessitated a child cursor. I carefully limited the query to confirm the reason that the cursor was not shared by including only the OPTIMIZER_MISMATCH column . However, the V$SQL_SHARED_CURSOR view contains a lot of fields (39 in Oracle Database 10g Release 1, for example), one for each possible reason a cursor might not have been shared.

I've always wondered why this view isn't named V$SQL_UNSHARED_SQL_CURSOR because that's what it actually shows. Either way, it's a very handy view for diagnosing cursor-sharing issues, so I encourage you to check it out.




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