课程第27次（2018-08-16星期四）

botang

默认的Filewatcher作业其实是一个queue：



当我们创建自己的Filewatcher时，其实就是上面这个Filewatcher的一个实例。

间隔性地进行Filewatcher：

1. BEGIN
   
2. sys.dbms_scheduler.set_attribute( name => '"SYS"."FILE_WATCHER_SCHEDULE"', attribute => 'repeat_interval', value => 'FREQ=MINUTELY;INTERVAL=2');
   
3. END;

复制代码

在64为Linux上，首先要做：
[root@station90 lib64]# ls -l libpam.so*
lrwxrwxrwx  1 root root    16  8月  4 15:48 libpam.so -> libpam.so.0.82.2
lrwxrwxrwx. 1 root root    16  7月 17 04:41 libpam.so.0 -> libpam.so.0.82.2
-rwxr-xr-x. 1 root root 55280  7月 10 2017 libpam.so.0.82.2
-----------------------------------------------------------------------------------------

1. begin
   
2.    dbms_scheduler.create_file_watcher(
   
3.        file_watcher_name => 'filewatcher1',
   
4.        directory_path => '/home/oracle/myfile',
   
5.        file_name => 'botang*.txt',
   
6.        credential_name => 'credential1');
   
7. end;

复制代码

1. select  j.state, j.next_run_date
   
2. from  dba_scheduler_jobs  j where j.job_name='FILE_WATCHER';

复制代码

	STATE	NEXT_RUN_DATE
1	SCHEDULED	16-AUG-18 08.00.24.578708 PM PRC

1. select  * from dba_scheduler_file_watchers;

复制代码

OWNER

FILE_WATCHER_NAME

ENABLED

DESTINATION_OWNER

DESTINATION

DIRECTORY_PATH

FILE_NAME

CREDENTIAL_OWNER

CREDENTIAL_NAME

MIN_FILE_SIZE

STEADY_STATE_DURATION

LAST_MODIFIED_TIME

COMMENTS

1

SYS

FILEWATCHER1

TRUE

/home/oracle/myfile

botang*.txt

SYS

CREDENTIAL1

0

16-AUG-18 07.50.24.582733 PM +08:00

1. grant execute on filewatcher1 to hr;
   
2. grant execute on SYS.SCHEDULER_FILEWATCHER_RESULT to hr;
   
3. 
   
4. create table hr.tfilewatcher1 ( a  varchar2(200)  ) ;
   
5. 
   
6. create or replace procedure hr.procfilewatcher ( p_1   SYS.SCHEDULER_FILEWATCHER_RESULT)
   
7. is
   
8. begin
   
9.   insert into hr.tfilewatcher1  values ( to_char( p_1.file_timestamp,'YYYY-MM-DD:HH24:MI:SS') ||'      '
   
10.                                                       ||p_1.directory_path||'        '
    
11.                                                       ||p_1.actual_file_name||'       '
    
12.                                                       ||p_1.file_size  ) ;
    
13.   commit;
    
14. end;
    
15. 
    
16. begin
    
17.     dbms_scheduler.create_program(
    
18.      program_name => 'HR.program10',
    
19.      program_type => 'STORED_PROCEDURE',
    
20.      program_action => 'HR.procfilewatcher',
    
21.      number_of_arguments => 1,
    
22.      enabled => false);
    
23. end;
    
24. 
    
25. begin
    
26.    dbms_scheduler.define_metadata_argument(
    
27.    program_name => 'HR.program10',
    
28.    metadata_attribute => 'event_message',argument_position => 1 ) ;
    
29. end;
    
30. 
    
31. begin
    
32.   dbms_scheduler.enable('HR.program10');
    
33. end;
    
34. 
    
35. BEGIN
    
36.   DBMS_SCHEDULER.CREATE_JOB(
    
37.    jOB_NAME=> 'hr.job10',
    
38.    PROGRAM_NAME=> 'hr.program10',
    
39.    EVENT_CONDITION => 'tab.user_data.file_size > 10',
    
40.    QUEUE_SPEC=> 'filewatcher1',
    
41.    AUTO_DROP=> FALSE,
    
42.    ENABLED=> true);
    
43. END;
    
44. 
    
45. begin
    
46.    dbms_scheduler.set_attribute('hr.job10','parallel_instances',true);
    
47. end;
    
48. 
    
49. 
    
50. select  * from  hr.tfilewatcher1;
    
51. 
    
52. 
    

复制代码

1. BEGIN
   
2. dbms_resource_manager.clear_pending_area();
   
