Re: sqlplus memory leak?

From: Pim <pimreinders_at_planet.nl>
Date: 27 Jun 2003 00:54:25 -0700
Message-ID: <c71d32c2.0306262354.69daec4@posting.google.com>

"Volker Hetzer" <volker.hetzer_at_ieee.org> wrote in message news:<bdcmuu$gfk$1_at_dackel.pdb.sbs.de>...
> Pim wrote:
> > We use many linux shell scripts requesting data from our (Oracle 8i
> > enterprise 8.1.7.4.0 running on Redhat 7.1) database. Many scripts use
> > sqlplus to connect to the db.
> > When these scripts are used intensively we observe a steady growing of
> > the memory usage until the point that the swap daemon consumes all the
> > CPU capacity.
> > Anyone who can shed some light on this?
> >
> > The problem can be isolated by running the following shell script:
> >
> > ii=1
> > while [ $ii -lt 1000000 ]
> > do
> > sqlplus scott/tiger <</
> > select 1 + 1 from dual
> > quit
> > /
> > ii=`expr $ii + 1`
> > done
>
>
> But wouldn't that script point to a memory leak in your shell?
> Or are you connecting locally and the server process leaks?
> Anyway, sqlplus can't leak much from one statement and any
> possible leak will disappear with the finishing of the process.
> i.e. with the "/" in your script.
>
> Lots of Greetings!
> Volker

It appears that also a single sqlplus session does not return memory after it is quit.
We have been monitoring memory usage by the following perl script:

#---------------------------------------------

#!/usr/bin/perl
#
$maxiter = 5000;
$wait = 1;
#
open MEMLOG, '>scratch__mem';
$iter = 0;
while ($iter++<=$maxiter) {
  $DATE = `date +%s`;
  $VMSTAT = `vmstat`;
  @items = split (/sy id/, "$VMSTAT");   $mstring = "$DATE "."$items[1]";
  $mstring =~ s/\n/ /g;
  print "$mstring\n";
  print MEMLOG "$mstring\n";
  sleep $wait;
};
close MEMLOG;

#---------------------------------------------

and plotting the result with gnuplot:

#---------------------------------------------

set title "memory usage versus time"
set xtics
set mxtics 2
set ytics
set mytics 2
set ticscale 1.0 0.5
set grid xtics ytics mxtics mytics lt 0 lw 0.2, lt -1 lw 0.1 set xlabel "Julian date [s]"
set ylabel "mem [Mb]"
#set xrange [0:10]
#set yrange [0:110]
set data style linespoints

#set term postscript landscape 12
#set output "memory_usage.ps"
#
#plot "scratch__mem" using 1:5 title "SWAP"
plot "scratch__mem" using 1:6 title "FREE MEM"

#plot "scratch__mem" using 1:7 title "BUFF" #plot "scratch__mem" using 1:8 title "CACHE" pause -1 "Hit return to continue"

#---------------------------------------------

the ASCII-fied result of a single sqlplus session is:

---

FREE MEM [kb]

     |
63100|
     |<**
     |   *************** *
     |                  *
63000|   ^                *
     |invoke               *
     |sqlplus          ^    *
     |             user/     *
62900|             passwd     *
     |                         *
     |                          *
     |                           *
62800|                            *
     |                             *
     |                              *
     |                               *
62700|                                *
     |                                 *
     |                                  *
     |                                   *
62600|                                    *
     |                                     *
     |                                      *
     |                                       *
62500|                                        *
     |                                         *
     |
     |
62400|                                                    **>
     |                                          **********
     |                                            ^
     |                                      select 1+1    ^
62300|                                      from dual   quit
     |_______________________________________________________
                          -> Julian date  (1s resolution)

Received on Fri Jun 27 2003 - 02:54:25 CDT