Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the sharing mode and proprietary mode of the database". In the daily operation, I believe that many people have doubts about the sharing mode and proprietary mode of the database. The editor consulted all kinds of materials and sorted out the simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "what is the sharing mode and proprietary mode of the database?" Next, please follow the editor to study!

I. brief introduction

Concept

Proprietary connection: respond to a client request with a server process

Shared connection: in shared server mode, after the client connects to the dispatcher through snooping, the dispatcher randomly assigns a port, and the client disconnects and listens through the assigned port and dispatcher connection. The connection to the monitor is short-lived.

Applicable scenario

For applications with a small number of connections, it is appropriate to use proprietary connections, and the customer's request response is timely.

For applications with a large number of connections, it is appropriate to use shared connections to make full use of system resources.

II. Related parameters

General parameter

Remote_listener: you can separate the database from the listener.

The remote_listener for a stand-alone environment is generally empty.

In the rac environment, listener is started with the cluster crs. And the startup of the database rdbms depends on the resources of the asm instance, so there is no listener that does not start after starting the database, except for failure.

Local_listener is the listener information of this node

Remote_listener is usually the information of scan

Local_listener

Pmon will only dynamically register listeners whose port is equal to 1521, otherwise pmon cannot dynamically register listener. If you want pmon to register listener dynamically, you need to set the local_listener parameter.

Listener_networks: required when configuring multiple remote_listener and local_listener, but rarely used at ordinary times

Example: alter system set LISTENER_NETWORKS=' ((NAME=network1) (LOCAL_LISTENER=listener_net1) (REMOTE_LISTENER=scanone.gaopp.com:1521))','((NAME=network2) (LOCAL_LISTENER=listener_net2) (REMOTE_LISTENER=remote_net2))'

Shared parameter

SHARED_SERVERS: if the shared_ server value is 0, the database does not start shared services mode. This parameter is required to configure shared server, and only this parameter is required. It specifies the number of shared server process starts when the instance starts. Do not set this parameter too large, otherwise it will take more time to start the database instance. After ORACLE starts, the shared_servers will be dynamically adjusted according to the load.

The maximum shared server process that MAX_SHARED_SERVERS:oracle can use at the same time. Do not set this parameter less than shared_servers, if you dynamically modify shared_servers greater than max_shared_servers,oracle will overwrite the value of max_shared_servers, you need to modify max_shared_servers. At the same time, it cannot be greater than processes. This parameter is set for resource-intensive operations (batch processing), to reserve some process for DBA tasks (rman backup)

SHARED_SERVER_SESSIONS: specifies the total number of shared server session allowed. If this parameter is set, do not exceed the value of sessions. If this value is not set, as long as there is free session, it can be used. Setting this value is the User Sessions reserved for dedicated connections.

DISPATCHERS: the number of dispatchers sharing the connection

MAX_DISPATCHERS: the maximum number of dispatchers for shared connections that can be concurrent at the same time, which is currently negligible

CIRCUITS: number of virtual loops available (Specifies the total number of virtual circuits that are available for inbound and outbound network sessions)

III. Resources

Memory

Large_pool_size

Compared with the dedicated server mode, the UGA of the shared service mode is in SGA, while the UGA of the dedicated server mode is in PGA. If large_pool_size is set in the shared server mode, the user's UGA will be in large_pool. The memory reduced by MTS is actually the memory needed for each user to connect to the operating system process in the dedicated server mode. The shared server mode limits the number of Server Process, reduces the memory needed to establish Server Process, and does not need to create and delete processes frequently when the concurrency is large, which reduces the corresponding overhead and improves the concurrency. Therefore, the shared server mode is suitable for business systems with high concurrency and small processing capacity.

Process

After the database is started, there are two more processes than the dedicated server mode, one is the scheduling process (Dnnn), and the other is the shared server process (Snnn). The request from the client is accepted by the dispatcher, and the dispatcher places the request into the Request queue. The idle Server Process starts processing requests in the queue according to the request queue. The processed results are put into the Response queue. Finally, DIspatcher returns the final result to the client.

Snnn: the dispatcher process enables client processes to share a limited number of server processes. You can create multiple dispatcher processes for a single database instance. The optimal number of dispatcher processes depends on the operating system limit and the number of connections per process.

