FW: Oracle connections using ALOT of memory

It looks like my response also went missing. Please see below...

-----Original Message-----
From: David N. Heydon
Sent: 01 September 2006 09:30
To: '[email protected]'
Subject: RE: [fourjs-users] Oracle connections using ALOT of memory


Are you FREEing cursors and statements? The ODI guides mention Oracle can
consume large amounts of RAM for cursors/statements etc.

-----Original Message-----
From: Michael Laporte [mailto:[email protected]]
Sent: 30 August 2006 22:37
To: [email protected]
Subject: [fourjs-users] Oracle connections using ALOT of memory


Hi,

We are running into a situation where a production site is running out
of memory on their server.  They are running Genero 1.3 on AIX 4.3.3
and Oracle 9.1; causing severe slowdowns due to excess amounts of
paging going on.  The main memory hog is the "oracle<dbname>" (in our
case "oraclerecords") process that is created everytime a program is
launched and connects to the DB.

One tactic used was to try and take advantage of Oracle's automatic
PGA management by setting the WORKAREA_SIZE_POLICY=auto and the
PGA_AGGREGATE_TARGET to a level that would suffice (we went with
900MB).  However, after making these changes, we ran into an Oracle
bug with large data result sets on that particular level of AIX and
Oracle, so we had to revert back...

Another tactic we are trying is setting the
dbi.database.ora.prefetch.rows and the
dbi.database.ora.prefetch.memory values in our Resource file.  We had
read in the Genero 2.0 manual that the defaults for these settings in
Genero 1 were 50 rows and 65535 bytes, so we tried setting them down
to 10/4096 and 10/0 with no visible results.  The only thing I did see
was that everytime I restarted the application, the "oracle<dbname>"
process seemed to use a little memory each time (not sure why?).  This
testing was performed on an AIX 5.1 machine running Oracle 9.2 and
Genero 1.33 (not identical to production, but adequate to test the
settings IMO).  Seeing no changes with these values I see no point in
trying this on a production machine.  The disconcerting part was that,
according to the "svmon -P <PID>" command, the "oracle<dbname>"
process was using between 18,000 and 20,000 pages (1 page =
4kbytes)... which translates to between 76MB and 80MB!!!  I am sure
that there are some shared memory components involved, but this still
seems excessive to me.

Has anyone on this list had any experience in dealing with similar
issues?  If so, I'd like to know what else I can try to reduce the
Oracle connection's memory footprint on the system.

Any input or ideas are greatly appreciated.

Thanks,

-- 
Michael Laporte