A Preliminary Study of Oracle's shared connections and dedicated connections

In the dedicated connection mode, the server establishes a connection with the client for every client request to connect to the database server. This connection is used to process all requests of the client, until the user actively disconnects or the network is interrupted. When the connection is idle, the background process pmon tests the user's connection status at intervals. If the connection is disconnected, pmon cleans up the site and releases related resources. A dedicated connection is equivalent to a one-to-one connection that can quickly respond to user requests. Of course, when connecting, you must first create a PGA (Program global area). The pga_aggregate_target parameter determines the total amount of memory that can be used by all server processes, the workarea_size_policy parameter determines whether to use manual or automatic management. For example:

SQL> show parameter pga_aggregate_target

Name type value
-----------------------------------------------------------------------------
Pga_aggregate_target big integer 10485760

SQL> show parameter workarea_size_policy

Name type value
-------------------------------------------------------------
Workarea_size_policy string auto

PGA consists of three parts, including sort_area_size that can be configured, session information, and stack space.

Sort_area_size is the memory space used for sorting:

SQL> show parameter sort_area_size

Name type value
-----------------------------------------------------------------
Sort_area_size integer 524288

If the sorting data volume is large and the sorting space is insufficient, Oracle uses a dedicated algorithm to segment the data. The data after segmentation is transferred to the temporary tablespace for sorting in the temporary tablespace, after the process is completed, it is combined and returned to the requesting user. This is why temporary tablespace is used for large sorting.

In a dedicated connection, all resources required for the connection are allocated in PGA. This memory zone is a private connection and cannot be accessed by other processes.

Dedicated connections use one-to-one connection to respond to user requests. However, if there are too many connected users, resources must be allocated to each connection, the number of connections is limited by hardware. To overcome this problem, Oracle proposed a connection method for shared connections, that is, using a server process to respond to multiple user connections. Unlike dedicated connections, the PGA is created only when there is a connection, A shared connection is allocated to a specified number of server processes as soon as the instance is started. The user's connections are queued and allocated to the server process by the distributor. Other processes are waiting in queue. As long as the user's request is completed, the connection will be disconnected immediately, and the Allocator will allocate idle server processes to other excluded processes.

The use of shared connections can effectively improve the utilization of server resources, but only one protocol is supported for one distributor. Each distributor has its own queue. After the request task is completed, the operator returns the operation result to the corresponding user process. However, the establishment of a shared connection requires the Oracle listening process, distributor, and sharing server process to complete the creation of a connection. Therefore, the connection allocation also takes some time and resources.

In the shared connection, sort_area_size is allocated in the large_pool of the SGA instance.

The above is the creation and management methods of the two connections. Ideally, using dedicated connections for long transactions or large transactions can effectively improve the system performance, reduces user waits and transaction queues to improve system utilization. For ultra-short transactions, short transactions, and small transactions, you can use the shared connection method to flatten the resource and efficiency. For example, for the OLTP system, use dedicated connections, and for websites, you can use shared connections.

So can we use shared connections in the OLTP system? If it can be used, can it improve performance?

The OLTP system generally has many long transactions and large transactions. For example, a user must perform several operations as a transaction. In this case, let's analyze and see what will happen:

The premise of analysis is that the number of user requests must be greater than the number of processes on the shared server. Otherwise, the performance of the shared connection is lower than that of the dedicated connection.

If the number of user requests is greater than the number of Shared Server Processes, it must be in the queue. If a Shared Server process is currently executing a long transaction, the request queue will always wait, until the current transaction ends. From the perspective of user requests, it is obvious that the response time is longer. From the server perspective, let's take a look at the instances provided by the netizen westlife_xu:

The shared connection is opposite to the long transaction. The shared connection of the long transaction will cause a serious queuing of the shared process, resulting in a serious reduction in performance.

Let's take a look at an extreme example.

Code:
  
Top
6191 Oracle 16 0 532 M 410 m 408 M r 14.1 40.6 1969: 23 Oracle
12599 Oracle 15 0 533 m 423 M 419 m s 13.2 41.8 4379: 02 Oracle
458 Oracle 16 0 532 M 411 M 409 M r 12.5 40.7 1808: 48 Oracle
12602 Oracle 16 0 532 M 421 M 419 M r 9.7 41.7 4295: 49 Oracle
4007 Oracle 16 0 533 m 410 m 408 M r 9.7 40.6 1527: 16 Oracle
13053 Oracle 16 0 532 M 370 m 368 M r 8.5 36.6 77: 44.69 Oracle
13967 Oracle 16 0 533 m 421 M 419 M r 8.1 41.7 2384: 56 Oracle
6228 Oracle 16 0 532 M 408 m 406 M r 7.8 40.4 773: 24.11 Oracle
30806 Oracle 16 0 533 m 415 M 412 m r 7.5 41.0 1139: 41 Oracle
12595 Oracle 15 0 533 m 97 m 96 m s 4.4 9.7 1355: 51 Oracle
12597 Oracle 15 0 533 m 98 M 96 m s 2.2 9.7 710: 42.61 Oracle
12520 Oracle 16 0 33388 3680 3000 s 0.3. 43 tnslsnr
12583 Oracle 16 0 533 m 308 m 306 m s 0.3. 57 Oracle


Vidb :~ # Ps-Ef | grep 6191
Oracle 6191 1 13 dec01? 1-08:49:28 ora_s002_service
Vidb :~ # Ps-Ef | grep 12599
Oracle 12599 1 13 nov18? 3-00:59:09 ora_s000_service

All the top 10 processes are Shared Server Processes similar to ora_s000, with the server load above 10.

---------

  

For example, the sharing ratio of 200 requests is 10. Each shared process can process only one request within one time, that is to say, 10 processes can only process 10 requests within the same time. If a request requires a long process, it will cause serious queuing of other requests.

The shared process requires that each request on the client be extremely fast. If one request on the client takes a long time, other requests will have to wait until they are shared.

It can be seen from the above that if there is an OLTP system with large transactions and long transactions, the system will be slower than the original!

In summary, shared connections and dedicated connections have their own advantages. The key is to look at the application. It is a good way to connect your own applications.