OracleVPDのファンクションによるトラブル&対処法

 私が過去に携わったプロジェクトではVPD(バーチャルデータベース)を利用してデータアクセスの制御を行っていました。

 しかし、ある時、データベースのCPU使用率が急激に向上し、SQLの実行待ちが大量に発生しました。そのとき問題の原因となっていたのがVPD機能で利用するために作成していたファンクションに問題がありました。

 VPD機能では指定したファンクションがSQL実行時に呼び出されファンクションの戻り値が抽出条件に自動で追加されます。このファンクションが呼ばれるのはSQLの実行計画を作成する直前です。

 そのためファンクションで文字列が返るようにコーディングすると同じSQLであっても、実行計画が共有されずハードパースが頻発します。

1.ダメな例

CREATE OR REPLACE FUNCTION USER_ID_FUNC (
 para1 IN VARCHAR2
 ,para2 IN VARCHAR2
 ) RETURN VARCHAR2 IS predicate VARCHAR2(60);
BEGIN
 IF LENGTH(SYS_CONTEXT(‘USERENV’,’CLIENT_IDENTIFIER’)) < 10 THEN   --この記述では1つのSQLidに対し、複数の子カーソルが発生   predicate := 'user_id ='||SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER');  ELSE   predicate := '1=2';  END IF;  RETURN predicate; END ;


2.OKな例

 ハードパースを回避するために文字列ではなく関数の形で返却されるようにすると、劇的にハードパースが無くなりました。

CREATE OR REPLACE FUNCTION USER_ID_FUNC (
  para1 IN VARCHAR2
  ,para2 IN VARCHAR2)
 RETURN VARCHAR2 IS predicate VARCHAR2(60);
BEGIN
 IF LENGTH(SYS_CONTEXT(‘USERENV’,’CLIENT_IDENTIFIER’)) < 10 THEN   --関数呼び出しにすることで   --同一のSQLとして実行され複数の子カーソルの発生を抑制できた   predicate := 'user_id = SYS_CONTEXT(''USERENV'',''CLIENT_IDENTIFIER'')';  ELSE   predicate := '1=2';  END IF;  RETURN predicate; END ;