Connection between the oracle client and the server

Author: skate
Oracle service process how to handle user process requests, see: http://www.bkjia.com/database/201202/120122.html
 

To log on to the database and do things in the database, you must first connect to the database. Based on the connection principle, the database can be divided into a dedicated server connection (dedicated server) and a shared server connection (shared server ). TCP/IP is the main network protocol used to connect to Oracle over the network.
 
What is a dedicated server?
 
When logging on to oracle, Oracle will always create a new process for me. This is usually called a dedicated server configuration, because this server process will specifically serve me during my session lifetime. A new dedicated server will appear for each session, and there is a one-to-one ing between the session and the dedicated server. As defined, this dedicated server is not part of the instance. My client processes (that is, programs that want to connect to the database) will communicate directly with this dedicated server through some network channel (such as TCP/IP socket, the server process receives and executes My SQL statements. If necessary, it will read the data file and search for the data I want in the database cache. Maybe it will complete my update statement, or it may run my PL/SQL code. The main goal of this server process is to respond to the SQL call I submitted.

 
What is a shared server?
 
Oracle can also accept connections in another way, known as shared server. The official saying is Multi-ThreadedServer or MTS. If this method is used, no other thread or new UNIX process will be created for each user connection. On the Shared Server, Oracle uses a "shared process" pool to provide services for a large number of users. A Shared Server is actually a connection pool mechanism (such as a connection pool of a program ). With the Shared Server, we do not have to create 10000 dedicated servers for 10000 database sessions (this is too many processes or threads and difficult to manage). Instead, we only need to create a few processes/threads, as the name suggests, these processes/threads will be shared by all sessions. In this way, Oracle allows more users to establish connections with databases, otherwise it is difficult to connect more users. If my machine is allowed to manage 10000 processes, this load will surely crush it, but it is still possible to manage 100 or processes. The sharing server mode is used. The sharing process is usually started with the database. You can see this process using the ps command.
 
 
There is a major difference between a shared server connection and a dedicated server connection. The client process connected to the database does not directly communicate with the shared server, but the dedicated server does not, the customer process will directly communicate with the dedicated server. The reason why you cannot directly talk to the sharing server is that the server process is shared. In order to share these processes, another mechanism is required to "talk" with the sharing server process ". Therefore, Oracle uses one or more processes called schedulers. The customer process communicates with a scheduler process through the network. This scheduler processes the customer's requests into the request queue in the UGA of the SGA (this is also one of the purposes of the SGA ). The first idle Shared Server will get this request and process it (for example, the request may be update t set x = X + 5 where y = 2 ). After the command is completed, the Shared Server places the response in the response queue of the original scheduler (the scheduler that receives the request. The scheduler process keeps listening to this queue and sends the result to the customer after the result is found.
 
 

The customer connection sends a request to the scheduler. The scheduler first places the request in the Request queue (①) in the SGA. The first available Shared Server retrieves this request from the request queue ② and processes it. After the Shared Server completes processing, place the response (return code, data, etc.) in the response queue ③. Then the scheduler obtains the response ④ and sends it back to the customer.
 
 
1. Connection Between the oracle client and the server
 
First, a user process sends a connection request. If the host name is used or the host name hit by the Local Service uses the machine name (non-IP address ), the request will be parsed by the service name of the DNS server or HOST file and then transmitted to the ORACLE listening process. After receiving the user request, the listening process will process the user request in two ways.
 
C:/Documents and Settings/Administrator> sqlplus sys/aibo @ test as sysdba
 
SQL * Plus: Release 10.2.0.4.0-Production on Monday August 16 10:51:15 2010
 
Copyright (c) 1982,200 7, Oracle. All Rights Reserved.
 
 
Connect:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL>
 
Sys/aibo is the username and password, test is the TNS service name, and TNS represents the bottom layer of the Transparent Network (Transparent Network Substrate), which is the "Basic" software for Oracle clients to process remote connections, it is possible to establish peer-to-peer communication. The TNS connection string tells the Oracle software how to connect to a remote database. Generally, client software running on your machine reads a tnsnames. ora file. This is a plain text configuration file with the following content:
 
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.2.10) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
 
 
The Oracle client can obtain useful information from the tns connection string TEST, that is, the host name, the port number of the host listening, and the database service name connected to the host, A service name indicates an application group with common attributes, service threshold, and priority. The number of instances that provide services is transparent to applications. Each database instance can be registered with the listener to provide multiple services. Therefore, services are mapped to physical database instances and DBA is allowed to associate the thresholds and priorities.
 
