Oracle session出现大量的inactive
一.官网说明
1.1 processes
11gR2 的文档：
|
Property |
Description |
|
Parameter type |
Integer |
|
Default value |
100 |
|
Modifiable |
No |
|
Range of values |
6 to operating system dependent |
|
Basic |
Yes |
|
Oracle RAC |
Multiple instances can have different values. |
PROCESSES specifies the maximum numberof operating system user processes that can simultaneously connect to Oracle.Its value should allow for all background processes such as locks, job queueprocesses, and parallel execution processes.
The defaultvalues of the SESSIONS and TRANSACTIONS parameters arederived from this parameter. Therefore, if you change the valueof PROCESSES, you should evaluate whether to adjust the values of thosederived parameters.
PROCESSES指定可同时连接到Oracle操作系统用户进程的最大数目。其值应允许所有后台进程，如锁，作业队列进程和并行执行的过程。
在会话和事务参数的默认值是从这个参数的。因此，如果你改变流程的价值，你应该评估是否调整这些衍生参数的值。
http://download.oracle.com/docs/cd/E11882_01/server.112/e25513/initparams198.htm#REFRN10175
1.2 sessions
11gR1:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams220.htm#REFRN10197
|
Property |
Description |
|
Parameter type |
Integer |
|
Default value |
Derived: (1.1 * PROCESSES) + 5 |
|
Modifiable |
No |
|
Range of values |
1 to 231 |
|
Basic |
Yes |
11gR2
|
Property |
Description |
|
Parameter type |
Integer |
|
Default value |
Derived: (1.5 * PROCESSES) + 22 |
|
Modifiable |
No |
|
Range of values |
1 to 231 |
|
Basic |
Yes |
这里要注意的是到了11gR2里，sessions 的默认值计算方式变了。 该值的计算是针对 dedicate 模式的。
SESSIONS specifies the maximum number of sessions that can becreated in the system. Because every login requires a session, this parametereffectively determines the maximum number of concurrent users in the system.You should always set this parameter explicitly to a value equivalent to yourestimate of the maximum number of concurrent users, plus the number ofbackground processes, plus approximately 10% for recursive sessions.
Oracle uses thedefault value of this parameter as its minimum. Values between 1 and thedefault do not trigger errors, but Oracle ignores them and uses the defaultinstead.
The defaultvalues of the ENQUEUE_RESOURCES and TRANSACTIONS parametersare derived from SESSIONS. Therefore, if you increase the valueof SESSIONS, you should consider whether to adjust the valuesof ENQUEUE_RESOURCES and TRANSACTIONS as well. (Notethat ENQUEUE_RESOURCES is obsolete as of Oracle Database 10g release2 (10.2).)
In a shared server environment, the value of PROCESSES canbe quite small. Therefore, Oracle recommends that youadjust the value of SESSIONS to approximately 1.1 * total numberof connections.
SESSIONS指定可在系统中创建的会话的最大数目。因为每次登录需要一个会话，这个参数有效决定了系统的并发用户的最大数量。你restimate并发用户的最大数量，再加上后台进程的数量，你应该始终明确设置此参数值当量，加上递归会话约10％。
1.3 transactions
11gR2
http://download.oracle.com/docs/cd/E11882_01/server.112/e25513/initparams258.htm#REFRN10222
|
Property |
Description |
|
Parameter type |
Integer |
|
Default value |
Derived: (1.1 * SESSIONS) |
|
Modifiable |
No |
|
Range of values |
4 to 232 |
|
Oracle RAC |
Multiple instances can have different values. |
TRANSACTIONS specifieshow many rollback segments to onlinewhen UNDO_MANAGEMENT = MANUAL. The maximum number of concurrenttransactions is now restricted by undo tablespace size(UNDO_MANAGEMENT = AUTO) or the number of online rollback segments(UNDO_MANAGEMENT = MANUAL).
二测试
select * from v$version
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
select count(*) from v$process;
select count(*) from v$session;
select count(*) from v$session where status='ACTIVE';
select username,count(username) from v$session where username is not null group by username;
查询oracle中的配置值
select num,name,type,value,display_value from v$parameter where name in ('processes','sessions')
SESSIONS=(1.5* PROCESSES) + 22
三 出现大量session是INACTIVE
1查询当前的连接数，状态
select b.MACHINE, b.PROGRAM , status ,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, status order by count(*) desc;
2 查询是否有死锁
select * from v$locked_object;
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid
Status:
l Achtive：正执行SQL语句(waiting for/using a resource)
l Inactive：等待操作(即等待需要执行的SQL语句)
l Killed：被标注为删除
Inactive对数据库本身没有什么影响，但是程序如果没有及时commit，那么就会造成占用过多回话，有两种方式解决
1修改sqlnet.ora文件，新增expire_time=x(minutes)
2 通过alter profile default limit idle_time=10; restart oracle service
show parameter resource_limit
alter system set resource_limit=true scope=both;
select username,profile from DBA_USERS
select distinct(profile) from dba_profiles;
SELECT name, value FROM gv$parameter WHERE name = 'resource_limit';
alter user system profile PROFILE9959;--new profile
select sid,serial#,paddr,username,status from v$session where status = 'SNIPED';
select * from dba_profiles--查看详细的profile的配置信息
select * from dba_profiles where profile='USER_PROFILE_RCS2';
创建function
CREATE OR REPLACE FUNCTION SYS.verify_function_pro_rcs
创建profile
SQL> CREATE PROFILE "USER_PROFILE_RCS2" LIMIT
SESSIONS_PER_USER UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 14
PASSWORD_REUSE_MAX 12
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION verify_function_pro_rcs;
Profile created.
Test
SQL> show user;
USER is "SYS"
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------------------------------------ --------------------
2 4119682648 PDB$SEED READ ONLY
3 2324297228 PDBORCL READ WRITE
4 397367019 YHQ_PDB READ WRITE
SQL> alter session set container=pdborcl;
Session altered.
SQL> alter system set resource_limit=true scope=both;
System altered.
SQL> create profile test_idletime limit idle_time 10;
Profile created.
SQL> create user idle_time_user identified by idle_time_user profile test_idletime;
User created.
SQL> grant resource,connect to idle_time_user;
Grant succeeded.
3 另一种解决方法
select A.SID,B.SPID,A.SERIAL#,a.lockwait,A.USERNAME,A.OSUSER,a.logon_time,a.last_call_et/3600 LAST_HOUR,A.STATUS,
'orakill '||sid||' '||spid HOST_COMMAND,
'alter system kill session '''||A.sid||','||A.SERIAL#||'''' SQL_COMMAND
from v$session A,V$PROCESS B where A.PADDR=B.ADDR AND SID>6
--sid的取值？？
一个自动杀 的job
CREATE OR REPLACE PROCEDURE "KILL_SESSION" AS
v_sid number;
v_serial number;
killer varchar2(1000);
CURSOR cursor_session_info is select sid,serial# from v$session where type!='BACKGROUND' and status='INACTIVE' and last_call_et>2700 and username='ICWEB' and machine='orc';
BEGIN
open cursor_session_info;
loop
fetch cursor_session_info into v_sid,v_serial;
exit when cursor_session_info%notfound;
killer:='alter system disconnect session '''||v_sid||','||v_serial||''' post_transaction immediate';
execute immediate killer;
end loop;
dbms_output.PUT_LINE(cursor_session_info%rowcount||' users with idle_time>2700s have been killed!');
close cursor_session_info;
END;
/
这样做其实还是治标不治本,最好能够解决连接池自动释放idle进程的问题
--登录前端应用服务器查看ip连接数
[root@rac1 ~]# netstat -ntu | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n
[root@rac1 ~]# netstat -ntu |grep 8080
[root@rac1 ~]# netstat -ntu |grep 8080|grep TIME_WAIT |wc -l
