2021.12.09

SQLチューニング（第20回）「FUNCTION実行とSQL性能問題の理解」(1/3)

今回から、三回に渡り「FUNCTION実行とSQL性能問題の理解」に関する情報をお届けします。

今回は、「FUNCTIONの基本内容を理解すること」と題してお送りします。

第二回目は、「FUNCTION動作方式を理解すること」、第三回目は、「サブクエリを活用したSQL性能改善」と題して情報をお伝えする予定です。ご期待ください。

それでは、早速始めましょう。

６.１　FUNCTIONの基本内容を理解すること

６.１.１　USER DEFINED FUNCTIONの意味

　Oracleは頻繁に使用されるTO_CHAR,NVL関数のような組み込み関数を提供してSQL作成時の便利さを支援する。そして使用者は必要に応じてUser Defined Function (以下Function)を作成し使用できる。 Oracleが提供する関数はあらかじめ生成されており修正は不可ですがが、Functionは使用目的に合うように直接作成するのが可能です。多くのFunctionはスカラーサブクエリで実装可能ですが、Functionを使う理由はスカラーサブクエリに比べて使う時得ることができる長所のためです。

　この章ではFunctionの特徴と長所、種類と作成法に対して調べてみた後、Functionの実行方式により発生する性能問題とその改善方法に対して調べてみます。

６.１.２　USER DEFINED FUNCTIONの特徴と長所

特徴

リターン値がある。
データベースオブジェクトとして保存されて、コンパイルされた状態で実行される。
単独的な使用より、SQLでたくさん実行される。
例外処理が可能である。

長所

モジュール化されたプログラムが可能である。
変数および多様な制御文使用が可能で、複雑なビジネスロジックも簡単に実現が可能である。
WASサーバーとネットワーク負荷を減らすことができる。
メンテナンスの側面で非常に効率的である。

６.１.２.１　WASサーバーとネットワーク負荷を減らすことができる

　DBサーバーで抽出されたデータをWASサーバーに持ってきて加工(演算など)した後、クライアントPCに結果を出力するプログラムがあるとする。このようなプログラムのうちDBサーバーで多くのデータが抽出されるが、クライアントPCに出力されるデータが少ない場合のプログラムは大きく二種類の性能問題を発生させる可能性があります。

　一番目に、DBサーバーで抽出された多くのデータをWASサーバーの物理メモリーに保存した後加工しなければならないので、この時WASサーバーの物理メモリーとCPU使用率が増加することになります。

　二番目に、DBサーバーで抽出された多くのデータをWASサーバーが伝送しなければならないので、データ伝送量と伝送回数が増え、プログラムの性能低下とネットワークトラフィック増加の原因となる恐れがあります。

　上記のようなプログラムにFunctionを使用することで、上記の二つの問題は最小化することができるはずです。 DBサーバーで抽出されたデータを直接加工した後、加工されたデータだけWASサーバーで伝送するようにFunctionを作成し、使用すれば良いのです。

　しかし、上記プログラムが頻繁に実行される場合、Functionの実行回数が過度に増加しDBサーバーのCPU使用率を高める非効率を発生させることがあるので、状況によって適切な方法を選択しなければならないでしょう。

６.１.２.２　メンテナンスの側面で非常に効率的である

　共通業務で使用するSQLを変更するべきだとすれば、共通SQLを使うすべてのプログラムソースを探して修正しなければなりません。しかし、共通で使うSQLであるだけにとても広範囲に使われており、変更できないプログラムがあって問題が発生する可能性があります。この時、発生する最も大きい問題は間違って抽出されたデータでトランザクションまで処理してデータ完全性を傷つけることがあるということです。

　例えば、一般使用者が製品を購入する時消費税が別に賦課されるならば、購買時製品価格に消費税を加えた金額を表示しなければなりません。この計算式をSQLに直接記述したとすれば、消費税が10%から11%に引き上げられる場合、計算式を使ったすべてのSQLを探して修正しなければなりません。しかしSQLでなくFunctionを使ったとすれば、Functionソースだけ修正して再コンパイルすれば、修正内訳がすべてのSQLに反映されるのでメンテナンスの側面で非常に効率的です。

６.１.３　USER DEFINED FUNCTIONの種類と使用法

６.１.３.１　NOT DETERMINISTIC FUNCTION

　Not Deterministic Functionは特別なオプションを与えない場合Defaultで生成されるFunctionで、入力値を受けて結果値を返す基本的なFunctionです。 SQLのSelect節に使う場合、最大SQLの抽出データだけにFunctionを反復実行するので、抽出データが多い配置プログラムに使われる場合、性能に悪影響を与えることがあるのでよく考えて使用しなければなりません。

　それではFunction作成方法に対して調べてみます。

CREATE [OR REPLACE] FUNCTION [関数名]
    ( [ param1  [mode] data_type1]
      [,param2  [mode] data_type2]
      [,param3  [mode] data_type3]
      ......
    )
      return data_type;--->大きさ指定は不可
    IS 
     変数宣言---> FUNCTION内で使う変数を宣言する。
     BEGIN
        実際のPL/SQL Program
        RETURN (変数);
    EXCEPTION
        ...........
    END;

・CREATE[OR REPLACE] FUNCTION:Functionを生成および再生成する時使う
・関数名:データベース内に保存されるFunction名前
・param:Function内で使用するパラメータ変数名
・mode:媒介変数の役割を意味しIN,OUT,IN OUT三つのうち選択
・data_type:param変数のデータ タイプ
・exception:特定条件に対する例外処理の部分
・begin ～ end:Functionの開始～終了を意味

PL/SQLは本一冊でも足りない程膨大な内容です。ここで記述した内容はFunctionの簡単な文法を説明するためであり、PL/SQLを扱う主題ではないので詳しい内容は省略します。関心がある方々はPL/SQLを扱った別途の本を参照するようにしてください。

　それでは、例題を通じて実際にFunctionを生成し、どのように使うのか確認してみます。

例)部署番号を入力を受けて部署名をリターンするFUNCTION生成

CREATE OR REPLACE FUNCTION dept_nm(pdeptno IN NUMBER)
RETURN VARCHAR2 IS --> RETURN値に対して何のオプションが与えられない。
pdeptnm VARCHAR2(32);
BEGIN
SELECT dname INTO pdeptnm
FROM dept
WHERE deptno = pdeptno;
RETURN pdeptnm;
EXCEPTION WHEN NO_DATA_FOUND THEN pdeptnm :='' ;
  RETURN pdeptnm ;
END dept_nm;
/

Functionを生成した後に下記のようにSelect節に使用できます。

SQL> SELECT empno,
ename,
deptno,
dept_nm(deptno) dname
FROM emp ;

EMPNO ENAME      DEPTNO DNAME                                   
----- ---------- ------ ----------------------------------------
 7499 ALLEN          30 SALES                                   
 7521 WARD           30 SALES                                   
 7566 JONES          20 RESEARCH                                
 7654 MARTIN         30 SALES                                   
 7698 BLAKE          30 SALES                                   
 7782 CLARK          10 ACCOUNTING                              
 7788 SCOTT          20 RESEARCH                                
 7839 KING           10 ACCOUNTING                              
 7844 TURNER         30 SALES                                   
 7876 ADAMS          20 RESEARCH                                
 7900 JAMES          30 SALES                                   
 7902 FORD           20 RESEARCH                                
 7934 MILLER         10 ACCOUNTING

　DEPTテーブルの部署名はDEPT_NM Functionを実行して抽出されたことが分かります。ここでFunctionは抽出データ件数ぶん、合計13回実行されます。

６.１.３.２　 DETERMINISTIC FUNCTION

　Deterministic Functionは入力値が同じならば、リターン値も常に同様を保障するFunctionです。 Not Deterministic Functionは抽出データだけに反復実行しなければなりません。もし、抽出データが100万件である配置プログラムにNot Deterministic Functionを使用する場合、100万回実行されなければなりません。

　Functionは1回実行ごとにRecursive Callで1回のパーシングが発生するため、結局、パーシングも合計100万回行う必要があるため、ソフトパーシングの負荷が大きく、繰り返しのブロックアクセスでHot Blockによる非効率が発生することもあります。