Now the oracle client can use the tns connection string to know where to connect. It will open a TCP/IP socket connection with the HOST (HOST = 192.168.2.10) on the port; if the server is configured with oracle net and a listening process listens for connection requests on port 1521, the connection request is sent. In the network environment, we have a tns listener process, which allows us to physically Connect the client to the server. After receiving the request, he will process the connection according to his configuration file, and may reject the request (for example, because there is no such database, or our IP address may be limited, cannot connect to this host). It may also accept the request and establish a connection.
 
In dedicated server mode: After a listening process receives a user process request, it generates a new dedicated server process (on UNIX, this is through fork () and exec () system Call (in UNIX, the only way to create a new process after Initialization is through fork (); on Windows, the listener process requests the database process to create a new thread for the connection. Once this thread is created, the customer will "Redirect" to this thread to establish a physical connection accordingly ). In addition, all the control information of the user process is sent to the server process, that is, the new server process inherits the information of the listening process, and then the server process sends a RESEND packet to the user process, the user process is notified that it can start sending messages to it. The user process sends a CONNECT packet to the newly created server process, and the server process then responds to the user process with an ACCEPT packet, the user process is formally connected to the server process. We call this connectionHAND-OFF connection, Also calledConversion connection.
 
Another method is to listen to a process that generates a new dedicated server process after receiving a request from the user process. This server process selects a TCP/IP Port to control interaction with the user process, then, the information is sent back to the listening process. The listening process then sends the information to the user process. The user process uses this port to send a CONNECT packet to the server process, the server process sends an ACCEPT packet to the user process. Therefore, the user process can send information to the server process. This method is called redirection connection. The HAND-OFF connection requires the system platform to have the process inheritance capability. To enable HAND-OFF support for windows nt/2000, you must set USE_SHARED_SOCKET in HKEY_LOCAL_MACHINE> SOFTWARE> ORACLE> HOMEX.
 
 
In the Shared Server mode, only the redirection connection is used. The working mode is to listen to a process that receives a request from the user process and generate a new scheduling process, this scheduling process selects a TCP/IP Port to control interaction with the user process, and then transmits this information to the listening process. The listening process then transmits this information to the user process, the user process uses this port to send a CONNECT packet to the scheduling process, and the scheduling process sends an ACCEPT packet to the user process. As a result, the user process can officially send information to the scheduling process.
 
You can set the MAX_DISPIATCHERS parameter to determine the maximum number of scheduling processes. If the number of scheduling processes has reached the maximum, or the existing scheduling process is not full, the listening process does not create a new scheduling process, but allows one of the scheduling processes to use a TCP/IP Port to interact with the user process. Each time a user process request is received by a scheduling process, the scheduling process registers at the listening process so that the listening process can balance the load of each scheduling process, all user process requests will be queued in a limited number of scheduling processes, and all scheduling processes will put some user process requests in their respective queues in the same request queue in sequence, wait for multiple ORACLE Shared Server Processes to process (the number of shared server processes can be set through the SHARED_SERVERS parameter). That is to say, all scheduling processes share the same request queue, in Shared Server mode, there is only one request queue for the next instance. After the Shared Server process processes the requests from the user process, it takes the requests from different scheduling processes and puts the returned results into different response queues, that is to say, the number of scheduling processes is the number of response queues, and each scheduling process extracts the results from their respective response queues and returns them to the user process.
 
 
So far, the user process has submitted the SQL to be executed to the oracle service process. How Does oracle handle this process?
 
 
 
---- Continued ---