Cursor_sharing and begin myproc(….) end;

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 changedto the newer CALL mypack.SetMyproc. Here is the resulting trace :