ある顧客のデータベースシステムでパフォーマンス障害が発生し、多数のトランザクションが滞留する現象が見られました。直接的な原因は、一部のOceanBaseデータベースサーバーノードのCPU利用率が100％まで急上昇し、トランザクションが正常に処理できなくなったことです。GV$OB_SQL_AUDITビューの実行監査情報を分析した結果、問題のあるSQL文の平均実行時間が2秒であり、そのCPU占有率がノード全体のCPUリソースの70％を超えていることが特定されました。実行計画(Explain)およびテーブル/ビューのDDL定義を組み合わせて分析した結果、最終的にDDLの最適化によりSQL文のCPU消費時間を4msに削減し、パフォーマンスボトルネックを完全に解消しました。
トラブルシューティングのアプローチ
本ケースのトラブルシューティングおよび最適化のアプローチは以下のとおりです：
GV$OB_SQL_AUDITビューを照会して、TOP SQLを特定します。指定された期間（30分）内に特定のOBserverノード（
svr_ip）で実行されたSQL（GROUP BY sql_id）の問題発生時の総CPU利用率（SUM(execute_time)/(30*60*1000*1000) AS cpu_cnt）などの情報を照会する次のSQLステートメントを実行し、CPU利用率が最も高いSQLをフィルタリングします：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実行計画（Explain）を分析して、パフォーマンスボトルネックを特定します。
EXPLAINコマンドを使用して、問題のあるSQLの実行計画を取得します：================================================================================= |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結果分析：
上記の
EXPALIN情報に基づき、この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重要な発見：
- 総コスト：UNION ALL 演算子（演算子3）のコストは、実行計画全体の92％（397,629 / 432,961）を占めます。
- インデックス無効化：TBL3 テーブルのインデックスIDX_TBL3_COL170 が有効になっていないため、テーブル全体のスキャン（TABLE SCAN）が行われます。
- 型変換の問題：COL170 フィールドはVARCHAR2であり、関連フィールドの数値型とマッチしないため、強制的な変換によりインデックスが無効になります。
関連するテーブルとビューの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定義と
EXPALIN情報を総合すると、このカテゴリの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") ) ;重要なテーブルフィールドタイプの比較：
テーブル/フィールド データ型 関連条件 問題の説明 TT01 Colbert001 NUMBER(20) tt01 Colbert001 = tbl3 Colbert170 ビュー内で強制的にTo_numberに変換されるため、型がマッチしない TBL3 Colbert170 VARCHAR2(20) tbl3 Colbert170 = tt01 Colbert001 文字列型は直接NUMBER関連フィールドとマッチできない TBL2 Colbert004 NUMBER(20) tbl2 Colbert004 = tbl3 Colbert170 TBL3 Colbert170と同じ型がマッチしない問題
最適化の考え方
本ケースでは、テーブルとビューのDDLに対する最適化案は以下の通りです：
不要な型変換を削除します。
- 問題：ビュー
V_TT01内のTO_NUMBER変換により、関連フィールドの型がマッチせず、インデックスが無効になります。 - 最適化：ネイティブフィールド型を直接使用し、
TO_NUMBERを削除します：
-- 最適化後のビュー定義（型変換を削除） CREATE VIEW "V_TT01_NEW" AS ( SELECT "tt01"."col001" AS "COL001", ... FROM "tt01", "tbl1" WHERE "tt01"."col001" = "tbl1"."col001" AND NOT EXISTS ( SELECT 1 FROM "tbl3" WHERE "tt01"."col001" = "tbl3"."col170" ) UNION ALL ( SELECT "tbl2"."col004" AS "COL001", ... FROM "tbl2", "tbl3" WHERE "tbl2"."col004" = "tbl3"."col170" ) );- 効果：SQL実行時間は2秒から150msに短縮され、オプティマイザーはより適切なJOIN順序を選択します。
- 問題：ビュー
フィールドデータ型を統一します。
- 問題：
TBL3 Colbert170はVARCHAR2(20)であり、関連フィールドCOL001とCOL004はNUMBERであるため、型変換とインデックスの無効化が発生します。 - 最適化：
-- TBL3フィールドタイプをNUMBER(20)に変更 ALTER TABLE "TBL3" MODIFY ("COL170" NUMBER(20)); -- インデックスの再作成 CREATE INDEX "IDX_TBL3_COL170" ON "TBL3" ("COL170");- 効果：テーブルとビュー内の関連フィールドのデータ型定義を
NUMBER(20)に統一し、型変換を回避することで、TBL3(IDX_TBL3_COL170)でインデックスマッチングが可能になり、型が統一された後にインデックスが有効になり、SQL実行時間はさらに20msに短縮されます。
- 問題：
パーティションとテーブルグループの変更。
- 問題：ホットビュー
V_TT01により、単一ノードのCPU負荷が高くなります。 - 最適化案：
- Hashパーティション：関連テーブルを
COL001/COL004/COL170フィールドに基づいてHashパーティションに分割します。 - テーブルグループバインディング：関連テーブルを同一のテーブルグループに追加し、ノード間の関連クエリを回避します。
- Hashパーティション：関連テーブルを
-- 例：TBL3にHashパーティションを実行 ALTER TABLE "TBL3" PARTITION BY HASH("COL170") PARTITIONS 8; -- テーブルをテーブルグループに追加 ALTER TABLE "TBL3" SET TABLEGROUP = 'group1';- 効果：クエリの負荷が複数のノードに分散され、最終的にSQL実行時間は4msに短縮されます。
- 問題：ホットビュー
最適化後の実行計画：
====================================================================
|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 |
====================================================================
主な改善点：
PX PARTITION ITERATORは、UNION ALLをパーティション内でのみ実行することを示します。- インデックス
TBL3.INDEX_TBL3_COL170が有効にマッチし、完全なテーブルスキャンを回避します。TBL3上のインデックスのコストは非常に低く、パフォーマンスが大幅に向上しています。
関連ドキュメント
本記事で説明されているビューについて詳しく知りたい場合は、該当するビューのドキュメントを参照してください。