Oracle 9i Gold Performance Tuning メモ

●チューニングの概要

1. チューニング手順は、 設計、app、メモリ、I/O、競合、OS。
2. 統計情報はV$SYSSTAT, V$SESSTAT, V$MYSTAT。V$SYSSTATはインスタンス起動からの累積
3. ユーザー・トレース・ファイルはサーバープロセス単位に作成される (ユーザープロセス単位ではない)
4. 共有プール、ラージプール、Javaプールの空きメモリの確認に使うのは V$SGASTAT
5. V$SESSION_WAITでWAIT_TIME=0の場合は現在待機中。その時間が分かる列はSECONDS_IN_WAIT
6. 共有メモリが断片化しても、共有メモリが不足というエラーになる。(ORA-04031)

1. STATSPACKでピーク時に実行すべき処理はSTATSPACK.SNAP
2. STATSPACKの統計を出力するには、 PERFSTATユーザーでspreport.sqlを実行する。
3. STATSPACKのサマリーに含まれる情報は、 「キャッシュのサイズ、負荷のプロファイル、インスタンスの効率、上位5つの待機イベント」
4. UTLBSTAT, UTLESTATの統計が出力されるファイルの名前はreport.txt

1. shared pool、library cacheラッチの競合が多い場合の解決策は
   「SQLを共通化し再利用されるようにする」
   「ラージプールを使う」
   「専用サーバー接続にする」の3つ
2. 9iでは自動調整されるラッチは cache buffers lru chain

1. データディクショナリキャッシュのチューニング目標 「V$ROWCACHEで、GETSに対するGETMISSESの比率が15%未満」 を実現するためDDCのサイズを大きくするには、SHARED_POOL_SIZEを大きくすることで間接的に指定する。
2. ライブラリキャッシュのチューニング目標 「V$LIBRARYCACHEのGETHITRATIOの値が90%以上」と 「V$LIBRARYCACHEのPINSに対するRELOADSの比率が1%未満」
3. 共有プールの予約領域のチューニング目標 「V$SHARED_POOL_RESERVEDのREQUEST_FAILURES、REQUEST_MISSESが両方0、または少なくとも今の値から増えない」 解決するには、 SHARED_POOL_SIZEとSHARED_POOL_RESERVED_SIZEを大きくする。
4. 共有プールのカーソル共有のチューニング目標 「V$LIBRARYCACHEのGETSに対するGETHITSの比率が0.9以上」
5. 共有プールで最初にチューニングを図るのはライブラリキャッシュ。 理由はデータディクショナリキャッシュのデータは、 ライブラリキャッシュのデータよりも長くメモリに保持される傾向があるため。
6. DBMS_SHARED_POOL.KEEPでオブジェクトを固定する際に事前に実行が必要なスクリプトは dbmspool.sql
7. 共有サーバー接続時に共有プールに格納されるのはユーザーのセッションデータとユーザーのカーソル領域。 ソート領域はセッションデータの一部である。
8. インスタンス全体で使用されているUGAサイズを確認するには、 V$SESSTATにて「session uga memory」統計を検索する
9. 全表操作のCACHE属性は、ラージプールの使用には関係がない。
10. カーソルがクローズするまで破棄されないようにするには、CURSOR_SPACE_FOR_TIMEをTRUEにする。
11. 共有プールの予約領域は、共有プールの中に作られる。 複数バッファプールと混同しないように注意

1. SGA_MAX_SIZEは動的に変更できない。
2. 起動中にサイズ変更可能なメモリ領域は、 データベース・バッファ・キャッシュ(DB_CACHE_SIZE)と共有プール。 ラージブール、REDOログバッファ、PGAのサイズは変更できない。
3. バッファ・キャッシュ・アドバイザは、DB_CACHE_ADVICEパラメータにONを指定すると有効になる。 異なるキャッシュ・サイズでの物理読込みの数を予測することができる。
4. V$SYSSTATの「free buffer inspected」の値が増え続ける場合、DBBCのサイズを大きくすべき
5. 複数バッファプール使用時、各領域のサイズを知るのに使うのはV$BUFFER_POOL
6. 複数バッファプール使用時、DBBCのヒット率を知るのに使うのはV$BUFFER_POOL_STATISTICS
7. DBBC上のデータブロックは空きバッファ、使用済みバッファ、確保済みバッファのいずれかの状態になり、 LRUリスト、使用済リストの2つのリストで管理される。
8. DBBCと共有プールのサイズで使うグラニュルは、SGA_MAX_SIZEが128MB以下ならば4MB、 それを越えると16MBであり、指定した値が割り切れないと切り上げられる。
9. DB_CACHE_SIZEで割り当てるDBBCは標準ブロックサイズの領域。 非標準サイズの領域やKEEP,RECYCLEの領域はこれとは別に確保される。
10. 空きリストの競合状況を知るには、V$SESSION_WAITの buffer busy waitsイベントの値を見る。 (free buffer waitsではない)
11. V$CACHEビューを見るには、データベース作成後、catparr.sqlをSYSユーザーにて実行する必要がある。

