Very Computer

by **Aidan Evan** » Thu, 21 Jun 2001 06:11:20

A DBA here wants to improve performance of an Oracle 8.1.7 database job
on AIX 4.3.3 (it runs about half the speed it did on Oracle 7.4.3). He
requested that we restrict the amount of memory used for file caching to
15% (from the default maximum of 80%) of the one gigabyte of memory, i.e.,
to about 150 MB. The theory was that Oracle processes would use more real
memory and thus run faster. The request to restrict file memory was based
on a recomendation from an IBM redbook on tuning Oracle on AIX:

If you can estimate how much memory is left after allowing for:

o AIX (32 MB)
o The basic Oracle process
o The Oracle Servers
o The user application processes

then we recommend to allocate the following amount of the remaining
memory:

o For JFS based databases, 75%

We restricted the file memory available with

vmtune -p 10 -P 15 -h 1

i.e., parameter "minperm" set to 10% of memory and "maxperm" set to 15%,
and the latter is a hard limit.

The job in fact ran a little slower than with maxperm at 80%. There was
paging (page space page in/out) and Oracle processes did not use
significantly more memory; there was over 700 megabytes free (observed via
the "monitor" program (http://www.mesa.nl/monitor)).

My explanation of some of the observed behaviour is that the Oracle
processes did not need any more real memory and in fact Oracle database
file I/O was hampered by the restricted amount of file memory. However, I
don't have a good explanation for the increased paging: obviously if it
didn't happen until file memory was restricted, that's the cause, but what
is now being paged that was not before? As I understand it, page space
paging numbers refer only to process and working storage memory going back
and forth to the page file and there shouldn't be any need for this because
there's plenty of memory available.

| 494-3332 | University Computing & Information Services
| Dalhousie University, Halifax, N.S., Canada

by **Brett Gee** » Thu, 21 Jun 2001 17:29:46

Morning,

first thing, I'm not an oracle user (informix dba speaking), here's
some things from our experience.

1) Are you talking to raw disks? If not, poof, theres a bigass
performance hit there immediately.
2) If you are talking to them, are you using the block or character
devices? Make sure the are the /dev/r* devices, otherwise poof
another 15-25% drop

We found that the file cache you are refering to did not help us at all, we talk
directly to the disks, its quicker, we dropped it much like you did and
pushed the additional free'd memory into informix's cache.

Another thing, if you are talking to files and you attempt to cache it
yourself, be aware that the o/s is going to cache aswell. Oracle's cache
mechanism is probably a lot more efficient than the o/s.

Bottom line, talk to raw disks, tune the disk cache down and push that
into oracle's cache.

Be aware though that your time loading apps will increase since they
aren't cached as much anymore

brett

> A DBA here wants to improve performance of an Oracle 8.1.7 database job
> on AIX 4.3.3 (it runs about half the speed it did on Oracle 7.4.3). He
> requested that we restrict the amount of memory used for file caching to
> 15% (from the default maximum of 80%) of the one gigabyte of memory,
> i.e., to about 150 MB. The theory was that Oracle processes would use
> more real memory and thus run faster. The request to restrict file
> memory was based on a recomendation from an IBM redbook on tuning Oracle
> on AIX:
> If you can estimate how much memory is left after allowing for:
> x
> o AIX (32 MB)
> o The basic Oracle process
> o The Oracle Servers
> o The user application processes

> then we recommend to allocate the following amount of the
> remaining memory:

> o For JFS based databases, 75%

> We restricted the file memory available with

> vmtune -p 10 -P 15 -h 1
> i.e., parameter "minperm" set to 10% of memory and "maxperm" set to 15%,
> and the latter is a hard limit.

