RAC環境單獨節點插入資料也會導致全域性等待(二)
在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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- RAC環境單獨節點插入資料也會導致全域性等待(一)
- RAC全域性等待事件分析事件
- 網路中斷導致RAC環境所有節點監聽lsnr自動關閉
- ORACLE RAC環境下刪除節點Oracle
- ElasticSearch(單節點)環境配置Elasticsearch
- Oracle10203RAC環境刪除節點(二)Oracle
- Oracle10203RAC環境新增新節點(二)Oracle
- enq: TX - allocate ITL entry等待過多導致全域性死鎖ENQ
- Solaris下修改RAC環境節點IP地址
- LGWR寫操作會導致效能全域性卡頓案例分析
- inode節點耗盡導致資料庫OOM資料庫OOM
- 單例項環境利用備份恢復RAC資料庫(二)單例資料庫
- RAC環境利用備份恢復RAC資料庫(二)資料庫
- 【RAC】刪除RAC資料庫節點(二)——刪除ASM資料庫ASM
- linux下rac環境下刪除節點Linux
- 虛擬機器環境下RAC加入節點虛擬機
- 利用STANDBY將單例項資料庫升級為RAC環境(二)單例資料庫
- 【RMAN】RAC資料恢復至單機環境資料恢復
- RAC環境關閉CLUSTER後導致連線緩慢
- Oracle RAC恢復成單節點資料庫Oracle資料庫
- RAC環境下節點歸檔目的地相互獨立的配置和備份要求
- (轉)windows環境下rac節點時間同步方法Windows
- 同時開啟節點導致資料DDL操作慢 ??
- RAC環境只啟動單例項資料庫單例資料庫
- RAC 雙節點 轉單節點流程
- ORACLE 11.2.0.4 rac for linux 鏈路宕導致的單節點異常當機OracleLinux
- mac配置全域性環境變數Mac變數
- npm 全域性環境變數配置NPM變數
- ddl 導致分割槽表全域性索引unusable索引
- ORACLE RAC環境下某節點的+ASM註冊到CRS資源中OracleASM
- asm例項自動dismount導致rac一個節點當機ASM
- oracle兩節點RAC,由於gipc導致某節點crs無法啟動問題分析Oracle
- shell全域性(系統)環境變數變數
- Oracle10g RAC 單獨關閉一個節點(維護)Oracle
- Oracle10203RAC環境刪除節點(三)Oracle
- Oracle10203RAC環境刪除節點(一)Oracle
- 虛擬機器環境下RAC刪除節點(Final)虛擬機
- Oracle10203RAC環境新增新節點(一)Oracle