LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@tlokweng

Wrong Resultバグに遭遇したのでDBMS_SQLDIAGで解決策を探る (Oracle)

Last updated at Posted at 2021-05-26

はじめに

先日再帰クエリでゴニョゴニョやってるときに「バグじゃないのこれ？」って感じの結果が出力されました。ま、再帰クエリのバグ（というかOracleのバグ）は結構多いのでバグとの遭遇自体にはさほどおどろきもないのですが、今回はせっかくなので(?) DBMS_SQLDIAGでSQL PATCHをつくったりヒントで解決してみようというお話です。

追記: よく考えたらOS書いてなかったですね。Linux上での現象なので、Windowsだとまた違った動きかもしれません。

正しい値を返さない再帰クエリ

対象となるクエリは以下です。簡単な再帰クエリですが、再帰に対して直積の外部結合しています。正しい結果は、nが偶数のときにのみ'Even'が表示されるはずですが、オラクルバージョン「11.2/12.1」と「12.2/18c/19c」では実行結果が異なります。

WITH nums (n, msg) AS (
         SELECT 1, '' FROM DUAL
         UNION ALL
         SELECT n + 1 ,even
         FROM   nums
                LEFT JOIN (SELECT 'Even' even FROM DUAL)
                           ON MOD (n + 1, 2) = 0
         WHERE  n < 6)
SELECT * FROM   nums;

まずは正しい結果です。12.1,11.2で実行すると偶数のときにのみ'Even'が表示されます。

# 11gR2 / 12cR1
         N MSG
---------- ----
         1
         2 Even
         3
         4 Even
         5
         6 Even

しかし同じクエリを12.2以降で実行すると、余計な行にも'Even'が現れます。

# 12cR2 / 18c / 19c
         N MSG
---------- ----
         1
         2 Even
         3 Even
         4 Even
         5 Even
         6 Even

う～ん、これ仕様じゃないですよね？(^^) 一応再帰クエリ内では再帰クエリへのRIGHT OUTER JOINは禁止となってますが、LEFT OUTER JOINは問題ないはずですし、なによりも11.2/12.1では意図した結果が帰ってきてるのでバグと見做しても差し支えないかと思います。

ちなみに、Oracle SQL Live (現在のバージョンは19.8)でも正しくない結果が帰ってくるので、まだパッチは出てないんじゃないかな。しらんけど。

DBMS_SQLDIAGでクエリを解析

では、ここからDBMS_SQLDIGAを利用して解決策をさぐってみたいと思います。流れとしては以下のとおりです。

  • DBMS_SQLDIAGやSQL PATCHの実行に必要な権限を付与する
  • 診断タスクを作る(create_diagnosis_task)
  • 診断タスクを実行する(execute_diagnosis_task)
  • 診断結果を参照する(report_diagnosis_task)
  • SQL PATCHを作成する(accept_sql_patch)
  • SQL PATCHの内容を確認する(sys.sqlobj$data)
  • SQL PATCHから得られたヒントをつけて実行してみる

権限の付与

DBMS_SQLDIAGを使うには、ADVISOR権限が必要です。さらにSQL PATCHの作成、削除にはCREATE ANY SQL PROFILE、DROP ANY SQL PROFILEが必要なので、ユーザに付与しておきます。

SQL> GRANT ADVISOR, CREATE ANY SQL PROFILE, DROP ANY SQL PROFILE TO testuser;

SQL IDの取得

次に診断タスクを作成するためにクエリを実行してSQL_IDを取得します。SQL_IDがなくてもSQLテキストで直接タスクをつくることもできますが、SQL_IDがあったほうが後々楽なので。

SQL> SET SERVEROUT OFF
SQL> WITH nums (n, msg) AS (SELECT 1, '' FROM DUAL UNION ALL SELECT n + 1 ,even FROM nums LEFT JOIN (SELECT 'Even' even FROM DUAL) ON MOD (n + 1, 2) = 0 WHERE n < 6) SELECT * FROM nums;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
SQL_ID  3hg5v4avacn33, child number 0
-------------------------------------
 WITH nums (n, msg) AS (SELECT 1, '' FROM DUAL UNION ALL SELECT n + 1
,even FROM nums LEFT JOIN (SELECT 'Even' even FROM DUAL) ON MOD (n + 1,
2) = 0 WHERE n < 6) SELECT * FROM nums

