Re: Oracle connections using ALOT of memory
"Michael Laporte" <[email protected]> Thu, 7 Sep 2006 14:03:28 +0200
| Newsgroups | gmane.comp.lang.4gl.fourjs.user |
|---|---|
| Message-ID | <[email protected]> |
------=_Part_11308_16119912.1157570505956 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi Sebastien, The memory usage is definitely on the runtime system side of things, which was what we were trying to regulate somewhat using the PGA settings in Oracle that I mentioned. If the excessive memory usage continues when using dedicated server processes, we will investigate using shared server processes instead. In the meantime, as I mentioned to David, we will first try to minimize concurrently open cursors in the system to see how low we can get these dedicated server processes to go. Thanks! -- Michael Laporte On 9/5/06, Sebastien FLAESCH <[email protected]> wrote: > > Michael, > > It's a long time I am fighting with Oracle about memory usage, but > so far I did not succeed to make those guys understand the issues. > > Here are some remarks, from my (little) experience in Oracle configs: > > You must distinguish memory usage of clients (1) (our runtime system) > from memory usage on server side (2). > > --------------------------------------------------------------------- > (1): client-side mem usage > > A bunch of customers used to face serious memory leaks on AIX with > Oracle 9.2 clients. Make sure you have the latest patches. > As far as I remember, the Oracle bug was related to pre-fetching, > so the symptom you describe can identify this particulare problem. > > Normal-running clients with no memory leak can consume from 6 to 8 > or more Mb... of course it depends how many cursors/stmts are open. > > A part of that memory is shared among all clients, but according to > the results of a benchmark made by one of our customers (8000 users), > each process uses that much memory. > > When I explain to Oracle people that we have one OS process for each > DB connection, they ask why we don't have a Java architecture (with > one unique VM), because then we could use connection pooling ... > But those guys don't understand that we have constraints which forces > us to use this architecture (what if 2 processes defined different > DBDATE env vars?). > Also, to be honnest, moving to a unique-process / multi-threaded > runtime system is a big effort for us. Not in a short/mid term. > > To safe memory, you need to find a good compromise between prepared > statements/cursors and freed stmts (which need to be re-prepared). > The more you free, the less mem you use, but the slower programs will > execute ... I would suggest to free as much as possible in interactive > programs, but keep stmts prepared in batches. That sounds a basic > advice but at that benchmark customer we used to identify about 50 > simultaneous opened / not freed cursors, while only a dozen was needed. > > --------------------------------------------------------------------- > (2): server-side mem usage > > Whatever we do or you do, even buggy code, should not make the oracle > server use more and more memory. > If you have such a problem, it's an Oracle bug. > > You probably know that, but the typical thing you can do is to use > shared server process instead of dedicated server processes. > > > http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10739/manproc.htm > > As a last suggestion you could try Oracle 10g (client and server, > or mix a 9.2 client with a 10g server), to see if it changes > something. > > > And of course make sure you use the very latest Genero 1.33 (we may have > fixed some bugs in the driver). > > > That's all so far - sorry I am not an Oracle DBA expert ;-) > > Yours, > Seb > > > ----------------------------------------------------------------------- > > Micheal LAPORTE wrote: > > 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 > > ------=_Part_11308_16119912.1157570505956 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi Sebastien,<br><br>The memory usage is definitely on the runtime system side of things, which was what we were trying to regulate somewhat using the PGA settings in Oracle that I mentioned.<br><br>If the excessive memory usage continues when using dedicated server processes, we will investigate using shared server processes instead. In the meantime, as I mentioned to David, we will first try to minimize concurrently open cursors in the system to see how low we can get these dedicated server processes to go. <br><br>Thanks!<br><br>-- <br>Michael Laporte<br><br><br><div><span class="gmail_quote">On 9/5/06, <b class="gmail_sendername">Sebastien FLAESCH</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;"> Michael,<br><br>It's a long time I am fighting with Oracle about memory usage, but<br>so far I did not succeed to make those guys understand the issues.<br><br>Here are some remarks, from my (little) experience in Oracle configs: <br><br>You must distinguish memory usage of clients (1) (our runtime system)<br>from memory usage on server side (2).<br><br>---------------------------------------------------------------------<br>(1): client-side mem usage <br><br>A bunch of customers used to face serious memory leaks on AIX with<br>Oracle 9.2 clients. Make sure you have the latest patches.<br>As far as I remember, the Oracle bug was related to pre-fetching,<br>so the symptom you describe can identify this particulare problem. <br><br>Normal-running clients with no memory leak can consume from 6 to 8<br>or more Mb... of course it depends how many cursors/stmts are open.<br><br>A part of that memory is shared among all clients, but according to<br> the results of a benchmark made by one of our customers (8000 users),<br>each process uses that much memory.<br><br>When I explain to Oracle people that we have one OS process for each<br>DB connection, they ask why we don't have a Java architecture (with <br>one unique VM), because then we could use connection pooling ...<br>But those guys don't understand that we have constraints which forces<br>us to use this architecture (what if 2 processes defined different<br>DBDATE env vars?). <br>Also, to be honnest, moving to a unique-process / multi-threaded<br>runtime system is a big effort for us. Not in a short/mid term.<br><br>To safe memory, you need to find a good compromise between prepared<br>statements/cursors and freed stmts (which need to be re-prepared). <br>The more you free, the less mem you use, but the slower programs will<br>execute ... I would suggest to free as much as possible in interactive<br>programs, but keep stmts prepared in batches. That sounds a basic<br>advice but at that benchmark customer we used to identify about 50 <br>simultaneous opened / not freed cursors, while only a dozen was needed.<br><br>---------------------------------------------------------------------<br>(2): server-side mem usage<br><br>Whatever we do or you do, even buggy code, should not make the oracle <br>server use more and more memory.<br>If you have such a problem, it's an Oracle bug.<br><br>You probably know that, but the typical thing you can do is to use<br>shared server process instead of dedicated server processes. <br><br><a href="http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10739/manproc.htm">http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10739/manproc.htm</a><br><br>As a last suggestion you could try Oracle 10g (client and server, <br>or mix a 9.2 client with a 10g server), to see if it changes<br>something.<br><br><br>And of course make sure you use the very latest Genero 1.33 (we may have<br>fixed some bugs in the driver).<br><br><br>That's all so far - sorry I am not an Oracle DBA expert ;-) <br><br>Yours,<br>Seb<br><br><br>-----------------------------------------------------------------------<br><br>Micheal LAPORTE wrote:<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> <br></blockquote></div><br><br> ------=_Part_11308_16119912.1157570505956--