TP業務シナリオにおいて、SQLの実行時間は通常非常に短い（ミリ秒単位）ですが、高頻度での実行により全体のリソース消費が急増する可能性があります。このようなクエリの「遅さ」は、単一実行時間ではなく、総読み取り行数または累積リソース消費量に現れます。例えば、高頻度のクエリが毎回フルスキャンを行う場合、単一実行時間が10msであっても、1秒間に1000回実行されると、総リソース消費量は低頻度の長時間クエリを大幅に上回る可能性があります。
シナリオ例
以下のSQLは、インデックスIDX_C1を使用する場合、毎回わずかな数行（数百マイクロ秒程度）しか読み取らない。しかし、フルテーブルスキャンを選択した場合、毎回大量のデータ（数十ミリ秒程度）が読み取られる。単一実行時間は長くないものの、クエリ頻度が非常に高い場合（例えば1秒間に数千回）、フルテーブルスキャンによりCPUリソースが枯渇する可能性がある。
CREATE TABLE T1 (C1 INT, C2 INT);
CREATE INDEX IDX_C1 ON T1 (C1);
-- 10K行のデータを挿入し、C1値はほぼ一意である
INSERT INTO T1 VALUES (1, 1), (2, 2), ... (10000, 10000);
-- 高頻度クエリ：単一実行時間は短いが、読み取る行数には大きな差が生じる可能性がある
SELECT * FROM T1 WHERE C1 = 1;
ステップバイステップの診断プロセス
ステップ1：GV$OB_SQL_AUDITを使用して頻繁に実行されるクエリのリソース消費を統計する
OceanBaseは、読み取り行数やCPU時間などの観点からSQLの累積的な影響を統計するためのシステムビューGV$OB_SQL_AUDITを提供します。以下のSQL文は、合計読み取り行数でソートされ、上位10個の頻繁に実行される短いクエリが特定されます：
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;
主要フィールドの説明：
total_row_count：このSQL文が実行したすべての累計読み取り行数であり、頻繁に実行される短いクエリを判断するための重要な指標です。avg_cpu_time：平均CPU消費時間であり、リソース集約型クエリを判断するための補助的な指標です。rpc_countとretry_cnt：高値はネットワーク遅延や再試行の問題を示している可能性があります。
もし上位にランクインしたいくつかのリクエストが、全体的な読み取り行数で明らかに1～2桁も高いことが分かった場合、これらのリクエストを最適化する価値は非常に高いと言えます。これらのリクエストの実行計画を最適化できれば、全体として非常に大きなメリットを得ることができます。
実際の診断プロセスでは、読み取り行数だけがCPU使用率を急上昇させる唯一の要因ではありません。そのため、診断時には他のフィールドに基づいてソート分析を試すこともできます。例えば、合計CPU時間でソートする、再試行回数（retry_cnt）を観察する、RPC回数（rpc_count）を観察するなどです。複数の観点から総合的に分析することで、潜在的なスロークエリを特定します。
ステップ2：問題のあるSQLの実行計画を特定する
ステップ1 の結果（例えば sql_id、tenant_id、svr_ip）に基づいて、GV$OB_PLAN_CACHE_PLAN_STAT を使用して実行計画のメタデータを確認します。
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;
出力例：
*************************** 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*/
出力フィールドの分析：
| フィールド | 役割 |
|---|---|
trace_id |
クエリの一意のトレース識別子。 |
plan_id |
クエリの実行計画の番号。 |
last_active_time |
プランが最後に使用された時間で、通常はスロークエリの開始時間に対応します。バッチ処理においては、last_active_time はスロークエリの開始時間である可能性が高いです。 |
first_load_time |
実行計画が初めて生成された時間。 |
outline_data |
オプティマイザーが生成したヒントを記録し、計画生成ロジックの分析を支援します。 |
ステップ3：実行計画の詳細を分析する
ステップ2で取得したplan_idを使用して、GV$PLAN_CACHE_PLAN_EXPLAINから物理的実行計画を照会します。
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 * FROM T1 WHERE C1 = 1;
ロジック計画の出力：
Query Plan: ===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1 |10000 |46 | -- インデックスヒットしない場合、予想される行数は実際よりもはるかに高くなる可能性があります。
==================================
Outputs & filters:
-------------------------------------
0 - output([t1.*]), filter([t1.c1 = 1]),
access([t1.__pk_increment]), partitions(p0)
主要な分析指標
以下の指標は、問題の特定に役立ちます。
- 読み取り行数（
total_row_count）：- 特定のSQL文の読み取り行数が他のクエリよりも著しく高い場合（例えば、1～2桁以上高い場合）、その実行計画を優先的に最適化する必要があります。
- CPU時間（
avg_cpu_time）：- 高頻度に実行されるSQL文の平均CPU消費量が継続的に増加すると、システム負荷の問題が発生する可能性があります。
- 実行計画の異常：
- 物理計画が
TABLE_SCANであるにもかかわらず、実際にはインデックスIDX_C1にヒットすべきである場合、オプティマイザーが非効率的な計画を選択したことを示します。
- 物理計画が