しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。 “しばちょう”こと柴田長（しばた つかさ）です。
驚きました。今回で連載20回を迎えているではないですか！飽きやすい性格の私ですが、ここまで続けられたのは関係者の方々のご協力のおかげであり、何よりもこの連載をご愛読して頂いている皆様があってこそだと思っています。本当にありがとうございます。まだまだ継続したいと考えておりますので、引き続きよろしくお願い致します。
さて、今回も前回から引き続き、フラッシュバック・データベースをご紹介してきます。前回はフラッシュバック・データベースを使用する上で必要となるフラッシュバック・ログを取得する為の設定を行い、実際にフラッシュバック・データベースを実行することでデータファイルのバックアップをリストアしてRedoレコードを適用するリカバリを実行するよりも短時間で過去の時点へデータベースを巻き戻すことが可能であることを体験して頂き、フラッシュバック・データベースが便利そうな機能であることを感じて頂けたかと思います。しかし、皆さんお気付きだと思いますが、このフラッシュバック・ログなるものは設定しなければ取得されません。つまり追加で取得する必要があるので、追加のH/Wリソースの消費に繋がりそうな予感がしますね。
と言う事で、今回はフラッシュバック・ログを取得する為の設定によるオーバーヘッドを確認していきましょう。以下の演習をOracle Database 11g Release 2 Enterprise Editionのデータベースで試してみてください。
- 【今回ご紹介するネタ一覧（逆引き）】
- Flashback Loggingの無効化（演習2）
- AWRスナップショットの作成（演習3）
- AWR期間比較レポートの生成（演習5）
1. 前回同様、第一カラムが主キーでNUMBER型のCOL1、第二カラムがCHAR(100)型のCOL2列である表TAB20を作成し、初期データ100万件をロードしてください。また、高速リカバリ領域とアーカイブ・ログ・モードが適切に設定されているかを確認してください。
sqlplus TRY/TRY SQL> -- TAB20表の作成 create table TAB20 (COL1 number NOT NULL, COL2 char(100)); insert /*+append */ into TAB20 select LEVEL, 'hoge'||to_char(LEVEL) from DUAL connect by LEVEL <= 1000000 ; commit; create unique index IDX_TBL20_COL1 on TAB19(COL1) ; alter table TAB20 add primary key (COL1) using index ; SQL> -- アーカイブ・ログ・モードの確認 connect / as sysdba archive log list データベース・ログ・モード アーカイブ・モード 自動アーカイブ 有効 アーカイブ先 USE_DB_RECOVERY_FILE_DEST 最も古いオンライン・ログ順序 5 アーカイブする次のログ順序 7 現行のログ順序 7 SQL> -- 高速リカバリ領域の確認 show parameter db_recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +FRA db_recovery_file_dest_size big integer 10G
はい、完全に前回の復習となります。もしアーカイブ・ログ・モードや高速リカバリ領域が設定されていない場合は、前回の演習2を参考にして実行してみてください。
2. Flashback Loggingを”無効化”し、フラッシュバック・ログが存在していないことを確認してください。
sqlplus / as sysdba SQL> -- Flashback Loggingの無効化 alter database flashback off ; select FLASHBACK_ON from V$DATABASE ; FLASHBACK_ON ------------------ NO SQL> -- Flashback Logの存在有無の確認 select * from V$FLASHBACK_DATABASE_LOG ; レコードが選択されませんでした。
Flashback Loggingを有効化するSQLが「alter database flashback on ;」であったのに対し、無効化する為のSQLは「alter database flashback off ;」となります。非常に覚え易いコマンドで良いですよね。まあそんなに頻繁に実行するSQLでは無いと思いますがね。
皆さんは「有効化したけど無効化の仕方が解らない」というシチュエーションに出合った経験は無いでしょうか？私は駆け出しの頃に結構あったような気がしますけどね。新しい設定を施す場合には、その前に必ず戻す方法も調査しておくことを心掛けたいものです。さらに、実際にFlashback Loggingが無効化されたことを確認することも大切ですね。これにはV$DATABASEビューを問合せますが、このビューを通して制御ファイル内の設定情報を確認することが可能です。また、Data Guard環境を構築/運用する場合には絶対に使用するビューですので、是非覚えておいてください。
最後に、フラッシュバック・ログの存在有無を確認していますが、Flashback Loggingを無効化したタイミングでフラッシュバック・ログが自動的に削除される動作であることを覚えておいてください。
ちなみに、あまり有益な情報ではないかもしれませんが、ここで削除されるフラッシュバック・ログはFlashback Loggingを無効化したデータベースの制御ファイルが認識しているものだけです。もしも、他のデータベースのフラッシュバック・ログが同一の高速リカバリ領域に保存されていた場合、勝手に消されてしまっても困りますよね？V$FLASHBACK_DATABASE_LOGビューではフラッシュバック・ログが存在していないことが確認できても、何故か高速リカバリ領域の空き領域が解放されない場合には、（もちろん、原因はアーカイブログやバックアップである可能性も有りますが）高速リカバリ領域には別のデータベースが作成したフラッシュバック・ログが残っている可能性も考えて調査してみてください。
3. TAB20表の特定のレコードを1000000回更新して下さい。ただし、この処理の前後に、AWRのスナップショット作成を実行してください。
sqlplus / as sysdba
SQL> -- テスト前にインスタンスの再起動
shutdown immediate ;
startup ;
SQL> -- テスト開始
connect system
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
connect TRY/TRY
begin
for i in 1..1000000 loop
update TAB20 set COL2='updated'||i where COL1=100 ;
commit ;
end loop ;
end ;
/
connect system
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
Flashback Loggingが無効化の状態で更新処理を実行した際のAWRレポートを確認したい為、更新処理を挟む形で2回のAWRスナップショットを作成します。Statspackレポートを生成した経験のある方にとっては、コマンドは違いますが実行の仕方は同じですね。
Oracle Databaseはインスタンス起動直後から処理を実行することでカウントアップする待機イベントや統計情報の累積値を保持しており、「スナップショットを作成する」=「その地点の待機イベントや統計情報の累積値をワークロード・リポジトリに保存しておく」と言う事です。AWRレポートはこの2つのスナップショットの差分を可視化してくれるので、その2つの期間にデータベースがどのような処理を実行したのかを分析することができるのです。
またまたちなみにですが、インスタンスを再起動した場合、待機イベントや統計情報の累積値はリセットされてしまいます。よって、インスタンス再起動を含む2つのスナップショットの間では正確なレポートにはならないのは明らかですね。
4. Flashback Loggingを”有効化”し、演習3を再度実行してください。
sqlplus / as sysdba
SQL> -- Flashback Loggingの有効化
shutdown immediate ;
startup mount ;
alter database flashback on ;
alter database open ;
select FLASHBACK_ON from V$DATABASE ;
FLASHBACK_ON
------------------
YES
SQL> -- 演習3の再実行
connect system
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
connect TRY/TRY
begin
for i in 1..1000000 loop
update TAB20 set COL2='updated'||i where COL1=100 ;
commit ;
end loop ;
end ;
/
connect system
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
はい、この演習は復習となります。上記の解答例では、Flashback Loggingを有効化する為に一旦、インスタンスをマウント・モードとしていますが、Oracle Database 11g Release 2においてはオープン状態でも実行できるようになっていたりしますね。
今回はFlashback Loggingの設定有無による影響だけを確認したいので、AWRレポートに雑音が入って分析しづらくなるのが嫌なので、毎回インスタンスの再起動を行っています。なので、そのタイミングでFlashback Loggingを有効化してみました。
5. 演習3と演習4でそれぞれ作成したAWRのスナップショットを使用して、AWR期間比較レポートを生成してください。
sqlplus /nolog SQL> connect system @?/rdbms/admin/awrddrpt.sql ..........(省略).......... Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' report_typeに値を入力してください: text Type Specified: text ..........(省略).......... Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing without specifying a number lists all completed snapshots. num_daysに値を入力してください: Listing all Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- orcl ORCL 78 29 7月 2013 13:43 1 79 29 7月 2013 13:48 1 80 29 7月 2013 13:49 1 81 29 7月 2013 13:52 1 Specify the First Pair of Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ begin_snapに値を入力してください: 78 First Begin Snapshot Id specified: 78 end_snapに値を入力してください: 79 First End Snapshot Id specified: 79 ..........(省略).......... Specify the Second Pair of Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ begin_snap2に値を入力してください: 80 Second Begin Snapshot Id specified: 80 end_snap2に値を入力してください: 81 Second End Snapshot Id specified: 81 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrdiff_1_78_1_80.txt To use this name, press to continue, otherwise enter an alternative. report_nameに値を入力してください: Using the report name awrdiff_1_78_1_80.txt
単純にFlashback Logging設定の有り無しの2枚のAWRレポートを生成して比較するのでは面白くないと思ったので、あまり使っている人が多く無いと私が勝手に思っている、AWR期間比較レポートを使用してもらいました。なんと！これを使用すれば頑張って2枚のAWRレポートを見比べる必要無く、1枚のレポートでその差を簡単に確認することが可能なのです。特にノートPCの小さな画面でDBA業務を行われている方にとっては重宝するレポートなのではないでしょうか？
きっちりとパフォーマンス・チューニング・ガイドに記載されているので、マニュアルを読まれている方は既にご存じかもしれませんね。もし、ご存じなかったDBAの方がいらっしゃったら、是非この機会に習得して周囲に自慢しちゃってくださいね。
6. 演習5で生成したAWR期間比較レポートを使用して、Flashback Loggingを有効化することによる増加した処理を確認してください。
Key Instance Activity Stats First DB/Inst: Snaps: 78-79, Second DB/Inst: Snaps: 80-81
-> Ordered by statistic name
Value
-------------------------------------------
Statistic 1st 2nd %Diff
------------------------------ ---------------- ---------------- ---------
db block changes 4,002,547 4,001,939 -0.0
execute count 1,006,722 1,005,471 -0.1
logons cumulative 25 325 1,200.0
opened cursors cumulative 2,005,919 2,005,646 -0.0
parse count (total) 2,342 3,318 41.7
parse time elapsed 123 406 230.1
physical reads 4,705 37,638 700.0
physical writes 30,017 31,618 5.3
redo size 738,652,576 729,708,524 -1.2
session cursor cache hits 40,253 38,616 -4.1
session logical reads 7,029,764 6,114,654 -13.0
user calls 99 2,813 2,741.4
user commits 1,000,038 1,000,021 -0.0
workarea executions - optimal 1,093 734 -32.8
--------------------------------------------------
Tablespace IO Stats
Tablespace
------------------------------
Avg Reads / Sec Avg Writes / Sec Reads
------------------------------- ------------------------------- -------------------------
1st 2nd %Diff 1st 2nd %Diff 1st 2nd
UNDOTBS1 0.0 197.8 9.887E+05 6.4 27.5 329.0 6 32,314
SYSTEM
10.0 15.5 55.6 0.1 0.1 42.9 3,105 2,539
SYSAUX
2.3 3.7 59.6 0.8 1.2 51.2 729 612
-------------------------------------------------------------------
さあ、いよいよFlashback Loggingのオーバーヘッドなるものを確認してみましょう。AWR期間比較レポートには様々なセクションで比較結果が出力されていますが、今回は上記の2つのセクションを抜粋して解説させて頂きますね。
まずは「Key Instance Activity Stats」セクションです。統計情報毎に、1st、2nd、%Diffの3つの列があります。この1stはSnaps:78-79（Flashback Loggingの設定無し）の値、2ndはSnaps:79-80（Flashback Loggingの設定有り）の値となっています。各Snap期間の絶対時刻はAWR期間比較レポートの先頭行にも表示されているので確認してみてください。
この1stと2ndの列の値を見比べても良いですが、%Diff列の値を見た方が一目瞭然ですね。どうも統計情報「physical reads」が700%も増加していることが確認できます。ちなみに、各統計情報の意味はリファレンス・マニュアルの統計情報の説明に記述がされており、この統計情報「physical reads」は「ディスクから読み込まれたデータ・ブロックの合計数」であることが理解できます
「なるほどー。Flashback Loggingを有効化することで、データ・ブロックの読み込み量が約32万回(= 37,638 – 4,705)増えたと言う事ね」と簡単に分かっちゃいますね。そして、次に気になるのは、具体的にどの表領域のデータ・ブロックの読み込み回数が増加したのか？となります。
そこでTablespace毎のI/O量を比較できるセクション「Tablespace IO Stats」を確認してみましょう。こちらも一目瞭然で、UNDO表領域の読み込み量が顕著に増加していることが読み取れます。具体的な回数の差は約32万回であり、先ほど確認した統計情報Physical Readsの差とも一致しますから、「なるほどー。Flashback Loggingを有効化することでブロックの読み込み量が増加していたけど、UNDO表領域のブロックだったのね」とまたまた簡単に分かっちゃいましたね。
思い出してください。フラッシュバック・ログとは更新前ブロック・イメージであり、フラッシュバック・データベースはそれを使用してデータベースを過去の時点に巻き戻す機能です。つまり、フラッシュバック・データベースではUNDO表領域も巻き戻しの対象になりますから、フラッシュバック・ログには、UNDO表領域の更新前ブロック・イメージも含まれることになるのです。
そして、UNDO表領域はOracle Databaseの読み取り一貫性を実現する為にUNDOデータを保持する為に使い回されています。この使い回される仕組み上、ディスク上のUNDOデータ・ブロックに何が書いてあろうと「上書きするだけ」=「ディスクから読み込まない」なのです。しかし、Flashback Loggingを有効化した場合には、上書きする前に、更新前のUNDOデータ・ブロックをフラッシュバック・ログとして保存しておかなければならないので、読み込みが追加されることになるのですね。
さて、いかがでしたでしょうか？意外な結末だったのではないでしょうか？
少々、難しい解説となってしまいましたが、通常は読み込む必要が無かったブロックが、Flashback Loggingを有効化することで必要になったと言う事です。これは、LOGセグメントのデータ・ブロックも共通して言えます。デフォルトのLOBセグメントは更新時に上書きするだけです。ディスクから読み込みませんので、Flashback Loggingを有効化した場合には、LOGセグメントのデータ・ブロックの読み込み処理が追加されることになりますので、H/Wリソースの消費量の増加にはご注意ください。
とは言え、フラッシュバック・データベースは非常に便利な機能です。実際に救われるケースは多々あります。もしオーバーヘッドが気になるようであれば、Data GuardのStandby Database側だけでもFlashback Loggingを有効化するという構成もあり得ます。このあたりは、是非、MAA（高可用性ベストプラクティス）を参考にして頂けると幸いです。
また、今回体験して頂いたオーバーヘッドをSSDでチューニング方法を、私が検証/執筆したホワイト・ペーパーにて公開しておりますので、そちらも参考にしてみてください。
第20回を迎え、ますます頑張っていきたいと考えておりますので、今後ともどうぞよろしくお願いします。