OracleDBA(パフォーマンスチューニング（SQL編) - コピー

２．ファイル管理 ①　モニター方法

領　　域

対　　　象

方　法　及び　項 　目

V$CONTOROLFILE 格納場所、ブロックサイズ V$CONTROL_RECORD_SECTIO N 制御タイプ、レコードサイズ、etc データファイル V$DATAFILE データファイルの物理的な構造情報 演繹的： （最もトランザクションが激しい時間帯で発生した REDOログの量）＊（その時間）／（ログ・スイッチの 時間間隔） 帰納法的： 右記SQLの実行前と後の差の平均。 V$LOGFILE REDOログファイルの場所、物理的な構成情報 V$LOG ロググループ＆ログメンバーのログ使用情報、ログ ファイルサイズ アラートログ　（拡張子　.ARC) 初期化パラメータ　LOG_CHECKPOINTS_TO_ALERT ＝TRUE に設定してアラートログに開始時間、終了 時間を出力する。 チェックポイント未完 了 V$SESSION_EVENT log file switch checkpoint incomplete

アーカイブ未完了

V$SESSION_EVENT

log file switch archiving needed

ログ消去

V$SESSION_EVENT log file switch clearing log file ディスク競合等の

上記以外

V$SESSION_EVENT log file switch completion

発生回数 V$SYSSTAT DBWR checkpoint write requests チェックポイント要求回数 開始回数 V$SYSSTAT

background checkpoints started

チェックポイント発生回数 完了回数 V$SYSSTAT background checkpoints completed チェックポイント完結回数 V$DATABASE log_mode ログモードの確認： ARCHIVEモードか？NONARCHIVEか？ パラメータ (V$PARAMETER) log_archive_start アーカイブが自動か否か ARCHIVEコマンド アーカイブ先、ログの順序 V$ARCHIVED_LOG アーカイブログのブロックサイズ等 ファイルサイズ V＄SYSSTAT

select STATISTIC#, NAME, CLASS, VALUE, STAT_ID from V_$SYSSTAT where name = 'redo size';

select EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, MAX_WAIT from V_$SESSION_EVENT log file switch completion'; ログスイッチ発生状況

イヴェント名、待機回数、タイムアウト回数、待機時 間、最大待機時間

select EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, MAX_WAIT from V_$SESSION_EVENT WHERE EVENT = 'log file switch checkpoint incomplete' ;

select GROUP#, STATUS, TYPE, MEMBER, IS_RECOVERY_DEST_FILE from V_$LOGFILE;

select GROUP#, BYTES, ARCHIVED, STATUS, FIRST_TIME from V_$LOG;

select EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, MAX_WAIT from V_$SESSION_EVENT WHERE EVENT = 'log file switch checkpoint incomplete' ;

select STATISTIC#, NAME, CLASS, VALUE, STAT_ID　from V_$SYSSTAT where name = 'DBWR checkpoint write requests';

select STATISTIC#, NAME, CLASS, VALUE, STAT_ID　from V_$SYSSTAT where name = 'background checkpoints started';

ARCHIVE LOG LIST;

select NAME, BLOCKS, BLOCK_SIZE from V_$ARCHIVED_LOG; アーカイブログモード

SELECT LOG_MODE FROM V$DATABASE; 制御ファイル

内　　　容

アーカイブロギング　自動or手動 アーカイブログ チェックポイン ト頻度 ログスイッチ 待機状況 物理構成情報 データベース物理構成情報 ディクショナリ＆ユーザ情報 REDOログ ファイル

モニター例

select STATUS, NAME, BLOCK_SIZE, FILE_SIZE_BLKS from V_$CONTROLFILE;

select TYPE, RECORD_SIZE, RECORDS_TOTAL, RECORDS_USED from V_$CONTROLFILE_RECORD_SECTION;

select FILE#, BYTES, BLOCKS, CREATE_BYTES, BLOCK_SIZE, NAME from V_$DATAFILE;

