実行計画の述語部分のINTERNAL_FUNCTIONとは一体何か？


http://blog.tanelpoder.com/2013/01/16/what-the-heck-is-the-internal_function-in-execution-plan-predicate-section/の翻訳です。

本文書に関する指摘については、原文著者のTanel Poderさんではなく、渡部まで御連絡お願いいたします。

注意事項


  • 自動翻訳に手を加えたレベルの翻訳と理解してください
  • 意味をとりやすくするために追加した箇所については、《…》で囲っています。
  • 訳がわからない、または、自信がない箇所は★をつけています。


日本語訳：渡部 亮太 (WR at Csus4 dot net)

履歴


  • 2013-01-27: 初稿up

ときどき、実行計画の中にこのようなものをみるだろう：

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
\--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    22 |     2   (0)| 00:00:01 |
\-------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
\---------------------------------------------------
 
   1 - filter("B"=INTERNAL_FUNCTION("A"))

このINTERNAL_FUNCTIONが本当に何かということ、なぜそれが表示されるのかということについて、利用可能な非常に情報がほとんどありません、 このため、このブログのエントリがあります。

OracleとこれでINTERNAL_FUNCTION と呼ばれる関数は実際にありません。V$SQLFN_METADATAビュー照会することによっても（部分的には）確認されています

SQL> @sqlfn %internal%
 
no rows selected

Oracleのマニュアルからくる一般的な理解は、 INTERNAL_FUNCTIONとは（暗黙的な）データ型の変換を行う一種の特殊な機能であるということです。これは、部分的には正しいですが、真実の全てではありません – しかしながら、最初ではデータ型変換について調べて、後で説明や、他の例に進みましょう。

暗黙的なデータ型変換

私は列AにVARCHAR2データ型の日付を、列BにDATEデータ型の日付を格納する表を作成しています：

SQL> CREATE TABLE t(a VARCHAR2(20), b DATE);
 
Table created.
 
SQL> @desc t
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      A                                        VARCHAR2(20)
    2      B                                        DATE
 
SQL> INSERT INTO t VALUES( TO_CHAR(sysdate), sysdate) ;
 
1 row created.

それでは、簡単な選択クエリを実行し、その実行計画を見てみましょう：

SQL> SELECT * FROM t WHERE a = b;
 
A                    B
\-------------------- -----------------
20130116 17:41:49    20130116 17:41:49
 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
 
\--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
\--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    21 |     2   (0)| 00:00:01 |
\--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
\---------------------------------------------------
   1 - filter("B"=INTERNAL_FUNCTION("A"))

ここで起こっていることは、Oracleが《物理的に》2つの異なるデータ型を比較できるようにするために、列Aに対してデータ型変換関数を（暗黙的に）追加しているということです。内部的には、Oracleは"WHERE a = b"という比較を実行しているわけではなく、何か"WHERE TO_DATE(a) = b" のようなことをしています。これはINTERNAL_FUNCTIONが現れた理由の一つです – 実際の "バイナリ"実行計画から人間が読める形式の実行プランを生成するコードは、内部のopcodeを《対応する》人間が読める関数名に変換することはできません。したがって、代わりにそこに デフォルト扱いの"INTERNAL_FUNCTION"という文字列を表示します。

これはOracle Database 10g以降動作です。9i以前の場合、Oracleはちょうどその関数名としては何も表示しませんでした。9i以前の出力例は以下のとおりです。

1 - filter(("DUAL"."DUMMY")=:TEST_VAR)

上記の "DUAL"."DUMMY"の周りに一見不要な括弧があることがわかりますか。 "DUAL.DUMMY"=:TEST_VAR ではなくて、("DUAL"."DUMMY"):=TEST_VARとなっているのはtypoでしょうか？これは、《実際には》タイプミスではありません。Oracle 10gより前では、このような "不必要な"カッコは、実際にはなんらかのファンクションが呼び出されていることを示しているとみなすように注意しなければなりませんでした。 F("DUAL"."DUMMY") があって、"F"が印刷されなかったかのように。
Oracle 10g以降では、そのような場合には何も表示されないのではなく、genericな"INTERNAL_FUNCTION"が表示される。のではなく、印刷されて-少なくとも我々は列/変数に適用されるいくつかの機能があると知っている。あなたは、それが論理的にどのような関数になりうるか（DATAデータ型とTIMESTAMPデータ型を比較したとき、TIMESTAMPデータ型をDATAデータ型にキャストする関数など）を把握するため、コード（および場合によってはセッションレベルNLS_設定）を詳細に見てゆく必要があります。

