一. Session 状态说明
可以通过v$session 视图的status列查看session 的状态。 关于该视图的使用,参考联机文档:
V$SESSION
http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/dynviews_3016.htm#REFRN30223
STATUS
|
VARCHAR2(8)
|
Status of the session:
ACTIVE - Session currently executing SQL
INACTIVE
KILLED - Session marked to be killed
CACHED - Session temporarily cached for use by Oracle*XA
SNIPED - Session inactive, waiting on the client
|
有关状态的说明:
(1)active 处于此状态的会话,表示正在执行,处于活动状态。
官方文档说明:
Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session.
(2)killed处于此状态的会话,被标注为删除,表示出现了错误,正在回滚。
当然,也是占用系统资源的。还有一点就是,killed的状态一般会持续较长时间,而且用windows下的工具pl/sql developer来kill掉,是不管用的,要用命令:alter system kill session 'sid,serial#' ;
(3)inactive 处于此状态的会话表示不是正在执行的
该状态处于等待操作(即等待需要执行的SQL语句),通常当DML语句已经完成。 但连接没有释放,这个可能是程序中没有释放,如果是使用中间件来连接的话,也可能是中间件的配置或者是bug 导致。
inactive对数据库本身没有什么影响,但是如果程序没有及时commit,那么就会造成占用过多会话。容易是DB 的session 达到极限值。
问了几个朋友,他们的做法是不处理inactive 状态的session, 如果达到了session 的最大值, 就增加processes 和 sessions 参数。 如果kill inactive session 可能会到中间件有影响。 具体中间件这块我也不太熟,等以后弄清楚了,在说。
二. 处理inactive 状态的session
在前面说不处理inactive 状态的session,但是还是有方法来解决的。 有两种方法。
2.1 在 sqlnet.ora文件中设置expire_time 参数
官网有关这个参数的说明:
http://download.oracle.com/docs/cd/B19306_01/network.102/b14213/sqlnet.htm
SQLNET.EXPIRE_TIME
Purpose
Use parameter SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.
sqlnet.expire_time 的原理:Oracle Server 发送包探测dead connection ,如果连接关闭,或者不再用,则关闭相应的server process.
Limitations on using this terminated connection detection feature are:
(1)It is not allowed on bequeathed connections.
(2)Though very small, a probe packet generates additional traffic that may downgrade network performance.
(3)Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.
Default :0
Minimum Value :0
Recommended Value :10
Example
SQLNET.EXPIRE_TIME=10
2.2 设置用户profile的idle_time 参数
之前整理的一篇有关profile的文章:
Oracle 用户 profile 属性
http://blog.csdn.net/tianlesoftware/archive/2011/03/10/6238279.aspx
注意,要启用idle_time 要先启用RESOURCE_LIMIT参数。 该参数默认是False。 官网说明如下:
RESOURCE_LIMIT
Property
|
Description
|
Parameter type
|
Boolean
|
Default value
|
false
|
Modifiable
|
ALTER SYSTEM
|
Range of values
|
true | false
|
RESOURCE_LIMIT determines whether resource limits are enforced in database profiles.
Values:
TRUE: Enables the enforcement of resource limits
FALSE:Disables the enforcement of resource limits
如下blog 在这块说的比较清楚,并提供了相关的脚本:
sqlnet.expire_time and IDLE_TIME
http://space.itpub.net/10687595/viewspace-420407
IDLE_TIME Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.
A valid database connection that is idle will respond to the probe packet causing no action on the part of the Server , whereas the resource_limit will snipe the session when idle_time is exceeded. The 'sniped' session will get disconnected when the user(or the user process) tries to communicate with the server again.
-- 通过idle_time限制session idle 时间。session idle超过设置时间,状态为sniped (v$session).,然而OS下的process并不会释放,当session(user process) 再次与server process 通讯,将关闭相应的server process.
What does 'SNIPED' status in v$session mean?
When IDLE_TIME is set in the users' profiles or the default profile. This will kill the sessions in the database (status in v$session now becomes SNIPED) and they will eventually disconnect. It does not always clean up the Unix session (LOCAL=NO sessions).
At this time all oracle resources are released but the shadow processes remains and OS resources are not released. This shadow process is still counted towards the parameters of init.ora.
This process is killed and entry from v$session is released only when user again tries to do something. Another way of forcing disconnect (if your users come in via SQL*Net) is to put the file sqlnet.ora on every client machine and include the parameter "SQLNET.EXPIRE_TIME" in it to force the close of the SQL*Net session
sqlnet.expire_time
sqlnet.expire_time actually works on a different principle and is used to detect dead connections as opposed to disconnecting(actually 'sniping') a session based on idle_time which the profile accomplishes.
But again, as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates.
修改示例:
SQL>alter profile default limit idle_time 10;
--需要重启下oracle
查询应用的连接数SQL:
/* Formatted on 2011/6/12 13:06:23 (QP5 v5.163.1008.3004) */
SELECT b.MACHINE, b.PROGRAM, COUNT (*)
FROM v$process a, v$session b
WHERE a.ADDR = b.PADDR AND b.USERNAME IS NOT NULL
GROUP BY b.MACHINE, b.PROGRAM
ORDER BY COUNT (*) DESC;
-------------------------------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
Email: dvd.dba@gmail.com
DBA1 群:62697716(满); DBA2 群:62697977(满) DBA3 群:62697850(满)
DBA 超级群:63306533(满); DBA4 群: 83829929 DBA5群: 142216823
DBA6 群:158654907 聊天 群:40132017 聊天2群:69087192
--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请
分享到:
相关推荐
但有时可能网络发生的瞬断,从而就产生了一些死进程,他们的状态为Inactive的状态。当我们用alter system kill session ‘sid,serial#’进行清除时,这些session的状态又变成了killed,这些就由Pmon进程来慢慢进行...
1、按照redo的状态可以分为current、active和inactive; 2、按照数据库归档模式可以分为归档和非归档; 3、按照脏块有没写入数据文件可以分为有和无; 4、按照损坏时数据库的状态可以分为在线和关闭;
DECODE(C.STATUS, 'INACTIVE', '不活动', 'ACTIVE', '活动') 活动情况, C.SERVER, C.SID, C.SERIAL#, C.PROGRAM 连接方式, C.LOGON_TIME FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C, v$lock ...
本文档为5GNR RRC_IDLE 和RRC_INACTIVE态描述,描述了这两个状态的主要任务,以及相应的服务类型。根据3GPP协议翻译整理而成,仅供学习。
该元素用于将Action元素定义到ActionServlet类中,它含有0到多个元素
Covers creating computers, joining computers to a domain, resetting computers, and locating computers that match certain criteria (e.g., have been inactive for a number of weeks). Chapter 9, Group ...
假如你引入之后发现页面的样式和组件都挂载了,但是console控制台会报错,说xxxx组件没有register,这个时候很有可能是你的vant插件版本有问题,重新下载一个最新的vant就可以了,现在是2.6.0版本 好,接下来继续 在...
南海热带气旋活动活跃年与不活跃年环境场的对比分析,李函卓,王磊,南海热带气旋的生成数量存在显著的年际变化。本研究对比了南海热带气旋活动活跃年份与不活跃年份对应环境背景场的特征差异:2000�
开发了Python软件包,以便使用自动语音识别引擎在语音识别框架中实现对计算机应用程序的基于上下文的命令和控制。 [ GitHub与(仅)我的GitHub赞助者捐款相匹配。] 通常,Kaldi解码图是整体的,需要昂贵的前期离线...
Inactive 和 current 状态的 redo 日志需要完成恢复 B. Online 和 Archived 日志需要实例恢复 C. 最后一个 checkpoint 之后的所有 redo 信息都要应用到数据文件 D. 所有记录在 current 状态日志中直到检查点位置...
Kaskade:[INACTIVE]简化状态管理
使用带有InfluxDB和Grafana的Telegraf获取SystemD服务的状态 1.0版中的新功能 添加了更多状态 修复了找不到systemd服务时输出为空的错误 测试并移至Python3 该python3脚本的主要目标是检查给定SystemD服务的列表并...
AFK.js 一个库,用于判断您的用户是否处于活动状态以及他们上次处于活动状态的时间。 依赖 jQuery 进行事件发射:如果您的项目尚未包含 jQuery,请使用另一个库(可能使用 EventEmitter)。安装 $ npm install --...
ime-mode : auto | active | inactive | disabled 取值: auto : 默认值。不影响IME(Input Method Editors 输入法编辑器)的状态。与不指定 ime-mode 属性时相同 active : 指定所有使用IME输入的字符。即激活本地...
Focus and blur event when an individual story becomes active or inactive. Programmatically animated scroll to any item. Items can be grouped into categories, with event dispatched as categories ...
on the network parameters, such as active/inactive probability of primary users, transmission range, and the user density. Moreover, due to the dynamic behavior of the unoccupied spectrum, the ...
enum:["active","inactive","potential"] 在 RelationshipStatusType 类中 active、inactive、potential 是 RelationshipStatusType 的子类 在RelationshipType.js 中 enum:["member","child","supporter","customer...
此外,针对多个虚拟系统同时访问一套硬件设备发生冲突的问题,设计了通用的active-inactive模型来保证虚拟系统间对硬件设备的隔离复用。实验结果表明,虚拟后的Android系统在CPU使用率上并没有增加额外的开销,在...
使用 Inactive Logout 插件可以自动终止非活跃的用户会话,从而在用户离开无人参与的会话时保护站点。 该插件非常易于配置和使用。安装并激活插件后,只需从插件设置中配置空闲超时即可。然后,现在任何长时间无...
const [state, send] = useStateMachine()({ initial: 'inactive', states: { inactive: { on: { TOGGLE: 'active' } , }, active: { on: { TOGGLE: 'inactive' }, effect() { console.log('Just into the