圖片很清楚的講明瞭 ORM 的關係結構

Resource consumer group (資源使用者組)
資源管理器將資源統一分配給資源使用者組，而不是某一個具體的會話，當會話建立時會根據自身屬性對映到不同的使用者組，並從中申請系統資源

Resource plan (資源計劃)
一個資料庫可以有多個Resource Plan,但是同一時刻只能有一個Plan狀態為Active（由初始化引數 resource_manager_plan 決定），關聯多個 Resource Plan directive

Resource plan directive (資源計劃指令)
建立一個計劃指令後就決定了系統資源如何分配給一個資源使用者組

建立一個資源計劃

1.處理pending area
所有關於 Resource Manager 元件建立和更改都必須在 Pending Area 中完成，文件說可以理解為一個“草稿區”

SQL> exec DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

PL/SQL procedure successfully completed.

SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA

PL/SQL procedure successfully completed.

2.建立資源計劃

DBMS_RESOURCE_MANAGER.CREATE_PLAN
(
plan IN VARCHAR2,
comment IN VARCHAR2,
cpu_mth IN VARCHAR2 DEFAULT NULL, -- deprecated
active_sess_pool_mth IN VARCHAR2 DEFAULT 'ACTIVE_SESS_POOL_ABSOLUTE',
parallel_degree_limit_mth IN VARCHAR2 DEFAULT
'PARALLEL_DEGREE_LIMIT_ABSOLUTE',
queueing_mth IN VARCHAR2 DEFAULT 'FIFO_TIMEOUT',
mgmt_mth IN VARCHAR2 DEFAULT 'EMPHASIS'
);

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN(plan => 'main_plan',comment => 'main database plan',sub_plan => FALSE);
END;
/
可以透過 DBA_RSRC_PLANS 進行檢視

3.建立資源使用者組

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
consumer_group IN VARCHAR2,
comment IN VARCHAR2,
cpu_mth IN VARCHAR2 DEFAULT NULL,
mgmt_mth IN VARCHAR2 DEFAULT 'ROUND-ROBIN');

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(consumer_group => 'kaifa',comment => 'kaifa',mgmt_mth => 'ROUND-ROBIN');
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(consumer_group => 'yingyong',comment => 'yingyong',mgmt_mth => 'ROUND-ROBIN');
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(consumer_group => 'lingdao',comment => 'lingdao',mgmt_mth => 'ROUND-ROBIN');
END;
/

我們為開發與應用和領導,各自建立了資源使用者組,可以透過 DBA_RSRC_CONSUMER_GROUPS 進行檢視

ROUND-ROBIN --該使用者組內各會話的CPU資源採取輪詢的分配方法

4.對映資源使用者組

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
attribute IN VARCHAR2,
value IN VARCHAR2,
consumer_group IN VARCHAR2 DEFAULT NULL);

上圖為 attribute 的內容

create user yanfa1 identified by oracle;
create user yanfa2 identified by oracle;
create user ying1 identified by oracle;
create user ying2 identified by oracle;
create user lingdao identified by oracle;

grant connect,resource,dba to yanfa1;
grant connect,resource,dba to yanfa2;
grant connect,resource,dba to ying1;
grant connect,resource,dba to ying2;
grant connect,resource,dba to lingdao;

BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(attribute => 'ORACLE_USER',value => 'yanfa1',consumer_group => 'kaifa');
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(attribute => 'ORACLE_USER',value => 'yanfa2',consumer_group => 'kaifa');
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(attribute => 'ORACLE_USER',value => 'ying1',consumer_group => 'yingyong');
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(attribute => 'ORACLE_USER',value => 'ying2',consumer_group => 'yingyong');
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(attribute => 'CLIENT_MACHINE',value => 'Lenovo-PC',consumer_group => 'lingdao');
END;
/

我們為2個研發使用者,2個應用使用者,一個領導使用者與資源使用者組做了關聯.其中領導比較特殊,領導採用機器的方式進行關聯
可以透過 DBA_RSRC_GROUP_MAPPINGS 檢視對映結果

