2.1. Reusing CursorsCursor 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 ParsingThe 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:
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 UseIt 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.
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 ShareOracle 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:
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:
2.1.4. PL/SQL Cursor ReformattingAs 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 ValuesAnother 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 AlgorithmsThe 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.
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 EnoughBeyond 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
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. |