バインド変数が使用できないバッチ処理の共有プール肥大化対処方法

とあるバッチ処理がバインド変数を使用できずに共有プールが肥大化していた。
そのためstatspackを１時間毎に取得していたが、１時間では取得処理が終わらない状況になってしまった。

select pool, name, bytes/1024/1024 MB from v$sgastat  where pool = 'shared pool' order by 3;
shared pool  free memory                 3163.6143
shared pool  SQLA                       5343.10341
shared pool  KGLH0                      5459.67253

共有プール肥大化原因

対象のバッチ処理のクエリはwhere句部分がリテラルになっていたが、10000種類ほどのクエリのため
10000種類のカーソルの再利用がされると思っていたがそうではなかった。
以下のようなクエリでidが1～10000まで実行。

select uuid,id from uuid_master where uuid = 'test' and id=1;

共有SQL領域をチェック

select count(*) ,substr(SQL_TEXT,0,44) as text from v$sql  group by substr(SQL_TEXT,0,44) having count(*) > 30 order by 1;
count text
_____ ____________________________________________
91627 select uuid,id from uuid_master where uuid =

10000件しかないはずと思っていたのに90000件以上キャッシュされていた。

select sql_text from v$sql where sql_id='bbwtpvkn337s5' group by sql_text ;
sql_text
------------------------------------------------------------
select uuid,id from uuid_master where uuid = 'test' and id=1

select count(*) from v$sql where sql_id='bbwtpvkn337s5';
  COUNT(*)
----------
        6

上のようにリテラル値のクエリでも別バージョンが作成されている状況であった。

select ROLL_INVALID_MISMATCH from V$SQL_SHARED_CURSOR where sql_id='bbwtpvkn337s5';
R
-
Y  

V$SQL_SHARED_CURSORで特定の子カーソルが既存の子カーソルと共有されない理由を確認したところ、ROLL_INVALID_MISMATCHがYとなっていた。

ROLL_INVALID_MISMATCHとは・・・「ローリング無効化のマークが付けられ、無効化ウィンドウを超えた。」

ってことらしいがちょっと意味がわからない。。。

V$SQL_SHARED_CURSOR

select child_number,LAST_ACTIVE_TIME,LAST_LOAD_TIME,EXECUTIONS from v$sql where sql_id='bbwtpvkn337s5';

CHILD_NUMBER LAST_ACTIVE_TIME    LAST_LOAD_TIME           EXECUTIONS 
------------ ------------------- ------------------------ ---------- 
           0 2015/09/14 05:45:16 2015-09-13/06:27:54               7 
           1 2015/09/05 08:15:25 2015-09-04/05:29:36              10 
           2 2015/09/09 07:49:14 2015-09-08/06:39:29              10 
           3 2015/09/12 10:42:43 2015-09-11/06:34:53               9 
           4 2015/09/16 07:23:42 2015-09-15/06:17:19              10 
           5 2015/09/18 07:27:58 2015-09-17/07:07:28               9
           

select LAST_ANALYZED from dba_tables where TABLE_NAME in (upper('uuid_master'));
LAST_ANALYZED
-------------------
2015-09-16 22:03:45

LAST_LOAD_TIME（問合せプランがライブラリ・キャッシュにロードされた時刻）を確認すると新たにカーソルが作成された日時は対象のテーブルの統計情報が取得されたあとに実行されているようであった。
テーブルの統計情報が取得された場合でリテラル値のクエリでも別バージョンでカーソルが作成されるようである。

対処方法

対象のバッチ処理で使用しているライブラリはprepared statemantで記述してもバインド変数ではなくリテラルで実行されてしまう仕様であった。
そのため、セッション単位でcursor_sharingをFORCEにすることで対応。

alter session set cursor_sharing=FORCE;

バッチ処理実行時にcursor_sharingをFORCEにすることで対象のクエリをバインド変数化することで共有プールの肥大化を抑えることができた。

select SQL_TEXT,executions,LAST_LOAD_TIME from v$sql where SQL_TEXT like 'SELECT shop_id, mall_id, name, description,%';

SQL_TEXT                                                                           EXECUTIONS  LAST_LOAD_TIME
---------------------------------------------------------------------------------- ----------- ----------------------------------------------
select uuid,id from uuid_master where uuid = :"SYS_B_0" AND id = :"SYS_B_1"             668393 2015-09-19/07:00:44
select uuid,id from uuid_master where uuid = :"SYS_B_0" AND id = :"SYS_B_1"             625840 2015-09-21/05:59:25
select uuid,id from uuid_master where uuid = :"SYS_B_0" AND id = :"SYS_B_1"             780720 2015-09-23/07:10:00