课程第49次（2017-10-31星期二）

botang

1. select  * from dba_rsrc_plans  ;
   
2. 
   
3. select  * from dba_rsrc_plan_directives  d
   
4. where d.PLAN='PLAN3';
   
5. 
   
6. select * from dba_rsrc_group_mappings;
   
7. 
   
8. select  * from dba_rsrc_consumer_groups;
   
9. 
   
10. --------------------------
    
11. 
    
12. DECLARE
    
13. spfileValue VARCHAR2(1000);
    
14. execText VARCHAR2(1000);
    
15. scopeValue VARCHAR2(30) := 'MEMORY';
    
16. planName VARCHAR2(100) :='PLAN4';
    
17. BEGIN
    
18. dbms_resource_manager.clear_pending_area();
    
19. dbms_resource_manager.create_pending_area();
    
20. dbms_resource_manager.create_plan( plan => 'PLAN4', comment => 'plan4',max_iops => NULL,max_mbps => NULL,cpu_mth=>'RATIO' );
    
21. dbms_resource_manager.create_plan_directive(
    
22.     plan => 'PLAN4',
    
23.     group_or_subplan => 'GROUP1',
    
24.     comment => '',
    
25.     switch_elapsed_time => NULL,
    
26.      max_utilization_limit => '',
    
27.     mgmt_p1 => 100, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
    
28.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
    
29.     parallel_degree_limit_p1 => NULL ,
    
30.     parallel_target_percentage => NULL ,
    
31.     parallel_queue_timeout => NULL ,
    
32.     parallel_stmt_critical => 'false' ,
    
33.     switch_io_logical => NULL ,
    
34.     switch_io_reqs => NULL ,
    
35.    switch_io_megabytes => NULL ,
    
36.     active_sess_pool_p1 => NULL,
    
37.     queueing_p1 => NULL,
    
38.     switch_group => '',
    
39.     switch_time => NULL,
    
40.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
    
41.     undo_pool => NULL ,
    
42.     max_idle_time => NULL,
    
43.     max_idle_blocker_time => NULL,
    
44.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
45. 
    
46. );
    
47. dbms_resource_manager.create_plan_directive(
    
48.     plan => 'PLAN4',
    
49.     group_or_subplan => 'GROUP2',
    
50.     comment => '',
    
51.     switch_elapsed_time => NULL,
    
52.      max_utilization_limit => '',
    
53.     mgmt_p1 => 20, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
    
54.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
    
55.     parallel_degree_limit_p1 => NULL ,
    
56.     parallel_target_percentage => NULL ,
    
57.     parallel_queue_timeout => NULL ,
    
58.     parallel_stmt_critical => 'false' ,
    
59.     switch_io_logical => NULL ,
    
60.     switch_io_reqs => NULL ,
    
61.    switch_io_megabytes => NULL ,
    
62.     active_sess_pool_p1 => NULL,
    
63.     queueing_p1 => NULL,
    
64.     switch_group => '',
    
65.     switch_time => NULL,
    
66.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
    
67.     undo_pool => NULL ,
    
68.     max_idle_time => NULL,
    
69.     max_idle_blocker_time => NULL,
    
70.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
71. 
    
72. );
    
73. dbms_resource_manager.create_plan_directive(
    
74.     plan => 'PLAN4',
    
75.     group_or_subplan => 'OTHER_GROUPS',
    
76.     comment => '',
    
77.     switch_elapsed_time => NULL,
    
78.      max_utilization_limit => '',
    
79.     mgmt_p1 => 30, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
    
80.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
    
81.     parallel_degree_limit_p1 => NULL ,
    
82.     parallel_target_percentage => NULL ,
    
83.     parallel_queue_timeout => NULL ,
    
84.     parallel_stmt_critical => 'false' ,
    
85.     switch_io_logical => NULL ,
    
86.     switch_io_reqs => NULL ,
    
87.    switch_io_megabytes => NULL ,
    
88.     active_sess_pool_p1 => NULL,
    
89.     queueing_p1 => NULL,
    
90.     switch_group => '',
    
91.     switch_time => NULL,
    
92.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
    
93.     undo_pool => NULL ,
    
94.     max_idle_time => NULL,
    
95.     max_idle_blocker_time => NULL,
    
96.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
97. 
    
98. );
    
99. dbms_resource_manager.create_plan_directive(
    
100.     plan => 'PLAN4',
     
101.     group_or_subplan => 'SYS_GROUP',
     
102.     comment => '',
     
103.     switch_elapsed_time => NULL,
     
104.      max_utilization_limit => '',
     
105.     mgmt_p1 => 40, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
     
106.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
     
107.     parallel_degree_limit_p1 => NULL ,
     
108.     parallel_target_percentage => NULL ,
     
109.     parallel_queue_timeout => NULL ,
     
110.     parallel_stmt_critical => 'false' ,
     
111.     switch_io_logical => NULL ,
     
112.     switch_io_reqs => NULL ,
     
113.    switch_io_megabytes => NULL ,
     
114.     active_sess_pool_p1 => NULL,
     
115.     queueing_p1 => NULL,
     
116.     switch_group => '',
     
117.     switch_time => NULL,
     
118.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
     
119.     undo_pool => NULL ,
     
120.     max_idle_time => NULL,
     
121.     max_idle_blocker_time => NULL,
     
122.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
     
123. 
     
124. );
     
125. dbms_resource_manager.submit_pending_area();
     
126. END;
     
127. 
     
128. 
     
129. ------
     
130. 
     
131. DECLARE
     
132. spfileValue VARCHAR2(1000);
     
133. execText VARCHAR2(1000);
     
134. scopeValue VARCHAR2(30) := 'MEMORY';
     
135. planName VARCHAR2(100) :='PLAN5';
     
136. BEGIN
     
137. dbms_resource_manager.clear_pending_area();
     
138. dbms_resource_manager.create_pending_area();
     
139. dbms_resource_manager.create_plan( plan => 'PLAN5', comment => 'plan5',max_iops => NULL,max_mbps => NULL );
     
140. dbms_resource_manager.create_plan_directive(
     
141.     plan => 'PLAN5',
     
142.     group_or_subplan => 'GROUP1',
     
143.     comment => '',
     
144.     switch_elapsed_time => NULL,
     
145.      max_utilization_limit => '',
     
146.     mgmt_p1 => 10, mgmt_p2 => 20, mgmt_p3 => NULL, mgmt_p4 => NULL,
     
147.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
     
148.     parallel_degree_limit_p1 => NULL ,
     
149.     parallel_target_percentage => NULL ,
     
150.     parallel_queue_timeout => NULL ,
     
151.     parallel_stmt_critical => 'false' ,
     
152.     switch_io_logical => NULL ,
     
153.     switch_io_reqs => NULL ,
     
154.    switch_io_megabytes => NULL ,
     
155.     active_sess_pool_p1 => NULL,
     
156.     queueing_p1 => NULL,
     
157.     switch_group => '',
     
158.     switch_time => NULL,
     
159.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
     
160.     undo_pool => NULL ,
     
161.     max_idle_time => NULL,
     
162.     max_idle_blocker_time => NULL,
     
163.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
     
164. 
     
165. );
     
166. dbms_resource_manager.create_plan_directive(
     
167.     plan => 'PLAN5',
     
168.     group_or_subplan => 'GROUP2',
     
169.     comment => '',
     
170.     switch_elapsed_time => NULL,
     
171.      max_utilization_limit => '',
     
172.     mgmt_p1 => 20, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
     
173.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
     
174.     parallel_degree_limit_p1 => NULL ,
     
175.     parallel_target_percentage => NULL ,
     
176.     parallel_queue_timeout => NULL ,
     
177.     parallel_stmt_critical => 'false' ,
     
178.     switch_io_logical => NULL ,
     
179.     switch_io_reqs => NULL ,
     
180.    switch_io_megabytes => NULL ,
     
181.     active_sess_pool_p1 => NULL,
     
182.     queueing_p1 => NULL,
     
183.     switch_group => '',
     
184.     switch_time => NULL,
     
185.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
     
186.     undo_pool => NULL ,
     
187.     max_idle_time => NULL,
     
188.     max_idle_blocker_time => NULL,
     
189.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
     
190. 
     
191. );
     
192. dbms_resource_manager.create_plan_directive(
     
193.     plan => 'PLAN5',
     
194.     group_or_subplan => 'OTHER_GROUPS',
     
195.     comment => '',
     
196.     switch_elapsed_time => NULL,
     
197.      max_utilization_limit => '',
     
198.     mgmt_p1 => 30, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
     
199.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
     
200.     parallel_degree_limit_p1 => NULL ,
     
201.     parallel_target_percentage => NULL ,
     
202.     parallel_queue_timeout => NULL ,
     
203.     parallel_stmt_critical => 'false' ,
     
204.     switch_io_logical => NULL ,
     
205.     switch_io_reqs => NULL ,
     
206.    switch_io_megabytes => NULL ,
     
207.     active_sess_pool_p1 => NULL,
     
208.     queueing_p1 => NULL,
     
209.     switch_group => '',
     
210.     switch_time => NULL,
     
211.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
     
212.     undo_pool => NULL ,
     
213.     max_idle_time => NULL,
     
214.     max_idle_blocker_time => NULL,
     
215.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
     
216. 
     
217. );
     
218. dbms_resource_manager.create_plan_directive(
     
219.     plan => 'PLAN5',
     
220.     group_or_subplan => 'SYS_GROUP',
     
221.     comment => '',
     
222.     switch_elapsed_time => NULL,
     
223.      max_utilization_limit => '',
     
224.     mgmt_p1 => 40, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
     
225.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
     
226.     parallel_degree_limit_p1 => NULL ,
     
227.     parallel_target_percentage => NULL ,
     
228.     parallel_queue_timeout => NULL ,
     
229.     parallel_stmt_critical => 'false' ,
     
230.     switch_io_logical => NULL ,
     
231.     switch_io_reqs => NULL ,
     
232.    switch_io_megabytes => NULL ,
     
233.     active_sess_pool_p1 => NULL,
     
234.     queueing_p1 => NULL,
     
235.     switch_group => '',
     
236.     switch_time => NULL,
     
237.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
     
238.     undo_pool => NULL ,
     
239.     max_idle_time => NULL,
     
240.     max_idle_blocker_time => NULL,
     
241.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
     
242. 
     
243. );
     
244. dbms_resource_manager.submit_pending_area();
     
245. ;

复制代码

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_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 => 1 ,
    
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 => NULL,
    
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.update_plan_directive(
    
35.     plan => 'PLAN1',
    
36.     group_or_subplan => 'GROUP2',
    
37.     new_comment => '',
    
38.     new_switch_elapsed_time => NULL,
    
39.     new_mgmt_p1 => 15, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
    
40.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
    
41.     new_parallel_degree_limit_p1 => NULL ,
    
42.     new_parallel_target_percentage => NULL ,
    
43.     new_parallel_queue_timeout => NULL ,
    
44.     new_parallel_stmt_critical => 'false' ,
    
45.     new_switch_io_logical => NULL ,
    
46.     new_switch_io_reqs => NULL,
    
47.     new_switch_io_megabytes => NULL ,
    
48.     new_active_sess_pool_p1 => NULL,
    
49.     new_queueing_p1 => NULL,
    
50.     new_switch_group => NULL,
    
51.     new_switch_time => NULL,
    
52.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
    
53.     new_undo_pool => NULL ,
    
54.     new_max_idle_time => NULL,
    
55.     new_max_idle_blocker_time => NULL,
    
56.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
57. 
    
58. );
    
59. dbms_resource_manager.update_plan_directive(
    
60.     plan => 'PLAN1',
    
61.     group_or_subplan => 'SYS_GROUP',
    
62.     new_comment => '',
    
63.     new_switch_elapsed_time => NULL,
    
64.     new_mgmt_p1 => 50, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
    
65.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
    
66.     new_parallel_degree_limit_p1 => NULL ,
    
67.     new_parallel_target_percentage => NULL ,
    
68.     new_parallel_queue_timeout => NULL ,
    
69.     new_parallel_stmt_critical => 'false' ,
    
70.     new_switch_io_logical => NULL ,
    
71.     new_switch_io_reqs => NULL,
    
72.     new_switch_io_megabytes => NULL ,
    
73.     new_active_sess_pool_p1 => NULL,
    
74.     new_queueing_p1 => NULL,
    
75.     new_switch_group => NULL,
    
76.     new_switch_time => NULL,
    
77.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
    
78.     new_undo_pool => NULL ,
    
79.     new_max_idle_time => NULL,
    
80.     new_max_idle_blocker_time => NULL,
    
81.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
82. 
    
83. );
    
84. dbms_resource_manager.update_plan_directive(
    
85.     plan => 'PLAN1',
    
86.     group_or_subplan => 'OTHER_GROUPS',
    
87.     new_comment => '',
    
88.     new_switch_elapsed_time => NULL,
    
89.     new_mgmt_p1 => 5, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
    
90.     new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
    
91.     new_parallel_degree_limit_p1 => NULL ,
    
92.     new_parallel_target_percentage => NULL ,
    
93.     new_parallel_queue_timeout => NULL ,
    
94.     new_parallel_stmt_critical => 'false' ,
    
95.     new_switch_io_logical => NULL ,
    
96.     new_switch_io_reqs => NULL,
    
97.     new_switch_io_megabytes => NULL ,
    
98.     new_active_sess_pool_p1 => NULL,
    
99.     new_queueing_p1 => NULL,
    
100.     new_switch_group => NULL,
     
101.     new_switch_time => NULL,
     
102.     new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
     
103.     new_undo_pool => NULL ,
     
104.     new_max_idle_time => NULL,
     
105.     new_max_idle_blocker_time => NULL,
     
106.     new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
     
107. 
     
108. );
     
109. dbms_resource_manager.submit_pending_area();
     
110. END;
     
111.                         
     

复制代码

1. [oracle@station26 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 31 21:22:32 2017
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn hr/oracle_4U
   
8. Connected.
   
9. SQL> select  count(*) from t_big;
   
10. 
    
11.   COUNT(*)
    
12. ----------
    
13.     326347
    
14. 
    
15. SQL> select  * from v$pq_sesstat;
    
16. 
    
17. STATISTIC                       LAST_QUERY SESSION_TOTAL     CON_ID
    
18. ------------------------------ ---------- ------------- ----------
    
19. Queries Parallelized                        0              0          0
    
20. DML Parallelized                        0              0          0
    
21. DDL Parallelized                        0              0          0
    
22. DFO Trees                                0              0          0
    
23. Server Threads                                0              0          0
    
24. Allocation Height                        0              0          0
    
25. Allocation Width                        0              0          0
    
26. Local Msgs Sent                         0              0          0
    
27. Distr Msgs Sent                         0              0          0
    
28. Local Msgs Recv'd                        0              0          0
    
29. Distr Msgs Recv'd                        0              0          0
    
30. 
    
31. STATISTIC                       LAST_QUERY SESSION_TOTAL     CON_ID
    
32. ------------------------------ ---------- ------------- ----------
    
33. DOP                                        0              0          0
    
34. Slave Sets                                0              0          0
    
35. 
    
36. 13 rows selected.
    
37. 
    
38. SQL> create  index i_big on t_big(text )  parallel  20 ;
    
39. 
    
40. Index created.
    
41. 
    
42. SQL> select  * from v$pq_sesstat;
    
43. 
    
44. STATISTIC                       LAST_QUERY SESSION_TOTAL     CON_ID
    
45. ------------------------------ ---------- ------------- ----------
    
46. Queries Parallelized                        0              0          0
    
47. DML Parallelized                        0              0          0
    
48. DDL Parallelized                        0              0          0
    
49. DFO Trees                                0              0          0
    
50. Server Threads                                0              0          0
    
51. Allocation Height                        0              0          0
    
52. Allocation Width                        0              0          0
    
53. Local Msgs Sent                         0              0          0
    
54. Distr Msgs Sent                         0              0          0
    
55. Local Msgs Recv'd                        0              0          0
    
56. Distr Msgs Recv'd                        0              0          0
    
57. 
    
58. STATISTIC                       LAST_QUERY SESSION_TOTAL     CON_ID
    
59. ------------------------------ ---------- ------------- ----------
    
60. DOP                                        0              0          0
    
61. Slave Sets                                0              0          0
    
62. 
    
63. 13 rows selected.
    
64. 
    
65. SQL> drop index i_big;
    
66. 
    
67. Index dropped.
    
68. 
    
69. SQL> create  index i_big on t_big(text )  parallel  20 ;
    
70. 
    
71. Index created.
    
72. 
    
73. SQL> select  * from v$pq_sesstat;
    
74. 
    
75. STATISTIC                       LAST_QUERY SESSION_TOTAL     CON_ID
    
76. ------------------------------ ---------- ------------- ----------
    
77. Queries Parallelized                        0              0          0
    
78. DML Parallelized                        0              0          0
    
79. DDL Parallelized                        1              1          0
    
80. DFO Trees                                1              1          0
    
81. Server Threads                               40              0          0
    
82. Allocation Height                       20              0          0
    
83. Allocation Width                        1              0          0
    
84. Local Msgs Sent                      3269           3269          0
    
85. Distr Msgs Sent                         0              0          0
    
86. Local Msgs Recv'd                     3269           3269          0
    
87. Distr Msgs Recv'd                        0              0          0
    
88. 
    
89. STATISTIC                       LAST_QUERY SESSION_TOTAL     CON_ID
    
90. ------------------------------ ---------- ------------- ----------
    
91. DOP                                       20              0          0
    
92. Slave Sets                                2              0          0
    
93. 
    
94. 13 rows selected.
    
95. 
    
96. SQL>
    

复制代码