定位和分析慢查询（二）

对一个 TP 业务来说，SQL 都是短查询，不同 SQL 的执行时间都非常的短（毫秒级别），但频次非常的高。这类查询，即使是“慢查询”，在时间上也不太容易判定。对高频短查询来说，最关键的不再是观察它的绝对执行时间，而是要计算这个查询产生的读行有多大。

示例场景

下面这个查询，如果走 IDX_C1 这个索引的话，每次执行时需要读取非常少量的行数，耗时是百微秒或者毫秒级；但如果选择全表扫的话，每次执行需要读取大量的数据，耗时是数十毫秒到百毫秒级别。

如果纯粹看执行时间的话，这个查询并不慢。但这个查询如果频次非常高的话，整体还是会消耗非常多的计算资源，甚至打爆 CPU。

CREATE TABLE T1 (C1 INT, C2 INT);
CREATE INDEX IDX_C1 ON T1 (C1);

// insert 10K rows into T1 and C1 is almost unique

SELECT * FROM T1 WHERE C1 = 1;

查询短查询

定位这类慢查询，需要进行一些聚合统计，统计一类查询的整体资源消耗。在 OceanBase 数据库中，可以利用 GV$OB_SQL_AUDIT 这个视图去发掘这类慢查询。典型的诊断 SQL 如下：

以下查询统计的是某一类 SQL 按照读行量排序前 10 的请求。如果发现排在最前的几个请求整体读行量明显高出了 1~2 个数量级。那么这些请求优化的价值就会非常高。如果可以优化这些请求的执行计划，整体可以得到非常大的收益。

在实际诊断的过程中，读行量不是唯一会导致 CPU 飙高的指标，诊断过程中还可以尝试按照其他字段进行排序分析，例如，按照总的 CPU 时间排序；观察重试的次数（retry_cnt）；观察 RPC 的次数（rpc_count）等等。综合分析多个维度找出潜在的慢查询。

select /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/  svr_ip, sql_id,
    tenant_id, tenant_name, user_name, db_name, plan_id,
    count(*) exections,
    max(event) event,
    max(table_scan) table_scan,
    sum(case when ret_code = 0 then 0 else 1 end) fail_times,
    sum(rpc_count) rpc_count,
    sum(retry_cnt) retry_cnt,
    sum(case when plan_type = 2 then 1 else 0 end)
    remote_plans,
    sum(case when is_hit_plan = 1 then 0 else 1 end) miss_plans,
    round(avg(elapsed_time)) elapsed_time,
    round(max(elapsed_time)) max_elapsed_time,
    round(avg(execute_time)) execute_time,
    round(avg(( execute_time - total_wait_time_micro + get_plan_time ))) cpu_time,
    round(max(( execute_time - total_wait_time_micro + get_plan_time ))) max_cpu_time,
    round(avg(queue_time)) queue_time,
    round(avg(net_wait_time)) netwait_time,
    round(avg(user_io_wait_time)) iowait_time,
    round(avg(get_plan_time)) getplan_time,
    round(avg(decode_time)) decode_time,
    round(avg(total_wait_time_micro)) total_wait_time,
    round(avg(application_wait_time)) app_wait_time,
    round(avg(concurrency_wait_time)) concurrency_wait_time,
    round(avg(schedule_time)) schedule_time,
    round(avg(return_rows)) return_rows,
    round(avg(affected_rows)) affected_rows,
    round(avg(row_cache_hit * 2 + bloom_filter_cache_hit * 2 + block_cache_hit +
    disk_reads)) logical_reads,
    round(avg(row_cache_hit)) row_cache_hit,
    round(avg(bloom_filter_cache_hit)) bloom_filter_cache_hit,
    round(avg(block_cache_hit)) block_cache_hit,
    round(avg(disk_reads)) disk_reads,
    round(avg(memstore_read_row_count)) memstore_read_row_count,
    round(avg(ssstore_read_row_count)) ssstore_read_row_count,
    sum(memstore_read_row_count + ssstore_read_row_count) as total_row_count
    from   gv$ob_sql_audit
    where  is_inner_sql = 0
    group  by svr_ip, sql_id order by total_row_count desc limit 10;

查询短查询信息

根据上查找短 SQL 得到的结果，可以搜索这个查询的计划信息。

1. 执行以下命令获取短 SQL 的计划信息。

   下面的查询中，tenant_id 根据实际分析的业务确定，svr_ip、sql_id 根据查找慢 SQL 中查询到的结果填写。

   SELECT tenant_id,
      svr_ip,
      svr_port,
      sql_id,
      plan_id,
      last_active_time,
      first_load_time,
      outline_data
   FROM GV$OB_PLAN_CACHE_PLAN_STAT
   WHERE TENANT_ID = 1002
   AND SQL_ID = '3310A1D1D81D4BA92CEEF42538136DD1'
   AND SVR_IP = '11.xxx.x.xx'
   AND SVR_PORT = 35046;
   

   执行以上命令返回如下结果。得到这个查询的执行计划的编号（plan_id），这个执行计划第一次生成的时间（first_load_time）以及最后一次被使用的时间（last_active_time）。对跑批来说，last_active_time 大概率就是慢查询发起的时间。

   outline_data 是对这个查询执行计划的一个 hint 描述。

   *************************** 1. row ***************************
       tenant_id: 1002
           svr_ip: 11.xxx.x.xx
           svr_port: 35046
           sql_id: 3310A1D1D81D4BA92CEEF42538136DD1
           plan_id: 741
   last_active_time: 2022-08-04 11:00:34.466037
   first_load_time: 2022-08-04 11:00:34.466037
       outline_data: /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "test.t1"@"SEL$1") END_OUTLINE_DATA*/
   

2. 获得慢 SQL 的计划形态。

   执行以下查询可以获得执行计划的树形形态。

   SELECT OPERATOR, NAME, ROWS, COST FROM GV$PLAN_CACHE_PLAN_EXPLAIN
   WHERE TENANT_ID = 1002 AND
       SVR_IP = '11.xxx.x.xx' AND
       SVR_PORT = 35046 AND
       PLAN_ID = 741 ;
   

   下面是上述查询的物理计划。

   +----------------+------+------+------+
   | OPERATOR       | NAME | ROWS | COST |
   +----------------+------+------+------+
   | PHY_TABLE_SCAN | t1   |    1 |   45 |
   +----------------+------+------+------+
   

   除了物理计划之后，也可以尝试采集一下逻辑计划，采集逻辑计划的方式很简单，直接 explain SQL 即可。

   explain select sleep(1000), rand() from t1;
   
   *************************** 1. row ***************************
   Query Plan: ===================================
   |ID|OPERATOR  |NAME|EST. ROWS|COST|
   -----------------------------------
   |0 |TABLE SCAN|t1  |1        |46  |
   ===================================
   
   Outputs & filters:
   -------------------------------------
   0 - output([sleep(?)], [rand()]), filter(nil),
       access([t1.__pk_increment]), partitions(p0)