那些丢失的笔记：OceanBase篇(SQL Audit)

前言

继续整理分享OceanBase的常用脚本，本篇为SQL Audit视图的应用，下面脚本适用OB2.x和3.x版本。

SQL Audit（OB3.X MySQL）

【sys租户/业务租户执行】

#1、最近执行的10条SQL语句

select usec_to_time(REQUEST_TIME),DB_NAME,USER_NAME,QUERY_SQL from oceanbase.gv$sql_audit order by REQUEST_TIME desc limit 10;

#2、匹配某个文本的最近执行的10条SQL

select usec_to_time(REQUEST_TIME),* from oceanbase.gv$sql_audit where QUERY_SQL like ‘%update%’ order by REQUEST_TIME desc limit 5\G

#3、最近 1000s 某个租户的 TOP SQL 耗时监控

#期望值: 观察 SQL 整体耗时、cpu_time、物理读及逻辑消耗是否合理，一般单行 insert 和主键查询在 500us 以内

select /*+read_consistency(weak),query_timeout(100000000)*/ SQL_ID,substr(query_sql, 1, 50) as query_sql,count(1),avg(ELAPSED_TIME),avg(EXECUTE_TIME),avg(QUEUE_TIME),avg(AFFECTED_ROWS),avg(GET_PLAN_TIME) from oceanbase.gv$sql_audit where time_to_usec(now(6))-request_time<1000000000 and tenant_name=’tenant’ group by SQL_ID order by avg(ELAPSED_TIME)*count(1) desc limit 20;

#4、查看集群中 SQL 请求流量是否均匀

select t2.zone, t1.svr_ip, count(*) as QPS from oceanbase.gv$sql_audit t1, oceanbase.__all_server t2 where t1.svr_ip = t2.svr_ip and t1.tenant_id = 1003 and IS_EXECUTOR_RPC = 0 and request_time > (time_to_usec(now()) – 1000000) and request_time < time_to_usec(now()) group by t1.svr_ip order by QPS;

#5、定位所有 SQL 中消耗 CPU 最多的 SQL

#思路：消耗 CPU 的时间是 elapsed_time – queue_time，因为queue_time 的过程是在排队，并不消耗 CPU。排查消耗 CPU 最多的 SQL 在 CPU 飙高的场景非常有用

select sql_id, substr(query_sql, 1, 50) as query_sql,sum(elapsed_time – queue_time) sum_t, count(*) cnt, avg(get_plan_time), avg(execute_time) from oceanbase.gv$sql_audit where tenant_id = 1003 and request_time > (time_to_usec(now()) – 10000000) and request_time < time_to_usec(now()) group by sql_id order by sum_t desc limit 10;

#6、查看 SQL 的执行是否出现大量不合理的请求使用了远程执行

思路：sql_audit 的 plan_type 字段可以看到该 SQL 的执行计划类型：

plan_type=1：本地执行计划，性能最好。

plan_type=2 : 远程执行计划。

plan_type=3 : 分布式执行计划，包含本地执行计划和远程执行计划。

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

select count(*), plan_type from oceanbase.gv$sql_audit where tenant_id = 1003 and IS_EXECUTOR_RPC = 0 and request_time > (time_to_usec(now()) – 10000000) and request_time < time_to_usec(now()) group by plan_type;

#7、查询全表扫描的 SQL

思路：sql_audit 的 table_scan 字段是标识语句是否进行全表扫描，=1 表示全表扫描，可以进一步分析 SQL 是否可以通过添加索引防止全表扫描

select query_sql from oceanbase.gv$sql_audit where table_scan = 1 and tenant_id = 1003 group by sql_id;

【gv$sql_audit视图的字段含义】

ELAPSED_TIME：本次执行的总时间（从请求到达到执行结束），由多个子阶段组成：

NET_TIME：发送 RPC 到接收到请求的时间。

NET_WAIT_TIME：接收到请求到进入队列时间。

QUEUE_TIME：队列时间，反映当前租户的请求积压情况。

DECODE_TIME：出队列后 Decode 时间。

GET_PLAN_TIME：生成执行计划的时间，反映当前租户 Plan Cache 的健康状况。

EXECUTE_TIME：计划的执行时间。

EXECUTE_TIME：本次实际执行的时间，由 CPU 时间和 TOTAL_WAIT_TIME_MICRO 组成。TOTAL_WAIT_TIME_MICRO 由 APPLICATION_WAIT_TIME、CONCURRENCY_WAIT_TIME、USER_IO_WAIT_TIME、SCHEDULE_TIME 等几个部分组成。EXECUTE_TIME 为实际上花费执行的总时间，包含 CPU 计算所需的时间（CPU_TIME）和各种等待时间（TOTAL_WAIT_TIME_MICRO）。

APPLICATION_WAIT_TIME：所有 application 类事件的总时间。

CONCURRENCY_WAIT_TIME：所有 concurrency 类事件的总时间。

USER_IO_WAIT_TIME：所有 user_io 类事件的总时间。

SCHEDULE_TIME：所有`schedule 类事件的时间。

逻辑读：请求执行中读取数据时会首先读取各级缓存（对应 ROW_CACHE_HIT、BLOOM_FILTER_CACHE_HIT、BLOCK_CACHE_HIT 等字段），如果全部没有命中会产生一次实际的磁盘读取（对应 DISK_READS 字段）。通过统计缓存读取次数和磁盘读取次数，可以得出该请求执行过程中扫描行数的多少（扫描行数不等于实际物理读，会首先扫描各级缓存），从而判断该 SQL 是否需要优化。

ROW_CACHE_HIT：行缓存命中次数。

BLOOM_FILTER_CACHE_HIT：bloom filter缓存命中次数。

BLOCK_CACHE_HIT：块缓存命中次数。

BLOCK_INDEX_CACHE_HIT：块索引缓存命中次数。

DISK_READS：物理读次数。

后记

如果您发现有错误的地方，还请留言指出，感谢。

本文转载自DBA阿Q

想了解更多干货，可通过下方扫码关注