Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, sing.

Asked: July 19, 2000 - 6:03 pm UTC

Last updated: July 30, 2012 - 11:46 am UTC

Version: 8.0.6

Viewed 10K+ times! This question is

You Asked

Tom,

a. db1 – concurrent users + processes = 410
b. db2 – concurrents users + processes = 50
c. db3 – concurrents users + processes = 50

The above three databases are going to be housed on one server.

What is the best way to calculate the kernel parameters based on the above information ? Is there a formula for each of the kernel parameters? I see the below values in the preinstallation documentation but they do not specify the formula? Can you please help me with the formula?

SHMMAX 4294967295
SHMMIN 1
SHMMNI 100
SHMSEG 10
SEMMNS 200
SEMMNI 70
SEMMSL Equal to the value of the PROCESSESinitialization parameter.

Thanks,
Sing

and Tom said...


SHMMAX = The maximum size(in bytes) of a single shared memory
segment.
SHMMIN = The minimum size(in bytes) of a single shared memory
segment.

The above settings let the max be bigger then we wil ever use and the min much smaller. They are sufficient for pretty much all systems.

SHMMNI = The number of shared memory identifiers.
SHMSEG = The maximum number of shared memory segments that can
be attached by a process.

The above settings should also be sufficient.

SEMMNS = The number of semaphores in the system.
SEMMNI = The number of semaphore set identifiers in the system;
determines the number of semaphore sets that can be
created at any one time.
SEMMSL = The maximum number of sempahores that can be in one
semaphore set. It should be same size as maximum number
of Oracle processes
(The PROCESSES parameter in the init.ora file).

SEMMSL
Set to 10 plus the largest initsid.ora PROCESSES parameter of an Oracle database on the system. The PROCESSES parameter can be found in each initsid.ora file, loc ORACLE_HOME/dbs directory. The default value of PROCESSES for the 8.1.5 prebuilt database is 50.

SEMMNS
Set to the sum of the PROCESSES parameter for each Oracle database the largest one, plus 2 times the largest PROCESSES value, plus 10 number of Oracle databases. For example, consider a system that has three Oracle instances with the PROCESSES parameter in their initsid.ora files set to the following values:

ORACLE_SID=A, PROCESSES=100
ORACLE_SID=B, PROCESSES=100
ORACLE_SID=C, PROCESSES=200


The value of SEMMNS is calculated as follows:

SEMMNS = ((A=100) + (B=100)) + ((C=200) * 2) +
((# of instances=3) * 10) = 630





Rating

  (45 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Cool Stuff

A reader, June 27, 2001 - 5:15 am UTC


A reader, September 24, 2001 - 8:14 pm UTC


Exactly what I was looking for.

Randal Columb, July 16, 2002 - 2:28 pm UTC

The title of te review says it all. I've looked everywhere for explanations for these settings...

Some details...

Brain.Xu, July 28, 2002 - 11:27 pm UTC

Hi,tom:

You are doing a really good topic for the kernel params around Oracle.But one point i cannot understand.it's this:

What's mean "semaphore" concept? It has any relation between "Shared memory"? It's in ORACLE level or OS level?
Thanks for help.

Tom Kyte
July 29, 2002 - 7:10 am UTC

semaphores are serialization devices (only one person at a time will get a semaphore). In general, they are "heavy weight" (we have light weight latches as well) and implemented by the OS itself. We use them to protect some shared data structures from being updated by more then one process at a time.

What about SEMMNI

Igor, July 29, 2002 - 12:01 pm UTC


Tom Kyte
July 29, 2002 - 1:55 pm UTC

what about it? do you mean "what is a semaphore set"? if so,

</code> http://www.google.com/search?hl=en&lr=&ie=ISO-8859-1&q=%22what+is+a+semaphore+set%22

(a search for "what is a semaphore set" on google) tells me:

http://users.evitech.fi/~tk/rtp/multi-process.html#semaphores_whatis <code>

A semaphore set is a structure that stores a group of semaphores together, and possibly allows the process to commit a transaction on part or all of the semaphores in the set together. In here, a transaction means that we are guaranteed that either all operations are done successfully, or none is done at all. Note that a semaphore set is not a general parallel programming concept, it's just an extra mechanism supplied by SysV IPC.

Igor, July 30, 2002 - 3:49 am UTC

I'm sorry I was not clear and made you nervous.
I just wanted to know your opinion about Mr Sing setting
and your way to calculate it.
I appreciate your job.


SEMMSL parameter

Anthony Reddy, August 05, 2003 - 11:26 pm UTC

Mr. Tom,
we are using hp-ux 11,
i can't see the SEMMSL parameter, when i type the following
command as root.

root:/>kmtune | grep sem

it shows

sema
semaem
semmap
semmni
semmns
semmnu
semume
semvmx

what's wrong here.
what could be the equivalent parameter for SEMMSL.

please help.
thanks in advance.




Tom Kyte
August 06, 2003 - 7:40 am UTC

hmm, guess it is time to break out that HP/UX Oracle Admin Guide and see what it says (i pointed that out on the link that sent you here...)?

Me, being someone who is not an SA, does not not admin HP/UX, does not even really use it -- doesn't know (but I'll betcha the docs do!)

</code> http://docs.oracle.com/docs/html/A96167_01/pre.htm#CHDCJDHI <code>

Anthony Reddy, August 07, 2003 - 4:25 am UTC

thanks for the help.

Is SHMMAX setting per SGA or for all

Arun Gupta, May 23, 2004 - 10:55 am UTC

Tom,
I am not clear on one thing after reading all the documentation.
If I am running multiple Oracle databases on the same server, then does SHMMAX has to be greater than the sum of all SGAs or do I have to ensure that each SGA is smaller than SHMMAX.

Thanks

Tom Kyte
May 23, 2004 - 11:11 am UTC

it is an OS setting, OS's don't care who is using the shared memory....

needs to be set for the total needs demanded of the OS -- shmmax needs to be set for ALL SGAs, cumulative.

kernal parameter

Sean, July 15, 2004 - 10:47 pm UTC

Hi Tom,

I have a few questions to ask. (902 on Solaris 8).

(1) You mentioned that these two parameters' values should be good enough. Is it still valid for 902?
shmmni=100
shmseg=10

(2) You didn't mention that how to set SEMMNI, so we still use SEMMNI=100 no matter how many instances and how many processes of each instance we use?

(3) What is the right syntax to set these parameters? I have the sample here, but I am not sure it is right or not. I know it is hard for you to check the syntax. Would you please just give us part of /etc/system setting from your system!

------------------------------------------
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=256
set semsys:seminfo_semmns=1024
------------------------------------------

Thanks so much for your help.


Tom Kyte
July 16, 2004 - 10:49 am UTC

1) sure

2) should be -- up to a resonable limit (if you are running more than you can count on a single hand -- you've far exceeded reasonable to me. I use a single finger to count the optimal number of instances on a server)

3)

set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmni=500
set semsys:seminfo_semmsl=500
set semsys:seminfo_semmns=500
set semsys:seminfo_semopm=500
set semsys:seminfo_semvmx=32767
set semsys:seminfo_semmap=250
set semsys:seminfo_semmnu=600
set semsys:seminfo_semume=100


this is on a test box, I have no production solaris boxes left that I have access to.

Very Good stuff

Orlando Reyes, July 29, 2004 - 6:11 pm UTC

Hi Tom,

For these settings
SHMMAX 4294967295
SHMMIN 1

You said “They are sufficient for pretty much all systems.”

Then you said “shmmax needs to be set for ALL SGAs, cumulative.”

So, my question is, if my server has 8 GB of RAM and I am running 3 DB’s with a total of 7GB for all SGAs, do I need to set SHMMAX to 7516192768?

Also, what would 'a single shared memory segment' be for an Oracle server? Would each SGA be the answer?

Please let me know your comments.
Thanks,

Orlando


Tom Kyte
July 30, 2004 - 7:09 am UTC

set it to your needs. in 2000, it was pretty much sufficient for most all systems. In 2004, I'd say it still is. In 2008 -- who knows

(i would hope you wouldn't run 3 instances on a single server, a single server should run 1)

Always Useful

Orlando Reyes, July 30, 2004 - 2:47 pm UTC

Tom,

One more question if you don’t mind, I’ve seen you always state that one server should run only one DB, I don’t really disagree with that statement, but if we have a lot of power/resources on the server what are some of the major reasons against having multiple production databases running on the same server, besides the business risks/polices.

On the up side, we see big savings on Oracle licenses :-), even though we buy more expensive servers.

Thanks and have a great day.

Orlando Reyes



Tom Kyte
July 30, 2004 - 6:06 pm UTC

