课程第28次（2018-08-21星期二）

botang

----------------UNDO池：

1. select  pd.group_or_subplan, pd.undo_pool
   
2. from dba_rsrc_plan_directives  pd
   
3.   where pd.plan='PLAN1';

复制代码

	GROUP_OR_SUBPLAN	UNDO_POOL
1	GROUP1	8
2	GROUP2
3	SYS_GROUP
4	OTHER_GROUPS
5	PLAN2

1. select  s.USERNAME ,s.TERMINAL, s.RESOURCE_CONSUMER_GROUP
   
2.   from v_$session s
   
3.   where s.TERMINAL  in ('pts/5','pts/8');

复制代码

	USERNAME	TERMINAL	RESOURCE_CONSUMER_GROUP
1	HR	pts/5	GROUP1
2	HR	pts/8	GROUP1

准备实验环境：

1. create table hr.t05316_undo( a char(2000)) ;
   
2. 
   
3. create table hr.t05316_undo2( a char(2000)) ;
   
4. 
   
5. insert into hr.t05316_undo values ('A');
   
6. 
   
7. insert into hr.t05316_undo2 values('X');
   
8. 
   
9. commit;

复制代码

UNDO池是两个表加起来，两个用户（会话）也加起来算总量：



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

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_SUBPLAN	ACTIVE_SESS_POOL_P1	QUEUEING_P1
1	GROUP1
2	GROUP2	1	1
3	SYS_GROUP
4	OTHER_GROUPS
5	PLAN2

把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. 
    
12. SQL> select  count(*)  from sales a ,sales b ;
    
13. 
    

复制代码

查：

1. select  s.USERNAME, s.TERMINAL ,s.STATUS,s.RESOURCE_CONSUMER_GROUP
   
2. from v_$session s
   
3. where s.TERMINAL='pts/5';

复制代码

	USERNAME	TERMINAL	STATUS	RESOURCE_CONSUMER_GROUP
1	SH	pts/5	ACTIVE	GROUP2

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_SUBPLAN	SWITCH_TIME	SWITCH_GROUP	SWITCH_FOR_CALL	SWITCH_ESTIMATE
1	GROUP1	8	GROUP2	TRUE	FALSE
2	GROUP2			FALSE	FALSE
3	SYS_GROUP			TRUE	FALSE
4	OTHER_GROUPS			FALSE	FALSE
5	PLAN2			FALSE	FALSE

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


IDLE TIME：

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. 
    
11.         197 Kevin                 Feeney
    
12. KFEENEY                   650.507.9822               23-MAY-06          SH_CLERK
    
13.       3000                         124                50
    
14. 
    
15. EMPLOYEE_ID FIRST_NAME                 LAST_NAME
    
16. ----------- -------------------- -------------------------
    
17. EMAIL                          PHONE_NUMBER               HIRE_DATE          JOB_ID
    
18. ------------------------- -------------------- ------------------ ----------
    
19.     SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
    
20. ---------- -------------- ---------- -------------
    
21. 
    
22. 
    
23. 107 rows selected.
    
24. 
    
25. Elapsed: 00:00:00.05
    
26. SQL> select * from employees;
    
27. select * from employees
    
28. *
    
29. ERROR at line 1:
    
30. ORA-02396: exceeded maximum idle time, please connect again
    
31. 
    
32. 
    
33. Elapsed: 00:00:00.00
    
34. SQL>
    

复制代码

做一些奇怪的映射规则：

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;

复制代码

	ATTRIBUTE	PRIORITY	STATUS
1	EXPLICIT	1
2	SERVICE_MODULE_ACTION	2
3	SERVICE_MODULE	3
4	MODULE_NAME_ACTION	4
5	MODULE_NAME	5
6	SERVICE_NAME	6
7	ORACLE_USER	7
8	CLIENT_PROGRAM	8
9	CLIENT_OS_USER	9
10	CLIENT_MACHINE	10
11	CLIENT_ID	11

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';

