Cursors1 a cursor is a vehicle for extracting data from an Oracle database. Cursors determine the columns and objects

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.

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.

1.3. How Oracle Decides To Share

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:
   

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.
   

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;

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

SELECT order_date FROM order 1 1

s WHERE order_numbeR = 11

s WHERE order_number = 11

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

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 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

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 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.
  

1.4. PL/SQL Cursor Reformatting

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

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;

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;

-- 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

WHERE ORDER_NUMBER = 200

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

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

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.

1.6. Matching Algorithms

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'

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';

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

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;

SQL_TEXT PARSE_CALLS EXECUTIONS

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

SELECT order_date FRO 2 2

M orders WHERE order_

number = :"SYS_B_0"

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;

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

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.

2 FROM ( SELECT *

3 FROM orders

4 WHERE region_id = 1 );

----------

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 );

----------

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" )

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" )

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.

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.

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: