しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。 “しばちょう”こと柴田長（しばた つかさ）です。
今回はこれまでの連載でも度々機能名だけ紹介してきた、表のオンライン再定義をご紹介したいと思います。例えば、表データ量が多くなったので表領域を移動しつつ圧縮したい。しかし、表をメンテナンス中でも業務トランザクションは継続させたい。という欲張りなシチュエーションに遭遇したらチャンス到来です。「私なら業務トランザクションを止めることなく、表をメンテナンスできますよ！」と宣言し、オンライン再定義を使用して作業をすれば、貴方は皆から尊敬されること間違いなしです。って、言い過ぎかもしれませんが。。。そのようなチャンスを逃さない為にもオンライン再定義を体験マスターしておきましょう。
また合わせて、オンライン再定義を使用しなかった場合にDML文が遅延してしまうことの確認、さらには、ASH（Active Session History）を使用した遅延の原因の特定方法も簡単にご紹介させて頂きますね。
以下の演習をOracle Database 11g Release 2 Enterprise Editionのデータベースで試してみてください。
【今回ご紹介するネタ一覧（逆引き）】
- SYS_CONTEXTを使用して自身のSIDを確認（演習2）
- V$ACTIVE_SESSION_HISTORYで待機イベントとBlockerセッションを特定（演習3）
- V$SQLTEXTでセッション毎の実行SQLを確認（演習3）
- 表のオンライン再定義の実行（演習4）
1. 第一カラムが主キーでNUMBER型のCOL1、第二カラムがCHAR(500)型のCOL2列である表TAB18を作成し、初期データ100万件をロードしてください。
sqlplus TRY/TRY SQL> @createTAB18.sql drop table TAB18 purge; create table TAB18 (COL1 number NOT NULL, COL2 char(500)); insert /*+append */ into TAB18 select LEVEL, 'hoge'||to_char(LEVEL) from DUAL connect by LEVEL <= 1000000 ; commit; create unique index IDX_TBL18_COL1 on TAB18(COL1) ; alter table TAB18 add primary key (COL1) using index ;
はい、毎度のことなので詳しい説明は割愛させて頂きます。
ちなみに、「createTAB18.sql」にSQLを書いておき、そのSQLファイルを@で指定してSQLを実行している例となります。以降の演習で、再実行する必要が出てくるので、このような形で実行しています。
2. TAB18表をOLTP表圧縮で圧縮してください。また、圧縮を実行中に新規レコードを1件INSERTして下さい。
sqlplus TRY/TRY
SQL> -- sessionA
alter table TAB18 move compress for oltp;
sqlplus TRY/TRY
SQL> -- sessionB
set timing on
insert into TAB18 values(0,'add record');
行1でエラーが発生しました。:
ORA-01502:
索引'TRY.IDX_TBL18_COL1'またはそのパーティションが使用不可の状態です。
経過: 00:00:18.67
SQL> select sys_context('USERENV','SID') from DUAL;
SYS_CONTEXT('USERENV','SID')
-----------------------------
4516
alter table MOVE文で表を圧縮しています。これは第15回 圧縮表への変更方法と注意点でご紹介していますね。そして、その実行中に対象表に1件のINSERTを試みていますが、なかなか実行が完了せずに18秒後にORA-01502エラーが発生してINSERTが失敗してしまいます。
ここでの問題は2点あると思います。1点目の問題はORA-01502です。エラーメッセージの通り、TAB18表に張られていた索引IDX_TBL18_COL1が使用不可の状態となっています。これも第15回 圧縮表への変更方法と注意点でご紹介していますが、alter table MOVE文=「レコードを入れ直す」=「各レコードのROWIDが変更される」のでROWIDを保持しているB*Tree索引が無効化してしまう為でしたね。このため、索引のRebuildが必要となると言う事ですから、事実上、索引のRebuildが完了するまでは業務トランザクションを実行できないということになりますね。
さらに2点目は、INSERT文でORAエラーが発生するまでに18秒間を要してしまっています。これに関しては次の演習を通して解説させて頂きます。そのためには、INSERT文を実行していたセッションのSESSION_ID（SID）を把握している必要があるため、最後にSYS_CONTEXTを使用して「4516」というSIDを確認しています。
3. 演習2でINSERT文が遅延した理由を、V$ACTIVE_SESSION_HISTORYを使用して特定してください。
sqlplus / as sysdba
SQL> -- INSERT文を実行したセッションの直近10分間の待機イベントを確認
SQL> -- SESSION_ID=4516は、演習2の最後に確認したSIDから
select SAMPLE_TIME, SESSION_ID, EVENT, TIME_WAITED, BLOCKING_SESSION
from V$ACTIVE_SESSION_HISTORY
where SESSION_ID=4516
and SAMPLE_TIME > SYSTIMESTAMP - 10/60/24
order by 1;
SAMPLE_TIME SESSION_ID EVENT TIME_WAITED BLOCKING_SESSION
---------------------- ---------- --------------------- ----------- ----------------
13-06-09 22:41:57.568 4516 enq: TM - contention 0 10
13-06-09 22:41:58.578 4516 enq: TM - contention 0 10
13-06-09 22:41:59.578 4516 enq: TM - contention 0 10
13-06-09 22:42:00.588 4516 enq: TM - contention 0 10
13-06-09 22:42:01.588 4516 enq: TM - contention 0 10
13-06-09 22:42:02.598 4516 enq: TM - contention 0 10
13-06-09 22:42:03.608 4516 enq: TM - contention 0 10
13-06-09 22:42:04.618 4516 enq: TM - contention 0 10
13-06-09 22:42:05.618 4516 enq: TM - contention 0 10
13-06-09 22:42:06.628 4516 enq: TM - contention 0 10
13-06-09 22:42:07.628 4516 enq: TM - contention 0 10
13-06-09 22:42:08.638 4516 enq: TM - contention 0 10
13-06-09 22:42:09.638 4516 enq: TM - contention 0 10
13-06-09 22:42:10.648 4516 enq: TM - contention 0 10
13-06-09 22:42:11.648 4516 enq: TM - contention 0 10
13-06-09 22:42:12.658 4516 enq: TM - contention 0 10
13-06-09 22:42:13.658 4516 enq: TM - contention 0 10
13-06-09 22:42:14.668 4516 enq: TM - contention 18667027 10
SQL> -- SID=10と4516のSQL文を確認
set PAGES 1000 LINES 150
col EVENT for a30
col USERNAME for a8
select T1.SID, T1.USERNAME, T1.SQL_ID, T2.SQL_TEXT
from V$SESSION T1, V$SQLTEXT T2
where T1.SQL_ID = T2.SQL_ID and SID in (10, 4516)
SID USERNAME SQL_ID SQL_TEXT
---------- ---------- ------------- ----------------------------------------------
10 TRY 1zgnzx989v02x alter table TAB18 move compress for oltp
4516 TRY 31w1x2kfv3fyy insert into TAB18 values(0,'add record')
まずは、V$ACTIVE_SESSION_HISTORYに問合せることで、SID=4516が「enq: TM – contention」待機イベントで「18667027」マイクロ秒間(=約18.667秒間)待機していたことが確認できます。なんと！！この待機時間は、演習2でINSERT文の実行時間と一致しますね。と言う事からも、V$ACTIVE_SESSION_HISTORYの凄さが垣間見られます。さらに、この待機イベントの原因を作っていたセッション（BLOCKING_SESSION）のSIDが「10」であることも解ってしまったので、このSID=10が実行していたSQL文が知りたくなってきます。
では早速調べてみましょう。V$SESSIONとV$SQLTEXTを組み合わせて確認しているのが後半のSQL文となります。こちらも見事に、SID=10はTRYユーザーで「alter table MOVE」を実行していることが簡単に解ってしまいました。
調査結果をまとめます。実行している環境によって前後すると思いますが、INSERT文でORAエラーが発生したタイミングでは、alter table MOVE文による表の圧縮は完了していたはずです。つまり、alter table MOVE文を実行しているセッションが表の排他ロックを取得している為、その操作が完了するまでINSERT文等のDML文の実行はエンキュー待ちをさせられてしまったということになります。表領域を移動したり圧縮したりというメンテナンスを実施したい場合にalter table MOVE文を使用するには、業務トランザクションを停止せざるを得ない状況になってしまうことが実感できたかと思います。
と言う事で、いよいよ表のオンライン再定義の出番となります。
4. 演習1を再度実行して表TAB18を再作成後、DBMS_REDEFINITINONパッケージで表のオンライン再定義を使用して圧縮表へ変換してください。その際、PL/SQL文で1秒に一度、表TAB18へINSERTを試みてください。
sqlplus TRY/TRY ###sessionA
SQL> @createTAB18.sql
sqlplus TRY/TRY ###sessionB
SQL> begin
for i in 1000001..1000100 loop
insert into TAB18 values(i,to_char(SYSDATE, 'HH24:MI:SS'));
commit;
DBMS_LOCK.SLEEP(1);
end loop;
end;
/
SQL> -----------------------------------------------------------------
SQL> -- sessionA
@online_redef.sql
SQL> set time on
23:53:07 SQL>
select TABLE_NAME, COMPRESS_FOR from USER_TABLES ;
TABLE_NAME COMPRESS_FOR
------------------------------ ------------
TAB18
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('TRY', 'TAB18', DBMS_REDEFINITION.CONS_USE_PK, NULL);
END;
/
drop table TAB18_NEW purge;
create table TAB18_NEW compress for oltp as select * from TAB18 where 1=2;
alter table TAB18_NEW add primary key(COL1);
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'TRY',
orig_table => 'TAB18',
int_table => 'TAB18_NEW',
options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname => 'TRY',
orig_table => 'TAB18',
int_table => 'TAB18_NEW',
copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
copy_triggers => TRUE,
copy_constraints => TRUE,
copy_privileges => TRUE,
ignore_errors => TRUE,
num_errors => num_errors,
copy_statistics => TRUE,
copy_mvlog => FALSE);
END;
/
set PAGES 5000 LINES 150
col OBJECT_NAME for a16
col BASE_TABLE_NAME for a12
col DDL_TXT for a100
set long 50000
select OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT
from DBA_REDEFINITION_ERRORS;
OBJECT_NAME BASE_TABLE_N DDL_TXT
---------------- ------------ --------------------------------------------------------
IDX_TBL18_COL1 TAB18 CREATE UNIQUE INDEX "TRY"."TMP$$_IDX_TBL18_COL10" ON
"TRY"."TAB18_NEW" ("COL1")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS1"
SYS_C0025607 TAB18 ALTER TABLE "TRY"."TAB18_NEW" MODIFY ("COL1" CONSTRAINT
"TMP$$_SYS_C00256070" NOT NULL ENABLE NOVALIDATE)
SYS_C0025608 TAB18 ALTER TABLE "TRY"."TAB18_NEW" ADD CONSTRAINT
"TMP$$_SYS_C00256080" PRIMARYKEY ("COL1")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS1" ENABLE NOVALIDATE
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TRY', 'TAB18', 'TAB18_NEW');
END;
/
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('TRY', 'TAB18', 'TAB18_NEW');
END;
/
23:53:49 SQL>
select TABLE_NAME, COMPRESS_FOR from USER_TABLES ;
TABLE_NAME COMPRESS_FOR
------------------------------ ------------
TAB18 OLTP
TAB18_NEW
drop table TAB18_NEW purge ;
かなり長い回答例となってしまいましたが、いかがでしょうか。この例でのオンライン再定義では合計5つのプロシージャをコールしています。全てのプロシージャを事細かに説明することはできませんが、ポイントを解説させて頂きます。
まず絶対に外せないのは、CAN_REDEF_TABLEプロシージャをコールすることです。このプロシージャでオンライン再定義可能か否かが判断されます。その後、移行先である圧縮属性付きの仮表を作成し、START_REDEF_TABLEプロシージャをコールすることで初期データの移行を開始します。オンライン再定義の特徴は、この移行中にも元表へのDML文が実行できるということです。これが本当か否かは演習5で確認しますので、少しお待ちくださいね。
そして、初期移行が完了したら、次に、COPY_TABLE_DEPENDENTSプロシージャをコールして表の依存オブジェクトである（トリガーや制約等）を仮表に複製します。非常に便利な臭いがしますよね。表を作成し直す際に一番面倒なのは、このような依存オブジェクトの再定義だったりするので、オンライン再定義ではこの辺りの作業の効率化を支援する機能が充実しています。ちなみに、COPY_TABLE_DEPENDENTSプロシージャで複製が失敗した依存オブジェクトのリストをDBA_REDEFINITION_ERRORSビューで確認することが可能です。今回の例でも3つ程度出力されてしまっていますが、全ては主キーに紐づくもので既に仮表に手動で作成済みなので、無視して問題ないものとなります。
次に、SYNC_INTERIM_TABLEプロシージャをコールしていますが、これは必要に応じて実行すれば良いです。オンライン再定義中に多くのDML文を元表に実行していることを把握している場合は、このプロシージャをコールすることで、START_REDEF_TABLEプロシージャで初期移行後に発生した差分レコードを仮表へ反映することができるからです。
最後に、FINISH_REDEF_TABLEプロシージャをコールすることで元表と仮表の名前を入れ替え、オンライン再定義が完了します。この例では圧縮属性付きの仮表であるTAB18_NEW表が、TAB18表に切り替わっていることが確認できますね。
より詳細な解説は、管理者ガイドやPL/SQLパッケージ・プロシージャおよびタイプ・リファレンスを参考にしてみてください。
5. 演習4でTAB18表へINSERTしていたレコードを参照し、オンライン再定義中にINSERT文の実行に遅延が発生しなかったことを確認してください。
sqlplus TRY/TRY
SQL> select COL1, substr(COL2,1,8) from TAB18 where COL1 >= 1000001;
COL1 SUBSTR(COL2,1,8)
---------- --------------------------------
1000001 23:53:03
1000002 23:53:04
1000003 23:53:05
1000004 23:53:06
1000005 23:53:07 ← ここから
1000006 23:53:08
1000007 23:53:09
1000008 23:53:10
1000009 23:53:11
1000010 23:53:12
1000011 23:53:13
1000012 23:53:14
1000013 23:53:15
1000014 23:53:16
1000015 23:53:17
1000016 23:53:18
1000017 23:53:19
1000018 23:53:20
1000019 23:53:21
1000020 23:53:22
1000021 23:53:23
1000022 23:53:24
1000023 23:53:25
1000024 23:53:26
1000025 23:53:27
1000026 23:53:28
1000027 23:53:29
1000028 23:53:30
1000029 23:53:31
1000030 23:53:32
1000031 23:53:33
1000032 23:53:34
1000033 23:53:35
1000034 23:53:36
1000035 23:53:37
1000036 23:53:38
1000037 23:53:39
1000038 23:53:40
1000039 23:53:41
1000040 23:53:42
1000041 23:53:43
1000042 23:53:44
1000043 23:53:45
1000044 23:53:46
1000045 23:53:47
1000046 23:53:48
1000047 23:53:49 ← ここまで
1000048 23:53:50
1000049 23:53:51
1000050 23:53:52
1000051 23:53:53
1000052 23:53:54
1000053 23:53:55
…………………
最後に、オンライン再定義中にINSERT文が遅延していなかったのかを確認してみましょう。演習4のオンライン再定義中に、別のセッションからPL/SQLで1秒毎にTAB18表へINSERTを実行していたので、それらのレコードを参照してみます。
INSERT文では、COL1列に1000001以降の連番を、COL2列に時刻を格納しています。つまり、オンライン再定義を実行していた22:53:07～22:57:49の間で毎秒INSERTがされていれば、オンライン再定義中にDML文が遅延（ブロッキング）されていなかったことになりますが・・・どうでしょう。。。
どうやら検証が成功したようですね。見事に全ての秒でINSERTされていることが確認できていますので、少しはオンライン再定義を信用してもらえたかと思います。
さて、いかがでしたでしょうか？ 表のオンライン再定義は複数のプロシージャを順番にコールしていく形になるので、今回の演習の解答例を保存しておいて頂けると、さまざまなシチュエーションで使用可能だと思います。また、パーティショニング・オプションを使用して表をパーティション化している場合、特定のパーティションに限定して表のオンライン再定義を実行することも可能です。つまり、特定のパーティショニングだけを圧縮したい、表領域を移動したいという場合にも使用できるというわけです。特に最近のデータベース管理では必ずと言って良いほど、メンテナンスをオンラインで実行できるか否かをユーザー様から問合せを受けると思いますので、この機能をフル活用して頂きたいですね。
とか言いつつ、何とか今回も原稿提出の締め切りを守れそうで、少しホッとしている私だったりします。今回もありがとうございました。次回も頑張りますので、どうぞよろしくお願いします。