數據庫用戶資源管理涉及到的數據包有哪些

本篇內容介紹了“數據庫用戶資源管理涉及到的數據包有哪些”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

用戶資源管理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分配。這點是常常被大家忽略的，一定特別注意下。

資源計劃代碼如下：

EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN=>'DW_PLAN',COMMENT=>'Resource plan/method for DW');

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

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

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

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

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

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

EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

執行過程說明：

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

SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE
resource_manager_cpu_allocation      integer     1
resource_manager_plan                string      DW_PLAN
SQL>

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

sys@ORCL> show parameter resource_limit

NAMETYPEVALUE

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

resource_limitbooleanTRUE

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操作。

“數據庫用戶資源管理涉及到的數據包有哪些”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！