> The job in fact ran a little slower than with maxperm at 80%. There
> was
> paging (page space page in/out) and Oracle processes did not use
> significantly more memory; there was over 700 megabytes free (observed
> via the "monitor" program (http://www.mesa.nl/monitor)).
> My explanation of some of the observed behaviour is that the Oracle
> processes did not need any more real memory and in fact Oracle database
> file I/O was hampered by the restricted amount of file memory. However,
> I don't have a good explanation for the increased paging: obviously if
> it didn't happen until file memory was restricted, that's the cause, but
> what is now being paged that was not before? As I understand it, page
> space paging numbers refer only to process and working storage memory
> going back and forth to the page file and there shouldn't be any need
> for this because there's plenty of memory available.

> | 494-3332 | University Computing & Information Services
> | Dalhousie University, Halifax, N.S., Canada

by **Aidan Evan** » Thu, 21 Jun 2001 22:55:06

>Morning,
> first thing, I'm not an oracle user (informix dba speaking), here's
> some things from our experience.
> 1) Are you talking to raw disks? If not, poof, theres a bigass
> performance hit there immediately.

Not raw disks. The database is in JFS files. I am aware that raw disks
are supposed to run faster and that with JFS both Oracle and AIX cache.
However, the behaviour I am trying to understand is that despite,
supposedly, having more real memory available for program code and working
storage we are seeing actual paging where there was none before. One
explanation I suppose could be that there is paging between (now
inadequate) file memory and paging storage, but from what I have read I
didn't think this happened.

P.S. I had an e-mail inquiry asking for the title of the Redbook I
referred to. It's "Database Performance on AIX in DB2 UDB and Oracle
Environments", SG-24-5511-00.

| 494-3332 | University Computing & Information Services
| Dalhousie University, Halifax, N.S., Canada

by **Piotrek Kapczu** » Fri, 22 Jun 2001 02:49:10

Hi there

> >Morning,

> > first thing, I'm not an oracle user (informix dba speaking), here's
> > some things from our experience.

> > 1) Are you talking to raw disks? If not, poof, theres a bigass
> > performance hit there immediately.

> Not raw disks. The database is in JFS files. I am aware that raw disks
> are supposed to run faster and that with JFS both Oracle and AIX cache.
> However, the behaviour I am trying to understand is that despite,
> supposedly, having more real memory available for program code and working
> storage we are seeing actual paging where there was none before. One
> explanation I suppose could be that there is paging between (now
> inadequate) file memory and paging storage, but from what I have read I
> didn't think this happened.

> P.S. I had an e-mail inquiry asking for the title of the Redbook I
> referred to. It's "Database Performance on AIX in DB2 UDB and Oracle
> Environments", SG-24-5511-00.

Are you 100% sure there are physical writes to disks ? Maybe it's just
normal LRU allocation work

In monitor pgsin are pgsout are the only ones which you should care

73.9 pgfaults
9.9 pgin
24.6 pgout
0.0 pgsin
0.0 pgsout

These few lines take from monitor above say that there are no paging at the
moment. I suggest to watch vmstat's pi and po columns.

--
Regards
Piotr Kapczuk

by **Brett Gee** » Fri, 22 Jun 2001 15:56:51

Files, ech... aways personal preference aside, just a few things, you are
paging, ok, lets look at it.

Does oracle have a cap on the amount of memory it may consume?

AIX apparently will free some of the cache when memory is required, the
manuals (somewhere) say that it uses all 'free' memory for disk cache and
releases as required.

Your problem is unlikely to be the cache mechanism, more likely an app
grabbing more memory than you think. Try firing up monitor and see whats
up

brett

>>Morning,

>> first thing, I'm not an oracle user (informix dba speaking), here's
>> some things from our experience.

>> 1) Are you talking to raw disks? If not, poof, theres a bigass
>> performance hit there immediately.

> Not raw disks. The database is in JFS files. I am aware that raw
> disks
> are supposed to run faster and that with JFS both Oracle and AIX cache.
> However, the behaviour I am trying to understand is that despite,
> supposedly, having more real memory available for program code and
> working storage we are seeing actual paging where there was none before.
> One explanation I suppose could be that there is paging between (now
> inadequate) file memory and paging storage, but from what I have read I
> didn't think this happened.
> P.S. I had an e-mail inquiry asking for the title of the Redbook I
> referred to. It's "Database Performance on AIX in DB2 UDB and Oracle
> Environments", SG-24-5511-00.

> | 494-3332 | University Computing & Information Services
> | Dalhousie University, Halifax, N.S., Canada

by **Rodney Clar** » Fri, 22 Jun 2001 17:42:40

There is a oracle white paper on the net out there that simply states.
That unless your absolutely i/o bound raw devices offer almost no
advantages.
Seems that most of the perf gains reported when moving from files to raw was
simply because an export import reorganised the data.
If you then go back to files performance stays.

Side note
Although the ssa raid10 option performs very nicely and solves the mwcc perf
hit

Now back to the memory rule.
we reserve about 4 Mbyte per client then give all the rest to the oracle sga
and use vmtune -p5 -P10 to tell AIX not to cache to much.
eg. 100 clients in a 1 gbyte box
This give 400 needed for the clients and configure the sga to about 400
Mbyte and let AIX have the rest. Check the size of the oracle sga memory use
with ipcs -mb
Check that no paging is happening if yes reduce the sga size till it stops.
This is a very large simplification.

> Files, ech... aways personal preference aside, just a few things, you are
> paging, ok, lets look at it.

> Does oracle have a cap on the amount of memory it may consume?

> AIX apparently will free some of the cache when memory is required, the
> manuals (somewhere) say that it uses all 'free' memory for disk cache and
> releases as required.

> Your problem is unlikely to be the cache mechanism, more likely an app
> grabbing more memory than you think. Try firing up monitor and see whats
> up

> brett

> >>Morning,

> >> first thing, I'm not an oracle user (informix dba speaking), here's
> >> some things from our experience.

> >> 1) Are you talking to raw disks? If not, poof, theres a bigass
> >> performance hit there immediately.

> > Not raw disks. The database is in JFS files. I am aware that raw
> > disks
> > are supposed to run faster and that with JFS both Oracle and AIX cache.
> > However, the behaviour I am trying to understand is that despite,
> > supposedly, having more real memory available for program code and
> > working storage we are seeing actual paging where there was none before.
> > One explanation I suppose could be that there is paging between (now
> > inadequate) file memory and paging storage, but from what I have read I
> > didn't think this happened.
> > P.S. I had an e-mail inquiry asking for the title of the Redbook I
> > referred to. It's "Database Performance on AIX in DB2 UDB and Oracle
> > Environments", SG-24-5511-00.

> > | 494-3332 | University Computing & Information Services
> > | Dalhousie University, Halifax, N.S., Canada

by **Aidan Evan** » Fri, 22 Jun 2001 22:36:20

>Hi there
...
>Are you 100% sure there are physical writes to disks ? Maybe it's just
>normal LRU allocation work
>In monitor pgsin are pgsout are the only ones which you should care
>73.9 pgfaults
> 9.9 pgin
>24.6 pgout
> 0.0 pgsin
> 0.0 pgsout

Yes, I'm sure there is physical paging. pgsin and pgsout are both
significantly non-zero, the combined totals roughly between 50 and 175 per
second. I should have been more explicit when I wrote

There was paging (page space page in/out)

Here's a rough gnuplot plot to give the flavour:

250 ++---+----+----+----+----+----+-----+----+----+----+----+----+----+---++
+ + + '0616/vmpaging' using 4:($11+$12) ****** +
| |
200 ++ ++
| |
| * ** |
| * ** |
150 ++ * * * ** ++
| * * * ** |
| * * * ** |
100 ++ * * * * * ** * ++
| * * * * * ** * * * |
| * * * * * * * * * * |
50 ++ * * * * * * * * ** * *++
| * * * * * * * * * * ** * * * **|
| * * *** ** * * * * * * * * * * ** * * *|
| ******* * * *** * ** ** * * * * * * * * * * *** *|
0 *** * ***** * ** *** *** *** ** ** ** * **
| * |
+ + + + + + + +
-50 ++---+----+----+----+----+----+-----+----+----+----+----+----+----+---++
10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00