复制代码

	USERNAME	RESOURCE_CONSUMER_GROUP
1	HR	GROUP1

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;

复制代码

	ATTRIBUTE	PRIORITY	STATUS
1	EXPLICIT	1
2	CLIENT_MACHINE	2
3	SERVICE_MODULE_ACTION	3
4	SERVICE_MODULE	4
5	MODULE_NAME_ACTION	5
6	MODULE_NAME	6
7	SERVICE_NAME	7
8	ORACLE_USER	8
9	CLIENT_PROGRAM	9
10	CLIENT_OS_USER	10
11	CLIENT_ID	11

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';

复制代码

	USERNAME	RESOURCE_CONSUMER_GROUP
1	HR	GROUP2

1. select  * from V$RSRC_CONSUMER_GROUP;

复制代码

	ID	NAME	ACTIVE_SESSIONS	EXECUTION_WAITERS	REQUESTS	CPU_WAIT_TIME	CPU_WAITS	CONSUMED_CPU_TIME	YIELDS	CPU_DECISIONS	CPU_DECISIONS_EXCLUSIVE	CPU_DECISIONS_WON	QUEUE_LENGTH	CURRENT_UNDO_CONSUMPTION	ACTIVE_SESSION_LIMIT_HIT	UNDO_LIMIT_HIT	SWITCHES_IN_CPU_TIME	SWITCHES_OUT_CPU_TIME	SWITCHES_IN_IO_MEGABYTES	SWITCHES_OUT_IO_MEGABYTES	SWITCHES_IN_IO_REQUESTS	SWITCHES_OUT_IO_REQUESTS	SQL_CANCELED	ACTIVE_SESSIONS_KILLED	IDLE_SESSIONS_KILLED	IDLE_BLKR_SESSIONS_KILLED	QUEUED_TIME	QUEUE_TIME_OUTS	IO_SERVICE_TIME	IO_SERVICE_WAITS	SMALL_READ_MEGABYTES	SMALL_WRITE_MEGABYTES	LARGE_READ_MEGABYTES	LARGE_WRITE_MEGABYTES	SMALL_READ_REQUESTS	SMALL_WRITE_REQUESTS	LARGE_READ_REQUESTS	LARGE_WRITE_REQUESTS	CURRENT_PQS_ACTIVE	CURRENT_PQ_SERVERS_ACTIVE	PQS_QUEUED	PQS_COMPLETED	PQ_SERVERS_USED	PQ_ACTIVE_TIME	CURRENT_PQS_QUEUED	PQ_QUEUED_TIME	PQ_QUEUE_TIME_OUTS
1	81576	GROUP1	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
2	81577	GROUP2	2	1	7	90909	238	135866	225	100	99	99	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
3	12168	SYS_GROUP	2	0	6	0	0	28	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	12166	OTHER_GROUPS	11	0	23	0	0	367	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	0	_ORACLE_BACKGROUND_GROUP_	35	0	35	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2	0	1	0	68	0	11	0	0	0	0	0	0	0	0	0

1Z0-052的第12章：

1. 
   
2. create tablespace tbs05212  datafile '/u01/app/oracle/oradata/orcl/tbs05212.dbf'
   
3. size 5M autoextend off;

复制代码

