Oracle Tuning analysis Session Management overhead

An analysis of tuning "session management Overhead"

Introduction

During tuning, the management of sessions is a common problem because the overhead of maintaining sessions is relatively high.

"The process behaves as follows"

Client request (SID) → Listener received → listener derived new process (systemprocess ID) → Client process

Comments:

Spid:system process ID, which represents the process ID of the serverprocess at the OS level (operating system process ID);

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

Sid:session identity, used when connecting to other columns

"Operation Procedure"

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 by SID (session ID)

--spid (System process ID) serverprocess The porcess ID at the OS level, which is the operating system process ID

--PID (Oracle Process ID) Oracle Progress ID

--The query condition is the process address equals the process address in the session, and the session SID is 159

--Check out the system ProcessID for sid159 service is 426192

SPID

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

426192

$ps-ef |grep 426192|grep-v grep

--View the process with the system process ID (serverprocess ID) of 426192 and exclude the grep process itself

Oracle 426192 303338 0 09:29:21-0:02 Oraclemetro (description= (Local=yes) (address= (PROTOCOL=BEQ)))

--Check the Oracle process for SPID 426192, which is a proprietary mode connected process

--The above query indicates that the overhead of maintaining a session is relatively high

"Scenario Example"

Assuming that the operator logs on to a system, SQL is sent to the application server, which is returned after validation, and the database link is broken. After a period of time when the operator wants to query the data according to the ID, it will resend a link, this time after returning the information, it will be broken off. This is a bad conversation link that repeats itself because of the constant creation of links, ending links, creating links, ending links ... This is reflected in Oracle, where costs are higher.

In a real-world production environment, a long link (or link pool) is established for the application server (b\s structure) to resolve the previous issue. For example, with WebLogic as the application server, many linked objects are created to connect to the Oracle database and are no longer disconnected after the connection. Then, when the front-end application needs to use the database, it connects to the application server, and the application server assigns a free link in the link pool to the application request. This link is not interrupted after it has been used, but it becomes an idle state and then back to the link pool. This process is effective in reducing the number of sessions.