SQL Explainによる最適化の実践

ある顧客のデータベースシステムでパフォーマンス障害が発生し、多数のトランザクションが輻輳する現象が見られました。直接的な原因は、一部のOceanBaseデータベースサーバーノードのCPU使用率が100%に達し、トランザクションが正常に処理できなくなったことでした。GV$OB_SQL_AUDITビューから取得した情報を分析した結果、問題のSQLの平均実行時間が2秒であり、そのSQLの総CPU使用量がサーバーノードのCPUリソースの70%を超えていることが特定されました。さらに、Explain情報および関連するテーブルとビューのDDL定義を組み合わせて、パフォーマンスに影響を与える原因を特定しました。一連のDDL最適化を経て、そのSQLのCPU実行時間を4ミリ秒まで削減し、パフォーマンスボトルネック問題を完全に解消しました。

トラブルシューティングのアプローチ

本ケースのトラブルシューティングおよび最適化のアプローチは以下の通りです：

1. GV$OB_SQL_AUDIT ビューをクエリして、TOP SQLを特定します。

   次のSQLステートメントを実行して GV$OB_SQL_AUDIT ビューをクエリし、問題のSQLを特定します。本ケースでは、問題発生後30分間の指定されたOBserverノード（svr_ip）上のSQL（GROUP BY sql_id）と、問題時間帯の総CPU使用率（SUM(execute_time)/(30*60*1000*1000) AS cpu_cnt）などの情報を主に照会します。

   SELECT sql_id,
          COUNT(*)               AS executions,
          SUM(execute_time)      AS tot_cpu_time,
          AVG(execute_time)      AS avg_cpu_time,
          SUM(execute_time)/(30*60*1000*1000)     AS cpu_cnt,
          query_sql
       FROM oceanbase.GV$OB_SQL_AUDIT
       WHERE tenant_id= 'mysql001'
           AND svr_ip='xxx.xxx.xxx.xxx'
           AND request_time BETWEEN (TIME_TO_USEC(NOW())-30*60*1000*1000) AND TIME_TO_USEC(NOW())
           AND is_executor_rpc =0
           GROUP BY sql_id
           HAVING COUNT(*)>1
           ORDER BY cpu_cnt DESC LIMIT 10;
   

   クエリの結果、SELECT ... FROM V_TT01 WHERE (COL001 IN (...)) カテゴリのSQLの実行頻度とCPU使用率が高く、約70%（80 c/108 c）であり、かつ同一のOBServerノードに集中していることがわかりました。

   +----------------------------------+------------+--------------+--------------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------+
   |       sql_id                     | executions | tot_cpu_time | avg_cpu_time | cpu_cnt | query_sql      |
   +----------------------------------+------------+--------------+--------------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------+
   | 2705182A6EAB699CEC8E59DA80710B64 |   23,425 |   33,818,182,526  |      1,443,679 |      56 | SELECT ... FROM V_TT01 WHERE (COL001 IN (20017476)) |
   | 32AB97A0126F566064F84DDDF4936F82 |    7,112 |     7,926.295,919 |      1,114,496 |      13 | SELECT ... FROM V_TT01 WHERE (COL001 IN (20035891,20035892)) |
   | A5F514E873BE9D1F9A339D0DA7481D69 |    4,046 |     6,053,872,739 |      1,496,261 |      10 | SELECT ... FROM V_TT01 WHERE (COL001 IN (20017892,20007654,20025634)) |
   | 31FD78420DB07C11C8E3154F1658D237 |      186 |       646,926,207 |      3,478,098 |       1 | SELECT ... FROM V_TT01 WHERE (COL001 IN (20027596,20035891))  |
   | C48AEE941D985D8DEB66892228D5E845 |       28 |       569,004,505 |     20,321,589 |       1 | SELECT ... |
   | 101B7B79DFA9AE801BEE4F1A234AD294 |      155 |       440,190,675 |      2,839,940 |       1 | SELECT ... |
   | 1D0BA376E273B9D622641124D8C59264 |      423 |       429,140,148 |      1,014,516 |       1 | SELECT ... |
   | 64CF75576816DB5614F3D5B1F35B1472 |      113 |       326,338,159 |      2,887,948 |       1 | SELECT ... |
   | 23D1C653347BA469396896AD9B20DCA1 |      381 |       309.367.170 |        811,987 |       1 | SELECT ... |
   | FA4F493FA5CE2DCC64F51CF3754F96C6 |        7 |       289,191,515 |     41,313,074 |       0 | SELECT ... |
   +------------+----------------------------+--------------+------------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
   10 rows in set
   
   

