しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。”しばちょう”こと柴田長（しばた つかさ）です。先週は毎日午前中に若手技術者に対する勉強会に没頭してみましたが、彼らが疑問に感じる部分が新鮮でもあり着実に成長していく姿を目の当たりにすることで、改めて自分自身のスキルも磨き続けたいと強く感じさせられた週でした。私も皆さんに負けないように頑張りますよ。
さて、第3回目の今回は「セグメント/エクステント/データ・ブロック」について演習を実施してみましょう。これらは表領域を含めて論理記憶域と呼ばれますが、その関係についてはマニュアル「Oracle Database概要」の「12 論理記憶域構造」（私が特に好きな章）に詳しく説明が記述されているので是非読んでみてください。そして、演習を通して実際のデータベース内部を覗いてみることで、その理解を深めることが重要だと思います。また、管理作業だけでは退屈な方もいらっしゃるかと思い、「検証データを如何に短時間で作成するのか」というパフォーマンス・チューニング的な演習も含めてみました。是非挑戦してみてください。
これまでの演習で使用したOracle Database 11g Release 2 Enterprise Editionのデータベースで試してみてください。今回の演習で登場するユーザーや表が存在しない場合は、第1回、第2回の演習も参考にして下さい。
■演習1．TRYユーザーのTBL2表にPL/SQLを使用して100万行をINSERTする時間を測定して下さい。
sqlplus TRY/TRY
SQL>
set timing on
begin
for i in 1..1000000 loop
insert into TBL2 values(i, rpad(to_char(i),100,'A'));
end loop;
commit;
end;
/
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:55.08
私のデータベース環境においては、100万行INSERTを行うPL/SQLの実行時間は約55秒を要すること結果となりました。もちろん、H/Wスペックに依存する部分があるので、皆さんのデータベース環境での実行時間と異なる可能性がある点だけご留意くださいね。いきなりPL/SQLの演習でしたが、このようなForループ処理をサクサク書けたりすると、「おー、勉強してるなー」という印象を与えることができるので、是非覚えておいてください。
INSERT対象のTBL2表は、第1カラムがNumber、第2カラムがVarchar2(100)で定義されています。このVarchar2(100)は100バイトの文字列を格納させることが可能なので、例えば1バイトの数文字やアルファベット等であれば、100文字まで格納できます。問題文には格納データの要件を記述し忘れてしまったのですが、もし「第2カラムに100文字格納せよ」という要件があったのであれば、「RPAD/LPAD」関数を使用してみてください。これらの関数を使用したSQL文を記述する姿は、まさにCoolです。ちなみに、「TO_CHAR」関数は引数で渡された値を文字列型に変換する関数ですね。
ここで、上記回答のPL/SQL内の「rpad(to_char(i), 100, ‘A’)」で作成される文字列を簡単に説明しておきます。Forループの変数iが1の場合、第1引数がto_char(1)となるので先頭は「1」で残りの99バイト（第2引数の100から第1引数の文字列のバイト数を引いた値）が第3引数で埋められた文字列が作成されます。詳細やその他の関数についてはマニュアル「SQL言語リファレンス」をご参照ください。
SQL> select rpad(to_char(1), 100, 'A') from DUAL; 1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
そして、SQL*Plusの基本中の基本なので紹介するまでもないかと思いますが、システム変数「set timing on」を設定することで、SQLコマンドまたはPL/SQLブロックが実行されるたびに、そのタイミング統計（実行に要した経過時間）を表示させることができます。このようなシステム変数は多数存在しているので、一度、マニュアル「SQL*Plusユーザーズ・ガイドおよびリファレンス」に目を通しておくと良いでしょう。
■演習2．100万行をINSERTしたことでTBL2のサイズがどのように変化したのかをデータ・ディクショナリ・ビュー「USER_SEGMENTS」で確認して下さい。
SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, EXTENTS, BLOCKS from USER_SEGMENTS where SEGMENT_NAME='TBL2'; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS BLOCKS ---------------- ------------------ ---------------- ---------- ---------- TBL2 TABLE TBS_BIG 87 16384
前回の演習の最後に、Oracle Database 11g Release 2の新機能「セグメント作成の遅延」の動作確認として、TBL2表のセグメントが作成されていないことを確認しました。同じSQL文を使用して、100万行をINSERTした後に、再度確認を行った結果が上記のとおりです。この結果より、TBS_BIG表領域上にTBL2表のセグメントが配置されており、合計87個のエクステントで構成されていて、その87個のエクステントは合計16384個のデータ・ブロックから構成されていることが確認できます。エクステント毎のブロック数は、UESR_SEGMENTSデータ・ディクショナリ・ビューではなく、USER_EXTENTSデータ・ディクショナリ・ビューで確認できるので、時間があれば挑戦してみてください。 ちなみに、この環境のブロック・サイズは8KB なので、TBL2のセグメントは128MB（=16384×8KB）の領域が割り当てられていることが理解できます。Number型は最大20バイトでVarchar2(100)に100バイトを格納しているので、1レコード当たり120+αバイトだと想定されます。そして、100万行は「100 × 1000 × 1000」なので100M行と解釈すると、120+α(B)× 100M = 120+α(MB)と暗算できますから、TBL2のセグメントに割り当てられている領域サイズと大体一致していますね。
■演習3．TRUNCATE文を使用してTBL2表の全レコードを削除する時間を測定して下さい。
SQL> truncate table TBL2; 表が切り捨てられました。 経過: 00:00:02.33
はい、これは簡単過ぎますね。この時点で確認して頂きたい点はTRUNCATEの実行時間です。私の環境では約2秒で完了することが確認できました。
■演習4．再度、TBL2のサイズを確認して下さい。
SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, EXTENTS, BLOCKS from USER_SEGMENTS where SEGMENT_NAME='TBL2'; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS BLOCKS ---------------- ------------------ ---------------- ---------- ---------- TBL2 TABLE TBS_BIG 1 8
TRUNCATE後に、再度USER_SEGMENTSデータ・ディクショナリ・ビューでTBL2表のセグメントを確認してみると、エクステント数及びブロック数が大幅に減少していることが確認できます。TRUNCATE処理が一瞬でレコードが格納されていた領域を切り捨てたことが理解して頂けたかと思います。
■演習5．初期値「10000001」、増加間隔「1」の順序「SEQ1」を作成して下さい。
sqlplus TRY/TRY SQL> create sequence SEQ1 start with 10000001 increment by 1; * 行1でエラーが発生しました。: ORA-01031: 権限が不足しています。
そろそろこのようなエラーが発生しても慌てなくなってきたのではないでしょうか。そして、この程度のエラーであれば、マニュアル「エラー・メッセージ」を参照しなくとも解決できるような感覚も備わってきていると思います。
SQL> connect / as sysdba grant CREATE SEQUENCE to TRY; 権限付与が成功しました。 SQL> connect try/TRY SQL> create sequence SEQ1 start with 10000001 increment by 1; 順序が作成されました。 SQL> select SEQUENCE_NAME, LAST_NUMBER, INCREMENT_BY, CACHE_SIZE from USER_SEQUENCES; SEQUENCE_NAME LAST_NUMBER INCREMENT_BY CACHE_SIZE ------------------------------ ----------- ------------ ---------- SEQ1 10000001 1 20
「CREATE SEQUENCE」権限をTRYユーザーに付与すれば、問題なく順序オブジェクトが作成できたかと思います。念のため、作成した順序オブジェクトが想定通りの属性に設定されているのかをUSER_SEQUENCESデータ・ディクショナリ・ビューで確認していたりすると非常に好印象ですね。何かの作業を依頼された際に作業ミスが無いことを証明する意味でも必要なログになるので、このようなクセを付けておくと良いでしょう。
USER_SEQUENCESデータ・ディクショナリ・ビューのINCREMENT_BYカラムは連想し易いと思いますが、LAST_NUMBERカラムやCACHE_SIZEカラムの値の意味は少々難しい感じがするので少し補足しておきます。
SQL> select SEQ1.nextval,SEQ1.currval from DUAL; NEXTVAL CURRVAL ---------- ---------- 10000001 10000001 SQL> select SEQUENCE_NAME, LAST_NUMBER, INCREMENT_BY, CACHE_SIZE from USER_SEQUENCES; SEQUENCE_NAME LAST_NUMBER INCREMENT_BY CACHE_SIZE ------------------------------ ----------- ------------ ---------- SEQ1 10000021 1 20 SQL> select SEQ1.nextval,SEQ1.currval from DUAL; NEXTVAL CURRVAL ---------- ---------- 10000002 10000002
復習になりますが、順序オブジェクトは次の値を取得する「nextval」と現在の値を取得する「currval」の2つの方法で使用します。順序オブジェクトを作成後に、nextvalで取得した値（10000001）はLAST_NUMBERカラムに出力されていた値と同じですね。つまり、LAST_NUMBERカラムの値は初期値を意味していたように見えます。しかし、再度USER_SEQUENCESデータ・ディクショナリ・ビューでLAST_NUMBERカラムを確認してみると、「10000021」に変化しており、前回から+20されていることが解ります。この+20はCACHE_SIZEの値と同じです。Cacheという言葉から連想できると思いますが、順序オブジェクトはCache_Size属性に設定された数だけ、共有プール内にキャッシュする仕組みが実装されています。nextvalが呼ばれる度に、毎回ディスクから値を取得するのではなく、ある程度の数をメモリ上にキャッシュしておくことでディスクアクセスの回数を減少させて、より高速に値を返すことが可能となります。つまり、LAST_NUMBERカラムの値は、ディスクへのアクセスが必要となる値を示しているのです。上記の例だと、「10000021」まで使用するとディスクへアクセスして次の20個（10000021～10000021+20）の値をキャッシュする設定になっています。
■演習6．演習5で作成した順序「SEQ1」を使用して、100万行をTBL2表へINSERTする時間を測定して下さい。可能であれば、PL/SQLは使用しないでチャレンジして下さい。
SQL> set timing on
begin
for i in 1..1000000 loop
insert into TBL2 values(SEQ1.nextval, rpad(to_char(SEQ1.currval),100,'A'));
end loop;
commit;
end;
/
PL/SQLプロシージャが正常に完了しました。
経過: 00:01:14.96
まずは、PL/SQLで順序オブジェクト「SEQ1」を使用する回答例です。上記のPL/SQLでどのような値のレコードがINSERTされるのかが頭の中で予想できるようにしておきましょう。
SQL> truncate table TBL2;
insert into TBL2
select SEQ1.nextval, rpad(to_char(SEQ1.currval),100,'A')
from (select 0 from all_catalog where rownum <= 1000),
(select 0 from all_catalog where rownum <= 1000);
1000000行が作成されました。
経過: 00:00:21.66
SQL> commit;
次に、PL/SQLを使用しないで100万行をINSERTするSQL文の回答例です。Where句で結合条件を指定しないことにより、2つの表を「直積」するロジックを応用しています。1つの副問い合わせ(select 0 from all_catalog where rownum <= 1000)では、all_catalogデータ・ディクショナリ・ビューを使用して「0」という値を持つレコードを1000行生成し、この副問い合わせを直積することで1000行×1000行=100万行を生成しています。その100万行を基に順序オブジェクト「SEQ1」からnextval/currvalで値を取得した結果をTBL2表にまとめてINSERTしています。ちなみに、all_catalogデータ・ディクショナリ・ビュー出なくとも、1000行保持しているビューで代替することが可能ですね。
皆さんの環境でも、このINSERT文の実行時間はPL/SQLを使用した時よりも、圧倒的に高速化していることが確認できたかと思います。「直積」のテクニックは是非身につけておいてください。
SQL> truncate table TBL2;
create sequence SEQ2 start with 10000001 increment by 1 cache 10000;
insert into TBL2
select SEQ2.nextval,rpad(to_char(SEQ2.currval),100,'A')
from (select 0 from all_catalog where rownum <= 1000),
(select 0 from all_catalog where rownum <= 1000);
1000000行が作成されました。
経過: 00:00:12.94
SQL> commit;
また、演習5において、順序オブジェクトのCache属性について触れましたが、上記の回答例のようにCache属性をデフォルトの20から10000に設定した順序オブジェクト「SEQ2」を使用することで、より高速にINSERT可能であることも確認できますので、ご興味があれば試してみてください。
ただし、Cache属性の取り扱いには十分注意する必要があります。メモリ上にある範囲の値を保持するということは障害等でインスタンスが強制的にダウンした場合には、キャッシュしていた値は消えてしまいます。つまり、次にインスタンスを起動した際には値が歯抜け状態になってしまいます。その他にも共有プールの空き領域が枯渇してきた場合、キャッシュしていた値を捨ててしまうこともありますので、アプリケーションの要件で値の歯抜けを許容してもらうか、Cache属性を「0」に設定する必要が出てきます。ちなみに、インスタンスを正常終了した場合は、キャッシュされていたが使用していなかった値はディスク上へ戻される動作をします。
SQL> truncate table TBL2;
insert into TBL2
select i,rpad(to_char(i),100,'A')
from (
with DATA(i) as (
select 1 i from DUAL
union all
select i+1 from DATA where i < 1000000)
select i from DATA);
1000000行が作成されました。
経過: 00:00:28.99
SQL> commit;
ついでなので、順序オブジェクトを使用せずにユニーク（一意）な100万行を高速にINSERTする方法をご紹介しておきます。それが上記の回答例にあるOracle Database 11g Release 2から実行可能となった、再帰的With句を活用したSQLです。初期値「1」、増加間隔「1」、最大値「1000000」の100万行のレコードをselect文で生成しています。
SQL> truncate table TBL2;
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:08.74
SQL> commit;
また、2つの再帰的With句を上記の例のように直積させることで、より高速化が可能です。PL/SQLのForループより、順序オブジェクト+直積よりも高速に100万行をINSERTすることが可能です。さらに高速化を行いたい方は、「APPEND」ヒント句をINSERT文に付加することで可能となる、ダイレクト・パス・インサートにチャレンジしてみてください。
■演習7．DELETE文を使用してTBL2表の全レコードを削除する時間を測定して下さい。
SQL> set timing on SQL> delete from TBL2; 1000000行が削除されました。 経過: 00:00:41.93 SQL> commit; コミットが完了しました。
こちらのSQLは非常に簡単ですね。DELETE文でWhere句を設定していない為、全てのレコードが削除されます。確認すべき点は、DELETEに要した時間です。TRUNCATE時は約2秒で完了していましたが、DELETEの場合は約42秒程度要していることが確認できるかと思います。この理由については、次の演習後に説明します。
■演習8．再度、TBL2のサイズを確認して下さい。
SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, EXTENTS, BLOCKS
from USER_SEGMENTS
where SEGMENT_NAME='TBL2';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS BLOCKS
---------------- ------------------ ---------------- ---------- ----------
TBL2 TABLE TBS_BIG 87 16384
全レコードのDELETE後に、再度USER_SEGMENTSデータ・ディクショナリ・ビューでTBL2表のセグメントを確認してみると、エクステント数及びブロック数がDELETE前と同じである為、明らかにTRUNCATE処理と異なる動作をしていることが読み取れますね。表やエクステントを箱に例えるのであれば、DELETEは中身を手で取り出しては捨てるという動作を繰り返し行い、最終的にも箱は残すような捨て方をします。一方、TRUNCATEは箱ごと一括で捨ててしまうようなイメージです。よって、それぞれの処理後のセグメントに割り当てられている領域に違いが生まれますし、処理時間にも大きな開きが生まれてくることは想像し易いかと思います。 この領域管理の仕組みを理解できていない場合にトラブルになるケースとしては、DELETE処理と演習６の最後にコメントしたダイレクト・パス・インサートの組み合わせです。これに関しては、次回の演習で体験して頂きたいと思っています。
さて、いかがでしたでしょうか。長文にも関わらず最後までお付き合い頂きましてありがとうございました。パフォーマンス・チューニングの観点を含めた演習であったので、これまでよりも面白い/楽しいと感じて頂ければ嬉しい限りです。「楽しい」という感覚は大切にしていきたいですね。興味があるからこそチャレンジし続けることができると思いますので、そういったものをご提供できるよう次回以降も頑張っていきますので、よろしくお願いします。
次回は、「続・データ領域管理の理解～ダイレクト・パス・インサートを試してみる～」についてになります。是非ご覧下さい。お疲れさまでした。