【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读-每日运维

1. 前言

前几天写了一篇博客，告诉大家在遇到慢SQL或者复杂的并行SQL的时候怎么高效的来收集【SQL Monitor Report】，上一篇博客的链接： https://open.oceanbase.com/blog/12020367891 ；发出去后有不少问我这份报告咋解读。今天再出一篇博客给大家介绍下如何解读报告。PS: 本文不介绍如何安装部署使用obdiag，用法参加上篇博客，本文仅做【SQL Monitor Report】报告解读。

2. 报告样式

生成的报告长这样，记得用浏览器打开。

#tree
.
├── resources
│   └── web
│       ├── bootstrap.min.css
│       ├── bootstrap.min.js
│       ├── jquery-3.2.1.min.js
│       └── popper.min.js
├── result_summary.txt
└── sql_plan_monitor_report.html

2 directories, 6 files

浏览器打开表头如下：（表头展示的是基本的sql执行信息，从gv$ob_sql_audit获取的）

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读-1

3. 报告【执行计划】篇

执行计划的部分如下：

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读-2

执行计划有两部分组成，一个是explain extend [SQL语句] 的结果，这个我们叫做物理执行计划，并不是实际这条SQL执行的时候命中的执行计划；物理执行计划下面的是才是实际执行计划。物理执行计划可以看到统计信息、hint、outline data等信息，就不在本文展开解释了。TIPS: 这两个执行计划大部分情况下是一样的，如果不一样需要关注一下。

4. 报告【SCHEMA 信息】篇

schema这块直接参见下图就行，注意一下表行数和第三节explan extend [SQL] 是否数量级能对应上，如果数量级都对应不上，那大概率统计信息收集存在滞后，先解决统计信息收集的问题再去看SQL慢的问题。

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读-3

5. 报告【SQL_AUDIT信息】篇

报告如下，点击可以展开

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读-4

使用的SQL如下：

-- ob 4.x --
select /*+ sql_audit */ 
`SVR_IP`,`SVR_PORT`,`REQUEST_ID`,`SQL_EXEC_ID`,`TRACE_ID`,`SID`,`CLIENT_IP`,`CLIENT_PORT`,`TENANT_ID`,
`EFFECTIVE_TENANT_ID`,`TENANT_NAME`,`USER_ID`,`USER_NAME`,`USER_CLIENT_IP`,`DB_ID`,`DB_NAME`,`SQL_ID`,
`QUERY_SQL`,`PLAN_ID`,`AFFECTED_ROWS`,`RETURN_ROWS`,`PARTITION_CNT`,`RET_CODE`,`QC_ID`,`DFO_ID`,`SQC_ID`,
`WORKER_ID`,`EVENT`,`P1TEXT`,`P1`,`P2TEXT`,`P2`,`P3TEXT`,`P3`,`LEVEL`,`WAIT_CLASS_ID`,`WAIT_CLASS`,`STATE`,
`WAIT_TIME_MICRO`,`TOTAL_WAIT_TIME_MICRO`,`TOTAL_WAITS`,`RPC_COUNT`,`PLAN_TYPE`,`IS_INNER_SQL`,
`IS_EXECUTOR_RPC`,`IS_HIT_PLAN`,`REQUEST_TIME`,`ELAPSED_TIME`,`NET_TIME`,`NET_WAIT_TIME`,`QUEUE_TIME`,
`DECODE_TIME`,`GET_PLAN_TIME`,`EXECUTE_TIME`,`APPLICATION_WAIT_TIME`,`CONCURRENCY_WAIT_TIME`,
`USER_IO_WAIT_TIME`,`SCHEDULE_TIME`,`ROW_CACHE_HIT`,`BLOOM_FILTER_CACHE_HIT`,`BLOCK_CACHE_HIT`,
`DISK_READS`,`RETRY_CNT`,`TABLE_SCAN`,`CONSISTENCY_LEVEL`,`MEMSTORE_READ_ROW_COUNT`,
`SSSTORE_READ_ROW_COUNT`,`REQUEST_MEMORY_USED`,`EXPECTED_WORKER_COUNT`,`USED_WORKER_COUNT`,
`TX_ID`,`REQUEST_TYPE`,`IS_BATCHED_MULTI_STMT`,`OB_TRACE_INFO`,`PLAN_HASH`  
from oceanbase.gv$ob_sql_audit where trace_id = '%s' " "AND client_ip IS NOT NULL ORDER BY QUERY_SQL ASC, REQUEST_ID

TIPS: 一些重要的时间间隔：

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读-5

