http://blog.csdn.net/aaaaaaaa2000/article/details/7392431
在这个里面看到885的进程阻塞了很多其他进程,但是885进程的状态竟然是inactive session的,那这个inactive session 到底是干啥的?这个状态什么含义?
http://blog.csdn.net/tianlesoftware/article/details/6539297--这个文件里有对inactive
“
当设置了resource_limit=true 。通过idle_time限制session idle 时间。session idle超过设置时间,状态为sniped (v$session).,然而OS下的process并不会释放,当session(user process) 再次与server process 通讯,将关闭相应的server process.
sqlnet.expire_time 的原理不一样,Oracle Server 发送包探测dead connection ,如果连接关闭,或者不再用,则关闭相应的server process.
以上两者组合使用,减少server process,防止process超过init$ORACLE_SID极限值。
”有关状态的说明:
(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
在前面说不处理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.-----对bequeathed connections说明
Simply an Oracle client-server connection that usesIPC(Inter Process Communication) and
notTCP(network connection established via the Listener).
In other words, the client and server process communicates directly (via IPC) with one another. There's no network layer in between that serves as the communication layer.
Obviously, this also means that both client process and server process run on the same kernel - as IPC is a communication method that the kernel provides for processes it is executing, to communicate with
one another.
(是否可以理解为client与server是在同一机器上)
(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----(分钟)
修改完之后需要reload监听
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
分享到:
相关推荐
本文档为5GNR RRC_IDLE 和RRC_INACTIVE态描述,描述了这两个状态的主要任务,以及相应的服务类型。根据3GPP协议翻译整理而成,仅供学习。
a vb6 code about howto_close_if_inactive
INACTIVE-httpmzl.laghe-archive-软件发布机制的流程文档.zip
使用 Inactive Logout 插件可以自动终止非活跃的用户会话,从而在用户离开无人参与的会话时保护站点。 该插件非常易于配置和使用。安装并激活插件后,只需从插件设置中配置空闲超时即可。然后,现在任何长时间无...
An add-in that helps you hide ’inactive’ pieces of your code in include files(42KB)
nadeko, [INACTIVE] 常数时间 Rust 实验 nadeko是一个实验性的语法扩展,它将函数转换为amd64汇编代码。比如,#[const_time]pub fn add3(a: u8, b: u8, c: u8) -> u8 { return a + b +
suruga, 在 Rust 中,[INACTIVE] TLS 1.2实现 suruga是 TLS 1.2的Rust 实现。它目前实现了 TLS 1.2的核心部分,NIST的P-256 ECDHE 和 chacha20-poly1305.用法extern crate suruga;use
CSS-Buddy, 在图层上,[INACTIVE] 一个草图 3插件,允许你使用 CSS 允许你在图层上使用CSS的草图 3插件。v0.0.3注意:这个插件还处于开发早期阶段。 点击这里 riiiight查看演示( gif在这一页 bogged ) 。用法安装...
不活动 - 没有进展
无效用户动作可以对GitHub组织运行的GitHub动作,以生成有关给定时间段内用户活动的报告。 这对于检测不活动的用户很有用,以便可以收回许可证。加工此操作将对您的组织执行许多API请求,以生成用于识别用户活动的...
TiCl4促进的磺酰胺与醚的N-烷基化反应,陈嘉燕,曾伟,磺酰胺类衍生物是一类具有生物活性的化合物,例如抗细菌、抗癌、抗病毒活性等,目前有关利用磺酰氯和胺,磺酰胺和醇等合成磺酰胺�
错别字 帮帮我 :)开始$ npm install$ npm start更多想法真正的魔力始于typos/scripts/server/gold.js 那就是我要构建用于搜索的API调用层的地方。 目前仅是一个文件,但是方法将被扩展并链接到数据库以保存搜索...
无效CSS 在某些情况下,CSS属性对元素没有任何影响。 这可能取决于当前对其应用了哪些其他属性,元素的类型,其父元素或该属性所包含CSS规则。 一个简单但常见的示例是尝试在内联级别元素(如上设置width 。...
Kaskade:[INACTIVE]简化状态管理
在活动选项卡上开始播放音频时,自动禁用非活动的可听标签 厌倦了多个标签同时播放音频?借助Smart Mute恢复对音频体验的控制,并一次聆听一个标签页的音频 ----------------------------注意:此扩展程序无法通过短...
语言:English (United States) 此扩展程序使非活动标签静音 此扩展程序自动使不活动的...在此处获取专业版:https://chrome.google.com/webstore/detail/really-mute-inactive-tabs/pjpajaodlaollgnoonbnlcpopkfcdajk
南海热带气旋活动活跃年与不活跃年环境场的对比分析,李函卓,王磊,南海热带气旋的生成数量存在显著的年际变化。本研究对比了南海热带气旋活动活跃年份与不活跃年份对应环境背景场的特征差异:2000�
Hydrotris(1,2,4-triazolyl)borato Complexes with the Main Group Elements Ca, Sr, and Pb - Unexpectedly Bent ML2 Structures and a Stereochemically Inactive Lone Pair at Lead(II) Hydrotris(1,2,4-...
不再维护ACL检查器这是一个 NodeJS 模块,允许您设置角色/资源/权限,然后允许您检查角色是否对资源具有特定权限。安装您可以使用 npm 安装它: npm install acl-checker快速指南您需要做的第一件事是创建 ACL 类的...