アーカイブログ構成情報

select STATISTIC#, NAME, CLASS, VALUE, STAT_ID　from V_$SYSSTAT where name = 'background checkpoints completed';

select EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, MAX_WAIT from V_$SESSION_EVENT WHERE EVENT = 'log file switch checkpoint incomplete' ;

②　メンテナンス方法 ※　コマンド、SQLの詳細な使用方法については割愛する。

領　　域

対　象

方　法

制御ファイル 拡張子.ｃｔｌファイル ALTER SYSTEM 文で拡張子.ｃｔｌ　ファイルをメンテ ナンスする。 ALTER DATABASE 文で拡張子.ｄｂｆ　ファイルのサ イズを変更する。

ALTER TABLESPACE 文でDATAFILEのサイズを変 更する。 ALTER DATABASE 文で拡張子.ｄｂｆ AUTOEXTEND を指定する。 初期化パラメータ DB_WRITER_PROCESSES ALTER SYSTEM 文で起動するDBWRプロセス数を 設定する。 デフォルト　１　値の範囲　１～２０ 初期化パラメータ FAST_START_MTTR_TARGET チェックポイントの発生頻度を、データベースがリカ バリに必要な最長の所要時間（秒数）で指定する。 設定範囲　0～3602秒 ※本パラメータを設定すると以下のパラメータ LOG_CHECKPOINT_INTERVAL　は設定出来ない。 初期化パラメータ LOG_CHECKPOINT_INTERVAL チェックポイントの発生頻度を、直前のチェックポイ ントからのLGWR発生までのブロック（ブロック数）で 指定する。 デフォルト　１ブロック 初期化パラメータ LOG_CHECKPOINT_TIMEOUT 補足資料➀　図２参照 チェックポイント発生頻度を、直前のチェックポイント から経過時間を秒数でしていする。 デフォルト　１８００秒 REDOロググループ

ALTER DATABASE ADD文でロググループを追加す る。

REDOロググループ

ALTER DATABASE DROP文でロググループを削除 する。

REDOログ

ALTER DATABASE CLEAR文でログを初期化（中身 を空に）する。

REDOログ

ALTER DATABASE ADD文でロググループに追加 する。

REDOログ

ALTER DATABASE DROP文でロググループから削 除する。 ログスイッチ ログファイルサイズを事前に見積もっておき、業務 ピーク時の切替を回避する様に手動で切替える。 チェックポイント ログファイルサイズを事前に見積もっておき、業務 ピーク時の切替を回避する様に手動で切替える。 初期化パラメータ LOG_ARCHIVE_START アーカイブログが自動か手動かを設定する。 アーカイブログへの出力のＯＮ、ＯＦＦを設定する。 手動アーカイブの実行 アーカイブログ書出しを手動で実行する。 ※　アーカイブ自動モードでも手動実行可能である。

ALTER SYSTEM SET LOG_CHECKPOINT_TIMEOUT

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' AUTOEXTEND

ALTER TABLESPACE ［表領域]　DATAFILE '/u02/oracle/rbdb1/stuff01.dbf'　SIZE 102M; アーカイブログモードの設定 制御ファイルミラー化 （拡張子 ｃｔｌ　ファイル）

内　容

REDOログ切替（手動） ※　補足資料①　図１参照 チェックポイント発生（手動） ※　補足資料①　図１参照 REDOログ追加 REDOログの初期化 チェックポイント発生頻度の調整 ※ FAST_START_MTTR_TARGET のデフォルトが推奨される。

①　アーカイブモードにする　　　alter database archivelog ②　アーカイブモードにしない　　alter database noarchivelog

メンテナンス例

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf'　RESIZE 101M;

ALTER SYSTEM SWITCH LOGFILE; alter database clear logfile member

'c:\oracle\product\10.1.0\TEST\REDO_TEST1.LOG' to group 1

ALTER SYSTEM CHECKPOINT;