(省略)

診断タスクを作成して実行

DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASKで診断タスクを作成します。診断の種類には、以下の５つがありますが、今回はクエリ結果異常なので、PROBLEM_TYPE_WRONG_RESULTSを使用します。

診断種類 診断対象
PROBLEM_TYPE_PERFORMANCE パフォーマンス問題
PROBLEM_TYPE_WRONG_RESULTS クエリ結果の異常
PROBLEM_TYPE_COMPILATION_ERROR コンパイル時のクラッシュ
PROBLEM_TYPE_EXECUTION_ERROR 実行時のクラッシュ
PROBLEM_TYPE_ALT_PLAN_GEN 代替実行計画の取得

SQL_IDパラメータ使用しています。クエリ文字列を使う場合はSQL_TEXTパラメータです。

SQL> VAR task_id VARCHAR2(50)

SQL> EXEC :task_id := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK (
                SQL_ID       => '3hg5v4avacn33',
                PROBLEM_TYPE => DBMS_SQLDIAG.PROBLEM_TYPE_WRONG_RESULTS,
                TASK_NAME    => 'recursive_wrong_result');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK (:task_id);

PL/SQL procedure successfully completed.

診断結果の取得とSQL PATCHの作成

診断が終了したら診断結果を取得します。以下の通り解決策が一つ見つかりました。

SQL> SET LINESIZE 200 PAGESIZE 0 LONG 200000

SQL> SELECT DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK (:task_id) FROM DUAL;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : recursive_wrong_result
Tuning Task Owner  : TESTUSER
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
(略)
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Patch Finding (see explain plans section below)
------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation
  --------------
  - Consider accepting the recommended SQL patch.
    execute dbms_sqldiag.accept_sql_patch(task_name =>
            'recursive_wrong_result', task_owner => 'TESTUSER', replace =>
            TRUE);
(略)

診断結果の解決策にそってDBMS_SQLDIAG.ACCEPT_SQL_PATCHを実行しSQL PATCHを作成しますが、上記には最低限のパラメータしか提示されていません。ここでは、SQL PATCH名、説明を追加し、さらにリテラル値が変更されてもSQL PATCHが働くようFORCE_MATCHを有効にします。

SQL> EXEC DBMS_SQLDIAG.ACCEPT_SQL_PATCH(
                TASK_NAME   => 'recursive_wrong_result',
                NAME        => 'recursive_bug_fix',
                DESCRIPTION => 'wrong result bug fix for recursive with outer join',
                TASK_OWNER  => 'TESTUSER',
                REPLACE     =>TRUE,
                FORCE_MATCH =>TRUE);

SQL PATCHの確認とクエリの実行

SQL PATCHが作成されていることを確認して、問題のクエリを実行します。

SQL> SELECT NAME, STATUS, FORCE_MATCHING FROM DBA_SQL_PATCHES where name = 'recursive_bug_fix';

NAME                 STATUS   FOR
-------------------- -------- ---
recursive_bug_fix    ENABLED  YES


SQL> WITH nums (n, msg) AS (SELECT 1, '' FROM DUAL UNION ALL SELECT n + 1 ,even FROM nums LEFT JOIN (SELECT 'Even' even FROM DUAL) ON MOD (n + 1, 2) = 0 WHERE n < 6) SELECT * FROM nums;

         N MSG
---------- ----
         1
         2 Even
         3
         4 Even
         5
         6 Even

正しい結果を取得することができました！

さらにそのまま実行計画を表示して、NoteセクションでSQL PATCHが使用されていることを確認します。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID  3hg5v4avacn33, child number 1
-------------------------------------
(略)

Note
-----
   - SQL patch "recursive_bug_fix" used for this statement

ついでにもう一つ確認です。SQL PATCHのFORCE_MATCHを有効にしたので、リテラル値を変えて実行してみます。以下、'Even'を'Odd'に、MOD (n + 1, 2) = 0をMOD (n + 1, 2) = 1に、さらにn < 6をn < 7に変更しましたが、正しく動いていますね。

