ITPub博客

首页 > Linux操作系统 > Linux操作系统 > RAC环境单独节点插入数据也会导致全局等待(二)

RAC环境单独节点插入数据也会导致全局等待(二)

原创 Linux操作系统 作者:yangtingkun 时间:2010-12-12 23:03:46 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10438568