Oracle共有プールの断片化解消のテスト方法

Question

開発者2年目の若輩者です。
テスト方法に関して、ご意見を頂きたく質問致します。

職場のDBサーバで共有プールの断片化が発生しているため、暫定的な処置として共有プールのフラッシュを行うことになりました。
夜間バッチの一部として、対処してほしいとのことです
そのため以下のようにして、断片化の解消をしようと思っています。

【hoge.bat】
@echo off
～省略～
@hoge.sql
～省略～
exit

【hoge.sql】
ALTER SYSTEM FLUSH SHARED_POOL;

そこでテストに関して質問なのですが、
① 何を目的としてテストを行えば良いか
② ①に関してのテスト実施方法はどうすれ良いか

ちなみに①に関しては以下を考えています。
(1)本当に共有プールの断片化が解消されているか
(2)他の夜間バッチに対する影響はないか
(3)キャッシュが削除されてしまうため、その後のアプリケーションからのアクセスにどの程度スピードの影響があるかです。
ただ、テスト実施方法がわからず困っています。

「①のここが足りていない」や「②の実施方法に関してはこうしたら良い」というご回答を頂きたいと思っています。
また、バッチやSQLに関しての厳しいご意見も承ります。

宜しくお願い致します。

【環境】
Windows Server
Oracle Database 11g

Answer 1

①の「何を目的としてテストを行えば良いか」については観点は問題ないと思います。
実施方法としては、ヒープダンプを取る、という方法があります。

ヒープダンプの取得

alter session set events 'immediate trace name heapdump level 2';

トレースファイルの場所

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

最新の.trcファイルを開き、HEAP DUMPで検索してください。そこからダンプの内容が始まります。
エクステント(EXTENT 0～?)の下にチャンク(Chunk)がぶら下がっていて、その横にステータスが書いてあると思います。チャンクの数が共有プールFLUSH後であれば少なくなっているのが分かると思います。

もしくは、単純にチャンクの数を数えるという方法でもよいかと思います。FLUSH後に少なくなっているのが分かると思います。

チャンクの数を数える

select count(*) from x$ksmsp;

共有プールFLUSH後の影響はOrlofskyさんの回答の通り、ハードパースがどれぐらいかかるかというところを確認すればよいと思います。どの程度の影響が出るかは、ACTIVE_SESSION_HISTORY（通称ASH）のin_hard_parse列がYのセッションを確認すればよいです。サンプルSQLを提示します。

ASHから影響確認

select
  substr(t2.sql_text,1,100) toplevelsqltext,
  t.SQL_FULLTEXT sqltext,
  h.*
from
  v$sql t,
  v$sql t2,
  v$active_session_history h
where
    t.sql_id = h.sql_id
  and h.top_level_sql_id = t2.sql_id
  and h.sample_time  between to_date('2017/11/21 10:00:00','YYYY/MM/DD Hh24:MI:SS') AND to_date('2017/11/21 15:59:59','YYYY/MM/DD Hh24:MI:SS')
  and h.in_hard_parse = 'Y'
order by
  sample_time
;

時間は、h.sample_time列で絞った方がいいです。また、h.module列にjavaのバッチであればJDBC等識字別できる文字が出力されるので、それで絞るとなおよいと思います。
1行につき1秒かかっているとみることができるので、sql_text単位で結果行数が多ければ多いほど影響があると捉えることができます。

Answer 2

可能であれば Oracleを一度shutdownしてstartupし直すのが手っ取り早いです。

SQLを実行すると、Oracleは今実行しようとするSQLが共有プールに同じSQLがあればそれを実行し、なければparse(コンパイル)して共有プールに保存し、実行します。

ALTER SYSTEM FLUSH SHARED_POOL;
を実行するとそれ以降実行されるSQLは共有プールに同じSQLがないのでparseし直さなければならないので遅くなります。どの程度の時間遅くなるかはSQLの多さなどによって違います。同じSQLが何度も実行するようなシステムなら遅くなる時間は短いです。

津島博士のパフォーマンス講座 第14回 メモリ・チューニングについて