Кто-нибудь использует dbms_resource_manager? : Oracle

OlegON · █ 14.03.2010 22:15

Сейчас по причине вмешательства в Супермажную базу писателя запросов сомнительного характера, использую менеджер ресурсов. Достаточно замечательная вещь, хотя и не без глючков, но порой просто необходимая.
Суть в возможности разделить пользователей на группы и раздавать им ресурсы. Т.е. если ваш сервер не резиновый или самописатели любят лочить таблички, то использование менеджера просто необходимо. Раньше я занимался обычным протыкиванием галочек, но после того, как юные пионеры обновили систему мне на сервере, включая Java-машину, dbconsole у меня сдохла и все выполняю скриптиками, что дает некоторый результат в понимании происходящего.
Что можно сделать. Например, особо заигрывающихся тяжелыми отчетами людей можно выделить в отдельную группу и выделить им не более 3х одновременно выполняющихся сессий, причем выделять можно и не заранее, как выполнил запрос потяжелее или даже ориентировочно долгий - перемещаем его в группу HARD_WORK (например). Если проца всего два, то можно выделить приоритетные расчеты, которые будут получать процессорное время до остальных. Поскольку у меня в базе косячат аксессом, а он по непонятной причине лочит таблички и ничего не делает, то по залочке более 30 секунд и бездействию, сессия прибивается (есть варианты, но мне нравится более жестокий, как повод кривописателю не мешать людям работать). Ограничено максимальное время бездействия, не столько по нехватке ресурсов, сколько просто, чтобы не болтались бесхозные сессии. Ограничено максимальное время выполнения запроса. Никакая дура, которая найдет комбинацию параметров запроса, уходящего в вечность, не будет занимать сутками процы и винты, запуская и прибивая отчет. Почтовик работает, когда другие отдыхают, т.е. он в самом конце очереди и не перебивает оперативную работу.
Это я вкратце описал возможности менеджера ресурсов. Думаю, что стоит к нему присмотреться. Могу что-то и подсказать.

OlegON · █ 14.03.2010 22:17

Например, список планов и директив:

Код:

select PLAN ,GROUP_OR_SUBPLAN,SWITCH_ESTIMATE,MAX_EST_EXEC_TIME from DBA_RSRC_PLAN_DIRECTIVES;

OlegON · █ 14.03.2010 22:34

Итак, создал я план SUPERMAG_DAY
что-то вроде

Код:

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
DBMS_RESOURCE_MANAGER.CREATE_PLAN('SUPERMAG_DAY','The plan for normal daily operations');
dbms_resource_manager.create_consumer_group('LOW_GROUP','low','ROUND-ROBIN');
dbms_resource_manager.create_consumer_group('SYS_GROUP','system','ROUND-ROBIN');
dbms_resource_manager.create_consumer_group('ANAL','bored','ROUND-ROBIN');
dbms_resource_manager.submit_pending_area();
END;
/

В нем решил распределить несколько групп:

Код:

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
    plan => 'SUPERMAG_DAY',
    group_or_subplan => 'OTHER_GROUPS',
    new_comment => 'other',
    new_cpu_p1 => 0, new_cpu_p2 => 0, new_cpu_p3 => 0, new_cpu_p4 => 0,
    new_cpu_p5 => 0, new_cpu_p6 => 0, new_cpu_p7 => 0, new_cpu_p8 => 1,
    new_parallel_degree_limit_p1 => 1,
    new_active_sess_pool_p1 => 2,
    new_queueing_p1 => NULL,
    new_switch_group => 'KILL_SESSION',
    new_switch_time => 600,
    new_switch_estimate => false,
    new_max_est_exec_time => 600,
    new_undo_pool => NULL,
    new_max_idle_time => 3600,
    new_max_idle_blocker_time => 10,
    new_switch_time_in_call => NULL
);
dbms_resource_manager.submit_pending_area();
END;
/

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
    plan => 'SUPERMAG_DAY',
    group_or_subplan => 'SYS_GROUP',
    new_comment => 'sys',
    new_cpu_p1 => 10, new_cpu_p2 => 10, new_cpu_p3 => 10, new_cpu_p4 => 10,
    new_cpu_p5 => 10, new_cpu_p6 => 10, new_cpu_p7 => 10, new_cpu_p8 => 10,
    new_parallel_degree_limit_p1 => 4,
    new_active_sess_pool_p1 => NULL,
    new_queueing_p1 => NULL,
    new_switch_group => 'KILL_SESSION',
    new_switch_time => 36600,
    new_switch_estimate => false,
    new_max_est_exec_time => NULL,
    new_undo_pool => NULL,
    new_max_idle_time => 36600,
    new_max_idle_blocker_time => 600,
    new_switch_time_in_call => NULL
);
dbms_resource_manager.submit_pending_area();
END;
/

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
    plan => 'SUPERMAG_DAY',
    group_or_subplan => 'DEFAULT_CONSUMER_GROUP',
    new_comment => 'default',
    new_cpu_p1 => 90, new_cpu_p2 => 90, new_cpu_p3 => 90, new_cpu_p4 => 90,
    new_cpu_p5 => 90, new_cpu_p6 => 90, new_cpu_p7 => 90, new_cpu_p8 => 86,
    new_parallel_degree_limit_p1 => 4,
    new_active_sess_pool_p1 => 50,
    new_queueing_p1 => NULL,
    new_switch_group => 'KILL_SESSION',
    new_switch_time => 7200,
    new_switch_estimate => false,
    new_max_est_exec_time => NULL,
    new_undo_pool => NULL,
    new_max_idle_time => 10000,
    new_max_idle_blocker_time => 30,
    new_switch_time_in_call => NULL
);
dbms_resource_manager.submit_pending_area();
END;
/

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
    plan => 'SUPERMAG_DAY',
    group_or_subplan => 'ANAL',
    new_comment => 'lowest',
    new_cpu_p1 => 0, new_cpu_p2 => 0, new_cpu_p3 => 0, new_cpu_p4 => 0,
    new_cpu_p5 => 0, new_cpu_p6 => 0, new_cpu_p7 => 0, new_cpu_p8 => 1,
    new_parallel_degree_limit_p1 => 1,
    new_active_sess_pool_p1 => 3,
    new_queueing_p1 => NULL,
    new_switch_group => 'KILL_SESSION',
    new_switch_time => 600,
    new_switch_estimate => false,
    new_max_est_exec_time => NULL,
    new_undo_pool => NULL,
    new_max_idle_time => 7200,
    new_max_idle_blocker_time => 15,
    new_switch_time_in_call => NULL
);
dbms_resource_manager.submit_pending_area();
END;
/

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
    plan => 'SUPERMAG_DAY',
    group_or_subplan => 'LOW_GROUP',
    new_comment => 'lower',
    new_cpu_p1 => 0, new_cpu_p2 => 0, new_cpu_p3 => 0, new_cpu_p4 => 0,
    new_cpu_p5 => 0, new_cpu_p6 => 0, new_cpu_p7 => 0, new_cpu_p8 => 1,
    new_parallel_degree_limit_p1 => 1,
    new_active_sess_pool_p1 => 10,
    new_queueing_p1 => NULL,
    new_switch_group => 'KILL_SESSION',
    new_switch_time => 1200,
    new_switch_estimate => false,
    new_max_est_exec_time => NULL,
    new_undo_pool => NULL,
    new_max_idle_time => 7200,
    new_max_idle_blocker_time => 15,
    new_switch_time_in_call => NULL
);
dbms_resource_manager.submit_pending_area();
END;
/

В принципе все просто до банального. Возьмем последнюю, LOW_GROUP. При распределении процессорного времени, этой группе достается 1% на восьмом уровне (каждый уровень = 100%, больше выделить не удастся). Уровень параллелизма зажат в 1, т.е. как бы не пытался распараллелить запрос неумеха, он это сделать не сможет (не надо есть мои процессоры кривыми запросами). Более 10 сессий он одновременно не запустит. Если он надумает запустить запрос более чем на 1200 секунд, то сессия будет прибита (для пацифистов есть вариант CANCEL SQL). Ну и соответственно, через 7200 секунд простоя или через 15, если что-то заблокирует, он так же выбивается.

Определяем привязку пользователей к группам (приоритет выбора):

Код:

dbms_resource_manager.set_consumer_group_mapping_pri(
explicit              IN NUMBER,
oracle_user           IN NUMBER,
service_name          IN NUMBER,
client_os_user        IN NUMBER,
client_program        IN NUMBER,
client_machine        IN NUMBER,
module_name           IN NUMBER,
module_name_action    IN NUMBER,
service_module        IN NUMBER,
service_module_action IN NUMBER);

Я выбрал, чтобы сначала работали правила привязки к машинам (поскольку любители экспериментов у меня сидят за одними и теми же), затем к имени модуля (чтобы выделить почтовик), ну и далее, в соответствии с описанным выше.

Код:

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping_pri(10,3,4,5,6,1,2,7,8,9);
dbms_resource_manager.submit_pending_area();
END;
/

Посмотреть приоритет можно запросом:

Код:

select * FROM dba_rsrc_mapping_priority;

Маленький финт ушами. Все пользователи супермага автоматом идут в LOW_GROUP, но с одной машины, где работает сервер супермага и считается товародвижение - в SYS_GROUP

Код:

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_initial_consumer_group(user=> 'SUPERMAG', consumer_group=>'LOW_GROUP');
dbms_resource_manager.submit_pending_area();
END;
/

Код:

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.CLIENT_MACHINE, 'WORKGROUP\EX','SYS_GROUP');
dbms_resource_manager.submit_pending_area();
END;
/

На всякий случай приведу список возможных привязок

Цитата:

* EXPLICIT – switch groups via command to switch either code or Resource Plan
* SERVICE_MODULE_ACTION – switch groups depending on service name used to connect, module and action of the code that is being executed. The programmers must embed these names within the code.
* SERVICE_MODULE – switch groups depending on service name used to connect and program module
* MODULE_NAME_ACTION – switch groups depending on program module and action being executed
* MODULE_NAME – switch groups depending on program module
o For all module or action methods, programmers must embed calls to DBMS_APPLICATION_INFO
* SERVICE_NAME – switch groups depending on service name used to connect
* ORACLE_USER – activate group at login depending on Oracle User ID
* CLIENT_PROGRAM – switch groups depending on user process used to connect
* CLIENT_OS_USER – switch groups depending on OS username
* CLIENT_MACHINE – switch groups depending on name of client machine

Можно изначально определить пользователя в одну из групп:

Код:

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_initial_consumer_group(user=> 'MARKET', consumer_group=>'LOW_GROUP');
dbms_resource_manager.submit_pending_area();
END;
/

В итоге можно посмотреть, кто в какой группе работает:

Код:

select distinct username,machine,resource_consumer_group from v$session;

OlegON · █ 15.03.2010 09:06

Да, забыл, грантовать право переключения надо...

Цитата:

exec DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('SUPERMAG','ANAL',TRUE);

OlegON · █ 22.06.2012 15:09

До кучи - удаление плана DAY_PLAN

Цитата:

DECLARE
l_plan_name DBA_RSRC_PLANS.plan%TYPE := UPPER('DAY_PLAN') ;
BEGIN
dbms_output.put ('Creating Pending Area...');
dbms_resource_manager.create_pending_area();
dbms_output.put_line ('Pending Area Created.');
dbms_output.put ('Deleting Plan '||l_plan_name||'...');
dbms_resource_manager.delete_plan( plan => l_plan_name);
dbms_output.put_line ('Plan Deleted.');
dbms_output.put ('Submitting Pending Area ...');
dbms_resource_manager.submit_pending_area();
dbms_output.put_line ('Pending Area Submitted.');
EXCEPTION WHEN others THEN null;
dbms_resource_manager.clear_pending_area();
END;
/

или (со всеми группами).

Цитата:

DECLARE
l_plan_name DBA_RSRC_PLANS.plan%TYPE := UPPER('DAY_PLAN') ;
BEGIN
dbms_output.put ('Creating Pending Area...');
dbms_resource_manager.create_pending_area();
dbms_output.put_line ('Pending Area Created.');
dbms_output.put ('Deleting Plan '||l_plan_name||'...');
dbms_resource_manager.delete_plan( plan => l_plan_name);
dbms_output.put_line ('Plan Deleted.');
FOR con_grp IN ( SELECT group_or_subplan
FROM dba_rsrc_plan_directives
WHERE plan = l_plan_name
) LOOP
dbms_output.put ('Deleting Consumer Group '
||con_grp.group_or_subplan||'...');
dbms_resource_manager.delete_consumer_group(
consumer_group => con_grp.group_or_subplan );
dbms_output.put_line ('Consumer Group Deleted.');
END LOOP;
dbms_output.put ('Submitting Pending Area ...');
dbms_resource_manager.submit_pending_area();
dbms_output.put_line ('Pending Area Submitted.');
EXCEPTION WHEN others THEN
dbms_output.new_line ;
dbms_output.put_line ('Error Occurred :'||SQLERRM);
dbms_resource_manager.clear_pending_area();
dbms_output.put_line ('... Pending Area Cleared.');
END;
/

leonid · █ 29.06.2012 17:00

Очень полезно и интересно. Давно об этом думаю.
Только не понятен один момент - как ограничить например Почтовик, если он работает под юзером Supermag ?
И как ограничить самописные отчеты, если они все выполняются под юзером OTCHET ?

OlegON · █ 29.06.2012 17:33

Я ограничиваю почтовик по машине. Т.е. если не с сервера БД, то supermag идет в группу пониженного приоритета. С OTCHET еще проще, его засунуть в группу пониженного приоритета сразу. Писать готовые скрипты не буду, извини.

leonid · █ 10.08.2012 15:24

простенькая вариация на тему, ограничим пользователя OTCHET и программу prog1.exe по процессору:

Код:

-- рабочая область
exec dbms_resource_manager.create_pending_area(); 
  
-- создаем план
exec DBMS_RESOURCE_MANAGER.CREATE_PLAN('SUPERMAG_DAY','The plan for normal daily operations'); 
  
-- создадим группу?
exec dbms_resource_manager.create_consumer_group('SM_LOW_GROUP','lower'); 

-- ограниченные пользователи
exec dbms_resource_manager.create_plan_directive( 'SUPERMAG_DAY', 'SM_LOW_GROUP', 'lower', cpu_p1 => 0, cpu_p2 => 0, cpu_p3 => 0, cpu_p4 => 0, cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 20, cpu_p8 => 20);

-- обычные пользователи
exec dbms_resource_manager.create_plan_directive( 'SUPERMAG_DAY', 'OTHER_GROUPS', 'ower', cpu_p1 => 100, cpu_p2 => 100, cpu_p3 => 100, cpu_p4 =>100, cpu_p5 => 100, cpu_p6 => 100, cpu_p7 => 80, cpu_p8 => 80);

-- проверим
EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA(); 

-- применим
EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); 
-- план создан.

-- разрешим переключение пользователя
exec DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('OTCHET', 'SM_LOW_GROUP', FALSE); 

-- разрешим переключение пользователя
exec DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('SYSTEM', 'SM_LOW_GROUP', FALSE);

-- эту прогу в лоу груп
exec DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.MODULE_NAME, 'prog1.exe','SM_LOW_GROUP');

-- эту прогу в лоу груп
exec DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM, 'prog1.exe','SM_LOW_GROUP');

-- этого пользователя в лоу груп
exec DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('OTCHET', 'SM_LOW_GROUP');

-- все остальные остаются без изменений.

-- применить план
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = SUPERMAG_DAY scope=memory; 

-- убрать план
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '' scope=memory;

-- кто в какой группе
select distinct username,machine,resource_consumer_group, v.program from v$session v

правда, после применения такого плана почему-то иногда вылезают блокировки кэша

OlegON · █ 14.08.2012 13:52

Просмотр закреплений:

Цитата:

select * from dba_rsrc_group_mappings;

OlegON · █ 22.08.2012 12:08

Удаление плана SUPERMAG_SYSTEM и группы пользователей LOW_GROUP

Код:

exec dbms_resource_manager.create_pending_area();
exec dbms_resource_manager.delete_plan( plan =>'SUPERMAG_SYSTEM');
exec dbms_resource_manager.delete_consumer_group('LOW_GROUP');
exec dbms_resource_manager.submit_pending_area();
exec dbms_resource_manager.clear_pending_area();