Oracle session and connection Optimization

Oracle session and connection Optimization

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.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian