The amount of memory provisioned in new servers keeps growing, we
have
several servers where single image of OS (Solaris, AIX, HP-UX) has
access to
more than 32 GB RAM. A question arises how to use this memory most
efficiently to run Oracle databases, in particular what problems one
may experience when running Oracle 9iR2 or 10gR2 with SGA > 20 GB?
I am particularly interested in systems with mixed workload, i.e.
where we see simultaneously highly efficient index-based queries, very
inefficient
queries joining multiple tables (consuming in excess of 800 million
buffer gets
per execution), large updates, dynamic SQL, ad-hoc queries, etc.
What would you do on a server with 100 - 200 GB RAM that runs one
Oracle instance?
- would you configure direct I/O (bypass UNIX filesystem buffer
cache)?
- would you configure SGA around 50 - 70% of physical RAM?
- would you use 8K block size of bigger blocks?
- would you allow automatic resizing of SGA components in 10g (ASMM)
or use fixed sizes?
- would you use some OS features like "large pages" on AIX in
combination with LOCK_SGA?

On Feb 1, 2:15=A0pm, vsevolod afanassiev <vsevolod.afanass...@gmail.com>
wrote:
> The amount of memory provisioned in new servers keeps growing, we
> have
> several servers where single image of OS (Solaris, AIX, HP-UX) has
> access to
> more than 32 GB RAM. A question arises how to use this memory most
> efficiently to run Oracle databases, in particular what problems one
> may experience when running Oracle 9iR2 or 10gR2 with SGA > 20 GB?
> I am particularly interested in systems with mixed workload, i.e.
> where we see simultaneously highly efficient index-based queries, very
> inefficient
> queries joining multiple tables (consuming in excess of 800 million
> buffer gets
> per execution), large updates, dynamic SQL, ad-hoc queries, etc.
> What would you do on a server with 100 - 200 GB RAM that runs one
> Oracle instance?
> - would you configure direct I/O (bypass UNIX filesystem buffer
> cache)?

This is very system dependent.

> - would you configure SGA around 50 - 70% of physical RAM?

IMO, that is just a wild-ass guess starting point from the days of
smaller systems.  Really, you need to determine what-all is going on
on a fully loaded running system.  Then Iterate.  With a mixed system,
you may want to allow some big PGA at times, to avoid spilling sorts
to disk, but that can have funny optimizer effects.  9i is different
than 10g, too, there's just no usable rule of thumb.

> - would you use 8K block size of bigger blocks?

There have been some very interesting discussions of this (like this
classic: http://www.oraclealchemist.com/oracle/hey-guys-does-size-matter/
) , which has convinced me to stay with 8K.  But my systems are more
limited than yours.

> - would you allow automatic resizing of SGA components in 10g (ASMM)
> or use fixed sizes?

My gut feel is to use it, then turn it off if it starts being too
stupid, where too stupid means things like getting tuning exactly
backwards because it is working with what-was.  Trust your testing
more than my gut.  There are stories floating about... my general view
is that all new features need some amount of time to work out the
bugs, the greater the spread of your feature usage in production, the
more likely you'll hit something obscure, which can mean "difficult to
replicate and fix."

> - would you use some OS features like "large pages" on AIX in
> combination with LOCK_SGA?

Haven't been on AIX recently to comment.  But it has always seemed
idiosyncratic among unix, to me.

Are you RAC?

jg
--
@home.com is bogus.
SAP and IBM say... what?
http://www.informationweek.com/news/software/database_apps/showArticle.jhtm=
l?articleID=3D222600700&subSection=3DNews

Thanks for responding.

Let's say there is no RAC, only standalone instances.

Of course "start from something and then iterate" is the best approach
available.
However it is not very practical as:
1. Changing SGA_MAX_SIZE requires outage.
2. We found that on heavily loaded system dynamic changes of
DB_CACHE_SIZE don't work very well.
For example when we tried to resize DB_CACHE_SIZE from 20 GB to 30 GB
it took almost an hour.
During this time database experienced heavy waits on 'log file sync' -
but there were no log switches
and few transactions. When we tried to reisize back to 20 GB the
instance crashed. This was 9.2.0.8.
3. We would rather avoid significant changes (e.g. increasing
DB_CACHE_SIZE from 20 GB to 40 GB),
this means that we need to go in small steps (20, 24, 28, 32, etc).
But this is time-consuming.
Many systems are on weekly/monthly cycle (i.e. certain batch job run
once per month),
so we'll have to wait one month to see the results. By that time the
data may change.

