v$backup_async_io の罠

RMANのバックアップに時間がかかるような問題が発生したとき、v$backup_async_io から非同期I/Oのパフォーマンス情報を確認することがあると思う。

ただ、このビューからデータファイルの読み込みとバックアップセットの書き出しのどちらに時間がかかっているのかを調べようとすると罠にはまる可能性がある。

以下の二つのケースで、ビューの見え方を確認してみる。

  • バックアップセットの書き出しに時間がかかっているケース
  • データファイルの読み込みに時間がかかっているケース

バックアップセットの書き出しに時間がかかっているケース

事前準備

1GB程度のデータファイルを作成しておき、これをバックアップする。

未使用ブロックの圧縮が効かないように、あらかじめLOBデータを入れておく。

create tablespace testtbs datafile 'C:\oracle\oradata\db19\testtbs.dbf' size 1G autoextend on;
create user testuser identified by testuser default tablespace testtbs;
grant dba to testuser;
conn testuser/testuser
create table test(data blob) lob(data) store as securefile(tablespace testtbs);
create or replace directory d1 as 'C:\Users\Administrator';

declare
  v_dest_lob blob;
  v_file_loc bfile := bfilename('D1','lob.bmp');
  v_amount number;
  v_dst_offset number;
  v_src_offset number;
begin
  dbms_lob.fileopen(v_file_loc, dbms_lob.file_readonly);
  v_amount := dbms_lob.getlength(v_file_loc);
  for i in 1 .. 500 loop
    v_dst_offset := 1;
    v_src_offset := 1;
    insert into test(data) values(empty_blob()) returning data into v_dest_lob;
    dbms_lob.loadblobfromfile(v_dest_lob, v_file_loc, v_amount, v_dst_offset, v_src_offset);
  end loop;
  commit;
  dbms_lob.fileclose(v_file_loc);
end;
/
RMAN> report schema;

リカバリ・カタログのかわりにターゲット・データベース制御ファイルを使用しています
データベースdb_unique_name DB19のデータベース・スキーマのレポート

永続データファイルのリスト
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    890      SYSTEM               YES     C:\ORACLE\ORADATA\DB19\SYSTEM01.DBF
3    500      SYSAUX               NO      C:\ORACLE\ORADATA\DB19\SYSAUX01.DBF
4    60       UNDOTBS1             YES     C:\ORACLE\ORADATA\DB19\UNDOTBS01.DBF
5    1345     TESTTBS              NO      C:\ORACLE\ORADATA\DB19\TESTTBS.DBF ← これ
7    5        USERS                NO      C:\ORACLE\ORADATA\DB19\USERS01.DBF

一時ファイルのリスト
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    32       TEMP                 32767       C:\ORACLE\ORADATA\DB19\TEMP01.DBF

高速なストレージにバックアップする場合

RMANでデータファイルがあるのと同じローカルディスク(SSD)上にバックアップする。

run {
  allocate channel c1 device type disk format 'C:\Users\Administrator\backup\%U';
  backup datafile 5;
}

この時の v$backup_async_io の該当箇所は以下のようになる。

SQL> select TYPE,STATUS,FILENAME,OPEN_TIME,CLOSE_TIME,ELAPSED_TIME,BYTES,EFFECTIVE_BYTES_PER_SECOND,IO_COUNT,LONG_WAITS from v$backup_async_io order by open_time;

TYPE       STATUS     FILENAME                                    OPEN_TIME            CLOSE_TIME           ELAPSED_TIME      BYTES EFFECTIVE_BYTES_PER_SECOND   IO_COUNT LONG_WAITS
---------- ---------- ------------------------------------------- -------------------- -------------------- ------------ ---------- -------------------------- ---------- ----------
AGGREGATE  UNKNOWN                                                2023-05-28 20:07:25  2023-05-28 20:07:32           700 1343750144                  191964306       1284          1
INPUT      FINISHED   C:\ORACLE\ORADATA\DB19\TESTTBS.DBF          2023-05-28 20:07:25  2023-05-28 20:07:32           700 1343750144                  191964306       1284          1
OUTPUT     FINISHED   C:\USERS\ADMINISTRATOR\BACKUP\021TADRT_1_1  2023-05-28 20:07:25  2023-05-28 20:07:32           700 1204396032                  172056576       1149        287

ELAPSED_TIME は 1/100秒 単位なので、7秒程度で完了している。ただ、INPUT と OUTPUTの両方が同じ数字となっていて、ぱっと見はどちらも同じ時間がかかっているように見える。

低速なストレージにバックアップする場合

同じくRMANで、今度は低速なUSBメモリにバックアップする。

run {
  allocate channel c1 device type disk format 'F:\backup\%U';
  backup datafile 5;
}

この時の v$backup_async_io の該当箇所は以下のようになる。

SQL> select TYPE,STATUS,FILENAME,OPEN_TIME,CLOSE_TIME,ELAPSED_TIME,BYTES,EFFECTIVE_BYTES_PER_SECOND,IO_COUNT,LONG_WAITS from v$backup_async_io order by open_time;

