Oracle 9i SGA Sizing on 32 bit machine

Printable View

10-09-2006, 04:56 AM
navisd

Oracle 9i SGA Sizing on 32 bit machine

I am in a way to tune oracle 9i Instance on 32 bit windows, system have 4 GB memory, Since i dont want to enable VLM so i want to create SGA upto 1.7 GB. Now the question i have is that how i will manage my instance within 1.7 GB. I applied following settings

SGA_MAX_SIZE = 1552.57 MB
DB_CACHE_SIZE = 1200 MB
SHARED_POOL_SIZE = 256 MB
LARGE_POOL_SIZE = 56 MB
JAVA_POOL_SIZE = 8 MB
LOG_BUFFER = 8 MB
PGA_AGGREGATE_TARGET = 300 MB

when i tried to give 600MB to pga_aggregate_target and restart the instance it crashed, not enough memory available on system to accomodate pga_aggregate_target size.
With above setting i successfully restart the server again, cache_hit_ratio is 60%.

Now my question is that how i will size the oracle instance within 1.7 GB memory. This 1.7 GB limit is with pga_aggregate_target i mean
ORACLE_INSTANCE + PGA_AGGREGATE_TARGET < 1.7G ?

if yes then how i will come to know that how much memory i will give to
shared_pool
db_cache_size
pga_aggregate
sga_max_size
Please suggest me for 1.7GB SGA.
10-09-2006, 05:07 AM
davey23uk

they are all completely application dependant, how much memory you need for your shared pool depends on how much sql you use. buffer cache - how much data you are dealing with.

So there is no answer to your question, you need to know how your aplication behaves
10-09-2006, 05:37 AM
yxez

yeah i agree...it doesnt mean that 1.7Gb memory will make ur system
attain peak performance....but maybe it will suffice at 500MB...then
gradually adjust it from there as u tune it. Tuning is sometimes
trial n error.

I hope this helps :)
10-10-2006, 12:46 AM
navisd

I need to adjust SGA/PGA to keep in my mind that i have limit of 1.7GB on windows, regarding performance i dont care right now about perfromance. I just want to know that how i will distribute the memory with in 1.7GB.
10-10-2006, 01:14 AM
yxez

hmnn...maybe u have to increase gradually by 10Mb its effect to the
whole sum might be great.
10-10-2006, 04:18 AM
davey23uk

Quote:

Originally Posted by navisd

I need to adjust SGA/PGA to keep in my mind that i have limit of 1.7GB on windows, regarding performance i dont care right now about perfromance. I just want to know that how i will distribute the memory with in 1.7GB.

if you dont care about performance, why do you need to adjust it at all?
10-10-2006, 05:41 AM
navisd

Performance is the next step first thing i need to utilize memory upto 1.7 G as system have 4 GB memory.
10-10-2006, 06:44 AM
davey23uk

why do you 'need' to, if you arent changing it for performace why are you changing at all
10-10-2006, 01:32 PM
navisd

Well performance is one concern but i again say i want to utilize maximum memory on windows box as there is 4 GB ram and most of it is free.Right now OS utilizing some memory and oracle utilizing only 1 GB of ram, 2-2.5GB is free most of the time. So i decided to utilize the free memory. This is the reason behind all this. I know how to enable VLM. As my favourite OS is Linux, I have configured 12GB instance on 32 bit machine with 16GB RAM. I successfully hit more than 1000 active users on that server and it didn't gave any error. So here my goal is to create 1.7GB oracle instance on 32 bit windows plateform which will work efficiently. Please suggest me how i will size my sga with in 1.7GB Limit and also how much memory i will give to PGA. Remember I need efficient, effective, optimized settings with 1.7GB restriction.
10-10-2006, 07:16 PM
davey23uk

you are coming at it from totally the wrong direction, you need to work out what you need, how your application works and how much memory you need.

You cant just say give Xmb to shared pool, Ymb to buffer cache, it just doesnt work like that.

It makes no sense to use memory just cos you have it - why not use it for something that actually needs it
10-10-2006, 08:08 PM
yxez

I think he is just proactive davey....well he got a point :)
Navis...I told u its a trial n error thing, u adjust(increase) it gradually
by 10 or 20 Mb then bounce the db, until u hit the desired value.
I noticed that u abruptly increased it from 300 to 600Mb.
But remind you, its really be ok if u have to keep that free
memory because u never know when the system needs it for adhoc/
unexpectied activities ;)
10-10-2006, 08:11 PM
davey23uk

