Why should we configure limits.conf for
Oracle database?
Hey guys, today i have read lot of good articles and feel like sharing
with you for knowledge sake.
So this is one of the interesting articles I have read that earlier
I never knew that this
file carries this much importance and its really worth reading..!
So, these configurations will have direct impact on Oracle Database Performance,
understanding these configurations will give you a clear picture to address
any “Performance Tuning” issues going forward.
limits.conf:
I have taken the below example from the installation documentation of Oracle 12cr1.
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
There are 4 columns in this syntax, let us understand these columns wise first
and then read it as a line.
Column 1: It specifies to which user on OS, the limits are applicable.
As Oracle guides us to create user “oracle” on UNIX, it guides to
use “oracle” in the first column. If you are using any different
user to install Oracle software, then please do include that username.
For example, include user “grid” for Oracle Grid installation.
Column 2: It accepts two options “soft” and “hard”. “soft” will be
the maximum number that a user can set. “hard” tells the maximum
number that a user(oracle) can re-configure.
Confusing? We will pick an example later to understand the same.
Column 3: On what resource the limits are applied. In the configuration
“nofile” specifies maximum number of file handlers that oracle
user can have at any given point in time. “nproc” specifies maximum
number of processes that oracle user can run on the
server. “stack” specifies maximum stack size in KB for
each thread that oracle user
process creates on the server.
Column 4: Specifies the maximum number associated to its resource parameters explained
in column 3 above.
Let us read the lines now.
Line 1:
oracle soft nofile 1024
Line 2:
oracle hard nofile 65536
extend his limit, he can resize it to maximum of 65536. Beyond which he will need root
Line 3:
oracle soft nproc 16384
Specifies the maximum number of 16384 processes are allowed to run by the user
oracle on the server.
Line 4:
oracle hard nproc 16384
extend this value beyond 16384 through his login. Resizing this will need root login.
Line 5:
oracle soft stack 10240
Line 6:
oracle hard stack 32768
Specifies that if user oracle wants to extend the size of this resource, he can resize it to
maximum of 32 MB.
Well, hope it is fine so far in understanding what these configurations mean to Oracle
Database software installation.
Case study:
Let us test the same by changing one of the resources “nproc” on Oracle 12c Database
running on Oracle Enterprise Linux.
1. Connected to the server as oracle and checked my resource limits.
cat /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768
2. Started oracle database to check how many maximum processes it is creating to bring
up the instance.
. oraenv
[oracle@ORASRV1 ~]$ . oraenv
ORACLE_SID = [oracle] ? ORADB1
The Oracle base has been set to /oracle/app/oracle
[oracle@ORASRV1 ~]$ sqlplus / as sysdba
SQL> startup;
ORACLE instance started.
Total System Global Area 834666496 bytes
Fixed Size 2929888 bytes
Variable Size 599788320 bytes
Database Buffers 226492416 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> exit
3. The number of process in my case found to be 61(It includes database software processes
and shell processes as well).
[oracle@ORASRV1 ~]$ ps -ef | grep oracle | wc -l
61
4. Brought down Oracle database instance now.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>exit
vi /etc/security/limits.conf
oracle soft nproc 30
…
6. Restarted server – Not mandatory, we can even reconnect in another putty session to
apply changes.
7. Now connected as oracle user and started the database.
[oracle@ORASRV1 ~]$ sqlplus / as sysdba
SQL> startup;
ORACLE instance started.
Total System Global Area 834666496 bytes
Fixed Size 2929888 bytes
Variable Size 599788320 bytes
Database Buffers 226492416 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> exit
8. Do you think everything is working fine? NO, check that your shell is now broken.
You will not be able to run anything in your terminal.
[oracle@ORASRV1 ~]$ ps -ef | grep oracle | wc -l
-bash: fork: retry: No child processes
-bash: fork: retry: No child processes
-bash: fork: retry: No child processes
-bash: fork: retry: No child processes
-bash: fork: Resource temporarily unavailable
9. Re-check the same by connecting a new putty session as different user.
In my case I logged in as “root” user.
[root@ORASRV1 ~]# ps -ef | grep oracle | wc -l
29
Impact on performance :
One of the examples that i could think about at the moment related to this blog is “stack”
where the max size per thread is 10MB, if a server process tries to access block size of more
than 10MB size, then you would see performance issue in the database. You can relate the
same to the block size in the database.
If we do not configure any resource limits, then no user on the server have any limitations.
You will be free to use any amount of resources.
If you have a confusion on file handler :
When a process starts running on the server, it might try accessing files on the disk.
Can be once or many times based on the program that process runs.
The resource limit says the maximum number of file handles that ORACLE user on
the OS can have at any given point of time. Irrespective of no of processes
Hey guys, today i have read lot of good articles and feel like sharing
with you for knowledge sake.
So this is one of the interesting articles I have read that earlier
I never knew that this
I never knew that this
file carries this much importance and its really worth reading..!
So, these configurations will have direct impact on Oracle Database Performance,
understanding these configurations will give you a clear picture to address
any “Performance Tuning” issues going forward.
limits.conf:
I have taken the below example from the installation documentation of Oracle 12cr1.
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
There are 4 columns in this syntax, let us understand these columns wise first
and then read it as a line.
Column 1: It specifies to which user on OS, the limits are applicable.
As Oracle guides us to create user “oracle” on UNIX, it guides to
use “oracle” in the first column. If you are using any different
user to install Oracle software, then please do include that username.
As Oracle guides us to create user “oracle” on UNIX, it guides to
use “oracle” in the first column. If you are using any different
user to install Oracle software, then please do include that username.
For example, include user “grid” for Oracle Grid installation.
Column 2: It accepts two options “soft” and “hard”. “soft” will be
the maximum number that a user can set. “hard” tells the maximum
number that a user(oracle) can re-configure.
the maximum number that a user can set. “hard” tells the maximum
number that a user(oracle) can re-configure.
Confusing? We will pick an example later to understand the same.
Column 3: On what resource the limits are applied. In the configuration
“nofile” specifies maximum number of file handlers that oracle
user can have at any given point in time. “nproc” specifies maximum
number of processes that oracle user can run on the
server. “stack” specifies maximum stack size in KB for
each thread that oracle user
process creates on the server.
Column 4: Specifies the maximum number associated to its resource parameters explained
in column 3 above.
in column 3 above.
Let us read the lines now.
Line 1:
oracle soft nofile 1024
Line 2:
oracle hard nofile 65536
extend his limit, he can resize it to maximum of 65536. Beyond which he will need root
Line 3:
oracle soft nproc 16384
Specifies the maximum number of 16384 processes are allowed to run by the user
oracle on the server.
Line 4:
oracle hard nproc 16384
extend this value beyond 16384 through his login. Resizing this will need root login.
Line 5:
oracle soft stack 10240
Line 6:
oracle hard stack 32768
Specifies that if user oracle wants to extend the size of this resource, he can resize it to
maximum of 32 MB.
Well, hope it is fine so far in understanding what these configurations mean to Oracle
Database software installation.
Case study:
Let us test the same by changing one of the resources “nproc” on Oracle 12c Database
running on Oracle Enterprise Linux.
1. Connected to the server as oracle and checked my resource limits.
cat /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768
2. Started oracle database to check how many maximum processes it is creating to bring
up the instance.
. oraenv
[oracle@ORASRV1 ~]$ . oraenv
ORACLE_SID = [oracle] ? ORADB1
The Oracle base has been set to /oracle/app/oracle
[oracle@ORASRV1 ~]$ sqlplus / as sysdba
SQL> startup;
ORACLE instance started.
Total System Global Area 834666496 bytes
Fixed Size 2929888 bytes
Variable Size 599788320 bytes
Database Buffers 226492416 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> exit
3. The number of process in my case found to be 61(It includes database software processes
and shell processes as well).
[oracle@ORASRV1 ~]$ ps -ef | grep oracle | wc -l
61
4. Brought down Oracle database instance now.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>exit
vi /etc/security/limits.conf
oracle soft nproc 30
…
6. Restarted server – Not mandatory, we can even reconnect in another putty session to
apply changes.
7. Now connected as oracle user and started the database.
[oracle@ORASRV1 ~]$ sqlplus / as sysdba
SQL> startup;
ORACLE instance started.
Total System Global Area 834666496 bytes
Fixed Size 2929888 bytes
Variable Size 599788320 bytes
Database Buffers 226492416 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> exit
8. Do you think everything is working fine? NO, check that your shell is now broken.
You will not be able to run anything in your terminal.
[oracle@ORASRV1 ~]$ ps -ef | grep oracle | wc -l
-bash: fork: retry: No child processes
-bash: fork: retry: No child processes
-bash: fork: retry: No child processes
-bash: fork: retry: No child processes
-bash: fork: Resource temporarily unavailable
9. Re-check the same by connecting a new putty session as different user.
In my case I logged in as “root” user.
[root@ORASRV1 ~]# ps -ef | grep oracle | wc -l
29
Impact on performance :
One of the examples that i could think about at the moment related to this blog is “stack”
where the max size per thread is 10MB, if a server process tries to access block size of more
than 10MB size, then you would see performance issue in the database. You can relate the
same to the block size in the database.
If we do not configure any resource limits, then no user on the server have any limitations.
You will be free to use any amount of resources.
If you have a confusion on file handler :
When a process starts running on the server, it might try accessing files on the disk.
Can be once or many times based on the program that process runs.
The resource limit says the maximum number of file handles that ORACLE user on
the OS can have at any given point of time. Irrespective of no of processes
No comments:
Post a Comment