はじめに
例えば、こんな状況。
定期的に完全リフレッシュをするマテリアライズド・ビューがある。REDOログを生成したくないので、NOLOGGINGにしてあるが、ATOMIC REFRESH有効だと意に反して大きなログが吐き出される。クエリを中断させないリフレッシュでREDOログを生成しない方法はないものか？
まぁ、思考実験みたいなものかな。
- ソースデータが大きく変わるのでMVは完全リフレッシュとしよう。
- まぁMVデータはいつでも再生成可能だし、REDOログっていらないよね？
- じゃあ NOLOGGING にしてみよう。パフォーマンスも向上するし。
- とはいえNOLOGGING が有効になるのはダイレクトパスインサートのみ。
- つまりは CREATE TABLE/INDEX または INSERT /*+ APPEND */ のこと。
- たしかにREDOは作られなくなる。ATOMIC REFRESH無効であれば、、、
- でもATOMIC REFRESH無効だと、実行中のクエリがエラーになるじゃないか。
- ATOMIC REFRESHは有効にしよう。でもそうするとREDOが生成されてしまう。
- TRUNCATE & INSERT APPENDじゃなくてDELETE & INSERTだもんな。
- インデックスメンテナンスを含めると結構なREDOログだな、これ。
- う～ん、クエリを中断させないリフレッシュでREDOログも抑えたいのだが、、、。
さてどうしたものか。
これもうマテリアライズド・ビューやめて、ダイレクトパスインサートでテーブルとインデックスを作った後、パーティションテーブルのパーティションと交換すればいんじゃね？って考えた。
で、やってみた。
以下12.2でテスト。10gでも特に問題ないはず。
アーカイブログ設定の確認
まずは、アーカイブログが有効でかつ強制ロギングが無効になってることを確認する。アーカイブログ無効だと、テーブルのロギング設定に関わらず、ダイレクトパスインサート時に自動的にREDOログ生成をスキップするから、テストとしてはふさわしくないだけ。無効でも特に問題はない。強制データベースロギングは今回の目的としては論外。
SELECT log_mode,
force_logging
FROM v$database;
LOG_MODE FORCE_LOGGING
------------ ---------------------------------------
ARCHIVELOG NO
REDO生成量確認スクリプト
生成REDOサイズの確認にはv$mystatを使う。定義変数に保存したセッションのREDO生成値からの差分を出す。ついでにlogin.sqlに定義変数の初期設定を追加しておく。やっとかないとスクリプトの最初の実行で値を聞いてくるから鬱陶しい。
define _REDO_SAVED=0
col session_redo new_value _REDO_SAVED
SELECT name,
value - NVL('&_REDO_SAVED', 0) redo_bytes,
value session_redo
FROM v$statname n,
v$mystat s
WHERE s.statistic# = n.statistic#
AND name = 'redo size';
テストデータ作成
いつものテストデータ。ダミー文字列を含む。
CREATE TABLE test_src
(
id NUMBER,
s VARCHAR2(500)
);
INSERT INTO test_src
SELECT LEVEL id,
RPAD(LEVEL, 500, 'X') s
FROM dual
CONNECT BY LEVEL <= 100000;
COMMIT;
SELECT COUNT(*) FROM test_src;
COUNT(*)
----------------
100,000
マテリアライズド・ビュー
事前準備ができたので、まずはNOLOGGINGのマテリアライズド・ビューを作ってリフレッシュ時のREDOログ生成量を確認してみる。ついでにインデックスも作っておく。
CREATE materialized VIEW test_nolog_mv
nologging
AS SELECT *
FROM test_src;
CREATE INDEX test_nolog_mv_n1
ON test_nolog_mv(id, s)
nologging;
アトミック有効でリフレッシュするとNOLOGGINGが効かずそこそこのREDOが生成される。さらにインデックス有りだとREDO生成量が跳ね上がる。インデックスメンテナンスって思ってた以上に高価なんだね。
exec dbms_mview.refresh('TEST_NOLOG_MV', 'C', atomic_refresh=>TRUE)
インデックス無し
------------------------- ----------------
redo size 136,334,416
インデックス有り
Elapsed: 00:00:21.74
------------------------- ----------------
redo size 599,895,696
アトミックを無効にすると、TRUNCATEしてからダイレクトパスインサートするなのでほとんどREDOが作られない。リフレッシュに必要な時間も相当短くなってる。ただパフォーマンスについては単純にTRUNCATEの恩恵だな。
exec dbms_mview.refresh('TEST_NOLOG_MV', 'C', atomic_refresh=>FALSE)
インデックス無し
NAME REDO_BYTES
------------------------- ----------------
redo size 305,260
インデックス有り
Elapsed: 00:00:04.57
------------------------- ----------------
redo size 748,236
じゃあ、もうアトミック無効でいいじゃん、ってなりそうだけど、そうでもない。リフレッシュでテーブルをTRUNCATEするから、実行中のクエリがエラーになってしまう。これは避けたい。
SELECT Count(*)
FROM test_nolog_mv mv1,
test_nolog_mv mv2
WHERE mv2.id <= 100
AND mv1.s LIKE '%' || mv2.id || '%';
----------
別セッションでTRUNCATEリフレッシュ
exec dbms_mview.refresh('TEST_NOLOG_MV', 'C', atomic_refresh=>FALSE)
----------
ERROR at line 1:
ORA-08103: object no longer exists
エラーメッセージが紛らわしいのだけど、テーブルをDROPしてるというわけではなくDDLの結果アクセス不能ってことらしい。
パーティションテーブル
そんなわけで、マテリアライズド・ビューを諦めて、パーティションテーブルで代替してみる。テーブルにパーティションを一つだけつくり、そこにすべてのデータが入るようにする。インデックスは、ローカルにしておく。
CREATE TABLE test_nolog_par
(
id NUMBER,
s VARCHAR2(500)
) partition BY RANGE(id) (PARTITION pt VALUES less than (100000000) nologging)
;
CREATE INDEX test_nolog_par_n1
ON test_nolog_par(id, s)
local nologging;
リフレシュの代わりに、このパーティションを新規テーブルと交換するというわけだ。まぁ、パーティションEXCHANGEは、バルクデータの投入なんかで使われてるけどその応用。
ここで更新データの入った新しいテーブルとインデックスを作る。どちらもNOLOGGINGでのダイレクトパスインサートなのでREDOの生成は極めて小さい。
CREATE TABLE test_nolog
nologging AS
SELECT * FROM test_src;
------------------------- ----------------
redo size 189,316
CREATE INDEX test_nolog_n1
ON test_nolog(id, s)
nologging;
------------------------- ----------------
redo size 313,720
そして、インデックスごとテーブルとパーティションを交換。これにて完了。REDOはほぼ無し。
SELECT COUNT(*) FROM test_nolog_par;
----------------
0
ALTER TABLE test_nolog_par exchange PARTITION pt WITH TABLE test_nolog INCLUDING INDEXES;
------------------------- ----------------
redo size 13,680
SELECT COUNT(*) FROM test_nolog_par;
----------------
100,000
交換したら実行中のクエリはどうなるんだ？って疑問だけれども、引き続き元のデータを参照し続ける。交換前にパーティションテーブルを参照中だったクエリは、交換後は元データをもつ方のテーブルを参照する。なのでエラーにはならない。逆に言うと交換後に元データを持つ方のテーブルをドロップすれば、即エラーとなる。つまり、不要になったテーブルはある程度余裕を持ってから削除しましょう、ってこと。
SELECT COUNT(*)
FROM test_nolog_par p1,
test_nolog_par p2
WHERE p2.id <= 100
AND p1.s LIKE '%' || p2.id || '%';
--------
別セッションでパーティションを交換したあと元テーブルをドロップ
ALTER TABLE test_nolog_par exchange PARTITION pt WITH TABLE test_nolog INCLUDING INDEXES;
DROP TABLE test_nolog;
--------
ERROR at line 1:
ORA-08103: object no longer exists
あとは、データ更新時に同じ様に、テーブルとインデックスの作成、パーティション交換、テーブルドロップを繰り返せばよいわけだ。テーブルドロップのタイミングは環境次第。
もしかして、これ単純にテーブルの名前変更でも同じことでできるんじゃね？ってふとよぎるかもれしれないけれども、そうするとテーブルがなくなる一瞬が存在するので、クエリ側でエラーになる可能性がやっぱりある。高速でクエリを繰り返している場合特にね。
CREATE TABLE test_tbl_new AS
SELECT *
FROM test_src;
DECLARE
n NUMBER;
BEGIN
WHILE TRUE LOOP
SELECT COUNT(*)
INTO n
FROM test_nolog;
END LOOP;
END;
/
----------
別セッションで、以下をスクリプトとして実行。
ALTER TABLE test_tbl RENAME TO test_tbl_old;
ALTER TABLE test_tbl_new RENAME TO test_tbl;
----------
ORA-00942: table or view does not exist
対して、パーティションテーブルだとオブジェクト自体はなくならないので、エラーにならない。たとえ、クエリの途中でパーティションをDROPしても問題なし。 追記：流石にDROPはエラーになるようです。訂正。
リカバリ後
もののついでなので、リカバリも試してみた。リカバリするとNOLOGGINGオブジェクトは思いっきりエラーになる。なるけれどもTRUNCATEすればよいみたいなので問題なし。
RMAN> backup archivelog all;
RMAN> alter tablespace users offline;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> alter tablespace users online;
SELECT COUNT(*) FROM test_nolog_par;
ORA-01578: ORACLE data block corrupted (file # 7, block # 134307)
ORA-01110: data file 7: '/oradata/TESTDB/datafile/o1_mf_users_fm1x46px_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
TRUNCATE TABLE test_nolog_par;
SELECT COUNT(*) FROM test_nolog_par;
COUNT(*)
----------------
0
おわりに
そういうわけで、ダイレクトパスインサートをつかってREDOログを生成しない状態で、クエリを中断を起こさずにテーブルを完全リフレッシュする方法でした。実際に使えるかどうかはまた別の話ということで (^^)。
以上。