more crazy talk - you dont just increase it , you change it in response to fixing a specific problem
10-10-2006, 08:24 PM
yxez

oh..you r still awake davey? I think London time is 3AM ...r u
in night shift? :)

Anyway just for a point of discussion....Suppose I give you
100GB additional Memory on your Prod Server. What do u gonna do
with it? :D

Well for me ....all add it up to my SHARED_POOL and load all
my tables in the memory? Is it a sound idea? How do u load all
the tables in the memory anyway?
10-11-2006, 12:25 AM
navisd

Then what parameters to be conisdered? Tell me in detail please
10-11-2006, 01:12 AM
pareshjavkar

Hi,

Large memory support is available in Windows Server 2003 and in Windows
2000
View products that this article applies to.
Article ID : 283037
Last Review : May 23, 2006
Revision : 9.1
This article was previously published under Q283037
SUMMARY
This article describes Physical Address Extension (PAE) and Address
Windowing
Extensions (AWE) and explains how they work together. This article also
discusses the
limitations of using memory beyond the 4-gigabyte (GB) range that is
inherent to 32-bit
operating systems.
Back to the top
MORE INFORMATION
PAE is the added ability of the IA32 processor to address more than 4
GB of physical
memory. The following operating systems can use PAE to take advantage
of physical
memory beyond 4 GB:
• Microsoft Windows 2000 Advanced Server
• Microsoft Windows 2000 Datacenter Server
• Microsoft Windows Server 2003, Enterprise Edition
• Microsoft Windows Server 2003, Datacenter Edition
To enable PAE, use the /PAE switch in the Boot.ini file.

Note In Windows Server 2003, PAE is automatically enabled only if the
server is using
hot-add memory devices. In this case, you do not have to use the /PAE
switch on a
system that is configured to use hot-add memory devices. In all other
cases, you must
use the /PAE switch in the Boot.ini file to take advantage of memory
over 4GB.

Typically, a process running under Windows 2000 or Windows Server 2003
can access up to
2 GB of memory address space (assuming the /3GB switch was not used)
with some of the
memory being physical memory and some being virtual memory. The more
programs (and,
therefore, more processes) that run, the more memory you commit up to
the full 2 GB of
address space.

When this situation occurs, the paging process increases dramatically
and performance
may be negatively impacted. The Windows 2000 and Windows Server 2003
memory managers
use PAE to provide more physical memory to a program. This reduces the
need to swap the
memory of the page file and results in increased performance. The
program itself is not
aware of the actual memory size. All the memory management and
allocation of the PAE
memory is handled by the memory manager independently of the programs
that run.

The preceding information is valid for programs that run when the /3GB
switch is used.
A program that requests 3 GB of memory is more likely to be able to
have more of its
memory remain in physical memory rather than be paged out. This
increases the
performance of programs that are capable of using the /3GB switch. The
exception is
when the /3GB switch is used in conjunction with the /PAE switch. In
this case, the
operating system does not use any memory in excess of 16 GB. This
behavior is caused by
kernel virtual memory space considerations. Thus, if the system
restarts with the /3GB
entry in the Boot.ini file, and the system has more than 16 GB of
physical memory, the
additional physical random access memory (RAM) is not used by the
operating system.
Restarting the computer without the /3GB switch enables the use of all
the physical
memory.

AWE is a set of application programming interfaces (APIs) to the memory
manager
functions that enables programs to address more memory than the 4 GB
that is available
through standard 32-bit addressing. AWE enables programs to reserve
physical memory as
non-paged memory and then to dynamically map portions of the non-paged
memory to the
program's working set of memory. This process enables memory-intensive
programs, such
as large database systems, to reserve large amounts of physical memory
for data without
having to be paged in and out of a paging file for usage. Instead, the
data is swapped
in and out of the working set and reserved memory is in excess of the 4
GB range.
Additionally, the range of memory in excess of 4 GB is exposed to the
memory manager
and the AWE functions by PAE. Without PAE, AWE cannot reserve memory in
excess of 4 GB.

The following is an example of a Boot.ini file where the PAE switch has
been added:
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003,
Enterprise" /fastdetect /PAE

