しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。”しばちょう”こと柴田長（しばた つかさ）です。
今回からOracle Database 12cを使用した演習をお届けしていこうと思います。その初回として、パーティション表の一部のパーティションをオンラインで表領域を移動させたり圧縮 させたりすることが可能となる「オンラインでのパーティション移動」を体験して頂きましょう。Oracle Database 11gまでであれば、第18回でご紹介した表のオンライン再定義でDML文の遅延を回避することが出来ましたが、オンライン再定義を使用するには複数 のプロシージャを順番にコールしていく形でしたよね。今回ご紹介するOracle Database 12cの新機能「オンラインでのパーティション移動」は、一つのDDL文だけで実現 可能な便利な機能です。Oracle Database 12cの主要な新機能では無いですが、データベース管理者が今すぐにでも使用できる機能ですから、是非この機会にマスターしておきましょう。
以下の演習をOracle Database 12c Release 1 (12.1.0.2) 以降のデータベースで試してみてください。
なお、Oracle Database 12c Release 1 (12.1.0.2) の単一インスタンス・データベースのインストレーション・ガイド及び、Oracle VM VirtualBoxを用いたOracle Database 12c Release 1 環境の構築ガイドが、Oracle Technology Networkのこちらのページに公開されておりますので、参考にしてみてください。
- 【今回ご紹介するネタ一覧（逆引き）】
- ✓ 従来のパーティション移動と課題（演習2）
- ✓ 従来から存在するUPDATE INDEXES句の効果と注意点（演習3）
- ✓ 初期化パラメータSKIP_UNUSABLE_INDEXES（演習4）
- ✓ Oracle Database 12cの新機能「オンラインでのパーティション移動（ONLINE句）」の効果（演習5）
1. 次のSQLを実行し、2つの表領域（400MBの表領域TBS41_HIGH、400MBの表領域TBS41_LOW）を作成し、その表領域TBS41_HIGH上にパーティション表TAB41作成してください。パーティション表TAB41は日付型データが格納されているCOL2列をパーティション化キーとした月単位の時間隔パーティションとし、約300MB（3ヵ月分）のレコードを挿入してください。
$ sqlplus / as sysdba
SQL>
-- 表領域TBS41_HIGHと表領域TBS41_LOWの作成
create tablespace TBS41_HIGH datafile '+DATA(DATAFILE)' size 400m ;
create tablespace TBS41_LOW datafile '+FRA(DATAFILE)' size 400m ;
-- 表領域TBS41_HIGHをデフォルト表領域とするTRYユーザーの作成
create user TRY identified by TRY12345 default tablespace TBS41_HIGH ;
grant CREATE SESSION, CREATE TABLE to TRY ;
alter user TRY quota unlimited on TBS41_HIGH
quota unlimited on TBS41_LOW ;
SQL> @recreateTAB41.sql
-- パーティション表の作成
connect TRY/TRY12345
drop table TAB41 purge ;
create table TAB41 (COL1 number not null, COL2 date, COL3 number,
COL4 number, COL5 char(1000))
tablespace TBS41_HIGH
partition by range(COL2) interval(NUMTOYMINTERVAL(1, 'month'))
(partition P201508 values less than (to_date('2015/09/01', 'YYYY/MM/DD'))) ;
-- 約300MBのレコードを表TAB41へINSERT
insert /*+append */ into TAB41
select LEVEL,
to_date('2015/08/01','YYYY/MM/DD') + mod(LEVEL, 31+30+31-1),
dbms_random.value(1,100),
dbms_random.value(1,100),
'hoge' || mod(LEVEL, 5)
from DUAL connect by LEVEL <= 6 * 128 * 300 ;
commit;
-- 主キー及び索引（グローバル索引及びローカル索引）の作成
create unique index PK_TAB41_COL1_COL2 on TAB41(COL1, COL2) local ;
alter table TAB41 add primary key (COL1, COL2) using index ;
create index IDX_TAB41_COL3 on TAB41(COL3, COL2) ;
create index IDX_TAB41_COL4 on TAB41(COL4, COL2) local ;
-- 作成したセグメントの確認
set linesize 150 pagesize 50000
col SEGMENT_NAME for a24
col PARTITION_NAME for a16
col TABLESPACE_NAME for a16
select SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE,
TABLESPACE_NAME, BYTES/1024/1024 "MB"
from USER_SEGMENTS ;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME MB
------------------- ---------------- ------------------ ---------------- ---
TAB41 P201508 TABLE PARTITION TBS41_HIGH 104
TAB41 SYS_P516 TABLE PARTITION TBS41_HIGH 104
TAB41 SYS_P517 TABLE PARTITION TBS41_HIGH 104
PK_TAB41_COL1_COL2 P201508 INDEX PARTITION TBS41_HIGH 8
PK_TAB41_COL1_COL2 SYS_P521 INDEX PARTITION TBS41_HIGH 8
PK_TAB41_COL1_COL2 SYS_P522 INDEX PARTITION TBS41_HIGH 8
IDX_TAB41_COL3 INDEX TBS41_HIGH 12
IDX_TAB41_COL4 P201508 INDEX PARTITION TBS41_HIGH 8
IDX_TAB41_COL4 SYS_P523 INDEX PARTITION TBS41_HIGH 8
IDX_TAB41_COL4 SYS_P524 INDEX PARTITION TBS41_HIGH 8
毎度お馴染みの検証環境の構築ですね。パーティション表TAB41は以降の演習で繰り返し再作成し直しますので、パーティションの作成から作成したセグメントの確認までのSQL文が書き込まれた「recreateTAB41.sql」ファイルを用意しておくと便利です。もちろん、今回のように条件を変えて繰り返し検証を行う場合には、第19回でご紹介したFlashback Loggingを有効化 or 保証付きリストア・ポイントを作成しておいて演習毎にFlashback Databaseを実行して巻き戻す方法が一番カッコイイですよね。
2. パーティション表TAB41において、「alter table」文を使用して最も古いデータが格納されているパーティションを別の表領域TBS41_LOWへ移動して下さい。ただし、その移動中に、一件INESRT文と主キーを使用した一件UPDATE文をそれぞれ別のセッションから実行して、影響を確認して下さい。
$ sqlplus /nolog
SQL> -- sessionA
connect TRY/TRY12345
set timing on time on
select sys_context('USERENV','SID') from DUAL;
SYS_CONTEXT('USERENV','SID')
----------------------------
266
| $ sqlplus /nolog
| SQL> -- sessionB
| connect TRY/TRY12345
| set timing on time on
| select sys_context('USERENV','SID') from DUAL;
|
| SYS_CONTEXT('USERENV','SID')
| ----------------------------
| 265
| $ sqlplus /nolog
| SQL> -- sessionC
| connect TRY/TRY12345
| set timing on time on
| select sys_context('USERENV','SID') from DUAL;
|
| SYS_CONTEXT('USERENV','SID')
| ----------------------------
| 31
-- sessionA
alter table TAB41 move partition P201508 tablespace TBS41_LOW ;
| -- sessionB
| insert into TAB41
| values(0,to_date('2015/08/01','YYYY/MM/DD'),0,0,'sibacho');
| -- sessionC
| update TAB41 set COL5='sibacho'
| where COL1=1
| and COL2=to_date('2015/08/02','YYYY/MM/DD');
-- sessionA
Table altered.
| -- sessionB
| ORA-01502: index 'TRY.PK_TAB41_COL1_COL2' or partition of such index
| is in unusable state
| -- sessionC
| 1 row updated.
まず、この演習2ではOracle Database 11g Release 2までのパーティション移動での課題について再度確認しておきましょう。課題があるからこそ、新機能が生まれるのです！！
でもって、この演習では3つのセッションを使用します。一つ目はalter table move partition文を実行する[sessionA]、二つ目は移動対象のパーティション・セグメントに一件レコード挿入するinsert文を実行する[sessionB]、三つ目は移動対象のパーティション・セグメント内の一件のレコードを更新するupdate文を実行する[sessionC]です。上記の回答例では、それぞれのセッションで実行したコマンドや結果について時系列で表示させたく、インデントをズラシテ表示させて頂いております。
演習の結果としては、 [sessionA]でalter table move partition文を実行した直後に、[sessionB]でinsert文を実行、さらに[sessionC]でupdate文を実行していて、[sessionA]のalter table move partition文が完了したタイミングで[sessionB]にORA-1502が戻り、[sessionC]は一件更新されたという結果が表示されています。と言うように、読み取れるかと思います。お願いしますm(_ _)m
この従来のパーティション移動での課題は何か？
答えは簡単ですよね。「DML文がパーティションの移動が完了するまで待たされる」という事が一番始めに挙げられるかと思います。まあ、これは良いとしても、insert文ではORAエラー（ORA-1502）まで発生してしまっている点がをもう少し分析してみると面白いです。と言うもの、何故、ORA-1502が発生しているのかは分かりますか？と言われて、新人君達はアタフタする傾向がありますね。大切なのはエラー番号だけじゃなく、エラー・メッセージも落ち着いて読んでみましょうね。そうですね。索引PK_TAB41_COL1_COL2が「unusable」なステータスになっていると書いてありますよね。なので、USER_INDEXESビューやUSER_IND_PARTITIONSビューを参照してみましょう。
$ sqlplus /nolog SQL> connect TRY/TRY12345 col INDEX_NAME for a20 col INDEX_TYPE for a10 col PARTITIONED for a12 select INDEX_NAME, INDEX_TYPE, PARTITIONED, STATUS from USER_INDEXES ; INDEX_NAME INDEX_TYPE PARTITIONED STATUS -------------------- ---------- ------------ -------- PK_TAB41_COL1_COL2 NORMAL YES N/A IDX_TAB41_COL3 NORMAL NO UNUSABLE IDX_TAB41_COL4 NORMAL YES N/A col INDEX_NAME for a20 col INDEX_TYPE for a10 col PARTITIONED for a12 select INDEX_NAME, PARTITION_NAME, STATUS from USER_IND_PARTITIONS order by 1,2 ; INDEX_NAME PARTITION_NAME STATUS -------------------- ---------------- -------- PK_TAB41_COL1_COL2 P201508 UNUSABLE PK_TAB41_COL1_COL2 SYS_P521 USABLE PK_TAB41_COL1_COL2 SYS_P522 USABLE IDX_TAB41_COL4 P201508 UNUSABLE IDX_TAB41_COL4 SYS_P523 USABLE IDX_TAB41_COL4 SYS_P524 USABLE
如何ですか？見事にUNUSABLEなステータスの索引セグメントが存在していますよね。なので、新たなレコードを挿入しようと試みた場合、主キーである索引をメンテナンスすることが出来ない為にinsert文でORA-1502が発生していたと言う事ですね。って、知ってるわ！！だと思います。なぜならば、第27回 パーティション表の管理～ILMにおける表データの圧縮と索引の再構成～において解説済みの内容ですからね。
と過去記事に言及していても進歩は無いので、もう一歩進んでみましょう。
[sessionB]のinsert文が失敗した理由は分かりました。しかし、[sessionC]のupdate文は成功していますよね。しかも、そのupdate文はwhere句でCOL1列とCOL2列を条件として指定しているので、insert文がORA-1502を発生させたUNUSABLEなステータスになっている索引PK_TAB41_COL1_COL2を使用しているのではないかと。よくよく考えてみると不思議に思いませんか？と言う事で、update文の実行計画を確認してみましょう。
$ sqlplus /nolog
SQL> connect TRY/TRY12345
explain plan for
update TAB41 set COL5='sibacho'
where COL1=1 and COL2=to_date('2015/08/02','YYYY/MM/DD') ;
@?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 3397589673
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 108 | 108K| 3617 (1)| 00:00:01 | | |
| 1 | UPDATE | TAB41 | | | | | | |
| 2 | PARTITION RANGE SINGLE| | 108 | 108K| 3617 (1)| 00:00:01 | 1 | 1 |
|* 3 | TABLE ACCESS FULL | TAB41 | 108 | 108K| 3617 (1)| 00:00:01 | 1 | 1 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("COL1"=1 AND "COL2"=TO_DATE(' 2015-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
じゃーん！！索引PK_TAB41_COL1_COL2は使用していませんね。と言うよりも、どの索引も使用せず「TABLE ACCESS FULL」のオペレーションが表示されていることから、全表検索が行われてしまっていますよ。一つの救いとしては、[Pstart]と[Pstop]がそれぞれ1であることからパーティション・プルーニング（第25回 パーティション化による問合せのパフォーマンス向上）が効いている事でしょうか。
整理すると、従来のパーティション移動（UPDATE INDEXES句無し）での課題は次の3点になりますかね。
- ✓ 対象パーティションへのDML文がパーティションの移動が完了するまで待機させられる
- ✓ 索引がUNUSABLEとなる為、対象パーティションへのINSERT文が失敗する
- ✓ 索引がUNUSABLEとなる為、対象パーティションへのUPDATE文が全表検索となる
上記で「UPDATE INDEXES句無し」と表現したと言う事は、「UPDATE INDEXES句有り」のケースでは課題が変わってくる事を臭わせていますね。勘の鋭い方は、alter table move partition文にupdate indexes句を付けると、索引がUNUSABLEにならなくなるのよね？と思いますよね。はい、その通りなのですが・・・
3. 演習2の「alter table」文に「update indexes」句を追加し、演習2を再実行して下さい。(表TAB41は演習1が完了した直後の状態であることが前提です)
$ sqlplus /nolog
SQL> @recreateTAB41.sql
SQL> -- sessionA
connect TRY/TRY12345
set timing on time on
select sys_context('USERENV','SID') from DUAL;
SYS_CONTEXT('USERENV','SID')
----------------------------
266
| $ sqlplus /nolog
| SQL> -- sessionB
| connect TRY/TRY12345
| set timing on time on
| select sys_context('USERENV','SID') from DUAL;
|
| SYS_CONTEXT('USERENV','SID')
| ----------------------------
| 32
| $ sqlplus /nolog
| SQL> -- sessionC
| connect TRY/TRY12345
| set timing on time on
| select sys_context('USERENV','SID') from DUAL;
|
| SYS_CONTEXT('USERENV','SID')
| ----------------------------
| 259
-- sessionA
alter table TAB41 move partition P201508 tablespace TBS41_LOW update indexes ;
| -- sessionB
| insert into TAB41
| values(0,to_date('2015/08/01','YYYY/MM/DD'),0,0,'sibacho');
| -- sessionC
| update TAB41 set COL5='sibacho'
| where COL1=1
| and COL2=to_date('2015/08/02','YYYY/MM/DD');
| -- sessionB
| ORA-01502: index 'TRY.PK_TAB41_COL1_COL2' or partition of such index
| is in unusable state
| -- sessionC
| 1 row updated.
|
| SQL> commit ;
| Commit complete.
-- sessionA Table altered.
はい、従来のパーティション移動において、「UPDATE INDEXES句」を付けてみた結果は上記の通りです。この結果だけでも説明は出来ますが皆様がご納得出来るとも思えないので、3つのセッションの待機状況についてV$ACTIVE _SESSION_HISTORYビューへ問い合わせた結果と合わせて解説していきたいと思います。
$ sqlplus / as sysdba SQL> -- Active Session Historyで、待機状況を確認 set linesize 200 set pages 5000 col EVENT for a32 select SAMPLE_TIME, SESSION_ID, EVENT, TIME_WAITED, BLOCKING_SESSION from V$ACTIVE_SESSION_HISTORY where SESSION_ID in (266, 32, 259) and SAMPLE_TIME > SYSTIMESTAMP - 20/60/24 order by 1 ; SAMPLE_TIME SESSION_ID EVENT TIME_WAITED BLOCKING_SESSION ------------------------- ---------- ----------------------------- ----------- ---------------- 30-AUG-15 05.27.19.703 AM 266 0 30-AUG-15 05.27.20.703 AM 259 enq: TM - contention 3595168 266 30-AUG-15 05.27.20.703 AM 32 enq: TM - contention 4025395 266 30-AUG-15 05.27.20.703 AM 266 0 30-AUG-15 05.27.21.713 AM 32 0 30-AUG-15 05.27.21.713 AM 259 0 30-AUG-15 05.27.22.713 AM 259 direct path read 17433 30-AUG-15 05.27.22.713 AM 266 db file sequential read 9066 30-AUG-15 05.27.23.723 AM 266 enq: TX - row lock contention 0 259 30-AUG-15 05.27.24.723 AM 266 enq: TX - row lock contention 0 259 ......(省略)...... 30-AUG-15 05.28.40.003 AM 266 enq: TX - row lock contention 0 259 30-AUG-15 05.28.41.003 AM 266 enq: TX - row lock contention 78682283 259 30-AUG-15 05.28.42.013 AM 266 db file scattered read 9126 30-AUG-15 05.28.43.023 AM 266 0
[sessionA(ID:266)]でalter table move partition update indexes文を実行した直後に、[sessionB(ID:32)]でinsert文を実行、さらに[sessionC(ID:259)]でupdate文を実行している。
[sessionB(ID:32)] と[sessionC(ID:259)]は待機イベント”enq: TM – contention”で[sessionA(ID:266)] を待機している（V$ACTIVE_SESSION_HISTORYの出力結果の赤文字部分で、BLOCKING_SESSION列が266となっていることからこのように理解できる）。
[sessionA(ID:266)]で表セグメントの移動処理が完了したタイミング（恐らく）で、待機イベント”enq: TM – contention”から解放された [sessionB(ID:32)]がinsert処理を試行するが、索引PK_TAB41_COL1_COL2がUNUSABLEなステータス（UPDATE INDEXES句有りだが、この時点では未だ索引Rebuildが行われていない）の為、ORA-1502が発生している。
[sessionC(ID:259)]ではupdate処理を試行するが、索引PK_TAB41_COL1_COL2がUNUSABLEなステータスのため、全表検索を伴う更新処理となっている（V$ACTIVE_SESSION_HISTORYの出力結果の青文字部分で、待機イベント”direct path read”がこれに該当すると推測される）。その結果、一件のレコードの更新に成功しているが、未コミット状態である。
一方、[sessionA(ID:266)]のalter table move partition update indexes文では索引のRebuild処理を試行するが、[sessionC(ID:259)]のupdate文による行ロック（enq: TX – row lock contention）が発生していて、V$ACTIVE_SESSION_HISTORYの出力結果としても、[sessionC(ID:259)]が[sessionA(ID:266)]の処理をブロックしていることが読み取れる。
[sessionC(ID:259)]でcommitを実行することで[sessionA(ID:266)]による索引Rebuildが流れ始めて、最終的に[sessionA(ID:266)]のalter table move partition update indexes文が完了している。
と言う事で、alter table move partition文にUPDATE INDEXES句を付けることで、パーティション移動と共に索引のRebuildを実行することが可能だが、DML文を実行するタイミングによってはこの演習の回答例のような状況になり得ることは理解しておいた方が良いと思います。このような背景から、第27回 パーティション表の管理～ILMにおける表データの圧縮と索引の再構成～の連載において個人的に表のオンライン再定義をお薦めしていた訳でもあります。が、Oracle Database 12cでは新機能「オンラインでのパーティション移動（ONLINE句）」が実装されたと言う事なので、これまで見てきた課題がどれだけ解消するのかを見てみようじゃありませんか！！
と、その前にオマケとして、一点体験しておいて頂きたいことがあります。
それは演習2と演習3のどちらでも課題となった、パーティション移動中に対象パーティションへの一件UPDATE文が全表検索となってしまう現象の回避策です。完璧では無いですが、「全表検索をしてでもupdate文を成功させたい」 or 「全表検索を止める為にupdate文を失敗させてもよい」のどちらかを選ぶことになります。この後者について、次の演習4で体験して頂きましょう。
4. 演習2において、UPDATE文を実行するセッションにおいて初期化パラメータSKIP_UNUSABLE_INDEXESの設定値をFALSEへ変更して再実行し、この初期化パラメータの動作について確認して下さい。(表TAB41は演習1が完了した直後の状態であることが前提です)
$ sqlplus /nolog
SQL> @recreateTAB41.sql
SQL> -- sessionA
connect TRY/TRY12345
set timing on time on
select sys_context('USERENV','SID') from DUAL;
SYS_CONTEXT('USERENV','SID')
----------------------------
266
| $ sqlplus /nolog
| SQL> -- sessionB
| connect TRY/TRY12345
| set timing on time on
| select sys_context('USERENV','SID') from DUAL;
|
| SYS_CONTEXT('USERENV','SID')
| ----------------------------
| 32
| $ sqlplus /nolog
| SQL> -- sessionC
| connect TRY/TRY12345
| set timing on time on
| select sys_context('USERENV','SID') from DUAL;
|
| SYS_CONTEXT('USERENV','SID')
| ----------------------------
| 259
-- sessionA
alter table TAB41 move partition P201508 tablespace TBS41_LOW ;
| -- sessionB
| insert into TAB41
| values(0,to_date('2015/08/01','YYYY/MM/DD'),0,0,'sibacho');
| -- sessionC
| alter session set SKIP_UNUSABLE_INDEXES=FALSE ;
| update TAB41 set COL5='sibacho'
| where COL1=1
| and COL2=to_date('2015/08/02','YYYY/MM/DD');
-- sessionA
Table altered.
| -- sessionB
| ORA-01502: index 'TRY.PK_TAB41_COL1_COL2' or partition of such index
| is in unusable state
| -- sessionC
| ORA-01502: index 'TRY.PK_TAB41_COL1_COL2' or partition
| of such index is in unusable state
さて、如何でしょうか？初期化パラメータSKIP_UNUSABLE_INDEXESをデフォルト値のTRUEからFALSEに設定した場合、[sessionC]のupdate文においてもinsert文と同様にORA-1502を発生するようになりましたね。これによって、想定外の全表検索を抑止出来たことになります。
初期化パラメータSKIP_UNUSABLE_INDEXESがデフォルトのTRUEの場合、UNUSABLEなステータスの索引が存在していても、その索引を使用せずにSQL文を実行させる動作となります。もちろん、ヒント句で明示的に使用する索引が指定されている場合にその索引がUNUSABLEな状態であれば、ORA-01502が返されます。注意点としては、リファレンス・マニュアルにも記載されている通り、UNIQUE制約を実現する索引の場合、新規レコードの挿入のような制約違反になる更新処理ではこの設定は無効となります。よって、演習2においてinsert文側でだけORA-01502が発生した理由はこの通りですね。
さて、お待たせ致しました！ Oracle Database 12cの新機能「オンラインでのパーティション移動（ONLINE句）」を試してみましょう！
5. 演習2の「alter table」文に「online」句を追加して演習2を再実行して下さい。(表TAB41は演習1が完了した直後の状態であることが前提です) ※ 「update indexes」句は付けません。初期化パラメータSKIP_UNUSABLE_INDEXESの設定はデフォルト（TRUE）とします。
$ sqlplus /nolog
SQL> @recreateTAB41.sql
SQL> -- sessionA
connect TRY/TRY12345
set timing on time on
select sys_context('USERENV','SID') from DUAL;
SYS_CONTEXT('USERENV','SID')
----------------------------
266
| $ sqlplus /nolog
| SQL> -- sessionB
| connect TRY/TRY12345
| set timing on time on
| select sys_context('USERENV','SID') from DUAL;
|
| SYS_CONTEXT('USERENV','SID')
| ----------------------------
| 32
| $ sqlplus /nolog
| SQL> -- sessionC
| connect TRY/TRY12345
| set timing on time on
| select sys_context('USERENV','SID') from DUAL;
|
| SYS_CONTEXT('USERENV','SID')
| ----------------------------
| 259
-- sessionA
alter table TAB41 move partition P201508 tablespace TBS41_LOW online ;
| -- sessionB
| insert into TAB41
| values(0,to_date('2015/08/01','YYYY/MM/DD'),0,0,'sibacho');
|
| 1 row created.
| -- sessionC
| update TAB41 set COL5='sibacho'
| where COL1=1
| and COL2=to_date('2015/08/02','YYYY/MM/DD');
|
| 1 row updated.
| SQL> commit ;
| Commit complete.
| SQL> commit ;
| Commit complete.
-- sessionA Table altered.
はい、何も言う事はありませんね。上記の通り、オンラインでパーティションの移動が実現出来ちゃっています。insert文もupdate文も遅延することなく、エラーが発生することもなく、実行出来ていますね。素晴らしい、パチパチパチ。
何も言う事は無いと言っておきながら一点お伝えしておくと、[sessionA]のalter table move partition online文を完了する為には移動中に実行された[sessionB]、及び[sessionC]のトランザクションが完了している必要があります。[sessionC]でupdate文を実行したままCommitもRollbackもせずに放置し続けると、[sessionA]のalter table move partition online文は待機し続けますのでご注意くださいね。
また、alter table move partition online文は「UPDATE INDEXES句」を付けていないにも関わらず、索引が自動的にRebuildされている状態になっていることも次の結果から明らかですね。イイ感じです！
$ sqlplus /nolog SQL> connect TRY/TRY12345 col INDEX_NAME for a20 col INDEX_TYPE for a10 col PARTITIONED for a12 select INDEX_NAME, INDEX_TYPE, PARTITIONED, STATUS, TABLESPACE_NAME from USER_INDEXES ; INDEX_NAME INDEX_TYPE PARTITIONED STATUS TABLESPACE_NAME -------------------- ---------- ------------ -------- ---------------- PK_TAB41_COL1_COL2 NORMAL YES N/A IDX_TAB41_COL3 NORMAL NO VALID TBS41_HIGH IDX_TAB41_COL4 NORMAL YES N/A col INDEX_NAME for a20 col INDEX_TYPE for a10 col PARTITIONED for a12 select INDEX_NAME, PARTITION_NAME, STATUS , TABLESPACE_NAME from USER_IND_PARTITIONS order by 1,2 ; INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME -------------------- ---------------- -------- ---------------- IDX_TAB41_COL4 P201508 USABLE TBS41_HIGH IDX_TAB41_COL4 SYS_P566 USABLE TBS41_HIGH IDX_TAB41_COL4 SYS_P567 USABLE TBS41_HIGH PK_TAB41_COL1_COL2 P201508 USABLE TBS41_HIGH PK_TAB41_COL1_COL2 SYS_P564 USABLE TBS41_HIGH PK_TAB41_COL1_COL2 SYS_P565 USABLE TBS41_HIGH
とは言え、この演習5は単発のINSERT文だけじゃないか。もっと連続でINESRTし続けたら、待機するタイミングが見えるんじゃないの？というご希望にお応えして、次の演習ではオンラインでのパーティション移動中に、対象となるパーティションに対して0.5秒毎にINSERT+Commitを繰り返し実行してみましょう。
6. オンラインでのパーティション移動中に、対象となるパーティションに対して0.5秒毎にINSERT+Commitを繰り返し、INSERT文の実行に遅延が発生し無かったことを確認して下さい。
$ sqlplus /nolog
SQL> @recreateTAB41.sql
SQL> -- sessionA
connect TRY/TRY12345
set timing on time on
| $ sqlplus /nolog
| SQL> -- sessionD
| connect TRY/TRY12345
| set timing on time on
|
| begin
| for i in 1000001..1000100 loop
| insert into TAB41
| values(i,to_date('2015/08/01 '||to_char(SYSDATE,'HH24:MI:SS'),
| 'YYYY/MM/DD HH24:MI:SS'),0,0,'sibacho');
| commit;
| DBMS_LOCK.SLEEP(0.5);
| end loop;
| end;
| /
-- sessionA
alter table TAB41 move partition P201508 tablespace TBS41_LOW online ;
Table altered.
Elapsed: 00:00:07.03
06:10:33 SQL>
SQL> -- sessionDから挿入されたレコードを確認
select COL1, to_char(COL2, 'YYYY/MM/DD HH24:MI:SS')
from TAB41 where COL1>1000001 ;
COL1 TO_CHAR(COL2,'YYYY/
---------- -------------------
......(省略)......
1000025 2015/08/01 06:10:24
1000026 2015/08/01 06:10:25 <-- ここから
1000027 2015/08/01 06:10:25
1000028 2015/08/01 06:10:26
1000029 2015/08/01 06:10:26
1000030 2015/08/01 06:10:27
1000031 2015/08/01 06:10:27
1000032 2015/08/01 06:10:28
1000033 2015/08/01 06:10:28
1000034 2015/08/01 06:10:29
1000035 2015/08/01 06:10:29
1000036 2015/08/01 06:10:30
1000037 2015/08/01 06:10:30
1000038 2015/08/01 06:10:31
1000039 2015/08/01 06:10:31
1000040 2015/08/01 06:10:32
1000041 2015/08/01 06:10:32
1000042 2015/08/01 06:10:33
1000043 2015/08/01 06:10:33 <-- ここまで
1000044 2015/08/01 06:10:34
1000045 2015/08/01 06:10:34
......(省略)......
（良い意味で）残念ながら、何も問題無く実行出来てしまいましたね。alter table move partition online文を実行してから完了するまでの間、各秒には2件のレコードがINSERTされていることが確認できています。もちろん、この演習では数千、数万と言ったトランザクション下で試した訳ではない点をご留意くださいね。
さて、今更ながらではありましたが、Oracle Database 12cの新機能「オンラインでのパーティション移動」は如何でしたでしょうか？マルチテナントやDatabase In-Memoryのように目立たない機能ではありますが、従来の課題を完全に打ち消すという圧倒的な存在感だと私は思います。ちなみに、Oracle Database 12cからはHeat Map / Adaptive Data Optimizationと呼ばれる、データへのアクセス・パターンや頻度に応じて自動的にデータを圧縮、別の表領域への移動を制御する機能が追加されています。なので、今回のようなパーティションの移動を自動で実行してくれるようになってきています。そのような機能についても、今後少しずつご紹介していければと考えております。
と言う事、今回も最後まで体験して頂きましてありがとうございました。是非、感想や質問をお待ちしておりますね。次回以降もどうぞよろしくお願い致します。
(ご質問の方法はこちらにあります)
https://blogs.oracle.com/otnjp/shibacho-index