Mohamed Houri’s Oracle Notes

June 16, 2011

Bind variable, shared pool and cursor sharing parameter

Filed under: Trouble shooting — hourim @ 6:23 pm

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.

7 Comments »

  1. Very nice article, it has helped me a lot in identifying the exact same problem that you encountered. Thanks!

    Comment by Rob — October 4, 2011 @ 11:47 am | Reply

  2. Rob,

    Thanks for your nice comment

    Best Regards

    Mohamed

    Comment by Houri Mohamed — October 4, 2011 @ 1:40 pm | Reply

  3. Hi,
    interesting article.
    Why should divide AWR time by Db time ?

    Dividing these two metrics gives (1,806.6/1,561) 1.15 CPU

    Thank you.

    Comment by Guy — November 22, 2012 @ 10:40 am | Reply

  4. Guy,

    You are referring to a different article https://hourim.wordpress.com/2012/05/25/tuning-a-batch-job-using-awr/.

    When you see in the TOP 5 timed events an event related to CPU then you have to do some arithmetic before making conclusion about whether you are CPU bound or not. In general here how I proceed:
    Awr snapshot * 60 (seconds) * available number of CPU = 30,11 * 60 * 32 = 57811,2 seconds available for CPU
    Top 5 timed events shows: 1561 CPU seconds

    Dividing the second by the first you get 1561/57811,2 = 0,027 = 2,7%

    Which means we are consuming only 2,7% of CPU time available.

    I have to correct a litte bit the original article

    Best regards

    Comment by hourim — November 22, 2012 @ 11:27 am | Reply

  5. Thanks Mohamed.

    Comment by Guy — November 23, 2012 @ 9:23 am | Reply

  6. Houri
    Thanks ver much for excellent article and reference to Jonathan Lewis book. Faced similar issue in production and it worked as stated here in VB code also. Surprise to know CALL statement works.

    Comment by Kpanchan — May 24, 2014 @ 10:14 am | Reply

  7. […] Bind variable, shared pool and cursor sharing parameter […]

    Pingback by Cursor Sharing in Oracle Database | All About Data — March 28, 2016 @ 7:00 am | Reply


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

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: