Skip to content

Latest commit

 

History

History
123 lines (97 loc) · 6.83 KB

5.7-4-how-to-analyze-the-sql-audit-view.md

File metadata and controls

123 lines (97 loc) · 6.83 KB
Raw

7.4 如何分析 SQL 审计视图

SQL 审计视图可以查看在 OceanBase 里执行过的所有 SQL，不管是成功的还是失败的。这对开发同学了解自己的业务 SQL 和定位问题细节非常有帮助。

SQL 审计视图概述

SQL 审计视图 gv$sql_audit 是虚拟表，是内存中一个 FIFO 队列。

功能的开启和数据大小是通过下面的 OceanBase 集群参数控制的。

参数名 参数值 参数含义
enable_sql_audit TRUE 指定是否开启 SQL 审计。默认 TRUE 是开启。FALSE 是关闭。
sql_audit_queue_size 10000000 SQL 审计视图里最大记录数。
sql_audit_memory_limit 3G SQL 审计视图内存最大大小。

SQL 审计能保留的数据大小跟租户内存资源的大小也有关系，通常不会特别大。建议自行实时将 SQL 审计视图的数据抽取走，然后做二次分析。

您可以在租户里开启或关闭 SQL 审计功能。

set global ob_enable_sql_audit = on;

视图列定义如下：

列名 含义
SVR_IP SQL 执行的 OBServer 节点 IP
SVR_PORT SQL 执行的 OBServer 节点端口
REQUEST_ID 唯一标识
TRACE_ID 该 SQL 的 TRACE_ID 信息，在 OBServer 节点日志里可以关联查询相关日志
SID SQL 执行的 OBServer 节点上的会话 ID
CLIENT_IP 该 SQL 执行的客户端 IP，通常是 ODP IP
TENANT_ID 该 SQL 执行的租户 ID
TENANT_NAME 该 SQL 执行的租户名称
USER_NAME 该 SQL 执行的租户内部用户名
USER_CLIENT_IP 该 SQL 执行的实际客户端 IP
DB_ID 该 SQL 执行的数据库 ID
DB_NAME 该 SQL 执行的数据库名称
SQL_ID 该 SQL 的 SQL_ID
QUERY_SQL 该 SQL 的文本，如果太长会截断
PLAN_ID 该 SQL 的执行计划 ID
AFFECTED_ROWS 该 SQL 的写影响行数
RETURN_ROWS 该 SQL 的返回行数
PARTITION_CNT 该 SQL 访问的分区数量
RET_CODE 该 SQL 的返回代码
EVENT 该 SQL 的主要等待事件
PLAN_TYPE 该 SQL 的执行计划类型
1：本地 SQL；2：远程 SQL；3：分布式 SQL
IS_HIT_PLAN 是否命中执行计划
REQUEST_TIME 该 SQL 执行时间点（时间戳类型，可通过 usec_to_time 转换为可读时间格式）
ELAPSED_TIME 该 SQL 执行总耗时
NET_TIME 该 SQL 执行网络消耗时间
QUEUE_TIME 该 SQL 执行内部排队时间
DECODE_TIME 出队列后 decode 时间
GET_PLAN_TIME 该 SQL 执行计划生成时间
EXECUTE_TIME 该 SQL 实际内部执行时间（不包括 CPU 排队时间）

如何查看 SQL 审计视图

以下 SQL 可以在 sys 租户和业务租户里执行。业务租户只能查看属于自己租户的 SQL 数据。

  • 查看近期所有 SQL

    SELECT /*+ read_consistency(weak) ob_querytimeout(100000000) */  substr(usec_to_time(request_time),1,19) request_time_, s.svr_ip, s.client_Ip, s.sid,s.tenant_id, s.tenant_name, s.user_name, s.db_name, s.query_sql, s.affected_rows, s.return_rows, s.ret_code, s.event, s.elapsed_time, s.queue_time, s.execute_time, round(s.request_memory_used/1024/1024/1024,2) req_mem_mb, plan_type, is_executor_rpc, is_inner_sql, TRANSACTION_HASH, trace_id 
FROM gv$sql_audit s
WHERE 1=1  and s.tenant_id = 1002
 and user_name='u_tpcc' 
 and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) )
ORDER BY request_time DESC
LIMIT 100;

    request_time 是时间戳，可通过函数 usec_to_time 和 time_to_usec 与微秒数转换。

  • 分析统计近期所有 SQL

    根据 sql_id 统计平均总耗时、平均执行时间等。

    SELECT sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time, round(avg(execute_time)) avg_exec_time
FROM gv$sql_audit s
WHERE 1=1  and s.tenant_id = 1002
 and user_name='u_tpcc' 
 and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) )
GROUP BY sql_id
order by avg_elapsed_time desc 
;

  • 查看报错的 SQL

    ret_code 是 SQL 执行报错时的错误码。正常是 0，错误码为负数。

    SELECT /*+ read_consistency(weak) ob_querytimeout(100000000) */  substr(usec_to_time(request_time),1,19) request_time_, s.svr_ip, s.client_Ip, s.sid,s.tenant_id, s.tenant_name, s.user_name, s.db_name, s.sql_id,  s.query_sql, s.affected_rows, s.return_rows, s.ret_code, s.event, s.elapsed_time, s.queue_time, s.execute_time, round(s.request_memory_used/1024/1024/1024,2) req_mem_mb, plan_type, is_executor_rpc, is_inner_sql, TRANSACTION_HASH, trace_id 
FROM gv$sql_audit s
WHERE 1=1  and s.tenant_id = 1002
 and user_name='u_tpcc' 
 and ret_code < 0
 and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) )
ORDER BY request_time DESC
LIMIT 500;

  • 查看远程 SQL 和分布式 SQL

    plan_type 的值有三个：1 表示本地 SQL；2 表示远程 SQL；3 表示分布式 SQL。

    SELECT /*+ read_consistency(weak) ob_querytimeout(100000000) */  substr(usec_to_time(request_time),1,19) request_time_, s.svr_ip, s.client_Ip, s.sid,s.tenant_id, s.tenant_name, s.user_name, s.db_name, s.sql_id,  s.query_sql, s.affected_rows, s.return_rows, s.ret_code, s.event, s.elapsed_time, s.queue_time, s.execute_time, round(s.request_memory_used/1024/1024/1024,2) req_mem_mb, plan_type, is_executor_rpc, is_inner_sql, TRANSACTION_HASH, trace_id 
FROM gv$sql_audit s
WHERE 1=1  and s.tenant_id = 1002
 and user_name='u_tpcc' 
 and plan_type > 1
 and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) )
ORDER BY request_time DESC
LIMIT 500;

    远程 SQL 的出现需要结合事务的业务逻辑分析。