1. データ・ファイルに対するI/Oを調査するには、V$FILESTATビューかSTATSPACKを使う。

1. TEMPORARY表領域の一時セグメントは、最初のディスクソート時にセグメントが作られる。
2. TEMPORARY表領域の一時セグメントは、データベースのクローズ時に削除される。
3. TEMPORARY表領域の一時セグメントは、ソートの規模に応じて動的に拡張される。
4. SORT_AREA_RETAINED_SIZEのデフォルト値は、SORT_AREA_SIZEと同じ値
5. PGA_AGGREGATE_TARGETは、SORT_AREA_SIZE、HASH_AREA_SIZE、BITMAP_MERGE_AREA_SIZEの個別指定の代わりをする。 PGA_AGGREGATE_TARGETを設定すると、これら3つのパラメータは無効となる。
6. メモリソートに対するディスクソートの比率の目安は5%未満
7. ディスクソートとメモリソートの回数を両方見れるビューはV$SYSSTAT。 V$SORT_USAGEはディスクソートの情報しか見れない。
8. WORKAREA_SIZE_POLICYをAUTOにすると、 自動PGAメモリ管理が有効になり、PGA内のメモリ領域をOracleが管理する。 (PGA_AGGREGATE_TARGETの設定も必須)
9. 2つの表がソート・マージ結合されORDER BY句によるソートを行う場合、 アクティブなソート領域が1つ(SORT_AREA_SIZE内)、 結合ソート用の領域が2つ(SORT_AREA_RETAINED_SIZE内)が必要。
10. SORT_AREA_RETAINED_SIZEをあえて設定し、SORT_AREA_SIZEより小さくする必要があるのは 「メモリが非常に不足しているとき」と「共有サーバーのとき」。
11. ソートで使われたブロック数を知るには、 V$SORT_SEGMENTのMAX_SORT_BLOCKSを見る。
12. アクティブなソート量を知るには、V$SORT_USAGEまたはV$SORT_SEGMENTを見る。
13. パラレル問合せの場合、プロセス毎にソート領域が必要になる (必要な領域の数が倍になる)
14. ANALYZEコマンドもソート領域を使う。一方、ネスト・ループ結合はソート領域を使わない。
15. ソート領域をカーソルがクローズするまで解放しないためにTRUEにできるパラメータは CURSOR_SPACE_FOR_TIME。名前からは想像できないので特に注意

1. 4つの同時トランザクションごとに1つのロールバック・セグメントを用意するのが、一般的な目安
2. ロールバックセグメントが小さいと、競合ではなくエラーが発生する。
3. CREATE ROLLBACK SEGMENT文での制限は、 「MINEXTENTSは2以上」「PCTINCREATEは指定不可(常に0)」「OPTIMALは、初期サイズより小さくできない」 の3つ。
4. ロールバックセグメントの書き込み側は、読み取り一貫性を必要としない (必要とするのは読み取り側)
5. UNDO_TABLESPACEパラメータの指定がなくてもUNDO表領域は作成できる。
6. UNDO表領域をALTER TABLESPACEすることは可能(データファイルの追加ができる)
7. UNDO表領域に自動拡張の設定は可能。 エクステントのサイズ(UNIFORM SIZE等)は設定できない。
8. 自動UNDO管理にするには、 UNDO表領域を用意し、UNDO_MANAGEMENTパラメータをAUTOに設定する。
9. 自動UNDO管理にすると、表領域内にUNDOセグメントが自動作成され、起動時に自動的にオンラインになる
10. ロールバックセグメントの競合の有無を知るには、以下の3つの方法がある
    ・ V$ROLLSTATビューのWAITS列
    ・ V$WAITSTATビュで待機イベントがundo headerのデータ行
    ・ V$SYSTEM_EVENTで待機イベントがundo segment tx slotのデータ行
    
