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

最新推荐文章于 2022-03-17 03:38:36 发布

ckawt40802

最新推荐文章于 2022-03-17 03:38:36 发布

阅读量163

点赞数

文章标签：数据库

在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关闭的情况下执行上面的代码，同时在另外的会话检查这个会话的等待状态：