データベースの実行中にシステム性能の低下や特定のクエリが長時間完了しない場合は、以下の方法でスロークエリを迅速に特定し、その実行計画を分析することができます。
本記事では、システムビュー（GV$OB_PROCESSLIST、GV$OB_PLAN_CACHE_PLAN_STAT、GV$OB_PLAN_CACHE_PLAN_EXPLAIN）に基づく分析プロセスを提供しており、スロークエリと対応する実行計画を見つけるために使用します。
サンプルシナリオ
長時間実行される遅延クエリ（例：SELECT SLEEP(1000) FROM T1;）があると仮定します。次の手順でその実行計画を特定し、分析することができます。
-- テストテーブルを作成してデータを挿入
CREATE TABLE T1 (C1 INT, C2 INT);
INSERT INTO T1 VALUES (1, 1);
-- 遅延クエリを発行（実行時間1000秒）
SELECT SLEEP(1000) FROM T1;
分析プロセス
ステップ１：遅延SQLの特定
GV$OB_PROCESSLISTビューを使用して、現在実行中のアクティブなSQLをフィルタリングし、実行時間でソートすることで、最も遅いクエリを迅速に特定します。
-- sysテナントにログインして、次のクエリを実行します
SELECT USER, -- ユーザー名
tenant, -- テナント名
sql_id, -- SQL識別子
CONCAT(time, 's') AS time, -- 実行時間
info, -- SQL文の内容
svr_ip, -- 実行ノードIP
svr_port, -- 実行ノードポート
trace_id -- トレースID（さらなる分析用）
FROM GV$OB_PROCESSLIST
WHERE STATE = 'ACTIVE' -- アクティブな状態のクエリをフィルタリング
ORDER BY time DESC
LIMIT 1;
出力例：
+------+----------+----------------------------------+------+-----------------------------------------------------------------------------------------------------------------+----------------+----------+------------------------------------+
| USER | tenant | sql_id | time | info | svr_ip | svr_port | trace_id |
+------+----------+----------------------------------+------+-----------------------------------------------------------------------------------------------------------------+----------------+----------+------------------------------------+
| root | mysql001 | A585F887331EF7267F0C87B343C69D99 | 0s | SELECT USER,
tenant,
sql_id,
concat(time, 's') as time,
info,
svr_ip,
svr_port,
trace_id
FROM GV$OB_PROCESSLIST | 11.xxx.x.xx | 28824 | Y70980BA1CCFB-0006062A332767A6-0-0 |
+------+----------+----------------------------------+------+-----------------------------------------------------------------------------------------------------------------+----------------+----------+------------------------------------+
ステップ2：実行計画メタデータの取得
ステップ1で取得したsql_id、svr_ip、svr_portなどの情報を使用して、GV$OB_PLAN_CACHE_PLAN_STATからそのSQLの実行計画の詳細を照会します。
SELECT tenant_id, -- テナントID
svr_ip, -- ノードIPアドレス
svr_port, -- ノードポート
sql_id, -- SQL識別子
plan_id, -- 実行計画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$OB_PLAN_CACHE_PLAN_EXPLAINから物理実行計画のツリー構造を照会します。
SELECT OPERATOR, -- 演算子タイプ
NAME, -- 操作対象
ROWS, -- 予想される行数
COST -- 予想されるコスト
FROM GV$OB_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 |
+----------------+------+------+------+
ステップ4：ロジック実行計画の追加分析
ロジック実行計画を確認するには、SQLに対して直接EXPLAINコマンドを実行します：
EXPLAIN SELECT SLEEP(1000), RAND() FROM T1;
ロジック計画の出力：
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)