| 494-3332 | University Computing & Information Services

by **Aidan Evan** » Sat, 23 Jun 2001 02:47:34

>Files, ech... aways personal preference aside, just a few things, you are
>paging, ok, lets look at it.
>Does oracle have a cap on the amount of memory it may consume?

It does have

data=262144 (131 megabytes)
stack=65536 ( 32 megabytes)

but this is per process and as far as I can see none of the Oracle
processes are that big.

Quote:>AIX apparently will free some of the cache when memory is required, the
>manuals (somewhere) say that it uses all 'free' memory for disk cache and
>releases as required.

"vmtune -p minperm% -P maxperm%" with "-h 1" fixes the amount of memory
that can be used for file caching to "minperm". When this command is
issued the amount of file memory immediately drops from 70-80% of total
memory down to the percentage specified and a whole whack of memory becomes
free. (As I understand it, without "-h 1" file memory is reduced (pages
taken from it and given to processes) only when process paging I/O
outweighs file I/O; the DBA wanted a guarantee that file memory would be
restricted, hence the use of "-h 1").

Quote:>Your problem is unlikely to be the cache mechanism, more likely an app
>grabbing more memory than you think. Try firing up monitor and see whats
>up

Thanks, it is monitor that's telling me there's physical paging
(pgsin/pgsout non-zero) and that 700 MB of memory is free.

| 494-3332 | University Computing & Information Services
| Dalhousie University, Halifax, N.S., Canada

by **W. Klo** » Sun, 24 Jun 2001 20:08:26

>>Files, ech... aways personal preference aside, just a few things, you are
>>paging, ok, lets look at it.

>>Does oracle have a cap on the amount of memory it may consume?

> It does have

> data=262144 (131 megabytes)
> stack=65536 ( 32 megabytes)

>but this is per process and as far as I can see none of the Oracle
>processes are that big.

>>AIX apparently will free some of the cache when memory is required, the
>>manuals (somewhere) say that it uses all 'free' memory for disk cache and
>>releases as required.

> "vmtune -p minperm% -P maxperm%" with "-h 1" fixes the amount of memory
>that can be used for file caching to "minperm". When this command is
>issued the amount of file memory immediately drops from 70-80% of total
>memory down to the percentage specified and a whole whack of memory becomes
>free. (As I understand it, without "-h 1" file memory is reduced (pages
>taken from it and given to processes) only when process paging I/O
>outweighs file I/O; the DBA wanted a guarantee that file memory would be
>restricted, hence the use of "-h 1").

>>Your problem is unlikely to be the cache mechanism, more likely an app
>>grabbing more memory than you think. Try firing up monitor and see whats
>>up

> Thanks, it is monitor that's telling me there's physical paging
>(pgsin/pgsout non-zero) and that 700 MB of memory is free.

Try vmtune without the -h 1. We also had problems with it and
discussed this with IBM development; the -h does not exactly work as
it is documented, mainly because it flushes ALL processes to the
paging area if it hits the maxperm-limit.

If you have this much memory free, why not try to give more memory to
the oracle-database ? This should boost performance of the job, if it
is really a memory-problem.

Willy Klotz, Oracle DBA

- Show quoted text -

> | 494-3332 | University Computing & Information Services
> | Dalhousie University, Halifax, N.S., Canada

by **Markus B?rtsch** » Sun, 24 Jun 2001 22:16:59

Quote:
> If you have this much memory free, why not try to give more memory to
> the oracle-database ? This should boost performance of the job, if it
> is really a memory-problem.

Just want to say the same thing. If you have a lot of free memory when
restricting the file cache with vmtune and the job got slower, then your
Oracle SGA is too small. You should make it big enough to use up all
memory without causing much paging.

Markus
--
Markus Baertschi
Bas du Ross 14b
CH-1163, Etoy, Switzerland

by **Aidan Evan** » Wed, 27 Jun 2001 06:17:39

>Try vmtune without the -h 1. We also had problems with it and
>discussed this with IBM development; the -h does not exactly work as
>it is documented, mainly because it flushes ALL processes to the
>paging area if it hits the maxperm-limit.

Thanks; what you say matches what I experienced: restricting file memory
with a hard limit causes more paging, not less. Saves me a call to IBM
support which is where I was heading.

I used "-h 1" because the DBA wanted AIX not to use more than 15% for
file caching, and indeed it didn't ("be careful what you ask for").

Quote:>If you have this much memory free, why not try to give more memory to
>the oracle-database ? This should boost performance of the job, if it
>is really a memory-problem.

>Willy Klotz, Oracle DBA

It's at least partly a memory problem. We ran some more tests. With
"db_block_buffers" set to the equivalent of 20 MB, the job ran in 20.4
minutes. Increasing to 50 MB, even with the vmtune -h 1, reduced this to
15.7 minutes; putting vmtune back to the defaults reduced paging
significantly and the time to 11.2 minutes (but I made the mistake of
telling the DBA he didn't need to restart Oracle between these two tests,
so it's not clear how much improvment was because of data already in the
SGA). Going to 100 MB produced a small improvement (to 10.8 minutes) and
did't have much paging. It seems from the memory numbers I collected with
"monitor" that AIX gave Oracle pretty much the real memory it needed.

It turns out the real problem the DBA is trying to solve is a 50%
slowdown of this job on Oracle 8.1.7 relative to Oracle 7.3.4, both on the
same AIX 4.3.3 machine. At first glance it seems to me that this is
therefore an Oracle problem to be solved, not an AIX one.

| 494-3332 | University Computing & Information Services

by **cyb..** » Wed, 27 Jun 2001 16:58:22

> >Try vmtune without the -h 1. We also had problems with it and
> >discussed this with IBM development; the -h does not exactly work as
> >it is documented, mainly because it flushes ALL processes to the
> >paging area if it hits the maxperm-limit.

> Thanks; what you say matches what I experienced: restricting file memory
> with a hard limit causes more paging, not less. Saves me a call to IBM
> support which is where I was heading.

> I used "-h 1" because the DBA wanted AIX not to use more than 15% for
> file caching, and indeed it didn't ("be careful what you ask for").

> >If you have this much memory free, why not try to give more memory to
> >the oracle-database ? This should boost performance of the job, if it
> >is really a memory-problem.

> >Willy Klotz, Oracle DBA

> It's at least partly a memory problem. We ran some more tests. With
> "db_block_buffers" set to the equivalent of 20 MB, the job ran in 20.4
> minutes. Increasing to 50 MB, even with the vmtune -h 1, reduced this to
> 15.7 minutes; putting vmtune back to the defaults reduced paging
> significantly and the time to 11.2 minutes (but I made the mistake of
> telling the DBA he didn't need to restart Oracle between these two tests,
> so it's not clear how much improvment was because of data already in the
> SGA). Going to 100 MB produced a small improvement (to 10.8 minutes) and
> did't have much paging. It seems from the memory numbers I collected with
> "monitor" that AIX gave Oracle pretty much the real memory it needed.

> It turns out the real problem the DBA is trying to solve is a 50%
> slowdown of this job on Oracle 8.1.7 relative to Oracle 7.3.4, both on the
> same AIX 4.3.3 machine. At first glance it seems to me that this is
> therefore an Oracle problem to be solved, not an AIX one.

> | 494-3332 | University Computing & Information Services

Just a thought - did you make any changes to async io as part of the
oracle upgrade?

--
Regards,
Clive

by **Aidan Evan** » Wed, 27 Jun 2001 23:15:41

>Just a thought - did you make any changes to async io as part of the
>oracle upgrade?

