oracle效能管理器(oracle database resource manager)－潛水珽的異想世界

在過去,資料庫對於系統中使用者的資源管理,一般都是使用"Profile"的觀念來進行管理,
但這個觀念常常會遇到一個問題:如果我的應用程式都使用同一個帳號(一般開發也都是這樣在開發),
如果同時要跑日常作業與批次作業的話(或是某些SQL執行時間太久),
這樣的Profile觀念就沒有辦法派上用場,因此,在oracle9i時,
Oracle導入了新的資源管理機制 - Oracle database resource manager

這篇文章將以目前筆者工作的環境(oracle 9i)為基礎來說明這個機制的設定方式,
至於在Oracle 10g、Oracle 11g如何設定,我想基本上應該是差不多的...

用途:
Managing Resource在實際用途中可以解決以下一些問題：

1.自動中止一些long query，這點在壓力大的OLTP系統中非常有用。

2.自動分配資源，如把CPU資源劃分給不同用戶，如AP_user占80%，batch_user2占20%，
甚至可以指定session不能產生超過一定數量的UNDO。

3.限制用戶的max session，idle time等，如idle time超過一定時間的session自動中斷。

基礎概念:
1.Resource consumer group
授權給user account用,換句話說,就是用來將使用者分類的物件。

2.Resource Plan Directives
用來控制使用者與系統資源的物件。

3.Resource plan
用來設定系統資源分配量的物件。

上面聽的"灰綠綠"對吧,來設定一次就會比較清楚啦...

規劃:
在開始使用resource manager時,我們必須先規劃相關的resouce plan。
這邊我們假設有個OLTP環境,平常需提供USER線上存取使用,而執行過長或是batch job等容易消耗系統
大量Resource的作業,將其resouce給保留給user線上查詢使用.

我們這樣設計:
線上使用者群組:NTUH_AP_USER_GROUP 擁有85% CPU的使用量,超過2分鐘(這個例子是用2分鐘)
則自動切換到BATCH_USER_GROUP。

排程使用者群組:BATCH_USER_GROUP 擁有15% CPU的使用量。

整個resource_plan如下圖所示:

開始建立Resource_consumer_group

語法命令:
--
-- NTUH_AP_USER_GROUP (Consumer Group)
--
begin
sys.dbms_resource_manager.clear_pending_area();

sys.dbms_resource_manager.create_pending_area();

sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'NTUH_AP_USER_GROUP'
     ,comment                      => 'ntuh application user group');

sys.dbms_resource_manager.submit_pending_area();
end;
/

begin
sys.dbms_resource_manager.clear_pending_area();

sys.dbms_resource_manager.create_pending_area();

sys.dbms_resource_manager_privs.grant_switch_consumer_group (
      grantee_name                 => 'TEST'
     ,consumer_group               => 'NTUH_AP_USER_GROUP'
     ,grant_option                 => FALSE);

sys.dbms_resource_manager.set_initial_consumer_group (
      user                         => 'TEST'
     ,consumer_group               => 'NTUH_AP_USER_GROUP');
sys.dbms_resource_manager.submit_pending_area();
end;
/

開始設定Resource_plan:

分配相關Resource_consumer_Group對系統資源的使用量。

命令:
--
-- APPLICATION_PLAN (Resource Plan)
--
begin
sys.dbms_resource_manager.clear_pending_area();

sys.dbms_resource_manager.create_pending_area();

sys.dbms_resource_manager.create_plan (
      plan                         => 'APPLICATION_PLAN'
     ,cpu_mth                      => 'EMPHASIS'
     ,active_sess_pool_mth         => 'ACTIVE_SESS_POOL_ABSOLUTE'
     ,parallel_degree_limit_mth    => 'PARALLEL_DEGREE_LIMIT_ABSOLUTE'
     ,queueing_mth                 => 'FIFO_TIMEOUT');

sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'BATCH_USER_GROUP'
     ,comment                      => 'over run 10 mins sql statement');

sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'APPLICATION_PLAN'
     ,group_or_subplan             => 'BATCH_USER_GROUP'
     ,cpu_p2                       => 15
     ,switch_estimate              => FALSE );

sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'NTUH_AP_USER_GROUP'
     ,comment                      => 'ntuh application user group');

sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'APPLICATION_PLAN'
     ,group_or_subplan             => 'NTUH_AP_USER_GROUP'
     ,cpu_p1                       => 85
     ,switch_group                 => 'BATCH_USER_GROUP'
     ,switch_time                  => 120
     ,switch_estimate              => FALSE
     ,comment                      => 'running over 10mins switch batch_user_group' );

sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'APPLICATION_PLAN'
     ,group_or_subplan             => 'OTHER_GROUPS'
     ,switch_estimate              => FALSE );

sys.dbms_resource_manager.submit_pending_area();
end;
/

我們建立一個新的user account叫做TEST,然後將該user的resource_group設定成"NTUH_AP_USER_GROUP",
其中:
1.admin的選項是用來決定該使用者有沒有權限可以執行grant or revoke resource_manager_privs package。

2.init的選項是用來設定該user init session時的Resource_group
(因為一個account可以同時賦予多個Resource_group)。

3.switch的選項可以賦予該user有切換到其他resource_group的權限。

語法:
BEGIN
SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
   'TEST', 'NTUH_AP_USER_GROUP', false);
SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/

BEGIN
SYS.DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP (
   'TEST', 'NTUH_AP_USER_GROUP');
END;
/

BEGIN
SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
   'TEST', 'BATCH_USER_GROUP', false);
SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/

啟動resource_plan:
1.是設定在init(SPFILE) parameter resource_manager_plan參數中
2.啟動資料庫後透過alter system set resource_manager_plan='resource_plan_name';
3.停用resource_plan則使用alter system set resource_manager_plan='';

實際測試:
我們寫了一個一個2000萬*2000萬筆資料的笛卡爾乘積的查詢,估計這個查詢如果沒有經過"人為"的中斷,
應該會跑上好幾個小時吧...

select * from table_a a,table_b b;

然後我們用DBA的帳號進系統查詢V$SESSION動態視圖,
我們只把TEST的這個USER Account session 資訊給show出來...

select resource_consumer_group ,* from v$SESSION
where user_name='TEST';

一開始是NTUH_AP_USER_GROUP,如下圖: