しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。”しばちょう”こと柴田長（しばた つかさ）です。2月は毎年恒例のOracle Technology Days 2012のセッション・スピーカーとして、高松、広島、名古屋の3ヶ所で講演させて頂きました。受講者の皆さんの集中力に負けないよう90分間必死にご説明させて頂きましたが、途中酸欠気味でクラクラしてしまいました。私のセッション「どこまでチューニングできるのか？最新Oracle Database 高速化手法」も含めて昨年開催されたOracle DBA & Developer Days 2011のコンテンツが元となっており、こちらから資料や動画をダウンロードすることが可能です。是非参考にしてみてください。
さて、第4回目の今回は、前回までの復習+データ領域管理の理解をもう少し掘り下げてみましょう。本番環境のメンテナンス作業や検証環境のデータ準備等で非常に役立つ基本的なスキルですから、しっかり押さえておいて頂きたい部分です。これまでの演習で使用したOracle Database 11g Release 2 Enterprise Editionのデータベースで試してみてください。
■演習0．演習前の環境準備（第1回～3回の演習を実施された方はスキップしてください）
sqlplus / as sysdba SQL> create bigfile tablespace TBS_BIG datafile '/oracle_datafile1/tbs_big.dbf' size 500M; create user TRY identified by TRY; grant CREATE SESSION to TRY; grant CREATE TABLE to TRY; grant CREATE SEQUENCE to TRY; alter user TRY default tablespace TBS_BIG; alter user TRY quota 400M on TBS_BIG; connect TRY/TRY create table TBL1 (col1 number, col2 varchar2(100)) segment creation immediate tablespace TBS_BIG; create table TBL2 segment creation immediate tablespace TBS_BIG as select * from TBL1 where 1=2;
これまでの演習の中で実行して頂いたコマンドがほとんどですが、最後の２行で表を作成する際に指定している「segment creation immediate」句は、第2回でご紹介したOracle Database 11g Release 2の新機能「セグメント作成の遅延」の挙動を設定する初期化パラメータ「DEFERRED_SEGMENT_CREATION」を上書きして、表作成のタイミングでセグメント割り当てを行わせる為の記述です。
■演習1．第3回の演習6の回答例を参考にしてTBL2表へ100万行をINSERTして下さい。
sqlplus TRY/TRY
SQL>
truncate table TBL2;
set echo on
set timing on
@insertTBL2_1M.sql
insert into TBL2
select i+j,rpad(to_char(i+j),100,'A')
from (
with DATA2(j) as (
select 0 j from DUAL
union all
select j+1000 from DATA2 where j < 999000
)
select j from DATA2
),
(
with DATA1(i) as (
select 1 i from DUAL
union all
select i+1 from DATA1 where i < 1000
)
select i from DATA1
);
1000000行が作成されました。
経過: 00:00:11.03
SQL> commit;
はい、完全に前回の復習となりますね。2つの再帰的With句を直積させた副問い合わせの結果をINSERTする例です。構文が複雑で覚えておくのは少し難しいと思うので、私はこういった類の頻繁に使用するSQLは、いつでも使用できるように個人PC内に保存して持ち歩いていたりしますよ。
また、上記の回答例ではINSERT文が記述されたテキスト・ファイル「insertTBL2_1M.sql」を「@（アットマーク）」の後ろに指定することで、INSERT文を実行しています。事前に「set echo on」を宣言したことで、実行時にテキスト・ファイル内のSQLがSQL*Plusの画面に出力されます。
■演習2．TBL2表の第1カラムに主キーを追加してください。また、その実行時間を測定して下さい。
SQL> alter table TBL2 add constraint PK_TBL2_COL1 primary key(COL1); 表が変更されました。 経過: 00:00:02.44
この演習はそれほど難しくは無いと思います。上記の例では主キーに明示的に名前を付けてはいますが、省略（「constraint PK_TBL2_COL1」を省く）することも可能です。その場合にはOracle Database内部で自動的に名称が設定されますが、構成（どの表？どの列？どの制約？）を推測し易い名前を付けておいた方が管理し易くなるというのはDBAとしては基本的なことではありますね。
■演習3．主キーを追加したことで作成されたセグメントを確認して下さい。
SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS
from USER_SEGMENTS
where SEGMENT_NAME like '%TBL2%';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS
------------------------ ------------------ ------------------------------ ----------
TBL2 TABLE TBS_BIG 16384
PK_TBL2_COL1 INDEX TBS_BIG 2176
お馴染みとなってきたデータ・ディクショナリ・ビュー「USER_SEGMENTS」を問い合わせることで、TBL2表以外のセグメント「PK_TBL2_COL1」が作成されており、EGMENT_TYPE列から索引（INDEX）であることが確認できます。つまり、演習2において主キー制約「PK_TBL2_COL1」を作成したことで索引「PK_TBL2_COL1」が作成されたということになります。
ちなみに、次のSQLを使用して索引のタイプやユニーク性を確認できますし、主キーに指定したカラム「COL1」に「Not Null」属性が設定されている等も確認してみると面白いですよ。各データ・ディクショナリ・ビューの列値が何を意味するのかは、マニュアル「リファレンス」を参照して下さい。
SQL> select INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS from USER_INDEXES where INDEX_NAME = 'PK_TBL2_COL1'; INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES ------------------------ --------------------------- ------------------------ --------- PK_TBL2_COL1 NORMAL TBL2 UNIQUE SQL> select TABLE_NAME, COLUMN_NAME, NULLABLE from USER_TAB_COLUMNS where TABLE_NAME = 'TBL2'; TABLE_NAME COLUMN_NAME N ------------------------ ------------------------------ - TBL2 COL1 N TBL2 COL2 Y SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME from USER_CONSTRAINTS; CONSTRAINT_NAME C TABLE_NAME ------------------------------ - ------------------------ PK_TBL2_COL1 P TBL2
■演習4．TBL2表の全レコードを削除してください。その際、TBL2表に割り当てられていた領域を解放して下さい。
SQL> truncate table TBL2;
SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS
from USER_SEGMENTS
where SEGMENT_NAME like '%TBL2%';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS
------------------------ ------------------ ------------------------------ ----------
TBL2 TABLE TBS_BIG 8
PK_TBL2_COL1 INDEX TBS_BIG 8
少し表現を変化させていますが、実行すべきコマンドは「TRUNCATE」ですよね。そして、この演習で気付いてもらいたかったのは、索引「PK_TBL_COL1」のブロック数も減少している点です。このコマンドの結果から、表をTRUNCATEするとその表に紐づく索引も一緒に領域が解放されるということが解ります。
■演習5．演習1と同じ方法で、TBL2表へ100万行をINSERTして下さい。
SQL> set timing on @insertTBL2_1M.sql 1000000行が作成されました。 経過: 00:00:20.94 SQL> commit;
おや？INSERT処理が遅くなったと感じたかと思います。そうです。同じINSERT文にも関わらず、前回の実行時間と比較すると遅いのです。何故？という疑問が自然に湧き始めている方は非常にラッキーですよ。そんな貴方は既にデータベースの魅力の渦に飲み込まれているので、これからは今まで以上の速度でデータベース・スキルが向上していくこと間違いなしです。とは言え、ベテラン技術者の方にはあまり面白くはないと思いますが。。。
さて、原因はそれほど難しくはありません。TBL2表に主キーを追加したことによって作成された索引が存在していることが違いになります。これまでの実行時間を整理すると、索引無し状態での100万行INSERTは11.03秒であり、その後の索引作成（主キー追加）では2.44秒でした。一方、索引有りの状態での100万行INSERTは20.94秒ですから、INSERTと索引作成を別々で実行した場合の13.47秒（=11.03 + 2.44）の方が早いことが解ります。つまり、大量データをINSERTするような場合は、索引を一時的に削除した状態で行い、その後、必要な索引を再作成するというオペレーションが効率的であることを体験できたかと思います。
■演習6．CREATE TABLE AS SELECT文を使用して、TBL2表からTBL3表を複製して下さい。
SQL> create table TBL3 as select * from TBL2; 表が作成されました。 経過: 00:00:05.96
はい、何度か登場している「create table as select」文を活用します。皆さんの環境においても、これまでで一番高速だったのではないでしょうか？その理由については後述するとして、ここでは注意点をご紹介しておきましょう。
SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS from USER_SEGMENTS; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS ------------------------ ------------------ ------------------------------ ---------- TBL1 TABLE TBS_BIG 8 TBL2 TABLE TBS_BIG 16384 TBL3 TABLE TBS_BIG 16384 PK_TBL2_COL1 INDEX TBS_BIG 1920 SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints; CONSTRAINT_NAME C TABLE_NAME ------------------------------ - ------------------------------ PK_TBL2_COL1 P TBL2 SQL> desc TBL3 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- COL1 NUMBER COL2 VARCHAR2(100)
上記の結果より、「create table as select」では索引は複製されませんし、主キー制約も複製されていないことが確認できます。特に、主キー制約を追加したことでCOL1列に設定されていた「Not Null」属性が引き継がれていない点が重要です。とだけ説明すると、「なるほど。制約は何も引き継がれないのね。」と解釈されてしまうのですが、これは大きな間違いなのです。実は、CHECK制約は引き継がれる挙動になっている為、列に明示的に「Not Null」属性を設定していた場合は複製されるので注意が必要です。
ちなみに、本文章では混乱を避けるために「Not Null」属性という言葉を使用しましたが、列データがNullではないことをCheckする制約を「Not Null」制約と呼び、CHECK制約の一つとなります。
■演習7．TBL3表をTRUNCATEした後に、ダイレクト・パス・インサートでTBL2表からTBL3表へ全レコードをコピーして下さい。
SQL> truncate table TBL3; set timing on insert /*+APPEND */ into TBL3 select * from TBL2; 1000000行が作成されました。 経過: 00:00:05.21 SQL> commit;
SELECTした結果を表へINSERTするINSERT + SELECT文に「APPEND」ヒント句を指定することで、「ダイレクト・パス・インサート」と呼ばれる方式でデータをINSERTすることが可能です。「ダイレクト・パス・インサート」って言葉の響きからして速そうですよね。では、何故速いのか、デメリットは無いのか？という点をご紹介しておきます。
メリット：通常のINSERT文はバッファ・キャッシュ上のデータ・ブロックにデータを書き込みますが、ダイレクト・パス・インサート方式ではバッファ・キャッシュを経由しないで、ダイレクトにデータ・ファイルへ書き込みを行う為、通常よりも高速に大量データを格納できます。また、UNDOブロックの生成量も抑制される点も高速化に貢献しています。
デメリット：INSERT対象の表に対する排他ロックが取られるため、INSERT完了まで他のセッションからその表に対するDML文は同時に実行できません。また、既存の空き領域にデータをINSERTするのではなく、そのテーブルのHigh Water Mark（HWM）より先に新しく領域を割り当てて、INSERT終了後に既存のテーブルにマージされるような内部挙動になります。
つまり、ダイレクト・パス・インサートは、夜間バッチ処理のように1つのセッションで大量データをINSERTしたい場合に採用する方法であり、日中の大量ユーザーが同時に行うような1レコードのINSERTでは使用しない方が良いことがご理解頂けたかと思います。
デメリット内の説明で登場したHWMについては又の機会に説明するとして、今回は前回の最後に少しだけご紹介したトラブルになるケースについて演習して終わりにしたいと思います。まずは、現状のTBL3表のセグメント・サイズとしては、総ブロック数が「16384」であることが確認できます。
SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS
from USER_SEGMENTS
where SEGMENT_NAME = 'TBL3';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS
------------------------ ------------------ ------------------------------ ----------
TBL3 TABLE TBS_BIG 16384
■演習8．TBL3表をDELETEした後に、ダイレクト・パス・インサートでTBL2表からTBL3表へ全レコードをコピーして下さい。
SQL> delete from TBL3;
1000000行が削除されました。
SQL> commit;
SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS
from USER_SEGMENTS
where SEGMENT_NAME = 'TBL3';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS
------------------------ ------------------ ------------------------------ ----------
TBL3 TABLE TBS_BIG 16384
SQL> insert /*+APPEND */ into TBL3 select * from TBL2;
1000000行が作成されました。
SQL> commit;
SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS
from USER_SEGMENTS
where SEGMENT_NAME = 'TBL3';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS
------------------------ ------------------ ------------------------------ ----------
TBL3 TABLE TBS_BIG 31744
では、解説です。まずはTBL3表をDELETEしていますが、DELETE後もブロック数が変化していないことが確認できます。これは前回説明したようにエクステントを箱に例えるのであれば、DELETEは中身を手で取り出しては捨てるという動作を繰り返し行い、最終的にも箱は残すような捨て方をする為です。その後、TBL3表に対してダイレクト・パス・インサートを行った結果、ブロック数がおよそ2倍に増加していることが確認できます。
ダイレクト・パス・インサートは既存の空き領域にデータをINSERTするのではなく新しく割り当てた領域にINSERTを行う為に発生する現象です。厳密にはINSERT前のHWMの位置を確認する必要がありますが、大雑把に言うと、既存の領域である始めの16384ブロックにはほとんどレコードが格納されておらず、その後ろに新しく割り当てられた15360（= 31744 – 16384）ブロックに多くのレコードが格納されている状態となります。
このようなトラブルを回避する為には「DELETE」ではなく「TRUN○○○○」を使用すれば良いですよね。
さて、いかがでしたでしょうか。新機能系の話が少ない回となってしまいましたが、データ管理をする上で絶対的に必要なスキルなので押さえておいてください。今回のダイレクト・パス・インサートのように、一見便利そうな機能でも適用場所を誤るとトラブルに陥る可能性を秘めているものがあります。何故、便利なのか？高速なのか？デメリッを裏付けるような内部動作を知っておくこと、デメリットを把握しておくことが、その機能をより効果的に活用するスキルに繋がりますよね。次回以降も頑張っていきますので、よろしくお願いします。
次回は、「SQLの実行計画からパフォーマンスの違いを読み解く」についてになります。是非ご覧下さい。お疲れさまでした。