ALTER SYSTEM SET control_files = '/ORADATA/u01/ctrl01.ctl', '/ORADATA/u02/ctrl02.ctl' SCOPE = SPFLE ；

alter database add logfile member

'c:\oracle\product\10.1.0\TEST\REDO_TEST1.LOG' to group 1 ALTER DATABASE ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 500K;

ALTER DATABASE db1 DROP LOGFILE GROUP 10;

alter system set log_archive_dest_1='' alter system switch logfile;

アーカイブログ

ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE　SCOPE=SPFILE；

alter system set log_archive_dest_1='location=c:\TEMP\ARCHIVE' 初期化パラメータ LOG_ARCHIVE_DEST_ｎ アーカイブログ手動実行 メンテナンス（追加、削除） ALTER SYSTEM 文でアーカイブログのロケーション を指定して追加、削除する。

ALTER SYSTEM ARCHIVE LOG CURRENT； ALTER SYSTEM ARCHIVE LOG ALL； データファイル

REDOログ削除 DBWRプロセス増加

ALTER SYSTEM SET DB_WRITER_PROCESSES = 10 SCOPE=SPFILE;

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/log2b.rdo'; REDOログ

ファイル

データファイル拡張 （拡張子 ｄｂｆ　ファイル）

拡張子.ｄｂｆファイル

ALTER SYSTEM SET FAST_START_MTTR_TARGET=31;

ALTER SYSTEM SET LOG_CHECKPOINT_INTERVAL

REDOロググループ追加 REDOロググループ削除

③　REDOログ、ログスイッチ、チェックポイントのサイズ調整とチューニングの手順 　　１）　ログスイッチ、チェックポイントプロセスのフロー 　 図 ２ 発生条件 ・5秒おき ・COMMIT ・ログバッファ／５ ・ロググループ満杯 ・ログスイッチ ・初期化パラメータ設定 ・DBシャットダウン時 ・オフラインバックアップ時 ・DBバッファが満杯 ・３秒おき ・CKPT発生 ・プロセスがバッファ 　獲得不能時 ３．リサイクルピン

①　リサイクルピンの表示

SELECT OBJECT_NAME,ORIGINAL_NAME,TYPE FROM R3ECYCLEBIN;

　SHOW RECYCLEBIN

②　リサイクル・ビンからのフラッシュバック

RLASHBACK TABLE

INT_DEPT TO BERORE DROP;

　　２）　REDOログ、アーカイブログのチューニングフロー。 　　　　　※　必ずしもこれが最適解ではないので、適宜必要に応じてフレキシブルに対応する事が望ましい。 ログバッ ＬＧＷＲ ＣＫＰＴ ＤＢＷＲ REDOロ 制御 データＦ Y REDOロ １ ２ V$SESSION_EVENT 閲 REDOログチューニング ロググループ ログスイッチ発生

log file switch checkpoint incomplete が待

アラートログをチェック

Y log file switch archiving needed が待

log file switch completion が待 機 チェックポイントは発生していな 手動でチェックポイントを発生させ Y ORA-00257: アーカイブ・エラー出 Y REDOログを追加 もしくはサイズを拡張 Y N Y 解決 Y チェックポイント発生頻度調 N

DBWR checkpoint write requests が多

V$SYSSTAT 閲覧 Y アーカイブ先の容量は充分 N 不要なアーカイブは削除 初期化パラメータ FAST_START_MTTR_TARGET OR LOG_CHECKPOINT_INTERVA L N 初期化パラメータ DB_WRITE_PROCESSEＳ 初期化パラメータ DB_ARCHIVE_MAX__PROCESSE REDOログを追加 もしくはサイズを拡張 REDOログを追加 もしくはサイズを拡張

３．リサイクルピン

①　リサイクルピンの表示

SELECT OBJECT_NAME,ORIGINAL_NAME,TYPE FROM R3ECYCLEBIN;

　SHOW RECYCLEBIN

②　リサイクル・ビンからのフラッシュバック

RLASHBACK TABLE