Un-unparsebleな複雑な式

OK、データ型変換の理由はすでにOracle docsとblogoshereでカバーされています 。しかし、それだけではありません。

OR句を使用するこの例に注目してほしい：

SQL> CREATE TABLE t AS SELECT * FROM dba_objects;
 
Table created.
 
SQL> SELECT COUNT(\*) FROM t WHERE owner = 'SYS' OR owner = 'SYSTEM';
 
  COUNT(\*)
\----------
     32272
 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
 
\---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
\---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   293 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 36652 |   608K|   293   (1)| 00:00:04 |
\---------------------------------------------------------------------------
 
   2 - filter(("OWNER"='SYS' OR "OWNER"='SYSTEM'))

これまでのところはまったく問題ない – ライブラリ?キャッシュから直接計画を読み取るDBMS_XPLAN.DISPLAY_CURSORファンクションが、正しくpredicateを説明することができる。

では、述語をもう少し複雑にしてみましょう、私はこのpredicateに別のORを追加しますが、それは別の列に対するものになります。

SQL> SELECT COUNT(\*) FROM t WHERE owner = 'SYS' OR owner = 'SYSTEM' OR object_id = 123;
...
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
 
\---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
\---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   293 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 36652 |  1073K|   293   (1)| 00:00:04 |
\---------------------------------------------------------------------------
 
   2 - filter((INTERNAL_FUNCTION("OWNER") OR "OBJECT_ID"=123))

さて、突然OWNERテーブル上の2つの条件が消え去り、INTERNAL_FUNCTIONに置き換えられれたのだろうか？

のではなく、OR演算子の代わりににIN演算子を試してみよう。ただ待ってほしい。
私たちは2つの異なる列の値をチェックしている。（従って私達はIN句同じにそれらすべてを置くことができない）

