oracle資源管理器（一）

使用者資源管理DBMS_RESOURCE_MANAGER

使用者資源管理涉及到的資料包主要有兩個：DBMS_RESOURCE_MANAGER和DBMS_RESOURCE_MANAGER _PRIVS。

其中包DBMS_RESOURCE_MANAGER主要是用於建立資源計劃，建立資源使用者組，建立資源分配方法等使用者資源相關的管理；而DBMS_RESOURCE_MANAGER _PRIVS的主要用途是進行使用者資源管理的許可權控制。

一、前言

資源管理器有三個部件組成：資源使用者組（Resource consumer group ）、資源規劃（Resource plan ）、資源分配方法（Resource allocation method）及資源計劃目錄(Resource plan directives) 。它們的功能如下：

部件說明

資源使用者組: 根據資料庫資源處理需求，將使用者會話分成組

資源規劃: 指定哪些資源分配給資源使用者的命令

資源分配方法: 資料庫資源管理器分配特殊資源時採用的方法，由資源使用者組和資源規劃來使用。

資源規劃命令: 管理員使用這些命令將資源使用者組與特殊規劃連線起來，並在資源使用者組之間分配資源。

資料庫資源管理器可以完成：

1．確保某些使用者處理少量的資源，不考慮對系統的載入和使用者的數量。

2．按比例將CPU時間分配給不同的使用者和程式，分配有效的處理資源。

3．限制一組使用者可以使用的並行度。

4．對例項進行配置，使其能使用特殊的資源分配方法。例如，DBA不用關閉資料庫例項就可以動態地改變這些配置方法。

二、概述

使用者資源管理涉及到的資料包主要有兩個：DBMS_RESOURCE_MANAGER和DBMS_RESOURCE_MANAGER _PRIVS。

其中包DBMS_RESOURCE_MANAGER主要是用於建立資源計劃，建立資源使用者組，建立資源分配方法等使用者資源相關的管理；而DBMS_RESOURCE_MANAGER _PRIVS的主要用途是進行使用者資源管理的許可權控制。

對於一個簡單的使用者資源管理計劃來說，僅僅使用DBMS_RESOURCE_MANAGER包就足夠了，所以下面僅僅對DBMS_RESOURCE_MANAGER的使用進行詳細的說明。

三、舉例

下面通過一個簡單的使用者資源管理計劃的建立來讓大家熟悉下DBMS_RESOURCE_MANAGER包的使用方法。

（1）使用者資源管理示意圖

DW-PLAN DB_DEV OTHER_GROUPS TMP_DATA CPU 80% LEVEL 1 CPU 100% LEVEL 2 CPU 20% LEVEL 1

上面就是一個資料倉儲的簡單的使用者資源管理計劃示意圖。資源使用者管理計劃的名字是DW_PLAN，在這個資源管理計劃下有三個資源使用者組，分別是DB_DEV,TMP_DATA,OTHER_GROUPS。

這個資源管理計劃裡面僅僅包括對CPU的控制，其中使用者組DB_DEV可以獲得的資源CPU 80% LEVEL 1，使用者組TMP_DATA可以獲得的資源是CPU 20% LEVEL 1，而使用者組OTHER_GROUPS可以獲得的資源是CPU 100% LEVEL 2。

CPU的百分比很好理解的，比如說DB_DEV可以獲得80%的CPU資源，他的級別是LEVEL 1。關於這個LEVEL是很讓人迷惑的，其實LEVEL就是資源獲取的優先順序別，拿上面的例子來說，假設DB_DEV和TMP_DATA分別獲得了系統的80%和20%的CPU資源，那麼作為下一級LEVEL 2的OTHER_GROUPS將不會獲得任何CPU的資源。當DB_DEV和TMP_DATA分別獲得了系統的40%和10%的CPU資源，那麼作為下一級LEVEL 2的OTHER_GROUPS將會獲得50%的CPU資源。

換句話說，LEVEL 2所能獲得的全部資源就是LEVEL 1所未能使用的那部分資源。

優先順序：LEVEL1 > LEVEL 2 > LEVEL 3……LEVEL N-1>LEVEL N

需要強調的一點是OTHER_GROUPS這個資源使用者組。任何一個資源計劃必須要包括這個OTHER_GROUPS使用者組，如果你的資源計劃沒有包括這個使用者組，那麼將會得到一個ORA-07453的錯誤，要求你必須新增此使用者組。這個使用者組的作用就是作為一個後選項，當一個沒有匹配到任何資源使用者組的SESSION連線到資料庫的時候會自動的匹配到OTHER_GROUPS下面，按照OTHER_GROUPS的資源限定執行SQL。

