Oracle session and connection optimization, oracle session

Oracle session and connection optimization, oracle session

1. Modify the Oracle session and maximum number of connections

1. view the maximum number of connections

SQL> show parameter processes;

NAME TYPE VALUE
-----------------------------------------------------------------------------
Aq_tm_processes integer 0
Db_writer_processes integer 3
Gcs_server_processes integer 0
Global_txn_processes integer 1
Job_queue_processes integer 1000
Log_archive_max_processes integer 4
Processes integer 150

2. Modify the maximum number of connections

SQL> alter system set processes = 500 scope = spfile;

The system has been changed.

3. view the maximum number of sessions

SQL> show parameters sessions;

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
Sessions integer 264
Shared_server_sessions integer

4. Modify the maximum number of sessions

SQL> alter system set sessions = 555 scope = spfile;

The system has been changed.
5. Relationship between the number of connections and the number of sessions

Sessions = 1.1 * process + 5

Description: The number of connections and the number of sessions are modified. Oracle must be restarted.

Ii. Common session and connection count query methods

1. query the maximum number of database connections

SQL> show parameter processes;

Or

SQL> select value from v $ parameter where name = 'processs ';

2. Modify the maximum number of connections to the database (150 by default)

SQL> alter system set processes = 500 scope = spfile;

3. query the current number of database connections

SQL> select count (*) from v $ process;
4. view the maximum number of database sessions

SQL> show parameters sessions;

Or

SQL> select value from v $ parameter where name = 'session ';

5. Modify the maximum number of database sessions (248 by default)

SQL> alter system set sessions = 555 scope = spfile;

6. view the number of current sessions

SQL> select count (*) from v $ session;

7. view the number of concurrent (active) sessions

SQL> select count (*) from v $ session where status = 'active ';

8. view the details of the current session

SQL> select sid, serial #, username, program, machine, status from v $ session;

9. View users in use

Select osuser,
T1.username as username,
Cpu_time/executions/1000000 |'s as runtimes,
SQL _fulltext,
Machine
From v $ session t1, v $ sqlarea t2
Where t1. SQL _ address = t2.address
Order by runtimes desc;

Iii. Differences in OS Parameters

1. for Linux and Windows systems, the processing of sessions is different.

In Linux, 1 session corresponds to 1 process.

In Windows, 1 session corresponds to 1 thread.

Oracle always prompts to limit the number of connections. How can I limit the number of connections in oracle?

It can only be set to a large value, not infinite
Current number of connections
Select count (*) from v $ process;
Set the maximum number of connections (default value: 150)
Select value from v $ parameter where name = 'processs ';
Modify the maximum number of connections
Alter system set processses = 300 scope = spfile;
When the maximum number of connections to the database is not enough, the client connection will fail intermittently, And the ORA-12519 is reported.

How to modify the maximum number of connections in Oracle
1. Go to "SQL * Plus Worksheet", as shown in.
2. Click "Change Database Connection ..." As shown in.
3. Enter the "database connection information" interface, enter the user name, password, and name of the database service for which you want to modify the maximum number of connections. Select "SYSDBA" as the connection identity and click "OK", as shown in.
4. Enter the command "show parameter processes;" and run the command to view the current maximum number of connections, as shown in.
5. Run the command "alter system set processes = 600 scope = spfile;" and change the maximum number of connections to 600, as shown in.
6. Enter the command "create pfile from spfile;" and run the command to create a Profile, as shown in.
7. Disable the "Enterprise Manager Console", restart the Oracle service, or restart the Oracle Database Server.
8. Repeat steps 1, 2, 3, and 4 to confirm that the maximum number of connections is successfully modified.

How can I check the relationship between the number of connections, number of processes, and number of users in ORACLE?

SQL> select count (*) from v $ session # Number of connections
SQL> Select count (*) from v $ session where status = 'active' # number of concurrent connections
SQL> show parameter processes # maximum connection

Process: this parameter limits the number of operating system processes (or the number of threads in Windows) that can be connected to SGA. The total number must be large enough, this can be applied to background processes and all dedicated server processes. In addition, the number of Shared Server Processes and scheduling processes is also included. therefore, in a dedicated server environment, this is a method to limit the number of concurrent connections.

Sessions: applies to the oracle hierarchy rather than the operating system hierarchy. This parameter limits the number of concurrent logins to a specified instance without considering logon through a dedicated server or shared server.