On Mon, 01 Feb 2010 14:15:33 -0800, vsevolod afanassiev wrote:

> The amount of memory provisioned in new servers keeps growing, we have
> several servers where single image of OS (Solaris, AIX, HP-UX) has
> access to
> more than 32 GB RAM. A question arises how to use this memory most
> efficiently to run Oracle databases, in particular what problems one may
> experience when running Oracle 9iR2 or 10gR2 with SGA > 20 GB? I am
> particularly interested in systems with mixed workload, i.e. where we
> see simultaneously highly efficient index-based queries, very
> inefficient
> queries joining multiple tables (consuming in excess of 800 million
> buffer gets
> per execution), large updates, dynamic SQL, ad-hoc queries, etc. What
> would you do on a server with 100 - 200 GB RAM that runs one Oracle
> instance?
> - would you configure direct I/O (bypass UNIX filesystem buffer cache)?
> - would you configure SGA around 50 - 70% of physical RAM? - would you
> use 8K block size of bigger blocks? - would you allow automatic resizing
> of SGA components in 10g (ASMM) or use fixed sizes?
> - would you use some OS features like "large pages" on AIX in
> combination with LOCK_SGA?

I have 64bit linux and here is one of my instances:


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit 
Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> show sga

Total System Global Area 1.7180E+10 bytes
Fixed Size                  2310752 bytes
Variable Size            8578973088 bytes
Database Buffers         8589934592 bytes
Redo Buffers                8650752 bytes
SQL> 
$>grep -i huge /proc/meminfo
HugePages_Total:  8192
HugePages_Free:    961
Hugepagesize:     2048 kB


The answer to your question is: yes, I do use huge pages because I don't 
want to waste memory on page tables. Also, huge pages aren't swapped or 
paged, handling of huge pages is much simpler. You're reducing your OS 
overhead by using huge pages.

On a IBM server that has between 100 and 200 GB RAM, I would urge 
management to license VLM ("Very Large Memory") option which allows me to 
have 32K blocks and allocate at least half of the memory for SGA. I would 
then call Connor McDonald to try getting bad BCHR on that machine.


-- 
http://mgogala.freehostia.com

On Feb 1, 5:19=A0pm, vsevolod afanassiev <vsevolod.afanass...@gmail.com>
wrote:
> Thanks for responding.

You're welcome.  Just noticed this:
http://hoopercharles.wordpress.com/2010/01/31/faulty-quotes-5-block-sizes/

>
> Let's say there is no RAC, only standalone instances.
>
> Of course "start from something and then iterate" is the best approach
> available.
> However it is not very practical as:
> 1. Changing SGA_MAX_SIZE requires outage.
> 2. We found that on heavily loaded system dynamic changes of
> DB_CACHE_SIZE don't work very well.
> For example when we tried to resize DB_CACHE_SIZE from 20 GB to 30 GB
> it took almost an hour.
> During this time database experienced heavy waits on 'log file sync' -
> but there were no log switches
> and few transactions. When we tried to reisize back to 20 GB the
> instance crashed. This was 9.2.0.8.
> 3. We would rather avoid significant changes (e.g. increasing
> DB_CACHE_SIZE from 20 GB to 40 GB),
> this means that we need to go in small steps (20, 24, 28, 32, etc).
> But this is time-consuming.
> Many systems are on weekly/monthly cycle (i.e. certain batch job run
> once per month),
> so we'll have to wait one month to see the results. By that time the
> data may change.

Thanks for the real data points.  Practicality usually wins.

jg
--
@home.com is bogus.
http://www.cleveland.com/living/index.ssf/2010/02/bill_watterson_creator_of=
_belo.html

Linux is a bit different as there is no direct I/O (as far as I know),
unless you use raw devices or ASM.
So memory that doesn't get allocated to SGA will be used for
filesystem buffer cache.