SQL> WITH nums (n, msg) AS (SELECT 1, 'Odd' FROM DUAL UNION ALL SELECT n + 1 ,even FROM nums LEFT JOIN (SELECT 'Odd' even FROM DUAL) ON MOD (n + 1, 2) = 1 WHERE n < 7) SELECT * FROM nums;

         N MSG
---------- ---
         1 Odd
         2
         3 Odd
         4
         5 Odd
         6
         7 Odd

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
(略)
Note
-----
   - SQL patch "recursive_bug_fix" used for this statement

SQL PATCHの内容確認

さて、ではSQL PATCHがどのようにバグを回避したのかみてみます。SQL PATCHは、SQL PROFILEの派生というか一種のようでSQL PROFILEと同じくOUTLINEヒントがsys.sqlobj$dataに保存されています。COMP_DATAコラムには、データがXMLで格納されているためxmltableを使いヒント部分を抽出します。また対象行を特定するためにシグネチャが必要ですが、これはv$sqlにあるのでSQL_IDで取得します。FORCE_MATCHを有効にしている場合はFORCE_MATCHING_SIGNATURE、無効の場合はEXACT_MATCHING_SIGNATUREとなりますが、面倒なので両方一度に対象にしてます。

SELECT outline_hint
FROM   sys.sqlobj$data,
       XMLTABLE('//hint'
                PASSING XMLTYPE(comp_data)
                COLUMNS outline_hint VARCHAR2(100) path 'text()')
WHERE  signature IN (SELECT n
                     FROM   v$sql
                     UNPIVOT (n FOR s IN (FORCE_MATCHING_SIGNATURE,
                                          EXACT_MATCHING_SIGNATURE))
                     WHERE sql_id = '3hg5v4avacn33');

OUTLINE_HINT
-------------------------------------------------------------
NO_QUERY_TRANSFORMATION

ということで、なんとSQL PATCHの内容は単なるNO_QUERY_TRANSFORMATIONヒントでした。(^^)

では、実際にNO_QUERY_TRANSFORMATIONヒントで正しい答えが得られるが試してみましょう。まずは、SQL PATCHを削除してバグ状態に戻します。

SQL> EXEC DBMS_SQLDIAG.DROP_SQL_PATCH(name => 'recursive_bug_fix');

PL/SQL procedure successfully completed.

ヒントなしとヒントありで結果を比べてみます。

SQL> WITH nums (n, msg) AS (SELECT 1, '' FROM DUAL UNION ALL SELECT n + 1 ,even FROM nums LEFT JOIN (SELECT 'Even' even FROM DUAL) ON MOD (n + 1, 2) = 0 WHERE n < 6)
SELECT * FROM nums;

         N MSG
---------- ----
         1
         2 Even
         3 Even
         4 Even
         5 Even
         6 Even


SQL> WITH nums (n, msg) AS (SELECT 1, '' FROM DUAL UNION ALL SELECT n + 1 ,even FROM nums LEFT JOIN (SELECT 'Even' even FROM DUAL) ON MOD (n + 1, 2) = 0 WHERE n < 6)
SELECT /*+ NO_QUERY_TRANSFORMATION */ * FROM nums;

         N MSG
---------- ----
         1
         2 Even
         3
         4 Even
         5
         6 Even

NO_QUERY_TRANSFORMATIONヒントで正しい答えが得られることが確認できました。とどのつまり、オプティマイザのクエリ置換におけるバグ、ということになろうかと思います。しかし12.2からだすると結構息の長いバグですね。単純なクエリなんだけどなー。そのうちfixが出ることを期待しましょう。

診断タクスの削除

最後に診断タクスを削除してクリーンナップしておきます。

SQL> EXEC DBMS_SQLDIAG.DROP_DIAGNOSIS_TASK (task_name => 'recursive_wrong_result');

PL/SQL procedure successfully completed.

おわりに

たまたまバグに遭遇してたまたまSQL診断で解決策が提示されたので記事にしてみました。まぁそうそう上手くいくとは思えないですけど、問題タイプもいくつか提示されている様ですし、なんか動きがおかしいときはとりあえず診断してみるのも悪くないのかもしれませんね。

以上です。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?