Two Connection Methods for Oracle Database Server

Two Connection Methods for Oracle Database Server
Oracle provides two Database Connection Methods: Private connection and shared connection. The difference is that the private connection mode is that a user corresponds to a database server process, while the Shared Server connection mode is that multiple users can use one server process in turn without orientation. Apsara stack connection is recommended for oracle. A session corresponds to a server process, which reduces competition and is useful for long transactions, but consumes PGA resources; the shared connection method is advantageous for systems with short transaction execution time and limited server resources. Consider which connection method you want to use.
You can use dbca to set the database connection mode:

Of course, you can also modify the shared_servers parameter to set the connection mode to the database. If this parameter is set to 0, it indicates a private connection. If it is not set to 0, it indicates a shared connection.

SQL> select * from v$version where rownum=1;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

The following analyzes the private and shared connections in sequence:
Private connection mode:

SQL> show parameter shared_servers;NAME TYPE VALUE------------------------------------ ----------- ------------------------------max_shared_servers integershared_servers integer 0

My database connection method is proprietary.
The pattern diagram of the private connection mode is provided in the official document (11.2:

When we connect to the database locally and configure the service name, we need to specify the method for connecting to the database. The following is the configuration information in tnsnames. ora:

55 =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 178.20.121.96)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = jing)))

Note: When we connect to the database in a shared manner, the SERVICE_NAME in tnsnames. ora must be a dynamically registered listening service.

SQL> select distinct SID from v$mystat;SID----------125SQL> select server from v$session where SID=125;SERVER---------DEDICATED

We use SID to find the operating system process number (spid) for this session service and the process ID (pid) identified in the database ):

SQL> select paddr from v$session where sid=125;PADDR--------393C5774SQL> select pid,spid from v$process where addr='393C5774';PID SPID---------- ------------------------19 4848

,

The 19th process of is the process that serves this session. The VPC process is also called shadow process (shad)
Shared connection mode:
You can set the shared connection mode by modifying the shared_servers parameter. The 11.2 official document also gives the shared connection mode diagram:

SQL> alter system set shared_servers = 5; set the number of database server processes to 5


The system has been changed.
Through the view v $ shared_server, We can intuitively see the server process:

SQL> alter system set shared_server_sessions = 20; set the database session count to 20
The system has been changed.

SQL> show parameter dispatchers; scheduling process parameter NAME TYPE VALUE =------------- ------------------------------ dispatchers string (PROTOCOL = TCP) (SERVICE = orcl3939XDB) max_dispatchers integer

If the dispatches parameter is set to null, the Shared Server cannot be started.
We can set this parameter:
Alter system set dispatchers = '(protocol = TCP) (disp = 8) (serv = xxx )'
The preceding figure shows the Protocol. disp indicates the number of processes of the scheduler (dipatcher). The service specifies the service names to use the Shared Server mode. Use the above mode to specify to start only the sharing mode of a service. If you want to set all services to use the sharing mode, set it:

alter system set dispatchers='(PROTOCOL=TCP)';SQL> alter system set dispatchers='(PROTOCOL=TCP)(dispatchers=2)';

The system has been changed.

SQL> select distinct sid from v$mystat;SID----------9SQL> select paddr from v$session where sid=9;PADDR--------393C8DACSQL> select distinct server from v$session;SERVER---------DEDICATEDSHAREDSQL> select pid,spid from v$process where addr='393C8DAC';PID SPID---------- ------------------------24 9722

To simulate the entire process, the write segment has an endless loop:

SQL> declare 2 a number :=0; beginloop a := a + 3; end loop; end;/

Now let's check that the process serves our session:

S004 is the process currently serving this session.
View the two scheduling processes we have set:




With v $ circuit, you can find the scheduling process serving the current process:

Find the specific scheduling process through the address of the current dispatcher:


If you locally connect to data in shared mode, you need to configure tnsnames. ora:

55 =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 178.20.121.96)(PORT = 1521)))(CONNECT_DATA =(SERVER = SHARED)(SERVICE_NAME = jing)))

The following shows how to disable the shared connection:

SQL> alter system set shared_servers = 0 scope = both;


The system has been changed.

SQL> alter system set max_shared_servers = 0 scope = both;


The system has been changed.

SQL> show parameter shared_serversNAME TYPE VALUE------------------------------------ ----------- ------------------------------max_shared_servers integer 0shared_servers integer 0

When both shared_servers and max_shared_servers are set to 0, the shared connection is terminated. All shared connections are disconnected (connected sessions are also disconnected)


The system has been changed.