Dedicated servers and shared servers in Oracle connect to session

Connection: A physical path between the client process and the server process

Session: A logical entity of a database, built by client processes and server processes, as a channel for communication between 2 processes

One, dedicated server connection

1. On Linux, you can run the client and server processes on the same machine, and you can clearly see this parent/child process relationship

System@prod> Select A.spid dedicated_server,b.process clientpid

2 from v$process a,v$session b

3 Where A.addr=b.paddr and

4 b.sid= (select Sid from V$mystat where rownum=1);

Dedicated_se clientPID

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

4606 4557

System@prod>!/bin/ps-ef | grep 4557

Oracle 4557 8686 0 15:25 PTS/3 00:00:00 sqlplus Client process (parent process)

Oracle 4606 4557 0 15:25? 00:00:00 Oracleprod (description= (Local=yes)

(address= (PROTOCOL=BEQ)) Server-side processes (child processes)

That is, the sqlplus process is the parent process->oracleprod is a subprocess, and can clearly see this parent/child process relationship. Who initiates who is the parent process

Description= (Local=yes) indicates that the client process is local

Description= (Local=no) indicates that the client process is remote

2. You can use Sqlplus to look at the actual connection and what the conversation looks like. The Autotrace command is used here and two sessions are found

, we created 2 sessions in a single connection. Different sessions on a single connection can use different user identities

That is, you can create 0, one, multiple sessions on a single connection, and each session is independent.

A session can have a connection or no connection (session will be idle)

[ORACLE@SECDB1 ~]$ sqlplus system/oracle Use System user to enter Sqlplus

System@prod> Select Username,sid,serial#,server,paddr,status from V$session

2 where Username=user;

USERNAME SID serial# SERVER paddr STATUS

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

SYSTEM dedicated329db9dcactive

Just open autotrace to see the statistics of the statements executed in Sqlplus

System@prod> set Autotrace on statistics to open the statistics in the execution plan

System@prod> Select Username,sid,serial#,server,paddr,status from V$session

2 where Username=user;

USERNAME SID serial# SERVER paddr STATUS

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

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

SYSTEM 109 183 Dedicated329db9dcinactive Monitoring session

SYSTEM dedicated329db9dcactive Actual session

This 109 110 session uses a 329DB9DC process address, which is a connection that creates 2 sessions

Statistics

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

0 Recursive calls

0 db Block gets

0 consistent gets

0 physical Reads

0 Redo Size

774 Bytes sent via sql*net to client

381 bytes received via sql*net from client

2 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

2 rows processed

System@prod> set Autotrace off execution plan

When Autotrace is enabled in Sqlplus, if we perform a DML operation (insert update Delete merge)

(1) If a "monitoring session" does not exist, it creates a new session using the current connection (monitoring session 109)

(2) Request this new session query V$sesstat view to remember the initial statistics of the actual conversation.

(3) running DML operations in the original session

(4) After the DML statement is executed, Sqlplus requests the monitoring session to query the V$sesstat view again and generates the previous report, showing that the actual session statistics are only poor.

3. Now use Sqlplus to view a connection without any sessions, type a disconnect command in the Sqlplus window

System@prod> Disconnect closes all sessions

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production

With the partitioning, OLAP and Data Mining options

@> Show User

User is "" has no logged-in users

@> Select Username,sid,serial#,server,paddr,status from V$session

2 where Username=user;

Sp2-0640:not connected just shows no session, but the connection is still

Note: Technically, this command should be called distroy_all_session (breaking all sessions) more appropriate than disconnect because we are not really disconnected from the physical connection. If you want to disconnect the physical connection, you should run exit

@> exit

[ORACLE@SECDB1 ~]$

[ORACLE@SECDB1 ~]$ sqlplus/as SYSDBA uses the SYS user to query the session of the System user

Sys@prod> SELECT * from v$session where username= ' SYSTEM '; As you can see, there is no session under this system user

No rows selected

But the PADDR329DB9DC process still exists and corresponds to a physical connection.

Sys@prod> Select Username,program from v$process where addr= ' 329DB9DC ';

USERNAME Program

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

Oracle ORACLE@SECDB1 (TNS v1-v3)

4. Now use the Sqlplus Connect command to create a new session, but still use the original PADDR329DB9DC process connection

@> Conn System/oracle

Connected.

System@prod>

System@prod> Select Username,sid,serial#,server,paddr,status from V$session

2 where Username=user;

USERNAME SID serial# SERVER paddr STATUS

SYSTEM dedicated329db9dcactive

You can note that PADDR329DB9DC still uses the original process connection, but the SID is the new session ID and may either be assigned the original SID or the new SID, depending on whether someone is logged in when we log off, and if our original SID is available.

Note: The most common is a connection that corresponds to a session, which is what most people see every day

1 Dedicated Server mode UGA in the PGA space allocation

2 in the shared server mode, UGA allocates space in the SGA

Author: 51cto Blog Oracle Little Bastard