达梦数据库cpu、内存异常问题
<h2>一、背景</h2>
<p>项目部署到正式环境后，使用一段时间后出现dmserver进程cpu、内存异常，以及压测条件下出现</p>
<h2>二、问题分析</h2>
<ul>
<li>数据库dmserver进程是否正常，cpu内存异常一般都存在慢sql，找出正在执行的慢sql并优化</li>
<li>数据库服务器是否有可疑进程占用服务器资源:ps -ef |grep dmdba</li>
</ul>
<h2>三、针对达梦数据库dmserver的排查思路（达梦监视工具看不到慢sql的情况下）</h2>
<ul>
<li><strong>查看dmserver的pid</strong></li>
</ul>
<pre><code class="language-java">ps -ef | grep dmserver**</code></pre>
<p><img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/a88780c0903048eca4c6f41b81358909" alt="" /></p>
<ul>
<li><strong>top -Hp pid（找出cpu占比较高的tid）</strong>
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/695cbf22969175472553cebd694659e8" alt="" /></li>
<li><strong>管理工具执行</strong>
<pre><code class="language-java">select SESS_ID,&quot;V$SESSIONS&quot;.USER_NAME,
SQL_TEXT,&quot;V$SESSIONS&quot;.TRX_ID,&quot;V$SESSIONS&quot;.THRD_ID,&quot;V$SESSIONS&quot;.CREATE_TIME,&quot;V$SESSIONS&quot;.CLNT_IP
from v$sessions
WHERE &quot;V$SESSIONS&quot;.THRD_ID IN (线程tid);
#例如：(25011)</code></pre></li>
<li><strong>复制sql_text的记录的sql，执行该sql记录执行时间</strong>
如果查询结果中sql_text获取不全，则执行
<pre><code class="language-java">select sf_get_session_sql(sess_id) #获取完整的慢sql语句</code></pre></li>
<li><strong> 根据查询到的慢sql，提交给开发进行优化（根据执行计划）</strong></li>
</ul>
<h2>四、相关sql附录</h2>
<p>数据库最慢的20条SQL</p>
<pre><code>select * from V$SYSTEM_LONG_EXEC_SQLS</code></pre>
<p>数据库近20条慢 SQL</p>
<pre><code class="language-java">select * from V$LONG_EXEC_SQLS</code></pre>
<p>数据库高内存的 SQL</p>
<pre><code class="language-java">select * from V$LARGE_MEM_SQLS</code></pre>
<p>数据库高MTAB的 SQL</p>
<pre><code class="language-java">select * from V$MTAB_USED_HISTORY</code></pre>
<p>数据库排序页最多的SQL</p>
<pre><code class="language-java">select * from V$SORT_HISTORY</code></pre>
<p>数据库HASH连接最多的SQL</p>
<pre><code class="language-java">select * from V$HASH_MERGE_USED_HISTORY</code></pre>
<p>查询数据库页大小</p>
<pre><code class="language-java">select para_name,para_value from v$dm_ini where para_name = 'GLOBAL_EXTENT_SIZE';
select para_name,para_value from v$dm_ini where para_name = 'GLOBAL_PAGE_SIZE';</code></pre>
<p>开启达梦慢sql
[开启慢sql参考地址](<a href="https://blog.csdn.net/maodongliang9/article/details/108196234">https://blog.csdn.net/maodongliang9/article/details/108196234</a> "开启慢sql参考方法")</p>
<pre><code class="language-java">查看达梦数据库使用率、总量，空闲量
```java
SELECT sum(df.TOTAL_SIZE) - sum(df.FREE_SIZE) as used,
sum(df.TOTAL_SIZE) as total,
sum(df.FREE_SIZE) as free
FROM &quot;SYS&quot;.V$TABLESPACE AS ts, &quot;SYS&quot;.V$DATAFILE AS df WHERE ts.ID = df.GROUP_ID;</code></pre>
<p>达梦查看死锁</p>
<pre><code class="language-java">SELECT ADDR AS &quot;锁地址&quot;,TRX_ID AS &quot;所属事务ID&quot;,LTYPE AS &quot;锁类型&quot;,LMODE AS &quot;锁模式&quot;,DECODE(BLOCKED,’1’,’是’,’0’,’否’) AS &quot;是否阻塞&quot;,TABLE_ID AS &quot;对应表锁ID&quot;,ROW_IDX AS &quot;TID锁事务ID&quot; FROM V$LOCK;
SELECT A.NAME AS &quot;对象名称&quot;,B.ADDR AS &quot;锁地址&quot;,B.TRX_ID AS &quot;所属事务ID&quot;,B.LTYPE AS &quot;锁类型&quot;,B.LMODE AS &quot;锁模式&quot;,DECODE(B.BLOCKED,’1’,’是’,’0’,’否’) AS &quot;是否阻塞&quot;,B.TABLE_ID AS &quot;对应表锁ID&quot;,B.ROW_IDX AS &quot;TID锁事务ID&quot; FROM SYSOBJECTS A,V$LOCK B WHERE B.TABLE_ID=A.ID AND BLOCKED=1;
SELECT THREAD_ID AS &quot;线程ID&quot;,TRX_ID AS &quot;事务ID&quot;,WAIT_CLASS AS &quot;等待类型号&quot;,WAIT_OBJECT AS &quot;等待对象&quot;,WAIT_START AS &quot;等待开始时间&quot;,TRUNC(WAIT_TIME,6)/1000000 AS &quot;等待时间&quot; FROM V$WAIT_HISTORY;</code></pre>