because on that single "big" server you could run more applications in a single instance then you could in an instance per application -- therefore saving you even more money.

if your server can support N databases each running "an application", then it could support N+M applications in a single instance on that same server -- M is a number greater than zero.

SHMMAX VALUE

Sagar, April 28, 2005 - 5:07 am UTC

KERNEL parameters have been set using
set shmsys:shminfo_shmmax=4294967295 on the unix box [sco openserver 5.0.5], and restarted the server, still the parameters remain the same.
Any valid reason for this, and how to set these values ?

Tom Kyte
April 28, 2005 - 8:00 am UTC

consult your SCO documentation, I've never administered a sco box and don't know how to change the system parameters. on solaris, I remember having to reboot with "-r" or touch a reconfig file for example -- might be something similar.

My doubt regarding SHMMAX

krishna, May 10, 2005 - 2:39 am UTC

Hi Tom,

i have one doubt, why this Oracle architecture is adjusting this kernel parameters, whats teh reason behind this.

Tom Kyte
May 10, 2005 - 8:15 am UTC

we are not adjusting anything, you are merely setting up the operating system to be capable of supporting shared memory so we can use it.

Setting SHMMAX on solaris 10 for Oracle 10g

George, June 14, 2005 - 12:36 am UTC

Hi Tom,

SHMMAX parameter is obsolete in solaris 10.
We would like to use more than 1.75 GB of sga. We followed metalink note and relocated sgabeg. Now when we try to start with 3GB of SGA it is failing. A TRUSS output of session shows it fails on "shmget()" call.
Since the SHMMAX is depreciated we could not set it.
ALso we are not able to find a way to current value that is used by system for SHMMAX(sysdef is not giving any value).

I have found the same question posted in may SUN forum and Oracle forums , but no where a correct answer is given.

Your help is highly appreciated

Tom Kyte
June 14, 2005 - 9:47 am UTC

You'll have to work with support - I don't have any solaris boxes sitting around anymore. All linux today.

Checking security kernel parameters

Dawar Naqvi, September 09, 2005 - 1:55 pm UTC

Hello Tom,

OS: SLES 9

I am installing Oracle AS Infrastructure 10.1.2 (Rel 2) x86 on SuSE Linux Enetrprise server 9.

I have installed SLES 9 from the scratch and fulfil all requirements include in Oracle® Application Server Installation Guide.10g Release 2 (10.1.2) for Linux x86 B19310-01.

when I starts install Oracle AS 10.1.2 Rel 2.


I got error in Checking security kernel parameters as below:

checking for hardnofiles = 65536 found hardnofiles = 1024 Failed
checking for softnofiles = 4096; softnofiles = 1024

Problem:

The security parameters in the kernel do not meet the minimum requirements.

Recommendation: Perform OS System specific instruction to update kernel parameter, namely BIT_SIZE and NON_EXEC_USER_STACK.


Regards,
Dawar


Tom Kyte
September 09, 2005 - 2:14 pm UTC

ok? have you followed the install/user guide to setup your kernel?

Checking security kernel parameters

Dawar Naqvi, September 09, 2005 - 2:54 pm UTC

Yes, I used Oracle® Application Server Installation Guide
10g Release 2 (10.1.2) for Linux x86 B19310-01 guide.

Regards,
Dawar

Tom Kyte
September 09, 2005 - 3:10 pm UTC

and did you set these values? I mean, if you check them now - are they set right?

Checking security kernel parameters

Dawar Naqvi, September 09, 2005 - 3:19 pm UTC

Yes, they are correct.


Please note: Here is the last part of the error message.

But installer guide or any related documents has no detail about these parameters. " BIT_SIZE and noexec_user_stack"

Recommendation: Perform operating system specific instructions to update the kernel parameters, namely BIT_SIZE and noexec_user_stack.

Regards,
Dawar

Tom Kyte
September 09, 2005 - 3:36 pm UTC

checking for hardnofiles = 65536 found hardnofiles = 1024 Failed
checking for softnofiles = 4096; softnofiles = 1024


those look like the culprits though don't they. are they set properly.

Dawar Naqvi, September 09, 2005 - 3:49 pm UTC

Tom,

oracle@abc:/etc> ulimit -Hn
65536

oracle@abc:/etc>ulimit -Sn
65536


I have added hard nofile & soft nofile in the end of /etc/security/limits.conf file per Oracle® Application Server Installation Guide 10g Release 2 (10.1.2) for Linux x86 B19310-01 . Ref: Sections 4.54 & 4.4.1.
Please see below:


oracle@abc:/etc/security> more limits.conf
# /etc/security/limits.conf
#
#Each line describes a limit for a user in the form:
#
#<domain> <type> <item> <value>
#
#Where:
#<domain> can be:
# - an user name
# - a group name, with @group syntax
# - the wildcard *, for default entry
# - the wildcard %, can be also used with %group syntax,
# for maxlogin limit
#
#<type> can have the two values:
# - "soft" for enforcing the soft limits
# - "hard" for enforcing hard limits
#
#<item> can be one of the following:
# - core - limits the core file size (KB)
# - data - max data size (KB)
# - fsize - maximum filesize (KB)
# - memlock - max locked-in-memory address space (KB)
# - nofile - max number of open files
# - rss - max resident set size (KB)
# - stack - max stack size (KB)
# - cpu - max CPU time (MIN)
# - nproc - max number of processes
# - as - address space limit
# - maxlogins - max number of logins for this user
# - priority - the priority to run user process with
# - locks - max number of file locks the user can hold
#
#<domain> <type> <item> <value>
#

#* soft core 0
#* hard rss 10000
#@student hard nproc 20
#@faculty soft nproc 20
#@faculty hard nproc 50
#ftp hard nproc 0
#@student - maxlogins 4
* hard nofile 65536
* soft nproc 2047
* hard nproc 16384
* soft nofile 2048
* hard nofile 65536
# End of file

***********************************************************For your convenince, from the guide see below

4.5 Set Shell Limits for the oracle User
To improve the performance of the software on Linux systems, you must increase the following shell limits for the oracle user, depending on the user's default shell:

Bourne or Bash Shell Limit Korn Shell Limit C or tcsh Shell Limit Hard Limit
nofile nofile descriptors 65536
noproc processes maxproc 16384


To increase the shell limits:

Add the following lines to /etc/security/limits.conf file:

* soft nproc 2047
* hard nproc 16384
* soft nofile 2048
* hard nofile 65536

Add the following line to the /etc/pam.d/login file, if it does not already exist:

session required /lib/security/pam_limits.so

Depending on the oracle user's default shell, make the following changes to the default shell start-up file:

For the Bourne, Bash, or Korn shell, add the following lines to the /etc/profile file:

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

For the C or tcsh shell, add the following lines to the /etc/csh.login file:

if ( $USER == "oracle" ) then
limit maxproc 16384
limit descriptors 65536
endif

Regards,
Dawar

Tom Kyte
September 09, 2005 - 4:13 pm UTC

did you restart? I believe you need to on solaris - at least you used to I think.

Checking security kernel parameters

Dawar Naqvi, September 09, 2005 - 4:21 pm UTC

Yes, I have restrated.



Tom Kyte
September 09, 2005 - 4:38 pm UTC

I am reaching way way back -- do you have to touch /etc/system or something like that to get the rebuild to work?

