课程第50次（2017-11-02星期四）

botang

1Z0-053第16章
1Z0-052共19章（上完13章），1Z0-053共21章（上完16章）和1Z0-063多租户部分共9章（上完0章）
总共上完全部49章中的29章

1. select  username , status from v$session
   
2. where terminal='pts/0';
   
3. 
   
4. select * from v$transaction ;
   
5. 
   
6. select  * from dba_rsrc_plan_directives d
   
7.   where d.PLAN='PLAN1';
   
8.   
   
9. begin
   
10.    dbms_resource_manager.create_pending_area;
    
11.    dbms_resource_manager.update_plan_directive(
    
12.     plan => 'PLAN1',
    
13.     group_or_subplan => 'GROUP1',
    
14.     new_active_sess_pool_p1 => 2,
    
15.     new_queueing_p1 => 3);
    
16.         dbms_resource_manager.submit_pending_area();
    
17. END;
    
18. 
    
19. select d.GROUP_OR_SUBPLAN,
    
20. 
    
21.    d.ACTIVE_SESS_POOL_P1, d.QUEUEING_P1 from dba_rsrc_plan_directives d
    
22.   where d.PLAN='PLAN1';
    
23.   
    
24.   ------
    
25.   
    
26.   select  s.sid, terminal  ,   
    
27.    s.RESOURCE_CONSUMER_GROUP  ,s.STATUS
    
28.    from v$session  s where terminal in ('pts/2', 'pts/0','pts/1');

复制代码

1. begin
   
2.    dbms_resource_manager.create_pending_area;
   
3.    dbms_resource_manager.update_plan_directive(
   
4.     plan => 'PLAN1',
   
5.     group_or_subplan => 'GROUP1',
   
6.     new_active_sess_pool_p1 => -1,
   
7.     new_queueing_p1 => -1);
   
8.         dbms_resource_manager.submit_pending_area();
   
9. END;
   
10. 
    
11. select d.GROUP_OR_SUBPLAN,
    
12.    d.ACTIVE_SESS_POOL_P1, d.QUEUEING_P1 from dba_rsrc_plan_directives d
    
13.   where d.PLAN='PLAN1';
    
14.   
    
15.   begin
    
16.    dbms_resource_manager.create_pending_area;
    
17.    dbms_resource_manager.update_plan_directive(
    
18.     plan => 'PLAN1',
    
19.     group_or_subplan => 'GROUP1',
    
20.     new_undo_pool=> 8
    
21.     );
    
22.         dbms_resource_manager.submit_pending_area();
    
23. END;
    
24.   
    
25.   
    
26.   select d.GROUP_OR_SUBPLAN,
    
27.    d.UNDO_POOL from dba_rsrc_plan_directives d
    
28.   where d.PLAN='PLAN1';

复制代码

12c在使用预估时有问题：

1. begin
   
2.    dbms_resource_manager.create_pending_area;
   
3.    dbms_resource_manager.update_plan_directive(
   
4.     plan => 'PLAN1',
   
5.     group_or_subplan => 'GROUP1',
   
6.     new_switch_group => 'CANCEL_SQL',
   
7.         new_switch_elapsed_time => 5,
   
8.         new_switch_estimate => true
   
9.     );
   
10.         dbms_resource_manager.submit_pending_area();
    
11. END;
    
12. 
    
13. select d.GROUP_OR_SUBPLAN,
    
14.    d.SWITCH_ELAPSED_TIME,
    
15.    d.SWITCH_ESTIMATE,
    
16.    d.SWITCH_FOR_CALL,
    
17.    d.SWITCH_GROUP,
    
18.    d.SWITCH_IO_LOGICAL,
    
19.    d.SWITCH_IO_MEGABYTES,
    
20.    d.SWITCH_IO_REQS,
    
21.    d.SWITCH_TIME,
    
22.    d.SWITCH_TIME_IN_CALL
    
23.   
    
24.     from dba_rsrc_plan_directives d
    
25.   where d.PLAN='PLAN1';
    
26.   
    
27.   begin
    
28.    dbms_resource_manager.create_pending_area;
    
29.    dbms_resource_manager.update_plan_directive(
    
30.     plan => 'PLAN1',
    
31.     group_or_subplan => 'GROUP1',
    
32.     new_switch_group => 'CANCEL_SQL',
    
33.         new_switch_elapsed_time => 5,
    
34.         new_switch_estimate => false
    
35.     );
    
36.         dbms_resource_manager.submit_pending_area();
    
37. END;
    
38. 
    
39. select d.GROUP_OR_SUBPLAN,
    
40.    d.SWITCH_ELAPSED_TIME,
    
41.    d.SWITCH_ESTIMATE,
    
42.    d.SWITCH_FOR_CALL,
    
43.    d.SWITCH_GROUP,
    
44.    d.SWITCH_IO_LOGICAL,
    
45.    d.SWITCH_IO_MEGABYTES,
    
46.    d.SWITCH_IO_REQS,
    
47.    d.SWITCH_TIME,
    
48.    d.SWITCH_TIME_IN_CALL
    
49.   
    
50.     from dba_rsrc_plan_directives d
    
51.   where d.PLAN='PLAN1';

复制代码

1. root@127.0.0.1's password:
   
2. Last login: Tue Oct 31 19:55:42 2017 from station29.example.com
   
3. [root@station26 ~]# su - oracle
   
4. [oracle@station26 ~]$ sqlplus /nolog
   
5. 
   
6. SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 2 19:10:35 2017
   
7. 
   
8. Copyright (c) 1982, 2014, Oracle.  All rights reserved.
   
9. 
   
10. SQL> conn hr/oracle_4U
    
11. Connected.
    
12. SQL> !ps
    
13.   PID TTY          TIME CMD
    
14. 7778 pts/0    00:00:00 bash
    
15. 7802 pts/0    00:00:00 sqlplus
    
16. 7821 pts/0    00:00:00 ps
    
17. 
    
18. SQL> update employees set salary=salary*2 where employee_id=100;
    
19. 
    
20. 1 row updated.
    
21. 
    
22. SQL> commit;
    
23. 
    
24. Commit complete.
    
25. 
    
26. SQL> select  count(*) from t_big;
    
27. 
    
28.   COUNT(*)
    
29. ----------
    
30.     326347
    
31. 
    
32. SQL>  select  count(*) from t_big a, departments b ;
    
33. 
    
34.   COUNT(*)
    
35. ----------
    
36.    8811369
    
37. 
    
38. SQL> select  count(*) from t_big a, departments b, departments c;
    
39. 
    
40.   COUNT(*)
    
41. ----------
    
42. 237906963
    
43. 
    
44. SQL> !ps
    
45.   PID TTY          TIME CMD
    
46. 7778 pts/0    00:00:00 bash
    
47. 7802 pts/0    00:00:00 sqlplus
    
48. 8803 pts/0    00:00:00 ps
    
49. 
    
50. SQL> drop index i_big ;
    
51. 
    
52. Index dropped.
    
53. 
    
54. SQL> desc t_big   
    
55. Name                                           Null?    Type
    
56. ----------------------------------------- -------- ----------------------------
    
57. OWNER                                                    VARCHAR2(128)
    
58. NAME                                                    VARCHAR2(128)
    
59. TYPE                                                    VARCHAR2(12)
    
60. LINE                                                    NUMBER
    
61. TEXT                                                    VARCHAR2(4000)
    
62. ORIGIN_CON_ID                                            NUMBER
    
63. 
    
64. SQL> create index i_big on t_big ( text , dump(text)  , OWNER, NAME, TYPE, LINE , ORIGIN_CON_ID ) parallel 20 ;
    
65. create index i_big on t_big ( text , dump(text)  , OWNER, NAME, TYPE, LINE , ORIGIN_CON_ID ) parallel 20
    
66.                                           *
    
67. ERROR at line 1:
    
68. ORA-01450: maximum key length (6398) exceeded
    
69. 
    
70. 
    
71. SQL> create index i_big on t_big ( text , substr(dump(text),1,20) , OWNER, NAME, TYPE, LINE , ORIGIN_CON_ID ) parallel 20 ;
    
72. 
    
73. Index created.
    
74. 
    
75. SQL> select  * from v$pq_sesstat ;
    
76. 
    
77. STATISTIC                       LAST_QUERY SESSION_TOTAL     CON_ID
    
