Christian Bilien’s Oracle performance and tuning blog

February 6, 2007

Cursor_sharing and begin myproc(….) end;

Filed under: Oracle — christianbilien @ 8:15 pm

I owe a lot to Jonathan Lewis’ for his book ‘Cost Based Oracle Fundamentals’, from which I learned many useful informations. Here the latest interesting trick I found in this book :

I had to set cursor_sharing=force in a 10gR2 data base to remove parse calls generated by literals coming from dynamic SQL , but I got stuck on a PL/SQL call for which Oracle was not willing to perform bind variable substitution. It took me a while before realizing that the PL/SQL procedure body was not actually generating the parsing (there was no reason for doing so), but the call itself. The call was written using the old begin mypack.SetMyproc syntax, which I changed to the newer CALL mypack.SetMyproc. Here is the resulting trace :

Before :

PARSING IN CURSOR #2 len=168 dep=0 uid=72 oct=47 lid=72 tim=5753883462601 hv=3807832698 ad=’51cad930′

BEGIN mypack.SetMyproc (‘AZZ.4.2′,’AZZ2′,’BRIDGE2’,121,’8=FIX.4.2^A9=53^…)



PARSE #2:c=10000,e=9412,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=5753883462587


PARSING IN CURSOR #1 len=164 dep=0 uid=72 oct=170 lid=72 tim=108036751889 hv=628731915 ad=’37f9ae70′

CALL mypack.SetMyproc (:“SYS_B_00″,:”SYS_B_01″,:”SYS_B_02″,:”SYS_B_03”,…)


PARSE #1:c=0,e=234,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=108036751882

Bringing this piece of code from 9.4ms down to 0.2ms was great as we are really figthing for milliseconds gains in this application (not so usual for most customers, but not for electronic trading).


Blog at