SQL> SELECT COUNT(\*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT') AND object_type = 'TABLE';
 
  COUNT(\*)
\----------
      1178
 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
 
\---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
\---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   293 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    28 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   374 | 10472 |   293   (1)| 00:00:04 |
\---------------------------------------------------------------------------
 
   2 - filter((INTERNAL_FUNCTION("OWNER") AND "OBJECT_TYPE"='TABLE'))

まだ、いまいちですね。
ちょうど同じ列で3つの値を検索する、論理的に簡単なoperationを試してみます：

SQL> SELECT COUNT(\*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT');
 
  COUNT(\*)
\----------
     32278
 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
 
\---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
\---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   293 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 31960 |   530K|   293   (1)| 00:00:04 |
\---------------------------------------------------------------------------
 
   2 - filter(("OWNER"='SCOTT' OR "OWNER"='SYS' OR "OWNER"='SYSTEM'))

動きました！OracleはこのIN述部を（同じ列に対する）一連のOR条件に変換しました。
（もしくは、explanationではすくなくともそう示しています。）

すでにこれまでの例で、あなたは何が起こっているかがわかったかもしれない – DBMS_XPLAN.DISPLAY_CURSORは、実行計画の単一のステップで適用される "複雑で"複合的な述語を説明することはできないのです。
すなわち、複数の異なる列が含まれており、かつ、列のうち少なくとも1つを複数の値とチェックするような述語（インリストまたはOR結合述部のような）

DISPLAY_CURSORがどこからデータを取得するか。《explanationの位置づけが不明》

DBMS_XPLAN.DISPLAY_CURSORは、V$SQL_PLANからの実行計画のデータを取得する。述語部分はACCESS_PREDICATESとFILTER_PREDICATES列に対応する。しかし、直接V$SQL_PLANを照会する場合、まだ同じ問題に出くわすことになる：

SQL> SELECT id, filter_predicates FROM v$sql_plan WHERE sql_id = 'gcqgrmtna9g1u';
 
        ID FILTER_PREDICATES
\---------- ------------------------------------------------------------
         0
         1
         2 (INTERNAL_FUNCTION("OWNER") AND "OBJECT_TYPE"='TABLE')

そして、上記のraw《V$SQL_PLANの情報をさすと思われる》 OR条件の周りにも、9iにおいて、報告されたpredicateの周りに"バイナリ"実行計画では "unexplained"な内部関数が存在することを意味する ( ) カッコがあることに、あなたは、気づいたかもしれません。
しかし、（10g以降で internal_functionという命名をサポートしている）この状況では、空白の関数名は発生しないはず… なぜ《 ( )カッコが》そこにあるかについては本当にわからないが、このポストに関していえば、低レベルすきる詳細でしょう。

V$SQL_PLANビューは、（適切に/ピン/ミューテックスをラッチを獲得した後で）ライブラリ?キャッシュ内の実際の"バイナリ"子カーソルにアクセスし、それをUNPARSESしているのです。なぜ、このような用語、"バイナリ"形式《があるのか》
人間が読める入力を受け取り、コンピュータ《が理解できる形式》に変換するのではなくて。★したがってunparsingは反対です《反対のことをします》 – V$SQL_PLANは、カーソルの "バイナリ"実行計画のメモリ構造にアクセスして、人間が読める形式の実行計画の出力に変換します。このV$SQL_PLANの動作を制御するパラメータがあります。falseに設定されている場合、ACCESS_PREDICATESとFILTER_PREDICATES列が空になります：

SQL> @pd unparse
Show all parameters and session values from x$ksppi/x$ksppcv...
 
NAME                             VALUE                                      DESCRIPTION
\----------------------------- --------- -----------------------------------------------
_cursor_plan_unparse_enabled      TRUE          enables/disables using unparse to build
                                                                  projection/predicates

ところで、私はなぜ二重引用符で囲んだ "バイナリ"実行計画と言い続けるのでしょうか？それは、Oracleが実行する実際の実行計画は、画面上に表示されるようなテキスト形式ではないことを強調したいからです。テキスト《形式の実行計画》は、人間だけのために、トラブルの理由で生成されます。実行計画は、実行するためにCPUに供給される、（ORACLE.EXEのような）本当の実行可能バイナリではありません。ライブラリ?キャッシュ 子カーソル内の物理的な実行計画は、RowSourceの実行順序と階層を定義するための、一連のopecodeとobject_idsとポインタで《構成されま》す。SQL実行エンジンが、opecodeを繰り返したどり、それらをデコードし、次に何をすべきか（呼び出すべきrow source関数）を知るのです。

だから、我々が上でみたように、連なる複雑なAND / OR条件を持ついくつかの述語は、DBMS_XPLAN.DISPLAY_CURSORとV$SQL_PLANによってINTERNAL_FUNCTION（）として表示されます。それらは、実行計画を完全にデコード(unparse)することができないので。

古き良き時代のEXPLAIN PLANを使用する

しかし、いくつかの良いニュースがあります！古き良きEXPLAIN PLANコマンドは、これらの複雑な述語（の一部）を適切にunparseできます！EXPLAIN PLANのoperationが、与えられたSQLを、特殊なmore instrumentedな方法で、再び解析するため、それは明らかにより多くの情報を持ちます。（ そしてそれはあまりにも多くのメモリを使用します ）。Or it could just be that whoever wrote V$SQL_PLAN, didn’t write the piece of code for unparsing more complex predicates :) ★

SQLを実行する代わりに、EXPLAIN PLANコマンドを使用します。この出力をチェックしてください。

SQL> EXPLAIN PLAN FOR 
     SELECT COUNT(\*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT') AND object_type = 'TABLE';
 
Explained.
 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
\---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
\---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    28 |   293   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |    28 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   374 | 10472 |   293   (1)| 00:00:04 |
\---------------------------------------------------------------------------
 
   2 - filter(("OWNER"='SCOTT' OR "OWNER"='SYS' OR "OWNER"='SYSTEM')
              AND "OBJECT_TYPE"='TABLE')

これは奇跡です！INTERNAL_FUNCTIONがなくなっていますし、すべての述語の値が正しく表示されます。EXPLAIN PLANコマンドはここでは非常に有用でした。

EXPLAIN PLANはあなたに嘘をつくことがあるため 、私は通常EXPLAIN PLANコマンドを使用しませんが、私はDISPLAY_CURSOR / V$SQL_PLAN / SQLモニターの出力でINTERNAL_FUNCTIONを見るたびに、私は同じクエリに対してEXPLAIN PLANコマンドを実行します。predicateが実際どのようであるかを迅速に調べられることを願って。

OK、まだ2:30だ – 寝ます！ :)

関連記事