Pim wrote:
> "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)
This a classic behavior for Solaris & Oracle;
but do NOT necessarily blame Oracle. Solaris
"consumes" free memory as OS file buffers &
Solaris never explicitly releases it. Oracle
will typically only consume in round numbers
SGA+(PGA*number Oracle processes).
In most cases where SWAPPING actually occurs
(as opposed to just paging), typically it is
due to the fact that sum of all SGA exceeds RAM.
Received on Mon Jun 30 2003 - 20:04:01 CDT
