limit active sessions

Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4

我們每個庫的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;

詳細瞭解