以下の内容は、Oracle9iの動作に即しています。
アプリケーションの設計とプログラミング
データベースの構成
新しいアプリケーションの導入
トラブルシューティングとチューニング
応答時間の改善
データベース可用性の改善
データベースヒット率の改善
待機イベントの低減
不適切なセッション管理、カーソル管理、リレーショナル設計
設計、アプリケーション、メモリ、I/O、競合、ＯＳ
ボトルネック特定 → 原因の確認 → 解決 → ボトルネックが解消されたかのチェック
制御ファイルの多重化
REDOログ・メンバーの多重化
チェックポイントの頻繁な発生
データ・ファイルのバックアップ
同時ユーザー／トランザクションの数
Background_dump_destにて配置ディレクトリを指定
Background_dump_destにて配置ディレクトリを指定
user_dump_destにて配置ディレクトリを指定
max_dump_file_sizeにて最大サイズを指定
STATSPACK 及び utlbstat.sql,utlestat.sql(8.1.5以前）
v$ビュー(v$sysstatなど）のスナップショットを作成して、スナップショット間の差分情報よりシステム解析レポートを自動生成する
PERFSTATユーザーにて実行
$ORACLE_HOME/rdbms/admin/spauto.sql
待機時間など時間に関する統計を有効にする(約1%のoverhead)
> TIMED_STATISTICS=TRUE
8.1.5以前ではutlbstat.sql,utlestat.sqlを利用する
v$system_event, v$event_name, v$session_event, v$session_wait
ANALYZE文やパッケージ・プロシージャDBMS_STATSによる実行結果を格納
DBA_TABLES, DBA_INDEXES, INDEX_STATS, INDEX_HISTOGRAM
元表のx$表は直接の問い合わせは不可
システム全体の統計の収集 v$sysstat
SGAグローバル統計 v$sgastat
待機イベントの統計 v$system_event, v$event_name
v$statname 統計番号、名前、クラス
v$sestat SID、統計番号、値
v$session SID、シリアル番号、ユーザー名、OSユーザー
v$event_name イベント番号、イベント名、パラメーター
v$session_event 起動してからのセッションごとの待機時間合計
SID、イベント名、待機合計、タイムアウト合計
v$session_wait 現在待機しているリソース、イベントの一覧
SID、順序番号、イベント、パラメーター1/2/3、待機時間
v$mystat 現セッションの統計
LRUリストやSGAなどのリソースのロックシステム
v$latchにて統計情報を確認する。
Willing-To-Waitモード：要求時にロックされている場合、一定時間後リトライ 統計項目 gets, misses, sleeps
Immediateモード：要求時にロックされている場合、他の処理に進む 統計項目 immediate_gets, immediate_misses<br>
v$sgastat
SHARED_POOL_SIZE 共有プールサイズ
DB_CACHE_SIZE データベース・バッファキャッシュサイズ
DB_RECYCLE_CACHE_SIZE リサイクルDBBCサイズ
DB_KEEP_CACHE_SIZE キープDBBCサイズ
LOG_BUFFER REDOログバッファサイズ
LARGE_POOL_SIZE ラージ・プールサイズ
JAVA_POOL_SIZE JAVAプールサイズ
SGA_MAX_SIZE SGA最大サイズ
shared_pool_sizeにてサイズを指定
shared_pool_sizeを大きくしすぎると、管理のオーバーヘッドでパフォーマンスが落ちることがある
十分な領域を割り当てて、文が廃棄されるのを防止
select namespace,gethitratio,GETS,PINS,RELOADS from v$librarycache;
sum(reloads)/sum(pins) < 1% がOLTPでの目安
・gethitratio > 90% も目安となる
・すべての実行計画の詳細な統計：v$sqlarea
・共有プールにキャッシュされているSQL一覧：v$sqltext
キャッシュされているオブジェクト一覧：v$db_object_cache
汎用コード、バインド変数などを利用して、実行計画を共有
再解析の原因となる共有SQL領域の無効を防止（一時的にパフォーマンス悪化）
・列の追加・変更・削除
・表、ビュー、順序、シノニムの変更
・Analyzeの実行
・ストアド・プロシージャの再コンパイル
（８未満）断片化の防止
・予約領域の割り当て
・ラージオブジェクトの固定
・大きな無名PL/SQLを使用しない
・共有サーバー構成ではラージプールを使用する
ストアド・オブジェクト（パッケージ、ビューなど）で必要なサイズを定義
select sum(sharable_mem) from v$db_object_cache where type in('PACKAGE','PACKAGEBODY','FUNCTION','PROCEDURE');
SQL文で使用されるメモリ量を定義
select sum(sharable_mem) from v$sqlarea where executions>5;
断片化、データの分散に備えて、大量のメモリーが必要になったときの領域を確保
・共有プールの予約領域shared_pool_reserved_size を定義
− shared_pool_sizeの１０％が目安（<shared_pool_sizeの50%の制限）
− v$shared_pool_reservedを確認してサイズを調整する
（ただし9iでは正しい結果を得られない）
頻繁に使用するオブジェクトをメモリ上に固定
・固定されていないPL/SQLオブジェクトを検索
select name,type,EXECUTIONS from v$db_object_cache where sharable_mem>10000 and type in('PACKAGE','PACKAGEBODY','FUNCTION','PROCEDURE') and kept='NO';
dbms_shared_poolパッケージの作成
・@$ORACLE_HOME/rdbms/admin/dbmspool.sql
ライブラリ・キャッシュにパッケージを固定
EXECUTE dbms_shared_pool.keep('object_name','object_type');
・object_type=P:パッケージ、ファンクション、プロシージャ（デフォルト） T:タイプ Q:順序 R:トリガー
固定解除
EXECUTE dbms_shared_pool.unkeep('object_name');
無名PL/SQLブロックを検索
SELECT sql_text,address,hash_value FROM v$sqlarea where command_type=47 and LENGTH(sql_text)>500;
・無名PL/SQLブロックはパッケージファンクションをコールする小さな無名PL/SQLに変換するか、固定する
exec dbms_shared_pool.keep('address,hash_value','A');
OPEN_CURSOR：ユーザーのプロセスに割り当てられたプライベートSQL領域を参照する
カーソルの数を定義
CURSOR_SPACE_FOR_TIME:TRUEの場合、高速化のためオブジェクトを参照する
カーソルがクローズするまで共有SQL領域から破棄されないので、
Reloadsが0でない限り設定しない。
SESSION_CACHED_CURSORS:セッションの範囲内で、クローズされたカーソルをキャッシュする
数を設定する。解析フェーズをスキップすることが出来る。
デフォルトは０。
Oracle9iでは実行計画を、SQL文が破棄されるまで保持する
キャッシュされた実行計画：V$SQL_PLAN
v$sqlには対応する実行計画に基づくハッシュ値 plan_hash_value列が追加
キャッシュミスを防止 v$rowcacheのsum(getmisses)/sum(gets)<15%が目安
・shared_pool_sizeの値にて間接的にサイズを指定
・ローカル管理にすれば、dc_free_extentsとdc_used_extentsのリクエスト数が少なくなる
共有サーバー接続ではUGAが共有プールに含まれる （代わりにUGAのサイズは小さくなる）
接続で使用されるUGA領域
select sum(value) || 'bytes' "Totalsessionmemory" from v$mystatm,v$statnamet where m.statistic# = t.statistic# and name = 'sessionugamemory';
共有サーバーの全ユーザーによって使用されるUGA領域
select sum(value) || 'bytes' "Totalsessionmemory" from v$sesstate,v$statnamet where e.statistic# = t.statistic# and name='sessionugamemory';
すべてのユーザーによって使用される最大UGA領域
select sum(value) || 'bytes' "Totalmaxmemory" from v$sesstate,v$statnamet where e.statistic# = t.statistic# and name='sessionugamemorymax';
LARGE_POOL_SIZE：サイズを指定
select * from v$sgastat where pool = 'largepool';
I/Oサーバー・プロセス：DBWR_IO_SLAVES
バックアップ及びリストア操作
共有サーバのセッション・メモリーを割り当て
パラレル実行
・PARALLEL_AUTOMATIC_TUNING=TRUEを設定することでパラレル実行バッファが割り当てられる
・FALSEの時は共有プールに割り当てられる
割り当てに失敗した際に、各コンポーネントがラージプールを要求する設定になっていた場合に発生する事象
・ログのアーカイブに失敗し、エラーを戻す
・RMANがアラートファイルにエラーを書き、I/Oスレーブを使用しない
・共有サーバー接続で、エラーORA-4031が発生する
JAVA_POOL_SIZE：サイズ指定
JAVA_SOFT_SESSIONSPACE_LIMIT：ユーザーのセッション中にJavaのメモリ使用量が
このサイズを超えるとアラートファイルに警告が書き込まれる
JAVA_MAX_SESSIONSPACE_SIZE：ユーザーのセッション中にJavaのメモリ使用量が
このサイズを超えそうになるとメモリ不足で終了する。ORA-29554
共有プール：ロードされるクラスごとに約8KB
共有プール、またはラージプール：サイズの大きいJARファイルのロードに約50MB
Javaプール：すべてのセッション固有Javaコード及びデータ用に使用
中程度のJavaアプリケーションに約50MB
select * from v$sgastat where pool = 'javapool';
DB_BLOCK_SIZE：標準ブロックサイズ Oracle9i以降のパラメーター。動的にサイズ変更可
DB_CACHE_SIZE：DEFAULTバッファプールのサイズをバイト単位で指定
DB_KEEP_CACHE_SIZE：KEEPバッファプールのサイズをバイト単位で指定
DB_RECYCLE_CACHE_SIZE：RECYCLEバッファプールのサイズをバイト単位で指定 Oracle8i以前 または下位互換用。9iパラメータとの同時利用不可
DB_BLOCK_BUFFERS：バッファプールのサイズをDB_BLOCK_SIZEで指定
BUFFER_POOL_KEEP：KEEPバッファ件数とLRUラッチ数
BUFFER_POOL_RECYCLE：RECYCLEバッファ件数とLRUラッチ数
DB_BLOCK_LRU_LATCHES：ラッチ総数の指定
キャッシュ内データブロックの状態
空きバッファ：ディスク上とメモリー内に同じイメージがあるデータブロック
使用済みバッファ：メモリ内にディスク上と異なるイメージのあるデータブロック
ディスクに書き込まれないと再利用できない
確保済みバッファ：現在アクセス中のデータブロック
１．まずハッシュ関数によりＤＢＢＣ内に目的のデータがないことが確認される
（見つけた場合は該当ブロックをLRUリストのMRU側へ移動して、読み込み。
該当ブロックが変更されている場合は、ロールバック・セグメントの情報を
元に以前のイメージを再構築する）
２．LRUリストをLRU側から検索して、空きバッファを検索
３．途中見つけた使用済みバッファは使用済みリストへ移動
４．使用済みリストのサイズが閾値を越えた場合は、DBWnへ書き込みを指示
検索閾値の範囲内で空きバッファがない場合にもDBWnへ書き込みを指示
５．空きバッファを見つけたら、そのブロックにデータを上書きし、
ブロックをLRU側からMRU側へ移動する
１．使用済みリストのサイズの閾値超過
使用済みリストのデータブロックをデータファイルへ書き出す
２．検索閾値の超過
LRUリストの使用済みバッファを直接データファイルへ書き出す
３．タイムアウト（３秒ごと）
LRUリストから使用済みバッファを使用済みリストへ移動した後に
使用済みリストのバッファをデータファイルへ書き出す
４．チェックポイント（ＣＫＰＴ→ＬＧＷＲ→ＤＢＷｎ）
LRUリストから使用済みバッファを使用済みリストへコピーした後に
使用済みリストのバッファをデータファイルへ書き出す
５．表領域がTEMPORARY OFFLINEまたはBEGIN BACKUP時
LRUリストから使用済みバッファを使用済みリストへコピーした後に
使用済みリストのバッファをデータファイルへ書き出す
６．オブジェクトの削除
LRUリストから使用済みバッファを使用済みリストへコピーした後に
使用済みリストのバッファをデータファイルへ書き出す
７．OracleのAbort以外の停止時
インスタンスを停止させずにSGAのメモリ量を変更可能
・ALTERSYSTEMSETdb_cache_size=8M;
グラニュル単位で変更（指定値がグラニュルの倍数でない場合は切り上げ）
SGAの合計サイズが128MB以下の場合：4MB
SGAの合計サイズが128MBより大きい場合：16MB
SGA_MAX_SIZEを超えて変更することは出来ない。ORA-00384
キャッシュサイズを変更した際の動作予測のための統計値を収集
目標：ヒット率。ＯＬＴＰの場合９０％以上が目安
・診断基準
計算式：
1-( physical reads-physical reads direct - physical reads direct(lob)) / session logical reads
V$BH：キャッシュされているデータブロックを表示
V$BUFFER_POOL：各バッファ・プールの割り当てと変更前のサイズを確認
V$BUFFER_POOL_STATISTICS：個々のプールの情報を診断
バッファ・キャッシュのサイズ増加
ヒット率９０％未満でメモリに余裕がある場合、
前回の増加で効果があった場合などに検討する
DSSではサイズを増やしてもパフォーマンスの向上は望めない場合がある
複数のバッファ・プールを使用
KEEPバッファ：再利用される可能性のあるオブジェクトの保持に利用
RECYCLEバッファ：再利用される可能性がほとんどない
データブロックの排除に利用
DEFAULTバッファ：常に存在する
8iの場合の複数バッファ・プールの指定
DB_BLOCK_BUFFERS=20000 #DEFAULTバッファのラッチ数は6-3-2=1
DB_BLOCK_LRU_LATCHES=6#LRUラッチ１つにつき最低50バッファ必要
BUFFER_POOL_KEEP=(BUFFERS:14000,LRU_LATCHES:3)
BUFFER_POOL_RECYCLE=(BUFFERS:14000,LRU_LATCHES:2)
ALTER TABLE table_name STORAGE (BUFFER_POOL KEEP);
KEEPバッファ・プールのガイドライン
サイズ見積もり：指定したオブジェクトのほぼ全体のサイズ
DEFAULTバッファプールサイズの10%未満
> ANALYZE TABLE table_name estimate statistics; > SELECT table_name,blocks FROM dba_tables WHERE table_name = 'TABLE_NAME';
RECYCLEバッファ・プールのガイドライン
サイズ見積もり：アクティブなデータブロックのみ保持
（データベースに大きな負荷がかかっている状態において）
> @?/rdbms/admin/catparr.sql > SELECT owner#,name,count(*) blocks from v$cache GROUPBY owner#,name;
検索したオブジェクトのうち、RECYCLEバッファで使用するオブジェクトのブロック数を４で割ってサイズを取得する。
複数バッファプールヒット率の確認
> select name, 1-(physical_reads/(db_block_gets+consistent_gets)) "HIT RATIO" from v$buffer_pool_statistics where db_block_gets + consistent_gets>0;
表のキャッシュ
・全表走査時でもブロックをLRUリストのMRU側にキャッシュする
CACHE句を使用して表を作成、変更
問い合わせ時にCACHEヒント句を使用
・多数のユーザーに使用される小さな参照表に使用すると効果がある
free buffer inspected：空きバッファを探すためにスキップされたバッファの数。
値が高いが増えている場合は、バッファキャッシュサイズを増やすことを検討する~ > select name,value from v$sysstat where name = 'freebufferinspected';
buffer busy waits：プロセスがDBBCが使用可能になるまで待機
free buffer waits：サーバプロセスが空きバッファを見つけられないか、
使用済みリストが満杯の場合に発生
この２つの頻繁な発生は、DBWnのチューニングが必要な場合がある
> select event,total_waits from v$system_event where event in('free buffer waits','buffer busy waits');
空きリスト競合 オブジェクトへの挿入処理時に、複数プロセスによるアクセス競合が発生する
競合の診断
> select class,count,time from v$waitstat where class = 'segmentheader';~ > select event,total_waits for mv$system_event where event='bufferbusywaits';~
競合の軽減
１．オブジェクトのFREELISTSを増やす FREELISTSの数の確認
> select s.segment_name,s.segment_type,s.freelists,w.wait_time,w.seconds_in_wait,w.state from dba_segmentss,v$session_waitw where w.event = 'eventbusywaits' and w.p1=s.header_file and w.p2=s.header_block;
ALTER TABLEコマンドにて空きリストを増やす
２．オブジェクトを自動管理表領域に移動する
（ただしver9.0.1ではブロックの使用効率が悪くなるので推奨しない）
create tablespace bit_seg_ts~ ...~ extentmanagement local~ segmentspacemanagement auto;
REDOログ・エントリにはINSERT,UPDATE,DELETE,CREATE,ALTER,DROPの各操作を再実行するために必要な情報を格納
初期パラメータ：LOG_BUFFER
値を増やすと、REDOログファイルへのI/Oが減少する
COMMITを頻繁に使用すると、バッファの使用量が少なくてすむが、REDOログ・ファイルへのI/Oが増加する
LOG_BUFFERの最小サイズは64K、デフォルト値は512K、3MB以上にしても効果は上がらない？
CPUは高速だが、ディスクの速度が比較的遅いマシンの場合、LGWRがREDOログ・バッファの一部をREDOログファイルに書き込んでいる間に、新しいエントリが残りのすべてのREDOログバッファを使いきってしまい待機してしまう場合がある
＜ログスイッチが発生しなかったためのバッファ内での領域待ち＞ 目標：待機時間０
> select sid, event, seconds_in_wait, state from v$session_wait where event = 'logbufferspace';
＜サーバー・プロセスがバッファ内の空き領域が空くのを待機しているかの確認＞
v$sysstatの"redobufferallocationretries"統計を確認。
目標：REDOログエントリー数の1%未満に押さえること
> select name,value from v$sysstat where name in ('redo buffer allocation retries','redo entries');
log buffer space待機イベントが発生している場合
redo buffer allocation retriesが発生している場合
・Redoログ・バッファが小さい場合はサイズを増やす
・Redoログ・ファイルを高速なディスク（ストライプ・ディスク）に移動する
LGWRがバッファを使用可能にするタイミングが遅い
REDOログ・ファイル上でディスクI/O競合が発生している場合
・対策：専用のディスクにREDOログファイルをのせかえる
ログスイッチの待機イベントlogfile switch completionの発生回数が多い場合
> select event,total_waits,time_waited,average_wait from v$system_event where event like 'logfile switch completion%';~
・対策：REODログ・ファイルのサイズを大きくします
チェックポイントが完了しないうちにログファイルを循環した場合
− アラートファイルに「CHECKPOINT NOT COMPLETED」というメッセージがないか確認
− V$SYSTEM_EVENTビューで確認
> select event,total_waits,time_waited,average_wait from v$system_event where event = 'logfile switch (checkpoint incomplete)';~
・対策：LOG_CHECK_POINT_INTERVALとLOG_CHECKPOINT_TIMEOUTに適切な値を設定。REDOログ・グループのサイズと数を調整する
V$SYSTEM_EVENTビューで確認
> select event,total_waits,time_waited,average_wait from v$system_event where event = 'logfile switch (archiving needed)';~
対策：アーカイブ先のディスクがあることを確認し、REDOログ・グループを追加
・ARCnプロセスの現在数が不十分であれば、LGWRが自動的にARCnプロセスを起動するので、大きな負荷が予測されるときには最大数 LOG_ARCHIVE_MAX_PROCESSESを調整する
大量のREDOログ・エントリ発生時に記録しないようにするには、
・NOARCHIVELOGモードにてダイレクト・パス・ロードの時
・ARCHIVELOGモードのダイレクト・パス・ロードで、ロードする表がNOLOGGINGオプションを設定している時
・SQL*Loaderの制御ファイルにUNRECORVERABLE句を設定したとき
・ダイレクト・ロード・インサートでNOLOGGINGモードを使用したとき
・CREATE TABLE ... AS SELECT, CREATE INDEX, ALTER INDEX ... REBUILDはNOLOGGING属性に設定可能
ディスクI/Oを最小限化
・ディスク負荷をディスク・デバイスとコントローラーに分散
− データ・ファイルとREDOログファイルを別々のディスクに格納
− 表データのストライピング
OS機能によってストライピングを実施する際には、ストライプのサイズをDB_FILE_MULTIBLOCK_READ_COUNT×DB_BLOCK_SIZEの倍数にすると効果的
パラレル問い合わせによる全表操作がある場合、表領域データ・ファイルを別デバイスに分散することでも効果を発揮する
− OracleServerに無関係なディスクI/Oの低減
− ローデバイスの使用を検討
・可能な場合はローカル管理表領域を利用
以下の要素はなるべく別の表領域に作成する
・データ・ディクショナリ・オブジェクト
・ロールバック・セグメント及びUNDOセグメント
・一時セグメント
・表、索引
・非常に大きいオブジェクト
v$filestat,STATSPACK：各データ・ファイルのI/O回数、時間の統計を確認できる
> select phyrds, phywrts, d.name from v$datafiled, v$filestatf where d.file# = f.file# order by d.name;
全表走査の実行回数概要の調査
> select name, value from v$sysstat where name like '%tablescan%';~
(tablescanのlongtablesとshorttablesが全表走査に関連する情報)
| NAMEVALUE | |
| -------------------------------------------------------------------------- | |
| tablescans(shorttables) 496 | |
| tablescans(longtables) 42 ←大部分は索引のない表の走査 | |
| tablescans(rowidranges) 0 | |
| tablescans(cachepartitions) 0 | |
| tablescans(directread) 0 | |
| tablescanrowsgotten 290545 | |
| tablescanblocksgotten 18532 |
チューニング：DB_BLOCK_READ_COUNT初期化パラメーター（１回のI/O処理で読み込むデータブロックの最大数）を調整してI/Oコールの回数を減らす。ただしOSによる上限あり
動的パラメーターなので、セッションごとに設定可能。
select sid, serial#, opname, to_char(start_time,'HH24:MI:SS') STARTTIME, (sofar / totalwork)*100PERCENTAGE from v$session_longops;
状態の確認には以下のビューを確認
・v$logfile・v$log・v$log_history
・REDOとアーカイブは別々のディスクに配置
・アーカイブ・ログ・ファイルの確認 v$archive_log
・Oracle9iからはアーカイブプロセスを10個まで起動できる（不足時にLGWRが自動的に起動）
v$archive_processesにて状況を確認（DBWR_IO_SLAVEが１以上の時、ARCnプロセス数は自動的に４になる）
・アーカイブ速度の調整パラメーター
LOG_ARCHIVE_MAX_PROCESSES：ARCnの最初に起動する数を設定
LOG_ARCHVE_DEST_n：アーカイブファイル作成先を複数指定できる
・チェックポイントの頻度を上げることで生じる項目
− インスタンスリカバリの短縮
− スループットの低下
− DBWnとCKPTによるI/Oの増加
・多くのサイトでは３０分ごとの頻度に設定
・頻度の診断
− アラートファイルにて "Checkpoint not complete; unable to allocate file"などの警告が発生しないようにする
−V$SYSSTATにてbackgroundcheckpointsstarted/completedの値が１以上だとチェックポイントがログスイッチ間で完了していない
−LOG_CHECKPOINTS_TO_ALERT=TRUEを設定することで、アラートファイルにチェックポイントの開始終了を記録できる
−DBWncheckpointswriterequests統計は、チェックポイントの発生回数をカウント
・FASTSTARTチェックポイント
−Oracle9iEEではFAST_START_MTTR_TARGETを設定して、MTTR時間によるチェックポイント頻度を設定可能
過去のインスタンス障害時の結果をもとに、以下の初期化パラメータを自動調整
LOG_CHECKPOINT_INTERVAL,LOG_CHECKPOINT_TIMEOUT~
−v$instance_recoveryにてリカバリ時間の見積もりなど確認可能
・テープデバイスへの非同期I/Oが不可能なOSにて使用する（可能な場合は推奨しない）
・プロセスの命名規則はora_innn_SID
・DISK_ASYNCH_IO、TAPE_ASYNC_IOにてオン、オフを設定
・DB_WRITER_PROCESSESにて設定
・v$system_eventにてfree_buffer_waitsの値が大きいと判明したときにDBWnのプロセス数を増やすことを検討
select total_waits from v$system_event where event = 'free buffer waits';
・拡張時にディクショナリ・テーブルへのアクセスが頻繁に行われるため、パフォーマンスが低下する
・以上のデメリットを解消するには
−ローカル管理表領域にて自動セグメント管理を利用
（表領域内のビットマップにてデータブロック利用状況を管理）
create tablespace user_data_1 datafile '/oracle9i/oradata/db1/lm_1.dbf' size 100m extentmanagement local uniform size 2m;
−セグメントサイズの設定を適切に設定
−定期的に拡張されそうなセグメントを診断する
＜空き領域が１０％未満の表セグメントを表示＞
> select owner, ltable_name, blocks, empty_blocks from dba_tables where empty_blocks / (blocks + empty_blocks) <.1;~
＜エクステントの手動割り当て＞
> alter table hr.employees allocate extent;
・メリット
−動的拡張が起こりにくい
−パフォーマンスが若干向上
特にマルチブロックリードを効率的に行うために、エクステントサイズをDB_FILE_MULTIBLOCK_READ_COUN×５の倍数に設定
−１回のI/O操作でエクステント・マップ全体の読込
・デメリット
−空き領域が有効に利用できない
−未使用領域が生じやすい
・小さなブロックサイズのメリット・デメリット
OLTPのような表へのランダムアクセスが多く発生する場合に適している
・メリット
データブロックの競合減少
データ量の少ない行、もしくはランダムアクセスを行う表に最適
−デメリット
比較的ヘッダ部分が大きい
データ・ブロックあたりの行数が少ない
リーフ・ブロックの読込数が増える可能性あり
・大きいブロックサイズのメリット・デメリット
DSSのような大量データへの順次アクセスが多く発生する場合に適している
−メリット
ヘッダ部分が比較的小さい
順次アクセス、及びサイズの大きい行に最適
索引読込のパフォーマンスが向上
−デメリット
データブロックの競合が増大
データベース・バッファ・キャッシュで、より多くのメモリ領域を使用
・標準・非標準ブロックサイズの指定
DB_BLOCK_SIZE：標準ブロックサイズ
非標準ブロックサイズの指定：CREATE TABLESPACE の BLOCKSIZEオプションにて指定 2,4,8,16,32KBのうちで選択する
DB_nK_CACHE_SIZE：非標準ブロックサイズ用のキャッシュサイズ指定
・PCTFREE(Default10)
挿入の平均的な行サイズ(INS)から、更新によって増加する
平均的なデータ量(UPD)にて算出。更新がなければ０に設定する
PCTFREE = 100 * UPD / (UPD + INS)~
・PCTUSED(Default40)
行が頻繁に削除される場合に設定
PCTUSED = 100 - PCTFREE - 100 * (UPD + INS) / BlockSize
・行移行
UPDATEによってブロックに収まりきらなくなった行を、次のブロックへのポインタだけ残して空きのあるブロックに移行する
・行連鎖
一つのブロックにて収まりきらない行を、複数のブロックに納める
・行移行・行連鎖の検出
＜ANALYZEコマンドによる方法＞
> ANALYZE TABLE sales.order_hist compute statitics;~ > select num_rows, chain_cnt from dba_tables where table_name = 'ORDER_HIST';~
＜STATSPACKによる方法＞
Instace Activity Stats for DBセクションの table fetch continued row統計
・移行している行の解消
−エクスポート・インポート
ALTER TABLE ... MOVEコマンドによる再構築
−移行行の再挿入
> @?/rdbms/admin/utlchain.sql accept table_name prompt analyze table &table_name list chainedrows;
> create table temp as select * from &table_name where rowid in (select head_rowid from chained_rows);
> delete from &table_name where rowid in (select head_rowid from chainedrows);
> insert into &table_name select * from temp;
> drop table temp;
・利用済みブロックの最高水位標をセグメント・ヘッダ・ブロックに記録
・作成時に、セグメントの先頭に設定される
・行挿入時に５ブロック単位で増分される
・Truncate文でリセットされるが、DELETE文ではリセットされない
・最高水位標より下に利用されていないブロックがあると、パフォーマンスが低下する場合がある。
・最高水位標の位置の確認
＜ANALYZEによる方法＞
> ANALYZE TABLE hr.employees COMPUTE STATISTICS;~ > SELECT num_rows, blocks, empty_blocks, empty, avgspace, chain_cnt, avg_row_len FROM dba_tables WHERE owner = 'HR' AND table_name = 'EMPLOYEES';~
BLOCKS／EMPTY_BLOCKS：最高水位標より下／上にあるブロック数
AVG_SPACE：最高水位標以下にあるデータ・ブロック内の平均空きバイト数
＜DBMS_SPACEパッケージによる方法（統計情報が更新されない）＞
...省略 ...
・エクスポート・インポートの実施
・ALTER TABLE ... MOVEによる表の再構築
・ALTER TABLE ... DEALLOCATE UNUSEDで最高水位標より後のエクステントの解除
・索引ブロックではエントリーはキー順に並べられるため、変更処理の多いテーブルの索引は定期的な再構築が必要となる
・変更が多い標の索引がパフォーマンス上問題となりうる
・完全に空になった索引ブロックのみ空きリストに登録される
・索引領域の診断
＜無効になった索引エントリの割合を確認＞
> analyze index emp_name_ix validate structure;~ > select name,(del_lf_rows_len / lf_rows_len) * 100wastage from index_stats;~
LF_ROWS_LEN：リーフ・ブロックにある削除されたエントリに数
・索引の再構築
再構築 ALTER INDEX ... REBUILD(索引を再作成する） オプション：ONLINE
PARALLEL / NOPARALLEL
RECOVERABLE / UNRECOVERABLE
LOGGING/NOLOGGING
結合 ALTER INDEX ... COALESCE
（同一ブランチ内のリーフブロックを結合する。低コスト）
・未使用索引の検出
> alter index hr.emp_name_ix monitoring usage;~#索引の使用状況の監視を終了&br;
> alter index hr.emp_name_ix monitoring usage;~#索引の使用状況を検索&br;
> select index_name,used from v$object_usage;
・CREATE INDEX
・SELECT .... ORDER BY
・SELECT DISTINCT
・SELECT .... GROUP BY
・SELECT ... CONNECT BY
・SELECT ... CONNECT BY ROLLUP
・ソート・マージ結合
・ANALYZEコマンド
メモリソート：ソート領域内にてソート用件が収まる場合
ディスクソート：ソート領域内にてソート用件が収まらず、一時セグメントを利用する場合、データはソートランと呼ばれる小さい断片に分割されて、一部をソート領域にてソート、他を中間結果として一時セグメントに納める
・専用サーバ構成の場合はPGA、共有サーバ構成の場合は共有プールに確保
・SORT_AREA_SIZE：サイズ指定（セッション単位で動的に編集可能。4MB以上にしてもパフォーマンスはあがらない）
・SORT_AREA_RETAINED_SIZE：ソートが完了し、フェッチが終了していない時点で縮小できるソート領域サイズ
(Oracle9iからの自動パラメータ）
・PGA_AGGREGATE_TARGET：すべてのサーバ・プロセス使用できるメモリの上限値
・WORKAREA_SIZE_POLICY：AUTOにすると、PGA管理を自動化
MANUALでは、8i以前のパラメータを使用して管理する
・実行計画には、複数のソートを含めることが可能
・単一サーバープロセスに必要なソート領域
アクティブなソート用にSORT_AREA_SIZE（バイト）の領域 ＋ 結合ソート用に最低一つのSORT_AREA_RETAINED_SIZEの領域
・パラレル・問い合わせに必要なソート領域
SORT_AREA_SIZE*2*並列度
SORT_AREA_RETAINED_SIZE*並列度*2以上
ソート処理をなるべく回避する
・索引作成時にNOSORTを使用
・検索時にUNIONの代わりにUNIONALLを使用
・表結合に索引アクセスを使用すれば、ソート・マージ結合の代わりにネスト・ループ結合を選択する
・ORDER BY句で参照される列に索引を作成 ＋ NOT NULL
・ANALYZE ... FOR COLUMNS を使用して分析
・サイズの大きいオブジェクトの ANALYZE には、 COMPUTE ではなく ESTIMATE を使用
ソートがメモリ内で行われるようにして、スワッピング／ページングを低減
一時領域を適切に割り当てて、領域割り当てコールを低減
v$sysstat：sorts(disk) 一時セグメントへのI/Oが必要なソート数
sorts(memory) メモリー内で完全に実行されたソート数
sorts(rows)ソート処理の対象となった行数
メモリー・ソートに対するディスク・ソートの比率は、５％未満が目安
超える場合はsort_area_sizeを増やす
・１インスタンスに１一時領域
・ソート処理時にセグメントがつくられ、終了時に削除される
・SGA内のソート・エクステント・プール(SEP)にて管理される
・各一時セグメントに関する情報
> select tablespace_name, current-users, total_extents from v$sort_segment;~
CURRENT_USERS:アクティブユーザーの数
TOTAL_EXTENTS:エクステントの総数
USED_EXTENTS:現在ソートに割り当てられているエクステント
EXTENT_HITS:未使用エクステントがプール内に見つかった回数
MAX_USED_BLOCKS:使用済みデータブロックの最大数
MAX_SORT_BLOCKS:個々のソートで使用されたデータ・ブロックの最大数
・ユーザーが必要とするソート領域の定義と、アクティブなディスクソートに関する情報の取得
> select username, tablespace, contents, extents, blocks from v$sort_usage;~
・一時表領域は複数のディスクにストライピングされることが望まれる
・一時ファイルに関する情報の取得 v$tempfile と dba_temp_files
・トランザクションのロールバック
・トランザクションのリカバリ
・読みとり一貫性
トランザクションはロールバック・セグメントの各エクステントに循環的にレコードを書き込む
ロールバック・セグメントへの書込のためには、ロールバックセグメント自体がDBBCにキャッシュされている必要がある
ロールバック・セグメントのヘッダー部分に、書き込まれたトランザクションの情報を書き込む
ヘッダー部分は頻繁に更新されるため、ロールバック・セグメントの数が少ないとパフォーマンスが低下し、エラーが発生する場合もある
自動UNDO管理ではロールバック・セグメントの数もある程度自動的に管理される現在のエクステントが満杯になって次のエクステントに移る際、次のエクステントがアクティブ・エクステント（書き込んだトランザクションがまだコミットされていない状態）であった場合、新規エクステントを追加して書き込む
エクステントの拡張によりパフォーマンスが低下するため、セグメントの動的拡張は出来る限り避ける
・作成
> create rollbacksegment rbs01 tablespace rbs storage ( initial 100k next 100k minextens 20 maxextents 100 optimal 2000k);~
・オンライン
> alter rollbacksegment rbs01 online;
読みとり専用：set transaction read only;
設定以降の読みとり一貫性が保証されるが、DMLは発行できない
直列可能：
set transaction isolation level serializable;
設定以降の読みとり一貫性が保証されるが、設定以降に他のトランザクションによって変更された行に対して変更しようとするとORA-8117にて中止される
上記二つのトランザクションではロールバック・セグメントは長い間保持されている必要があり、大きめのロールバックセグメントを設定しておく必要がある
ロールバック・セグメントへのアクセス待機をさせない
ロールバック・セグメントの拡張を出来る限りさせない
ロールバック実行回数を低減する
トランザクションで十分なロールバックセグメント領域を確保する
読みとり側が、常に必要な読みとり一貫性イメージを参照出来るようにする
V$ROLLNAME：オンライン・ロールバック・セグメントのNAMEと番号を表示
v$rollstat：各オンライン・ロールバック・セグメントの統計を表示
− ヘッダー・トランザクション表の待機の回数
− トランザクションで書き込まれたデータの量
v$system_event の undosegmenttxslot 統計：トランザクション表への待機と、ロールバックセグメントの待機の累積統計を表示
v$waitstat：すべてのロールバック・セグメントのヘッダー・ブロックとデータ・ブロックの待機の累積統計を表示
v$transaction：ロールバック・セグメントを使用する現行のトランザクションと必要な領域の量を表示
> select sum(waits) / sum(gets)*100 "Ratio(%)", sum(waits) "Waits", sum(gets) "Gets" from v$rollstat;~
セグメントヘッダの待機数は、総要求数の１％未満が目安
１％以上の場合は、ロールバックセグメントの数を増やす
・OLTPの場合は、大体４トランザクションに一つのＲＢＳを割り当てる
・大きめのトランザクションを流す場合は、MAXEXTENTSをUNLIMITEDにした大きめのRBSを１トランザクションに一つ割り当てる
＜方法＞
> SET TRANSACTION USE ROLLBACKSEGMENT large_rbs;~
または
> execute dbms_transaction.use_rollback_segment ('large_rbs');~
または、作り込んであるトランザクションには小さなRBSをOFFLINEにしてしまう
INITIAL/NEXTエクステントは同じサイズにして、小さいトランザクションの場合は8KB,16KB,32KB,64KB、大きいトランザクションの場合は128KB,156KB,512KB,1MB,2MB,4MBを目安にして設定
すべてのRBSのサイズを同じにして、大きいRBSは必要ないときはofflineにしておく
MINEXTENTSは２０に設定する
表領域は大きいトランザクションがロールバック・セグメントを拡張できるように備えて十分空き領域を確保しておく
削除：行データ全体を格納するので大量に使用
挿入：RowIDのみ格納
更新：変更する列数とそのデータ量に依存
索引：索引付きの表・列への操作ではデータ量が増加する
見積もりにはもっとも長いトランザクションを実行した後のRBSサイズをチェックするか、
> select usn,write from v$rollstat;~
現行トランザクションであった場合には、以下でチェックする
> select s.username, t.used_ublk, t.start_time from v$transaction, v$sessions where t.addr=s.taddr;
・ユーザーが定期的にトランザクションをコミットできるよう設計
・長いトランザクションを避けてコーディング
・エクスポート・インポート操作
インポート：COMMIT=Yに設定。行のセットのサイズをBUFFERキーワードでセット
エクスポート：CONSISTENT=Nに設定（但し読みとり一貫性を失う）
・ロールバック領域不足によるトランザクションの失敗
RBSがエクステントの最大数に到達するか、表領域が満杯になる
解決策：RBSを大きくするか、表領域のサイズを増やす
・ORA-1555 読みとり一貫性にたいするエラー
読みとり一貫性に必要なRBSが上書きされた
解決策：ロールバック・セグメントを増やすか、さらに大きなロールバックセグメントを用意する
バッチ処理のような大きなトランザクションを日中に実行しない
100%の回避は不可能
・設定
UNDO_MANGEMENT
AUTO 自動UNDO管理
MANUAL ロールバック・セグメントの手動管理
UNDO_TABLESPACE：UNDO表領域の名前を指定（動的） 使用可能なUNDO表領域がない場合、SYSTEM 表領域の RBS を使用する
UNDO_SUPPRESS_ERRORS：エラーORA-30019を抑制する
UNDO_RETENTION：UNDO情報の保持期間（秒数）を指定
(UNDO_RETENTION*１秒間あたりのUNDOブロック数+オーバーヘッド)*DB_BLOCK_SIZE
問い合わせによる計算
> select ( retention * undoblock) * datablock) from ( select value "RETENTION" from v$parameter where name = 'undo_retion'), ( select (sum(undoblks) / sum(((end_time-begin_time)*86400))) "UNDOBLOCK" from v$undostat),( select value "DATABLOCK" from v$parameter where name = 'db_block_size');
CREATE DATABASE コマンドにて UNDOTABLESPACE句を指定
指定しなかった場合、SYS_UNDOTBSという名前のUNDO表領域が作成される
datafile '$ORACLE_HOME/dbs/DBU1<SID>.dbf' AUTOEXTEND ON CREATE UNDOTABLESPACEコマンドを使用して作成
UNDOセグメント以外のオブジェクトを作成不可
データ・ファイルのパスとextent_management句のみ指定可能
MINIMUM EXTENTとDEFAULT STORAGEの値は、OracleServerによって自動生成
１０分ごとの自動UNDOの領域割り当てと使用状況
（手動UNDO管理時にも使用可能）
> select undoblks, maxquerylen, maxconcurrency from v$undostat;
・自動管理
・高レベルのデータ同時実行性を提供
ー DML文においては行レベルでのロック
ー 問い合わせではロック不要
・様々なレベルのデータ整合性を提供
・排他および共有ロックモードを提供
・ロックの期間はロック発行からコミットorロールバックまで有効
２種類のDML(INSERT,UPDATE,DELETE,SELECT...FORUPDATE)ロック
・表レベルの共有ロック(TMロック)
変更対象行のある表に対してかけられる
トランザクションと競合する可能性のあるDDL操作を禁止する
ー行排他(RX)：INSERT,UPDATE,DELETE
他のトランザクションの同じ表内の他の行の問合／挿入／更新／削除／ロックを実行できる
他のトランザクションは、手動で表ロックして排他的に読み書きできなくなる
ー行共有(RS)：SELECT...FORUPDATE
他のトランザクションは、手動で表ロックして排他的に読み書きできなくなる
・行レベルの排他ロック(TXロック)
変更対象行に対してかけられる
別のトランザクションによる行の変更を禁止する
手動による排他(X)表ロック
LOCKTABLEtable_nameINmode_nameMODE;
mode_name：EXCLUSIVE排他ロック 表の問い合わせのみ実行可能
参照整合性制約による暗黙ロック（Oracle8i以前）
・共有(S)ロック
ー参照整合性制約によりRXロックのかかった表の子表に暗黙的にSロックが取得される場合がある。
ー回避策は、子表の外部キーに索引を作成すること。
ーDML操作の実行が出来ない
・共有行排他(SRX)ロック
ー参照整合性制約によりRXロックのかかった表の子表に暗黙的にSロックが取得される場合がある。
ー回避策は、子表の外部キーに索引を作成すること。
ーDML操作または共有ロックの取得が出来ない
・排他DDLロック
ーDROP TABLE,ALTER TABLEコマンドを発行したときに取得
ー対象表に別のセッションによる何らかのロックが保持されている場合、
取得できずコマンドはエラーとなる
ーディクショナリ管理ではなく、
ローカル管理を利用するとデータ・ディクショナリへの更新がなくなるため、
領域トランザクション・ロックの競合がなくなる
・共有DDLロック
ーGRANT / CREATE PACKAGEなどの文では参照先オブジェクトの共有DDLロックを取得
ー別のセッションによる変更または削除を禁止
・ブレーク可能解析ロック
ーライブラリ・キャッシュ内の文またはPL/SQLが参照先オブジェクトが
変更されたときに無効になるかどうかチェックするために保持する
ー待機や競合は発生しない
・不必要に高いロック・レベル
・長時間実行のトランザクション
・コミットされていない変更
・より高いロックレベルを必要とするサード・パーティーの製品
v$lock：すべてのロック
sidが５以下のものは、BGDジョブ
他のトランザクションの待機が発生しているとき、REQUEST>=1
ID1,2はオブジェクト番号
v$lock_object：オブジェクトに対するロック
・ロックを保持しているユーザーにコミットorロールバックを要請
・セッションの強制終了
select sid, serial#, username from v$session sharetype='USER';~ alter system kill session '10,23'; sid,serial#
・デッドロックが発生した場合には、原因となるトランザクションの片方だけロールバックされる
・デッドロック発生時には、ユーザートレースファイルに記録される
・STATSPACKのSQLレポート
・EXPLAINPLANコマンド
PLAN_TABLE表を作成
@?/rdbms/admin/utlxplan.sql~
EXPLAINPLAN表を実行
EXPLAINPLAN FOR SELECTlast_namefromhr.employees;~
実行計画を表示
PLAN_TABLE表への問い合わせ
utlxpls.sqlスクリプトの実行
utlxplp.sqlスクリプトの実行（パラレル問い合わせ表示）
・SQLTRACE及びTKPROFユーティリティ
初期化パラメータUSER_DUMP_DEST,MAX_DUMP_FILE_SIZE,TIMED_STATISTICSを適切に設定
SQL_TRACEをTRUEに設定
alter system session set sql_trace=TRUE;~ exec dbms_session.set_sql_trace(TRUE);~ exec dbms_system.set_sql_trace_in_session(session_id,serial_id,TRUE);~
調査対象のアプリケーションを実行
SQL_TRACEをFALSEに設定、もしくはセッションを開始
TKPROFでユーザー・トレース・ファイルを変換して検証
$tkproftracefile.trcoutput.txt[options]
例：$tkproftracefile.trcoutput.txtSORT=exeelaEXPLAIN=oracle/oracle
SYS=no
PLAN_TABLE表の作成
PLUSTRACEロールの作成及び付与
@?/sqlplus/admin/plustrce.sql GRANT plustrace TO scott;
AUTOTRACE機能の有効化
set autotrace [ off | on | traceonly ] [ explain | statistics ]
ON：実行計画、統計情報、SQL結果のすべてを表示
TRACEONLY：実行計画、統計情報は表示するが、SQL結果は表示せず
EXPLAIN：実行計画のみ表示
ルール・ベース
ー構文とデータ・ディクショナリ情報をあわせてアクセスパスを選択
ー構文／データ・ディクショナリ駆動型
コスト・ベース
ー想定されるアクセスパスを検査し、もっともコストの低いアクセス・パスを選択
ー統計駆動型
インスタンスレベルでの設定
OPTIMIZER_MODE={CHOOSE|RULE|FIRST_ROWS|ALL_ROWS}
セッションレベルでの設定
ALTER SESSION SET OPTIMIZER_MODE={CHOOSE|RULE|FIRST_ROWS|ALL_ROWS}
文レベルでの設定（ヒントを使用）
create index gen_idx on customers(cust_gender); select /*+INDEX(customersgen_idx)*/ cust_last_name, cust_street_address, cust_postal_code from sh.customers where cust_gender='M'
ANALYZEコマンドを使用して統計を収集／削除
ANALYZE {INDEX|TABLE|CLUSTER} object_name {COMPUTE|DELETE|ESTIMATE}STATISTICS[FOR...[SIZEn]] [SAMPLEn{ROWS|PERCENT}];
COMPUTER：表全体をみて、正確な統計を作成
ESTIMATE：表の一部から統計を概算
DELETE：統計の消去
FORTABLE：表と列ではなく、表のみの統計を収集
FORCOLUMNS：指定された列のみ
FORALLCOLUMNS：すべての列の統計を収集
FORALLINDEXEDCOLUMNS：すべての索引付きの列
FORALL[LOCAL]INDEXES：表に関連づけられたすべての索引
SIZE：ヒストグラム・パケットの最大数を指定
行数
データブロック及び空データブロックの数
利用可能な空き領域の平均
連鎖行または移行行の数
行長さの平均
ANALYZEの最終日付及びサンプルのサイズ
データ・ディクショナリ・ビュー：DBA_TABLES
索引レベル（高さ）
リーフ・ブロックと個別キーの数
キーあたりの平均リーフ・ブロック数
キーあたりの平均データ・ブロック数
索引エントリ数
クラスタ化係数
データ・ディクショナリ・ビュー：DBA_INDEXES
個別値の数
最低値、最高値（バイナリ形式で格納）
最終ANALYZE日とサンプルのサイズ
データ・ディクショナリ・ビュー：USER_TAB_COL_STATISTIヒストグラム
１．統計を保持する表を作成
exec DBMS_STATS.CREATE_STAT_TABLE('HR','STATS');
２．統計を表にコピー
execDBMS_STATS.EXPORT_SCHEMA_STATS('HR','STATS');
（他に、EXPORT_COLUMN_STATS,EXPORT_INDEX_STATSなども利用可能）
３．STATS表をコピー先のデータベースへエクスポート／インポート
４．統計をデータ・ディクショナリにコピー
execDBMS_STATS.IMPORT_SCHEMA_STATS('HR','STATS');
実行計画を固定
異なるリリース間(Oracle8i〜）の実行計画を利用可能
OPTIMIZER_FEATURES_ENABLEを設定する事で、旧バージョンの実行計画を
利用できるようになる
＜複数のSQL文についてのストアド・アウトラインを作成＞
ALTER SESSION SET CREATE_STORED_OUTLINES=outln_educ; SELECT ... FROM...; SELECT ... FROM...; ALTER SESSION SET CREATE_STORED_OUTLINES=FALSE;
＜一つの文についてのストアド・アウトラインを作成＞
CREATE OR REPLACE OUTLINE co_cl_join FOR CATEGORY outln_educ ON~ SELECT co.crs_id,.. FROM ...;
ALTER SESSION SET USED_STORED_OUTLINES = outln_educ;~
(インスタンス・レベルでも設定可能）
アウトラインを手入力にて編集する
編集中のアウトラインは、現在のセッションにおいてのみ有効になっている
利用手順：アウトラインを実行できるユーザで接続。以下の権限を確認する
CREATEANYOUTLINEシステム権限
SELECT_ANY_CATALOGロール
DBMS_OUTLN_EDITプロシージャのEXECUTE権限
アウトライン表をスキーマ内に作成
exec DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES;
CREATEPRIVATEOUTLINEコマンドを利用して、アウトラインをアウトライン表にコピーし、プライベート・アウトラインを作成する
CREATEPRIVATEOUTLINEp_dev01FROMdev01;
OEMのOutlineEditoryやOL$HINTS表によってアウトラインを編集。表の結合順序を変更するには
DBMS_OUTLN_EDIT.CHANGE_JOIN_POSを使用
編集内容のテストのため、USE_PRIVATE_OUTLINES=TRUEを設定してアウトライン分を発行するか、文に対してEXPLAIN
PLANを実行、保存、公開
CREATE OR REPLACE OUTLINE dev01 FROM PRIVATE p_dev01;
OUTLN_PKGパッケージ
DROP_UNUSED：１度も使用されたことのないアウトラインの削除
DROP_BY_CAT：特定のカテゴリに割り当てられたアウトラインの削除
UPDATE_BY_CAT：１つのカテゴリから別のカテゴリへのアウトラインの再割当て
ALTER/DROPOUTLINEコマンド
アウトラインの名前の変更
アウトラインの再構築
アウトラインのカテゴリの変更
アウトラインはOUTLNスキーマに格納されているので、これをエクスポート／インポートする事で実行計画のエクスポート／インポートが可能 OUTLNは自動的に作成されるので、パスワード要変更
OL$：アウトライン名、カテゴリ、作成タイムスタンプ、文のテキスト
OL$HINTS：OL$のアウトラインのヒント
表の行の5%未満にたいして問い合わせが頻繁に実行される表示作成する
リーフブロックがいっぱいになった時は、２つのリーフブロックに分割して、50%づつにデータを分ける。階層が増えたり、多くの行が削除されると、パフォーマンスが低下するー 索引のキー圧縮
COMPRESSオプションにより、索引サイズの圧縮が可能。若干のパーフォーマンス悪化の可能性あり
カーディナリティの低い列、AND,OR条件での検索を使用する列で有効。NULL値を索引に格納する。記憶領域はBツリーより少なくてすむ。DMLの発行時にパフォーマンス悪化
キーのバイトを反転して、索引の偏りをさける。ただし範囲検索に利用できない
主キーでのアクセス以外パフォーマンスが落ちる。２次索引を保持可(8i〜）
敷居値(PCTTHRESHOLD句で指定)を越えるデータや、指定したデータはOVERFLOW句で指定するオーバーフローセグメントに保持可
ほとんど利用されていない
表の結合でディスクI/Oが減り、記憶領域を削減する。全表検索ではパフォーマンスが落ちる
同じクラスター・キー値を持つデータが、一つのデータブロックに収まらない場合にはパフォーマンスが落ちる
索引・クラスタとハッシュ・クラスタ
ビューの問い合わせ結果をキャッシュしておく
マテリアライズド・ビューの作成（要QUERY_REWRITE権限）
CREATE MATERIALIZEDVIEW dept_sal_sum TABLESPACE dept_ts [ BUILD IMMEDIATE / REFRESH COMPLETE ON DEMAND / ENABLE QUERY REWRITE ] AS SELECT...;
完全リフレッシュ：再度問い合わせを実行
高速(FAST)リフレッシュ：リフレッシュ以降の実表の変更部分に対してのみ適用。実表の変更記録をマテリアライズド・ビュー・ログに記録しておく方法と、新規行ROWIDに基づいたダイレクトパスロード後の高速リフレッシュを利用する方法がある
コミット時(ONCOMMIT)：実表に対する更新がコミットされた時点
指定時間（STARTWITH...NEXT）：指定時間にリフレッシュ
手動リフレッシュ：DBMS_MVIEWパッケージを利用して管理
特定のマテリアライズド・ビューをリフレッシュ
DBMS_MVIEW.REFRESH('CUST_SALES',parallelism=>10);
１つ以上の実表に基づいてリフレッシュ
DBMS_MVIEW.REFRESH_DEPENDENT('SALES');
リフレッシュが必要なすべてのマテリアライズド・ビューをリフレッシュ
DBMS_MVIEW.REFRESH_ALL_MVIEWS;
実行計画の時点で、実表の代わりにマテリアライズド・ビューを利用することが可能
初期化パラメーターQUERY_REWRITE_ENABLEDをTRUEに設定
マテリアライズド・ビューにENABLEQUERYREWRITE句を設定
クエリー・リライトの制御
OPTIMIZER_MODE：クエリー・リライトはコストベースの場合のみ有効
QUERY_REWRITE_ENABLED（動的）：FALSEに指定することで、抑制できる
QUERY_REWRITE_INTEGRITY（動的）
ENFORCED(DEFAULT)：データの一貫性が保証される場合のみリライトする
TRUSTED：強制されていない関係に基づいたリライトを許可
STALE_TOLERATED：DML操作が最後に宣言されて以降、リフレッシュされていないマテリアライズド・ビューを使用したリライトを許可
NO REWRITE：クエリー・リライトを禁止する
ユーザーのグループ。リソース割り当ての管理単位となる
ユーザーは複数グループに所属できるが、１時期に有効に出来るのは１つだけ
リソース・コンシューマー・グループへのリソース割り当て名
グループとプランの関連づけ
CPUレベル：ジョブの優先度(p8まで)
配分：同CPUレベル内でのCPU配分率
管理者にリソースマネージャーのシステム権限を割り当て
exec DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE ('SCOTT','ADMINISTER_RESOURCE_MANAGER',FALSE);~
１つめのパラメータは管理者名、３つめはADMINOPTIONの負荷です
・ペンディング・エリアを作成 （リソース・マネージャーの変更内容は一旦このエリアに保存される）
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();~
・コンシューマー・グループを作成
exec DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP ('OLTP','OnlineUser');~
・リソースプランを作成
exec DBMS_RESOURCE_MANAGER.CREATE_PLAN ('NIGHT','DSS/Batchpriority,...');~
・リソースプラン・ディレクティブを作成
exec DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ('NIGHT','OLTP','...',100,20);~
・ペンディング・エリアを検証
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();~
・ペンディング・エリアをコミット
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();~
・ユーザーをグループに割り当て
exec DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('MOIRA','OLTP',FALSE);~
・ユーザーの初期化コンシューマーグループを設定
exec DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUmER_GROUP ('MOIRA','OLTP');~
・リソースプランの決定
初期化パラメータRESOURCE_MANGER_PLANにて設定する 動的に変更可能
・コンシューマー・グループの切り替え
exec DBMS_SESSION.SWITCH_CURREENT_GROUP ('DSS',v_old_group,FALSE);~
・管理者によるコンシューマー・グループの切り替え
exec DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS(7,13,'OLTP');
exec DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_USER('MOIRA','OLTP');
データベース・リソース・マネージャの情報管理
アイドル時間のために、サーバープロセスが十分に活用されていない場合に利用
サーバー・プロセスを共有可能
OracleNetをサポートする
同時接続ユーザー数の増加
ある程度のオーバーヘッドが発生する
構成 ディスパッチャ、要求キュー、応答キュー、共有サーバプロセスにて構成
PGAにはスタック領域、共有プールにはUGAを格納
V$SHARED_SERVER_MONITOR：最大接続数、最大セッション数、セッションの現在の使用状況を確認
V$DISPATCHER：指定したディスパッチャ・プロセスの使用状況確認
select network "Protocol",status "Status", SUM(owned) "Clients", sum(busy)*100/(sum(busy)+sum(idle)) "BusyRate" from v$dispatcher group by network;
select decode(sum(totalq),0,'NoResponses',sum(wait)/sum(totalq)) "Average wait time" from v$queueq,v$dispatcherd where q.type='Dispatcher'and q.paddr=d.paddr;
V$DESIPATCHER_RATE：競合の解析。CURの値がMAXの値に近い場合は、ディスパッチャの追加を検討する。CURの値が逆に低い場合は削減を検討する。(ALTER
SYSTEMSETDISPATCHERSにて動的に変更可能）
共有サーバ数はBusy時はMAX_SHARED_SERVERSに到達するまで、徐々にPMONによって動的に起動
idle時はSHARED_SERVERS（動的パラメータ）になるまで削除される
select name,requests,status,busy*100/busy+idle)"BUSY%" from v$shared_server where status != 'QUIT';
select decode(totalq,0,'NoRequests',wait/totalq||'hundredthsofseconds') from v$queue where type='COMMON';~
v$circuitビュー：サーバー・アドレス、ディスパッチャ・アドレス、ユーザー・セッション・アドレスの確認
V$SHARED_SERVER：共有サーバープロセスに関する情報
リスナーが実行されていない
共有サーバーの初期化パラメーターの設定が適切でない
ディスパッチャ・プロセスが強制終了されている
SYSDBAユーザーとして接続しようとした
PROCESSESパラメーターの値が小さすぎる
OSが対応していればLOCK_SGA初期化パラメータにより、SGAを実メモリ上にロックする
ディスクとディスクコントローラー間の負荷の均衡化
ロー・デバイスの使用
ガイドライン
最大CPU使用率：９０％
最大ＯＳ／アプリ処理比：４０／６０
ＣＰＵ負荷は各ＣＰＵで均衡化
高スループットの挿入／更新集約型
大容量のデータが継続的に増加
多数のユーザーが同時アクセスを実行
チューニングの目標 可用性、スピード、同時実行性、リカバリ能力
要件 エクステントの明示的な割り当てにより、動的な割り当てによる負荷をさける
索引の数を押さえて、更新時の負荷をさける
挿入に使用する順次列に逆キー索引を使用して、Bツリーブロックが頻繁に分割するのをさける
索引は定期的に再構築を実施
マテリアライズド・ビュー、索引構成表の利用の検討
考慮事項 コードの共有化 CURSOR_SHARINGパラメータを使用して解析済みコードの共有化
大量のデータに対して問い合わせを実行 全表走査を多用
チューニングの目標：迅速な応答、正確性
パラレル問い合わせ機能は、データウェアハウス環境を対象にしている
要件
DB_BLOCK_SIZEが大きければ集中的な読み込み操作が高速に処理される
DB_FILE_MULTI_READ_COUNTは値を大きくすれば、表走査の見積もりコストが低くなる
エクステントサイズをこれらのパラメーター値の倍数に設定
ANALYZEまたはDBMS_STATSを定期的に実行
可能であればビットマップ索引を使用
主キー検索には索引構成表を使用
不均一な索引付き列にはヒストグラムを生成
ハッシュクラスタの使用を検討
考慮事項
解析時間は重要ではない
実行計画の最適化が目標
パラレル問い合わせの使用
ヒントの使用
問い合わせのロジックにPL/SQLの使用検討
バインド変数は問題発生の原因となりうる（ヒストグラムが使用されないなど）
