スロークエリの特定と分析（2）

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 は、このクエリの実行計画に対するヒントの説明です。

   *************************** 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 |
   +----------------+------+------+------+
   

   物理計画に加えて、論理計画の収集も試すことができます。論理計画を収集する方法は非常に簡単で、SQLを直接EXPLAINするだけです。

   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)