limit active sessions

Normal07.8 磅02falsefalsefalseMicrosoftInternetExplorer4

我们每个库的active sessions都是基本固定的，如果突然上升，一般都是有问题。结果是LOAD上升，某些参数达到临界值，如share_pool，打开文件数等等。最终DB挂掉。

也许是DB有其他问题导致active sessions上升，这个上升只是一个结果，但如果能够限制了active数量，可以防止结果恶化。

Active Session Pool

As new transactions start in a specific consumer group they take a share of the available resources. If too many transactions are active at once performance can suffer.The new Active Session Pool feature allows a maximum number of active sessions to be set for each resource consumer group. Once this figure is reached, all subsequent requests are queued until an active session completes or becomes inactive.

The active session pool currently works on the First-In-First-Out (FIFO) basis, with a timeout period. If the request times out an error is issued that can be trapped by an application. Parallel operations are counted as single sessions by resource manager.

If there are multiple resource plan directives that refer to the same consumer group, the active session pool is the sum of all the incoming values. In this case the queue timeout is the minimum of all incoming timeout values.

The active session pool is defined using the following parameters of the CREATE_PLAN_DIRECTIVE and UPDATE_PLAN_DIRECTIVE procedures in the DBMS_RESOURCE_MANAGER package:

[NEW_]ACTIVE_SESSION_POOL_P1 - Defines the active session pool limit.
* [NEW_]QUEUING_P1 - Defines the timeout period in seconds.

[@more@]

• 测试步骤（这是在10g上的测试）：
  

1、建个PLAN

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'simple_plan1',
CONSUMER_GROUP1 => 'mygroup1');
END;
2、给用户授权

BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
GRANTEE_NAME => 'test',
CONSUMER_GROUP => 'mygroup1',
GRANT_OPTION => TRUE);
END;

3、设置用户session

exec dbms_resource_manager.create_pending_area;
BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'test', 'mygroup1');
END;
/
EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
exec dbms_resource_manager.set_initial_consumer_group(user => 'test', consumer_group=>'mygroup1');

4、修改active_sess_pool_p1

exec dbms_resource_manager.create_pending_area;
EXEC DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'simple_plan1', -
GROUP_OR_SUBPLAN => 'mygroup1', NEW_active_sess_pool_p1 => 5);
EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

5、使生效

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'simple_plan1';

6、测试完成后

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ;

---

管理---------

查看active_sess_pool_p1设置值

SQL> select plan,active_sess_pool_p1 from DBA_RSRC_PLAN_DIRECTIVES;

PLAN ACTIVE_SESS_POOL_P1
------------------------------ -------------------
SYSTEM_PLAN
SYSTEM_PLAN
SYSTEM_PLAN
INTERNAL_QUIESCE
INTERNAL_QUIESCE 0
INTERNAL_PLAN
SIMPLE_PLAN1
SIMPLE_PLAN1
SIMPLE_PLAN1 5

9 rows selected.

查看达到限制的次数及被kill的次数。

SQL> select name,ACTIVE_SESSIONS,ACTIVE_SESSION_LIMIT_HIT,QUEUE_LENGTH,ACTIVE_SESSIONS_KILLED from V$RSRC_CONSUMER_GROUP;

NAME ACTIVE_SESSIONS ACTIVE_SESSION_LIMIT_HIT QUEUE_LENGTH ACTIVE_SESSIONS_KILLED
-------------------------------- --------------- ------------------------ ------------ ----------------------
SYS_GROUP 1 0 0 0
OTHER_GROUPS 0 0 0 0
MYGROUP1 2 19 0 0

这里19次达到了5个以上active session,我们可以监控QUEUE_LENGTH，当session在队列里时，说明达到limit并排队了，判断如果是正常session,此时可以很方便地取消限制

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ;
然后再修改limit值。

SQL> select sequence# seq, name, cpu_wait_time, cpu_waits,consumed_cpu_time from V$RSRC_CONS_GROUP_HISTORY;

SELECT sequence# seq, name plan_name,
to_char(start_time, 'DD-MON-YY HH24:MM') start_time,
to_char(end_time, 'DD-MON-YY HH24:MM') end_time, window_name
FROM v$rsrc_plan_history;

SELECT se.sid sess_id, co.name consumer_group,
se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
FROM v$rsrc_session_info se, v$rsrc_consumer_group co
WHERE se.current_consumer_group_id = co.id;

详细了解

http://www.oracle-base.com/articles/9i/ResourceManagerEnhancements9i.php#ActiveSessionPool

http://download.oracle.com/docs/cd/B14117_01/server.101/b10739/dbrm.htm#i1008675

http://www.filibeto.org/sun/lib/nonsun/oracle/11.1.0.6.0/B28359_01/server.111/b28310/dbrm009.htm#CHDEGIIA

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

转载于:http://blog.itpub.net/668365/viewspace-1029729/