SinoDB 监控性能常用SQL

运维指南 运维指南
1

1 前言

在数据库运维管理中，需要监控数据库的性能指标，从而了解数据库运行健康状态，本文将介绍SinoDB监控性能几个常用的SQL。

2 SinoDB 监控性能常用SQL

  • Top Busy Sessions（数据库中最忙的session）
select sp.sid,ss.username,ss.connected,
(sp.isreads+sp.iswrites+sp.isdeletes+sp.isrewrites) TOP_OP,
sp.locksheld,sp. locksheld , sp.seqscans, sdb.odb_dbname 
from syssesprof sp,syssessions ss, sysopendb sdb
 where sp.sid=ss.sid and ss.sid=sdb.odb_sessionid;
  • Top I/O Sessions (数据库中IO最多的Session)
select sp.sid,ss.username,ss.connected, 
(sp.isreads+sp.iswrites) TOP_OP
, sum(sp.isreads+sp.iswrites) RW, 
sum(sp.isreads) DR ,sum(sp.iswrites) DW, 
sp.seqscans, sdb.odb_dbname 
from syssesprof sp,syssessions ss,
 sysopendb sdb where sp.sid=ss.sid and ss.sid=sdb.odb_sessionid
 group by sid,username, 
connected ,top_op, seqscans, odb_dbname;
  • Top Memory Sessions(数据库中内存使用最多的Session)
select sp.sid,ss.username,ss.connected,slt.memtotal,slt.memused,
sp.total_sorts,sp.locksheld,sp.bufreads,sp.bufwrites,sp.seqscans, 
sdb.odb_dbname from syssesprof sp,syssessions ss,sysscblst slt, 
sysopendb sdb where sp.sid=ss.sid and ss.sid=slt.sid and
 slt.sid=sdb.odb_sessionid;
  • dbspaces usage (表空间使用率）
SELECT TRIM(sysdbspaces.name) as name,  round ((1-SUM(syschunks.nfree)/SUM(syschunks.chksize))*100,4)||'%' as pcused  
FROM sysshmvals,syschunks JOIN sysdbspaces
ON syschunks.dbsnum = sysdbspaces.dbsnum GROUP BY 1 ORDER BY NAME;
  • dbspaces size （表空间大小）
SELECT TRIM(sysdbspaces.name) as name, SUM(syschunks.chksize*sh_pagesize)/1024 as size, SUM(syschunks.nfree*sh_pagesize)/1024 as free 
FROM sysshmvals,syschunks JOIN sysdbspaces 
ON syschunks.dbsnum = sysdbspaces.dbsnum 
GROUP BY 1 ORDER BY NAME;