2. 問題のSQLのExplain情報を照会し、パフォーマンスに影響を与えるキーポイントを分析します。

   EXPLAIN コマンドを実行して、この問題のSQLのExplain情報を取得します。

   =================================================================================
   |ID|OPERATOR                             |NAME                 |EST. ROWS|COST  |
   ---------------------------------------------------------------------------------
   |0 |EXCHANGE IN REMOTE                   |                     |177      |432961|
   |1 | EXCHANGE OUT REMOTE                 |                     |177      |397632|
   |2 |  SUBPLAN SCAN                       |V_TT01               |177      |397632|
   |3 |   UNION ALL                         |                     |177      |397629|
   |4 |    NESTED-LOOP JOIN CARTESIAN       |                     |0        |151990|
   |5 |     TABLE SCAN                      |TBL1(IDX_TBL1_COL001)|1        |92    |
   |6 |     MATERIAL                        |                     |0        |151899|
   |7 |      NESTED-LOOP ANTI JOIN CARTESIAN|                     |0        |151899|
   |8 |       TABLE SCAN                    |TT01(IDX_TT01_COL001)|1        |92    |
   |9 |       MATERIAL                      |                     |177      |151781|
   |10|        SUBPLAN SCAN                 |VIEW1                |177      |151780|
   |11|         TABLE SCAN                  |TBL3(IDX_TBL3_COL170)|177      |151777|
   |12|    NESTED-LOOP JOIN CARTESIAN       |                     |177      |236327|
   |13|     TABLE GET                       |TBL2                 |1        |46    |
   |14|     TABLE SCAN                      |TBL3(IDX_TBL3_COL170)|177      |235510|
   =================================================================================
   
   Outputs & filters:
   -------------------------------------
   
     5 - output(......), filter(nil),
         access(......), partitions(p0),
         is_index_back=true,
         range_key([TBL1.COL001(0x7f510ed97860)], [TBL1.COL002(0x7f48eec9baa0)]), range(20017476,MIN ; 20017476,MAX),
         range_cond([TBL1.COL001(0x7f510ed97860) = 20017476(0x7f510edbd750)])
   
     8 - output(......), filter(nil),
         access(......), partitions(p0),
         is_index_back=true,
         range_key([TT01.COL001(0x7f510ed97570)], [TT01.COL003(0x7f510ed97b50)]), range(20017476,MIN ; 20017476,MAX),
         range_cond([TT01.COL001(0x7f510ed97570) = 20017476(0x7f510edbcc50)])
   
    11 - output(......), filter([20017476 = cast(cast(TBL3.COL170(0x7f3f5b51c090), VARCHAR2(20 BYTE))(0x7f3f5b51d6b0), NUMBER(-1, -85))(0x7f3f5b51ce80)(0x7f3f5b51c380)]),
         access(......), partitions(p0),
         is_index_back=false, filter_before_indexback[false],
         range_key([TBL3.COL170(0x7f3f5b51c090)], [TBL3.COL002(0x7f3f5b528e80)]), range(MIN,MIN ; MAX,MAX)always true
   
    13 - output(......), filter(nil),
         access(......), partitions(p0),
         is_index_back=false,
         range_key([TBL2.COL004(0x7f0cb0ff9f20)]), range[20017476 ; 20017476],
         range_cond([TBL2.COL004(0x7f0cb0ff9f20) = 20017476(0x7f0cb1001170)])
   
    14 - output(......), filter([20017476 = cast(cast(TBL3.COL170(0x7f0cb0ffa210), VARCHAR2(20 BYTE))(0x7f0cb1002fa0), NUMBER(-1, -85))(0x7f0cb1002770)(0x7f0cb1001c70)]),
         access(......), partitions(p0),
         is_index_back=true, filter_before_indexback[true],
         range_key([TBL3.COL170(0x7f0cb0ffa210)], [TBL3.COL002(0x7f3f5b582c10)]), range(MIN,MIN ; MAX,MAX)always true
   

   上記の EXPLAIN 情報に基づき、このSQLの実行総コストは約432961と推定されます。コストは主に3番目の演算子 UNION ALL の部分に反映されています（コストは約397629）。UNION ALL の2つの分岐にはどちらも NESTED-LOOP JOIN が含まれており、コストはそれぞれ約151990と236327と推定されています。UNION ALL 部分の最初の分岐（4番目から11番目の演算子）の主なコストは11番目の演算子にあります。これはインデックス TBL3(IDX_TBL3_COL170) を使用した TABLE SCAN によるものです。2番目の分岐（12番目から14番目の演算子）の主なコストも14番目の演算子にあり、同様にインデックス TBL3(IDX_TBL3_COL170) を使用した TABLE SCAN によるものです。これにより、11番目と14番目の演算子のコストが高いのは、どちらも TBL3 のインデックスが原因である可能性が高く、本ケースのパフォーマンス問題のボトルネックを初期段階で特定できました。

   次に、11番目と14番目の演算子のOutputs & filters情報を分析します。その中で、range(MIN,MIN ; MAX,MAX)always true は、このSQLの実行過程でインデックスマッチングが行われなかったことを示しており、filter([20017476 = cast(cast(TBL3.COL170(0x7f0cb0ffa210), VARCHAR2(20 BYTE))(0x7f0cb1002fa0), NUMBER(-1, -85))(0x7f0cb1002770)(0x7f0cb1001c70)]) は、アクセス時にデータ型の変換が発生したことを示しています。これにより、関連フィールドのデータ型が統一されていないためデータ型の変換が行われ、TBL3 Colbert170 上のインデックスがマッチしなかった可能性があります。この結論に基づき、DDL定義と組み合わせてさらに分析を進めます。

    11 - output(......), filter([20017476 = cast(cast(TBL3.COL170(0x7f3f5b51c090), VARCHAR2(20 BYTE))(0x7f3f5b51d6b0), NUMBER(-1, -85))(0x7f3f5b51ce80)(0x7f3f5b51c380)]),
         access(......), partitions(p0),
         is_index_back=false, filter_before_indexback[false],
         range_key([TBL3.COL170(0x7f3f5b51c090)], [TBL3.COL002(0x7f3f5b528e80)]), range(MIN,MIN ; MAX,MAX)always true
   
    14 - output(......), filter([20017476 = cast(cast(TBL3.COL170(0x7f0cb0ffa210), VARCHAR2(20 BYTE))(0x7f0cb1002fa0), NUMBER(-1, -85))(0x7f0cb1002770)(0x7f0cb1001c70)]),
         access(......), partitions(p0),
         is_index_back=true, filter_before_indexback[true],
         range_key([TBL3.COL170(0x7f0cb0ffa210)], [TBL3.COL002(0x7f3f5b582c10)]), range(MIN,MIN ; MAX,MAX)always true
   

