从v$sysstat的指标ges messages sent理解oracle 10.2.0.1 rac lmd进程系列三

最新推荐文章于 2021-04-13 02:06:00 发布

clg10051

最新推荐文章于 2021-04-13 02:06:00 发布

阅读量162

点赞数

文章标签：数据库

结论

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1829980/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1829980/