|
- select * from dba_rsrc_plans ;
- select * from dba_rsrc_plan_directives d
- where d.PLAN='PLAN3';
- select * from dba_rsrc_group_mappings;
- select * from dba_rsrc_consumer_groups;
- --------------------------
- DECLARE
- spfileValue VARCHAR2(1000);
- execText VARCHAR2(1000);
- scopeValue VARCHAR2(30) := 'MEMORY';
- planName VARCHAR2(100) :='PLAN4';
- BEGIN
- dbms_resource_manager.clear_pending_area();
- dbms_resource_manager.create_pending_area();
- dbms_resource_manager.create_plan( plan => 'PLAN4', comment => 'plan4',max_iops => NULL,max_mbps => NULL,cpu_mth=>'RATIO' );
- dbms_resource_manager.create_plan_directive(
- plan => 'PLAN4',
- group_or_subplan => 'GROUP1',
- comment => '',
- switch_elapsed_time => NULL,
- max_utilization_limit => '',
- mgmt_p1 => 100, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
- mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
- parallel_degree_limit_p1 => NULL ,
- parallel_target_percentage => NULL ,
- parallel_queue_timeout => NULL ,
- parallel_stmt_critical => 'false' ,
- switch_io_logical => NULL ,
- switch_io_reqs => NULL ,
- switch_io_megabytes => NULL ,
- active_sess_pool_p1 => NULL,
- queueing_p1 => NULL,
- switch_group => '',
- switch_time => NULL,
- switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
- undo_pool => NULL ,
- max_idle_time => NULL,
- max_idle_blocker_time => NULL,
- switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.create_plan_directive(
- plan => 'PLAN4',
- group_or_subplan => 'GROUP2',
- comment => '',
- switch_elapsed_time => NULL,
- max_utilization_limit => '',
- mgmt_p1 => 20, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
- mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
- parallel_degree_limit_p1 => NULL ,
- parallel_target_percentage => NULL ,
- parallel_queue_timeout => NULL ,
- parallel_stmt_critical => 'false' ,
- switch_io_logical => NULL ,
- switch_io_reqs => NULL ,
- switch_io_megabytes => NULL ,
- active_sess_pool_p1 => NULL,
- queueing_p1 => NULL,
- switch_group => '',
- switch_time => NULL,
- switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
- undo_pool => NULL ,
- max_idle_time => NULL,
- max_idle_blocker_time => NULL,
- switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.create_plan_directive(
- plan => 'PLAN4',
- group_or_subplan => 'OTHER_GROUPS',
- comment => '',
- switch_elapsed_time => NULL,
- max_utilization_limit => '',
- mgmt_p1 => 30, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
- mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
- parallel_degree_limit_p1 => NULL ,
- parallel_target_percentage => NULL ,
- parallel_queue_timeout => NULL ,
- parallel_stmt_critical => 'false' ,
- switch_io_logical => NULL ,
- switch_io_reqs => NULL ,
- switch_io_megabytes => NULL ,
- active_sess_pool_p1 => NULL,
- queueing_p1 => NULL,
- switch_group => '',
- switch_time => NULL,
- switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
- undo_pool => NULL ,
- max_idle_time => NULL,
- max_idle_blocker_time => NULL,
- switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.create_plan_directive(
- plan => 'PLAN4',
- group_or_subplan => 'SYS_GROUP',
- comment => '',
- switch_elapsed_time => NULL,
- max_utilization_limit => '',
- mgmt_p1 => 40, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
- mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
- parallel_degree_limit_p1 => NULL ,
- parallel_target_percentage => NULL ,
- parallel_queue_timeout => NULL ,
- parallel_stmt_critical => 'false' ,
- switch_io_logical => NULL ,
- switch_io_reqs => NULL ,
- switch_io_megabytes => NULL ,
- active_sess_pool_p1 => NULL,
- queueing_p1 => NULL,
- switch_group => '',
- switch_time => NULL,
- switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
- undo_pool => NULL ,
- max_idle_time => NULL,
- max_idle_blocker_time => NULL,
- switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.submit_pending_area();
- END;
- ------
- DECLARE
- spfileValue VARCHAR2(1000);
- execText VARCHAR2(1000);
- scopeValue VARCHAR2(30) := 'MEMORY';
- planName VARCHAR2(100) :='PLAN5';
- BEGIN
- dbms_resource_manager.clear_pending_area();
- dbms_resource_manager.create_pending_area();
- dbms_resource_manager.create_plan( plan => 'PLAN5', comment => 'plan5',max_iops => NULL,max_mbps => NULL );
- dbms_resource_manager.create_plan_directive(
- plan => 'PLAN5',
- group_or_subplan => 'GROUP1',
- comment => '',
- switch_elapsed_time => NULL,
- max_utilization_limit => '',
- mgmt_p1 => 10, mgmt_p2 => 20, mgmt_p3 => NULL, mgmt_p4 => NULL,
- mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
- parallel_degree_limit_p1 => NULL ,
- parallel_target_percentage => NULL ,
- parallel_queue_timeout => NULL ,
- parallel_stmt_critical => 'false' ,
- switch_io_logical => NULL ,
- switch_io_reqs => NULL ,
- switch_io_megabytes => NULL ,
- active_sess_pool_p1 => NULL,
- queueing_p1 => NULL,
- switch_group => '',
- switch_time => NULL,
- switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
- undo_pool => NULL ,
- max_idle_time => NULL,
- max_idle_blocker_time => NULL,
- switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.create_plan_directive(
- plan => 'PLAN5',
- group_or_subplan => 'GROUP2',
- comment => '',
- switch_elapsed_time => NULL,
- max_utilization_limit => '',
- mgmt_p1 => 20, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
- mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
- parallel_degree_limit_p1 => NULL ,
- parallel_target_percentage => NULL ,
- parallel_queue_timeout => NULL ,
- parallel_stmt_critical => 'false' ,
- switch_io_logical => NULL ,
- switch_io_reqs => NULL ,
- switch_io_megabytes => NULL ,
- active_sess_pool_p1 => NULL,
- queueing_p1 => NULL,
- switch_group => '',
- switch_time => NULL,
- switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
- undo_pool => NULL ,
- max_idle_time => NULL,
- max_idle_blocker_time => NULL,
- switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.create_plan_directive(
- plan => 'PLAN5',
- group_or_subplan => 'OTHER_GROUPS',
- comment => '',
- switch_elapsed_time => NULL,
- max_utilization_limit => '',
- mgmt_p1 => 30, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
- mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
- parallel_degree_limit_p1 => NULL ,
- parallel_target_percentage => NULL ,
- parallel_queue_timeout => NULL ,
- parallel_stmt_critical => 'false' ,
- switch_io_logical => NULL ,
- switch_io_reqs => NULL ,
- switch_io_megabytes => NULL ,
- active_sess_pool_p1 => NULL,
- queueing_p1 => NULL,
- switch_group => '',
- switch_time => NULL,
- switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
- undo_pool => NULL ,
- max_idle_time => NULL,
- max_idle_blocker_time => NULL,
- switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.create_plan_directive(
- plan => 'PLAN5',
- group_or_subplan => 'SYS_GROUP',
- comment => '',
- switch_elapsed_time => NULL,
- max_utilization_limit => '',
- mgmt_p1 => 40, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
- mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
- parallel_degree_limit_p1 => NULL ,
- parallel_target_percentage => NULL ,
- parallel_queue_timeout => NULL ,
- parallel_stmt_critical => 'false' ,
- switch_io_logical => NULL ,
- switch_io_reqs => NULL ,
- switch_io_megabytes => NULL ,
- active_sess_pool_p1 => NULL,
- queueing_p1 => NULL,
- switch_group => '',
- switch_time => NULL,
- switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
- undo_pool => NULL ,
- max_idle_time => NULL,
- max_idle_blocker_time => NULL,
- switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.submit_pending_area();
- ;
复制代码
- DECLARE
- spfileValue VARCHAR2(1000);
- execText VARCHAR2(1000);
- scopeValue VARCHAR2(30) := 'MEMORY';
- planName VARCHAR2(100) :='PLAN1';
- BEGIN
- dbms_resource_manager.clear_pending_area();
- dbms_resource_manager.create_pending_area();
- dbms_resource_manager.update_plan_directive(
- plan => 'PLAN1',
- group_or_subplan => 'GROUP1',
- new_comment => '',
- new_switch_elapsed_time => NULL,
- new_mgmt_p1 => 30, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
- new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
- new_parallel_degree_limit_p1 => 1 ,
- new_parallel_target_percentage => NULL ,
- new_parallel_queue_timeout => NULL ,
- new_parallel_stmt_critical => 'false' ,
- new_switch_io_logical => NULL ,
- new_switch_io_reqs => NULL,
- new_switch_io_megabytes => NULL ,
- new_active_sess_pool_p1 => NULL,
- new_queueing_p1 => NULL,
- new_switch_group => NULL,
- new_switch_time => NULL,
- new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
- new_undo_pool => NULL ,
- new_max_idle_time => NULL,
- new_max_idle_blocker_time => NULL,
- new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.update_plan_directive(
- plan => 'PLAN1',
- group_or_subplan => 'GROUP2',
- new_comment => '',
- new_switch_elapsed_time => NULL,
- new_mgmt_p1 => 15, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
- new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
- new_parallel_degree_limit_p1 => NULL ,
- new_parallel_target_percentage => NULL ,
- new_parallel_queue_timeout => NULL ,
- new_parallel_stmt_critical => 'false' ,
- new_switch_io_logical => NULL ,
- new_switch_io_reqs => NULL,
- new_switch_io_megabytes => NULL ,
- new_active_sess_pool_p1 => NULL,
- new_queueing_p1 => NULL,
- new_switch_group => NULL,
- new_switch_time => NULL,
- new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
- new_undo_pool => NULL ,
- new_max_idle_time => NULL,
- new_max_idle_blocker_time => NULL,
- new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.update_plan_directive(
- plan => 'PLAN1',
- group_or_subplan => 'SYS_GROUP',
- new_comment => '',
- new_switch_elapsed_time => NULL,
- new_mgmt_p1 => 50, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
- new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
- new_parallel_degree_limit_p1 => NULL ,
- new_parallel_target_percentage => NULL ,
- new_parallel_queue_timeout => NULL ,
- new_parallel_stmt_critical => 'false' ,
- new_switch_io_logical => NULL ,
- new_switch_io_reqs => NULL,
- new_switch_io_megabytes => NULL ,
- new_active_sess_pool_p1 => NULL,
- new_queueing_p1 => NULL,
- new_switch_group => NULL,
- new_switch_time => NULL,
- new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
- new_undo_pool => NULL ,
- new_max_idle_time => NULL,
- new_max_idle_blocker_time => NULL,
- new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.update_plan_directive(
- plan => 'PLAN1',
- group_or_subplan => 'OTHER_GROUPS',
- new_comment => '',
- new_switch_elapsed_time => NULL,
- new_mgmt_p1 => 5, new_mgmt_p2 => NULL, new_mgmt_p3 => NULL, new_mgmt_p4 => NULL,
- new_mgmt_p5 => NULL, new_mgmt_p6 => NULL, new_mgmt_p7 => NULL, new_mgmt_p8 => NULL ,
- new_parallel_degree_limit_p1 => NULL ,
- new_parallel_target_percentage => NULL ,
- new_parallel_queue_timeout => NULL ,
- new_parallel_stmt_critical => 'false' ,
- new_switch_io_logical => NULL ,
- new_switch_io_reqs => NULL,
- new_switch_io_megabytes => NULL ,
- new_active_sess_pool_p1 => NULL,
- new_queueing_p1 => NULL,
- new_switch_group => NULL,
- new_switch_time => NULL,
- new_switch_estimate => case 'false' when 'false' then false when 'true' then true else false end ,
- new_undo_pool => NULL ,
- new_max_idle_time => NULL,
- new_max_idle_blocker_time => NULL,
- new_switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
- );
- dbms_resource_manager.submit_pending_area();
- END;
-
复制代码
- [oracle@station26 ~]$ sqlplus /nolog
- SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 31 21:22:32 2017
- Copyright (c) 1982, 2014, Oracle. All rights reserved.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> select count(*) from t_big;
- COUNT(*)
- ----------
- 326347
- SQL> select * from v$pq_sesstat;
- STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
- ------------------------------ ---------- ------------- ----------
- Queries Parallelized 0 0 0
- DML Parallelized 0 0 0
- DDL Parallelized 0 0 0
- DFO Trees 0 0 0
- Server Threads 0 0 0
- Allocation Height 0 0 0
- Allocation Width 0 0 0
- Local Msgs Sent 0 0 0
- Distr Msgs Sent 0 0 0
- Local Msgs Recv'd 0 0 0
- Distr Msgs Recv'd 0 0 0
- STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
- ------------------------------ ---------- ------------- ----------
- DOP 0 0 0
- Slave Sets 0 0 0
- 13 rows selected.
- SQL> create index i_big on t_big(text ) parallel 20 ;
- Index created.
- SQL> select * from v$pq_sesstat;
- STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
- ------------------------------ ---------- ------------- ----------
- Queries Parallelized 0 0 0
- DML Parallelized 0 0 0
- DDL Parallelized 0 0 0
- DFO Trees 0 0 0
- Server Threads 0 0 0
- Allocation Height 0 0 0
- Allocation Width 0 0 0
- Local Msgs Sent 0 0 0
- Distr Msgs Sent 0 0 0
- Local Msgs Recv'd 0 0 0
- Distr Msgs Recv'd 0 0 0
- STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
- ------------------------------ ---------- ------------- ----------
- DOP 0 0 0
- Slave Sets 0 0 0
- 13 rows selected.
- SQL> drop index i_big;
- Index dropped.
- SQL> create index i_big on t_big(text ) parallel 20 ;
- Index created.
- SQL> select * from v$pq_sesstat;
- STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
- ------------------------------ ---------- ------------- ----------
- Queries Parallelized 0 0 0
- DML Parallelized 0 0 0
- DDL Parallelized 1 1 0
- DFO Trees 1 1 0
- Server Threads 40 0 0
- Allocation Height 20 0 0
- Allocation Width 1 0 0
- Local Msgs Sent 3269 3269 0
- Distr Msgs Sent 0 0 0
- Local Msgs Recv'd 3269 3269 0
- Distr Msgs Recv'd 0 0 0
- STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
- ------------------------------ ---------- ------------- ----------
- DOP 20 0 0
- Slave Sets 2 0 0
- 13 rows selected.
- SQL>
复制代码
|
|