DB스토리

Resourecs => 경합시 우선권 부여.

-hr oltp consumer groups 으로 만들기.

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_consumer_group(consumer_group => 'oltp', comment => '', cpu_mth => 'ROUND-ROBIN');
dbms_resource_manager.submit_pending_area();
BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group('HR', 'oltp', false);
END;END;

 SQL> @createConsumerGroup

PL/SQL procedure successfully completed.

SQL> @fv
Enter value for key: consumer
old   2: where view_name like upper('%&KEY%')
new   2: where view_name like upper('%consumer%')

VIEW_NAME
------------------------------
V_$RSRC_CONSUMER_GROUP_CPU_MTH
V_$RSRC_CONSUMER_GROUP
GV_$RSRC_CONSUMER_GROUP
DBA_RSRC_CONSUMER_GROUPS
DBA_RSRC_CONSUMER_GROUP_PRIVS
USER_RSRC_CONSUMER_GROUP_PRIVS
KU$_RMGR_CONSUMER_VIEW
KU$_RMGR_INIT_CONSUMER_VIEW
_DBA_STREAMS_MESSAGE_CONSUMERS
DBA_STREAMS_MESSAGE_CONSUMERS
ALL_STREAMS_MESSAGE_CONSUMERS

11 rows selected.

RSRC => ReSouRCe 

SQL> desc DBA_RSRC_CONSUMER_GROUPS
 Name                                                            Null?    Type
 --------------------------------------------------------------- -------- -------------------------------------------
 CONSUMER_GROUP                                                           VARCHAR2(30)
 CPU_METHOD                                                               VARCHAR2(30)
 COMMENTS                                                                 VARCHAR2(2000)
 STATUS                                                                   VARCHAR2(30)
 MANDATORY                                                                VARCHAR2(3)

SQL> select CONSUMER_GROUP from DBA_RSRC_CONSUMER_GROUPS;

CONSUMER_GROUP
------------------------------
OTHER_GROUPS
DEFAULT_CONSUMER_GROUP
SYS_GROUP
LOW_GROUP
AUTO_TASK_CONSUMER_GROUP
OLTP

6 rows selected.

--Scott 을 DW consumer groups 으로 만들기.

SQL> ed
Wrote file afiedt.buf

  1  BEGIN
  2     dbms_resource_manager.clear_pending_area();
  3     dbms_resource_manager.create_pending_area();
  4     dbms_resource_manager.create_consumer_group
  5             (consumer_group => 'dw', comment => '', cpu_mth => 'ROUND-ROBIN');
  6     dbms_resource_manager.submit_pending_area();
  7     BEGIN
  8             dbms_resource_manager_privs.grant_switch_consumer_group('SCOTT', 'dw', false);
  9     END;
 10* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> select CONSUMER_GROUP from DBA_RSRC_CONSUMER_GROUPS;

CONSUMER_GROUP
------------------------------
OTHER_GROUPS
DEFAULT_CONSUMER_GROUP
SYS_GROUP
LOW_GROUP
AUTO_TASK_CONSUMER_GROUP
OLTP
DW

7 rows selected.

SQL> select grantee,granted_group from DBA_RSRC_CONSUMER_GROUP_PRIVS where grantee='SCOTT';

GRANTEE                        GRANTED_GROUP
------------------------------ ------------------------------
SCOTT                          DW

SQL> select USERNAME,INITIAL_RSRC_CONSUMER_GROUP from dba_users where username= 'SCOTT';

USERNAME                       INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
SCOTT                          DEFAULT_CONSUMER_GROUP

createResourcePlan

 BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( 'MYPLAN', '');