3. 関連するテーブルとビューのDDL定義を分析し、パフォーマンス問題の原因を特定します。

   まず、ビュー V_TT01 の定義から、UNION ALL の最初の分岐において tbl3 が NOT EXISTS サブクエリ内に存在することがわかります。EXPLAIN 情報と組み合わせることで、関連するサブクエリが NESTED-LOOP ANTI JOIN に書き換えられたことが導き出されます。さらに、ビュー V_TT01 では TO_NUMBER 変換が行われており、その中の col001 と col004 は col170 を介して TBL3 のインデックス (IDX_TBL3_COL170) に渡されます。

   次に、テーブル TBL3 の定義を分析すると、インデックス TBL3(IDX_TBL3_COL170) が COL170 フィールドに定義されており、データ型は VARCHAR2(20) であることがわかります。そして、このフィールドがビュー V_TT01 が他のテーブルと関連付けるフィールドです。ビュー V_TT01 の定義から、UNION ALL の最初の分岐において、11番目の演算子に対応する関連付けは "tbl3"."col170" = "tt01"."col001" = 20017476、2番目の分岐における14番目の演算子に対応する関連付けは "tbl3"."col170" = "tbl2"."col004" = 20017476 です。関連フィールドのデータ型が一致しないため、TO_NUMBER 変換が発生し、COL170 フィールドの VARCHAR2 型が強制的に NUMBER 型に変換されました。これは、11番目と14番目の演算子のOutputs & filtersにある filter 情報と一致しています。

   DDL定義と EXPLAIN 情報を総合すると、このカテゴリのSQL実行過程において、ビュー V_TT01 で発生した TO_NUMBER の強制変換が不適切であったため、インデックスのパフォーマンスが低下し、11番目と14番目の演算子のコスト推定値が大きくなったことがわかります。TO_NUMBER 変換を除去すべきです。

   /* 問題のSQLの定義 */
   SELECT ......
       FROM   v_tt01
       WHERE  COL001 IN (20017476);
   
   /* ビュー V_TT01の定義 */
   CREATE VIEW "V_TT01_OLD"
   AS
     (SELECT
             To_number("LOCAL_CXZX"."tt01"."col001") AS "COL001",
             ... ...
      FROM   "LOCAL_CXZX"."tt01",
             "LOCAL_CXZX"."tbl1"
      WHERE  ( "LOCAL_CXZX"."tt01"."col001" = "LOCAL_CXZX"."tbl1"."col001" )
             AND NOT EXISTS((SELECT 1
                             FROM   "LOCAL_CXZX"."tbl3"
                             WHERE  ( "LOCAL_CXZX"."tt01"."col001" =
                                      "LOCAL_CXZX"."tbl3"."col170"
                                    ))))
   
     UNION ALL
   
     (SELECT
             To_number("LOCAL_CXZX"."tbl2"."col004") AS "COL001",
             ... ...
      FROM   "LOCAL_CXZX"."tbl2",
             "LOCAL_CXZX"."tbl3" "TBL3"
      WHERE  ( "LOCAL_CXZX"."tbl2"."col004" = "tbl3"."col170" ))
   
      ;
   
   /* テーブル TBL1の定義*/
   CREATE TABLE "LOCAL_BDZGBGYY"."TBL1" (
     "COL001" NUMBER(16)  NOT NULL ,    // 5: To_number("tt01"."col001") = "tbl1"."col001" = 20017476,  To_number(NUMBER(16)) = NUMBER(20)
     ... ...
     PRIMARY KEY ("COL002")
   ) ;
   
   CREATE INDEX "LOCAL_BDZGBGYY"."IDX_TBL1_COL001" on "LOCAL_BDZGBGYY"."TBL1" (
    "COL001"
   ) GLOBAL ;
   
   /* テーブル TT01の定義 */
   CREATE TABLE "EINP_BASICINFO"."TT01" (
   ... ...
     "COL001" NUMBER(20)  NOT NULL,     // 8: To_number("tt01"."col001") = "tbl1"."col001" = 20017476,  To_number(NUMBER(16)) = NUMBER(20)
   ... ...
     PRIMARY KEY ("COL003")
   ) ;
   
   CREATE INDEX "EINP_BASICINFO"."IDX_TT01_COL001" on "EINP_BASICINFO"."TT01" (
    "COL001"
   ) GLOBAL ;
   
   /* テーブル TBL3の定義*/
   CREATE TABLE "LOCAL_BDZGBGYY"."TBL3" (
     "COL170" VARCHAR2(20) NOT NULL,      // 11: "tbl3"."col170" = "tt01"."col001" = 20017476, VARCHAR2(20) を NUMBER (20) に変更
     ... ...                              // 14: "zb10"."col170" = "tbl2"."col004" = 20017476, VARCHAR2(20) を NUMBER (20) に変更
     PRIMARY KEY ("COL002")
   ) ;
   
   CREATE INDEX "LOCAL_BDZGBGYY"."IDX_TBL3_COL170" on "LOCAL_BDZGBGYY"."TBL3" (
    "COL170"
   ) GLOBAL ;
   
   /* テーブル TBL2の定義 */
   CREATE TABLE "SICP4_BASICINFOCENTER_EINP"."TBL2" (
     "COL004" NUMBER(20)  NOT NULL,      //  13: COL004 = 20017476
     ... ....
     PRIMARY KEY ("COL004")
   ) ;
   

