|
1Z0-053第16章
1Z0-052共19章(上完13章),1Z0-053共21章(上完16章)和1Z0-063多租户部分共9章(上完0章)
总共上完全部49章中的29章- select username , status from v$session
- where terminal='pts/0';
-
- select * from v$transaction ;
-
- select * from dba_rsrc_plan_directives d
- where d.PLAN='PLAN1';
-
- begin
- dbms_resource_manager.create_pending_area;
- dbms_resource_manager.update_plan_directive(
- plan => 'PLAN1',
- group_or_subplan => 'GROUP1',
- new_active_sess_pool_p1 => 2,
- new_queueing_p1 => 3);
- dbms_resource_manager.submit_pending_area();
- END;
- select d.GROUP_OR_SUBPLAN,
-
- d.ACTIVE_SESS_POOL_P1, d.QUEUEING_P1 from dba_rsrc_plan_directives d
- where d.PLAN='PLAN1';
-
- ------
-
- select s.sid, terminal ,
- s.RESOURCE_CONSUMER_GROUP ,s.STATUS
- from v$session s where terminal in ('pts/2', 'pts/0','pts/1');
复制代码
- begin
- dbms_resource_manager.create_pending_area;
- dbms_resource_manager.update_plan_directive(
- plan => 'PLAN1',
- group_or_subplan => 'GROUP1',
- new_active_sess_pool_p1 => -1,
- new_queueing_p1 => -1);
- dbms_resource_manager.submit_pending_area();
- END;
- select d.GROUP_OR_SUBPLAN,
- d.ACTIVE_SESS_POOL_P1, d.QUEUEING_P1 from dba_rsrc_plan_directives d
- where d.PLAN='PLAN1';
-
- begin
- dbms_resource_manager.create_pending_area;
- dbms_resource_manager.update_plan_directive(
- plan => 'PLAN1',
- group_or_subplan => 'GROUP1',
- new_undo_pool=> 8
- );
- dbms_resource_manager.submit_pending_area();
- END;
-
-
- select d.GROUP_OR_SUBPLAN,
- d.UNDO_POOL from dba_rsrc_plan_directives d
- where d.PLAN='PLAN1';
复制代码
12c在使用预估时有问题:
- begin
- dbms_resource_manager.create_pending_area;
- dbms_resource_manager.update_plan_directive(
- plan => 'PLAN1',
- group_or_subplan => 'GROUP1',
- new_switch_group => 'CANCEL_SQL',
- new_switch_elapsed_time => 5,
- new_switch_estimate => true
- );
- dbms_resource_manager.submit_pending_area();
- END;
- select d.GROUP_OR_SUBPLAN,
- d.SWITCH_ELAPSED_TIME,
- d.SWITCH_ESTIMATE,
- d.SWITCH_FOR_CALL,
- d.SWITCH_GROUP,
- d.SWITCH_IO_LOGICAL,
- d.SWITCH_IO_MEGABYTES,
- d.SWITCH_IO_REQS,
- d.SWITCH_TIME,
- d.SWITCH_TIME_IN_CALL
-
- from dba_rsrc_plan_directives d
- where d.PLAN='PLAN1';
-
- begin
- dbms_resource_manager.create_pending_area;
- dbms_resource_manager.update_plan_directive(
- plan => 'PLAN1',
- group_or_subplan => 'GROUP1',
- new_switch_group => 'CANCEL_SQL',
- new_switch_elapsed_time => 5,
- new_switch_estimate => false
- );
- dbms_resource_manager.submit_pending_area();
- END;
- select d.GROUP_OR_SUBPLAN,
- d.SWITCH_ELAPSED_TIME,
- d.SWITCH_ESTIMATE,
- d.SWITCH_FOR_CALL,
- d.SWITCH_GROUP,
- d.SWITCH_IO_LOGICAL,
- d.SWITCH_IO_MEGABYTES,
- d.SWITCH_IO_REQS,
- d.SWITCH_TIME,
- d.SWITCH_TIME_IN_CALL
-
- from dba_rsrc_plan_directives d
- where d.PLAN='PLAN1';
复制代码- root@127.0.0.1's password:
- Last login: Tue Oct 31 19:55:42 2017 from station29.example.com
- [root@station26 ~]# su - oracle
- [oracle@station26 ~]$ sqlplus /nolog
- SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 2 19:10:35 2017
- Copyright (c) 1982, 2014, Oracle. All rights reserved.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> !ps
- PID TTY TIME CMD
- 7778 pts/0 00:00:00 bash
- 7802 pts/0 00:00:00 sqlplus
- 7821 pts/0 00:00:00 ps
- SQL> update employees set salary=salary*2 where employee_id=100;
- 1 row updated.
- SQL> commit;
- Commit complete.
- SQL> select count(*) from t_big;
- COUNT(*)
- ----------
- 326347
- SQL> select count(*) from t_big a, departments b ;
- COUNT(*)
- ----------
- 8811369
- SQL> select count(*) from t_big a, departments b, departments c;
- COUNT(*)
- ----------
- 237906963
- SQL> !ps
- PID TTY TIME CMD
- 7778 pts/0 00:00:00 bash
- 7802 pts/0 00:00:00 sqlplus
- 8803 pts/0 00:00:00 ps
- SQL> drop index i_big ;
- Index dropped.
- SQL> desc t_big
- Name Null? Type
- ----------------------------------------- -------- ----------------------------
- OWNER VARCHAR2(128)
- NAME VARCHAR2(128)
- TYPE VARCHAR2(12)
- LINE NUMBER
- TEXT VARCHAR2(4000)
- ORIGIN_CON_ID NUMBER
- SQL> create index i_big on t_big ( text , dump(text) , OWNER, NAME, TYPE, LINE , ORIGIN_CON_ID ) parallel 20 ;
- create index i_big on t_big ( text , dump(text) , OWNER, NAME, TYPE, LINE , ORIGIN_CON_ID ) parallel 20
- *
- ERROR at line 1:
- ORA-01450: maximum key length (6398) exceeded
- SQL> create index i_big on t_big ( text , substr(dump(text),1,20) , OWNER, NAME, TYPE, LINE , ORIGIN_CON_ID ) 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 4530 4530 0
- Distr Msgs Sent 0 0 0
- Local Msgs Recv'd 4530 4530 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> drop index i_big;
- Index dropped.
- SQL> create index i_big on t_big ( text , substr(dump(text),1,20) , OWNER, NAME, TYPE, LINE , ORIGIN_CON_ID ) parallel 20 ;
- Index created.
- SQL> drop index i_big;
- Index dropped.
- 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 ;
- Index created.
- SQL> drop index i_big;
- Index dropped.
- SQL> select count(*) from t_big a , t_big b;
- ^Cselect count(*) from t_big a , t_big b
- *
- ERROR at line 1:
- ORA-01013: user requested cancel of current operation
- SQL> conn hr/oracle_4U
- Connected.
- SQL> create table t05316_a ( a char(2000)) ;
- Table created.
- SQL> create table t05316_b ( a char(2000)) ;
- Table created.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> conn / as sysdba
- Connected.
- SQL> insert into hr.t05316_a values ('A') ;
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> insert into hr.t05316_b values ('X') ;
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> update t05316_a set a='B';
- 1 row updated.
- SQL> update t05316_a set a='C';
- 1 row updated.
- SQL> exec dbms_stats.gather_table_stat('HR','T_BIG') ;
- BEGIN dbms_stats.gather_table_stat('HR','T_BIG') ; END;
- *
- ERROR at line 1:
- ORA-06550: line 1, column 18:
- PLS-00302: component 'GATHER_TABLE_STAT' must be declared
- ORA-06550: line 1, column 7:
- PL/SQL: Statement ignored
- SQL> exec dbms_stats.gather_table_stats('HR','T_BIG') ;
- PL/SQL procedure successfully completed.
- SQL> select count(*) from t_big a , departments b, departments c;
- select count(*) from t_big a , departments b, departments c
- *
- ERROR at line 1:
- ORA-56735: elapsed time limit exceeded - call aborted
- SQL> select count(*) from t_big a , departments b, departments c;
- select count(*) from t_big a , departments b, departments c
- *
- ERROR at line 1:
- ORA-56735: elapsed time limit exceeded - call aborted
- SQL>
复制代码
- 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 => -1,
- 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 => 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 => 'CANCEL_SQL',
- 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;
-
- 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_switch_group => 'CANCEL_SQL',
- new_switch_io_megabytes =>1,
- new_switch_estimate => false
- );
- dbms_resource_manager.submit_pending_area();
- END;
- select d.GROUP_OR_SUBPLAN,
- d.SWITCH_ELAPSED_TIME,
- d.SWITCH_ESTIMATE,
- d.SWITCH_FOR_CALL,
- d.SWITCH_GROUP,
- d.SWITCH_IO_LOGICAL,
- d.SWITCH_IO_MEGABYTES,
- d.SWITCH_IO_REQS,
- d.SWITCH_TIME,
- d.SWITCH_TIME_IN_CALL
-
- from dba_rsrc_plan_directives d
- where d.PLAN='PLAN1';
复制代码 切换到别的组去:
- begin
- dbms_resource_manager.create_pending_area;
- dbms_resource_manager.update_plan_directive(
- plan => 'PLAN1',
- group_or_subplan => 'GROUP1',
- new_undo_pool=> -1
- );
- dbms_resource_manager.submit_pending_area();
- END;
- 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_switch_group => 'GROUP2',
- new_switch_io_megabytes =>3,
- new_switch_estimate => false,
- new_switch_for_call =>true
- );
- dbms_resource_manager.submit_pending_area();
- END;
- select d.GROUP_OR_SUBPLAN,
- d.SWITCH_ELAPSED_TIME,
- d.SWITCH_ESTIMATE,
- d.SWITCH_FOR_CALL,
- d.SWITCH_GROUP,
- d.SWITCH_IO_LOGICAL,
- d.SWITCH_IO_MEGABYTES,
- d.SWITCH_IO_REQS,
- d.SWITCH_TIME,
- d.SWITCH_TIME_IN_CALL
-
- from dba_rsrc_plan_directives d
- where d.PLAN='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_switch_group => 'GROUP2',
- new_switch_io_megabytes =>-1,
- new_switch_elapsed_time => 5,
- new_switch_estimate => false,
- new_switch_for_call =>true
- );
- dbms_resource_manager.submit_pending_area();
- END;
-
- ---
- select d.GROUP_OR_SUBPLAN,
- d.SWITCH_ELAPSED_TIME,
- d.SWITCH_ESTIMATE,
- d.SWITCH_FOR_CALL,
- d.SWITCH_GROUP,
- d.SWITCH_IO_LOGICAL,
- d.SWITCH_IO_MEGABYTES,
- d.SWITCH_IO_REQS,
- d.SWITCH_TIME,
- d.SWITCH_TIME_IN_CALL
-
- from dba_rsrc_plan_directives d
- where d.PLAN='PLAN1';
复制代码
- Resource Plans > Edit Resource Plan: PLAN1 Logged in as SYS
- Show SQL
-
-
- 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 => -1,
- 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 => 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 => '',
- 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 => 5,
- new_max_idle_blocker_time => 3,
- new_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) :='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_max_utilization_limit => 100,
- 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 => 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 => 'GROUP2',
- 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_max_utilization_limit => 90,
- 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_max_utilization_limit => 80,
- 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_max_utilization_limit => 70,
- 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;
复制代码 观察resource_consumer_group对cpu的使用:
- SELECT begin_time, consumer_group_name, cpu_consumed_time, cpu_wait_time
- FROM v$rsrcmgrmetric_history
- ORDER BY begin_time;
- SELECT name, consumed_cpu_time, cpu_wait_time
- FROM v$rsrc_consumer_group;
复制代码- select s.USERNAME, s.RESOURCE_CONSUMER_GROUP
- from v$session s
- where s.TERMINAL='pts/1';
-
- select s.USERNAME, s.RESOURCE_CONSUMER_GROUP,s.MACHINE
- from v$session s
- where s.TERMINAL='pts/0';
-
- BEGIN
- dbms_resource_manager.clear_pending_area();
- dbms_resource_manager.create_pending_area();
- dbms_resource_manager.set_consumer_group_mapping_pri(
- EXPLICIT => 1, CLIENT_MACHINE => 2,
- SERVICE_MODULE_ACTION => 3,
- SERVICE_MODULE => 4,
- MODULE_NAME_ACTION => 5,
- MODULE_NAME => 6,
- SERVICE_NAME => 7,
- ORACLE_USER => 8,
- CLIENT_PROGRAM => 9,
- CLIENT_OS_USER => 10,
- CLIENT_ID => 11
- );
- dbms_resource_manager.submit_pending_area();
- END;
-
- ---
- select * from DBA_RSRC_MAPPING_PRIORITY;
-
- select * from DBA_RSRC_GROUP_MAPPINGS;
- BEGIN
- dbms_resource_manager.clear_pending_area();
- dbms_resource_manager.create_pending_area();
- dbms_resource_manager.set_consumer_group_mapping(
- dbms_resource_manager.client_machine,
- '192.168.0.37',
- NULL
- );
- dbms_resource_manager.submit_pending_area();
- END;
- select * from DBA_RSRC_GROUP_MAPPINGS;
-
- BEGIN
- dbms_resource_manager.clear_pending_area();
- dbms_resource_manager.create_pending_area();
- dbms_resource_manager.set_consumer_group_mapping(
- dbms_resource_manager.client_machine,
- 'station37.example.com',
- 'group2'
- );
- dbms_resource_manager.submit_pending_area();
- END;
- select * from DBA_RSRC_GROUP_MAPPINGS;
复制代码
11g和12c Simple Plan 的差别:
- begin
- dbms_resource_manager.create_simple_plan('PLAN7',
- 'group1', 60 , 'group2' ,40,
- GROUP1_PERCENT=>80,
- GROUP2_PERCENT=>50);
- end;
-
- select * from dba_rsrc_plan_directives where plan='PLAN7';
-
- select * from dba_rsrc_plans where plan='PLAN7';
-
复制代码
12c:
11g:
|
|