從v$sysstat的指標ges messages sent理解oracle 10.2.0.1 rac lmd程式系列三

結論

1,如果節點間的DML操作非常頻繁，會在節點間產生大量的GES資訊傳遞
2,ges messages sent可以評估RAC節點的DML操作或通訊是否頻繁，如果此值小，說明RAC資料庫並不繁忙
3,lmd程式是負責傳送ges message到遠端的RAC節點
4,如果用oradebug suspend lmd,會導致遠端RAC節點的insert操作受阻，可見lmd程式就是管理全域性鎖資源
5,ges messages sent在lmd hang時，也會有微量的增加
6,引申一下，可能v$sysstat中的效能指標，說白了就是評估RAC不同的後臺程式的效能的，透過這些指標可以進一步分析後臺程式是否正常
   進而進一步診斷分析RAC資料庫的效能

測試

SQL> select * from v$version where rownum=1;


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi


SQL> select statistic#,name,class from v$statname where lower(name) like '%ges%';


STATISTIC# NAME                                                                  CLASS
---------- ---------------------------------------------------------------- ----------
        22 messages sent                                                           128
        23 messages received                                                       128
        44 gcs messages sent                                                        32
        45 ges messages sent                                                        32  ---主要研究這個指標






可見ges messages sent隸屬於叢集層面
CLASS NUMBER A number representing one or more statistics classes. The following class numbers are additive:
1 - User
2 - Redo
4 - Enqueue
8 - Cache
16 - OS
32 - Real Application Clusters
64 - SQL
128 - Debug


--node1


SQL> select v$statname.name,v$sysstat.value from v$sysstat,v$statname where v$sysstat.statistic#=v$statname.statistic# and v$statname.statistic# in (45);


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
ges messages sent                                                     44374




SQL> create table t_ges(a int,b int);


Table created.


SQL> insert into t_ges select level,level from dual connect by level<=1000000;


1000000 rows created.


SQL> commit;


Commit complete.


可見大量的DML操作後，指標值大幅提升
SQL> select v$statname.name,v$sysstat.value from v$sysstat,v$statname where v$sysstat.statistic#=v$statname.statistic# and v$statname.statistic# in (45);


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
ges messages sent                                                     44925




可見TRUNCATE大表指標也會提升，不過不如INSERT增幅大
SQL> truncate table t_ges;


Table truncated.


SQL> select v$statname.name,v$sysstat.value from v$sysstat,v$statname where v$sysstat.statistic#=v$statname.statistic# and v$statname.statistic# in (45);


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
ges messages sent                                                     45104






--node2
SQL> create table t_ges2(a int,b int);


Table created.


可見在另一個節點也會使指標提升
SQL> insert into t_ges2 select level,level from dual connect by level<=1000000;


1000000 rows created.


SQL> commit;


Commit complete.


---node1


SQL> select v$statname.name,v$sysstat.value from v$sysstat,v$statname where v$sysstat.statistic#=v$statname.statistic# and v$statname.statistic# in (45);


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
ges messages sent                                                     45373




如果暫停lmd,上述指標還有變化嗎？會如何表現呢？
SQL> select addr,program,username,pid,spid from v$process where username='oracle' and pid=6;


ADDR             PROGRAM                                          USERNAME               PID SPID
---------------- ------------------------------------------------ --------------- ---------- ------------
0000000083A585C8 oracle@jingfa1 (LMD0)                            oracle                   6 15271


SQL> select v$statname.name,v$sysstat.value from v$sysstat,v$statname where v$sysstat.statistic#=v$statname.statistic# and v$statname.statistic# in (45);


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
ges messages sent                                                     45430


SQL> oradebug setospid 15271
Oracle pid: 6, Unix process pid: 15271, image: oracle@jingfa1 (LMD0)
SQL> oradebug suspend
Statement processed.


如果HANG LMD，指值增量極小
SQL> select v$statname.name,v$sysstat.value from v$sysstat,v$statname where v$sysstat.statistic#=v$statname.statistic# and v$statname.statistic# in (45);


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
ges messages sent                                                     45678


SQL> oradebug resume
Statement processed.


SQL> select v$statname.name,v$sysstat.value from v$sysstat,v$statname where v$sysstat.statistic#=v$statname.statistic# and v$statname.statistic# in (45);


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
ges messages sent                                                     45711


--可見如果LMD超過一個時間期限，仍不能恢復正常，會引發IPC TIMEOUT，進而會把另一個節點從叢集中驅逐
oracle@jingfa2 bdump]$ tail -f alert_jingfa2.log 
IPC Send timeout to 0.0 inc 8 for msg type 29 from opid 22
Wed Nov 11 03:29:07 2015
Communications reconfiguration: instance_number 1
Wed Nov 11 03:29:07 2015
Trace dumping is performing id=[cdmp_20151111032907]
Wed Nov 11 03:29:11 2015
IPC Send timeout detected.Sender: ospid 18114
Receiver: inst 1 binc 433078410 ospid 15271
Wed Nov 11 03:29:13 2015
IPC Send timeout to 0.0 inc 8 for msg type 12 from opid 18




Wed Nov 11 03:30:59 2015
Evicting instance 1 from cluster
Wed Nov 11 03:31:01 2015
Trace dumping is performing id=[cdmp_20151111033042]
Wed Nov 11 03:31:06 2015
Reconfiguration started (old inc 8, new inc 12)
List of nodes:
 1
 Global Resource Directory frozen
 * dead instance detected - domain 0 invalid = TRUE 
 Communication channels reestablished
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
Wed Nov 11 03:31:06 2015
 LMS 0: 0 GCS shadows cancelled, 0 closed
 Set master node info