Nigel Noble's Oracle Blog

11/01/2011 dbms_sql leaks memory AND performance degrades

Filed under: Uncategorized — Nigel Noble @ 11:24 am

My site recently upgraded one of its databases to the patchset and found a serious performance problem relating to code using dbms_sql. Once we had completed the upgrade, we noticed a number of data feeds to the upgraded database started to fall behind and could no longer keep up. When we stopped and restarted the feeds they appeared to speed up. After some investigation we found two problems, the first relating to a “log file sync” problem in RAC which is still under investigation (one for a future post when we have more detail) but the second issue caused the performance of the data feed to degrade steadily over time.  

We use a couple of products to dynamically feed data to our database and it seemed the performance degraded  over time (many days). We did a 10046 session trace and found we lost a lot of time between the dbms_sql.parse of some dynamic SQL and the bind phase. We found reference in Metalink to Bug 10269717 (DBMS_SQL.PARSE leaks session heap memory in  Although the bug discusses a memory leak, we found that the performance also degrades over time.

We applied the patch for 10269717 and the PGA memory leak was resolved but more importantly the performance remained constant. I just checked the known issues document and this does now list the bug, but it still only references the leak and not the performance implications.



  1. Thank You for sharing such vital information. This is really useful to me.

    Comment by Vega Lin — 11/01/2011 @ 11:39 am

  2. I’d be interested in the reason for lots of DBMS_SQL.PARSEing. I’ve found the main advantage of DBMS_SQL over EXECUTE IMMEDIATE is that you can parse one and re-bind/execute.

    I had one situation where a bunch of formulas stored in a table needed to be evaluated and I got a big (and necessary) performance boost from having each of the dozen or so formulas with an open corresponding DBMS_SQL cursor.

    The cursor pointers were part of a associative array (INDEX BY VARCHAR2) so it was easy to grab the already parsed cursor for the relevant formula.

    Obviously you don’t want to (and shouldn’t need to) recode for an Oracle bug, but it may be worth considering a different approach as an option.

    Comment by Gary — 11/01/2011 @ 11:13 pm

    • In the two applications we have that have had problems, they both take feeds of dynamic data and transform them before constructing dynamic sql. Each generated insert statement can have a varying number of columns and data types which is only known at run time. I seem to remember in the past it was known as method 4 dynamic sql. I don’t think that is possible with “execute immediate” although I am not 100% sure.

      Comment by Nigel Noble — 11/01/2011 @ 11:59 pm

      • In fact, forgot to say, but it can also construct update statements for varying number of columns as well.

        Comment by Nigel Noble — 12/01/2011 @ 12:02 am

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

Create a free website or blog at

%d bloggers like this: