概要
OracleのチューニングはSQLの改善が基本です。
必要なところにインデックスを張り、そのインデックスが有効活用されるようなクエリを書く。
なのですが、SQLをおいそれと変更できないシステムってありますよね。
そういったシステムに対して効果がありそうなチューニングポイントについて、独断と偏見と経験で評価していきます。
期待度★★★
cursor_sharing パラメータの変更
遅いケースと速いケースが混在する場合などに効果がある可能性があります。
FORCE => 意味的に同じSQLであれば同じと見なしてカーソルを共有する
EXACT => 一言一句変わらないSQLのみを同じと見なしてカーソルを共有する
「意味的に同じ」というところがアヤシイポイントです。
実は全く別の実行計画が最適なはずのSQLが十把一絡げで処理されてしまう可能性があるため、
「こっちは速いけどこっちは遅い」という現象が起こりえます。
EXACTであればそのような事態は防げるのですが、少しでも違うSQLは別物として処理されてしまうため、
そのオーバーヘッドが無駄になることがあります。
SELECT * FROM xx WHERE yy = 1000
SELECT * FROM xx WHERE yy = 1001
このような2つのSQLであれば内部の実行計画は通常同じで問題ないはずです。
しかし、EXACTにするとこれらがすべて別々に扱われてしまいます。
この1000, 1001のところの値が無数にあると、それを保持するメモリが枯渇する可能性もあります。
通常、このような場合はバインド変数などを使ってSQL側からアプローチするべきですが。。。
このパラメータのいいところは、「ALTER SESSION文」で変えられるところです。
つまり、自分自身のセッションだけFORCEになったりEXACTになったりできます。
行う処理のタイプに合わせて切り替えながら処理をすることで高速化することが期待できます。
ALTER SESSIONの良さは、即時反映（Oracleの再起動等が不要）、システム全体に影響を与えない（影響するのは自分のセッションのみ）
というところがあると思うので、これで回避できるなら僥倖といえるでしょう。
optimizer_index_cost_adj パラメータの変更
SQLを書くときには、全件取得でもしない限り、インデックスを使ってもらうようにするのが基本です。
しかし、インデックスを使うかどうかはOracleオプティマイザのさじ加減。
そのさじ加減を調整しようというのがこのパラメータです。
Oracleのオプティマイザはインデックスを使うか、使うまいか、という判断をそれぞれの実行コストを見積もって決めています。
たとえばあるSQLに対して、
インデックスを使う => コスト100
インデックスを使わない => コスト70
とOracleが見積もれば、インデックスは使われません。
ところが、このパラメータを50にした場合、以下のようになります。
インデックスを使う => コスト100 * 50% = 50
インデックスを使わない => コスト70
これで、インデックスを使ってくれるようになるわけです。
値を下げれば下げるほどインデックスを使うほうによっていきます。
経験則だと、極端な話「1」つまりインデックスコスト1/100でも、
なにがなんでもインデックスを使わせたいシステムであれば効果があります。
期待度★★
optimizer_dynamic_sampling パラメータの変更
これについては正直挙動をよく理解していませんが、「壊滅的に遅い」という状況下でダメもとで0にしたところ、改善しました。
optimizer_mode パラメータの変更
これもふわっとしか理解していませんが、
FIRST_ROWS_n => 最初のn行を最速で返そうとする（フェッチ開始までの時間を最短にする？）
ALL_ROWS => 全行取得を最速で返そうとする
なんとなくですが、FIRST_ROWS_nだと少ない件数取得のための最適化なので、インデックスが使われやすくなるような気がします。
ALL_ROWSは全行なので、フルスキャンでもいいか、という。
これも経験則です。
期待度★
optimizer_index_caching パラメータの変更
インデックスを使う／使わないはもちろんですが、テーブル結合時の結合方法というのも、
パフォーマンスに影響を与えます。
このパラメータを高くすればするほど、「ネステッド・ループ結合」が採用されやすくなります。
（つまりコストが低く見積もられます。optimizer_index_cost_adjと違ってパラメータを高くするほどコストは低くなる、という仕組みなので注意）
SQLの実行計画を覗いてみて、結合方法がハッシュ結合等になっていて、そこがボトルネックであるようなら効果が期待できます。
尚、結合方法はSQL側でヒント句を使用することでもコントロールできます。
（ここでは割愛します）
optimizer_features_enable パラメータの変更
オプティマイザの機能を、Oracleのどのバージョンにするか、ということを決められるパラメータです。
通常、インストールされているOracleのバージョンのオプティマイザが使われるわけですが、それを変更することで、
インストールされているバージョンとは別のバージョンのオプティマイザ機能を使うことができます。
これが特に効果を発揮するのはOracleをバージョンアップするケースかと思います。
SQLやパラメータ等何も変えていないのにバージョンアップしたら遅くなった、というようなときは、
オプティマイザの挙動が新バージョンで変わったことが原因、という可能性もあるわけです。
そういうときは、このパラメータをバージョンアップ前の値にしてやることで解決することがあります。
いつまでも前のバージョンに縛られるということでもあるので、できれば使わずに済ませたいところですが。。。
ALTER SYSTEM FLUSH SHARED_POOL; を定期実行
遅くなったまま固定されている実行計画のキャッシュをクリアすることで、実行計画を作り直してもらう。
「たまたま」実行計画が遅かった場合は効果が期待できるが、
そうでない場合は結局遅い実行計画が再作成されるだけということもありえます。
むしろ、再計画することで遅くなる、という可能性もあります。
「サイコロを振りなおす」くらいの意味合いしかないのではないでしょうか。
しかも、実行中のキャッシュはクリアされないようです。
（当然といえば当然ですが。。。）
統計情報を消す
最終手段、荒療治です。
Oracleのオプティマイザはテーブルの統計情報を元に実行計画作成のためのコスト計算をしているわけですが、
その情報ソースを断ってしまう、というOracleオプティマイザに見切りをつける運用です。
統計情報がなくなることで、「無難な」実行計画になることが期待できます。
（このあたりは完全に想像です）
トリッキーな実行計画に振り回されている場合などは、効果があるかもしれません。
消すにしても、テーブルAは統計情報を取得して、テーブルBは消す、するとAとBのJOINが高速化する、
というように、細やかなコントロールをすることで問題が解消することもあります。
ここまでくると完全に手探りですね。
ひとつひとつ、パターンを変えながら最適解を見つけ出していくという地道な作業です。