TIPS: sql_audit的PLAN_TYPE字段可以看到该SQL的执行计划类型，

plan_type=1 ：本地执行计划。性能最好。
plan_type=2 : 远程执行计划。
plan_type=3 : 分布式执行计划。包含本地执行计划和远程执行计划。

一般情况下，如果出现远程执行比较多时可能时出现切主或proxy客户端路由不准的情况。

TIPS:

查看retry次数是否很多(RETRY_CNT, 如果次数很多，则是否考虑是否有锁冲突或切主等情况)
查看queue time是不是很大(QUEUE_TIME字段)
查看获取执行计划时间(GET_PLAN_TIME), 如果时间很长，一般会伴随IS_HIT_PLAN ＝ 0, 表示没有命中plan cache)
查看EXECUTE_TIME是否很长，如果很长，则

a. 查看是否有很长等待事件耗时

b. 查看访问的行数是否很多, 看SSSTORE_READ_ROW_COUNT, MEMSTORE_READ_ROW_COUNT两个字段, 比如大小账号场景可能导致rt抖动。

6. 报告【SQL_PLAN_MONITOR DFO级调度时序汇总】篇

概念介绍：

并行执行使用生产者-消费者模型来进行流水执行。并行调度算子解析计划，将它们切分成多个操作步骤，每个操作步骤称之为一个 DFO（Data Flow Operation）。

DFO：Data Flow Operation（DFO）即子计划，每个DFO包含若干个串行执行的算子。例如，一个DFO里包含了扫描分区，聚集，发送算子，另外一个DFO里包含了收集、聚集算子等。

obdiag 封装的SQL如下：

