Re: Memory Usage in Oracle

jeffchirco_at_gmail.com wrote:
> Hi, I am the DBA of dawaves who started this thread. Our SGA and PGA
> are fixed at 1.4gigs and 400M respectively. And we are running 10g.
>
> Jeff
>
> Jerome Vitalis wrote:
> > dawaves wrote:
> > > Hello,
> > >
> > > I'm an AIX Systems Administrator who is kind of confused w/ Oracle's
> > > use of memory and I wanted to see if maybe some Oracle DBA's out there
> > > can help me out.
> > >
> > > Here is our situation:
> > >
> > > We have a 16GB Oracle Production Database running on AIX 5.3 ML04. We
> > > have a total of 16GB of RAM.
> > >
> > > It seems every time we had more physical RAM, the Oracle DB wants to
> > > use all the available RAM. Now when I mount the /oradata directory w/
> > > the 'cio' option, the Oracle Processes tend to use less which make
> > > sense since it is eliminating the file buffer portion in memory.
> > >
> > > Now is this normal for Oracle to use as much RAM as it can?
> > >
> > > What kind of systems are other DBA's running their Oracle Servers on
> > > and with how much RAM? How do you deal w/ Paging?
> > >
> > > If we cap the use of memory for our Oracle DB from the OS, can that
> > > lead to ramifications for our Oracle DB?
> > >
> > > Thanks for reading this and hopefully some Oracle DBA's out there can
> > > respond to some of my questions.
> > >
> > > thanks!
> > >
> >
> > Which Oracle version?
> >
> > And what are your instance parameters related to the SGA and PGA?

Are you using PL/SQL to process. Does it grab memory at startup or after the thing gets going. Are you swapping? If so,make sure it is not your PGA that is eating up all the memory because compiled code is allowing it to do so. You can't set an upper bounds if this is the case and your system will start to swap. PGA memory should be released back to the system after process completes if WORKAREA_SIZE_POLICY=AUTO. "PGA_AGGREGATE_TARGET.... It should be noted that this work area memory is for for sorting and hashing and does not in any way limit the amount of memory your PL/SQL programs or Java-stored procedures may allocate." tkyte, Effective Oracle By Design (2003 p360) Received on Thu Jan 11 2007 - 15:33:20 CST