1. [oracle@station90 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 21 21:26:23 2018
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn hr/oracle_4U
   
8. Connected.
   
9. SQL> create table t05212  (  a  char(2000))  tablespace tbs05212;
   
10. 
    
11. Table created.
    
12. 
    
13. SQL> insert into t05212  values ('X') ;
    
14. 
    
15. 1 row created.
    
16. 
    
17. SQL> commit;
    
18. 
    
19. Commit complete.
    
20. 
    
21. SQL> insert into t05212 select  * from t05212;
    
22. 
    
23. 1 row created.
    
24. 
    
25. SQL> insert into t05212 select  * from t05212;
    
26. 
    
27. 2 rows created.
    
28. 
    
29. SQL> insert into t05212 select  * from t05212;
    
30. 
    
31. 4 rows created.
    
32. 
    
33. SQL> insert into t05212 select  * from t05212;
    
34. 
    
35. 8 rows created.
    
36. 
    
37. SQL> insert into t05212 select  * from t05212;
    
38. 
    
39. 16 rows created.
    
40. 
    
41. SQL> com
    
42. SP2-0042: unknown command "com" - rest of line ignored.
    
43. SQL> commit;
    
44. 
    
45. Commit complete.
    
46. 
    
47. SQL> insert into t05212 select  * from t05212;
    
48. 
    
49. 32 rows created.
    
50. 
    
51. SQL> insert into t05212 select  * from t05212;
    
52. 
    
53. 64 rows created.
    
54. 
    
55. SQL> insert into t05212 select  * from t05212;
    
56. 
    
57. 128 rows created.
    
58. 
    
59. SQL> insert into t05212 select  * from t05212;
    
60. 
    
61. 256 rows created.
    
62. 
    
63. SQL> commit;
    
64. 
    
65. Commit complete.
    
66. 
    
67. SQL> begin
    
68.   2   for i in 1..64
    
69.   3   loop
    
70.   4     insert into t05212  values ('Y');
    
71.   5   end loop;
    
72.   6   commit;
    
73.   7  end;
    
74.   8  /
    
75. 
    
76. PL/SQL procedure successfully completed.
    
77. 
    
78. SQL> /
    
79. 
    
80. PL/SQL procedure successfully completed.
    
81. 
    
82. SQL> /
    
83. 
    
84. PL/SQL procedure successfully completed.
    
85. 
    
86. SQL> /
    
87. 
    
88. PL/SQL procedure successfully completed.
    
89. 
    
90. SQL> /
    
91. 
    
92. PL/SQL procedure successfully completed.
    
93. 
    
94. SQL> /
    
95. 
    
96. PL/SQL procedure successfully completed.
    
97. 
    
98. SQL> /
    
99. 
    
100. PL/SQL procedure successfully completed.
     
101. 
     
102. SQL> /
     
103. 
     
104. PL/SQL procedure successfully completed.
     
105. 
     
106. SQL> /
     
107. 
     
108. PL/SQL procedure successfully completed.
     
109. 
     
110. SQL> /
     
111. 
     
112. PL/SQL procedure successfully completed.
     
113. 
     
114. SQL>
     

复制代码

1. select  * from dba_outstanding_alerts;

复制代码

SEQUENCE_ID

REASON_ID

OWNER

OBJECT_NAME

SUBOBJECT_NAME

OBJECT_TYPE

REASON

TIME_SUGGESTED

CREATION_TIME

SUGGESTED_ACTION

ADVISOR_NAME

METRIC_VALUE

MESSAGE_TYPE

MESSAGE_GROUP

MESSAGE_LEVEL

HOSTING_CLIENT_ID

MODULE_ID

PROCESS_ID

HOST_ID

HOST_NW_ADDR

INSTANCE_NAME

INSTANCE_NUMBER

USER_ID

EXECUTION_CONTEXT_ID

ERROR_INSTANCE_ID

1

651

9

TBS05212

TABLESPACE

Tablespace [TBS05212] is [100 percent] full

21-AUG-18 09.35.11.290761 PM +08:00

21-AUG-18 09.35.11.290761 PM +08:00

Add space to the tablespace

100

Warning

Space

1

SPACE MANAGEMENT:ktt.c

"orcl"."orcl"

station90.example.com

192.168.0.90

orcl

1

651-0

1. alter tablespace tbs05212 add datafile
   
2.   '/u01/app/oracle/oradata/orcl/tbs05212b.dbf' size 50M;

复制代码

........一段时间以后

1. select  * from dba_outstanding_alerts;

复制代码

没输出
图形界面的首页alter也消失了，转移到了：

1. select  * from dba_alert_history ah
   
2.    order by ah.time_suggested  desc;
   

复制代码

SEQUENCE_ID

REASON_ID

OWNER

OBJECT_NAME

SUBOBJECT_NAME

OBJECT_TYPE

REASON

TIME_SUGGESTED

CREATION_TIME

SUGGESTED_ACTION

ADVISOR_NAME

METRIC_VALUE

MESSAGE_TYPE

MESSAGE_GROUP

MESSAGE_LEVEL

HOSTING_CLIENT_ID

MODULE_ID

PROCESS_ID

HOST_ID

HOST_NW_ADDR

INSTANCE_NAME

INSTANCE_NUMBER

USER_ID

EXECUTION_CONTEXT_ID

ERROR_INSTANCE_ID

RESOLUTION

1

651

9

TBS05212

TABLESPACE

Tablespace [TBS05212] is [10 percent] full

21-AUG-18 09.45.13.291297 PM +08:00

21-AUG-18 09.35.11.290761 PM +08:00

Add space to the tablespace

10.9090909090909

Notification

Space

32

SPACE MANAGEMENT:ktt.c

"orcl"."orcl"

station90.example.com

192.168.0.90

orcl

1

651-0

cleared

2

650

121

Commit

EVENT_CLASS

Metrics "Database Time Spent Waiting (%)" is at 49.55853 for event class "Commit"

21-AUG-18 07.59.52.806885 PM +08:00

21-AUG-18 07.54.51.929247 PM +08:00

Run ADDM to get more performance analysis about your system.

ADDM

49.558533655248

Notification

Performance

32

SERVER MANAGEABILITY:kelr.c

"orcl"."orcl"

station90.example.com

192.168.0.90

orcl

1

650-0

cleared

3

649

121

Commit

EVENT_CLASS

Metrics "Database Time Spent Waiting (%)" is at 49.02129 for event class "Commit"

21-AUG-18 07.49.50.950818 PM +08:00

21-AUG-18 07.46.50.406913 PM +08:00

Run ADDM to get more performance analysis about your system.

ADDM

49.0212867683013

Notification

Performance

32

SERVER MANAGEABILITY:kelr.c

"orcl"."orcl"

station90.example.com

192.168.0.90

orcl

1

649-0

cleared

4

648

121

Commit

EVENT_CLASS

Metrics "Database Time Spent Waiting (%)" is at 12.95075 for event class "Commit"

21-AUG-18 07.36.48.399570 PM +08:00

21-AUG-18 07.33.47.793205 PM +08:00

Run ADDM to get more performance analysis about your system.

ADDM

12.9507485491163

Notification

Performance

32

SERVER MANAGEABILITY:kelr.c

"orcl"."orcl"

station90.example.com

192.168.0.90

orcl

1

648-0

cleared

5

647

121

Commit

EVENT_CLASS

Metrics "Database Time Spent Waiting (%)" is at 29.49839 for event class "Commit"

21-AUG-18 07.27.46.699476 PM +08:00

21-AUG-18 03.45.52.864763 PM +08:00

Run ADDM to get more performance analysis about your system.

ADDM

29.4983910837832

Notification

Performance

32

SERVER MANAGEABILITY:kelr.c

"orcl"."orcl"

station90.example.com

192.168.0.90

orcl

1

647-0

cleared

6

646

121

Commit

EVENT_CLASS

Metrics "Database Time Spent Waiting (%)" is at 49.84263 for event class "Commit"

21-AUG-18 03.42.52.320128 PM +08:00

21-AUG-18 03.06.45.486719 PM +08:00

Run ADDM to get more performance analysis about your system.

ADDM

49.8426327175679

Notification

Performance

32

SERVER MANAGEABILITY:kelr.c

"orcl"."orcl"

station90.example.com

192.168.0.90

orcl

1

646-0

cleared