　しかし、Deterministic Functionを使用すると、入力値に対応する列値の種類（NUM_ DISTINCT値）が少ない場合、自然に入力値が等しい場合が多く、Functionを毎回実行せずにメモリ内にCacheされた結果値を直接取得してパフォーマンスは大幅に改善されます。そしてDeterministic Functionを使用するとき、入力値のキャッシュはスカラーサブクエリとは異なり、SQLレベルではなくFetchレベルで行われます。したがって、Cache効果を最大化するには、FETCH ARRAY SIZEを適切に設定する必要があります。

　それでは、Deterministic Functionを生成して使用してみましょう。

例)部署番号を部署名でリターンするDETERMINISTIC FUNCTION生成

CREATE OR REPLACE FUNCTION d_dept_nm(pdeptno IN NUMBER)
RETURN VARCHAR2 DETERMINISTIC 
IS
pdeptnm VARCHAR2(32);
BEGIN
SELECT dname INTO pdeptnm
FROM   dept
WHERE  deptno = pdeptno;
RETURN pdeptnm;
EXCEPTION WHEN no_data_found THEN
	pdeptnm:='';
  RETURN pdeptnm ;
END d_dept_nm;
/

　Deterministic Functionは、以前にNot Deterministic Functionを作成するのと同じであり、RETURN句宣言にDETERMINISTICオプションのみが追加されます。

　Deterministic Functionを生成した後下記のようにSelect節で使うことができます。

SQL> SELECT empno, 
ename,
deptno,
d_dept_nm(deptno) dname
FROM emp ;

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- -------------------------------------------
      7499 ALLEN              30 SALES
      7521 WARD               30 SALES
      7566 JONES              20 RESEARCH
      7654 MARTIN             30 SALES
      7698 BLAKE              30 SALES
      7782 CLARK              10 ACCOUNTING
      7788 SCOTT              20 RESEARCH
      7839 KING               10 ACCOUNTING
      7844 TURNER             30 SALES
      7876 ADAMS              20 RESEARCH
      7900 JAMES              30 SALES
      7902 FORD               20 RESEARCH
      7934 MILLER             10 ACCOUNTING

　上記SQLで使用されたFunctionは入力値に対してCacheされた値を利用するため、Functionの実行回数が13回ではなく入力値の種類(10,20,30)だけ合計3回実行されます。

６.１.３.３　PIPELINE TABLE FUNCTION

Pipeline Table Functionは、Multi Column + Multi Row型を入力値として受け入れ、値を返すことができるFunctionで、9i以前に使用していたTable Functionとは異なり、全体範囲処理せず、部分範囲処理が可能です。

・PIPELINE TABLE FUNCTION生成方法

Pipeline Table Functionは次のような手続きで生成します。

データを入れるUser Object Typeを作る。
データを入れるために1で作ったTypeを利用してテーブル方式(2次元配列方式)のTypeを生成する。
Pipeline Table Functionを生成する。

まずデータを入れるTypeを生成します。

CREATE TYPE table_type_row AS OBJECT (
SEQ NUMBER,
DTL VARCHAR2(50));
/

　NUMBERタイプのSEQとVARCHAR2(50)タイプのDTLカラムを持つTABLE_TYPE_ROW名でTypeを生成しました。

CREATE TYPE table_type_tab IS TABLE OF table_type_row ;

　前で生成したTABLE_TYPE_ROWを利用して2次元配列方式のTypeを生成します。これでデータを入れるTypeの宣言は完了となりました。

　Pipeline Table Functionを作成するときは、通常のFunctionを作成するのと同じですが、RETURN句でPIPELINEDオプションを宣言する必要があります。そして、データを抽出するときは、PIPE ROW関数を使用してROW単位でデータを抽出するだけです。

例)値を入力を受ける数ぐらい繰り返して抽出するPIPELINE TABLE FUNCTION

  CREATE OR REPLACE FUNCTION pipe_table (end_num IN NUMBER) 
  RETURN table_type_tab pipelined AS
  BEGIN 
      FOR i IN 1 .. end_num LOOP 
         dbms_output.put_line ('count ==> ' || i);-- dbms_outputで出力
         pipe row(table_type_row (i、'count for '|| i));--結ぼうがマだ出力
      END LOOP;
  RETURN ; 
  END ;
  /

　以下は、Pipeline Table Functionの特徴を調べるために、入力値に10を書き込み、SEQカラム値に対して降順にソート処理したSQLです。

SELECT *
 FROM TABLE ( pipe_table (10) )
ORDER BY seq DESC;

SEQ         DTL
---------- --------------------------------------------------
        10 count for 10
         9 count for 9
         8 count for 8
         7 count for 7
         6 count for 6
         5 count for 5
         4 count for 4
         3 count for 3
         2 count for 2
         1 count for 1

10 rows selected.

count ==> 1
count ==> 2
count ==> 3
count ==> 4
count ==> 5
count ==> 6
count ==> 7
count ==> 8
count ==> 9
count ==> 10

　Functionを生成する構文を見ると、PIPE ROWの前にDBMS_OUTPUTを最初に実行するように書かれています。ところで、PIPE ROWで抽出されたデータが先に出力されました。なぜなら、Pipelineはロー単位で毎回出力しますが、DBMS_OUTPUTパッケージを使用した出力は、Functionがすべて処理された後にできるからです。

　それでは、Pipeline Table Functionが各件ごとに抽出され、部分範囲処理が可能だと言っていましたが、本当にそうなのか、次のSQLで調べてみます。

SELECT *
  FROM TABLE ( pipe_table (10) )
 WHERE ROWNUM <= 5;

SEQ DTL
---------- -------------
         1 count for 1
         2 count for 2
         3 count for 3
         4 count for 4
         5 count for 5

count ==> 1
count ==> 2
count ==> 3
count ==> 4
count ==> 5

　全体範囲処理になったと仮定すると、PIPE ROWは10までの値を取り込んだ後、ROWNUM条件によって5までのデータのみを出力したはずです。しかし、Functionがすべて実行された後に一括処理するDBMS_OUTPUTを見ると、5までのデータだけが抽出されました。つまり、Functionは1から10までループを実行するようになっていますがが、Where句にROWNUM条件がある場合、条件に満足するデータだけ抽出して実行が停止することになるのです。

　実行計画を見ればCOUNT STOPKEYオペレーションがあるので、部分範囲処理で実行したとのことがさらに明確になります。

---------------------------------------------------------------------------------
| Id  | Operation                                       | Name  | Rows  | Bytes |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                |       |     5 |    10 |
|*  1 |  COUNT STOPKEY                                  |       |       |       |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| PIPE_TABLE |       |       |       |
---------------------------------------------------------------------------------

　これまで、Functionの種類、生成方法、特徴について簡単に調べました。 Functionの種類と特性をよく理解し、適時適所によく使用すればSQLのパフォーマンスを向上させることができます。

　　さて、先ほど知った Function の中で一般的に使う Not Deterministic Function についてさらに詳しく見てみましょう。

　ということで、今回のお話は、ここまでとなります。次回は、Not Deterministic Function についてさらに詳しく調べる「FUNCTION動作方式を理解すること」についてです。ご期待ください。では、seeyou^^

Back to index

SQLチューニング（第20回）「FUNCTION実行とSQL性能問題の理解」(1/3)

６.１ FUNCTIONの基本内容を理解すること

６.１.１ USER DEFINED FUNCTIONの意味

６.１.２ USER DEFINED FUNCTIONの特徴と長所

６.１.２.１ WASサーバーとネットワーク負荷を減らすことができる

６.１.２.２ メンテナンスの側面で非常に効率的である

６.１.３ USER DEFINED FUNCTIONの種類と使用法

６.１.３.１ NOT DETERMINISTIC FUNCTION

６.１.３.２ DETERMINISTIC FUNCTION

６.１.３.３ PIPELINE TABLE FUNCTION

SQLチューニング 2nd Season（第24回）第4章 SQL TuningとHINTの関係 （7/8）

SQLチューニング 2nd Season（第23回）第4章 SQL TuningとHINTの関係 （6/8）

SQLチューニング 2nd Season（第22回） 第4章 SQL TuningとHINTの関係 （5/8）