Oracle10g キャッシュヒット率について

質問

Linuxサーバー（Red Hat Enterprise Linux 5.5）にOracle10g（10.2.0.4）をインストールしたのですが、パフォーマンスの問題で、バッファ・キャッシュ・ヒット率が９０％を下回っており、まずはこれを解決する必要があるとのことで、少し調べ始めたのですが、10gではsga_tａrgetで自動管理できるということを知りました。

最終目的は、パフォーマンス最適化を目指していますが、そのために確認しておくべき内容および設定方法を教えてください（あるいは回答にマッチしたサイトがあれば）。sga_tａrgetを攻略すれば達成できるのでしょうか？

当面の目的はバッファ・キャッシュ・ヒット率を上げることですが、どうすればよいでしょうか？

Oracleド素人ですので、コマンドベースで教えていただけると助かります（^^；

よろしくお願いします。

回答No.5

muyoshid

こんにちわ。

先ずは、Statspack Report を確認して見て下さい。
Top5 Timed Event だけでも見れればと思います。

恐らくデフォルトの状態と推察しますが、log_buffer の設定
(sga_target では制御されません) も気になります。

更新系の処理が多い場合、log_buffer を50MB ～ 100MB 程度に増やすと
効果があるかも知れません。

お礼

お礼が遅くなり申し訳ございません。
一旦、sga_targetを増やすことで９０％以上のヒット率となりました。
その他のチューニングは、もう少し勉強してから行ってみたいと思います。
ありがとうございました。

回答No.4

nora1962

まあ、バッファヒット率の向上というのはあくまで手段であって目標ではありません。
実際、巨大なDBデータを全件対象にする処理では、ヒット率はどうしても上がりません。
どれだけのスループットやレスポンスが必要なのかをまず検討してみるのもあるんじゃないかな、と思いますけど。

お礼

何度もコメントいただきありがとうございました！

回答No.3

entree

SGA_TARGETは10gから導入されたパラメータで、SGAの全体サイズを指定しておけば
コンポーネントのサイズを自動決定してくれる便利なパラメータです。

なので、それを攻略すれば・・・というのはちょっと違うような気がしますが。
ただ、SGAとしてに割り当てたいメモリサイズを指定するだけです。

さて、バッファヒット率が低い原因ですが、多くのケースでは全表走査が多いこと。
全表走査の結果は基本的にはキャッシュされないかされてもすぐに追い出されて
しまいます。それが意図されていることであればよいのですが、そうではないのであれば
SQLのチューニングや索引の追加が必要になります。

お礼

お礼が遅くなり申し訳ございません。
一旦、sga_targetを増やすことで９０％以上のヒット率となりました。
その他のチューニングは、もう少し勉強してから行ってみたいと思います。
ありがとうございました。

回答No.2

uresiiwa

１．SGA_MAX_SIZEの最適化
SGA割当が適切な設定値か、確認してみてください。
マシンの物理メモリと、Oracleにいくらまで使わせることができるか、により決まると思います。
Oracle以外にもメモリを使うので、vmstatコマンドなどで、Freeメモリを確認し、Freeが物理メモリの25％の値になるぐらいがぎりぎり一杯の設定値となります。大きすぎる値は設定しないように気をつけてください。

２．バッファキャッシュ利用効率が悪いSQLがいないか？
たくさんメモリを割り当てても、効率の悪いSQLを乱発すると、いくらあっても足りなくなります。
とても効率が悪いSQLがいないかを確認し、いればチューニングしましょう。
１，２ともに実施しても足りない場合、物理メモリが足りないということになりますが、大抵はチューニングによって大幅に効率化できます。

効率の悪いSQL＝遅いSQLと考えてほぼ差し支えなく、それを探す方法としてSTATSPACKレポートが簡単でお勧めですが、もっと簡単な方法としては、以下のような手順があります。
(1)アプリを一通り動かす（SQL実行統計情報を生成する）
(2)以下のSQLで、バッファアクセスの激しいSQLを調べる
select * from v$sql order by BUFFER_GETS desc;
（elapsed_timeがSQLの実行経過時間（マイクロ秒）ですので、そっちを見てもよいです）

>最終目的は、パフォーマンス最適化を目指していますが、そのために確認しておくべき内容および設定方法を教えてください（あるいは回答にマッチしたサイトがあれば）。sga_tａrgetを攻略すれば達成できるのでしょうか？

sga_targetだけで解決するものではなさそうに思いました。
キャッシュバッファがボトルネックとして現れている場合、SGAが少なすぎる（DBインスタンスの問題）の場合と、SQLがバッファを乱用しすぎている（アプリケーションの問題）がともに考えられ、後者である可能性のほうが一般的です。
また、チューニング順序としては、DBインスタンスチューニングよりもアプリチューニングのほうが順番としては先に行うべきものといえます。

お礼

お礼が遅くなり申し訳ございません。
一旦、sga_targetを増やすことで９０％以上のヒット率となりました。
その他のチューニングは、もう少し勉強してから行ってみたいと思います。
ありがとうございました。

回答No.1

nora1962

・CURSOR_SHARINGをSimilarかForceにする。（個人的にはSimilarをお勧め）
ALTER SYSTEM SET CURSOR_SHARING = Similar;
・定期的（出来れば1時間ごとぐらい）に以下のSQL文を実行し、結果を保存する。
SELECT T.*
FROM ( SELECT
SQL_TEXT,
CASE COMMAND_TYPE
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'SELECT'
WHEN 6 THEN 'UPDATE'
WHEN 7 THEN 'DELETE'
ELSE 'OTHER'
END COMMAND_TYPE,
EXECUTIONS,
DISK_READS,
DIRECT_WRITES,
BUFFER_GETS,
RANK() OVER( PARTITION BY COMMAND_TYPE ORDER BY DISK_READS DESC ) RNK
FROM V$SQLAREA ) T
WHERE T.RNK < 10
ORDER BY T.COMMAND_TYPE,T.RNK;

この結果でDISK_READSが多いSQLについて実行計画はどうなっているか分析する。
索引がない場合、有効に活用されていない場合などを重点にチェックする。

・その上でSGA自体を増やせるか検討する。その際、スラッシングが起きないようにLinuxならvmsat、Windowsならパフォーマンスカウンタでページイン、ページアウトを監視する。

お礼

お礼が遅くなり申し訳ございません。
一旦、sga_targetを増やすことで９０％以上のヒット率となりました。
その他のチューニングは、もう少し勉強してから行ってみたいと思います。
ありがとうございました。