About Oracle connections, processes, sessions.
===================================
This is an extract from:
Tom Kyte, What's the difference between connections, sessions and processes?
JDBC Developer’s Guide and Reference 10.2
Expert Oracle JDBC Programming
A connection
A connection is a network, physical connection between you and Oracle database.
A connection might be of two types:
DEDICATED server and SHARED server.
A session
A session encapsulates user interaction with the database, from the moment user was authenticated until the moment the user disconnects.
In most cases, one connection corresponds to one session, but it does not have to be so.
Zero, one or more sessions may be established over a given connection to the database.
A process will be used by a session to execute statements.
A process
A process is a physical process or thread.
On Linux, one can see a process with "ps".
There are two types of processes in Oracle:
Background processes like SMON, PMON, RECO, ARCH, CKPT, EMNn, DBWR, etc...
User processes like dedicated servers or shared server (multi-threaded server, MTS )
You can be connected to a database yet have 0 or 1 or MORE sessions going on that connection.
Sometimes there is a one to one relationship between CONNECTION->SESSION->PROCESS. This is the regular situation in dedicated server connection.
Sometimes there is a one to many from connection to
sessions (eg: like autotrace, one connection, two sessions, one process).
A process does not have to be dedicated to a specific connection or session.
When using shared server (MTS), your SESSION will grab a process from a pool of processes in order to execute a statement. When the call is over, that process is released back to the pool of processes.
In dedicated server mode.
Each physical connection has one session, and one separate (or dedicated) process (or thread) associated with it.
In this configuration, each entry in V$SESSION would have a unique entry in V$PROCESS, connected by V$SESSION.paddr = V$PROCESS.addr.
V$PROCESS.spid is the actual server pid on the host machine.
Dedicated server is the most commonly used option.
(This is the option SH is using)
In shared server (multi-threaded server) mode.
A pool of processes is shared by physical connections.
A user has a session.
A session is your login, it means you are connected and authenticated, and you can do SQL. You have a row in v$session.
When you are not doing SQL, you are inactive and you will NOT be associated with any process.
When a shared server session becomes active, it is associated with some process.
It only uses a process for the duration of the request/call to the database.
The session does not have a process. A process does not have sessions.
A process is a resource used by sessions over time.
Example
===================
[tkyte@tkyte-pc-isdn tkyte]$ ps -auxww | grep oracleora920 | grep -v grep
[tkyte@tkyte-pc-isdn tkyte]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 10:36:03 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
idle> !ps -auxww | grep oracleora920 | grep -v grep
tkyte 19971 0.0 0.1 2196 916 pts/1 S 10:36 0:00 /bin/bash -c ps -auxww |
grep oracleora920 | grep -v grep
no process, no nothing
idle> connect /
Connected.
idle> !ps -auxww | grep oracleora920 | grep -v grep
ora920 19974 1.5 2.2 230976 11752 ? S 10:36 0:00 oracleora920
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
got my process now...
idle> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
idle> !ps -auxww | grep oracleora920 | grep -v grep
ora920 19974 0.6 2.3 230976 11876 ? S 10:36 0:00 oracleora920
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
idle> select 1 from dual;
SP2-0640: Not connected
Still have connection, process, but no session.
The message is a little "misleading". Technically there is a connection, but no session
further, autotrace in sqlplus can be used to show that you can have
a) a connection
b) that uses a single process
c) to service two sessions:
SELECT username FROM V$PROCESS WHERE username is not null;
USERNAME
--------------
OPS$TKYTE
one session
SELECT username, program FROM V$PROCESS;
USERNAME PROGRAM
--------------- ------------------------------------------------
PSEUDO
ora920 oracle@tkyte-pc-isdn.us.oracle.com (PMON)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (DBW0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (LGWR)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (CKPT)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (SMON)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (RECO)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (CJQ0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (QMN0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (S000)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (D000)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (ARC0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (ARC1)
tkyte oracle@tkyte-pc-isdn.us.oracle.com (TNS V1-V3)
14 rows selected.
To get number of non backgrouond processes:
SELECT count(*) FROM V$PROCESS WHERE background IS NOT NULL;
Background processes and one dedicated server process.
set autotrace on statistics;
Autotrace for statistics uses ANOTHER session so it can query up the stats for your
CURRENT session without impacting the STATS for that session!
SELECT username FROM V$PROCESS WHERE username IS NOT NULL;
USERNAME
--------------
OPS$TKYTE
OPS$TKYTE
Two sessions but....
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
418 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SELECT username, program FROM V$PROCESS;
USERNAME PROGRAM
--------------- ------------------------------------------------
PSEUDO
ora920 oracle@tkyte-pc-isdn.us.oracle.com (PMON)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (DBW0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (LGWR)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (CKPT)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (SMON)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (RECO)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (CJQ0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (QMN0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (S000)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (D000)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (ARC0)
ora920 oracle@tkyte-pc-isdn.us.oracle.com (ARC1)
tkyte oracle@tkyte-pc-isdn.us.oracle.com (TNS V1-V3)
14 rows selected.
same 14 processes...
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
1095 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
ops$tkyte@ORA920.US.ORACLE.COM>
A connection pooling
Connection pooling is maintained by Application server.
Creating new connection involves creating a new process or a thread in OS, and is both time consuming and exhausting for the host OS .
Rather connection pooling, is a better option.
The application server takes a connection from a pool, marks it as "used", and hands it over to the application.
The application is actually receiving a reference to a Logical Connection, rather than to a Physical Connection, so when application does close() to the connection, it just clear the connection state, and return it to the pool of Physical connections.
See also
ORA-00020: No more process state objects available and ORA-00020: maximum number of processes 0 exceeded
No comments:
Post a Comment