SQL性能问题定位思路

数据库若出现 SQL 长时间等待的情况，可以从以下几个方向进行排查：

执行过程中某些对象或资源发生等待，导致执行语句长时间等待不能返回。
所执行语句本身存在性能问题。
其他异常情况。

对象或资源发生等待问题
如果存在长时间没有返回结果的语句，首先通过 V$SESSIONS 确认语句处于活动状态，语句如下：

SELECT * FROM v$sessions WHERE state='ACTIVE'
AND dbms_lob.substr(sf_get_session_sql(sess_id)) LIKE '%语句片段%'

明确事务等待导致语句没有正常执行结束，语句如下：
SELECT * FROM v$trxwait WHERE id = 上述语句得到结果的 TRX_ID

1.VTRXWAIT 查询得到结果的 WAIT_FOR_ID 字段标记的事务即为当前语句正在等待的事务。通过 SELECT * FROM vsessions WHERE trx_id = 查询到的 WAIT_FOR_ID; 可以得到当前事务所在等待会话的一些信息，一般来说，因为某些会话或者客户端忘记进行提交或者回滚操作，后续一直空闲，导致其他的事务由于跟该事务存在一些事务上的依赖关系发生等待。针对这种情况，需要在确认安全的情况下针对这个阻塞源头的会话进行操作，可以通过关闭客户端、关闭会话、发送提交或者回滚命令等方式结束等待的会话。
2.当 VTRXWAIT 查询不到结果时，可以通过查询 SELECT * FROM vlock WHERE blocked = 1; 来进行确认，在查询结果中可以对相关锁对象的持有事务来查询来确认阻塞来源。
注意
一般情况下 DDL 导致的等待会有一个显示的等待时间，由 dm.ini 配置文件中的参数 DDL_WAIT_TIME 来进行控制，默认为 10，也就是说如果等待 10 秒钟后，阻塞源头的 DDL 还没有执行完释放资源，会抛出锁超时错误。可以适当调大等待时间再次运行查看是否还会抛出锁超时错误。

确认语句处于活动状态，语句如下：

SELECT * FROM v$sessions WHERE state='ACTIVE'
AND dbms_lob.substr(sf_get_session_sql(sess_id)) LIKE '%语句片段%'

查询等待事务：

SELECT * FROM v$trxwait WHERE id = 上述语句得到结果的 TRX_ID

若查询不到结果，说明该语句没有发生事务性等待。如果发现语句活动，且没有事务性等待，则大概率是该语句自身执行存在效率问题，需要对执行计划进行调整。
2.3 其他异常情况问题
若对象或资源未发生等待，且 SQL 语句的执行性能正常，则可参考以下排查思路：
1.临时表空间大小限制。在 dm.ini 配置文件中对 TEMP 表空间大小进行了限制，查询中存在 /SORT/HASH JOIN/HAGR 等操作使用临时表空间，在临时表空间没有被其他会话释放时发生等待。
2.刷新 REDO 日志语句出现等待。主备、读写分离等环境运行过程中，由于备机自身或者配置相关的原因（备机 IO 出现异常、主备网络异常、数据延迟达到配置的主备最大延迟）等，导致主机上运行一些需要刷 REDO 日志的语句时发生等待。
3.相关表出现大批量修改操作。大表发生大批量删除数据后，由于这些数据都只是被标记删除，在一定事件后会由回收站进行统一的清理操作，清理过程中需要对数据页进行修改，而涉及的数据页又非常多，导致的执行速度缓慢。

生产常见场景如下：

整体业务 SQL 执行速度慢且主机 CPU 使用率高
【问题描述】
系统整体执行业务 SQL 较为缓慢，执行 top -p 数据库服务进程查看数据库服务进程的 CPU 使用率，发现数据库主机 CPU 使用率高。
【解决方案】
1.确认数据库状态

##确认数据库进程正常。
ps –ef|grep dmserver

通过以下语句查询数据库状态：
select instance_name,status$ from v$instance;

1.检查数据库当前会话，是否有运行中的异常 SQL。
如果有大量执行效率低的 SQL，确认是否可以限流、强制终止。如果可以强制终止，执行下一步。

（1）查询所有会话。
select
        sysdate                    ,
        SF_GET_SESSION_SQL(SESS_ID), --获取完整 SQL
        sess_id                    ,
        sess_seq                   ,
        sql_text                   ,
        state                      ,
        seq_no                     ,
        user_name                  ,
        trx_id                     ,
        create_time                ,
        clnt_ip
from
        v$sessions
where
        state='ACTIVE';

（2）查询执行效率慢 SQL。
SELECT
        *
FROM
        (
                SELECT
                        sess_id                                         ,
                        sql_text                                        ,
                        datediff (ss, last_recv_time, SYSDATE) Y_EXETIME,
                        SF_GET_SESSION_SQL (SESS_ID) fullsql            ,
                        clnt_ip
                FROM
                        V$SESSIONS
                WHERE
                        STATE = 'ACTIVE'
        )
WHERE
        Y_EXETIME >= 2; --执行时间超 2s，可以自定义该时间

处理方式：

1.终止异常会话。在数据库里面执行 SQL 语句 sp_close_session(sess_id);
2.分析 SQL 执行计划，看是否有优化空间，进行 SQL 优化。
3.观察数据库活动会话是否正常。
部分业务 SQL 执行缓慢且 CPU 占用正常持续变化

数据库部分业务 SQL 执行缓慢，但执行 top -p 数据库服务进程，发现 CPU 占用正常并持续变化。
【解决方案】
1.检查存储读写性能，检查网络传输速度。可以参考以下命令：

dd if=/dev/zero of=/home/test bs=8k count=1k oflag=dsync

(本地服务器速度一般为 25M/秒以上,磁盘阵列 10M/S 以上)

1.检查数据库当前会话，是否有运行中的异常 SQL。查询所有会话：

select
        sysdate                    ,
        SF_GET_SESSION_SQL(SESS_ID), --获取完整 sql
        sess_id                    ,
        sess_seq                   ,
        sql_text                   ,
        state                      ,
        seq_no                     ,
        user_name                  ,
        trx_id                     ,
        create_time                ,
        clnt_ip
from
        v$sessions
where
        state='ACTIVE';

1.检查数据库执行 SQL 缓存信息，是否有异常 SQL。
2.查看数据库 topsql，是否有异常 SQL。

Select top 60 datediff(ss,last_send_time,sysdate) TIME,sess_id,run_status,sql_text,AUTO_CMT,user_name,appname,CLNT_IP,trx_id  from  v$sessions  order by TIME desc;

1.检查数据库 SQL 日志，通过关键字在 SQL 日志中查找是否有异常 SQL。
2.收集最近一天的 SQL 日志，分析 SQL 执行计划，看是否有优化空间，进行 SQL 优化。
3.观察数据库活动会话是否正常。

生产常见场景如下：

作者