CURSOR_SHARING=FORCE using BIND variables to improve performance, Source code and timing provided



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


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"