it-rex
asked on
CURSOR_SHARING AND PL/SQL (call vs begin)
hello
the book cost based oracle by Jonathan Lewis states:
CURSOR_SHARING AND PL/SQL
In most cases, Oracle will replace literal values with bind variables with names like :SYS_B_0; however, if you
write code that calls anonymous PL/SQL blocks using the old begin proc_name (...)end; syntax, then
Oracle will not perform bind variable substitution. Since PL/SQL blocks don’t need to be optimized (even
though they still need to have cursor areas prepared for them), the overhead caused by this deficiency in the
mechanism may be something you can put up with.
If you can convert your code to the newer call proc_name(...) syntax, then Oracle will do bind variable
substitution. Note, however, that some older versions of Oracle have a bug that causes sessions to crash
if you mix literals and real bind variables in a call, and then enable cursor_sharing.
my Question is:seraching our v$sql we found that all plsql calls made as begin "CALL" is not used at all.
something like
begin appl_test_update(:Var1,:Va r2)
is this something that we can fix and replace begin with call?
if so what do we need to verify here?
does this apply to specific PLSQL code?
the book cost based oracle by Jonathan Lewis states:
CURSOR_SHARING AND PL/SQL
In most cases, Oracle will replace literal values with bind variables with names like :SYS_B_0; however, if you
write code that calls anonymous PL/SQL blocks using the old begin proc_name (...)end; syntax, then
Oracle will not perform bind variable substitution. Since PL/SQL blocks don’t need to be optimized (even
though they still need to have cursor areas prepared for them), the overhead caused by this deficiency in the
mechanism may be something you can put up with.
If you can convert your code to the newer call proc_name(...) syntax, then Oracle will do bind variable
substitution. Note, however, that some older versions of Oracle have a bug that causes sessions to crash
if you mix literals and real bind variables in a call, and then enable cursor_sharing.
my Question is:seraching our v$sql we found that all plsql calls made as begin "CALL" is not used at all.
something like
begin appl_test_update(:Var1,:Va
is this something that we can fix and replace begin with call?
if so what do we need to verify here?
does this apply to specific PLSQL code?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Sorry, I don't get it...Can you explain exactly what is it that you want to accomplish?
ASKER
tnx
ASKER