Explanation of server processes of Oracle Database Processes

We know that,Oracle DatabaseEach process in to complete a specific task or a group of tasks, each process will allocate internal memory PGA memory) to complete its tasks. Oracle instances mainly have three types of processes:Server processBackground processes and subordinate processes. This article mainly introduces the knowledge of Oracle Database Server processes. We will continue to introduce background processes and subordinate processes in subsequent articles.

Oracle instances mainly have three types of processes:

1) server processes (serverprocess): these processes are completed based on customer requests. We have some knowledge about dedicated servers and shared servers. They are server processes.

2) backgroundprocess: these processes are started with the database and used to complete various maintenance tasks, such as writing blocks to disks, maintaining online redo logs, and clearing abnormal and aborted processes.

3) subordinate processes (slaveprocess): these processes are similar to background processes, but they must perform some additional work on behalf of background or server processes.

In some operating systems such as Windows, Oracle uses threads for implementation. Therefore, in such operating systems, we need to regard what we call "processes" as synonyms of "Threads.

The word "process" indicates both a process and a thread. If you are using a multi-process Oracle implementation, such as the Oracle implementation on UNIX, the "process" is very appropriate. If you are using the Oracle implementation of a single process, such as the Oracle implementation on Windows, the "process" actually refers to the "thread in the Oracle process ". So, for example, when talking about the DBWn process, it corresponds to the DBWn thread in the Oracle process on Windows.

Server process

A server process is a process that completes the work on behalf of a customer session. The SQL statements sent by the application to the database are finally received and executed by these processes.

1) dedicated server dedicatedserver) when a dedicated server is used for connection, a dedicated process for this connection will be obtained on the server. There is a one-to-one ing between a database connection and a process or thread on the server.

2) Shared Server (sharedserver) connection. When a Shared Server is used for connection, multiple sessions can share a server process pool. The processes are generated and managed by the Oracle instance. You are connected to a Database Scheduler dispatcher, instead of a dedicated server process specially created for the connection.

Note the differences between connections and sessions:

1) connection) is a physical path between the customer process and the Oracle instance, for example, a network connection between the customer and the instance ).

2) session sessions) are different. This is a logical entity in the database. The customer process can execute SQL statements on the session. Multiple independent sessions can be associated with one connection. These sessions can even exist independently of the connection.

The tasks of the dedicated server process and the Shared Server process are the same: process all the SQL statements you submit. When you submit a SELECT * from emp query to the database, an Oracle dedicated/Shared Server process will parse the query, and put it in the shared pool or it is best to find that the query is already in the Shared Pool ). This process should propose an execution plan. If necessary, it may find the necessary data in the buffer cache or read the data from the disk into the buffer cache. These server processes are heavy-duty processes. In many cases, you will find that these processes occupy the most CPU time, because these processes are used to perform sorting, aggregation, joining, and so on. Almost all work is done by these processes.

Dedicated server connection

In dedicated server mode, there is a one-to-one ing between client connections and server processes or threads. If a UNIX host has 100 dedicated server connections, the corresponding 100 processes will be executed. It can be illustrated in graphs, as shown in Figure 5-1.

The customer application links to the Oracle database, which provides the APIS required to communicate with the database. These APIs know how to submit queries to the database and process the returned cursor. They know how to package your requests for network calls, while dedicated servers Know How To unbind these network calls. This part of the software is called Oracle Net, but it may be called SQL * Net or Net8 in earlier versions. This is a network software/protocol. Oracle uses this software to support customer/server processing. Even in an n-tier architecture, it "lurks" customer/server programs ). However, even though Oracle Net is not technically involved, Oracle adopts the same architecture. That is to say, even if the customer and the server are on the same machine, these two processes are also called two tasks) architecture.

This architecture has two advantages:

1) remote execution of remoteexecution): the client application may be executed on another machine rather than the machine where the database is located. This is natural.

2) address space isolation addressspace isolation): server processes can read and write SGA. If the customer process and the server process are physically linked together, an incorrect pointer in the customer process can easily damage the data structure in SGA.

Shared Server connection

Oracle Net must be mandatory for shared server connections, even if both the client and server are on the same machine. If the OracleTNS listener is not used, the Shared Server cannot be used. As mentioned above, the customer application will connect to the Oracle TNS Listener and redirect or forward it to a scheduler. The scheduler acts as a "catheter" between customer applications and Shared Server Processes ". Figure 5-2 shows the architecture for establishing a shared server connection with a database.

Here, we can see that the customer application links to the Oracle database) and physical connection to a scheduler process. You can configure multiple schedulers for a given instance, but there are hundreds or even thousands of schedulers. This is not uncommon if you only have one scheduler. The scheduler is only responsible for receiving incoming station requests from the customer application and placing them into a request queue in the SGA. The first available Shared Server process is essentially the same as the dedicated server process) Select a request from the queue and attach the UGA Figure 5-2 of the relevant session to the box marked with "S ). The Shared Server processes this request and places the output in the response queue. The scheduler keeps monitoring the response queue to get the results and returns the results to the customer application. As far as the customer is concerned, it cannot tell whether a connection is made through a dedicated server connection or through a shared server connection. It seems that the two are the same, but the difference between the two is obvious at the database level.

Connection and session

One or more sessions can be established on one connection. Each session is independent, even if they share the same database physical connection. The submission in one session does not affect any other sessions on the connection. In fact, each session on a connection can use different user identities.

In Oracle, the connection is only a special line between the customer process and the database instance, and the most common is the network connection. This connection may be connected to a dedicated server process or the scheduler. As mentioned above, the connection can have 0 or more sessions, which means there can be connections without corresponding sessions. In addition, a session can be connected or not connected. When using advanced Oracle Net features such as connection pool), the customer can delete a physical connection, while the session is still retained but the session will be idle ). When a customer performs an operation on this session, it will re-establish the physical connection.

Connection): the connection is a physical path from the customer to the Oracle instance. The connection can be established on the network or through the IPC Mechanism. A connection is usually established between a customer process and a dedicated server or a scheduler. However, if you use the Connection Manager (CMAN) of Oracle, you can also establish a Connection between the customer and CMAN and between the CMAN and the database.

Session): a session is a logical entity in an instance. This is your session state), that is, the data structure in the memory of a group of specific sessions. When talking about "database connection", most people first think of "session ". You need to execute SQL, submit transactions, and run stored procedures on sessions on the server. You can use SQL * Plus to see what the actual connection and session looks like. It is also quite common to see that the actual last connection has multiple sessions.

Here, the AUTOTRACE command is used to concurrently run two sessions. We used a process to create two sessions on a connection. The first session is as follows:

 
 

  
  
SQL> select username, sid, serial#, server,paddr, status from v$session where username='SYS';  
  
  
USERNAME                              SID    SERIAL# SERVER    PADDR   STATUS  
  
  
------------------------------ -------------------- --------- -------- --------  
  
  
SYS                                   153         27 DEDICATED 3621B264 ACTIVE 
 
 

The above PADDR column is the address of the dedicated server process.

Next, you only need to open AUTOTRACE to view the statistical results of the statements executed in SQL * Plus:

 
 

  
  
SQL> set autotrace on statistics  
  
  
SQL> select username, sid, serial#,server, paddr, status from v$session where username='SYS';  
  
  
USERNAME                              SID    SERIAL# SERVER    PADDR   STATUS  
  
  
------------------------------ -------------------- --------- -------- --------  
  
  
SYS                                   152         88 DEDICATED 3621B264 INACTIVE  
  
  
SYS                                   153         27 DEDICATED 3621B264 ACTIVE  
  
  
Statistics  
  
  
----------------------------------------------------------  
  
  
0  recursive calls  
  
  
0  db block gets  
  
  
0  consistent gets  
  
  
0  physical reads  
  
  
0  redo size  
  
  
770  bytes sent via SQL*Net toclient  
  
  
385  bytes received via SQL*Netfrom client  
  
  
2  SQL*Net roundtrips to/fromclient  
  
  
0  sorts (memory)  
  
  
0  sorts (disk)  
  
  
2  rows processed 
 
 

Now there are two sessions, but both sessions use the same dedicated server process. We can see from the fact that they both have the same PADDR value. It can also be confirmed from the operating system, because no new process is created, only one process is used for one connection for the two sessions ).

Note that one of the original sessions is ACTIVE ). This makes sense: it is running a query to display this information, so it is certainly active. But what about INACTIVE sessions? What does the session do? This is the AUTOTRACE session. Its task is to "Monitor" our actual sessions and report what it has done.

When enabling AUTOTRACE in SQL * Plus, if we perform dml insert, UPDATE, DELETE, SELECT, and MERGE operations, SQL * Plus performs the following actions:

(1) If no secondary session exists [1], it will use the current connection to create a new session.

(2) This new session is required to query the V $ SESSTAT view to remember the initial statistical value of the actual session that runs DML.

(3) run the DML operation in the original session.

(4) After the DML statement is executed, SQL * Plus requests another session, that is, "monitoring" session.) query V $ SESSTAT again and generate the preceding report, displays the statistical results of DML sessions executed by the original session.

If AUTOTRACE is disabled, SQL * Plus terminates this additional SESSION and will not be able to see this SESSION in V $ SESSION. You may ask, "Why does SQL * Plus need to do so and why does it need to create another session ?". The reason is: if the same session is used to monitor memory usage, the execution monitoring itself will also use the memory.

If you observe the statistical results in the same session, the statistical results will be affected, resulting in modifications to the statistical results ). If SQL * Plus uses a session to report the number of I/O executions, how many bytes are transferred over the network, and how many sort operations are performed, then, the query to view the detailed information will also affect the statistical result. These queries may also sort, execute I/O, and transmit data on the network !). Therefore, we need to use another session for proper measurement.

So far, we have seen that a connection can have one or two sessions. Now, we want to use SQL * Plus to view a connection without any session. This is easy. In the same SQL * Plus window used in the preceding example, you only need to type a "Easy to misunderstand" command, that is, DISCONNECT:

Ops $ tkyte @ ORA10G> set autotrace off

Ops $ tkyte @ ORA10G> disconnect

Technically speaking, this command should be called DESTROY_ALL_SESSIONS, which is more suitable than DISCONNECT, because we have not physically disconnected.

Note that to truly disconnect the connection in SQL * Plus, run the "exit" command, because you must exit to completely cancel the connection. However, we have closed all sessions.

Use another user account to open another session and query the original user SYS.

 
 

  
  
SQL> select username, sid, serial#,server, paddr, status from v$session where username='SYS';  
  
  
no rows selected 
 
 

As you can see, there is no session under this account, but there is still a process with a physical connection using the previous ADDR value ):

 
 

  
  
SQL> select username, program fromv$process where addr = hextoraw('3621B264');  
  
  
USERNAME        PROGRAM  
  
  
---------------------------------------------------------------  
  
  
oracle          oracle@db1 (TNS V1-V3) 
 
 

Therefore, there is a "connection" with no related sessions ". You can also use the SQL * Plus CONNECT command to get the command name inappropriate). It is more appropriate to create a new session CONNECT command named CREATE_SESSION in this existing process ):

 
 

  
  
SQL> conn / as sysdba;  
  
  
Connected.  
  
  
SQL> select username, sid, serial#,server, paddr, status from v$session where username='SYS';  
  
  
USERNAME                              SID    SERIAL# SERVER    PADDR   STATUS  
  
  
------------------------------ -------------------- --------- -------- --------  
  
  
SYS                                   158         34 DEDICATED 3621B264 ACTIVE 
 
 

We can note that PADDR is the same, so we are still using the same physical connection, but it may) have a different SID. I said "There may be" because the same SID may be assigned, depending on whether someone else logs in when we log out and whether our original SID is available.

So far, these tests are executed using a dedicated server connection, so PADDR is the process address of the dedicated server process.

Here is an introduction to the Oracle database server process. I hope this introduction will be helpful to you!