Oracle processes parameter установить
~ Process vs Sessions parameter values in Oracle
~ Setting Sessions and Transaction parameter values in Oracle 11g with respect to process parameter values.
~ Applicable to different oracle version as described.
-- Formula ( upto Oracle version 11.2.0.x.x) - I believe
processes=x
sessions=x*1.1+5
transactions=sessions*1.1
Click Here to view from Oracle document
-- To View all
$sqlplus '/as sysdba'
sql> show parameter sessions;
sql> show parameter processes;
sql> show parameter transactions;
-- To set ( as per as usual process)
alter system set processes=1000 scope=spfile;
alter system set sessions=1105 scope=spfile;
alter system set transactions=1216 scope=spfile;
-- In Oracle 11.2.0.3.x onwards
-- Deviation ( as I observed, Concluded formula) - May be a bug
-- When I set process values and bounce the database automatically values set for sessions and transactions
In 11.2.0.3 formula is :
i.e.,
processes=x
sessions=x*1.5+26
transactions=sessions*1.1
Example:
If process=500, the sessions will be 776, Transactions will be 853.
-- When viewed from one of production database environment:
SQL> show parameter process;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
cell_offload_processing boolean TRUE
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 500
processor_group_name string
SQL> show parameter session;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
session_cached_cursors integer 50
session_max_open_files integer 10
sessions integer 776
shared_server_sessions integer
SQL> show parameter transaction;
* Some sites also explains similar issues / bugs. Please go through the below link. But I have little bit deviations.
Note: When spfile is common location ( in asm) in RAC database, then do in mount stage and bounce the instances.
PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.
The default values of the SESSIONS and TRANSACTIONS parameters are derived from this parameter. Therefore, if you change the value of PROCESSES , you should evaluate whether to adjust the values of those derived parameters.
Demonstration on how to increase processes, sessions, and transactions parameters in Oracle.
Whenever DB processes are utilized 100% then it’s NOT allowed any new sessions/connections to DB’s instances and we get the below error:
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log for
the ORA-20 errors.
Cause: All process state objects are in use.
Action: Increase the value of the PROCESSES initialization parameter.
Before starting on the solution, we understand what is processes, sessions & transactions:
PROCESSES: It specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.
The default values of the SESSIONS and TRANSACTIONS parameters are derived from the PROCESSES parameter. Therefore, if we change the value of PROCESSES, We should evaluate whether to adjust the values of those derived parameters.
SESSIONS: It specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system. We should always set this parameter explicitly to a value equivalent to our estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.
Oracle uses the default value of this parameter as its minimum. Values between 1 and the default do not trigger errors, but Oracle ignores them and uses the default instead.
TRANSACTIONS : It specifies the maximum number of concurrent transactions. Greater values increase the size of the SGA and can increase the number of rollback segments allocated. The default value is greater than SESSIONS (and, in turn, PROCESSES) to allow for recursive transactions.
Let’s move on to the solution and below is the step by step demonstration:
Solution:-
Connect database as sysdba-
sqlplus / as sysdba
Check the current setting of parameters-
sql> show parameter processes
sql> show parameter sessions
sql> show parameter transactions
We can also use below SQL code to find values of the required parameters:
set lines 222
col RESOURCE_NAME for a15
col LIMIT_VALUE for a15
select resource_name,current_utilization, limit_value
from v$resource_limit
where resource_name in ('sessions','processes','transactions') order by resource_name;
OR
set lines 222
col NAME for a20
col VALUE for a20
select name, value FROM v$parameter
where name in (‘sessions’,’processes’,’transactions’) order by name;
- If we are planning to increase “PROCESSES” parameter so, we should also plan to increase the “SESSIONS” and “TRANSACTIONS” parameters. A basic formula for determining these parameter values is as follows:
processes=x
sessions=x*1.1+5
transactions=sessions*1.1
- Before altering parameters we need to take a backup of pfile.
SQL> create pfile ='/u02/DBSGURU/initdbsguru.ora' from spfile;
SQL> alter system set processes=500 scope=spfile;
SQL> alter system set sessions=555 scope=spfile;
SQL> alter system set transactions=610 scope=spfile;
After resetting the parameters we need to bounce the database-
SQL>shutdown immediate;
SQL>startup;
After that will check the settings of parameters-
SQL> show parameter processes;
NAME TYPE VALUE
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 4000
log_archive_max_processes integer 4
processes integer 500
SQL> show parameter session;
NAME TYPE VALUE
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
session_cached_cursors integer 50
session_max_open_files integer 10
sessions integer 784
Also, we can use the below SQL command to validate allocated and utilized value of changed parameters:
NOTE – Here it increased the value of session automatically 784 but we wanted to set it 555 so, whenever we alter the processes it automatically calculates and increases the sessions. So again we need to set the transaction as per the current sessions.
SQL> show parameter transactions;
NAME TYPE VALUE
transactions integer 610
transactions_per_rollback_segment integer 5
We need to follow the below formula and set the transaction value-
transactions=sessions*1.1
SQL> alter system set transactions=862 scope=spfile;
Now again, need to bounce the database –
SQL>shutdown immediate;
SQL>startup;
Finally, we can execute SQL to get values.
This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.
In this post I’m going to show you how to change the processes parameter in an Oracle RAC database.
You can use this same method for any RAC parameter that requires instances to be rebooted for the change to take effect.
In an ideal situation, you would just alter system the parameters and then you would stop and start each instance.
But this did not work as expected because some services were only available in one node and not in the other, so I had to relocate them and try again.
I will show you all the details in this post.
You can check the current Oracle process limits with this query
First of all I connect and check the current values.
Once I have the values, I want to change the processes parameter to 1500.
So now ideally I would have to just stop and restart each of the 2 RAC instances.
So I checked all the services for that database like that.
It was complaining about the service xi002pro_bkup1.
So I tried to relocate all services to node 3.
And then tried again
This time it worked.
Now I need to relocate the services back to node 1.
There is still one service that was on node 3 that I have to move to node 1 to start and stop the instance.
But I faced another problem.
The problem is that this service xi002pro_bkup2 is only preferred on node 3 and not even available in node 1 as you can see with this.
So I added this service as available to node 1 and preferred at node 3 and checked again.
As shown above, the service is now preferred on node 3 and available on node 1.
So now I can relocate the service to node 1.
And then I can stop and start the instance in node 3.
Now I relocate the service back to node 3.
And I check again the services how they are distributed on each node to see if they are back as I had them at the beginning.
Now I validate that the change to the processes parameter has been applied.
So everything seems OK.
Of course, if you do some preparation before doing the stop and start of each instance, you could have avoided all this during the intervention.
Out of the box, Oracle Datatbase 10g Express Edition RDBMS is fast and powerful. The stated limitations of 2GB of maximum RAM usage and 2GB of total datafile management are plentiful for it to easily run as the back-end for a small to medium-sized office application.
However, we soon hit a connection limit as characterised by the following Oracle Errors:
We can get this second message because Oracle creates Operating System processes to handle Connections (or Sessions) - which means Processes, Sessions (and as we’ll soon see..) Transactions are all related.
- Processes = 40
- Sessions = 49
- Transactions = 53
I was able to generate the above error message ( ORA-00020 ) from about ~30 connections on a vanilla Oracle XE installation (on Windows 7).
So, let’s increase these limits to allow more connections to our Oracle Server..
1. Log in as SYSDBA
From the menu ‘Oracle Database 10g Express Edition’, find and select ‘Run SQL Command Line’, then type:
and enter your SYS, or SYSTEM password at the prompt
3. Verify the new parameters
with this simple select statement..
And we’re done - a free, light and powerful Oracle RDBMS that’s able to serve more connections
Related Posts
- Advent of Code 2021 27 February 2022 0 Comments
- Understanding autoload_paths and namespaces in Ruby on Rails 5 July 2019 0 Comments
- Foreign Keys to custom Primary Key caveats in Ruby on Rails 25 August 2018 0 Comments
- About me
- Curriculum Vitae
- Advent of Code 2021
- Understanding autoload_path.
- Foreign Keys to custom Prim.
- StackOverflow
- GitHub
My articles are licensed under a Creative Commons Attribution 4.0 International License.
2022 Andrew Freemantle. All rights reserved.
Proudly generated with Jekyll and hosted on GitHub Pages
Specifying the Maximum Number of Processes
The PROCESSES initialization parameter determines the maximum number of operating system processes that can be connected to Oracle Database concurrently. The value of this parameter must be a minimum of one for each background process plus one for each user process. The number of background processes will vary according the database features that you are using. For example, if you are using Advanced Queuing or the file mapping feature, you will have additional background processes. If you are using Automatic Storage Management, then add three additional processes for the database instance.
If you plan on running 50 user processes, a good estimate would be to set the PROCESSES initialization parameter to 70.
SAP Notes:
SAP Notes for Recommended Oracle Parameters:
Error Captured:
Check Current Value of Processes:
ST04 / DBCOCKPIT Transaction
SPFILE Entry in ST04
Current Used Processes Value
Compare the PIDs on task manager
Calculation of Processes by Hardware of System:
Parallel_max_servers calculation
PARALLEL_MAX_SERVERS depends on the CPU cores in the database server available for database (total CPU cores on database server minus CPU requirements of other applications including SAP central instance)
PARALLEL_MAX_SERVERS = Number of DB machine CPU CORES*10
For example for ABAP Stack:
Number of CPU = 4
Parallel_Max_Servers = 4 * 10 = 40
ABAP Work Processes = 25
Processes = (ABAP Work Processes * 2) + Parallel_Max_Servers + 40 = (25*2) + 40 + 40 = 130
Session = 2 * Processes = 260
For example for JAVA Stack
PROCESSES = J2EE server processes * + PARALLEL_MAX_SERVERS + 40
J2EE Server Processes = 2 (if two server nodes – Server0 and Server1)
Max Connections = Open the Visual Administrator and select Server -> Services -> JDBC Connector = 50
2. ALTER SYSTEM commands
Update: The Oracle XE 10g documentation links below were broken, so they now point to the Oracle 11g R2 Standard Edition documentation instead - note that the default values are greater in 11g R2 Standard than the Express Edition (XE).
The Oracle Documentation states that TRANSACTIONS is derived from SESSIONS, which in turn is derived from PROCESSES, thus:
PROCESSES = 40 to Operating System Dependant
SESSIONS = (1.1 * PROCESSES) + 5
TRANSACTIONS = 1.1 * SESSIONS
So, what value to start with for PROCESSES? Trebling it is as good a start as any, then I’d add a few more for good measure.. Here are the values I recommend:
- PROCESSES = 150
- SESSIONS = 300
- TRANSACTIONS = 330
type the following commands:
then to make the settings take effect, we need to bounce the database..
Читайте также: