I want to maximize the use of my box specially the memory utilization. When I first adjust the shared pool in one of my production server(original 200mb-) to 600mb, it didn't allow me, and when I even re-adjust to only about 300mb still it didn't allow me. I'm sure it's the kernel settings. But my worries is that I don't know what is the maximum limits for each setting, I could set values that will hit over board and will not allow my O.S. to boot normally. I coordinate this to SUN's(vendor) I.T. and I'm still waiting for the response, they want me to consult to Oracle and yet I can't find any reference regarding the guidelines on how to set properly the Kernel settings. My machine specs is SFV880, 4GB mem
Can you guys have the idea of the maximum settings or the proper guidelines on how to setup properly the kernel?
Here is the settings currently I made:
one thing: SEMMNS=630 right now, generate error, I'm not sure if this is the proper syntax. With AIX 4.3.3 I setup before it works.
I will really appreciate if at least you guys can lead me to info/sites that will clear this issues.
I would go to http://otn.oracle.com/documentation and look up the installation guide of Solaris for the version of Oracle you are running. This will go over all of the appropriate kernel settings.
You are probably running into an issue trying to allocate more SGA space than Oracle is currently configured to allow. There is a metalink (http://metalink.oracle.com) article which discusses how to relocate the SGA, changing the attach address to allow a larger SGA size on Sun for Versions 7-9 - Reference Note:1028623.6
A word of advice before doing all of this - make sure that you are tuning these parameters for your instance and not just maximizing the amount of memory usage. Having too much memory used, filled improperly, can hurt performance instead of help it. In a larger SGA you have more buffer chains to search through and you can get latch contention, etc... There are a lot of things that go into properly tuning your instance and there are a number of resources, including the Oracle provided database administration and concept guides as well as some great books by people like Cary Milsap, Johnathan Lewis, etc.. that will give you a better idea on how to go about tuning the
performance for your instance.
That being said, here is some information that may help you.
Here is an excerpt with a map to give you the appropriate attach address for the size of SGA you are wanting and how to reattach for Oracle 9i
You need to configure the SGA to a value greater than 256 Mbytes on your Sun system.
You are referring to the following sgabeg parameter chart:
sgabeg = 0xe0000000 | 256Mb SGA
sgabeg = 0xd0000000 | 512Mb SGA
sgabeg = 0xc0000000 | 768Mb SGA
sgabeg = 0xb0000000 | 1Gb SGA
sgabeg = 0xa0000000 | 1.25Gb SGA
sgabeg = 0x90000000 | 1.5Gb SGA
sgabeg = 0x80000000 | 1.75Gb SGA
sgabeg = 0x77000000 | just less than 2Gb SGA
sgabeg = 0x70000000 | 2Gb SGA
sgabeg = 0x60000000 | 2.25Gb SGA
sgabeg = 0x50000000 | 2.5Gb SGA
sgabeg = 0x40000000 | 2.75Gb SGA
sgabeg = 0x30000000 | 3Gb SGA
sgabeg = 0x20000000 | 3.25Gb SGA
sgabeg = 0x10000000 | 3.5Gb SGA
sgabeg = 0x01000000 | 16Mb less than 3.75Gb SGA
A. For RDBMS version 9.x - How to Change your Attach Address: ===============================================================
For RDBMS version 9.x, change your attach address by doing the following steps to relocate the SGA:
1. Shutdown any databases using the current "ORACLE_HOME".
2. Change your location to the "/lib" directory
% cd $ORACLE_HOME/lib
3. Make a backup copy of 'libserver9.a'.
% cp libserver9.a libserver9.a.orig
4. Change your location to the "rdbms/lib" directory
% cd $ORACLE_HOME/rdbms/lib
5. Generate the "ksms.s" file
% $ORACLE_HOME/bin/genksms -b > ksms.s
For example, changing the attached address starting at 0x80000000 to 0x60000000 would be:
% $ORACLE_HOME/bin/genksms -b 0x60000000 >ksms.s
6. Regenerate the 'ksms.o' object:
% make -f ins_rdbms.mk ksms.o
7. Archive 'ksms.o' into 'libserver9.a'
%ar r $ORACLE_HOME/lib/libserver9.a ksms.o
% make -f ins_rdbms.mk ioracle
Here are the recommended minimum kernel settings for a single instance running on Sun
The install guide for Oracle 9i on Sun Solaris recommends the following MINIMUM settings.
Kernel Parameter Setting Purpose
Defines the maximum number of semaphore sets in the
Defines the maximum semaphores on the system. This
setting is a minimum recommended value, for initial
installation only. The SEMMNS parameter should be set to the sum of the PROCESSES parameter for each Oracle database, adding
the largest one twice, and then adding an additional 10 for
SEMMSL 256 (You have at 200 which is below minimum recommendation)
Defines the minimum recommended value, for initial
Defines the maximum allowable size of one shared
memory segment. 4 GB = 4294967295
Defines the minimum allowable size of a single shared
Defines the maximum number of shared memory segments
in the entire system.
Defines the maximum number of shared memory one process can attach.
Senior Database Administrator
Good stuff Ramsey, One thing I'd like to add. I've found from previous experience that once a semaphore is used it won't relase it until a bounce. So if you run into that error (I can't remember the exact syntax) where you try to increase your kernal prams that's what you gotta do (at least for Solaris).
Oracle it's not just a database it's a lifestyle!
-------------- BTW....You need to get a girlfriend who's last name isn't .jpg
I am still in the process of learning and will experiment soon in relation to your input guys. I am yet to gather more inputs, like the metalink article mention by wjramsey.
Will it be applicable also to 8.1.7 with that item you specify regarding that mapped for appropriate attach address? Or it is more on relation to O.S., just like TAMIL mentioned for 64-bit O.S.
With this DB, my average concurrent users is more than 350+, my sort_area_size is 2m, and that is why I am also concern with my PGA, which could actually grow in a fast phase, and will use swapfile instead of memory.
That is why I really need to know the proper utilization of setting up the semaphores without hearting the performance.
again many thanks. If there is still more input from you guys, then it will be very much appreciated.
Originally posted by tamilselvan I think 4 GB RAM may not be sufficient for 350 concurrent users. You need to buy more memory.
yes its true, especially when an originally OLTP designed system, includes a semi-DW system as it evolves. But I have to make sure that all options will be considered first, before I can even make a proposal to the management, that indeed it is about time to upgrade the box(maybe # of CPUs) or for at least with its memory.