You have 17 GB SGA, and db_cache_size = 8.5 GB. This leaves 8.5 GB for
shared pool,
large pool, and Java pool. Seems too much? Is ASMM enabled? I think 1
GB should be enough for shared pool in most cases.

On Mon, 01 Feb 2010 20:02:08 -0800, vsevolod afanassiev wrote:

> Linux is a bit different as there is no direct I/O (as far as I know),
> unless you use raw devices or ASM.


Huh? There is direct I/O, I am using it for years, I have even been 
writing about it..


> So memory that doesn't get allocated to SGA will be used for filesystem
> buffer cache.

Yup. That's why I use direct I/O.




-- 
http://mgogala.byethost5.com

On Feb 1, 5:15=A0pm, vsevolod afanassiev <vsevolod.afanass...@gmail.com>
wrote:

snip

> The amount of memory provisioned in new servers keeps growing, we
> have
> several servers where single image of OS (Solaris, AIX, HP-UX) has
> access to
> more than 32 GB RAM. A question arises how to use this memory most
> efficiently to run Oracle databases, in particular what problems one
> may experience when running Oracle 9iR2 or 10gR2 with SGA > 20 GB?
> I am particularly interested in systems with mixed workload, i.e.
> where we see simultaneously highly efficient index-based queries, very
> inefficient
> queries joining multiple tables (consuming in excess of 800 million
> buffer gets
> per execution), large updates, dynamic SQL, ad-hoc queries, etc.
> What would you do on a server with 100 - 200 GB RAM that runs one
> Oracle instance?
> - would you configure direct I/O (bypass UNIX filesystem buffer
> cache)?
> - would you configure SGA around 50 - 70% of physical RAM?
> - would you use 8K block size of bigger blocks?
> - would you allow automatic resizing of SGA components in 10g (ASMM)
> or use fixed sizes?
> - would you use some OS features like "large pages" on AIX in
> combination with LOCK_SGA?

On a 32 gig linux system I would use huge pages and probably setup 16
gig for a one instance SGA.

Lots of rumours of problems with huge pages and ASMM in 10g/11g so I
would stay away from that.

Probably go for a 16 gig SGA and 8 to 10 gig PGA ( aggregate target )
and leave some memory left over since the aggregate target is not a
hard limit.

Throw most of your memory in the SGA at buffer cache.  How big does
your shared pool need to be?  Got any monster apps with a ton of
dynamic SQL that is not using bind variables?

Except for one system running Solaris all my stuff is linux these days
( OEL 5.4 ) so not sure exactly on your AIX question but if it makes
sense to reserve memory permanently for oracle ( that's huge pages )
then go for it probably.

vsevolod afanassiev wrote:
> The amount of memory provisioned in new servers keeps growing, we
> have
> several servers where single image of OS (Solaris, AIX, HP-UX) has
> access to
> more than 32 GB RAM. A question arises how to use this memory most
> efficiently to run Oracle databases, in particular what problems one
> may experience when running Oracle 9iR2 or 10gR2 with SGA > 20 GB?
> I am particularly interested in systems with mixed workload, i.e.
> where we see simultaneously highly efficient index-based queries, very
> inefficient
> queries joining multiple tables (consuming in excess of 800 million
> buffer gets
> per execution), large updates, dynamic SQL, ad-hoc queries, etc.
> What would you do on a server with 100 - 200 GB RAM that runs one
> Oracle instance?
> - would you configure direct I/O (bypass UNIX filesystem buffer
> cache)?
> - would you configure SGA around 50 - 70% of physical RAM?
> - would you use 8K block size of bigger blocks?
> - would you allow automatic resizing of SGA components in 10g (ASMM)
> or use fixed sizes?
> - would you use some OS features like "large pages" on AIX in
> combination with LOCK_SGA?

Here's a "must read" for Oracle configuration & tuning on AIX:
http://www.ibm.com/developerworks/wikis/download/attachments/104533513/Oracle_AIX+Tuning+1.pdf

-- 
Jeroen 

On Tue, 02 Feb 2010 15:22:22 -0800, hpuxrac wrote:

> On a 32 gig linux system I would use huge pages and probably setup 16
> gig for a one instance SGA.

Yup. That's precisely what I did.



-- 
http://mgogala.freehostia.com