5.建立資源計劃指令

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
plan IN VARCHAR2,
group_or_subplan IN VARCHAR2,
comment IN VARCHAR2,
cpu_p1 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p2 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p3 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p4 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p5 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p6 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p7 IN NUMBER DEFAULT NULL, -- deprecated
cpu_p8 IN NUMBER DEFAULT NULL, -- deprecated
active_sess_pool_p1 IN NUMBER DEFAULT NULL,
queueing_p1 IN NUMBER DEFAULT NULL,
parallel_degree_limit_p1 IN NUMBER DEFAULT NULL,
switch_group IN VARCHAR2 DEFAULT NULL,
switch_time IN NUMBER DEFAULT NULL,
switch_estimate IN BOOLEAN DEFAULT FALSE,
max_est_exec_time IN NUMBER DEFAULT NULL,
undo_pool IN NUMBER DEFAULT NULL,
max_idle_time IN NUMBER DEFAULT NULL,
max_idle_blocker_time IN NUMBER DEFAULT NULL,
switch_time_in_call IN NUMBER DEFAULT NULL, -- deprecated
mgmt_p1 IN NUMBER DEFAULT NULL,
mgmt_p2 IN NUMBER DEFAULT NULL,
mgmt_p3 IN NUMBER DEFAULT NULL,
mgmt_p4 IN NUMBER DEFAULT NULL,
mgmt_p5 IN NUMBER DEFAULT NULL,
mgmt_p6 IN NUMBER DEFAULT NULL,
mgmt_p7 IN NUMBER DEFAULT NULL,
mgmt_p8 IN NUMBER DEFAULT NULL,
switch_io_megabytes IN NUMBER DEFAULT NULL,
switch_io_reqs IN NUMBER DEFAULT NULL,
switch_for_call IN BOOLEAN DEFAULT NULL);

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'main_plan',
group_or_subplan => 'kaifa', --可以指定使用者組，也可以指定Sub Plan和上一個引數構成了 Plan Diretive 的唯一屬性
comment => 'Directives for yanfa',
active_sess_pool_p1 => 1, --最大活躍會話數，達到該限制後其他會話進入 Inactive session queue
queueing_p1 => 1, --在 Inactive session queue 的等待超時時間
parallel_degree_limit_p1 => 2, --最大並行度
switch_group => 'KILL_SESSION', --在滿足指定條件後，對會話所採取的操作
switch_time => 60, --執行時間限制，會話操作執行時間超過該值後，即被採取上一步的相應操作（second）
switch_estimate => FALSE, --如果設定為 TRUE,在操作執行前Oracle先估算執行時間以決定是否對其切換使用者組
max_est_exec_time => 0, --最大估算執行時間，最佳化器對操作時間進行估算，如果超過該值則直接返回“ORA-07455”
undo_pool => NULL, --uncommited undo segments 限制（KB）
max_idle_time => 300, --會話最大空閒時間
max_idle_blocker_time => 3, --Blocker會話的最大空閒時間
mgmt_p1 => 10, --CPU使用限制（Level 1）
mgmt_p2 => 0); --CPU使用限制（Level 2）
END;
/

由於研發的帳號比較嚴格,只允許一個帳號線上,只允許 2 個並行, 如果一個查詢超過了 60 秒就會被kill

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'main_plan',
group_or_subplan => 'yingyong', --可以指定使用者組，也可以指定Sub Plan，和上一個引數構成了 Plan Diretive 的唯一屬性
comment => 'Directives for yingyong',
active_sess_pool_p1 => 2, --最大活躍會話數，達到該限制後其他會話進入 Inactive session queue
queueing_p1 => 1, --在 Inactive session queue 的等待超時時間
parallel_degree_limit_p1 => 2, --最大並行度
switch_group => 'KILL_SESSION', --在滿足指定條件後，對會話所採取的操作
switch_time => 0, --執行時間限制，會話操作執行時間超過該值後，即被採取上一步的相應操作（second）
switch_estimate => FALSE, --如果設定為 TRUE,在操作執行前Oracle先估算執行時間以決定是否對其切換使用者組
max_est_exec_time => 0, --最大估算執行時間，最佳化器對操作時間進行估算，如果超過該值則直接返回“ORA-07455”
undo_pool => NULL, --uncommited undo segments 限制（KB）
max_idle_time => 300, --會話最大空閒時間
max_idle_blocker_time => 3, --Blocker會話的最大空閒時間
mgmt_p1 => 10, --CPU使用限制（Level 1）
mgmt_p2 => 0); --CPU使用限制（Level 2）
END;
/