(can you tell it has been a very long time since I've configured a solaris box myself?)

Checking security kernel parameters

Dawar Naqvi, September 09, 2005 - 6:11 pm UTC

Tom,

OS is Linux (SuSE Linux Enterprise Server 9).
Not a Sun Solaris.

you said:

do you have to touch /etc/system or something like
that to get the rebuild to work?


No

Regards,
Dawar

Tom Kyte
September 09, 2005 - 8:53 pm UTC

sorry, I'll have to refer you to support then - I haven't installed AS on that platform myself.

Checking security kernel parameters

Dawar, September 14, 2005 - 7:32 pm UTC

Tom,

Problem has been solved.


Ref: Article-ID: Note 300819.1 (metalink)

Cause
*****
The mechanism used to set the ulimit depends on PAM limits being used, but the required module(pam_limits.so) is not loaded by default for various applications (ssh, gdm, kde, xdm,su ). The OracleAS 10g installation guide only explains how to configure the "login" mechanism.

Solution
********
In addition to setting the hard and soft values in /etc/security/limits.conf, add the line "session required pam_limits.so" in file used for your session type. Change of the following files may be required

/etc/pam.d/login
/etc/pam.d/su
/etc/pam.d/sshd
/etc/pam.d/xdm
/etc/pam.d/gdm
/etc/pam.d/kde

Some linux installations are using pam system_auth service. Limits for most applications are set changing

/etc/pam.d/system_auth

Regards,
Dawar

Tom Kyte
September 14, 2005 - 8:30 pm UTC

Great, thanks for the followup, appreciate that immensely (for the next person)

Multiple databases in multiple oracle_homes

A reviewer, October 10, 2005 - 12:16 pm UTC

Hi Tom,

What is your opinion about the following architecture:

1 Solaris server serving multiple oracle databases.

Each database will have separate Unix user account, separate mount points (2 points each).

Each databse will have a separate ORACLE_HOME running probably same version of Oracle or may be different patch levels or different versions.

I know your recommendation is to have a dedicated server for each database. But since this is a test server we want to be flexible enough to
- failover each database separatelly
- upgrade each database separately etc.

What are the adverse impacts of having this kind of architecture on server resources availability.

Thanks.

Tom Kyte
October 10, 2005 - 12:51 pm UTC

This is fine for a test machine, in fact it is exactly how mine are setup.

The "adverse" affect? you have finite resources and each database instance requires some of them. ensure you have enough of them to go around.

Multiple Oracle SIDs

A reader, January 24, 2006 - 6:02 am UTC

Hi Tom,
I am kinda new to Oracle on Solaris and Unix.
Just wantes to know how to find out if there are multiple SIDs and Oracle Homes on a server, say Solaris.
As I heard, the .profile can have only one ORACLE_SID set.
Therefore if there are more than one Oracle Homes and Instances on a server, how to identify them.
Please advice.
Thanks as always



Tom Kyte
January 24, 2006 - 8:17 am UTC

you can have as many as you want since a "SID" is just a string - an evnironment variable used to find default files.

To find the instances currently running on a machine, you can typically do something like:

$ ps -aef | grep pmon

that'll show the pmon processes for each instance, and their "sid" will be part of their name

but if you have a bunch of instances "not currently running", you won't see them, you have to "know" of them ($ORACLE_HOME/dbs/*.ora might be helpful, look for init.ora or spfile.ora files)

One DB per application v/s

Naresh, January 25, 2006 - 3:40 am UTC

Tom,

You mentioned in one of your responses above :

***
if your server can support N databases each running "an application", then it
could support N+M applications in a single instance on that same server -- M is
a number greater than zero.
***

Can you please elaborate some possible reasons behind this?
Thanks,
Naresh


Tom Kyte
January 25, 2006 - 1:35 pm UTC

each instance has a PMON.

PMON takes some amount of resources (cpu, memory, stuff)

if you have 10 PMON's - you have 10 times the stuff needed.
Versus
if you have 1 PMON


each instance as a redo log buffer.
each instance has X
each instance has Y
....
and so on


it takes more STUFF to run two instances than a single instance.

How is the SEMMNS formula derived?

Naresh, January 25, 2006 - 3:58 am UTC

Tom,

You mentioned the formula in the first response:

ORACLE_SID=A, PROCESSES=100
ORACLE_SID=B, PROCESSES=100
ORACLE_SID=C, PROCESSES=200


The value of SEMMNS is calculated as follows:

SEMMNS = ((A=100) + (B=100)) + ((C=200) * 2) +
((# of instances=3) * 10) = 630


Is it possible to know why this is calculated thus?

Thanks.
Naresh.


Tom Kyte
January 25, 2006 - 1:36 pm UTC

because the support notes say to set it thusly.

Great explanations

Chris, February 09, 2006 - 10:55 am UTC

Thanks for all the info on the kernel parameters

Do kernel parameters affect database creation

A reader, February 22, 2006 - 1:59 pm UTC

Tom,

Is it possible that 'low' kernel parameter values which exist on the server when a database is created will permanently 'cripple' that database, even after these 'low' values have been adjusted?

In other words do kernel parameters affect the *creation* of a database in any way?

Thanks,

Robert.

Tom Kyte
February 22, 2006 - 2:13 pm UTC

not that I can think of, everything is pretty dynamic. We ask the OS questions when we startup (and from time to time - cpu count can change as cpu's are added/removed).

None that I can think of, no.

CPU count

A reader, February 22, 2006 - 3:37 pm UTC

Hi Tom,
Regarding:-
"and from time to time - cpu count can change as cpu's are
added/removed"
In a dynamic CPU swapping env., how cpu count is known to oracle without bouncing the instance?
Regards

Tom Kyte
February 22, 2006 - 3:39 pm UTC

Conflicting info about SHMMAX from Oracle Support

Brian, March 09, 2006 - 8:34 am UTC

Tom,

You explained earlier in this thread that SHMMAX is a cumulative sum of all SGA's on a particular machine. Oracle support (SR# and portion of text below) is explaining that SHMMAX is related to any particular instance running on a machine (not cumulative). From what I understand of the explanation below, we would now have 12gb shared memory area total, which exceeds the 2gb defined to ths OS via SHMMAX. Could you clear up the discrepancy? We are running 10.1.0.4 on Linux ES/3.

Thanks,
Brian
----------------------
SR Number 5207904.992

Explained that SHMMAX is an upper kernel limit defined for what is the maximum single shared memory segment we can allocate upon instance startup.

E.g.
Lets say we set SHMMAX to 2gb.

Any instance on this machine can have its SGA size up to 2gb in size this is the limit & its per instance.

So if you have 6 instances all of 2gb SGA's then you'll have 6 shared memory segments each of 2gb in size so a total usage of 12gb of Memory this does NOT include your PGA as this is seperate private memory not attached to the shared memory segment.


setting kernel parameter

sachin, March 28, 2006 - 9:11 am UTC

Hi Tom,

If i set the SHMMAX as half of RAM say for example 8GB. Is this 8GB of RAM is used only for Oracle or it can be usable for other application also..

Thanks in advance.
Sachin

Tom Kyte
March 28, 2006 - 3:53 pm UTC

it can be used by anything if we are not already using it.

shmmax

A reader, February 07, 2007 - 1:09 am UTC

Operating system is Solaris 5.9
Oracle is 32-bit and version is 9.2.0.5
sga_max_size is 2.6GB
shmmax is set to 1.7GB
db_cache_size is 1.6GB
Shared pool is 800MB

I am having performance problems in Oracle.

I am thinking of raising shmmax to 4GB so that I can increase db_cache_size to 2.5GB.

[1] Will this increase the performance.
[2] Is increase in shmmax size is directly propotional to db_cache_size in above configuration.
Tom Kyte
February 07, 2007 - 1:14 pm UTC

yes, no, maybe, sort of, never, always.

No one can answer that - think about it. If your performance issues are centered around latch contention due to excessive hard parsing, well, increasing the buffer cache isn't going to do a thing.

if you are cpu bound, it isn't going to do a thing.

if you are totally out of memory, (lots of PGA memory in use), this will kill you.


Balvant Chhasiya, June 06, 2008 - 2:41 pm UTC

Hi Tom,

We are facing Oracle database performance problem on HPUX(64-bit) server. We have very good server configuration with 16 processor 32GB RAM, however it's giving very worst performance while our QA database running with 4 processor server is giving good performance.

I think there is some problem with Kernel parameters setting on the production server. Let me point out my questions:

i.) In every database startup, in alert log file we get following warning.

-------------------------------------------------------------
WARNING:Oracle instance running on a system with low open file descriptor limit. Tune your system to increase this limit to avoid
severe performance degradation.
----------------------------------------------------------------
on our database db_files set to 1024.

and please find below kernel parameter setting on production HPUX box:

* Tunable parameters

STRMSGSZ 65535
dbc_max_pct 5
dnlc_hash_locks 512
enable_idds 1
ksi_alloc_max 80000
max_thread_proc 256
maxdsiz 4096000000
maxdsiz_64bit 4096000000
maxfiles 1024
maxfiles_lim 2048
maxssiz 401604608
maxssiz_64bit 1073741824
maxswapchunks 16384
maxtsiz 1073741824
maxtsiz_64bit 0X40000000
maxuprc 9995
maxusers 1564
maxvgs 30
msgmni 10000
msgseg 32767
msgtql 10000
ncsize 87168
nfile 152048
nflocks 10000
ninode 82048
nproc 10000
npty 120
nstrpty 60
semmap 1332
semmni 1330
semmns 16384
semmnu 9996
semmsl 4096
semvmx 32768
shmmax 0x8000000
shmmni 512
shmseg 320
swchunk 4096
vps_ceiling 64


I do understand, I can solve this problem by reducing db_files to less than 512 as per formula ( open files=2*db_files+82), but I guess that will solve my error, but as per my understanding if OS is not tuned properly, it might give problem in other way, hence would like to solve this by changing kernel parameter. do you know which kernel parameter do i need to change?( I suspect I need to change maxfiles(curently set to 1024, but not sure).


ii) Query-2: On the same HPUX box, SHMMAX is set to 0x8000000, I am not sure how much memory size it represent. my doubt is, it's not properly set as we are having 32GB RAM and SGA size of the database is 5GB. Could you please let me know "0x8000000" represent how much GB and what is the optimal value for SHMMAX should be for above environment?

Your answer will be highly appreciated.

Thanks & Regards.
Balvant Chhasiya.
Tom Kyte
June 09, 2008 - 10:30 am UTC

why don't you use statspack, awr and or tkprof to see what the differences are - what are you waiting on in this "nicely configured" machine that you are not in qa.

What are the DIFFERENCES



Balvant Chhasiya, June 13, 2008 - 1:07 pm UTC

Dear Tom,

Thanks for your reply.

For my previous question, I got the reply from metalink for the SR raised. Please find below their feedback:
================================================
RDA OUTPUT
--------------------
Database parameters
-------------------------------
processes 1000

--> this allows only about 1 file descriptor per Oracle process on average (ignoring any other process on the
box).


RESEARCH
========
WebIV Search

Bug 5372330 - Abstract: NUMBER OF FILE DESCRIPTOR INCREASING IN 5 NODE RAC
Status: 92,Closed, Not a Bug

Development explains :

"Each Oracle process (connection) will use about 12 file descriptors from miscellaneous items (eg: stdin/stdout/reserved descriptors for trace + alert,
net connection to/from the process etc..) and then additional descriptors for a
ny datafiles opened, external tables opened, and things like db links, utl_http
etc.. etc.. The main item is usually for datafiles."


CAUSE DETERMINATION
===================
Low value for nofiles(descriptors) for OS 'oracle' user.

CAUSE JUSTIFICATION
===================
The 'processes' parameter is set to 1000, but the nofiles(descriptors) limit for 'oracle' user is only 1024.

PROPOSED SOLUTION(S)
====================
Increase nofiles(descriptors) limit for 'oracle' user to at least 12288 (the best would be 65536).

PROPOSED SOLUTION JUSTIFICATION(S)
==================================
The 'processes' parameter is set to 1000, but the nofiles(descriptors) limit for 'oracle' user is only 1024.
As explained by Development in Bug 5372330 - Abstract: NUMBER OF FILE DESCRIPTOR INCREASING IN 5
NODE RAC (closed as 'Not a Bug') :

"Each Oracle process (connection) will use about 12 file descriptors from miscellaneous items (eg: stdin/stdout/reserved de
scriptors for trace + alert, net connection to/from the process etc..) and then
additional descriptors for any datafiles opened, external tables opened, and thi
ngs like db links, utl_http etc.. etc.. The main item is usually for datafiles."

=============================================

As I mentioned in my previous question, I would like to discuss with you about the query which is giving vary wiered result on my QA(output in 50min) & Prod(output in 5 hours) environment, I tried to post a query on a "SQL Tuning" thread, but I got the message saying "New query will be deleted", could you please let me know in which thread should I post the query.

Appreciate your feedback.
Tom Kyte
June 13, 2008 - 1:30 pm UTC

that has nothing to do with performance. It is saying "if you don't up this, and you hit 1000 processes, you will start failing"


if you have a five page query with a ten page query plan - I don't really want to see it. I cannot tune every individual query out there. I would suggest using enterprise manager and the tuning pack - it'll be happy to look at a 5 page query with a ten page query plan - and suggest indexes, materialized views, sql profiles, or a rewrite of the query to implement.

Kernel Parameters Settings

Ram, January 31, 2011 - 2:51 am UTC

Tom,

Why do we have to set Kernel Parameters for Solaris, HP, Linux servers but not for Windows ??
Why is it necessary for Unix Servers only?
If we are not going with the necessary settings on Windows Servers, then how is it being able to manage itself ??
Tom Kyte
February 01, 2011 - 4:36 pm UTC

because windows doesn't have any kernel parameters to set?

Let's turn this around:


why do we have to create services and start services for windows but not on solaris, hp, linux services?

why is is necessary for windows servers only?

If we are not going with the necessary settings on Unix, then how is it being able to manage itself?


It is quite simply because windows is not unix, unix is not windows - they are different and are configured differently.

Naresh, May 14, 2011 - 10:56 am UTC

Hi Tom,

First of all, thanks for your answers in understanding different kernel parameters and their significance.

Let us say, I have my SHMMAX parameter set to 4 GB, then will oracle allow me to set my SGA to be at 7 GB?

If yes, will the SGA be allocated in two different chunks of shared memory? Will it have any performance impact on the database.

Please explain other disadvantages, if there any..

Regards
Naresh
Tom Kyte
May 18, 2011 - 3:01 am UTC

it controls the max size of a given "piece" - we can use multiple pieces to get a bigger SGA.

Having multiple chunks is less preferred than having a single one in general - yes.

sreeNIVASa suMAN P, January 08, 2012 - 2:19 pm UTC

Hi Tom,
Appreciate your responses. I do red al above queries & solutions.
But fail to understand keenly on
1.Why we need to set manually on lunux and not in windows? - if we want to use better performance in windows, should we add anywhere
2.Better documentation / links / books on setting kernel parameter for different OS and with diff. RAM sizes and with multiple dbs
3. I am using 4GB RAM. Pl suggest kernel parameters in linux system with good performance with justifications and how it will impact on SGA
4. examples or comparison of kernel parameters..since flow is missing and have confusion

I am new to this link...sending mail is also appreciatable

Thanks in advance
Tom Kyte
January 10, 2012 - 10:17 pm UTC

1) because windows is not unix and unix is not windows? they are different operating systems that are configured entirely differently.

