在RAC环境中，登陆到一个实例，在处理的数据完全与另外实例内存中数据无关的情况下，也会导致gc全局等待产生。
这一篇描述性能影响。
上一篇文章描述gc等待产生的现象，这篇文章验证一下产生的全局等待是否会对性能产生不利影响：
SQL> truncate table t;
表被截断。
SQL> set timing on
SQL> insert into t
2 select *
3 from t_big;
已创建6430135行。
已用时间: 00: 01: 05.05
SQL> commit;
提交完成。
已用时间: 00: 00: 00.00
在节点1上执行预热操作，为下面的测试做准备：
SQL> CREATE GLOBAL TEMPORARY TABLE T_SESSION_STAT
2 (ID NUMBER, NAME VARCHAR2(100), VALUE NUMBER);
表已创建。
已用时间: 00: 00: 00.02
SQL> set serverout on size 1000000
SQL> DECLARE
2 V_NUMBER1 NUMBER;
3 V_NUMBER2 NUMBER;
4 BEGIN
5
6 INSERT INTO T_SESSION_STAT SELECT 1, 'STAT:' || NAME, VALUE
7 FROM V$SESSTAT A, V$STATNAME B
8 WHERE A.STATISTIC# = B.STATISTIC#
9 AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1)
10 UNION ALL
11 SELECT 1, 'LATCH:' || NAME, GETS VALUE FROM V$LATCH;
12
13 V_NUMBER1 := DBMS_UTILITY.GET_TIME;
14 INSERT INTO T
15 SELECT *
16 FROM T_BIG;
17 V_NUMBER1 := DBMS_UTILITY.GET_TIME - V_NUMBER1;
18
19 INSERT INTO T_SESSION_STAT SELECT 2, 'STAT:' || NAME, VALUE
20 FROM V$SESSTAT A, V$STATNAME B
21 WHERE A.STATISTIC# = B.STATISTIC#
22 AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1)
23 UNION ALL
24 SELECT 2, 'LATCH:' || NAME, GETS VALUE FROM V$LATCH;
25
26 DBMS_LOCK.SLEEP(600);
27
28 V_NUMBER2 := DBMS_UTILITY.GET_TIME;
29 INSERT INTO T
30 SELECT *
31 FROM T_BIG;
32 V_NUMBER2 := DBMS_UTILITY.GET_TIME - V_NUMBER2;
33
34 INSERT INTO T_SESSION_STAT SELECT 3, 'STAT:' || NAME, VALUE
35 FROM V$SESSTAT A, V$STATNAME B
36 WHERE A.STATISTIC# = B.STATISTIC#
37 AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1)
38 UNION ALL
39 SELECT 3, 'LATCH:' || NAME, GETS VALUE FROM V$LATCH;
40
41 DBMS_OUTPUT.PUT_LINE('P1 EXECUTE ' || V_NUMBER1/100 || ' SECONDS');
42 DBMS_OUTPUT.PUT_LINE('P2 EXECUTE ' || V_NUMBER2/100 || ' SECONDS');
43
44 FOR C IN
45 (
46 SELECT *
47 FROM
48 (
49 SELECT A.NAME, C.VALUE + A.VALUE - 2 * B.VALUE VALUE
50 FROM
51 T_SESSION_STAT A,
52 T_SESSION_STAT B,
53 T_SESSION_STAT C
54 WHERE A.NAME = B.NAME
55 AND A.NAME = C.NAME
56 AND A.ID = 1
57 AND B.ID = 2
58 AND C.ID = 3
59 )
60 WHERE ABS(VALUE) > 100
61 ) LOOP
62 DBMS_OUTPUT.PUT_LINE(RPAD(C.NAME, 50, ' ') || C.VALUE);
63 END LOOP;
64
65 END;
66 /
P1 EXECUTE 66.34 SECONDS
P2 EXECUTE 77.16 SECONDS
STAT:opened cursors cumulative -106
STAT:recursive calls -1583
STAT:recursive cpu usage 374
STAT:session logical reads -2610
STAT:CPU used by this session 380
STAT:cluster wait time 655
STAT:messages sent -124
STAT:session pga memory 196608
STAT:enqueue waits 1382
STAT:enqueue requests -301
STAT:enqueue releases -299
STAT:global enqueue gets sync -226
STAT:global enqueue get time 113
STAT:global enqueue releases -225
STAT:physical read total bytes -770048
STAT:gcs messages sent 43620
STAT:ges messages sent 1398
STAT:db block gets -1892
STAT:db block gets from cache -1892
STAT:consistent gets -718
STAT:consistent gets from cache -718
STAT:physical read bytes -770048
STAT:db block changes -1394
STAT:change write time 143
STAT:free buffer requested -146
STAT:dirty buffers inspected -123
STAT:hot buffers moved to head of LRU 208
STAT:calls to kcmgas -196
STAT:calls to get snapshot scn: kcmgss -530
STAT:redo entries -861
STAT:redo size -132304
STAT:gc local grants -43691
STAT:gc remote grants 43620
STAT:gc CPU used by this session 191
STAT:undo change vector size -31756
STAT:no work - consistent read gets -221
STAT:table scan rows gotten -11188
STAT:table scan blocks gotten -112
STAT:cluster key scan block gets -148
STAT:session cursor cache hits -102
STAT:buffer is not pinned count -211
STAT:parse count (total) -106
STAT:execute count -106
LATCH:process allocation 5600
LATCH:session allocation -995
LATCH:session idle bit 2631
LATCH:messages 27308
LATCH:enqueues 43747
LATCH:enqueue hash chains 45129
LATCH:channel operations parent latch 35467
LATCH:message pool operations parent latch 205
LATCH:active service list 4555
LATCH:OS process allocation 208
LATCH:queued dump request 2902
LATCH:ges process parent latch 12029
LATCH:ges process hash list 5476
LATCH:ges resource table freelist 2618
LATCH:ges caches resource lists 2376
LATCH:ges resource hash list 109152
LATCH:ges enqueue table freelist 6304
LATCH:ges synchronous data 106
LATCH:KJC message pool free list 8297
LATCH:KJCT flow control latch 68079
LATCH:ges domain table 2543
LATCH:ges group table 4121
LATCH:gcs resource hash 258704
LATCH:gcs opaque info freelist 1066
LATCH:gcs resource freelist -63331
LATCH:name-service request queue 5702
LATCH:name-service namespace bucket 5772
LATCH:name-service memory objects 161
LATCH:gcs remastering latch 71777
LATCH:gcs partitioned table hash 923267
LATCH:KMG MMAN ready and startup request latch 203
LATCH:mostly latch-free SCN 223
LATCH:lgwr LWN SCN 216
LATCH:cache buffers lru chain 67878
LATCH:active checkpoint queue latch 4773
LATCH:checkpoint queue latch 57221
LATCH:cache buffers chains 101265
LATCH:simulator hash latch -7726
LATCH:object queue header operation 72573
LATCH:object queue header heap 4778
LATCH:redo writing 5342
LATCH:redo allocation 489
LATCH:KCL gc element parent latch 377308
LATCH:dml lock allocation 871
LATCH:list of block allocation -243
LATCH:transaction branch allocation 205
LATCH:undo global data -208
LATCH:row cache objects -558
LATCH:shared pool 1739
LATCH:library cache 6351
LATCH:library cache lock 3696
LATCH:library cache pin 3268
LATCH:shared pool simulator 710
LATCH:Shared B-Tree 446
LATCH:session timer 205
LATCH:process queue reference 1233
LATCH:parallel query alloc buffer 235
LATCH:SQL memory manager workarea list latch 13609
LATCH:kwqbsn:qsga 353
LATCH:qmn task queue latch 773
LATCH:KWQMN job cache list latch 209
LATCH:ASM db client latch 413
LATCH:JS broadcast add buf latch 483
LATCH:JS broadcast drop buf latch 483
LATCH:JS broadcast load blnc latch 465
LATCH:JS queue state obj latch 19530
PL/SQL过程已成功完成。
已用时间: 00: 12: 25.00
在节点2关闭的情况下执行上面的代码，同时在另外的会话检查这个会话的等待状态：
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read file# 7 block# 30891 blocks 16
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read file# 7 block# 33691 blocks 16
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read file# 7 block# 43387 blocks 16
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
PL/SQL lock timer duration 59473 0 0
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
PL/SQL lock timer duration 40936 0 0
当等待时间变成pl/sql lock timer时，启动实例2：
bash-2.03$ srvctl start inst -d testrac -i testrac2
确认实例2启动后，pl/sql lock timer等待还没有结束：
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
PL/SQL lock timer duration 26908 0 0
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
PL/SQL lock timer duration 6367 0 0
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read file# 7 block# 3866 blocks 16
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read file# 7 block# 4666 blocks 15
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
gc current multi block request file# 7 block# 135752 id# 33554433
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read file# 7 block# 7243 blocks 16
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
gc current multi block request file# 7 block# 140152 id# 33554433
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read file# 7 block# 8043 blocks 16
.
.
.
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
gc current multi block request file# 7 block# 144232 id# 33554433
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read file# 7 block# 12187 blocks 16
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
gc current multi block request file# 7 block# 145464 id# 33554433
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read file# 7 block# 13291 blocks 16
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
db file scattered read file# 7 block# 13883 blocks 16
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
gc current multi block request file# 7 block# 146712 id# 33554433
SQL> select event, p1text, p1, p2text, p2, p3text, p3
2 from v$session_wait
3 where sid = 143;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
gc current multi block request file# 7 block# 143736 id# 33554433
可以看到，当另外一个实例启动后，从等待信息中就可以看到gc current multi block request file#等待了。
最后分析一下pl/sql的输出结果。第一次执行insert的时候，RAC环境只有一个节点在运行，而第二次运行insert语句的时候，RAC的两个节点都处于运行状态。
从运行时间上看，单节点情况下运行的insert要比RAC环境下快了1/6。
从统计信息上看，似乎单节点运行的统计并没有占有，除了一些gc全局统计要小意外，很多统计值比RAC环境下运行还要大，尤其在物理、逻辑读以及redo生成上，更是比RAC环境下运行的insert要多。
但是从LATCH方面看，单节点运行明显占有，这也很正常，Oracle为了保证RAC环境两个节点间的数据完整性和一致性，肯定需要大量的LATCH来进行维护、锁定的操作。
结论显而易见，RAC并不见得就一定意味着性能提升，Oracle为了维护这种复杂的环境，必要要比单实例付出多得多的代价。
此外，在单实例上进行操作实际上也会遭遇RAC全局等待事件，即使另外的实例并没有包含任何对当前实例产生影响的对象。
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