3. dbms_resource_manager.create_pending_area();
   
4. dbms_resource_manager.create_plan( plan => 'PLAN2',
   
5.                                                                comment => 'PLAN2',
   
6.                                                                cpu_mth => 'RATIO' );
   
7. dbms_resource_manager.create_plan_directive(
   
8.     plan => 'PLAN2',
   
9.     group_or_subplan => 'SYS_GROUP',
   
10.     comment => 'SYS_GROUP',
    
11.     mgmt_p1 => 50   
    
12. );
    
13. dbms_resource_manager.create_plan_directive(
    
14.     plan => 'PLAN2',
    
15.     group_or_subplan => 'GROUP1',
    
16.     comment => 'GROUP1',
    
17.     mgmt_p1 => 30   
    
18. );
    
19. dbms_resource_manager.create_plan_directive(
    
20.     plan => 'PLAN2',
    
21.     group_or_subplan => 'GROUP2',
    
22.     comment => 'GROUP2',
    
23.     mgmt_p1 => 15   
    
24. );
    
25. dbms_resource_manager.create_plan_directive(
    
26.     plan => 'PLAN2',
    
27.     group_or_subplan => 'OTHER_GROUPS',
    
28.     comment => 'OTHER_GROUPS',
    
29.     mgmt_p1 => 5   
    
30. );
    
31. dbms_resource_manager.submit_pending_area();
    
32. end;
    
33. 
    
34. select  p.plan, p.cpu_method,p.active_sess_pool_mth,
    
35.     p.parallel_degree_limit_mth , p.queueing_mth
    
36. from dba_rsrc_plans  p
    
37.   where p.plan like 'PLAN%';

复制代码

	PLAN	CPU_METHOD	ACTIVE_SESS_POOL_MTH	PARALLEL_DEGREE_LIMIT_MTH	QUEUEING_MTH
1	PLAN2	RATIO	ACTIVE_SESS_POOL_ABSOLUTE	PARALLEL_DEGREE_LIMIT_ABSOLUTE	FIFO_TIMEOUT
2	PLAN1	EMPHASIS	ACTIVE_SESS_POOL_ABSOLUTE	PARALLEL_DEGREE_LIMIT_ABSOLUTE	FIFO_TIMEOUT

子计划出现在哪里：

1. begin
   
2.   dbms_resource_manager.create_simple_plan(simple_plan => 'PLAN3',
   
3.   consumer_group1 => 'group1',
   
4.   group1_cpu => 80,
   
5.   consumer_group2 => 'group2',
   
6.   group2_cpu => 20);
   
7. end;

复制代码

1. select  p.plan, p.cpu_method,p.active_sess_pool_mth,
   
2.     p.parallel_degree_limit_mth , p.queueing_mth
   
3. from dba_rsrc_plans  p
   
4.   where p.plan like 'PLAN%';

复制代码

	PLAN	CPU_METHOD	ACTIVE_SESS_POOL_MTH	PARALLEL_DEGREE_LIMIT_MTH	QUEUEING_MTH
1	PLAN1	EMPHASIS	ACTIVE_SESS_POOL_ABSOLUTE	PARALLEL_DEGREE_LIMIT_ABSOLUTE	FIFO_TIMEOUT
2	PLAN3	EMPHASIS	ACTIVE_SESS_POOL_ABSOLUTE	PARALLEL_DEGREE_LIMIT_ABSOLUTE	FIFO_TIMEOUT
3	PLAN2	RATIO	ACTIVE_SESS_POOL_ABSOLUTE	PARALLEL_DEGREE_LIMIT_ABSOLUTE	FIFO_TIMEOUT

1. select  pd.mgmt_p1,
   
2.             pd.mgmt_p2,
   
3.             pd.mgmt_p3
   
4.   from dba_rsrc_plan_directives pd
   
5. where pd.plan='PLAN3';
   
6.   

复制代码

	MGMT_P1	MGMT_P2	MGMT_P3
1	0	80	0
2	100	0	0
3	0	0	100
4	0	20	0

RATIO没有第二级：




测一下cpu：





---------------------------------------------------------------------------------------
并行度：

1. select    pd.group_or_subplan  ,pd.parallel_degree_limit_p1
   
2.   from dba_rsrc_plan_directives pd
   
3. where pd.plan='PLAN2';

复制代码

	GROUP_OR_SUBPLAN	PARALLEL_DEGREE_LIMIT_P1
1	SYS_GROUP
2	GROUP1	1
3	GROUP2
4	OTHER_GROUPS

-----------------------------活跃会话池