最適化の考え方

本ケースにおけるテーブルとビューのDDL最適化案は以下の通りです：

• ビュー内の TO_NUMBER 型変換を削除し、オプティマイザーがより優れたJOIN順序を選択したことで、SQL実行時間が2秒から150ミリ秒に短縮されました（ビューの入力パラメータは NUMBER 型であり、ビュー内の COL001 も元のテーブルでは NUMBER 型であるため、ビュー内の TO_NUMBER 強制変換は不要でした）。
• テーブルとビュー内の関連フィールドのデータ型を NUMBER(20) に統一し、型変換を回避することで、TBL3(IDX_TBL3_COL170) でのインデックスマッチングが可能になり、SQL実行時間はさらに20ミリ秒まで短縮されました（TBL3 Colbert170 は VARCHAR 型データ型であり、ビュー上のクエリ条件 COL001 IN ( value,value,...) が TBL3 Colbert170 に渡される際に VARCHAR から NUMBER の型変換が発生し、TBL3 Colbert170 上のインデックスでのマッチングができなかったためです）。
• ビュー内の4つの関連テーブルに対してパーティションとテーブルグループの改革を行いました。このビューは業務のエントリクエリビューであり、以前このビューを使用するSQLが原因で、少数のOBServerノードの計算リソースが使い果たされる問題が発生していました。このホットスポットビューが再びボトルネックになるのを避けるため、パーティション改革によりビューのクエリ負荷を各OBServerノードに分散させました。具体的には、ビュー内の4つの関連テーブルを関連キーに基づいてHashパーティションに分割し、テーブルグループを追加することで、ノード間の関連クエリを回避しました。改革後、SQL実行時間はさらに4ミリ秒まで短縮されました。