應用的帳號許可權也不高,但可以兩個使用者同時線上.

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'main_plan',
group_or_subplan => 'lingdao', --可以指定使用者組，也可以指定Sub Plan，和上一個引數構成了 Plan Diretive 的唯一屬性
comment => 'Directives for lingdao',
active_sess_pool_p1 => 1, --最大活躍會話數，達到該限制後其他會話進入 Inactive session queue
queueing_p1 => 1 , --在 Inactive session queue 的等待超時時間
parallel_degree_limit_p1 => 200, --最大並行度
switch_group => 'KILL_SESSION', --在滿足指定條件後，對會話所採取的操作
switch_time => 0, --執行時間限制，會話操作執行時間超過該值後，即被採取上一步的相應操作（second）
switch_estimate => FALSE, --如果設定為 TRUE,在操作執行前Oracle先估算執行時間以決定是否對其切換使用者組
max_est_exec_time => 0, --最大估算執行時間，最佳化器對操作時間進行估算，如果超過該值則直接返回“ORA-07455”
undo_pool => NULL, --uncommited undo segments 限制（KB）
max_idle_time => 300, --會話最大空閒時間
max_idle_blocker_time => 3, --Blocker會話的最大空閒時間
mgmt_p1 => 0, --CPU使用限制（Level 1）
mgmt_p2 => 50); --CPU使用限制（Level 2）
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'main_plan',
group_or_subplan => 'OTHER_GROUPS', --可以指定使用者組，也可以指定Sub Plan，和上一個引數構成了 Plan Diretive 的唯一屬性
comment => 'Directives for OTHER_GROUPS',
mgmt_p1 => 20, --CPU使用限制（Level 1）
mgmt_p2 => 0); --CPU使用限制（Level 2）
END;
/

OTHER_GROUPS 建議不要進行限制,否則庫會啟不來.
領導還是比較牛的,可以給 200個並行, cpu 只可以使用 (100-40) 的一半也就是 30%這就很牛了, 大家看到 oracle 這個例項一共可使用 os 的 10 + 10 + 20+ 30 共70% cpu,留下的 30% cpu 留下給其它例項與os. OTHER_GROUPS 就是留下其它使用者,比如系統system,sys來使用的資源.

7.驗證並提交

SQL> exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA()

PL/SQL procedure successfully completed.

SQL> exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA()

PL/SQL procedure successfully completed.

我們看到驗證成功,也提交成功

8.修改引數進行測試

SQL> alter system set resource_manager_plan='main_plan' scope=both;

System altered.

重啟後進行測試

7.1 測試開發使用者只有一人active
新建立兩session
同時執行select count(*) from system.wh left join system.wh1 on wh.object_id=wh1.object_id; 大約 1億條資料

第兩個session 報
ERROR at line 1:
ORA-07454: queue timeout, 1 second(s), exceeded
後面依次進行測試即可



附（動態效能檢視及資料字典）
DBA_RSRC_CATEGORIES
DBA_RSRC_CONSUMER_GROUP_PRIVS
DBA_RSRC_CONSUMER_GROUPS
DBA_RSRC_GROUP_MAPPINGS
DBA_RSRC_IO_CALIBRATE
DBA_RSRC_MANAGER_SYSTEM_PRIVS
DBA_RSRC_MAPPING_PRIORITY
DBA_RSRC_PLAN_DIRECTIVES
DBA_RSRC_PLANS
V$RSRC_CONS_GROUP_HISTORY
V$RSRC_CONSUMER_GROUP
V$RSRC_CONSUMER_GROUP_CPU_MTH
V$RSRC_PLAN
V$RSRC_PLAN_CPU_MTH
V$RSRC_PLAN_HISTORY
V$RSRC_SESSION_INFO
V$RSRCMGRMETRIC
V$RSRCMGRMETRIC_HISTORY

Oracle Database Resource Manager

相關文章