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^…)

END ;

END OF STMT

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

After:

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”,…)

END OF STMT

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

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: