Bo's Oracle Station

查看: 2710|回复: 0

课程第28次(2018-08-21星期二)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-8-21 19:38:04 | 显示全部楼层 |阅读模式
----------------UNDO池:
2.png
  1. select  pd.group_or_subplan, pd.undo_pool
  2. from dba_rsrc_plan_directives  pd
  3.   where pd.plan='PLAN1';
复制代码
   GROUP_OR_SUBPLANUNDO_POOL
1GROUP18
2GROUP2
3SYS_GROUP
4OTHER_GROUPS
5PLAN2

  1. select  s.USERNAME ,s.TERMINAL, s.RESOURCE_CONSUMER_GROUP
  2.   from v_$session s
  3.   where s.TERMINAL  in ('pts/5','pts/8');
复制代码
   USERNAMETERMINALRESOURCE_CONSUMER_GROUP
1HRpts/5GROUP1
2HRpts/8GROUP1

准备实验环境:
  1. create table hr.t05316_undo( a char(2000)) ;

  2. create table hr.t05316_undo2( a char(2000)) ;

  3. insert into hr.t05316_undo values ('A');

  4. insert into hr.t05316_undo2 values('X');

  5. commit;
复制代码

UNDO池是两个表加起来,两个用户(会话)也加起来算总量:
3.png


------------------------------------组间切换, 要切入的组,即使其活跃会话指标满了,也能切进去:

4.png

  1.     select  pd.group_or_subplan, pd.active_sess_pool_p1 , pd.queueing_p1
  2.     from dba_rsrc_plan_directives  pd
  3.       where pd.plan='PLAN1';
复制代码
   GROUP_OR_SUBPLANACTIVE_SESS_POOL_P1QUEUEING_P1
1GROUP1
2GROUP211
3SYS_GROUP
4OTHER_GROUPS
5PLAN2

把group2里头的唯一的活跃会话指标占领:
  1. SQL> conn sh/oracle_4U
  2. Connected.
  3. SQL>
  4. SQL>
  5. SQL>
  6. SQL>
  7. SQL> !ps
  8.   PID TTY          TIME CMD
  9. 12558 pts/5    00:00:00 ps
  10. 23606 pts/5    00:00:00 sqlplus

  11. SQL> select  count(*)  from sales a ,sales b ;

复制代码

查:
  1. select  s.USERNAME, s.TERMINAL ,s.STATUS,s.RESOURCE_CONSUMER_GROUP
  2. from v_$session s
  3. where s.TERMINAL='pts/5';
复制代码
   USERNAMETERMINALSTATUSRESOURCE_CONSUMER_GROUP
1SHpts/5ACTIVEGROUP2

5.png


  1. select  pd.group_or_subplan,
  2.      pd.switch_time,
  3.      pd.switch_group,
  4.      pd.switch_for_call,
  5.      pd.switch_estimate
  6.     from dba_rsrc_plan_directives  pd
  7.       where pd.plan='PLAN1';
复制代码
   GROUP_OR_SUBPLANSWITCH_TIMESWITCH_GROUPSWITCH_FOR_CALLSWITCH_ESTIMATE
1GROUP18GROUP2TRUEFALSE
2GROUP2 FALSEFALSE
3SYS_GROUP TRUEFALSE
4OTHER_GROUPS FALSEFALSE
5PLAN2 FALSEFALSE

以上实验的现场视频:
http://124.16.180.178:8080/4078/switch.mpeg


IDLE TIME:
6.png


  1. EMPLOYEE_ID FIRST_NAME                 LAST_NAME
  2. ----------- -------------------- -------------------------
  3. EMAIL                          PHONE_NUMBER               HIRE_DATE          JOB_ID
  4. ------------------------- -------------------- ------------------ ----------
  5.     SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
  6. ---------- -------------- ---------- -------------
  7.         196 Alana                 Walsh
  8. AWALSH                          650.507.9811               24-APR-06          SH_CLERK
  9.       3100                         124                50

  10.         197 Kevin                 Feeney
  11. KFEENEY                   650.507.9822               23-MAY-06          SH_CLERK
  12.       3000                         124                50

  13. EMPLOYEE_ID FIRST_NAME                 LAST_NAME
  14. ----------- -------------------- -------------------------
  15. EMAIL                          PHONE_NUMBER               HIRE_DATE          JOB_ID
  16. ------------------------- -------------------- ------------------ ----------
  17.     SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
  18. ---------- -------------- ---------- -------------


  19. 107 rows selected.

  20. Elapsed: 00:00:00.05
  21. SQL> select * from employees;
  22. select * from employees
  23. *
  24. ERROR at line 1:
  25. ORA-02396: exceeded maximum idle time, please connect again


  26. Elapsed: 00:00:00.00
  27. SQL>
复制代码
7.png

做一些奇怪的映射规则:
8.png


  1. BEGIN
  2.     dbms_resource_manager.clear_pending_area();
  3.     dbms_resource_manager.create_pending_area();
  4.     dbms_resource_manager.set_consumer_group_mapping(
  5.         dbms_resource_manager.client_machine,
  6.         'station90.example.com',
  7.         'GROUP2'
  8.     );
  9.     dbms_resource_manager.submit_pending_area();
  10.     END;
复制代码


  1. select  *
  2. from dba_rsrc_mapping_priority;
复制代码
   ATTRIBUTEPRIORITYSTATUS
1EXPLICIT1
2SERVICE_MODULE_ACTION2
3SERVICE_MODULE3
4MODULE_NAME_ACTION4
5MODULE_NAME5
6SERVICE_NAME6
7ORACLE_USER7
8CLIENT_PROGRAM8
9CLIENT_OS_USER9
10CLIENT_MACHINE10
11CLIENT_ID11

  1. SQL> conn hr/oracle_4U@orcl
  2. Connected.
  3. SQL> !ps
  4.   PID TTY          TIME CMD
  5. 18868 pts/8    00:00:00 ps
  6. 23883 pts/8    00:00:00 sqlplus
复制代码
  1. select  s.USERNAME  , s.RESOURCE_CONSUMER_GROUP
  2.   from v_$session s
  3.   where s.TERMINAL='pts/8';
复制代码
   USERNAMERESOURCE_CONSUMER_GROUP
1HRGROUP1

  1. begin
  2.     dbms_resource_manager.clear_pending_area;
  3.     dbms_resource_manager.create_pending_area;
  4.     dbms_resource_manager.set_consumer_group_mapping_pri(
  5.     explicit => 1,
  6.     client_machine => 2,
  7.     SERVICE_MODULE_ACTION=>3,
  8.     SERVICE_MODULE=>4,
  9.     MODULE_NAME_ACTION=>5,
  10.     MODULE_NAME=>6,
  11.     SERVICE_NAME=>7,
  12.     oracle_user => 8,
  13.     CLIENT_PROGRAM=>9,
  14.     CLIENT_OS_USER=>10,
  15.     CLIENT_ID=>11);
  16.     dbms_resource_manager.submit_pending_area;
  17.   end;
复制代码
  1. select  *
  2. from dba_rsrc_mapping_priority;
复制代码
   ATTRIBUTEPRIORITYSTATUS
1EXPLICIT1
2CLIENT_MACHINE2
3SERVICE_MODULE_ACTION3
4SERVICE_MODULE4
5MODULE_NAME_ACTION5
6MODULE_NAME6
7SERVICE_NAME7
8ORACLE_USER8
9CLIENT_PROGRAM9
10CLIENT_OS_USER10
11CLIENT_ID11


  1. Connected.
  2. SQL>  conn hr/oracle_4U@orcl
  3. Connected.
  4. SQL>
  5. SQL>
  6. SQL>
  7. SQL>
复制代码
  1. select  s.USERNAME  , s.RESOURCE_CONSUMER_GROUP
  2.   from v_$session s
  3.   where s.TERMINAL='pts/8';
复制代码
   USERNAMERESOURCE_CONSUMER_GROUP
1HRGROUP2


  1. select  * from V$RSRC_CONSUMER_GROUP;
复制代码
   IDNAMEACTIVE_SESSIONSEXECUTION_WAITERSREQUESTSCPU_WAIT_TIMECPU_WAITSCONSUMED_CPU_TIMEYIELDSCPU_DECISIONSCPU_DECISIONS_EXCLUSIVECPU_DECISIONS_WONQUEUE_LENGTHCURRENT_UNDO_CONSUMPTIONACTIVE_SESSION_LIMIT_HITUNDO_LIMIT_HITSWITCHES_IN_CPU_TIMESWITCHES_OUT_CPU_TIMESWITCHES_IN_IO_MEGABYTESSWITCHES_OUT_IO_MEGABYTESSWITCHES_IN_IO_REQUESTSSWITCHES_OUT_IO_REQUESTSSQL_CANCELEDACTIVE_SESSIONS_KILLEDIDLE_SESSIONS_KILLEDIDLE_BLKR_SESSIONS_KILLEDQUEUED_TIMEQUEUE_TIME_OUTSIO_SERVICE_TIMEIO_SERVICE_WAITSSMALL_READ_MEGABYTESSMALL_WRITE_MEGABYTESLARGE_READ_MEGABYTESLARGE_WRITE_MEGABYTESSMALL_READ_REQUESTSSMALL_WRITE_REQUESTSLARGE_READ_REQUESTSLARGE_WRITE_REQUESTSCURRENT_PQS_ACTIVECURRENT_PQ_SERVERS_ACTIVEPQS_QUEUEDPQS_COMPLETEDPQ_SERVERS_USEDPQ_ACTIVE_TIMECURRENT_PQS_QUEUEDPQ_QUEUED_TIMEPQ_QUEUE_TIME_OUTS
181576GROUP1001000000000000000000000000000000000000000000
281577GROUP221790909238135866225100999900000000000000000000000000000000000
312168SYS_GROUP2060028000000000000000000000000000000000000000
412166OTHER_GROUPS1102300367000000000000000000000000000000000000000
50_ORACLE_BACKGROUND_GROUP_3503500000000000000000000000000201068011000000000



1Z0-052的第12章:

  1. create tablespace tbs05212  datafile '/u01/app/oracle/oradata/orcl/tbs05212.dbf'
  2. size 5M autoextend off;
复制代码
  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 21 21:26:23 2018

  3. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  4. SQL> conn hr/oracle_4U
  5. Connected.
  6. SQL> create table t05212  (  a  char(2000))  tablespace tbs05212;

  7. Table created.

  8. SQL> insert into t05212  values ('X') ;

  9. 1 row created.

  10. SQL> commit;

  11. Commit complete.

  12. SQL> insert into t05212 select  * from t05212;

  13. 1 row created.

  14. SQL> insert into t05212 select  * from t05212;

  15. 2 rows created.

  16. SQL> insert into t05212 select  * from t05212;

  17. 4 rows created.

  18. SQL> insert into t05212 select  * from t05212;

  19. 8 rows created.

  20. SQL> insert into t05212 select  * from t05212;

  21. 16 rows created.

  22. SQL> com
  23. SP2-0042: unknown command "com" - rest of line ignored.
  24. SQL> commit;

  25. Commit complete.

  26. SQL> insert into t05212 select  * from t05212;

  27. 32 rows created.

  28. SQL> insert into t05212 select  * from t05212;

  29. 64 rows created.

  30. SQL> insert into t05212 select  * from t05212;

  31. 128 rows created.

  32. SQL> insert into t05212 select  * from t05212;

  33. 256 rows created.

  34. SQL> commit;

  35. Commit complete.

  36. SQL> begin
  37.   2   for i in 1..64
  38.   3   loop
  39.   4     insert into t05212  values ('Y');
  40.   5   end loop;
  41.   6   commit;
  42.   7  end;
  43.   8  /

  44. PL/SQL procedure successfully completed.

  45. SQL> /

  46. PL/SQL procedure successfully completed.

  47. SQL> /

  48. PL/SQL procedure successfully completed.

  49. SQL> /

  50. PL/SQL procedure successfully completed.

  51. SQL> /

  52. PL/SQL procedure successfully completed.

  53. SQL> /

  54. PL/SQL procedure successfully completed.

  55. SQL> /

  56. PL/SQL procedure successfully completed.

  57. SQL> /

  58. PL/SQL procedure successfully completed.

  59. SQL> /

  60. PL/SQL procedure successfully completed.

  61. SQL> /

  62. PL/SQL procedure successfully completed.

  63. SQL>
复制代码
9.png


  1. select  * from dba_outstanding_alerts;
复制代码
   SEQUENCE_IDREASON_IDOWNEROBJECT_NAMESUBOBJECT_NAMEOBJECT_TYPEREASONTIME_SUGGESTEDCREATION_TIMESUGGESTED_ACTIONADVISOR_NAMEMETRIC_VALUEMESSAGE_TYPEMESSAGE_GROUPMESSAGE_LEVELHOSTING_CLIENT_IDMODULE_IDPROCESS_IDHOST_IDHOST_NW_ADDRINSTANCE_NAMEINSTANCE_NUMBERUSER_IDEXECUTION_CONTEXT_IDERROR_INSTANCE_ID
16519 TBS05212 TABLESPACETablespace [TBS05212] is [100 percent] full21-AUG-18 09.35.11.290761 PM +08:0021-AUG-18 09.35.11.290761 PM +08:00Add space to the tablespace 100WarningSpace1 SPACE MANAGEMENT:ktt.c"orcl"."orcl"station90.example.com192.168.0.90orcl1 651-0

11.png



  1. alter tablespace tbs05212 add datafile
  2.   '/u01/app/oracle/oradata/orcl/tbs05212b.dbf' size 50M;
复制代码
10.png

........一段时间以后
  1. select  * from dba_outstanding_alerts;
复制代码
没输出
图形界面的首页alter也消失了,转移到了:
  1. select  * from dba_alert_history ah
  2.    order by ah.time_suggested  desc;
复制代码

   SEQUENCE_IDREASON_IDOWNEROBJECT_NAMESUBOBJECT_NAMEOBJECT_TYPEREASONTIME_SUGGESTEDCREATION_TIMESUGGESTED_ACTIONADVISOR_NAMEMETRIC_VALUEMESSAGE_TYPEMESSAGE_GROUPMESSAGE_LEVELHOSTING_CLIENT_IDMODULE_IDPROCESS_IDHOST_IDHOST_NW_ADDRINSTANCE_NAMEINSTANCE_NUMBERUSER_IDEXECUTION_CONTEXT_IDERROR_INSTANCE_IDRESOLUTION
16519 TBS05212 TABLESPACETablespace [TBS05212] is [10 percent] full21-AUG-18 09.45.13.291297 PM +08:0021-AUG-18 09.35.11.290761 PM +08:00Add space to the tablespace 10.9090909090909NotificationSpace32 SPACE MANAGEMENT:ktt.c"orcl"."orcl"station90.example.com192.168.0.90orcl1 651-0cleared
2650121 Commit EVENT_CLASSMetrics "Database Time Spent Waiting (%)" is at 49.55853 for event class "Commit"21-AUG-18 07.59.52.806885 PM +08:0021-AUG-18 07.54.51.929247 PM +08:00Run ADDM to get more performance analysis about your system.ADDM49.558533655248NotificationPerformance32 SERVER MANAGEABILITY:kelr.c"orcl"."orcl"station90.example.com192.168.0.90orcl1 650-0cleared
3649121 Commit EVENT_CLASSMetrics "Database Time Spent Waiting (%)" is at 49.02129 for event class "Commit"21-AUG-18 07.49.50.950818 PM +08:0021-AUG-18 07.46.50.406913 PM +08:00Run ADDM to get more performance analysis about your system.ADDM49.0212867683013NotificationPerformance32 SERVER MANAGEABILITY:kelr.c"orcl"."orcl"station90.example.com192.168.0.90orcl1 649-0cleared
4648121 Commit EVENT_CLASSMetrics "Database Time Spent Waiting (%)" is at 12.95075 for event class "Commit"21-AUG-18 07.36.48.399570 PM +08:0021-AUG-18 07.33.47.793205 PM +08:00Run ADDM to get more performance analysis about your system.ADDM12.9507485491163NotificationPerformance32 SERVER MANAGEABILITY:kelr.c"orcl"."orcl"station90.example.com192.168.0.90orcl1 648-0cleared
5647121 Commit EVENT_CLASSMetrics "Database Time Spent Waiting (%)" is at 29.49839 for event class "Commit"21-AUG-18 07.27.46.699476 PM +08:0021-AUG-18 03.45.52.864763 PM +08:00Run ADDM to get more performance analysis about your system.ADDM29.4983910837832NotificationPerformance32 SERVER MANAGEABILITY:kelr.c"orcl"."orcl"station90.example.com192.168.0.90orcl1 647-0cleared
6646121 Commit EVENT_CLASSMetrics "Database Time Spent Waiting (%)" is at 49.84263 for event class "Commit"21-AUG-18 03.42.52.320128 PM +08:0021-AUG-18 03.06.45.486719 PM +08:00Run ADDM to get more performance analysis about your system.ADDM49.8426327175679NotificationPerformance32 SERVER MANAGEABILITY:kelr.c"orcl"."orcl"station90.example.com192.168.0.90orcl1 646-0cleared


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 03:51 , Processed in 0.193188 second(s), 36 queries .

快速回复 返回顶部 返回列表