しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。年明け早々の大規模なパフォーマンス検証に没頭し、Oracleデータベースと自分自身に負荷をかけ続けている、しばちょう”こと柴田長（しばた つかさ）です。
前回はパーティション化による問合せのパフォーマンス向上を体験して頂きましたが、今回は削除処理のパフォーマンス向上を取り上げたいと思います。問合せのパフォーマンスよりも華やかさには欠けますが、データベースを運用管理する上ではデータの削除は必須なオペレーションですから、是非この機会に学んでおきましょう。
もちろん、近年のハードディスク・ドライブの大容量化や法制度等により、業務データを保持する期間が延びてきている傾向があると思いますが、そのように大規模化したデータベースでこそ、今回の体験が必ず生きてくると私は考えています。表定義や格納レコードの特性によって異なりますが、一般的には表のレコード数が多くなればなるほど削除処理（DELETE文）の実行時間が長時間化する可能性が高まるからですね。
以下の演習をOracle Database 11g Release 2 Enterprise Editionのデータベースで試してみてください。前回と同様に、リアルタイムSQL監視レポートの取得や表のオンライン再定義、さらにはオプティマイザ統計情報の収集等の復習も含めてありますので、スキル定着度もチェックして頂いけると思います。
1. 次のSQLを実行し、約1200MB（一ヵ月100MBの12ヶ月分）のレコードが格納されている表TAB26を作成してください。
$ sqlplus / as sysdba
SQL>
-- TBS26表領域と、この表領域をデフォルト表領域とするTRYユーザーの作成
create tablespace TBS26 datafile '＋DATA(DATAFILE)' size 2520m ;
create user TRY identified by TRY default tablespace TBS26 ;
grant connect, resource, dba to TRY ;
-- TAB26表の作成とデータ・ローディング
connect TRY/TRY
create table TAB26 (COL1 number not null, COL2 date, COL3 number, COL4 char(2000)) ;
insert /*+append */ into TAB26 select LEVEL, to_date('2014/01/01','YYYY/MM/DD') + mod(LEVEL, 365), dbms_random.value(1,100), 'hoge' from DUAL connect by LEVEL <= 3 * 128 * 100 * 12 ;
commit;
create unique index IDX_TAB26_COL1_COL2 on TAB26(COL1, COL2) ;
alter table TAB26 add primary key (COL1, COL2) using index ;
--TAB26表に格納されているレコードのCOL2列の最小日付、最大日付を確認
alter session set NLS_DATE_FORMAT='YYYY/MM/DD' ;
select min(COL2),max(COL2) from TAB26 ;
MIN(COL2) MAX(COL2)
---------- ----------
2014/01/01 2014/12/31
-- TRYスキーマ内のセグメント・サイズを確認
set linesize 150 pagesize 50000
col SEGMENT_NAME for a12
col PARTITION_NAME for a16
col TABLESPACE_NAME for a16
select SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024
from USER_SEGMENTS
where SEGMENT_NAME = 'TAB26' ;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024
------------ ---------------- ------------------ ---------------- ---------------
TAB26 TABLE TBS26 1216
はい。前回とほぼ同じSQL文で通常表（非パーティション表）であるTAB26表を作成して頂きました。INSERT文で12ケ月文のレコードを作成している部分については、前回（第25回）の演習1で解説させて頂いているので、今回は割愛させて頂きます。
2. TAB26表のオプティマイザ統計情報収集を取得して下さい。
$ sqlplus TRY/TRY SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TRY', tabname => 'TAB26') ;
オプティマイザ統計情報と言えば、DBMS_STATSパッケージですね。そして、使用頻度が上位に入るであろう、表単位で統計情報を収集するプロシージャは、GATHER_TABLE_STATSプロシージャです。基本的なオペレーションですから、もちろん覚えていましたよね？という、プレッシャーをかけておきます！ ただし、こっそりと復習をしておきたい方は、次の記事を参照してみてくださいね。第8回 オプティマイザ統計情報の管理 ～統計収集の高速化を体験してみる～
ちなみに前回の記事では、このオプティマイザ統計情報の収集をしていませんでした。正直言うと、私自身がこの作業を忘れてしまっていたからです。。。とは言え、そのようにオプティマイザ統計情報が存在しない場合、最新のOracleデータベースにおいては、Dynamic Sampling（動的サンプリング）が自動的に実行されるように初期化パラメータ”OPTIMIZER_DYNAMIC_SAMPLIG”がデフォルトでレベル「2」に設定されていますから、格納されているレコードの特性にある程度正しい実行計画が選択されることになります。
この機能を簡単に紹介しておくと、SQL文をハードパースする際に、対象表の一部のデータを実際に読み込んで、その表に格納されているレコードの特性（オプティマイザ統計情報=カーディナリティ等）を把握します。そして、その情報を元にSQLの実行計画を作成されるという流れです。つまり、Disk I/Oが発生しますし、ハードパースに要する時間が長くなる可能性がありますので、過度なDynamic Samplingは控えるように注意してください。実際に、オプティマイザ統計情報を全く取得していないOLTP系のデータベースにおいて、非常に多くのSQL文でDynamic Samplingが実行されて、パフォーマンスが劣化するというトラブル対応があったことを思い出しましたので、オプティマイザ統計情報をしっかりと取得しておくことが基本ということを忘れないで下さいね。
ちなみに、この初期化パラメータの設定レベルが「0」の場合には動的サンプリングが実行されませんが、「1~10」を指定した場合には、10段階で動的サンプリングが実行される場面やサンプリングするデータ量が変化します。
3. 2014年2月分のレコードを全て削除し、その実行時間を測定してください。また、リアルタイムSQL監視のアクティブ・レポートを生成し、実行計画も確認しておきましょう。
$ sqlplus /nolog
SQL> -- 性能検証前の準備（バッファ・キャッシュと共有プールのフラッシュ）
connect / as sysdba
alter system flush buffer_cache ;
alter system flush shared_pool ;
SQL> -- 非パーティション表における削除処理のパフォーマンス測定
connect TRY/TRY
set timing on
delete /*+MONITOR */ from TAB26
where COL2 >= to_date('2014/02/01','YYYY/MM/DD')
and COL2 < to_date('2014/03/01','YYYY/MM/DD') ;
35364 rows deleted.
Elapsed: 00:00:16.06
SQL> -- リアルタイムSQL監視のActiveレポートの取得
set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool sqlmon_nopart_delete.html
select dbms_sqltune.report_sql_monitor(type=>'active') from dual;
spool off
SQL> -- 演習のシナリオ上、レコードを残しておく為に一旦ロールバック
rollback ;
さてさて、12ケ月文のレコードが格納されている非パーティション表に対して、一ヵ月分のレコードだけを削除して頂きました。参考までに16秒間という数字を出していますが、これは私のテスト環境で値です。より長いレコード長、かつ多くのレコードが格納されている表ではもっと時間を要することになりますので、環境によって変化することはご理解下さいね。
では、リアルタイムSWL監視のActiveレポートを少し見てみましょう。
前回同様に、IO Statistics枠内のIO Bytesを確認してみると、115MB程度しかDiskから読み込んでいないことが理解できます。これは、前回の記事を体験しておられた方にとっては、少し意外な結果かもしれませんね。と言うのも、非パーティション表のサイズは1200MB以上ですが、その1/12である値だからです。「パーティション表にしていないのにどうして？」という感じかなと。
そこで、実行計画を見てみましょう。すると、どうでしょうか？
前回の問合せパフォーマンス時には「TABLE ACCESS FULL」のオペレーションでしたが、今回は「INDEX SKIP SCAN」となっています。これはオプティマイザ統計情報を取得したことも一つの要因だったりするのですが、そもそも索引の構成が少し異なるからなのですね。演習1で作成した索引IDX_TAB26_COL1_COL2を見直して頂くと、今回のDELETE文のWhere句で使用した日付列COL2が二番目のカラムとして含まれている複合索引となっていたのですねー。すいません。仕込んでおきました。そして、一番目のカラムではないので、良く皆さんが目にする索引の範囲検索「INDEX RANGE SCAN」とはならないのです。2番目のカラムを使用する場合にはBツリーの構造的には読み飛ばす必要が出てきますから、「INDEX SKIP SCAN」しながら、2月のレコードを探索するという処理になったという訳です。
この段階で勘の鋭い方は、「この処理をパーティション化しても意味がないのでは？」と思っているかもしれませんね。その勘は非常に正しいです。ただし、その予想を覆させて頂きますので、最後までお付き合いくださいね。逆に、経験豊富なDBAの方は、「あー、この後の流れが読めた！」と、ネタばれしていると思いますが、お付き合いください。。。
4. 表のオンライン再定義を使用して、表TAB26を次の条件に従ってパーティション化して下さい。 条件：日付型データが格納されているCOL2列をパーティション化キーとした月単位の時間隔パーティション化
$ sqlplus TRY/TRY
SQL>
-- オンライン再定義の実行可否の確認
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('TRY', 'TAB26', DBMS_REDEFINITION.CONS_USE_PK, NULL);
END;
/
-- データ移行先となる仮表（時間隔パーティション表）の作成
create table TAB26_TMP (COL1 number not null, COL2 date, COL3 number, COL4 char(2000))
partition by range(COL2) interval(NUMTOYMINTERVAL(1, 'month'))
(partition P201401 values less than (to_date('2014/02/01', 'YYYY/MM/DD'))) ;
create unique index IDX_TAB26P_COL1_COL2 on TAB26_TMP(COL1, COL2) local ;
alter table TAB26_TMP add primary key(COL1, COL2) using index ;
-- オンライン再定義によるレコードのコピーを開始
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'TRY',
orig_table => 'TAB26',
int_table => 'TAB26_TMP',
options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/
-- オンライン再定義による依存オブジェクトのクローニングを開始
/* 今回は省略*****
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname => 'TRY',
orig_table => 'TAB26',
int_table => 'TAB26_TMP',
ignore_errors => TRUE,
num_errors => num_errors);
END;
/
***** */
-- オンライン再定義によるレコードの差分同期を実行
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TRY', 'TAB26', 'TAB26_TMP');
END;
/
-- オンライン再定義の完了処理（表名の付け替え）
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('TRY', 'TAB26', 'TAB26_TMP');
END;
/
はい、この作業は慣れましたよね。完全に前回（第25回）と同じですから処理の説明は割愛させて頂きますね。
最近、このオンライン再定義に関しての質問を頂く機会が増えてきたように感じます。データベースのサイズが大きくなればメンテナンス時間も長くなる傾向がありますから、オンライン業務を停止することなく作業したいという要望は、今後より強く求められるスキルとなると予想されます。是非、この機会に表のオンライン再定義をマスターしておいて頂ければと思います。
演習問題とはしませんでしたが、パーティション化された表の情報を載せておきます。今回もインターバル・パーティションを使用して、一ヵ月単位でのパーティションに分割しています。
-- パーティションの種類を確認
set linesize 150
col TABLE_NAME for a10
col INTERVAL for a32
select TABLE_NAME, PARTITIONING_TYPE, PARTITION_COUNT, PARTITIONING_KEY_COUNT, INTERVAL
from USER_PART_TABLES
where TABLE_NAME = 'TAB26' ;
TABLE_NAME PARTITION PARTITION_COUNT PARTITIONING_KEY_COUNT INTERVAL
---------- --------- --------------- ---------------------- --------------------------------
TAB26 RANGE 1048575 1 NUMTOYMINTERVAL(1, 'MONTH')
-- パーティション化キーの列名を確認
col COLUMN_NAME for a24
select * from USER_PART_KEY_COLUMNS
where NAME = 'TAB26'
and OBJECT_TYPE = 'TABLE' ;
NAME OBJEC COLUMN_NAME COLUMN_POSITION
------------------------------ ----- ------------------------ ---------------
TAB26 TABLE COL2 1
-- 時間隔パーティション表TAB25に所属する各パーティションの範囲の定義を確認してください。
set linesize 150 pagesize 50000
col TABLE_NAME for a5
col PARTITION_NAME for a8
select TABLE_NAME, PARTITION_NAME, HIGH_VALUE
from USER_TAB_PARTITIONS
where TABLE_NAME = 'TAB26' ;
TABLE PARTITIO HIGH_VALUE
----- -------- --------------------------------------------------------------------------------
TAB26 P201401 TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P161 TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P162 TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P163 TO_DATE(' 2014-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P164 TO_DATE(' 2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P165 TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P166 TO_DATE(' 2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P167 TO_DATE(' 2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P168 TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P169 TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P170 TO_DATE(' 2014-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P171 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
5. パーティション化したTAB26表の統計情報を収集後、2014年2月分のレコードを全て削除し、その実行時間を測定してください。
$ sqlplus /nolog
SQL> -- オプティマイザ統計情報を収集
connect TRY/TRY
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TRY', tabname => 'TAB26') ;
SQL> -- 性能検証前の準備（バッファ・キャッシュと共有プールのフラッシュ）
connect / as sysdba
alter system flush buffer_cache ;
alter system flush shared_pool ;
SQL> -- パーティション表における削除処理のパフォーマンス測定
connect TRY/TRY
set timing on
delete /*+MONITOR */ from TAB26
where COL2 >= to_date('2014/02/01','YYYY/MM/DD')
and COL2 < to_date('2014/03/01','YYYY/MM/DD') ;
Elapsed: 00:00:14.53
-- SQL監視レポートの取得
set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool sqlmon_part_delete.html
select dbms_sqltune.report_sql_monitor(type=>'active') from dual;
spool off
SQL> -- 演習のシナリオ上、レコードを残しておく為に一旦ロールバック
rollback ;
さあ、パーティション化の効果は？と意気込んで実行して頂いた方、ありがとうございます。もちろん、環境にも依存しますので一概には言えませんが、残念ながら結果には満足しなかった方が多いのではないでしょうか？とは言え、既に演習3の解説部分で前振りをしていたので、この後の期待感の方が強いかもしれませんね。
まずは、今回は何故、パーティション化によってDELETE処理があまり高速化しなかったのかをリアルタイムSQL監視のActiveレポートを使用して確認してみましょう。
いつも通り、このSQLでDiskから読み込んだデータを確認してみると94MBであり、非パーティション表の場合（115MB）よりも小さくはなっていますが、12分割したパーティション化によって1/12にはなっていません。とは言え、2月のパーティションのサイズは、表全体の1200MBの1/12ですから約100MBと考えれば、94MBよりも小さくなると期待することは不可能でしょう。つまり、非パーティション化の段階において「INDEX SKIP SCAN」を使用することで十分なI/O削減、高速化が行われていたので、パーティション化の効果が薄かった。という例になります。
と言う事で、「今回の連載は終わりです。」とはなりませんので、ご安心（?）ください。ここまで表の一部のレコードを削除する為に、DELETE文を使用してきましたが、本当に、DELETE文しか出来ないのでしょうか？ 答えは「No！」ですね。パーティション化された表では、DROP PARTITION句を使用して、一部のパーティションを丸ごと削除することが出来ます。つまり、2月のレコードが格納されたパーティションだけを表から削除できるようになりますので、早速、次の演習6で試してみましょう。
ちなみに、今回の演習とは関係ありませんが、DELETE文以外でレコードを削除するもう一つの方法は、「CREATE TABLE as SELECT文を使用して必要なレコードのみで別表を作成+元表をDROP」ですね。機会があれば是非体験して頂きたいと思います。
6. alter table DROP PARTITION文を使用して、2014年2月分のレコードを全て削除して下さい。
$ sqlplus /nolog
SQL> -- 不要だが、これまでの検証の前提条件を揃える目的で実行しておく
connect / as sysdba
alter system flush buffer_cache ;
alter system flush shared_pool ;
SQL> -- drop partitionの実行
connect TRY/TRY
alter table TAB26 drop partition for (to_date('2014/02/01', 'YYYY/MM/DD')) ;
-- alter table TAB26 drop partition SYS_P161 ; と同じ意味
Elapsed: 00:00:00.37
SQL> -- 2月分のレコードが存在しないことを確認
select count(*) from TAB26
where COL2 >= to_date('2014/02/01','YYYY/MM/DD')
and COL2 < to_date('2014/03/01','YYYY/MM/DD') ;
COUNT(*)
----------
0
SQL> -- 2月分のレコードを格納するパーティションが消えていることを確認
set linesize 150 pagesize 50000
col TABLE_NAME for a5
col PARTITION_NAME for a8
select TABLE_NAME, PARTITION_NAME, HIGH_VALUE
from USER_TAB_PARTITIONS
where TABLE_NAME = 'TAB26' ;
TABLE PARTITIO HIGH_VALUE
----- -------- --------------------------------------------------------------------------------
TAB26 P201401 TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P162 TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P163 TO_DATE(' 2014-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P164 TO_DATE(' 2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P165 TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P166 TO_DATE(' 2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P167 TO_DATE(' 2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P168 TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P169 TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P170 TO_DATE(' 2014-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TAB26 SYS_P171 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
どうでしょう?凄いですよね。0.36秒で2月分のレコードを削除できてしまいました。
DDL文ですから削除対象のレコード（ブロック）を読み込む必要はなく、管理ブロックのみの変更で済むので高速なのですね。これは、非パーティション表において全件削除する際に、DELETE文とTRUNCATE文のどちらが高速化という比較を体験している方であれば、スムーズにご納得いただける結果だと思います。
ちなみに、今回はDROP PARTITION句を使用して頂きましたので、2月分のレコードを格納していたパーティションの定義から消えてしまった事が、USER_TAB_PARTITIONSビューから確認することが出来ますが、TRUNCATE PARTITION句を使用すれば、TRUNCATE文と同様に、入れ物（定義）は残したまま、中のレコードを全て削除することが可能ですので、こちらも試して頂ければと思います。
最後に、もう一点、あまり知っている方が少ないポイントをお伝えしておきます。DROP PARTITIONを実行する際には表名だけではなく、対象とするパーティション名を指定する必要がありますが、パーティション名を調べるのが面倒な私は(1)を良く使用します。一般的には、具体的なパーティション名を指定する(2)が多いのかと思います。
(1) alter table TAB26 drop partition for (to_date('2014/02/01', 'YYYY/MM/DD')) ;
(2) alter table TAB26 drop partition SYS_P161 ;
(2)の場合、USER_TAB_PARTITIONSビューを使用してパーティション名を調べるオペレーションが追加で必要となりますが、(1)の場合には、DROP PARTITIONしたいパーティションに含まれている一つのレコードを「PARTITION FOR ()」句で指定する形となりますので、上記では2月1日のレコードを含むパーティションを指定したことになります。これは非常に便利なコマンドですから、是非活用して頂ければと思います。
さて、いかがでしたでしょうか？データ削除という当たり前のオペレーションであっても、非常に興味深い体験をして頂けたのではないでしょうか？
パーティション機能はSQL文に透過的なので、パーティション化することによってアプリケーションやメンテナンスのSQLを書き換える必要が無いと言う優れたメリットを持っています。しかしながら、DROP PARTITION文のように、パーティション化することで初めて使用可能となる便利なSQLが存在することも事実です。つまり、これまで使用してきたSQL文でも問題はありませんが、よりパーティション化のメリットを引き出す為には、パーティション表独自のSQL文に置き換えることで高速化可能な処理があるかもしれないということをお伝えしたかったのです。
ちなみに、演習の中で、何故1月分のレコードでは無く、2月分のレコードを削除対象しているのか解りますか？もし、答えが解っている方は、インターバル・パーティションの動作を極めている方でしょう。インターバル・パーティションは少しだけクセのある機能ですから今後体験して頂ける機会を作りたいと考えております。
今回も最後まで体験して頂きまして、ありがとうございました。次回以降もどうぞよろしくお願い致します。