DIFFERENCES BETWEEN PROCESSES, SESSIONS AND CONNECTIONS(zt from asktom)

最新推荐文章于 2024-11-15 18:38:47 发布
最新推荐文章于 2024-11-15 18:38:47 发布
阅读量140 收藏
点赞数
文章标签： 数据库
-- Thanks for the question regarding "DIFFERENCES BETWEEN PROCESSES, SESSIONS AND CONNECTIONS", version 8.1.7.4
originally submitted on 28-Sep-2002 10:19 Eastern US time, last updated 27-Apr-2005 16:37

You Asked 
Hi 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.

[@more@]

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

转载于:http://blog.itpub.net/94317/viewspace-797106/

congticuan8801
关注
Diagnostic differences between educationally handicapped and learning disabled students
06-29
Diagnostic differences between educationally handicapped and learning disabled students Pr~.rhulugv in the Schuul.r PPVT and MSCA with Autistic Children 469 1980. 17. 469-473 SCHOPLER, E. ...
Identification of statistically significant differences between scaled scores and psycholinguistic ages on the ITPA
06-29
Identification of statistically significant differences between scaled scores and psycholinguistic ages on the ITPA IDENTIFICATION OF STATISTICALIiY SIGNIFICANT DIFFERENCES BETWEEN SCALED SCORES ...
DIFFERENCES BETWEEN PROCESSES, SESSIONS AND CONNECTIONS", version 8.1.7.4
20年老程序员重新学编程
04-14 1123
You Asked (Jump to Toms latest followup)TomCan you give an example carried out in pro* c and the same done in pl/sql, and show us which is faster and why.I guess pro*c also does array in
Caused by: com.mysql.cj.core.exceptions.InvalidConnectionAttributeException: The server time zone...
mjlfto的博客
07-09 1万+
spring boot + mybaits 出现异常：org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: ### Error updating database. Cause: org.springframework.jd
The difference between two redis connections in PHP
马金波
05-24 177
ContentconnectpconnectAdvantages of pconnect connect The life cycle of connect mode is only in the running PHP process, when the process ends, the connection is disconnected. pconnect The lifecycle of...
6 Major Differences between English and Chinese
王二狗
08-06 7541
There are some basic major differences between English and Chinese. It is important to be aware of the major differences between the two languages in order to reduce your mistakes in Chinese and also t
The Differences of Dietary Cultures Between China and the West
fanyang_1996的博客
12-21 6001
Abstract Since the entrance of 21st century, the cross-culturalcommunication has become the key feature of this new era. As a result of thedifference of regional feature, climatic environment and soc
The Differences Between Data, Information and Knowledge
技术是条不归路
10-25 3489
We frequently hear the words Data, Information and Knowledge used as if they are the same thing.By defining what we mean by data, information and knowledge – and how they interact with one another.
Behavior differences between S3 and Modern Standby
佛系程序员
06-13 4332
Technical differences Connected and disconnected Modern Standby states transitions follow a very similar code flow. The biggest difference is maintaining network connection. When a system goes into
The Differences Between Successful People and Unsuccessful People
热门推荐
03-20 6万+
A few weeks ago I received a postcard in the mail from the CEO of Petra Coach, the creator of Align Software and a fellow member of Entrepreneurs Organization. I've never met him, but Andy Bailey an
What are the differences between struct and class in C++?
玮哥的博客
03-26 1599
Question： This question was already asked in the context of C#/.Net. Now I'd like to learn the differences between a struct and a class in C++. Please discuss the technical differences as well
What are the differences between iputils-ping and inetutils-ping?
hhhhhyyyyy8的博客
06-01 2231
今天无意发现iputils、inetutils两个包中都有ping命令，区别在哪里？下面这个网址中的回答很好。 https://unix.stackexchange.com/questions/400351/what-are-the-differences-between-iputils-ping-and-inetutils-ping 我自己电脑中的ping命令是iputils包中的： ...
The Differences Between Chinese and American Cultures from the P
09-14
The Differences Between Chinese and American Cultures from the Perspective of Politeness Utterances.zip
Talk Show Differences Between China and America from the Perspec
09-14
Talk Show Differences Between China and America from the Perspective of the Form and Express.zip
Beginning SQL_ Differences Between SQL Server and…….ppt
10-27
- 带有第二个`from`子句的删除：Oracle允许在`delete`语句中使用`with`子句或第二个`from`子句，而SQL Server通常不支持这种语法。 9. 强大的新特性： 两者都有各自的强大新功能，如SQL Server的窗口函数和Oracle...
Mybatis
最新发布
庸不易的博客
11-15 807
1、定义不同：#{} 预处理：而 ${} 是直接替换2、使用不同：#{} 适用于所有类型的参数匹配；但 ${} 只适用于数值类型。3、安全性不同：#{} 性能高，并且没有安全问题；但 $ {} 存在 SQL 注入的安全问题。4、使用场景不同：当传递的是一个 SQL 关键字 的时候，只能 使用 ${}。当传递的是一个字段，总之，就是需要获取到参数类型 与 内容，只能使用 #{}。（PS：数字类型，#{} 和 ${} 都是可以使用的！5、 ${} 不能用于 模糊匹配查询；
[Docker#9] 存储卷 | Volume、Bind、Tmpfs | -v/mount | MySQL 灾难恢复 | 问题
2301_80171004的博客
11-14 790
本文介绍了 Docker 存储卷的概念、分类及其应用。从什么是存储卷、生活案例到存储卷的必要性，再到管理卷、绑定卷和临时卷的具体创建与使用方法，最后通过 MySQL 灾难恢复的实战案例，帮助读者全面掌握存储卷的运用技巧。
约束（MYSQL）
2301_80363309的博客
11-13 329
not null（非空） unique（唯一） default（默认约束，规定值） 主键约束primary key（非空且唯一） auto_increment（自增类型） 复合主键 check（保证列中值符合指定条件） foreign key（外键约束）
labview中连接sql server数据库查询语句
weixin_70653117的博客
11-13 431
日常记录
Talk about differences between linear thinking and lateral thinking
03-20
Linear thinking is a logical and sequential way of thinking that follows a straight path from point A to point B. It involves analyzing and solving problems in a step-by-step manner, using a cause-and...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值