11. UNDO_TABLESPACEの変更はALTER SYSTEM。ALTER SESSIONでは変更できない。
12. 複数トランザクションがあるアプリケーションで一定期間内のロールバックセグメントの使用量を知るには、 V$ROLLSTATのWRITESの値を、実行前後で比較する。
13. ロールバックセグメントが大きく、DBBCのサイズが小さい場合、 I/Oのパフォーマンスで問題が発生しうる。
14. ロールバックセグメントの自動縮小の指定は「AUTOSHRINK ON」ではなく「OPTIMAL」

1. V$LOCKED_OBJECTのXIDUSNは、使用しているロールバックセグメントの番号を表すが、 これが0の場合、他のセッションのロックが原因で待機していることを表す。
2. ロックを保持しているOracleユーザは、V$LOCKだけでは分からない。(V$LOCKED_OBJECTなら分かる)
3. 「ロック競合を診断できるもの」なら、V$LOCKもV$LOCKED_OBJECTも該当する。
4. ORA-00060によるデッドロックの検出時、その原因となった文のみロールバックされ、 トランザクションは中断状態になっている。トランザクションを中止するには、 明示的にロールバックする必要がある。
5. V$LOCKのTYPE列がTXは行ロック、TMは表ロックを表す。 TMの場合、ID1列はロックされているオブジェクト番号を表す。
6. CREATE、ALTER、DROPは排他DDLロック操作。共有DDLロックの代表例は、 「CREATE PROCEDURE」「AUDIT」「GRANT」。
7. utllockt.sqlスクリプトでロック競合を表示するには、事前に catblock.sqlスクリプトの実行が必要

1. 共有サーバーへの接続数、起動されたプロセス数等を知るにはV$SHARED_SERVER_MONITORを見る。
2. ディスパッチャの負荷状況を知るにはV$QUEUEとV$DISPATCHERを見る。
3. ユーザーセッションが正しく共有サーバー接続を行っているか確認するには、V$CURCUITを見る。
4. 共有サーバープロセスがアイドルなのかビジーなのかを知るには、V$SHARED_SERVERを見る。
5. 各セッションが使用している接続タイプ(専用 or 共有)を知るには、V$SESSIONを見る。
6. 共有サーバー接続にすべきケースは、「メモリが限界に近づいているハードで動いている」または 「ユーザとの対話に多くの時間を費やすアプリケーション」の場合。
7. 共有サーバープロセスは自動的に追加されるため、SHARED_SERVERSの値は小さい値でよい。
8. アイドル状態の共有サーバープロセスをSHARED_SERVERSパラメータで指定された数まで PMONが削除することがある。

1. 索引にはPCTUSEDは設定できない。 利用率が0になって初めて、空きリストに登録される。
2. 索引の利用率を知るためのビューは、V$OBJECT_USAGE (INDEX_USAGEではない)
3. 索引を作るときNOSORTが指定できるのは、索引を作る列が物理的に昇順ソートされている場合だけ (主キー列のソート順は関係ないので注意)
4. 高水位標を確認する方法は、DBA_TABLESビューとANALYZEコマンド（質問の意味がよく分からないが…）
5. オブジェクトが使用しているサイズが分かるビューは、V$DB_OBJECT_CACHE
6. 索引構成表のCREATE TABLEでOVERFLOW MAPPING TABLEを指定すると、 Ora9i以降は索引構成表でもビットマップ索引を作ることができる。
7. 索引構成表の作成にOVERFLOWをつけると、長い非キー列を別のセグメントに格納することができる。
8. 索引構成表には主キー制約は必須。追加の索引も作成可
9. 任意のセッションでトレースを有効にするには DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,TRUE);
10. STATSPACKは、4種類のSQLの結果を表示する：
    取得順、読み込み順、実行順、解析コール順
11. パーティション表、索引は
    ・パーティションレベルの権限等の制御はできない
    ・デフォルトではコストベースで動作する