TYPE       STATUS     FILENAME                            OPEN_TIME            CLOSE_TIME           ELAPSED_TIME      BYTES EFFECTIVE_BYTES_PER_SECOND   IO_COUNT LONG_WAITS
---------- ---------- ----------------------------------- -------------------- -------------------- ------------ ---------- -------------------------- ---------- ----------
AGGREGATE  UNKNOWN                                        2023-05-28 20:13:06  2023-05-28 20:17:44         27800 1343750144                    4833634       1284          0
INPUT      FINISHED   C:\ORACLE\ORADATA\DB19\TESTTBS.DBF  2023-05-28 20:13:06  2023-05-28 20:17:44         27800 1343750144                    4833634       1284          0
OUTPUT     FINISHED   F:\BACKUP\041TAE6C_1_1              2023-05-28 20:13:06  2023-05-28 20:17:44         27800 1204396032                    4332360       1149        287

この時も、ELAPSED_TIME は INPUT、OUTPUTの双方で 27800 = 約4分半かかっているように見える。しかし、実際に時間がかかっているのはUSBメモリに対する書き出しであり、データファイルの読み込みに時間がかかっているわけではない。

データファイルの読み込みに時間がかかっているケース

事前準備

先ほどと同様、1GB程度のデータファイルを今度は低速なUSBメモリに作成する。

create tablespace testtbs2 datafile 'F:\datafile\testtbs2.dbf' size 1G autoextend on;
create table test2(data blob) lob(data) store as securefile(tablespace testtbs2);

declare
  v_dest_lob blob;
  v_file_loc bfile := bfilename('D1','lob.bmp');
  v_amount number;
  v_dst_offset number;
  v_src_offset number;
begin
  dbms_lob.fileopen(v_file_loc, dbms_lob.file_readonly);
  v_amount := dbms_lob.getlength(v_file_loc);
  for i in 1 .. 500 loop
    v_dst_offset := 1;
    v_src_offset := 1;
    insert into test2(data) values(empty_blob()) returning data into v_dest_lob;
    dbms_lob.loadblobfromfile(v_dest_lob, v_file_loc, v_amount, v_dst_offset, v_src_offset);
  end loop;
  commit;
  dbms_lob.fileclose(v_file_loc);
end;
/
RMAN> report schema;

データベースdb_unique_name DB19のデータベース・スキーマのレポート

永続データファイルのリスト
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    890      SYSTEM               YES     C:\ORACLE\ORADATA\DB19\SYSTEM01.DBF
2    1346     TESTTBS2             NO      F:\DATAFILE\TESTTBS2.DBF ← これ
3    510      SYSAUX               NO      C:\ORACLE\ORADATA\DB19\SYSAUX01.DBF
4    60       UNDOTBS1             YES     C:\ORACLE\ORADATA\DB19\UNDOTBS01.DBF
5    1346     TESTTBS              NO      C:\ORACLE\ORADATA\DB19\TESTTBS.DBF
7    5        USERS                NO      C:\ORACLE\ORADATA\DB19\USERS01.DBF

一時ファイルのリスト
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    128      TEMP                 32767       C:\ORACLE\ORADATA\DB19\TEMP01.DBF

高速なストレージにバックアップ

この状態で、高速なストレージ(ローカルSSD)にバックアップを取ってみる。

run {
  allocate channel c1 device type disk format 'C:\Users\Administrator\backup\%U';
  backup datafile 2;
}

この時のv$backup_async_ioは以下のようになる。

SQL> select TYPE,STATUS,FILENAME,OPEN_TIME,CLOSE_TIME,ELAPSED_TIME,BYTES,EFFECTIVE_BYTES_PER_SECOND,IO_COUNT,LONG_WAITS from v$backup_async_io order by open_time;

TYPE       STATUS     FILENAME                                    OPEN_TIME            CLOSE_TIME           ELAPSED_TIME      BYTES EFFECTIVE_BYTES_PER_SECOND   IO_COUNT LONG_WAITS
---------- ---------- ------------------------------------------- -------------------- -------------------- ------------ ---------- -------------------------- ---------- ----------
AGGREGATE  UNKNOWN                                                2023-05-28 21:41:35  2023-05-28 21:42:52          7700 1344798720                   17464918       1285          0
INPUT      FINISHED   F:\DATAFILE\TESTTBS2.DBF                    2023-05-28 21:41:35  2023-05-28 21:42:52          7700 1344798720                   17464918       1285          0
OUTPUT     FINISHED   C:\USERS\ADMINISTRATOR\BACKUP\071TAJCF_1_1  2023-05-28 21:41:35  2023-05-28 21:42:52          7700 1204281344                   15640017       1149        287

やはり ELAPSED_TIMEはINPUTとOUTPUTの双方が同じ時間(1分強)となっており、読み込みと書き出しの双方に時間がかかっているように見えてしまう。

要するに、v$backup_async_io の結果だけ見ても、読み込みと書き込みのどちらにボトルネックがあるのかは特定できないということになる。

また、Oracle Masterの試験ではLONG_WAITS列をIO_COUNTで割った比率が大きいデータファイルがボトルネックである、と記載されているのだが、今回実験する限りどこで時間がかかっていても結果は同じということが分かった。

根本的にI/Oが遅いディスクがあった場合、v$backup_async_io から被疑箇所を特定することは難しいようだ。

コメント