ストレステスト環境におけるSQLの実行効率は、システムのパフォーマンスをある程度決定するため、パフォーマンスチューニングプロセスにおいて、SQL診断は重要なステップです。本記事では、システムビューとモニタリングツールに基づく診断プロセスを提供し、遅延SQLを迅速に特定し、ターゲットを絞った最適化を行うことを支援します。
高頻度SQLの特定
Plan Cacheを通じて高頻度SQLを統計
gv$ob_plan_cache_plan_statビューを使用し、hit_count（ヒット数）でソートして、高頻度のSQLをフィルタリングします。
obclient> SELECT plan_id, sql_id, hit_count, avg_exe_usec, SUBSTR(statement, 1, 100)
FROM gv$ob_plan_cache_plan_stat
WHERE tenant_id = 1002
ORDER BY hit_count DESC
LIMIT 10;
出力例：
+---------------------+----------------------------------+-----------+--------------+------------------------------------------------------------------------------------------------------+
| plan_id | sql_id | hit_count | avg_exe_usec | substr(statement, 1, 100) |
+---------------------+----------------------------------+-----------+--------------+------------------------------------------------------------------------------------------------------+
| 7020372274597948570 | 17605A1DA6B6A2150E9FBCA5D4C7653A | 14767 | 562 | SELECT row_id, column_name, column_value FROM __all_core_table WHERE table_name = ? ORDER BY row_id, |
| 7020372274597948744 | 9CA2F8D24467EB1A28CA50EE09743A86 | 14707 | 468 | SELECT * FROM __all_acquired_snapshot WHERE tenant_id = ? |
| 7020372274597948743 | C1E19F19B0677FD5875F8C7C4FF30436 | 14707 | 492 | SELECT * FROM __all_freeze_info |
| 7020372274597948834 | 624F9288016A7704D6201261C0F494FF | 11823 | 110 | select * from __all_tenant_scheduler_job where tenant_id = ? and job = ? |
| 7020372274597948569 | 17605A1DA6B6A2150E9FBCA5D4C7653A | 7424 | 158 | SELECT row_id, column_name, column_value FROM __all_core_table WHERE table_name = ? ORDER BY row_id, |
| 7020372274597948812 | B6E4D946D9527AB02AFBEC16F74F2E25 | 7405 | 589 | SELECT * FROM __all_freeze_info ORDER BY frozen_scn DESC LIMIT ? |
| 7020372274597948741 | C1E19F19B0677FD5875F8C7C4FF30436 | 7389 | 80 | SELECT * FROM __all_freeze_info |
| 7020372274597948742 | 9CA2F8D24467EB1A28CA50EE09743A86 | 7389 | 68 | SELECT * FROM __all_acquired_snapshot WHERE tenant_id = ? |
| 49 | 17605A1DA6B6A2150E9FBCA5D4C7653A | 7382 | 1012 | SELECT row_id, column_name, column_value FROM __all_core_table WHERE table_name = ? ORDER BY row_id, |
| 49 | 17605A1DA6B6A2150E9FBCA5D4C7653A | 7381 | 1009 | SELECT row_id, column_name, column_value FROM __all_core_table WHERE table_name = ? ORDER BY row_id, |
+---------------------+----------------------------------+-----------+--------------+------------------------------------------------------------------------------------------------------+
10 rows in set
スローSQLのサンプリング分析
SQL Auditによる実行データの収集
ターゲットSQL_IDに基づいて、指定された時間帯内の実行データをサンプリングします。
obclient> SELECT svr_ip, plan_type, elapsed_time,
AFFECTED_ROWS, RETURN_ROWS, tx_id,
usec_to_time(REQUEST_TIME),
SUBSTR(query_sql, 1, 30)
FROM gv$ob_sql_audit
WHERE sql_id = 'F96CE9DFB959E383828A9D91575EE97F'
AND request_time > time_to_usec('2021-08-25 22:00:00')
AND request_time < time_to_usec('2021-08-25 22:50:00')
ORDER BY elapsed_time DESC
LIMIT 10;
出力例：
+---------------+-----------+--------------+---------------+-------------+----------------------+----------------------------+--------------------------------+
| svr_ip | plan_type | elapsed_time | AFFECTED_ROWS | RETURN_ROWS | transaction_hash | usec_to_time(REQUEST_TIME) | substr(query_sql, 1, 30) |
+---------------+-----------+--------------+---------------+-------------+----------------------+----------------------------+--------------------------------+
| 10.10.10.1 | 1 | 465114 | 0 | 0 | 10023348016566894972 | 2021-08-25 22:44:08.533070 | SELECT * FROM __all_root_table |
| 10.10.10.1 | 1 | 375107 | 0 | 0 | 13001988804803062059 | 2021-08-25 22:44:08.573525 | SELECT * FROM __all_root_table |
| 10.10.10.2 | 2 | 226940 | 0 | 0 | 0 | 2021-08-25 22:44:08.722480 | SELECT * FROM __all_root_table |
| 10.10.10.2 | 2 | 224519 | 0 | 0 | 0 | 2021-08-25 22:44:08.730139 | SELECT * FROM __all_root_table |
| 10.10.10.1 | 1 | 220272 | 0 | 0 | 6454906702768493748 | 2021-08-25 22:44:08.745529 | SELECT * FROM __all_root_table |
| 10.10.10.3 | 2 | 78577 | 0 | 0 | 0 | 2021-08-25 22:44:08.884916 | SELECT * FROM __all_root_table |
| 10.10.10.3 | 2 | 49034 | 0 | 0 | 0 | 2021-08-25 22:44:08.905322 | SELECT * FROM __all_root_table |
| 10.10.10.2 | 2 | 48885 | 0 | 0 | 0 | 2021-08-25 22:44:08.905610 | SELECT * FROM __all_root_table |
| 10.10.10.1 | 1 | 45239 | 0 | 0 | 11958340144270554107 | 2021-08-25 22:44:08.906159 | SELECT * FROM __all_root_table |
| 10.10.10.3 | 2 | 33454 | 0 | 0 | 0 | 2021-08-25 22:44:08.920650 | SELECT * FROM __all_root_table |
+---------------+-----------+--------------+---------------+-------------+----------------------+----------------------------+--------------------------------+
10 rows in set
OCPによるモニタリングの補助分析
OceanBase Cloud Platform (OCP) は、以下を含む包括的なスローSQL分析機能を提供します：
- 実行計画：SQLの実行パスを確認します。
- 実行頻度：SQLの呼び出し回数を統計します。
- 時間消費分布：実行時間の変動状況を分析します。
OCPを使用することで、スローSQLの重要な指標を迅速に特定し、診断効率を向上させることができます。
SQLチューニングチェックリスト
スローSQLを特定した後、以下の観点から調査する必要があります：
リソース面：
- テナントのリソース(CPU、メモリ、I/O)は十分ですか？
- リソース競合(例えば、高い同時実行によるキューイング)が発生していますか？
実行計画面：
- 現在の実行計画は最適ですか(他の可能な計画と比較して)？
- インデックスが不足しているか、またはインデックスの選択が不適切ですか？
データ面：
affected_rowsとreturn_rowsは過大ではありませんか？- クエリで対象となるパーティション数と行数は予想を超えていますか？
ネットワーク面：
- 都市間やマシン間のアクセスによる遅延が発生していますか？
中間結果面：
- 複雑なSQLの場合、中間結果がディスクにダンプされる可能性があります。期待どおりであるか確認する必要がありますか？
システム面：
- フリーズ(Freeze)操作によってリソースが占有されていますか？
- ディスクI/O利用率は高すぎませんか？
関連ドキュメント
- SQLチューニングガイド：インデックスの最適化やパーティション戦略の調整など、SQLチューニング方法について詳しく説明しています。