Oracle Optimization Analysis of "session management overhead"

Analysis on "session management overhead"

[Overview]

During the optimization process, session management is a common problem, because the overhead of session maintenance is relatively high.

[The process is as follows]

Customer request (sid) → listening to receive → listening to derive a new process (systemprocess id) → customer Process

Note:

SPID: system process id, indicating the OS-level Process ID of the serverprocess );

PID: oracle process id, which can be understood as the process ID used by Oracle itself;

SID: session ID, used to connect to other columns

Operation Process]

SQL> select sid from v $ mystat whererownum = 1;

-- Sid of the current session

SID

----------

159

SQL> select p. spid

2 from v $ process p, v $ session s

3 where p. addr = s. paddr ands. sid = 159;

-- Find the corresponding serverprocess through sid (session ID)

-- Spid (system process id) The Porcess ID at the OS level of serverprocess, that is, the operating system process ID

-- Pid (oracle process id) oracle process ID

-- The query condition is that the process address is equal to the process address in the session and the session sid is 159.

-- The system processid of sid159 service is 426192.

SPID

------------

426192

$ Ps-ef | grep 426192 | grep-v grep

-- View the process whose system process id (serverprocess id) is 426192 and exclude the grep process itself

Oracle 426192 303338 0 09:29:21-oraclemetro (DESCRIPTION = (LOCAL = YES) (ADDRESS = (PROTOCOL = beq )))

-- Check the oracle process with spid 426192. This is a dedicated mode connection process.

-- The preceding query indicates that the overhead for maintaining a session is relatively high.

[Scenario example]

Assume that when an operator logs on to a system, the SQL statement is sent to the application server. If the SQL statement meets the verification requirement, the system returns the message. In this case, the database link is disconnected. After a period of time, when the operator wants to query the corresponding data based on the ID, a new link will be sent. After the information is returned this time, it will be disconnected. This is a relatively bad session link, which repeats in a loop, because you are constantly creating links, ending links, creating links, and ending links ...... this situation is reflected in oracle, and the cost will be relatively high.

In the actual production environment, a long link (or link pool) is established for the Application Server (B \ S structure) to solve the previous problem. For example, if weblogic is used as the application server and many connection objects are created to connect to the oracle database, the connection will no longer be disconnected. Then, when the front-end application needs to use the database, it will first connect to the application server, and the application server will allocate an idle link in the connection pool for the application request. After the link is used up, it will not be interrupted. Instead, it will become idle and be put back into the link pool. This process effectively reduces the number of sessions.