[20151203]关于grd对性能影响.txt
--前几天写了1篇，统计分析对grd的影响，提到一些大表在晚上分析后会出现资源重新分配，参考链接
--blog.itpub.net/267265/viewspace-1851145/
--我们的生产系统业务并不是很忙，今天做一点"危险"的测试,让另外1个实例掌控某些另外实例经常访问的对象。
--先看看没有切换的awr报表(昨天的10－11点)rac部分：
RAC Statistics DB/Inst: xxxx/xxxx1 Snaps: 9593-9594
Begin End
----- -----
Number of Instances: 2 2
Global Cache Load Profile
~~~~~~~~~~~~~~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Global Cache blocks received: 14.52 0.17
Global Cache blocks served: 9.26 0.11
GCS/GES messages received: 134.63 1.60
GCS/GES messages sent: 136.04 1.61
DBWR Fusion writes: 0.21 0.00
Estd Interconnect traffic (KB) 243.13
Global Cache Efficiency Percentages (Target local+remote 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer access - local cache %: 99.97
Buffer access - remote cache %: 0.01
Buffer access - disk %: 0.02
Global Cache and Enqueue Services - Workload Characteristics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg global enqueue get time (ms): 0.0
Avg global cache cr block receive time (ms): 0.1
Avg global cache current block receive time (ms): 0.3
Avg global cache cr block build time (ms): 0.0
Avg global cache cr block send time (ms): 0.0
Global cache log flushes for cr blocks served %: 0.3
Avg global cache cr block flush time (ms): 0.0
Avg global cache current block pin time (ms): 0.0
Avg global cache current block send time (ms): 0.0
Global cache log flushes for current blocks served %: 0.0
Avg global cache current block flush time (ms): 0.0
Global Cache and Enqueue Services - Messaging Statistics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg message sent queue time (ms): 0.2
Avg message sent queue time on ksxp (ms): 0.3
Avg message received queue time (ms): 0.1
Avg GCS message process time (ms): 0.0
Avg GES message process time (ms): 0.0
% of direct sent messages: 90.53
% of indirect sent messages: 9.29
% of flow controlled messages: 0.19
-------------------------------------------------------------
--以上是昨天10:00-11:00 的awr 报表 rac的小部分。注意看 Estd Interconnect traffic (KB) 243.13 ，很小的流量。
--今天把一些大对象让另外的实例掌控。
SYS@xxxx2> oradebug setmypid
Statement processed.
SYS@xxxx2> oradebug lkdebug -m pkey 94701
Statement processed.
SYS@xxxx2> oradebug lkdebug -m pkey 93703
Statement processed.
SYS@xxxx2> oradebug lkdebug -m pkey 97158
Statement processed.
SYS@xxxx2> select * from GV$GCSPFMASTER_INFO where DATA_OBJECT_ID in (94701,93703,97158);
INST_ID FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT
---------- ---------- -------------- ----------- -------------- --------------- ------------
1 0 93703 Affinity 1 0 30
1 0 94701 Affinity 1 0 32
1 0 97158 Affinity 1 0 5
2 0 93703 Affinity 1 0 29
2 0 94701 Affinity 1 0 31
2 0 97158 Affinity 1 0 5
6 rows selected.
--实际上10:29分，有1个对象有跑回来了。DATA_OBJECT_I=97158
SELECT inst_id
,policy_event
,data_object_id
,target_instance_number
,event_date, substr(event_date,12,5) hhmm,to_char(to_date(event_date,'mm/dd/yyyy hh24:mi:ss'),'d') week
FROM gv$policy_history
WHERE data_object_id =97158
ORDER BY data_object_id, event_date desc ,inst_id;
INST_ID POLICY_EVENT DATA_OBJECT_ID TARGET_INSTANCE_NUMBER EVENT_DATE HHMM W
---------- -------------------- -------------- ---------------------- -------------------- ---------- -
2 push_affinity 97158 1 12/03/2015 10:29:42 10:29 5
1 push_affinity 97158 2 12/01/2015 07:46:09 07:46 3
2 push_affinity 97158 1 11/25/2015 17:04:52 17:04 4
...
SYS@xxxx2> select * from GV$GCSPFMASTER_INFO where DATA_OBJECT_ID in (94701,93703,97158);
INST_ID FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT
---------- ---------- -------------- ----------- -------------- --------------- ------------
1 0 93703 Affinity 1 0 30
1 0 94701 Affinity 1 0 32
1 0 97158 Affinity 0 1 6
2 0 93703 Affinity 1 0 29
2 0 94701 Affinity 1 0 31
2 0 97158 Affinity 0 1 6
--为了测试准确再次切换过去：
SYS@xxxx2> oradebug lkdebug -m pkey 97158
Statement processed.
--等11点后看awr报表。
RAC Statistics DB/Inst: xxxx/xxxx1 Snaps: 9617-9618
Begin End
----- -----
Number of Instances: 2 2
Global Cache Load Profile
~~~~~~~~~~~~~~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Global Cache blocks received: 31.64 0.37
Global Cache blocks served: 0.78 0.01
GCS/GES messages received: 416.42 4.85
GCS/GES messages sent: 1,107.51 12.90
DBWR Fusion writes: 0.14 0.00
Estd Interconnect traffic (KB) 556.99
Global Cache Efficiency Percentages (Target local+remote 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer access - local cache %: 99.97
Buffer access - remote cache %: 0.01
Buffer access - disk %: 0.01
Global Cache and Enqueue Services - Workload Characteristics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg global enqueue get time (ms): 0.0
Avg global cache cr block receive time (ms): 0.3
Avg global cache current block receive time (ms): 0.1
Avg global cache cr block build time (ms): 0.0
Avg global cache cr block send time (ms): 0.0
Global cache log flushes for cr blocks served %: 0.8
Avg global cache cr block flush time (ms): 0.0
Avg global cache current block pin time (ms): 13915.2
Avg global cache current block send time (ms): 0.0
Global cache log flushes for current blocks served %: 1.5
Avg global cache current block flush time (ms): 6311.1
Global Cache and Enqueue Services - Messaging Statistics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg message sent queue time (ms): 2.6
Avg message sent queue time on ksxp (ms): 2.3
Avg message received queue time (ms): 0.1
Avg GCS message process time (ms): 0.0
Avg GES message process time (ms): 0.0
% of direct sent messages: 12.28
% of indirect sent messages: 87.68
% of flow controlled messages: 0.04
-------------------------------------------------------------
--注意Estd Interconnect traffic (KB)=556.99 ,比前面增加1倍。
--对比前面这些指标都存在很大的变化。
GCS/GES messages received: 416.42 4.85
GCS/GES messages sent: 1,107.51 12.90
Avg global cache current block pin time (ms): 13915.2
Avg global cache current block flush time (ms): 6311.1
% of indirect sent messages: 87.68
--第1次的结果：
GCS/GES messages received: 134.63 1.60
GCS/GES messages sent: 136.04 1.61
Avg global cache current block pin time (ms): 0.0
Avg global cache current block flush time (ms): 0.0
% of indirect sent messages: 9.29
--当然这里这点流量不算什么，如果业务分割不好，内联流量就会增加，对系统影响还是很大的。
--收尾工作，把上面的修改切换回来。
SYS@xxxx1> select * from GV$GCSPFMASTER_INFO where DATA_OBJECT_ID in (94701,93703,97158);
INST_ID FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER REMASTER_CNT
---------- ---------- -------------- ----------- -------------- --------------- ------------
2 0 93703 Affinity 0 1 30
2 0 94701 Affinity 0 1 32
2 0 97158 Affinity 0 1 8
1 0 93703 Affinity 0 1 31
1 0 94701 Affinity 0 1 33
1 0 97158 Affinity 0 1 8
6 rows selected.
--补充：Dynamic Remastering Stats部分内容：
Dynamic Remastering Stats DB/Inst: xxxx/xxxx1 Snaps: 9593-9594
-> times are in seconds
-> Affinity objects - objects mastered due to affinity at begin/end snap
per Begin End
Name Total Remaster Op Snap Snap
------------------------- ------------ ------------- -------- --------
remaster ops 1 1.00
remastered objects 1 1.00
replayed locks received 13,765 13,765.00
replayed locks sent 0 0.00
resources cleaned 0 0.00
remaster time (s) 3.9 3.93
quiesce time (s) 1.2 1.19
freeze time (s) 0.2 0.22
cleanup time (s) 0.6 0.59
replay time (s) 0.1 0.12
fixwrite time (s) 1.2 1.17
sync time (s) 0.6 0.64
affinity objects N/A 175 176
------------------------------------------------------
Dynamic Remastering Stats DB/Inst: xxxx/xxxx1 Snaps: 9617-9618
-> times are in seconds
-> Affinity objects - objects mastered due to affinity at begin/end snap
per Begin End
Name Total Remaster Op Snap Snap
------------------------- ------------ ------------- -------- --------
remaster ops 5 1.00
remastered objects 5 1.00
replayed locks received 1,112,712 222,542.40
replayed locks sent 3,383,681 676,736.20
resources cleaned 0 0.00
remaster time (s) 26.1 5.22
quiesce time (s) 6.9 1.38
freeze time (s) 1.0 0.21
cleanup time (s) 4.3 0.87
replay time (s) 4.2 0.84
fixwrite time (s) 4.5 0.90
sync time (s) 5.1 1.03
affinity objects N/A 176 173
------------------------------------------------------
--自己看还是一目了然的。