12. ストアドアウトラインの目的は、 「ＤＢを変更しても一貫した実行パスを維持するため」
13. ストアドアウトラインをセッションで使うには、USE_STORED_OUTLINES=パラメータで指定する
14. ストアドアウトラインは
    ・カテゴリによってSQL文をグループ化できる
    ・カテゴリが異なれば、同じSQL文に複数のアウトラインが作成できる
    ・「同じSQL文の場合にアウトラインが使用される」
    …アウトラインのテキストとSQLが全く同一の場合のみ使われるという意味
15. アウトラインはOUTLNスキーマに格納され、OUTLN_PKGパッケージで管理する。
16. アウトラインを確認するには、OUTLNユーザのOL$表か、DBA_OUTLINESビューを見る。
17. プライベートアウトラインを使うには、 DBMS_OUTLN_EDIT.CREATE_OUTLINE_TABLESプロシージャで格納表を作っておく必要がある
18. ＴＫＰＲＯＦでは、通常トレースファイル名を指定するが、 実行時点から実行計画を測定する場合には、 EXPLAIN=user/password をオプションで指定する。
19. MVIEWの高速リフレッシュには、MVIEWログを使うものと、 ROWID範囲を指定するものがある。 MVIEWの更新は、インスタンスの上げ下げとは関係ない。
20. 統計情報を収集する方法は、ANALYZEコマンド、DBMS_STATSパッケージの2種
21. DBMS_STATSパッケージで、統計情報を保存したり、DB間で移動したりできる
22. 複数列からなる索引の読み込むブロックを減らすには、 REBUILD INDEX 等にCOMPRESSオプションをつける。
23. ビットマップ索引は、セグメント単位でロックが発生する。
24. ビットマップ索引のセグメントのロックは、表ロックとは意味が違う。 表ロックという言葉がついている選択肢を選ばないように。
25. ビットマップ索引は、B*Tree索引より少ない領域で格納される。
26. 索引列の値の行数が不均一な場合、データによって応答性能が 大きく変わるのを修正するには、ヒストグラムを収集する。 ANALYZE TABLE 〜 COMPUTE STATISTICS FOR COLUMNS 〜
27. 索引のリーフブロックで競合が多く発生する場合、逆キー索引の作成を検討する
28. 逆キー索引は、範囲検索では使われない。
29. クエリーリライトは、元表への問合せがMVIEWへの問合せで完全に置き換え可能な場合、MVIEWへの問合せに読み替える機能。
30. クエリーリライトを使うには、コストベースが必須。
31. クエリーリライトの初期化パラメータはQUERY_REWRITE_ENABLED、 MVIEWにつけるオプションはENABLE QUERY REWRITEオプション。 語順に注意
32. 大量のデータをimpでインポートする際は、COMMIT=Y をつけるとロールバックセグメント関連のエラーが起きにくくなる。

1. ユーザーに初期グループを指定するには、 DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP を使う。
2. DBリソースマネージャのリソースプランは複数作成できるが、一度に1つだけ有効にできる。
3. ユーザに複数のコンシューマーグループを指定できるが、一度に1つだけ有効にできる。
4. リソースプランの切替は ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=plan_name;
5. リソースマネージャやグループの管理に使うパッケージは DBMS_RESOURCE_MANAGER_PRIVS
6. リソースマネージャで管理できるのは「並列度」と「ＣＰＵ」
7. リソースマネージャの使用によって、各セッション別のＣＰＵ使用率を 知るには、V$SYSSTATまたはV$SESSTATの 「CPU used by this session」 統計を見る。
8. DBMS_RESOURCE_MANAGERパッケージで権限の管理をする際に、 最初に実行する必要があるのは CREATE_PENDING_AREA の実行
9. リソースプラン等を作成したが、有効にしようとするとエラーが出てしまう場合は、 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA を実行
10. コンシューマーグループを切り替えるには セッションレベル… DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP 管理者が切り替える… DBMS_RESOURCE_MANAGER.SWTICH_CONSUMER_GROUP_FOR_SESS DBMS_RESOURCE_MANAGER.SWTICH_CONSUMER_GROUP_FOR_USER
11. リソースプランに必ず含める必要があるコンシューマーグループはOTHER_GROUPS