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 ;



>>おすすめのORACLE参考書まとめ




★★★ブログ村ランキングに1票お願いします!もちろん無料です(笑)★★★
ブログランキング・にほんブログ村へ

【SQLでお困りの方にオススメ】
■SQLが遅くなる原因
・SQLが突然遅くなる原因と対応方法
・OracleSQLのレスポンスが遅い原因3つ
・JOINを使って表結合するとSQLが遅い!?
・SQLがパラレル実行されない原因

■SQLチューニングの基礎知識
・ORACLE SQLのヒントとは
・ORACLEのオプティマイザと実行計画について
・ORACLEのテーブルやインデックスの「統計情報」とは

■SQLチューニングの方法
・SLECT文が遅いときのインデックス作成によるチューニング方法
・Update文が遅いときのチューニング方法
・Delete文が遅いときのチューニング方法
・IN句をEXISTS句に置き換えするチューニング方法
・OR句の置き換えによるチューニング方法
・IN句の置き換えによるチューニング方法
・テーブルアクセスフルスキャンを無くしてチューニングする方法
・SQLでGROUP BYが遅いときのチューニング方法
・SQLのソート処理が遅い場合のチューニング方法

■表や索引の断片化対策
・プロが教えるORACLEの表や索引の断片化解消の見積り方法
・ORACLE表の断片化を解消する3つの方法

■SQLのチューニングの補足情報
・ORACLE SQLヒントの書き方
・SQLに複数のヒントの書き方
・OracleSQLに実装すべき3つのSQLヒント
・ORACLE表の統計情報取得日の確認方法
・「統計情報が古い」とはどういうことか
・ORACLE表の統計情報の取得方法

【自己学習したい方にオススメ】
・自宅で勉強用にORACLEデータベースを無償でダウンロードし構築する方法
・OracleMasterGold12cに一ヵ月で合格した勉強方法
・OracleMasterExpertパフォーマンス&チューニングに一ヵ月で合格した勉強方法
・手に職がない人はIT業界へ、オススメ資格と勉強方法
・OracleDBのチューニング勉強方法
・OraclePL/SQL勉強方法