Introduction to the concept and v$session/v$process view of the user/server process

Server Process Concepts:

Oracle's server process has an Oracle instance created automatically to handle requests from the client process connected to the instance, and the user must obtain information from the database through a server process connected to Oracle.

For dedicated server mode (default when building a library), the client process and the Oracle server process are one by one corresponding, add a server process, approximately the required memory is: AIX 5-10m, LINUX 3-5m memory-measured is 1M.

In Shared server mode, an Oracle server process may serve multiple client processes at the same time.

The server process is primarily used to perform the following tasks:

Resolves and executes the SQL statement submitted by the client.

Reads the necessary data block from the disk data file to the SGA data buffer.

Returns the results of the SQL statement execution in the appropriate form.

User Process UI Concept

The client uses tools such as sqlplus/plsql to connect to the server process of the database server through the service name in Tnsnames.ora. To be monitored-the server process--PGA.

relationship between processes and sessions number: --

11g official Default: Sessions=1.5*processes + 22 Documents: http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams234.htm# sthref696

10g official Default: Sessions=1.1*processes + 5 Documents: http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams191.htm# REFRN10197

Connection and Session Relationship:

Connection:

The communication channel between the user process and the Oracle instance communication pathway.

This communication channel is through interprocess communication mechanisms interprocess communication mechanisms (running user processes and Oracle processes on the same computer) or network software network software (when database applications and Oracle servers are built when they are running on different computers and need to be communicated over the network.

Session:

--In proprietary server mode, a connection corresponds to a session.

Mainly refers to the connection between the user and the database.

For example, when a user initiates Sql*plus, a valid username and password must be provided, and Oracle then establishes a session for this user. The session persists from the time the user connects to the user's disconnect (or quits the database application). See Figure 1 below

For example , using Plsql to connect to a database, there is a connection, and there is a corresponding session. A SQL window opens in the Plsql, a connection, and a corresponding session.

SQL window query opened in Plsql:

Select Sid from V$mystat where Rownum=1;

40

Select B.spid,a.sid,a.username,a.program,a.machine from v$session a,v$process b where a.paddr=b.addr and a.type= ' USER ';

30152 Wuyi BYS Plsqldev.exe Workgroup\bys---This is to open the session information for the Plsql software connection to the database

30187 BYS Plsqldev.exe Workgroup\bys-This is the corresponding session information for the SQL window in the current Plsql

On the Linux system running the database query:---Process of the PID and plsql query can correspond.

[Oracle@bys3 ~]$ ps-ef |grep local |grep-v grep

Oracle 30152 1 1 21:01? 00:00:02 Oraclebys3 (Local=no)--the session of the corresponding Plsql program

Oracle 30187 1 0 21:02? 00:00:00 Oraclebys3 (local=no)--corresponding SQL window in Plsql

#####################

Using the Sqlplus test:--experimental environment, now not connected to the database.

[Oracle@bys3 ~]$ ps-ef |grep local |grep-v grep---query is not returned and no Oracle connection information is present in the system

Then open another window 2,ssh to the database server, start the sqlplus login database. Query again:

[Oracle@bys3 ~]$ ps-ef |grep local |grep-v grep

Oracle 30305 30302 3 21:10? 00:00:00 Oraclebys3 (description= (local=yes) (address= (PROTOCOL=BEQ))--This is the new open Sqlplus Connection in Window 2 Lacal=yes, indicating that it is not connected by listening--local IPC