另外一個需要強調的是使用者資源限定的生效條件。拿上面的例子來說，當系統的CPU使用率沒有達到100%的時候，DW_PLAN這個資源計劃並不會生效，各個資源使用者也不會按照限定來分配CPU的使用。僅僅當CPU的使用率為100%的時候，資源計劃才可以發揮功效，來限制各個資源使用者組的CPU分配。這點是常常被大家忽略的，一定特別注意下。

資源計劃程式碼如下：

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN=>'DW_PLAN',COMMENT=>

'Resource plan/method for DW');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP=>

'DB_DEV',COMMENT => 'Resource plan user group for DB_DEV');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP=> 'TMP_DATA',COMMENT => 'Resource plan user group for TMP_DATA');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP=> 'OTHER_GROUPS',COMMENT => 'Resource plan user group for OTHER_GROUPS');

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN=>'DW_PLAN',GROUP_OR_SUBPLAN => ' DB_DEV ',COMMENT => ' DB_DEV ',CPU_P1 => 80);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN=>'DW_PLAN',GROUP_OR_SUBPLAN => ' TMP_DATA ',COMMENT => ' TMP_DATA ',CPU_P1 => 20);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN=>'DW_PLAN',GROUP_OR_SUBPLAN => ' OTHER_GROUPS ',COMMENT => ' OTHER_GROUPS ',CPU_P1 => 0, CPU_P1 => 100);

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

END;

過程說明：

1．DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

建立一掛起區域，每次對資源計劃進行操作之前都必須要執行掛起操作，申請一塊記憶體。

2，DBMS_RESOURCE_MANAGER.CREATE_PLAN

建立一個資源計劃，引數PLAN表示資源計劃的名字，COMMENT為該資源計劃的註釋資訊

3，DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP

建立一個資源使用者組，引數CONSUMER_GROUP為資源使用者組名字，COMMENT為該資源使用者組的註釋資訊

4，DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE

建立一個資源分配方法，引數PLAN為資源計劃的名字，,GROUP_OR_SUBPLAN為下層資源使用者的名字，COMMENT為資源分配方法的註釋資訊，CPU_P1表示該資源使用者組在LEVEL上的CPU分配方案。

5，DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA()

驗證使用者資源計劃的有效性

6，DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA()

提交使用者資源計劃

（2）當建立好使用者資源計劃之後，就需要將特定的使用者與特定的資源計劃相關聯。

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (DBMS_RESOURCE_MANAGER.ORACLE_USER,'CNODS', ' DB_DEV ')

通過這條命令就可以將使用者CNODS分配到資源組DB_DEV下。

（3）關聯好之後就可以將新建立的使用者資源管理置為有效

dbms_resource_manager.switch_plan( plan_name => 'DW_PLAN', sid => 'ORCL' )

通過這條命令將當前的使用者資源管理計劃設定為DW_PLAN

（4）接下來檢查初始化引數resource_limit，將其設定為TRUE

sys@ORCL> show parameter resource_limit

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

resource_limit boolean TRUE

sys@ORCL> alter system set resource_limit=true scope=both;

系統已更改。

經過上面的步驟，就可以使用新生成的使用者資源計劃了。

上面僅僅介紹了關於CPU的資源管理，其實使用者資源管理包還可以對相同使用者的活動SESSION數量，SESSION空閒時間，UNDO空間進行管理。下面就分別詳細的說明下各個管理的使用方法和注意事項。

四、ACTIVE_SESS_POOL_P1

這個引數控制的是資源使用者組內的使用者同時可以執行的最大的活動SESSION的數量。這裡值得強調的是ACTIVE_SESS_POOL_P1並不限制那些非活動的SESSION，僅僅對那些活動的SESSION有限制，因為一般說來只有那些活動的SESSION才會消耗系統的資源。

DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(PLAN => 'dw_plan',GROUP_OR_SUBPLAN => 'TEST_GROUP',NEW_ACTIVE_SESS_POOL_P1 => 1);

上面這條語句表示資源使用者組TEST_GROUP內的使用者同時僅能存在一個活動的事務。

舉例：

假設TEST使用者的資源組是TEST_GROUP

SESSION1：

test@ORCL> CONN TEST/TEST

test@ORCL> SELECT COUNT（*） FROM DBA_OBJECTS,DBA_OBJECTS;