Dnnn: in a shared server configuration, each shared server process serves multiple client requests. Shared server processes and dedicated server processes provide the same functionality except that shared server processes are not associated with specific client processes.

Fourth, advantages and disadvantages

Advantages

Reduces the number of processes in the instance

Increased the number of more concurrent users

Realize dynamic load balancing

Reduced the number of idle server processes

Reduced instance PGA memory

Disadvantages: because there are all kinds of problems in the shared server mode, and the middleware can also achieve the effect of connection pooling, so in general, it will not share the server mode, just use the dedicated server mode.

The code path of a shared server is longer than that of a dedicated server, so it is inherently slower than a dedicated server.

There is the possibility of artificial deadlock because it is serial, and as long as a connection is blocked, all users on the server process are blocked and are most likely deadlocked.

There is the possibility of exclusive transactions, because if a session's transaction runs for too long, it monopolizes shared resources, other users can only wait, while dedicated servers, each client is a session.

The shared server mode limits certain database features, such as the inability to start and shut down instances separately, media recovery, the use of Log Miner, and SQL_TRACE is meaningless (because it is shared rather than the current session).

V. Information collection

Event

Event 10257 (details will be given later)

View

V$DISPATCHER: provides DISPATCH process information, including DISPATCH name, network, address, status, statistics, index number, etc.

It is recorded that the dispatchers parameter is configured.

V$DISPATCHER_CONFIG: provides configuration information for DISPATCH

V$DISPATCHER_RATE: provides RATE statistics for DISPATCH

V$CIRCUIT: provides virtual loop information (virtual circuits) for users to connect to the database through DISPATCH

There are records indicating that there is a connection using shared mode. If there is no record, the server mode cannot be determined.

V$SHARED_SERVER: provides shared server information

If there is a record, and the STATUS field is WAIT (COMMON), it means sharing is started.

No record, or the STATUS field is TERMINATED, which means the shared server is not started

V$SHARED_SERVER_MONITOR: provides optimization information for shared servers

V$QUEUE: provides shared server queue information.

V$SESSION: if the value of the SERVER field includes DEDICATED and NONE, the current instance starts the shared server, and the session whose SERVER is NONE is using the shared server to connect. At the same time, if only DEDICATED is displayed, it does not mean that the server must be working under the dedicated server, and it is possible to start the sharing mode. It's just that currently all connections to the database are in dedicated server mode.

Turn off the database sharing mode

You only need to set the parameter shared_servers to 0 to turn off the shared mode of the database. After the script is executed, all shared connections to the database will not succeed, but unreleased shared connections will remain connected until disconnected.

Alter system set shared_servers=0

If both the parameters shared_servers and max_shared_servers are set to 0, the shared connection mode will be terminated. All shared connections will be disconnected (already connected sessions will also be disconnected)

7. Open the database sharing mode

Set the parameter shared_servers and set its value to greater than 0 to turn on server sharing mode. Other shared server parameters do not need to be set, but it is best to set the max_shared_servers parameter as well

Alter system set shared_servers=1 scope=both;alter system set max_shared_servers = 24 scope=both

For setting the parameter dispatchers, you can use the following command

Alter system set dispatchers=' (protocol=TCP) (disp=8) (serv=xxx)'

The protocol is indicated above, the disp represents the number of processes in the dipatcher, and the service specifies the name of the service to use the shared server mode, respectively. Use the above mode to specify a shared mode in which only a service is started, or to set all services to use shared mode, set to:

Alter system set dispatchers=' (PROTOCOL=TCP)'; VIII. Judgment of the current model

View the V$SESSION view

The current connection mode is further determined by the SERVER field of the v$session view

SELECT SID, USERNAME, OSUSER, MACHINE,TERMINAL, SERVER FROM V$SESSION WHERE USERNAME IS NOT NULL

View the tns configuration file for connecting to the database, as shown below

TEST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.16.22) (PORT = 1521)) (CONNECT_DATA = # SERVER = DEDICATED is a dedicated service mode (SERVER = SHARED) (SERVICE_NAME = epps)). At this point, the study on "what is the shared mode and proprietary mode of the database" is over, hoping to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.