Re: FW: Oracle connections using ALOT of memory
"Michael Laporte" <[email protected]> Thu, 7 Sep 2006 14:05:42 +0200
| Newsgroups | gmane.comp.lang.4gl.fourjs.user |
|---|---|
| Message-ID | <[email protected]> |
------=_Part_12304_183710.1157572621077 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline We do indeed perpare/declare many cursors on program load, we are evaluating ways of reducing the amount of open cursors as much as possible; without affecting any performance gains of having frequently used cursors always prepared/declared in memory. Thanks! On 9/5/06, David N. Heydon <[email protected]> wrote: > > 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 > -- Michael Laporte ------=_Part_12304_183710.1157572621077 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline <br><br>We do indeed perpare/declare many cursors on program load, we are evaluating ways of reducing the amount of open cursors as much as possible; without affecting any performance gains of having frequently used cursors always prepared/declared in memory. <br><br>Thanks!<br><br><div><span class="gmail_quote">On 9/5/06, <b class="gmail_sendername">David N. Heydon</b> <<a href="mailto:[email protected]">[email protected]</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> It looks like my response also went missing. Please see below...<br><br>-----Original Message-----<br>From: David N. Heydon<br>Sent: 01 September 2006 09:30<br>To: '<a href="mailto:[email protected]">[email protected] </a>'<br>Subject: RE: [fourjs-users] Oracle connections using ALOT of memory<br><br><br>Are you FREEing cursors and statements? The ODI guides mention Oracle can<br>consume large amounts of RAM for cursors/statements etc. <br><br>-----Original Message-----<br>From: Michael Laporte [mailto:<a href="mailto:[email protected]">[email protected]</a>]<br>Sent: 30 August 2006 22:37<br>To: <a href="mailto:[email protected]">[email protected] </a><br>Subject: [fourjs-users] Oracle connections using ALOT of memory<br><br><br>Hi,<br><br>We are running into a situation where a production site is running out<br>of memory on their server. They are running Genero 1.3 on AIX 4.3.3<br>and Oracle 9.1; causing severe slowdowns due to excess amounts of<br>paging going on. The main memory hog is the "oracle<dbname>" (in our<br>case "oraclerecords") process that is created everytime a program is <br>launched and connects to the DB.<br><br>One tactic used was to try and take advantage of Oracle's automatic<br>PGA management by setting the WORKAREA_SIZE_POLICY=auto and the<br>PGA_AGGREGATE_TARGET to a level that would suffice (we went with <br>900MB). However, after making these changes, we ran into an Oracle<br>bug with large data result sets on that particular level of AIX and<br>Oracle, so we had to revert back...<br><br>Another tactic we are trying is setting the <br>dbi.database.ora.prefetch.rows and the<br>dbi.database.ora.prefetch.memory values in our Resource file. We had<br>read in the Genero 2.0 manual that the defaults for these settings in<br>Genero 1 were 50 rows and 65535 bytes, so we tried setting them down <br>to 10/4096 and 10/0 with no visible results. The only thing I did see<br>was that everytime I restarted the application, the "oracle<dbname>"<br>process seemed to use a little memory each time (not sure why?). This <br>testing was performed on an AIX 5.1 machine running Oracle 9.2 and<br>Genero 1.33 (not identical to production, but adequate to test the<br>settings IMO). Seeing no changes with these values I see no point in<br>trying this on a production machine. The disconcerting part was that, <br>according to the "svmon -P <PID>" command, the "oracle<dbname>"<br>process was using between 18,000 and 20,000 pages (1 page =<br>4kbytes)... which translates to between 76MB and 80MB!!! I am sure <br>that there are some shared memory components involved, but this still<br>seems excessive to me.<br><br>Has anyone on this list had any experience in dealing with similar<br>issues? If so, I'd like to know what else I can try to reduce the <br>Oracle connection's memory footprint on the system.<br><br>Any input or ideas are greatly appreciated.<br><br>Thanks,<br><br>--<br>Michael Laporte<br></blockquote></div><br><br clear="all"><br>-- <br>Michael Laporte ------=_Part_12304_183710.1157572621077--