Well that's another story. The tests were run on AIX 4.3.3 maintenance
level 6. When async I/O was turned on, the system hung, so needless to say
async I/O was not used in the 8.1.7 benchmark. We installed ML8 this
morning which includes APAR IY13988 "DISABLE_LOCK NEEDED IN FREEPOOL_ENQUE"
and async I/O no longer hangs the system.

I don't have complete information from the DBA on the Oracle 7.3.4 job's
performance. We may be able to run the 7.3.4 job again to get a proper
comparison with 8.1.7.

| 494-3332 | University Computing & Information Services

by **Kik** » Fri, 06 Jul 2001 18:27:18

> A DBA here wants to improve performance of an Oracle 8.1.7 database job
> on AIX 4.3.3 (it runs about half the speed it did on Oracle 7.4.3). He
> requested that we restrict the amount of memory used for file caching to
> 15% (from the default maximum of 80%) of the one gigabyte of memory, i.e.,
> to about 150 MB. The theory was that Oracle processes would use more real
> memory and thus run faster. The request to restrict file memory was based
> on a recomendation from an IBM redbook on tuning Oracle on AIX:

> If you can estimate how much memory is left after allowing for:

> o AIX (32 MB)
> o The basic Oracle process
> o The Oracle Servers
> o The user application processes

> then we recommend to allocate the following amount of the remaining
> memory:

> o For JFS based databases, 75%

> We restricted the file memory available with

> vmtune -p 10 -P 15 -h 1

> i.e., parameter "minperm" set to 10% of memory and "maxperm" set to 15%,
> and the latter is a hard limit.

> The job in fact ran a little slower than with maxperm at 80%. There was
> paging (page space page in/out) and Oracle processes did not use
> significantly more memory; there was over 700 megabytes free (observed via
> the "monitor" program (http://www.mesa.nl/monitor)).

> My explanation of some of the observed behaviour is that the Oracle
> processes did not need any more real memory and in fact Oracle database
> file I/O was hampered by the restricted amount of file memory. However, I
> don't have a good explanation for the increased paging: obviously if it
> didn't happen until file memory was restricted, that's the cause, but what
> is now being paged that was not before? As I understand it, page space
> paging numbers refer only to process and working storage memory going back
> and forth to the page file and there shouldn't be any need for this because
> there's plenty of memory available.

> | 494-3332 | University Computing & Information Services
> | Dalhousie University, Halifax, N.S., Canada

What's your real question :

1) WHy performance slow down when you migrate to Oracle 8

2) Or why you're paging more when you modify the maxperm.

1) Be carefull with Oracle 8 you have a lot of new parameter in
initSID.ora
like java_pool_size which reserve 64Mo by default in the SGA if you
don't specify explicitly 0 ...
and other parameters which were existing in 7 but change their default
value.

2) Could you send me all the result of your vmtune command.
If you minimize maxperm (> minperm at least 5%) you should paginate
less.
But it's theory and it seems that IBM doen't respect that at all.
strict_maxperm should solve that but it doesn't really work too ...

CD :

Very Computer

Oracle 8.1.7 memory usage on AIX 4.3.3

Oracle 8.1.7 memory usage on AIX 4.3.3

Oracle 8.1.7 memory usage on AIX 4.3.3

Oracle 8.1.7 memory usage on AIX 4.3.3

Oracle 8.1.7 memory usage on AIX 4.3.3

Oracle 8.1.7 memory usage on AIX 4.3.3

Oracle 8.1.7 memory usage on AIX 4.3.3

Oracle 8.1.7 memory usage on AIX 4.3.3

Oracle 8.1.7 memory usage on AIX 4.3.3

Oracle 8.1.7 memory usage on AIX 4.3.3

Oracle 8.1.7 memory usage on AIX 4.3.3

Oracle 8.1.7 memory usage on AIX 4.3.3

Oracle 8.1.7 memory usage on AIX 4.3.3

Oracle 8.1.7 memory usage on AIX 4.3.3

Oracle 8.1.7 memory usage on AIX 4.3.3