負荷テスト環境におけるSQLの実行効率は、システムのパフォーマンスをある程度左右するため、SQL診断はパフォーマンスチューニングプロセスにおいて最も重要な段階です。本記事では、主にスローSQLの特定と最適化の方法について説明します。
ビュー統計
Plan Cacheの統計に基づき、頻繁に実行される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_IDに基づき、一定期間の実行データをサンプリングします。具体的なSQLは以下のとおりです。
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
具体的なSQLの書き方は、実際のニーズに合わせて調整できます。
OCPによる監視
OCPは現在、実行計画、実行頻度、実行時間などを含む、充実したスローSQL分析機能を備えています。OCPを利用することで、効率を大幅に向上させ、スローSQLに関する情報を迅速に特定できます。
SQLチューニング
スローSQLを特定した後、以下の観点から調査を進める必要があります：
テナントのリソースは十分ですか？
インデックスや実行計画は相対的に最適ですか？
affected rowsおよびreturn rowsのサイズを確認し、書き込みまたはクエリに関わるパーティション数や行数が多すぎないかどうかを判断します。
都市間アクセスやマシン間アクセスが存在しますか？
複雑なSQLでは、中間結果がディスクにダンプされる可能性があります。予想通りに行われているか確認する必要があります。
システムレベルでの調査：ダンプやディスクI/O使用率などを確認します。
SQLチューニングの詳細については、「SQLチューニングガイド」を参照してください。