查询 SQL 审计表信息
SQL 审计表 gv$ob_sql_audit 记录了最近执行的 SQL 详细信息，可以用于分析 SQL 执行情况。在使用 gv$ob_sql_audit 时主要关注耗时信息和特征信息。
SELECT svr_ip, trace_id, user_name, db_name, sql_id, query_sql, plan_id FROM oceanbase.gv$ob_sql_audit LIMIT 1;
查询耗时信息
SELECT svr_ip, query_sql, elapsed_time, queue_time, execute_time, total_wait_time_micro, event FROM oceanbase.gv$ob_sql_audit LIMIT 1;
查询特征信息
SELECT sql_id, query_sql, is_executor_rpc, is_inner_sql, ret_code, plan_type, rpc_count, is_hit_plan, request_type FROM oceanbase.gv$ob_sql_audit LIMIT 1;
查询最近 5 分钟出错的语句
SELECT substr(usec_to_time(s.request_time),1,19) request_time,
s.svr_ip, s.user_name, s.db_name, s.sql_id, s.query_sql, s.ret_code, 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, trace_id
FROM oceanbase.gv$ob_sql_audit s
WHERE ret_code < 0
AND db_name=DATABASE()
AND request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 5 MINUTE) )
ORDER BY request_time DESC
LIMIT 100;
查询慢 SQL
SELECT svr_ip, query_sql,
elapsed_time, queue_time, execute_time, total_wait_time_micro, event
FROM oceanbase.gv$ob_sql_audit WHERE query_sql LIKE ‘%test%’ LIMIT 10;
查询耗时最长的 SQL
SELECT svr_ip, query_sql,
elapsed_time, queue_time, execute_time, total_wait_time_micro, event
FROM oceanbase.gv$ob_sql_audit
WHERE is_executor_rpc=0 AND is_inner_sql=0
AND request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 5 MINUTE) )
ORDER BY elapsed_time DESC LIMIT 10;
查询本地计划 CPU 时间
SELECT svr_ip, query_sql,plan_type,
(execute_time+get_plan_time-total_wait_time_micro) AS cpu_time,
elapsed_time, queue_time, execute_time, total_wait_time_micro
FROM oceanbase.gv$ob_sql_audit
WHERE is_executor_rpc=0 AND is_inner_sql=0 AND plan_type=1
AND request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 5 MINUTE) )
ORDER BY cpu_time DESC LIMIT 10;
查询分布式计划和远程计划 CPU 时间
SELECT trace_id, sql_id, substr(usec_to_time(request_time),1,19) AS request_time_, query_sql, plan_type,
sum(execute_time+get_plan_time-total_wait_time_micro) AS cpu_time,
sum(execute_time) AS execute_time,
sum(get_plan_time) AS get_plan_time,
sum(total_wait_time_micro) AS total_wait_time_micro
FROM oceanbase.gv$ob_sql_audit
WHERE is_executor_rpc=0 AND is_inner_sql=0 AND plan_type<>1
AND request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 5 MINUTE) )
GROUP BY trace_id
ORDER BY cpu_time DESC LIMIT 10;