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!

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?
>

No, in a word. I'd guess it's a reporting issue on AIX, but you know
that system and I don't. The (hideously oversimplified) memory usage of
Oracle is as follows.

1, A large shared memory area called the SGA. This is of fixed (or at
least capped) size. You can see how large this is by typing show sga at
a sqlplus prompt.
2. Private memory areas for use by client processes. This is either
fixed overall, or fixed per connection.


--
Niall Litchfield
Oracle DBA
http://www.orawin.info/services

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!


Looks like you don't use direct i/o (as recommended and as included
with the concurrent I/O option) and any Oracle access is going through
the file system cache.
As a 16 Gb database shouldn't need 16 Gb RAM, it also looks like there
is something seriously wrong with your database, and your firm has
resorted to 'more is better' tuning.
This won't help, ever.
Taking O/S measures to trim down Oracle memory usage would however
bring you further down on the path to disaster.

--
Sybrand Bakker
Senior Oracle DBA

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?

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?

Sybrandb:

Your wording is a little confusing so I just wanted to clarify some
things:

We are using the 'CIO' mount option. So this is the command I type to
mount the /oracle/oradata directory: mount -o cio /oracle/oradata.

Are you saying I should use 'DIO' instead? Is that what Oracle
recommends? If so could you post a link to the article that states
that?

What kind of setup do you have? How much memory? Size of DB?

thanks!

sybrandb 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!
>
>
> Looks like you don't use direct i/o (as recommended and as included
> with the concurrent I/O option) and any Oracle access is going through
> the file system cache.
> As a 16 Gb database shouldn't need 16 Gb RAM, it also looks like there
> is something seriously wrong with your database, and your firm has
> resorted to 'more is better' tuning.
> This won't help, ever.
> Taking O/S measures to trim down Oracle memory usage would however
> bring you further down on the path to disaster.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

jeffchirco@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)

I don't understand the question about suing PL/SQL to process.
After we reboot the system and before I start up the database it is
using 1.5 gigs of ram. Once I start the database it slowly starts to
creep up. Within 6 hours of the database running it was using 13 gigs
of memory.
workarea_size_policy is set to AUTO. How do I know of the PGA is
eating up all the memory?


EscVector wrote:
> jeffchirco@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)

Oh and the system does not page.


EscVector wrote:
> jeffchirco@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)

jeffchirco@gmail.com wrote:
> I don't understand the question about suing PL/SQL to process.

You can pass in sql or you can have your processes compiled in packages
procedures and functions. If your database has compiled pl/sql
procedures that create cursors, it can effectively eat up all your
memory if you have no programmatic limit on the cursor.

psuedo code example:
call procedure GetRecords
create cursor as select * from 1 terabyte table;
Work with cursor
End procedure

All available os memory will be allocated to the offending process,
system will swap, and die. It will die on the cursor create and never
get to the work.