for the same reason you don't set up a paging file on unix.

2) google around, that is OS specific. I don't use every OS and there is no single best setting for every OS.

3) use defaults beyond what the install guide says to set. 4gb is pretty small, nothing special needs to be setup.

4) experience, reading, hands on - that is the only way to get there.


I do not email people.

thanks tom u r the god of oracle

srinivasa j, April 08, 2012 - 7:19 am UTC

Hi

Tom this srinivasa,reading your valuables replays since from many years thanks for that.


thanks & regards

J.srinivasa




Kernel Parameters

Lavanya, April 09, 2012 - 4:55 am UTC

Hi Tom,
Your informations on the kernel parameters are very clear.
But if these kernel para are not set can i know what kind of errors we will get? or
If we miss any 1 of the kernel para settings whats the error do we get?
Tom Kyte
April 09, 2012 - 11:43 am UTC

they are also very old, make sure to refer to your installation guide for your platform.

You'll get ora-xxxxx errors, various ones based on what setting is wrong. Basically, the database won't start.

kernel parameters

avinash saxena, June 20, 2012 - 1:34 pm UTC

Hi ,
Sir i just wanted to ask you is that 10 value fix i didn't get that .........

Thanks
Tom Kyte
June 21, 2012 - 7:56 am UTC

er??? what?

