Bind variable, shared pool and cursor sharing parameter

I was asked to investigate a real life production problem concerning the shared pool (library cache) of the SGA which come to be full so that the only solution which remains available was to stop and restart the data base.  I did immediately start thinking if I have not already been confronted to a similar case until I do realize that I have read something close to this situation in Chapter 4 – Memory Structures – of Tom Kyte book Expert Oracle Data Base Architecture 9i and 10g Programming Techniques and Solution;

So my first step in the trouble shooting path of this memory problem was to issue the following select against the v$sql dynamic view:

SELECT sql_text
      ,executions
FROM v$sql
WHERE sql_text LIKE '%Package_Name.P_UPDATE%'
AND executions <= 2
;

Which returns the following data picture:

sql_text                                           executions
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
…/…
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1

Thousands calls of the same instructions

BEGIN Package_Name.P_UPDATE_procedure(p_in_p1 ..) END;

executed once for each call. It is clear that  this application is suffering from not using bind variables and is dramatically filling up the library cache of the shared pool. This needs absolutely to be fixed.

Then I asked myself how could they fill up the shared pool while all what they are doing is calling a stored PL/SQL procedure? We all know that when using PL/SQL (static SQL) we should not encounter issues related to bind variables. PL/SQL itself takes care of our code and uses bind variables behind the scene. The only situation where we have to look carefully to the use of bind variable within PL/SQL is when we use Dynamic sql into stored procedures or functions (this is in fact another reason to avoid using dynamic SQL).

So how could they arrive to such a shared pool filled by thousands of same non re-executed PL/SQL calls?

And here where the problem resides: it is true that when using static PL/SQL stored objects, you don’t have to care about using bind variables inside those stored programs but you have to care about the third party which will call your PL/SQL stored procedure; they should do that using input parameters as bind variables; otherwise you will have your shared pool (library cache) full of calls to your stored PL/SQL objects as it is the case here.

Knowing this, I started figuring out the solution. I have identified the application responsible of those thousands of PL/SQL calls.  It is a non Oracle application developed with a language named SNAP and which is calling our PL/SQL stored objects without using bind variables. I explained them that they need to use OCI interface to prepare their statement, and implement it correctly using bind variables via OCIStmtPrepare2 and OCIDefineByPos (http://www.amazon.fr/Troubleshooting-Oracle-Performance-Christian-Antognini/dp/1590599179

Although they agreed doing that they have asked me to find a work around in between. The only dirty work around I was able to suggest them is to alter the session they are connecting to (and via which they are calling the stored PL/SQL procedure) so that the corresponding cursor_sharing value will be set to “FORCE”.

alter session set cursor_sharing=force;

Which they did immediately.

However, the same sql against the v$sql executed after this change was still showing the same picture of thousands of non re-executed calls

sql_text                                          executions
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
…/…

What went wrong?

I verified that they have really altered their session to set the cursor_sharing parameter to FORCE!!! So what’s going on here?

The problem in this case is that even if you set the cursor_sharing parameter to FORCE if you still keep the calls like:

BEGIN
Package_Name.P_UPDATE_procedure(p_in_p1,…);
END;

Oracle will not perform bind variables substitution (even when cursor sharing is set to force). In order for this substitution to occur we need to change the above code to be as follows (see http://apress.com/book/view/9781590596364)

   CALL Package_Name.P_UPDATE_procedure(p_in_p1,…);

So they did the change and so we obtain the new picture taken from v$sql:

SELECT
    substr(sql_text,1,55)
   ,executions
FROM v$sql
WHERE sql_text LIKE '%Package_Name.P_UPDATE%'
AND executions <= 2
;

which gives now the new following picture:

sql_text                                                 executions
------------------------------------------------------- ----------
CALL Package_Name.P_UPDATE_procedure (:"SYS_B_00",TO        897
CALL Package_Name.P_UPDATE_procedure (:"SYS_B_00",TO       8380
CALL Package_Name.P_UPDATE_procedure (:"SYS_B_00",TO         24
CALL Package_Name.P_UPDATE_procedure (:"SYS_B_00",TO      18671

Woo. See how the number of executions has gone so high 18.671. The shared pool becomes very attractive now.

It is also known that setting cursor_sharing to force may have unexpected side effects; for example when there exist a function based index having literals in its definition (substr (col_nam, 4, 2) for example), in this case you will notice that your query which was using your function based index starts not using it since you’ve set your cursor sharing to force. This is simply because oracle will change your query predicate from substr (col_name, 4,2) to something like substr (col_name,:SYS_B_0,SYS_B_1) which will not match your query predicate anymore and hence will not use your index.

The bottom line is that setting the cursor_sharing to force has to be a temporary solution and that the definitive solution should be to use correctly bind variables when calling PL/SQL stored object.