Program

▣ 실행될 프로그램 등록

▶ Show SQL

BEGIN

DBMS_SCHEDULER.CREATE_PROGRAM(

program_name=>'SYS.myProgram',

program_action=>'begin

     insert into hr.x.values(hr.xs.nextval,''myJob done by myPrg'',systimestamp);

end;',

program_type=>'PLSQL_BLOCK',

number_of_arguments=>0,

comments=>'',

enabled=>TRUE);

END;

   

▣ 등록한 Program을 Job에 등록

   

▶ Show SQL

BEGIN

sys.dbms_scheduler.set_attribute_null( name => '"SYS"."MYJOB"', attribute => 'job_type');

sys.dbms_scheduler.set_attribute_null( name => '"SYS"."MYJOB"', attribute => 'job_action');

sys.dbms_scheduler.set_attribute_null( name => '"SYS"."MYJOB"', attribute => 'number_of_arguments');

sys.dbms_scheduler.set_attribute( name => '"SYS"."MYJOB"', attribute => 'program_name', value => 'SYS.MYPROGRAM');

END;

   

▣ 실행확인

Job

▣ Job 실습에 사용할 테이블 생성

   

▣ EM에서 Job 등록

   

▶ PL/SQL부분에 테이블에 삽입하는 sql문을 입력

   

▶ Job이 실행되는 시간 간격 조정

▶ 등록했지만 실제 테이블에 삽입되지 않음

S HR> select * from x;

   

ID DATA TIME

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

1 sqlplus test 22-AUG-09 12.19.47.496777 PM

   

▶ 에러 확인

   

▶ 해결 방법

EM interface map form Job Scheduler

• Window : represented by an interval of time with a well-defined beginning and end, and is used to activate different resource plans at different times.

  this always you to change resource allocation during a time period such as time of day or time of the sales year.

• Window Group : represents a list of windows, and allows for easier management of windows. You can use a window or window group as the schedule

  for a job to ensure that the job runs only when a window and its associated resource plan are active.

• Resource consumer group : associated with the job class determines the resources that are allocated to the job in the job class.
• Resource plan : enables users to prioritize resources(most notably CPU) among resouce consumer groups.

Database Resource Manager Information

View Name	Information
DBA_RSRC_PLANS	Plans and status
DBA_RSRC_PLAN_DIRECTIVES	Plan directives
DBA_RSRC_CONSUMER_GROUPS	Consumer groups
DBA_RSRC_CONSUMER_GROUPS_PRIVS	Users/roles
DBA_RSRC_GROUP_MAPPINGS	Consumer group mapping
DBA_RSRC_MAPPING_PRIORITY	Mapping priority
DBA_USERS	Column initial_rsrc_consumer_group
DBA_RSRC_MANAGER_SYSTEM_PRIVS	Users/roles

   

SQL> show parameter resource_manager_plan

   

NAME TYPE VALUE

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

resource_manager_plan string INTERNAL_PLAN

Resource Consumer Group mapping

• Consumer Group 가입은 단지 유저만 하는 것이 아님
  • Group mapping 가능 한 것
    • Service,Module,and Action
    • Service and Module
    • Module ad Action
    • Module
    • Service
    • Oracle User
    • Client Program
    • Client OS User
    • Client Machine

     

• OS 유저 기준이 OLTP, DB 유저 기준이 DW라면 DB가 우선한다.

Monitoring the Resource Manager

   

▣ doCpu.sql

begin

for i in 1..500000000 loop

dbms_output.put_line(9/2/5);

end loop;

end;

/

   

▶ Resource 사용 group 가 변하지 않음

   

▣ 해결 방법 => 유저의 consumer groups switching 권한을 변경

▶ show sql -1 : SCOTT

BEGIN

    dbms_resource_manager.set_initial_consumer_group(

        user => 'SCOTT',

        consumer_group => 'DW'

    );

END;

   

   

▶ show sql - 2 : HR

BEGIN

dbms_resource_manager.set_initial_consumer_group(

user => 'HR',

consumer_group => 'OLTP'

 );

END;

   

   

▣ 다시 doCpu 실행

• HR이 실행한 작업이 OLTP이기 때문에 먼저 Resource를 사용하고 종료 후 SCOTT이 실행한 작업이 실행된다.

   

▣ Resource Plan 변경

   

▶ SQL

ALTER SYSTEM SET resource_manager_plan='INTERNAL_PLAN';

   

▶ 실습

SQL> select PLAN from DBA_RSRC_PLANS; => 전체 PLAN 검색

   

PLAN

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

SYSTEM_PLAN

MYPLAN

INTERNAL_PLAN

INTERNAL_QUIESCE

   

SQL> select NAME,IS_TOP_PLAN from V$RSRC_PLAN;

   

NAME IS_TO

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

MYPLAN TRUE => 현재 적용중인 PLAN

   

SQL> SHOW PARAMETER PLAN;

   

NAME TYPE VALUE

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

resource_manager_plan string MYPLAN => 파라미터로 검색

   

SQL> ALTER SYSTEM SET resource_manager_plan='INTERNAL_PLAN'; => PLAN 변경

   

System altered.

   

SQL> select NAME,IS_TOP_PLAN from V$RSRC_PLAN;

   

NAME IS_TO

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

INTERNAL_PLAN TRUE => INTERNAL_PLAN 으로 변경된 것 확인

   

SQL> show parameter plan;

   

NAME TYPE VALUE

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

resource_manager_plan string INTERNAL_PLAN

   

▣ 지정된 시간 사용 후 다른 Resource Group으로 전환

   

▶ show sql

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 => 3600

);

dbms_resource_manager.submit_pending_area();

END;

   

▣ 실습 - DW 30초 후 DEFAULT_CONSUMER_GROUP 으로 변경

• Consumer Group Switching Tab 에서 DW group의 Maximum Execution Time 을 30으로 바꾸고 Action To Take를

  Switch to Group DEFAULT_CONSUMER_GROUP 으로 변경 후 확인

• Scott 유저에서 doCpu 실행
• 실행하면 SCOTT이 실행하기 때문에 DW가 사용하는 Resource가 증가하는 것 확인 할 수 있음
• 30초 후 DW는 줄어들고 DEFAULT_CONSUMER_GROUP의 CPU 사용량이 증가하는 것 확인

Resource Plan

※ consumer group 간 resource mapping

   

▣ Resource Plan 생성

※ Activate this plan : 현재 Plan 활성화

   

▶ show sql

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;