-- DFO 级
select
  AVG(ROWS) EST_ROWS, /*0*/
  plan_monitor.PLAN_DEPTH PLAN_DEPTH,
  plan_monitor.PLAN_LINE_ID PLAN_LINE_ID,
  PLAN_OPERATION,
  COUNT(*) PARALLEL,
  MIN(FIRST_REFRESH_TIME) MIN_FIRST_REFRESH_TIME,/*5*/
  MAX(LAST_REFRESH_TIME) MAX_LAST_REFRESH_TIME,
  MIN(FIRST_CHANGE_TIME) MIN_FIRST_CHANGE_TIME,
  MAX(LAST_CHANGE_TIME) MAX_LAST_CHANGE_TIME,
  UNIX_TIMESTAMP(MIN(FIRST_REFRESH_TIME)) MIN_FIRST_REFRESH_TS,
  UNIX_TIMESTAMP(MAX(LAST_REFRESH_TIME)) MAX_LAST_REFRESH_TS, /*10*/
  UNIX_TIMESTAMP(MIN(FIRST_CHANGE_TIME)) MIN_FIRST_CHANGE_TS,
  UNIX_TIMESTAMP(MAX(LAST_CHANGE_TIME)) MAX_LAST_CHANGE_TS,
  AVG(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) AVG_REFRESH_TIME,
  MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) MAX_REFRESH_TIME,
  MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) MIN_REFRESH_TIME, /*15 */
  AVG(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) AVG_CHANGE_TIME,
  MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) MAX_CHANGE_TIME,
  MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) MIN_CHANGE_TIME,
  SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,
  (MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) - MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME))) / MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)+0.00000001) SKEWNESS,
  SUM(STARTS) TOTAL_RESCAN_TIMES,/* 20 */
  MAX(OTHERSTAT_1_ID) OTHERSTAT_1_ID,
  SUM(OTHERSTAT_1_VALUE) SUM_STAT_1,
  MAX(OTHERSTAT_1_VALUE) MAX_STAT_1,
  MIN(OTHERSTAT_1_VALUE) MIN_STAT_1,
  AVG(OTHERSTAT_1_VALUE) AVG_STAT_1, /* 25 */
  MAX(OTHERSTAT_2_ID) OTHERSTAT_2_ID,
  SUM(OTHERSTAT_2_VALUE) SUM_STAT_2,
  MAX(OTHERSTAT_2_VALUE) MAX_STAT_2,
  MIN(OTHERSTAT_2_VALUE) MIN_STAT_2,
  AVG(OTHERSTAT_2_VALUE) AVG_STAT_2, /* 30 */
  MAX(OTHERSTAT_3_ID) OTHERSTAT_3_ID,
  SUM(OTHERSTAT_3_VALUE) SUM_STAT_3,
  MAX(OTHERSTAT_3_VALUE) MAX_STAT_3,
  MIN(OTHERSTAT_3_VALUE) MIN_STAT_3,
  AVG(OTHERSTAT_3_VALUE) AVG_STAT_3, /* 35 */
  MAX(OTHERSTAT_4_ID) OTHERSTAT_4_ID,
  SUM(OTHERSTAT_4_VALUE) SUM_STAT_4,
  MAX(OTHERSTAT_4_VALUE) MAX_STAT_4,
  MIN(OTHERSTAT_4_VALUE) MIN_STAT_4,
  AVG(OTHERSTAT_4_VALUE) AVG_STAT_4, /* 40 */
  MAX(OTHERSTAT_5_ID) OTHERSTAT_5_ID,
  SUM(OTHERSTAT_5_VALUE) SUM_STAT_5,
  MAX(OTHERSTAT_5_VALUE) MAX_STAT_5,
  MIN(OTHERSTAT_5_VALUE) MIN_STAT_5,
  AVG(OTHERSTAT_5_VALUE) AVG_STAT_5, /* 45*/
  MAX(OTHERSTAT_6_ID) OTHERSTAT_6_ID,
  SUM(OTHERSTAT_6_VALUE) SUM_STAT_6,
  MAX(OTHERSTAT_6_VALUE) MAX_STAT_6,
  MIN(OTHERSTAT_6_VALUE) MIN_STAT_6,
  AVG(OTHERSTAT_6_VALUE) AVG_STAT_6 /* 50 */,
  TRUNCATE(AVG(DB_TIME)/1000000000.0/2.5, 4) MY_DB_TIME,
  TRUNCATE(AVG(DB_TIME-USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_CPU_TIME,
  TRUNCATE(AVG(USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_IO_TIME
from
(
  select * FROM oceanbase.gv$sql_plan_monitor
where
  trace_id = 'YF2A0BA2DA7E-00061D6A8ADDA95A-0-0'
) plan_monitor
LEFT JOIN
(
 SELECT ROWS, PLAN_LINE_ID FROM oceanbase.gv$ob_plan_cache_plan_explain WHERE plan_id = xxx AND tenant_id = xxxx and svr_ip = 'xxxx'  and svr_port = xxxx
) plan_explain
ON
  plan_monitor.PLAN_LINE_ID = plan_explain.PLAN_LINE_ID
GROUP BY
  plan_monitor.PLAN_LINE_ID, plan_monitor.PLAN_OPERATION
ORDER BY
  plan_monitor.PLAN_LINE_ID ASC;

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读-6

7. 报告【SQL_PLAN_MONITOR SQC级汇总】篇

概念介绍：

QC：当用户给定的 SQL 语句需要访问的数据位于 2 台或 2 台以上 OBServer、或者单节点包含多个分区的表，就会启用并行执行，当SQL计划在连接的OBserver执行到并行查询时，主进程在决定并行度后，发送工作线程获取请求到各个机器，此时用户连接的OBServer就会作为QC角色，即查询协调者Query Coordinator（QC）。
SQC：每个处理QC请求的线程，自动成为该查询的Sub Query Coordinator（SQC）即辅助查询协调者。SQC 负责在所在 OBServer上为各个 DFO 申请执行资源、构造执行上下文环境等，然后启动 DFO 在各个 OBServer上并行执行。

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读-7

8. 报告【SQL_PLAN_MONITOR 】详情篇

这部分主要是算子展开和线程展开，你能看到哪些算子耗费时间比较长，哪些线程耗费时间多，同理你也可以看到哪些线程是不干活的，如果大部分线程都是空闲状态，那就得注意了，如果你加了并行，那大概率并行没什么用，有线程在偷懒，赶紧看看是不是并行设置的不对吧。并行这块可以参看博客：

并行执行系列的内容大家可以参考以下博客。


第一篇	并行执行概念
第二篇	设定并行度
第三篇	并发控制与排队
第四篇	并行执行分类
第五篇	并行执行控制参数
第六篇	并行执行诊断及调优技巧
第七篇	并行执行 PoC QuickStart

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读-8

9. 案例: 统计信息不准导致走错执行计划

执行SQL如下：

SELECT 
    `t`.`date` AS `date`,
    SUM(`t`.`adcost`) AS `adcost`,
    SUM(`t`.`ns`) AS `ns`,
    SUM(`t`.`nc`) AS `nc`,
    SUM(`t`.`realtimeordernum`) AS `realtimeordernum`,
    SUM(`t`.`realtimeorderprice`) AS `realtimeorderprice`
FROM
    (select * from `galileo`.`report_game_consume_conversion` where `date` BETWEEN '2024-07-21' AND '2024-07-22') AS `t`
        JOIN (
        SELECT
            `date` AS `date1`,
            `solutionid`
        FROM
            `galileo`.`report_game_solution`
        WHERE
            `date` BETWEEN '2024-07-21' AND '2024-07-22'
    ) AS `t1` ON `t1`.`date1`=`t`.`date`
        AND `t`.`solutionid`=`t1`.`solutionid`
        JOIN (
        SELECT
            `date` AS `date2`,
            `adspaceid`
        FROM
            `galileo`.`report_game_adspace`
        WHERE
            `date` BETWEEN '2024-07-21' AND '2024-07-22'
    ) AS `t2` ON `t2`.`date2`=`t`.`date`
        AND `t`.`adspaceid`=`t2`.`adspaceid`
        LEFT JOIN (
        SELECT
            `date` AS `date3`,
            `solutionid`,
            `adspaceid`
        FROM
            `galileo`.`report_game_order`
        WHERE
            `date` BETWEEN '2024-07-21' AND '2024-07-22'
        GROUP BY
            `date`,
            `solutionid`,
            `adspaceid`
    ) AS `t3` ON `t3`.`date3`=`t`.`date`
        AND `t`.`solutionid`=`t3`.`solutionid`
        AND `t`.`adspaceid`=`t3`.`adspaceid`
GROUP BY
    `t`.`date` limit 10

从报告中我们看到了估行和吐行相差太大了，我们再去看报告中实际的数据行数，发现有百万级别，这也证明了算子吐行是准确的，估行相差太大了，这种估行不准非常容易误导优化器，导致优化器给出错误的执行计划。【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读-9

ok, 带着怀疑我们往下看看执行计划是怎么个事。发现有三次PHY_NESTED_LOOP_JOIN动作，三张表都是百万级别的吐行，还去走PHY_NESTED_LOOP_JOIN是非常耗费时间的，不如直接hash join。

我们看了一眼SQL，发现其实SQL看起来复杂，其实一点也不复杂，多表关联而已。试试走hash join 会怎么行，说干就干，直接通过hint 来调整算子

/*+ 
LEADING(@"SEL$1" (("galileo"."report_game_solution"@"SEL$3" "galileo"."report_game_consume_conversion"@"SEL$2") "galileo"."report_game_adspace"@"SEL$4")) 
USE_HASH(@"SEL$1" "galileo"."report_game_adspace"@"SEL$4")                                                                                                                                                            |
USE_HASH(@"SEL$1" "galileo"."report_game_consume_conversion"@"SEL$2")
*/

说明：hint这块的内容稍微高阶一点，但是学会了很受用，大家可以看看文档： https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001053019

小知识点：不知道咋写hint的时候，教你一招，去看看explain extend [SQL] 的结果，照着改就行

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读-10

加完hint之后执行 4s出结果，从300s --> 4s，直接提升了2个数量级。

10. 附录

obdiag 下载地址： https://www.oceanbase.com/softwarecenter
obdiag 官方文档： https://www.oceanbase.com/docs/obdiag-cn
obdiag github地址： https://github.com/oceanbase/obdiag
obdiag SIG 营地： https://oceanbase.yuque.com/org-wiki-obtech-vh7w9r/imzr6c

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读

1. 前言

2. 报告样式

3. 报告【执行计划】篇

4. 报告【SCHEMA 信息】篇

5. 报告【SQL_AUDIT信息】篇

6. 报告【SQL_PLAN_MONITOR DFO级调度时序汇总】篇

7. 报告【SQL_PLAN_MONITOR SQC级汇总】篇

8. 报告【SQL_PLAN_MONITOR 】详情篇

9. 案例: 统计信息不准导致走错执行计划

10. 附录

相关文章

发布评论取消回复

泡泡

百度搜索：蓝易云 – Docker的安装与卸载(Centos、Ubuntu )

PostgreSQL系数据库使用COPY导数时如何实现增量及重复数据更新导入

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读

1. 前言

2. 报告样式

3. 报告【执行计划】篇

4. 报告【SCHEMA 信息】篇

5. 报告【SQL_AUDIT信息】篇

6. 报告【SQL_PLAN_MONITOR DFO级调度时序汇总】篇

7. 报告【SQL_PLAN_MONITOR SQC级汇总】篇

8. 报告【SQL_PLAN_MONITOR 】详情篇

9. 案例: 统计信息不准导致走错执行计划

10. 附录

相关文章

发布评论 取消回复

泡泡

百度搜索：蓝易云 – Docker的安装与卸载(Centos、Ubuntu )

PostgreSQL系数据库使用COPY导数时如何实现增量及重复数据更新导入

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读

发布评论取消回复