最適化後のSQLのExplain情報は以下の通りです。PX PARTITION ITERATOR は UNION ALL がパーティション内でのみ実行されることを示しており、この場合 TBL3 上のインデックスのコストは非常に低く、性能が大幅に向上しています。

====================================================================
|ID|OPERATOR                |NAME                   |EST. ROWS|COST|
--------------------------------------------------------------------
|0 |PX COORDINATOR          |                       |3        |477 |
|1 | EXCHANGE OUT DISTR     |:EX10000               |3        |405 |
|2 |  SUBPLAN SCAN          |V_TT01                 |3        |405 |
|3 |   PX PARTITION ITERATOR|                       |3        |405 |
|4 |    UNION ALL           |                       |3        |405 |
|5 |     NESTED-LOOP JOIN   |                       |2        |273 |
|6 |      MERGE ANTI JOIN   |                       |2        |183 |
|7 |       TABLE SCAN       |TT01(INDX_TT01_COL001) |2        |137 |
|8 |       SUBPLAN SCAN     |VIEW1                  |1        |46  |
|9 |        TABLE SCAN      |TBL3(INDEX_TBL3_COL170)|1        |46  |
|10|      TABLE SCAN        |TBL1(INDEX_TBL1_COL001)|1        |44  |
|11|     NESTED-LOOP JOIN   |                       |1        |132 |
|12|      TABLE SCAN        |TBL3(INDEX_TBL3_COL170)|1        |92  |
|13|      TABLE GET         |TBL2                   |1        |40  |
====================================================================