78. ------------------------------ ---------- ------------- ----------
    
79. Queries Parallelized                        0              0          0
    
80. DML Parallelized                        0              0          0
    
81. DDL Parallelized                        1              1          0
    
82. DFO Trees                                1              1          0
    
83. Server Threads                               40              0          0
    
84. Allocation Height                       20              0          0
    
85. Allocation Width                        1              0          0
    
86. Local Msgs Sent                      4530           4530          0
    
87. Distr Msgs Sent                         0              0          0
    
88. Local Msgs Recv'd                     4530           4530          0
    
89. Distr Msgs Recv'd                        0              0          0
    
90. 
    
91. STATISTIC                       LAST_QUERY SESSION_TOTAL     CON_ID
    
92. ------------------------------ ---------- ------------- ----------
    
93. DOP                                       20              0          0
    
94. Slave Sets                                2              0          0
    
95. 
    
96. 13 rows selected.
    
97. 
    
98. SQL> drop index i_big;
    
99. 
    
100. Index dropped.
     
101. 
     
102. SQL> create index i_big on t_big ( text , substr(dump(text),1,20) , OWNER, NAME, TYPE, LINE , ORIGIN_CON_ID ) parallel 20 ;
     
103. 
     
104. Index created.
     
105. 
     
106. SQL> drop index i_big;
     
107. 
     
108. Index dropped.
     
109. 
     
110. SQL> create index i_big on t_big ( text , substr(dump(text),1,20) , substr(dump(text),21,10), OWNER, NAME, TYPE, LINE , ORIGIN_CON_ID ) parallel 20 ;
     
111. 
     
112. Index created.
     
113. 
     
114. SQL> drop index i_big;
     
115. 
     
116. Index dropped.
     
117. 
     
118. SQL> select  count(*)  from t_big a  , t_big b;
     
119. ^Cselect        count(*)  from t_big a        , t_big b
     
120.                        *
     
121. ERROR at line 1:
     
122. ORA-01013: user requested cancel of current operation
     
123. 
     
124. 
     
125. 
     
126. SQL> conn hr/oracle_4U
     
127. Connected.
     
128. SQL> create table t05316_a ( a char(2000)) ;
     
129. 
     
130. Table created.
     
131. 
     
132. SQL> create table t05316_b ( a char(2000)) ;
     
133. 
     
134. Table created.
     
135. 
     
136. SQL> conn hr/oracle_4U
     
137. Connected.
     
138. SQL> conn / as sysdba
     
139. Connected.
     
140. SQL> insert into hr.t05316_a values ('A') ;
     
141. 
     
142. 1 row created.
     
143. 
     
144. SQL> commit;
     
145. 
     
146. Commit complete.
     
147. 
     
148. SQL> insert into hr.t05316_b values ('X') ;
     
149. 
     
150. 1 row created.
     
151. 
     
152. SQL> commit;
     
153. 
     
154. Commit complete.
     
155. 
     
156. SQL> conn hr/oracle_4U
     
157. Connected.
     
158. SQL> update t05316_a set a='B';
     
159. 
     
160. 1 row updated.
     
161. 
     
162. SQL> update t05316_a set a='C';
     
163. 
     
164. 1 row updated.
     
165. 
     
166. SQL> exec dbms_stats.gather_table_stat('HR','T_BIG') ;
     
167. BEGIN dbms_stats.gather_table_stat('HR','T_BIG') ; END;
     
168. 
     
169.                  *
     
170. ERROR at line 1:
     
171. ORA-06550: line 1, column 18:
     
172. PLS-00302: component 'GATHER_TABLE_STAT' must be declared
     
173. ORA-06550: line 1, column 7:
     
174. PL/SQL: Statement ignored
     
175. 
     
176. 
     
177. SQL>  exec dbms_stats.gather_table_stats('HR','T_BIG') ;
     
178. 
     
179. PL/SQL procedure successfully completed.
     
180. 
     
181. SQL> select count(*)  from t_big a , departments b, departments c;
     
182. select count(*)  from t_big a , departments b, departments c
     
183.                       *
     
184. ERROR at line 1:
     
185. ORA-56735: elapsed time limit exceeded - call aborted
     
