oracle ask tom,ask Tom解释 connect processes sesssions 的区别

ask Tom解释的很清楚，如下[@more@]

You AskedHi Tom

What's the difference between connections, sessions and processes?

I read a note from Metalink about the difference but I simply dont get it!

May you give a brief explanation?

Thank you

and we said...A process is a physical process or thread.

On unix, you can see a process with "ps" for example. It is there.

There are many types of processes in Oracle -- background processes like SMON, PMON,

RECO, ARCH, CKPT, EMNn, DBWR, etc..... And user processes like dedicated servers or

shared server (multi-threaded server -- aka MTS -- configuration)

A connection is a "physical circuit", a pathway to a database. You can be connected to a

database yet have 0 or 1 or MORE sessions going on that connection. We can see that with

sqlplus, consider (single user system here, its all about me)

[tkyte@tkyte-pc-isdn tkyte]$ ps -auxww | grep oracleora920

[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

tkyte 19971 0.0 0.1 2196 916 pts/1 S 10:36 0:00 /bin/bash -c ps -auxww |

grep oracleora920

tkyte 19973 0.0 0.1 1736 564 pts/1 S 10:36 0:00 grep oracleora920

no process, no nothing

idle> connect /

Connected.

idle> !ps -auxww | grep oracleora920

ora920 19974 1.5 2.2 230976 11752 ? S 10:36 0:00 oracleora920

(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

tkyte 19975 0.0 0.1 2196 916 pts/1 S 10:36 0:00 /bin/bash -c ps -auxww |

grep oracleora920

tkyte 19977 0.0 0.1 1736 564 pts/1 S 10:36 0:00 grep oracleora920

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

ora920 19974 0.6 2.3 230976 11876 ? S 10:36 0:00 oracleora920

(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

tkyte 19978 0.0 0.1 2196 916 pts/1 S 10:36 0:00 /bin/bash -c ps -auxww |

grep oracleora920

tkyte 19980 0.0 0.1 1736 564 pts/1 S 10:36 0:00 grep oracleora920

idle> select * from dual;

SP2-0640: Not connected

still have my process, but no session, the message is a little "misleading".

Technically -- I have a connection, I don't have a 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:

ops$tkyte@ORA920.US.ORACLE.COM> select username from v$session where username is not

null;

USERNAME

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

OPS$TKYTE

one session, ME

ops$tkyte@ORA920.US.ORACLE.COM> 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.

you can see all of the backgrounds and my dedicated server...

ops$tkyte@ORA920.US.ORACLE.COM> 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!

ops$tkyte@ORA920.US.ORACLE.COM> select username from v$session where username is not

null;

USERNAME

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

OPS$TKYTE

OPS$TKYTE

see, 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

ops$tkyte@ORA920.US.ORACLE.COM> 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>

I'll try to put it into a single, simple paragraph:

A connection is a physical circuit between you and the database. A connection might be

one of many types -- most popular begin DEDICATED server and SHARED server. Zero, one or

more sessions may be established over a given connection to the database as show above

with sqlplus. A process will be used by a session to execute statements. Sometimes

there is a one to one relationship between CONNECTION->SESSION->PROCESS (eg: a normal

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 however, for example 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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271063/viewspace-1008036/，如需转载，请注明出处，否则将追究法律责任。