Re: sqlplus memory leak?

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?

I ran your test script on my Linux desktop. No problems. But I'm not connecting locally (i.e. db instance is on another platform). Swap space remained constant and CPU idle at 80+% according to top.

I think Peter's posting may be close to the mark. Everytime you connect to Oracle, a dedicated process is created on the Oracle side to manage and service your session (assuming that you're not running MTS). These processes are also called shadows.

As I'm connecting to a remote instance, the shadows are running there.

What you could be seeing is a problem there - shadow processes not terminating correctly. Back in version 7 we had a similar problem with PQ (Parallel Query processes) being orphaned (loosing the PQ controlling processing) and then rampaging through the system consuming loads of CPU resources.

Change your test script to perform a loopback connection, e.g do a sqlplus scott/dual_at_mylocaldatabase where mylocaldatabase is an entry for your db in the tnsnames.ora file ($ORACLE_HOME/network/admin).

See if that makes a difference (the instantiating of the shadow is done differently - in one case it is via IPC and the other via the TCP listener process).

Also monitor all oracle processes - especially the ones that shows up with a (LOCAL=YES) in ps -ef | grep. These will be the shadows.

PS. My system is also RH 7.1 running Oracle Enterprise 8.1.7.4 (but no local db's as I'm still waiting extra diskspace :-)

--
Billy