Tech Note CURSOR_SHARING=FORCE

Oracle Database Administration
Technical Note
September 28, 2009
George Milliken, DBA

TOPIC: CURSOR_SHARING=FORCE

Background on the Benefits of Using CURSOR_SHARING=FORCE

Setting the init.ora CURSOR_SHARING=FORCE will work around source code that uses literal values instead of bind variables. (see below for proof of the performance improvement). However, there are some potential side effects of using CURSOR_SHARING=FORCE as follows:

1. Applications (report writers) that DESCRIBE cursors may get erroneous column widths
2. Soft parsing will continue to be higher than needed
3. Latches consumption will be higher than needed
4. Oracle will work harder than needed to "convert" the SQL (i.e. to perform the "FORCE")

Here is an example of code that was already using bind variables (and had legit uses of literals) that CURSOR_SHARING=FORCE "fixed". I consider this a side effect.

update inv_investment_allocs_flat
set flat_status_code = :"SYS_B_0"
,last_updated_date = :v0
,last_updated_by = :v1
where flat_status_code = :"SYS_B_1"

In the example below three scenarios are tested to see the performance improvements as follows:

1. Bind Variables Used
2. No Bind Variables Used
3. No Bind Variables Used With CURSOR_SHARING=FORCE

From the results below we see that using bind variables yields the best performance, CURSOR_SHARING=FORCE is second best, and no bind variables is the worst. For these reasons, CURSOR_SHARING=FORCE should be considered a temporary workaround until the application source code can be modified to use bind variables. Additional testing is recommended to determine the impact of potential CURSOR_SHARING=FORCE side effects.

Background on the Benefits of Using Bind Variables

The Oracle database has a capability to reuse SQL statements and avoid expensive parse operations. This feature only works on statements that are identical. Statements that differ only by the value of a variable may be coded to use bind variables and the database will consider them identical and avoid hard parsing. You can see the direct benefits of using bind variables by using STATSPACK and executing the examples below. Abbreviated results are presented below for your reference. The key statistics are related to the "hard parse".

To reproduce these results open two sessions, one logged in as SCOTT/TIGER and one as PERFSTAT. Flush the shared pool using the command below prior to issuing the first STATSPACK SNAP for each run (bind.sql or no_bind.sql). Please note the examples are from Tom Kyte.

-- Flush the pool
ALTER SYSTEM FLUSH SHARED_POOL;

Bind Version
===============
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
Hard parses: 4.4 128.0

Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 4.6 98.4
DB CPU 4.6 98.3
parse time elapsed 0.8 17.8
hard parse elapsed time 0.8 16.2
PL/SQL execution elapsed time 0.1 3.1
PL/SQL compilation elapsed time 0.0 .9
repeated bind elapsed time 0.0 .1
DB time 4.7
background elapsed time 0.3
background cpu time 0.0
-------------------------------------------------------------
parse count (failures) 0 0.0 0.0
parse count (hard) 128 4.4 128.0
parse count (total) 2,180 75.2 2,180.0
parse time cpu 61 2.1 61.0
parse time elapsed 61 2.1 61.0

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 97.79 Optimal W/A Exec %: 100.00
Library Hit %: 84.79 Soft Parse %: 94.13
Execute to Parse %: 31.98 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 100.00 % Non-Parse CPU: 83.60

No Bind Version
=============
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
Hard parses: 3.7 449.0

Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 98.1 99.9
DB CPU 97.7 99.5
parse time elapsed 94.2 96.0
hard parse elapsed time 93.1 94.8
PL/SQL compilation elapsed time 2.3 2.3
PL/SQL execution elapsed time 0.2 .2
repeated bind elapsed time 0.0 .0
sequence load elapsed time 0.0 .0
hard parse (sharing criteria) elaps 0.0 .0
DB time 98.2
background elapsed time 2.6
background cpu time 1.3
-------------------------------------------------------------

parse count (failures) 0 0.0 0.0
parse count (hard) 1,347 3.7 449.0
parse count (total) 7,695 21.0 2,565.0
parse time cpu 9,361 25.5 3,120.3
parse time elapsed 9,393 25.6 3,131.0

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 97.37 Optimal W/A Exec %: 100.00
Library Hit %: 91.58 Soft Parse %: 82.50
Execute to Parse %: 31.86 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 99.66 % Non-Parse CPU: 4.33

FORCE Version
=============

alter session set cursor_sharing=FORCE;

alter system flush shared_pool;

EXECUTE statspack.snap;

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
Hard parses: 8.0 313.0

Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 17.4 99.8
DB CPU 17.0 97.7
parse time elapsed 14.3 81.9
hard parse elapsed time 13.9 79.9
PL/SQL compilation elapsed time 0.4 2.1
PL/SQL execution elapsed time 0.2 1.2
repeated bind elapsed time 0.0 .0
DB time 17.4
background elapsed time 0.7
background cpu time 0.1
-------------------------------------------------------------

parse count (failures) 0 0.0 0.0
parse count (hard) 313 8.0 313.0
parse count (total) 3,298 84.6 3,298.0
parse time cpu 1,419 36.4 1,419.0
parse time elapsed 1,449 37.2 1,449.0

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 97.57 Optimal W/A Exec %: 100.00
Library Hit %: 88.10 Soft Parse %: 90.51
Execute to Parse %: 24.98 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 97.93 % Non-Parse CPU: 18.82

Source Code Used In This Example

-- BIND.SQL

declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1..1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = :x'
using i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
' seconds...' );
end;
/

-- NO_BIND.SQL

set timing on
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1..1000
loop
open l_rc for
'select object_name from all_objects where object_id = ' || i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
( round( (dbms_utility.get_time-l_start)/100, 2 ) || ' seconds...' );
end;
/

References

http://www.oracle.com
Thomas Kyte "Expert Oracle Database Architecture Apress Publishing 2005"



Need Help?
Clark Fork Data Systems, Inc. can assist you with any of your Oracle database administration needs.

Call today! 650-275-2989
Email gpmilliken@clarkforkdata.com