186. 
     
187. 
     
188. SQL> select count(*)  from t_big a , departments b, departments c;
     
189. select count(*)  from t_big a , departments b, departments c
     
190.                       *
     
191. ERROR at line 1:
     
192. ORA-56735: elapsed time limit exceeded - call aborted
     
193. 
     
194. 
     
195. SQL>
     

复制代码

1. DECLARE
   
2. spfileValue VARCHAR2(1000);
   
3. execText VARCHAR2(1000);
   
4. scopeValue VARCHAR2(30) := 'MEMORY';
   
5. planName VARCHAR2(100) :='PLAN1';
   
6. BEGIN
   
7. dbms_resource_manager.clear_pending_area();
   
8. dbms_resource_manager.create_pending_area();
   
9. dbms_resource_manager.update_plan_directive(
   
10.     plan => 'PLAN1',
    
11.     group_or_subplan => 'GROUP1',
    
12.     new_comment => '',
    
13.     new_switch_elapsed_time => -1,
    
14.     new_mgmt_p1 => 30, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
    
15.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
    
16.     new_parallel_degree_limit_p1 => NULL ,
    
17.     new_parallel_target_percentage => NULL ,
    
18.     new_parallel_queue_timeout => NULL ,
    
19.     new_parallel_stmt_critical => 'false' ,
    
20.     new_switch_io_logical => NULL ,
    
21.     new_switch_io_reqs => NULL,
    
22.     new_switch_io_megabytes => NULL ,
    
23.     new_active_sess_pool_p1 => NULL,
    
24.     new_queueing_p1 => NULL,
    
25.     new_switch_group => 'CANCEL_SQL',
    
26.     new_switch_time => NULL,
    
27.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
    
28.     new_undo_pool => NULL ,
    
29.     new_max_idle_time => NULL,
    
30.     new_max_idle_blocker_time => NULL,
    
31.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
32. 
    
33. );
    
34. dbms_resource_manager.submit_pending_area();
    
35. END;
    
36. 
    
37. 
    
38. 
    
39. begin
    
40.    dbms_resource_manager.clear_pending_area;
    
41.    dbms_resource_manager.create_pending_area;
    
42.    dbms_resource_manager.update_plan_directive(
    
43.     plan => 'PLAN1',
    
44.     group_or_subplan => 'GROUP1',
    
45.     new_switch_group => 'CANCEL_SQL',
    
46.         new_switch_io_megabytes =>1,
    
47.         new_switch_estimate => false
    
48.     );
    
49.         dbms_resource_manager.submit_pending_area();
    
50. END;
    
51. 
    
52. select d.GROUP_OR_SUBPLAN,
    
53.    d.SWITCH_ELAPSED_TIME,
    
54.    d.SWITCH_ESTIMATE,
    
55.    d.SWITCH_FOR_CALL,
    
56.    d.SWITCH_GROUP,
    
57.    d.SWITCH_IO_LOGICAL,
    
58.    d.SWITCH_IO_MEGABYTES,
    
59.    d.SWITCH_IO_REQS,
    
60.    d.SWITCH_TIME,
    
61.    d.SWITCH_TIME_IN_CALL
    
62.   
    
63.     from dba_rsrc_plan_directives d
    
64.   where d.PLAN='PLAN1';

复制代码

切换到别的组去：

1. begin
   
2.    dbms_resource_manager.create_pending_area;
   
3.    dbms_resource_manager.update_plan_directive(
   
4.     plan => 'PLAN1',
   
5.     group_or_subplan => 'GROUP1',
   
6.     new_undo_pool=> -1
   
7.     );
   
8.         dbms_resource_manager.submit_pending_area();
   
9. END;
   
10. 
    
11. begin
    
12.    dbms_resource_manager.clear_pending_area;
    
13.    dbms_resource_manager.create_pending_area;
    
14.    dbms_resource_manager.update_plan_directive(
    
15.     plan => 'PLAN1',
    
16.     group_or_subplan => 'GROUP1',
    
17.     new_switch_group => 'GROUP2',
    
18.         new_switch_io_megabytes =>3,
    
19.         new_switch_estimate => false,
    
20.         new_switch_for_call =>true
    
21.     );
    
22.         dbms_resource_manager.submit_pending_area();
    
23. END;
    
24. 
    
25. select d.GROUP_OR_SUBPLAN,
    
26.    d.SWITCH_ELAPSED_TIME,
    
27.    d.SWITCH_ESTIMATE,
    
28.    d.SWITCH_FOR_CALL,
    
29.    d.SWITCH_GROUP,
    
30.    d.SWITCH_IO_LOGICAL,
    
31.    d.SWITCH_IO_MEGABYTES,
    
32.    d.SWITCH_IO_REQS,
    
33.    d.SWITCH_TIME,
    
34.    d.SWITCH_TIME_IN_CALL
    
35.   
    
36.     from dba_rsrc_plan_directives d
    
37.   where d.PLAN='PLAN1';
    
38.   
    
39.   ----
    
40.   
    
41.   begin
    
42.    dbms_resource_manager.clear_pending_area;
    
43.    dbms_resource_manager.create_pending_area;
    
44.    dbms_resource_manager.update_plan_directive(
    
45.     plan => 'PLAN1',
    
46.     group_or_subplan => 'GROUP1',
    
47.     new_switch_group => 'GROUP2',
    
48.         new_switch_io_megabytes =>-1,
    
49.         new_switch_elapsed_time => 5,
    
50.         new_switch_estimate => false,
    
51.         new_switch_for_call =>true
    
52.     );
    
53.         dbms_resource_manager.submit_pending_area();
    
54. END;
    
55.   
    
56.   ---
    
57.   select d.GROUP_OR_SUBPLAN,
    
58.    d.SWITCH_ELAPSED_TIME,
    
59.    d.SWITCH_ESTIMATE,
    
60.    d.SWITCH_FOR_CALL,
    
61.    d.SWITCH_GROUP,
    
62.    d.SWITCH_IO_LOGICAL,
    
63.    d.SWITCH_IO_MEGABYTES,
    
64.    d.SWITCH_IO_REQS,
    
65.    d.SWITCH_TIME,
    
66.    d.SWITCH_TIME_IN_CALL
    
67.   
    
68.     from dba_rsrc_plan_directives d
    
69.   where d.PLAN='PLAN1';

复制代码

1. Resource Plans  >   Edit Resource Plan: PLAN1        Logged in as SYS
   
2. Show SQL
   
3.         
   
4.                         
   
5. DECLARE
   
6. spfileValue VARCHAR2(1000);
   
7. execText VARCHAR2(1000);
   
8. scopeValue VARCHAR2(30) := 'MEMORY';
   
9. planName VARCHAR2(100) :='PLAN1';
   
10. BEGIN
    
11. dbms_resource_manager.clear_pending_area();
    
12. dbms_resource_manager.create_pending_area();
    
13. dbms_resource_manager.update_plan_directive(
    
14.     plan => 'PLAN1',
    
15.     group_or_subplan => 'GROUP1',
    
16.     new_comment => '',
    
17.     new_switch_elapsed_time => -1,
    
18.     new_mgmt_p1 => 30, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
    
19.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
    
20.     new_parallel_degree_limit_p1 => NULL ,
    
21.     new_parallel_target_percentage => NULL ,
    
22.     new_parallel_queue_timeout => NULL ,
    
23.     new_parallel_stmt_critical => 'false' ,
    
24.     new_switch_io_logical => NULL ,
    
25.     new_switch_io_reqs => NULL,
    
26.     new_switch_io_megabytes => NULL ,
    
27.     new_active_sess_pool_p1 => NULL,
    
28.     new_queueing_p1 => NULL,
    
29.     new_switch_group => '',
    
30.     new_switch_time => NULL,
    
31.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
    
32.     new_undo_pool => NULL ,
    
33.     new_max_idle_time => 5,
    
34.     new_max_idle_blocker_time => 3,
    
35.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
36. 
    
37. );
    
38. dbms_resource_manager.submit_pending_area();
    
39. END;
    
40.                         
    

复制代码

保障上限：

1. DECLARE
   
2. spfileValue VARCHAR2(1000);
   
3. execText VARCHAR2(1000);
   
4. scopeValue VARCHAR2(30) := 'MEMORY';
   
5. planName VARCHAR2(100) :='PLAN1';
   
6. BEGIN
   
7. dbms_resource_manager.clear_pending_area();
   
8. dbms_resource_manager.create_pending_area();
   
9. dbms_resource_manager.update_plan_directive(
   
10.     plan => 'PLAN1',
    
11.     group_or_subplan => 'GROUP1',
    
12.     new_comment => '',
    
13.     new_switch_elapsed_time => NULL,
    
14.      new_max_utilization_limit => 100,
    
15.     new_mgmt_p1 => 30, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
    
16.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
    
17.     new_parallel_degree_limit_p1 => NULL ,
    
18.     new_parallel_target_percentage => NULL ,
    
19.     new_parallel_queue_timeout => NULL ,
    
20.     new_parallel_stmt_critical => 'false' ,
    
21.     new_switch_io_logical => NULL ,
    
22.     new_switch_io_reqs => NULL,
    
23.     new_switch_io_megabytes => NULL ,
    
24.     new_active_sess_pool_p1 => NULL,
    
25.     new_queueing_p1 => NULL,
    
26.     new_switch_group => 'GROUP2',
    
27.     new_switch_time => NULL,
    
28.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
    
29.     new_undo_pool => NULL ,
    
30.     new_max_idle_time => NULL,
    
31.     new_max_idle_blocker_time => NULL,
    
32.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
33. 
    
34. );
    
35. dbms_resource_manager.update_plan_directive(
    
36.     plan => 'PLAN1',
    
37.     group_or_subplan => 'GROUP2',
    
38.     new_comment => '',
    
39.     new_switch_elapsed_time => NULL,
    
40.      new_max_utilization_limit => 90,
    
41.     new_mgmt_p1 => 15, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
    
42.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
    
43.     new_parallel_degree_limit_p1 => NULL ,
    
44.     new_parallel_target_percentage => NULL ,
    
45.     new_parallel_queue_timeout => NULL ,
    
46.     new_parallel_stmt_critical => 'false' ,
    
47.     new_switch_io_logical => NULL ,
    
48.     new_switch_io_reqs => NULL,
    
49.     new_switch_io_megabytes => NULL ,
    
50.     new_active_sess_pool_p1 => NULL,
    
51.     new_queueing_p1 => NULL,
    
52.     new_switch_group => NULL,
    
53.     new_switch_time => NULL,
    
54.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
    
55.     new_undo_pool => NULL ,
    
56.     new_max_idle_time => NULL,
    
57.     new_max_idle_blocker_time => NULL,
    
58.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
59. 
    
60. );
    
61. dbms_resource_manager.update_plan_directive(
    
62.     plan => 'PLAN1',
    
63.     group_or_subplan => 'SYS_GROUP',
    
64.     new_comment => '',
    
65.     new_switch_elapsed_time => NULL,
    
66.      new_max_utilization_limit => 80,
    
67.     new_mgmt_p1 => 50, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
    
68.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
    
69.     new_parallel_degree_limit_p1 => NULL ,
    
70.     new_parallel_target_percentage => NULL ,
    
71.     new_parallel_queue_timeout => NULL ,
    
72.     new_parallel_stmt_critical => 'false' ,
    
73.     new_switch_io_logical => NULL ,
    
74.     new_switch_io_reqs => NULL,
    
75.     new_switch_io_megabytes => NULL ,
    
76.     new_active_sess_pool_p1 => NULL,
    
77.     new_queueing_p1 => NULL,
    
78.     new_switch_group => NULL,
    
79.     new_switch_time => NULL,
    
80.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
    
81.     new_undo_pool => NULL ,
    
82.     new_max_idle_time => NULL,
    
83.     new_max_idle_blocker_time => NULL,
    
84.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
85. 
    
86. );
    
