Stegalex has asked for the wisdom of the Perl Monks concerning the following question:

I have had a persistent problem for a while with my httpd daemons consuming all my box's memory and CPU after a couple of hours. Now most people will say that this is normal, httpd sucks memory and you can't always believe what top says. However, site performance slows down and some programs die from memory starvation, so this is far from an OK thing.

After much research, I have pinpointed the problem to one lousy sql statement that sometimes inexplicably leaves a cursor opened in Oracle. When this happens, the httpd process it is running under bloats and starts sucking CPU and doesn't stop until I restart httpd.

I am certain that there is not a logic error in my code. The cursors are SPORADICALLY left open even though the cursor is closed unconditionally after it's no longer needed.

Before I go much further, here's the info about my setup.

Apache 1.3.12
Oracle: 8.1.5.0.2
Perl 5.6.0
RH Linux 7
DBI 1.14
mod_perl 1.24
html::embperl 1.3.1


Has anyone else encountered situations where doing an $sth->finish; doesn't work?

I know the cursors are being left open because I have examined the sql_text column in the v$open_cursors view in oracle and it shows me the statement that has the open cursor. When I kill the offending httpd process, the cursor gets closed.

Strange.

Replies are listed 'Best First'.
Re: DBI - cursors left open in Oracle
by seesik (Initiate) on Sep 08, 2001 at 23:05 UTC
    first off, what dbd::oracle version are you using? i had some trouble w/ 1.03 and cursor refs, but nothing on 1.06.

    secondly, are you using pl/sql cursors, or just the statement handle cursor? can we see your query, and can you tell us what your database's open_cursors/max_open_cursors init param is (check v$parameter)?

    remember, $sth->finish does NOT DESTROY the handle. it just sets the handle's Active attribute to false. you could try undef'ing the handle explicitly just to see if it get's gc'd.

    lastly, if you're on a production machine, i'd advise getting off of 8.1.5x altogether; either downgrade to 8.0.5 or up to 8.1.7 (skip 8.1.6 and 9.0.1).there have been a few times now that i've seen 8.1.5's SMON freak out and chew up 99% cpu time just trying to allocate extents. not that this has anything to do w/ your cursor issue...

[reply]
[d/l]
[select]
      I don't have anything new to add other than to reiterate what seesik mentioned based on my experience. Consider it a second opinion ;-)

      Upgrade to 8.1.7. Your mother would tell you that it's good for you. And undef every handle after you've finished with it.

      Hope this helps!

[reply]
      DBD::Oracle version 1.06

      This particular cursor is a statemtent handle cursor of the form:

      $sql = "select 'x', 'y' from dual";
      $sth=$dbh->prepare(qq{$sql});
      $sth->execute;
      ($x, $y) = $sth->fetchrow_array;
      $sth->finish;

      open_cursors parameter is 50
      there does not seem to be a max_open_cursors param defined

      Thanks for any and all comments!
[reply]
(jptxs) Re: DBI - cursors left open in Oracle - for now...
by jptxs (Curate) on Sep 08, 2001 at 23:02 UTC
    I've never seen this particular behavior, but I have seen Oracle do a number of incredibly odd things on Linux. Oracle has told me that the client on Linux is the root of the cause in many cases. What I had done in one case where mystery sessions were being spawned was to simply write a script to query the sys views (v$sessions in my case, but v$open_cursors in yours) for the offending conditions periodically through cron and to kill off the asociated processes. It will depend on your exact configuration and session management style, but this may work for you for now as well. At least until you get through this.

    we eventually had to run Oracle on Sun to get the stability we needed.

    We speak the way we breathe. --Fugazi

[reply]

Back to Seekers of Perl Wisdom