From: Anil Rajapure (anil_unix1975@yahoo.com)
Date: Wed Dec 04 2002 - 09:28:06 EST
Thanks to bill & armanini Oscar.
Check their responses.
Hi
three more things:
1) never let your system swap: it's much much better reduce the SGA of each
instances.
Or, if your system is swapping, be sure that pages swapped aren't those of
Oracle SGA
neither Oracle Server processes
2)I have my buffer cache 5% and 5% (egual): Oracle uses SGA and doesn't need
HPUX cache.
3) I wonder what is the specific reason to use different instances. Consider
to use a single instance.
talk with your DBA to create all Oracle users into the same instance: they
will use
the same rollback segments (so your DBA might need more of them) and the
same system tablespace
but each will own its dedicated tablespaces (data, indexes, temporary).
Your SGA will allocated the sum of current three (or four SGA).
The Pros are:
- less Oracle Server processes (for 3 instances, you have about 3x 8
processes)
- less use of semaphores, IPC calls
The Cons are:
- you must use the same init.ora prameters for all
- you cannot shutdown a single instance to free some memory
To move from 3 instances to one, your DBA must export all Oracle users,
create the tablespaces
in the saved instance (possibly with the same name) and reimport the Oracle
users (if new tablespaces
have different names than previously, you must create all objects before
doing import, with option "ignore=Y")
For detailed infos on SGA, do a research in the archives
Bill Hassell <pooderbill@mindspring.com> wrote:Hi,
On Wed, 4 Dec 2002 03:05:50 -0800 (PST) Anil Rajapure
wrote:
> The problem is this server have three oracle
> instances on it with just 2GB mem.
> Now every oracle owner go on running this or
> that and finally system starts thrashing. This
> situation arises because every oracle owner
> says my processes/sql quries are OK. I have
> been asked to put one more oracle instance on
> this server.
VERY IMPORTANT: An Oracle owner will say that
their SQL or database layout is OK, but what
they really mean is: it works. That does NOT
mean the SQL is efficient or the indexes are
balanced or that enough indexes exist. It also
does not mean that the Oracle instance has a
reasonable amount of SGA. Generally speaking,
each Oracle instance should have 800-900 megs
for SGA such that a temp sort area on disk is
unneeded and that row insertions always fit
into SGA. There are a *lot* more Oracle tuning
features that will drastically improve the
overall speed of the system.
> Before putting that I would like to streamline
> things. SGA for all oracle owners would be such
> that everyone gets their due share. (I have
> asked every oracle owner to have similar
> init.ora as far as possible so that SGA is
> almost same for all.)
Unfortunately, this means that every Oracle
instance is heavily constrained and will run
very slowly as the load increases. There is
*NO* alternative to providing enough RAM. The
users will simply have to get used to very slow
response times with only 2Gb RAM.
> Not to forget my request for more memory was
> declined by my manager. (This all is
> development work he says)
Just as long as the manager understands that
the server will run *VERY* slowly and that
there is no workaround. It's sort of like
asking you to run a car without gas...find
some sort of workaround so you don't need gas.
> Memory has become so precious on this server
> that every bit counts that is why I am tuning
> ninode also. maxdsiz or it's 64 bit counterpart
> is 1 Gb.
ninode should be set to 1000 and that's it.
That may save a couple of megs. maxdsiz will
*NEVER* save memory! maxdsiz/maxdsize_64) are
runaway program fences. They limit the maximum
size that a program can grow. Now if your
programmers are just playing around to see how
much RAM they can get, setting maxdsiz low in
in the kernel will crash their programs when
they get too big (perhaps desirable) but unless
you start Oracle with ulimit to set maxdsiz for
Oracle to a larger value, Oracle will crash too.
> Thanks again. Would welcome more replies.
> Would someone explain me relationship between
> sga and shmmax or any other kernel parameter?.
SGA is Oracle's name for a shared memory area.
shmmax is simply a fence for the largest shared
segment that can be requested. Since you have so
very little memory, issuesw regarding large SGA
sizes are not important. Just note that 32bit
Oracle (like all 32bit programs) are severely
constrained beyond 1000 megs for data and/or
shared memory.
Also, shared memory for 32bit programs is kept
in a single map shared by every program that
needs shared memory. Fragmentation is very
common and Oracle instances may not start
simply because there is no single area large
enough to meet the Oracle requirements for SGA.
--
Best regards,
Bill Hassell, pooderbill@mindspring.com
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
--
---> Please post QUESTIONS and SUMMARIES only!! <---
To subscribe/unsubscribe to this list, contact majordomo@dutchworks.nl
Name: hpux-admin@dutchworks.nl Owner: owner-hpux-admin@dutchworks.nl
Archives: ftp.dutchworks.nl:/pub/digests/hpux-admin (FTP, browse only)
http://www.dutchworks.nl/htbin/hpsysadmin (Web, browse & search)
This archive was generated by hypermail 2.1.7 : Sat Apr 12 2008 - 11:02:22 EDT