夏真っ盛りのある日、expdpによるデータ移行で非常に時間がかかっているので調査して欲しいとの連絡を受けました。
リリース前の準備として事前データ移行試験を行っている現行本番環境で、構成は以下の通りです。
対象は3テーブル、サイズは約93GBで、PARALLEL=4およびPARALLEL=8指定で時間計測を行ったところ、いずれのパラメータでも40Gを超えるとExport処理時間が遅くなるとの事。
filesize=5Gを指定してあり、途中までは1ファイル約1分でエクスポートされているのに、40Gを超えると急に1ファイルあたり30分くらいかかるようになるという謎めいた（？）状況です。
![【オラクル認定資格試験対策書】ORACLE MASTER Bronze［Bronze DBA 12c］（試験番号：1Z0-065）完全詳解＋精選問題集 オラクルマスタースタディガイド [ エディフィストラーニング株式会社 ]](https://thumbnail.image.rakuten.co.jp/@0_mall/book/cabinet/5428/9784797375428.jpg?_ex=128x128 "【オラクル認定資格試験対策書】ORACLE MASTER Bronze［Bronze DBA 12c］（試験番号：1Z0-065）完全詳解＋精選問題集 オラクルマスタースタディガイド [ エディフィストラーニング株式会社 ]")
40Gを超えると急に遅くなるって、それはOracleより、OSの問題では・・・？
と思ったのですが、まずは送られてきたAWRレポートを確認します。
| Elapsed Time (s) | Executions | Elapsed Time per Exec (s) | %Total | %CPU | %IO | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|---|
| 3,607.63 | 0 | 101.92 | 4.55 | 95.91 | bcbdhc5zr6bfs | BEGIN SYS.KUPW$WORKER.MAIN('S... |
処理時間が数時間に及んでいるのでExecutionsは0になっていますが、これが時間のかかっている処理であり、IO待機が殆どです。IOが処理時間の大部分を占めるのは、エクスポート処理としては正常です。
DB全体の待機イベントを見ても、特に不審なものはありません。
| Event | Waits | Total Wait Time (sec) | Wait Avg(ms) | % DB time | Wait Class |
|---|---|---|---|---|---|
| db file scattered read | 131,392 | 1883.2 | 14 | 53.2 | User I/O |
| DB CPU | 1194.6 | 33.7 | |||
| direct path read | 31,022 | 205.3 | 7 | 5.8 | User I/O |
| db file parallel read | 9,987 | 128.4 | 13 | 3.6 | User I/O |
| gc cr multi block request | 133,981 | 118.3 | 1 | 3.3 | Cluster |
MOS(MyOracleSupport)でexpdpの性能劣化について調べてみます。
Poor Performance With DataPump Export On Large Databases (ドキュメントID 473423.1)
ディクショナリ統計が取られていない大規模DBでexpdpが遅いという事象で、途中から遅くなるという今回のケースには該当しなさそうです。
Streams AQ: Enqueue待機は出ていないので、これでもなさそうです。
Long型のカラムがたくさんあると、expdpがハングするというもの。対象テーブルがこれに該当するか、一応確認してもらいます。
SecureFileのCLOBがあると、パラレル実行されないのでexpdpが遅くなるという事象。これも対象テーブルがこれに該当するか、要チェックです。
断片化しているテーブルのexpdp処理が遅いという内容です。検証環境でテストしてみましたが、断片化ではそこまで遅くなりませんでした。
これも断片化が原因で遅くなるという話。断片化解消したら9.5時間かかっていたのが15分に短縮されたとか。どれだけ断片化していたんでしょう・・・
表領域の数が多いとexpdp/impdpが遅くなるという不具合。今回のケースには該当しなさそうです。
行連鎖・行移行*1が発生しているテーブルをDIRECT_PATHモードでエクスポートすると、'DB file Sequential Read'の待機で処理が遅くなるという事象です。
DB全体の待機イベントでは'DB file Sequential Read'は上位にランクされていませんでしたが、検証環境でテーブルに行移行を発生させ、ASH(ActiveSessionHistory)で待機イベントを確認すると、確かに'DB file Sequential Read'で遅くなっています。
移行準備中の現行本番環境でもASHを確認したところ、同様に'DB file Sequential Read'で遅くなっていました。
40GB超えたあたりから急に遅くなるのは、そのあたりから行移行が発生しているからではないかと考えられます。
実際に対象テーブルの行移行を確認します。確認方法は以下の通りです。
SQL> create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date
);
SQL> ANALYZE TABLE TEST_TABLE LIST CHAINED ROWS;
SQL> SELECT owner_name,
table_name,
head_rowid
FROM chained_rows where table_name='TEST_TABLE';
結果が返ってくれば、行移行が発生している事になります。
何と4,386,767行もの行移行が発生していました。（件数が多そうだったので、実際にはchained_rowsテーブルの件数だけ確認しています）
行移行が発生しているとexpdp以外にもパフォーマンス影響を及ぼすので行移行を解消するのがベストですが、運用中の本番環境でテーブルの再作成は無理なので、エクスポート時にACCESS_METHOD=EXTERNAL_TABLEオプションを指定し、外部表メソッドを使用する事で回避する事になりました。
現行本番環境でテストしたところ、途中で速度が落ちることも無く、1ファイルあたり1,2分でエクスポートが完了しました。
新本番環境でも現行同様の運用をしていればいずれ行移行が発生すると考えられるので、それを避ける為にpct freeをデフォルトより大きく設定する事を推奨して、今回の問題は解決となりました。
*1:1行の長さがブロック内のデータ格納サイズを超え、2ブロック以上にまたがって格納されるのが行連鎖。updateによって可変長カラムサイズが増え、元のブロックに格納できなくなって別のブロックに移行するのが行移行。
