RAC環境單獨節點插入資料也會導致全域性等待(二)

yangtingkun發表於2010-12-12

RAC環境中,登陸到一個例項,在處理的資料完全與另外例項記憶體中資料無關的情況下,也會導致gc全域性等待產生。

這一篇描述效能影響。

RAC環境單獨節點插入資料也會導致全域性等待(一):http://yangtingkun.itpub.net/post/468/510317

 

 

上一篇文章描述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全域性等待事件,即使另外的例項並沒有包含任何對當前例項產生影響的物件。

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4227/viewspace-681590/,如需轉載,請註明出處,否則將追究法律責任。

相關文章