SHMMAX & SGA

Russell Cannon, July 27, 2012 - 2:46 pm UTC

Tom,

I understand that the allocation of shared memory segments goes something like this in order of priority:

1. Allocate a single segment for the entire SGA whenever possible.
2. Allocate contiguous segments when SGA_TARGET > SHMMAX.
3. Allocate non-contiguous segments as a last resort.

Can you tell me whether there is anything internal to Oracle that makes a single segment significantly more desireable than contiguous segments? What is it about contiguous segments that is suboptimal?

Thanks,
Russ
Tom Kyte
July 30, 2012 - 11:46 am UTC

the math involved in resolving an address gets more and more complex as you go from single segment, to multiple contiguous segments to non-contiguous segments.

thiyagusham, November 24, 2012 - 4:40 am UTC

Hai tom;

It was really useful to me. I am using Host/Guest operating System. Allocated ram size was 1GB. Shmmax showing 2gb. sga_target=800m

what's the correlation assigning SHMMAX ?
is it depend sga_target or half physical of RAM ?







thiyagusham, November 24, 2012 - 4:47 am UTC

Hai tom;

It was really useful to me. I am using Host/Guest operating System. Allocated ram size was 1GB. Shmmax showing 2gb. sga_target=800m

what's the correlation assigning SHMMAX ?
is it depend sga_target or half physical of RAM ?







Very useful but there is something else...

Alexander Murdoch, January 13, 2014 - 12:06 pm UTC

Hello.
I've found this article very helpful but I'm still not 100% sure on how many semaphores I'll need to host a database.

Please see this message if you want to know more about my query.
( https://community.oracle.com/message/11325503 )

Thanks,
Alex.

How to set kernel parameter

jai, January 23, 2014 - 9:23 am UTC

if server has 10GB of RAM and i supposed to use 8GB for Oracle db
-How will i set the kernel parameter?
plz help
thanks in advance.