通过 gv$sql_audit 视图统计某段时间内的读写流量

本文介绍在 OceanBase 数据库中如何通过 gv$sql_audit 视图统计某段时间内的读写流量，来判断是否有大事务请求。

适用版本

OceanBase 数据库 V2.x 和 V3.x 版本。

通过 gv$sql_audit 视图统计读写流量

• 写请求统计

  select /*+READ_CONSISTENCY(weak),parallel(128),QUERY_TIMEOUT(150000000)*/ usec_to_time(min(REQUEST_TIME)) as min_request_time,round(avg(affected_rows)) as avg_write_rows,round(avg(REQUEST_MEMORY_USED)/1024/1024) as avg_request_memory_used_MB,count(1) as sum_executes from gv$sql_audit where tenant_id=xxxx and usec_to_time(REQUEST_TIME) between $time1 and $time2 and return_rows = 0;
  

  说明

  将 "xxxx" 替换为相应的租户 ID，将 "$time1" 和 "$time2" 替换为相应的请求的起始时间和结束时间。执行此查询可以统计出最早的请求时间、平均写入行数、平均请求内存使用量和总执行次数。

• 读请求统计

  select /*+READ_CONSISTENCY(weak),parallel(128),QUERY_TIMEOUT(150000000)*/ usec_to_time(min(REQUEST_TIME)) as min_request_time,round(avg(return_rows)) as avg_read_rows,round(avg(REQUEST_MEMORY_USED)/1024/1024) as avg_request_memory_used_MB,count(1) as sum_executes from gv$sql_audit where tenant_id=xxxx and usec_to_time(REQUEST_TIME) between $time1 and $time2 and affected_rows = 0;
  

  说明

  将 "xxxx" 替换为相应的租户 ID，将 "$time1" 和 "$time2" 替换为相应的请求的起始时间和结束时间。执行此查询可以统计出最早的请求时间、平均读取行数、平均请求内存使用量和总执行次数。