Warning The contents of your Boot.ini file will vary based upon your
configuration. For
more information, click the following article number to view the
article in the
Microsoft Knowledge Base:
317526 (http://support.microsoft.com/kb/317526/) How to edit the
Boot.ini file in
Windows Server 2003
To summarize, PAE is a function of the Windows 2000 and Windows Server
2003 memory
managers that provides more physical memory to a program that requests
memory. The
program is not aware that any of the memory that it uses resides in the
range greater
than 4 GB, just as a program is not aware that the memory it has
requested is actually
in the page file.

AWE is an API set that enables programs to reserve large chunks of
memory. The reserved
memory is non-pageable and is only accessible to that program. For more
information
about AWE and PAE, click the following article number to view the
article in the
Microsoft Knowledge Base:
268363 (http://support.microsoft.com/kb/268363/) Intel Physical
Addressing Extensions
(PAE) in Windows 2000
For more information, visit the following Microsoft Web sites:
http://msdn.microsoft.com/library/ps...erver_1fnd.htm (
http://msdn.microsoft.com/library/ps...erver_1fnd.htm)
http://msdn.microsoft.com/library/de...l=/library/en-
us/dngenlib/html/awewindata.asp
(http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dngenlib/html/awewindata.asp )

If you add more memory to the system, it is possible that the BIOS will
recognize the
full amount of physical RAM that is installed in the server but that
Windows will
recognize only a part of the RAM. If the server has a redundant memory
feature or a
memory mirroring feature that is enabled, the full complement of memory
may not be
visible to Windows. Redundant memory provides the system with a
failover memory bank
when a memory bank fails. Memory mirroring splits the memory banks into
a mirrored set.
Both features are enabled or disabled in the BIOS and cannot be
accessed through
Windows. To modify the settings for these features, you may have to
refer to the system
user manual or the OEM Web site. Alternatively, you may have to contact
the hardware
vendor.

For example, if you are running a system that has 4 GB of RAM installed
and you then
add 4 GB of additional RAM, Windows may recognize only 4 GB of physical
memory or
possibly 6 GB instead of the full 8 GB. The redundant memory feature or
the memory
mirroring feature may be enabled on the new memory banks without your
knowledge. These
symptoms are similar to the symptoms that occur when you do not add the
/PAE switch to
the Boot.ini file.
Back to the top
Link http://support.microsoft.com/kb/283037/
10-11-2006, 03:50 AM
Thomasps

Quote:

Originally Posted by yxez

Well for me ....all add it up to my SHARED_POOL and load all
my tables in the memory?

Nice to see that you are loading all your tables into "SHARED_POOL" :D
10-11-2006, 05:50 AM
yxez

Hi Thom....so, what would u do with ur extra 100Gb free memory?
just put it to idle waste? :rolleyes:
10-11-2006, 05:59 AM
pando

Quote:

Originally Posted by yxez

Hi Thom....so, what would u do with ur extra 100Gb free memory?
just put it to idle waste? :rolleyes:

hah you didnt get the point huh
10-11-2006, 06:11 AM
yxez

hi pando....thom's point is, he doesn't agree with me that I should load
all my tables in the memory just to make use of it being idle. assuming that all my tables fit the 100GB. all my queries are done
in the memory...no more disk i/o :D
10-11-2006, 07:03 AM
davey23uk

remind me never to let you anywhere near any database of any importance
10-11-2006, 09:27 PM
yxez

ok fine :rolleyes: ....so why don't u tell me davey, what to do with
an idle big memory?
10-12-2006, 05:37 PM
tomcat

Quote:

no more disk i/o

Do you believe that's the only thing to tuning a database?

Quote:

what to do with an idle big memory?

Nothing - until you need it and know what effect using it will have...
10-12-2006, 08:19 PM
yxez

ah ok...tomcat, u mean additional memory is useless in performance tuning if ur apps is running fine? well noted :)

hmmmnn...let me think of some scenario

im thinking in a proactive manner....say if i have free 100gb available now...
i want my db server configured so that if my system will be ready to
accept more loads in the near future without touching it...like u leave it alone.

well i guess....theres no such thing as that...

davey...dont be mad ok? thinking wild ideas....but u see
that was really what the post owner is trying to do...so
u can understand that most of us here think alike :D
I thot u said i am unique here :p