87. dbms_resource_manager.update_plan_directive(
    
88.     plan => 'PLAN1',
    
89.     group_or_subplan => 'OTHER_GROUPS',
    
90.     new_comment => '',
    
91.     new_switch_elapsed_time => NULL,
    
92.      new_max_utilization_limit => 70,
    
93.     new_mgmt_p1 => 5, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
    
94.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
    
95.     new_parallel_degree_limit_p1 => NULL ,
    
96.     new_parallel_target_percentage => NULL ,
    
97.     new_parallel_queue_timeout => NULL ,
    
98.     new_parallel_stmt_critical => 'false' ,
    
99.     new_switch_io_logical => NULL ,
    
100.     new_switch_io_reqs => NULL,
     
101.     new_switch_io_megabytes => NULL ,
     
102.     new_active_sess_pool_p1 => NULL,
     
103.     new_queueing_p1 => NULL,
     
104.     new_switch_group => NULL,
     
105.     new_switch_time => NULL,
     
106.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
     
107.     new_undo_pool => NULL ,
     
108.     new_max_idle_time => NULL,
     
109.     new_max_idle_blocker_time => NULL,
     
110.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
     
111. 
     
112. );
     
113. dbms_resource_manager.submit_pending_area();
     
114. END;

复制代码

观察resource_consumer_group对cpu的使用：

1. SELECT begin_time, consumer_group_name, cpu_consumed_time, cpu_wait_time
   
2. FROM v$rsrcmgrmetric_history
   
3. ORDER BY begin_time;
   
4. 
   
5. 
   
6. SELECT name, consumed_cpu_time, cpu_wait_time
   
7. FROM v$rsrc_consumer_group;

复制代码

1. select  s.USERNAME, s.RESOURCE_CONSUMER_GROUP
   
2. from v$session s
   
3. where s.TERMINAL='pts/1';
   
4. 
   
5. select  s.USERNAME, s.RESOURCE_CONSUMER_GROUP,s.MACHINE
   
6. from v$session s
   
7. where s.TERMINAL='pts/0';
   
8. 
   
9. BEGIN
   
10. dbms_resource_manager.clear_pending_area();
    
11. dbms_resource_manager.create_pending_area();
    
12. dbms_resource_manager.set_consumer_group_mapping_pri(
    
13.     EXPLICIT => 1,  CLIENT_MACHINE => 2,
    
14.     SERVICE_MODULE_ACTION => 3,
    
15.     SERVICE_MODULE => 4,
    
16.     MODULE_NAME_ACTION => 5,
    
17.     MODULE_NAME => 6,
    
18.     SERVICE_NAME => 7,
    
19.     ORACLE_USER => 8,
    
20.     CLIENT_PROGRAM => 9,
    
21.     CLIENT_OS_USER => 10,
    
22.     CLIENT_ID => 11
    
23. );
    
24. dbms_resource_manager.submit_pending_area();
    
25. END;
    
26. 
    
27. ---
    
28. select  * from DBA_RSRC_MAPPING_PRIORITY;
    
29. 
    
30. select * from DBA_RSRC_GROUP_MAPPINGS;
    
31. 
    
32. BEGIN
    
33. dbms_resource_manager.clear_pending_area();
    
34. dbms_resource_manager.create_pending_area();
    
35. dbms_resource_manager.set_consumer_group_mapping(
    
36.     dbms_resource_manager.client_machine,
    
37.     '192.168.0.37',
    
38.     NULL
    
39. );
    
40. dbms_resource_manager.submit_pending_area();
    
41. END;
    
42. 
    
43. select * from DBA_RSRC_GROUP_MAPPINGS;
    
44. 
    
45. BEGIN
    
46. dbms_resource_manager.clear_pending_area();
    
47. dbms_resource_manager.create_pending_area();
    
48. dbms_resource_manager.set_consumer_group_mapping(
    
49.     dbms_resource_manager.client_machine,
    
50.     'station37.example.com',
    
51.     'group2'
    
52. );
    
53. dbms_resource_manager.submit_pending_area();
    
54. END;
    
55. 
    
56. select * from DBA_RSRC_GROUP_MAPPINGS;

复制代码

11g和12c  Simple Plan 的差别：

1. begin
   
2. dbms_resource_manager.create_simple_plan('PLAN7',
   
3.          'group1', 60 , 'group2' ,40,
   
4.                  GROUP1_PERCENT=>80,
   
5.                  GROUP2_PERCENT=>50);
   
6. end;
   
7. 
   
8. select  * from dba_rsrc_plan_directives where plan='PLAN7';
   
9. 
   
10. select  * from dba_rsrc_plans where plan='PLAN7';
    

复制代码

12c：

11g：