SESSION2:

test@ORCL> CONNN TEST/TEST

可以發現此時SESSION2被阻塞了，僅僅當SESSION1的SQL執行完畢，變成INACTIVE狀態後，SESSION2才可以連線到資料庫。那麼這這個時候就有兩個SESSION連線到資料庫，但當一個執行SQL的時候，另一個SESSION會立刻被掛起。

對我們資料倉儲來說，這個引數的意義比較重大。因為我們要控制的是系統的資源，而系統的絕大部分資源被那些ACTIVE的SESSION所佔據著，那麼只要限定了併發的ACTIVE的SESSION數量，系統的資源就得到了有效的控制。

五、QUEUEING_P1

這個引數控制的是SESSION的等待時候，一個SESSION被放到等待佇列中，那麼正常的情況下，他會一直等待所需要的資源，當設定了QUEUEING_P1以後，當超過了QUEUEING_P1指定的時間後，系統會報錯誤ORA-07454，提醒已經等待超時。

DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(PLAN => 'dw_plan',GROUP_OR_SUBPLAN => ' TEST_GROUP ',NEW_ACTIVE_SESS_POOL_P1 => 1,NEW_QUEUEING_P1 => 10);

SESSION1

CONN TEST/TEST

SESSION2:

CONNN TEST/TEST

SESSION1

test@ORCL> SELECT COUNT（*） FROM DBA_OBJECTS,DBA_OBJECTS;

SESSION2:

test@ORCL> select sysdate from dual;

select sysdate from dual

*

第 1 行出現錯誤:

ORA-07454: 佇列超時, 已超過 10 秒

這個引數的用途很象SELECT * FROM XXX FOR UPDATE,避免使用者一直等待。

六、PARALLEL_DEGREE_LIMIT_P1

這個引數就沒什麼好說的了，是限制使用者執行SQL時候的並行度，就不舉例說明了，呼叫方法如下：

DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(PLAN => 'dw_plan',GROUP_OR_SUBPLAN => ' TEST_GROUP ',NEW_ACTIVE_SESS_POOL_P1 => 1,NEW_QUEUEING_P1 => 10，NEW_PARALLEL_DEGREE_LIMIT_P1 => 2);

七、SWITCH_GROUP，NEW_SWITCH_TIME，NEW_SWITCH_ESTIMATE

這個三個引數之所以一起介紹，是因為他們共同的合作完成一項很重要的功能。

DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(PLAN => 'dw_plan',GROUP_OR_SUBPLAN => 'DW_TEST', NEW_SWITCH_GROUP => ‘dw_sys’, NEW_SWITCH_TIME => 300, NEW_SWITCH_ESTIMATE => ‘true’);

上面這個條語句完成的功能是，如果一個SESSION的資源使用者組是屬於DW_TEST，他如果執行一個很複雜的SQL，系統估計這個條SQL的執行時間會超過300秒的時候，自動把這個SESSION切換到使用者組dw_sys上，這樣該SESSION會獲得更多的資源，更快的執行完成這個SQL。但是執行完成之後，這個SESSION就在以後的時間保留在切換的組中。

另外一個和NEW_SWITCH_TIME對立的引數是NEW_SWITCH_TIME_IN_CALL，如果使用了這個引數，那麼在執行完成後，切換回原來的資源使用者組。

八、MAX_EST_EXEC_TIME

這個引數控制一個事務最大的執行時間。如果一個事務很複雜，執行時間很長，那麼他就不會被系統執行。

九、UNDO_POOL

這個引數很讓人迷惑，開始的時候我以為這個引數控制的一個使用者在回滾表空間內最大的UNDO段的大小，因為UNDO段的塊是迴圈使用的，所以只要單個事務產生的回滾資訊不超過這個最大值就應該沒有問題。

可實際上經過測試，這個引數控制的是個總量，即該使用者產生的總的UNDO數量不能超過這個值，如果超過這個值就會報錯。注意是總的UNDO，是個累加的值。

十、MAX_IDLE_TIME

這個引數控制的一個使用者的SESSION最大的空閒時間，如果空閒時間超過這個限定，該SESSION會被終止。

十一、MAX_IDLE_BLOCKER_TIME

這個引數是控制那些佔有資源的空閒SEESION的，當一個佔有資源的SESSION空閒時間超過了MAX_IDLE_BLOCKER_TIME的限制，那麼系統會要求他釋放所佔有的資源，一般就是ROLLBACK操作。

冰凍奶茶

12月3日