dbms_resource_manager.create_plan_directive(
    plan => 'MYPLAN',
    group_or_subplan => 'DEFAULT_CONSUMER_GROUP',
    comment => '',
    cpu_p1 => 5, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
    cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,
    parallel_degree_limit_p1 => NULL,
    active_sess_pool_p1 => NULL,
    queueing_p1 => NULL,
    switch_group => '',
    switch_time => NULL,
    switch_estimate => false,
    max_est_exec_time => NULL,
    undo_pool => NULL,
    max_idle_time => NULL,
    max_idle_blocker_time => NULL,
    switch_time_in_call => NULL
);
dbms_resource_manager.create_plan_directive(
    plan => 'MYPLAN',
    group_or_subplan => 'DW',
    comment => '',
    cpu_p1 => 1, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
    cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,
    parallel_degree_limit_p1 => NULL,
    active_sess_pool_p1 => NULL,
    queueing_p1 => NULL,
    switch_group => '',
    switch_time => NULL,
    switch_estimate => false,
    max_est_exec_time => NULL,
    undo_pool => NULL,
    max_idle_time => NULL,
    max_idle_blocker_time => NULL,
    switch_time_in_call => NULL
);
dbms_resource_manager.create_plan_directive(
    plan => 'MYPLAN',
    group_or_subplan => 'OLTP',
    comment => '',
    cpu_p1 => 90, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
    cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,
    parallel_degree_limit_p1 => NULL,
    active_sess_pool_p1 => NULL,
    queueing_p1 => NULL,
    switch_group => '',
    switch_time => NULL,
    switch_estimate => false,
    max_est_exec_time => NULL,
    undo_pool => NULL,
    max_idle_time => NULL,
    max_idle_blocker_time => NULL,
    switch_time_in_call => NULL
);
dbms_resource_manager.create_plan_directive(
    plan => 'MYPLAN',
    group_or_subplan => 'OTHER_GROUPS',
    comment => '',
    cpu_p1 => 4, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
    cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,
    parallel_degree_limit_p1 => NULL,
    active_sess_pool_p1 => NULL,
    queueing_p1 => NULL,
    switch_group => '',
    switch_time => NULL,
    switch_estimate => false,
    max_est_exec_time => NULL,
    undo_pool => NULL,
    max_idle_time => NULL,
    max_idle_blocker_time => NULL,
    switch_time_in_call => NULL
);
dbms_resource_manager.submit_pending_area();
dbms_resource_manager.switch_plan( plan_name => 'MYPLAN', sid => 'orcl' );
END;

/

-scott consumer group  바꾸기.

SQL> select USERNAME,INITIAL_RSRC_CONSUMER_GROUP from dba_users where username= 'SCOTT';

USERNAME                       INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
SCOTT                          DEFAULT_CONSUMER_GROUP

BEGIN 
    dbms_resource_manager.set_initial_consumer_group(
        user => 'SCOTT',
        consumer_group => 'DW'
    );
END;

 /

SQL> sav setInitialConsumerGroup

SQL> select USERNAME,INITIAL_RSRC_CONSUMER_GROUP from dba_users where username= 'SCOTT';

USERNAME                       INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
SCOTT                          DW

SQL>BEGIN
        dbms_resource_manager.set_initial_consumer_group(
           user => 'HR',
           consumer_group => 'OLTP'
        );
        END;
        /

PL/SQL procedure successfully completed.

SQL> select username,initial_rsrc_consumer_group from dba_users where username='HR';

USERNAME                       INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
HR                             OLTP

SQL>  select grantee,granted_group from DBA_RSRC_CONSUMER_GROUP_PRIVS
           where granted_group in ('DW','OLTP')
           /

GRANTEE                        GRANTED_GROUP
------------------------------ ------------------------------
SCOTT                          DW
HR                             OLTP

이 상태에서

 begin
        for i in 1..500000000 loop
                dbms_output.put_line(9/2/5);
        end loop;
end;
/

@doCpu 날리고

em 에서 Admin => Resource 에서 monitor 보시오.

 -plan 기본으로 돌리기.

ALTER SYSTEM SET resource_manager_plan = 'INTERNAL_PLAN'

 SQL> show parameter plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string      MYPLAN

 SQL> select NAME, IS_TOP_PLAN from V_$RSRC_PLAN;

NAME                             IS_TO
-------------------------------- -----
MYPLAN                           TRUE

SQL> select plan from dba_rsrc_plans;

PLAN
------------------------------
SYSTEM_PLAN
MYPLAN
INTERNAL_PLAN
INTERNAL_QUIESCE

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

System altered.

SQL> show parameter plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string      INTERNAL_PLAN

-Consumer Group Switching

oltp  30초 사용시간 지나면 일반 그룹으로 switch

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
    plan => 'MYPLAN',
    group_or_subplan => 'OLTP',
    new_comment => '',
    new_cpu_p1 => 90, new_cpu_p2 => NULL, new_cpu_p3 => NULL, new_cpu_p4 => NULL,
    new_cpu_p5 => NULL, new_cpu_p6 => NULL, new_cpu_p7 => NULL, new_cpu_p8 => NULL,
    new_parallel_degree_limit_p1 => NULL,
    new_active_sess_pool_p1 => NULL,
    new_queueing_p1 => NULL,
    new_switch_group => 'DEFAULT_CONSUMER_GROUP',
    new_switch_time => NULL,
    new_switch_estimate => false,
    new_max_est_exec_time => NULL,
    new_undo_pool => NULL,
    new_max_idle_time => NULL,
    new_max_idle_blocker_time => NULL,
    new_switch_time_in_call => 30

);
dbms_resource_manager.submit_pending_area();
END;