株式会社インサイトテクノロジー 発行
http://www.insight-tec.com/jp/
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━Vol.41
☆☆☆ おら！オラ！ Oracle −どっぷり検証生活− ☆☆☆
2001.01.31
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆
/// ///┏━━━━━━━━━━━━━━━━━━━━━━━━━┓/// ///
★ ★ ┃ ┃★ ★
/// ///┃ 当メルマガライターとしておなじみの、つけまいこと ┃/// ///
★ ★ ┃ 成田裕亮がOracle8iを熱く語ります！！ ┃★ ★
/// ///┃ 以下のNET&COM21のHPよりお申し込みください。 ┃ /// ///
★ ★ ┃ お得な事前申し込みの締め切り迫る！ ┃★ ★
/// ///┃ ┃/// ///
★ ★ ┗━━━━━━━━━━━━━━━━━━━━━━━━━┛★ ★
★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆
＜NET&COM21フォーラム案内＞
http://expo.nikkeibp.co.jp/netcom21/index_forum.html
つけまいの講演は、2月8日（木）午後2時〜
「Oracle8i新機能の活用法−内部構造を理解し有用性を探る」
と題して行われます。是非、ご参加ください。
＜お詫び＞
書籍をご購入いただいた皆様、ありがとうございました。
お申し込み多数のため、発送が遅れましたことをこの場を借りて
深くお詫び申し上げます。
大好評につき、消費税サービス＋送料無料キャンペーン継続中！
この機会を是非お見逃し無く。
お申し込みは以下のURLより受付中！
http://www.insight-tec.com/jp/topics/books.html
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
<<目次>>
■Oracle検証生活・・・ソートに関する検証 その６
■Oracle入門生活・・・整合性制約の状態
■お知らせ・・・○Oracle管理ツール Performance Insight
○SQeeLのご案内 ○連載情報
○書籍ご予約受付中 ○ＱＡについて
■編集者より
■■注意事項！！■■
本文中にテーブルが含まれていますので、お読みになる際はMSゴシック等、
等幅フォントをお使いただくことをお勧めします。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ Oracle検証生活 ▼━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
〜ソートに関する検証 その６ 〜 ペンネーム ちゃむ
今回は、前回の前提をふまえて以下のような指針が正しいかどうかを検証する。
＜指針＞
初期化パラメータのSORT_AREA_SIZEと一時表領域のDEFAULT STORAGE句のINITIAL
、NEXT、PCTINCREASE に関して、次のような指針がある。
「SORT_AREA_SIZE=INITIAL=NEXT PCTINCREASE=0にする。」
以下の検証はDB_BLOCK_SIZE=2Kの環境でかつソートを行なうSQL文を発行する前に
Oracleを再起動する。
＜SORT_AREA_SIZEの閾値＞
SELECT /*+ FULL(T10) */ EMPNO,JOB FROM T10MAN_ORG T10 ORDER BY EMPNO;
（T10MAN_ORGはEMP表を10万件に拡張したようなもの）
上記のようなSQL文であれば、SORT_AREA_SIZEの閾値は3241984と3241985であった。
以下にその時のAUTOTRACEの様子を示す。
＜メモリソート＞
SQL> ALTER SESSION SET SORT_AREA_SIZE=3241985;
SQL> SET AUTOTRACE TRACE
SQL> SELECT /*+ FULL(T10) */ EMPNO,JOB FROM T10MAN_ORG T10 ORDER BY EMPNO;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1607 Card=100000 Byt
es=1100000)
1 0 SORT (ORDER BY) (Cost=1607 Card=100000 Bytes=1100000)
2 1 TABLE ACCESS (FULL) OF 'T10MAN_ORG' (Cost=379 Card=10000
0 Bytes=1100000)
Statistics
----------------------------------------------------------
0 recursive calls
15 db block gets
2499 consistent gets
1433 physical reads
0 redo size
5706824 bytes sent via SQL*Net to client
740350 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
1 sorts (memory) ← メモリソートをした証拠
0 sorts (disk) ← ディスクソートをしていない証拠
100000 rows processed
＜ディスクソートの閾値＞
ALTER SESSION SET SORT_AREA_SIZE=3241984;
「ソート先のテーブルスペースの情報」
１．SCOTTユーザーのTEMPORARY_TABLESPACEはTEMP5である様子。
SQL> SELECT USERNAME,TEMPORARY_TABLESPACE FROM USER_USERS;
USERNAME TEMPORARY_TABLESPACE
-------------------------------
SCOTT TEMP5
２．TEMP5のDEFAULT STORAGE句は、INITIAL=4K NEXT=2K PCTINCREASE=0であり
かつ専用一時表領域（TEMPORARY）である様子。
（INITIALエクステントは、セグメントヘッダ１ブロックを含むのでTEMP5の
DEAFULT STORAGE句に INITIAL=2Kと指定して作っても実際はINITIAL=4Kになる。
SQL> SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,PCT_INCREASE,
CONTENTS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='TEMP5';
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE CONTENTS
------------------------------------------------------------------
TEMP5 4096 2048 0 TEMPORARY
上記の専用一時表領域（TEMPORARY）の状況を踏まえてディスクソートを行なっ
てみよう。
SQL> ALTER SESSION SET SORT_AREA_SIZE=3241984;
SQL> SET AUTOTRACE TRACE
SQL> SELECT /*+ FULL(T10) */ EMPNO,JOB FROM T10MAN_ORG T10 ORDER BY EMPNO;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1607 Card=100000 Byt
es=1100000)
1 0 SORT (ORDER BY) (Cost=1607 Card=100000 Bytes=1100000)
2 1 TABLE ACCESS (FULL) OF 'T10MAN_ORG' (Cost=379 Card=10000
0 Bytes=1100000)
Statistics
----------------------------------------------------------
89896 recursive calls ← 内部コールも盛りだくさん
6479 db block gets ← メモリソートより読み込み数が多い
71788 consistent gets ← メモリソートより読み込み数が多い
3572 physical reads ← メモリソートより読み込み数が多い
1342444 redo size ← SELECT文なのにREDOの生成
5706824 bytes sent via SQL*Net to client
740350 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory) ← メモリソートをしていない証拠
1 sorts (disk) ← ディスクソートをした証拠
100000 rows processed
では、その時の専用一時表領域がどれくらい拡張したかを以下のSQL文で確かめてみよう。
SQL>SELECT TABLESPACE_NAME,SEGMENT_NAME,EXTENTS,BLOCKS FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME='TEMP5';
TABLESPACE_NAME SEGMENT_NAME EXTENTS BLOCKS
----------------------------------------------
TEMP5 13.450 1016 1016
EXTENTS=1016、BLOCKS=1016よりエクステントが1016個でブロック数が1016ブロック
確保された様子がわかる。
ここで、検証したかった内容を考えてみる
SORT_AREA_SIZE=3241984=3241984/1024=3166K=3166/2=1583ブロックである。
また、ディスクソートの閾値は 3241985だが、内部的にはDB_BLOCK_SIZEに
丸められているはずである。つまり、1584ブロックである。
＜結果比較＞
ディスク上での実際のソートブロック数（BLOCKS）= 1016ブロック
そのときのSORT_AREA_SIZEの閾値 = 1583ブロック
今回の検証ではSORT_AREA_SIZEの閾値1583の約3分の2が専用一時表領域
（TEMPORARY）に書き込まれたことになる。
つまり、SORT_AREA_SIZE=INITIAL=NEXTというようなことにはならない。
しかし、一番いけないのは、INITIAL=NEXT=2Kのように小さいエクステントに
対してメモリから吐き出すことである。今回検証した指針に従えば、ソート
セグメントの1エクステントで収まるので、この指針は個人的に適切であると
判断している。但し、前回でも述べたが、セグメントヘッダー分を加味して、
「INITIAL=NEXT=SORT_AREA_SIZE+DB_BLOCK_SIZE、PCTINCREASE=0」
なんてここまで細かい指針はいらない気がする。
また、もっとソート量が多いときに、INITIAL=NEXT=SORT_AREA_SIZE*正の整数
というような指針も間違いではないであろう。
最後に、今回検証するに当たり統計上で違いが出たのでその説明をしておこう。
recursive calls、redo sizeは、一時表領域上での領域管理に必要としたもの。
ということでREDOは、SELECT文でも発生することはある。
consistent gets、physical readsは、ソート済みデータを一時表領域に書き出
し（一時待避）、マージ処理のために再度読み込む必要があるため、その分、
メモリソートに比べて読み込み数が増えるのは当然であろう。
以上 鉄砲通りが長くなった 茅ヶ崎にて
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ Oracle入門生活 ▼━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜Oracle入門＞ ペンネーム モンキーターン
前回は、「整合性制約の管理」（遅延制約の設定方法）について理解していた
だけたと思う。
今回は、「整合性制約の管理」のつづきで「整合性制約の状態」についてお送
りしよう。
--- それでは、どっぷり入門生活スタート！ -----------------------------
整合性制約は、次のいずれかの状態にすることができる。
１．DISABLE NOVALIDATE・・・新たに入力されるデータの制約はチェックされない。
既存のデータもチェックされない。
２．DISABLE VALIDATE・・・・データの変更が禁止される。
既存のデータはチェックされる。
UPDATE文、INSERT文、DELETE文を受け付けない。
つまり、既存のデータの制約は確実に保証する。
３．ENABLE NOVALIDATE ・・・新たに入力されるデータの制約はチェックされる。
既存のデータはチェックされない。
全体のデータを見れば、制約が守られているわけで
はない。
４．ENABLE VALIDATE ・・・・新たに入力されるデータの制約はチェックされる。
既存のデータもチェックされる。
注：２．は、Oracle8iから拡張された。（一番特殊な機能かも）
３．は、Oracle8から拡張された。 （あまり使えない機能かも）
Oracle8でいう「DISABLE」は、１．のことである。
Oracle7でいう「DISABLE」と「ENABLE」は、１．と４．のことである。
それでは、それぞれの状態に設定したときの動きを見てみよう。
まず、検証で使うテーブルを作成する。
SQL> CREATE TABLE WORK
(ID number constraint PK_WORK PRIMARY KEY
NOT DEFERRABLE INITIALLY IMMEDIATE ) ;
状態を何も指定しないときは、デフォルトで４．の状態になるのである。
SQL> INSERT INTO WORK(ID) VALUES(1) ;
SQL> INSERT INTO WORK(ID) VALUES(2) ;
SQL> INSERT INTO WORK(ID) VALUES(3) ;
SQL> INSERT INTO WORK(ID) VALUES(4) ;
SQL> INSERT INTO WORK(ID) VALUES(5) ;
SQL> COMMIT ;
各状態に変更するには、ALTER TABLE 文を使う。
SQL> ALTER TABLE 表名 変更したい状態 CONSTRAINT 制約名 ;
＜DISABLE VALIDATEに変更する例＞
SQL> ALTER TABLE EMP DISABLE VALIDATE CONSTRAINT PK_EMP ;
＜ DISABLE NOVALIDATE の場合＞
これは、Oracle7またOracle8でいう「DISABLE」と同じ状態である。
Oracle8iで「DISABLE」と指定したときも、この状態になるのである。
WORK表の PK_WORK制約を DISABLE NOVALIDATE状態にして、動きを見てみよう。
入力データの制約はチェックされないし、既存のデータもチェックされない
はずである。
SQL> SELECT TABLE_NAME , INDEX_NAME FROM USER_INDEXES
WHERE TABLE_NAME = 'WORK' ;
TABLE_NAME INDEX_NAME
-------------- --------------
WORK PK_WORK
SQL> ALTER TABLE WORK DISABLE NOVALIDATE CONSTRAINT PK_WORK ;
表が変更されました。
SQL> SELECT TABLE_NAME , INDEX_NAME FROM USER_INDEXES
WHERE TABLE_NAME = 'WORK' ;
レコードが選択されませんでした。
主キーを DISABLE NOVALIDATE に設定すると主キーに付いていた索引は、削除
されるのである。
SQL> INSERT INTO WORK(ID) VALUES(1) ; ---------------→（A）
~~~
1行が作成されました。
SQL> COMMIT ;
コミットが完了しました。
上の結果より
制約を DISABLE NOVALIDATE 状態に設定すると、PRIMARY制約が使用不可になり
主キーについていた索引が削除されていた。よって、PRIMARY制約に違反する
（A）の値も挿入することができたのである。
今回は、この辺で「どっぷり入門生活」おしまい -------------------------
「どっぷり入門生活」では、基本的な質問をお待ちしています。また、このような
テーマでやってほしい等の要望がございましたら、メールでどしどしお寄せく
ださい。
以上 「NET&COM21 来てね！ 私も行きます。」 モンキー・ターンより
（場所：幕張メッセ、主催：日経BP社）
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ Oracle 管理ツール Performance Insight ▼━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Oracleを知り尽くしたメンバーが開発したOracleパフォーマンス監視ツール
の決定版。それがPerformance Insightです。インサイトテクノロジーの技術
者の知恵とノウハウがここに結集！
パフォーマンス監視だけでなくOracleを使用しているシステムの運用、管理、
そして開発にも役立つ機能がいっぱいです。
詳しくは以下のURLをご覧ください。
http://www.insight-tec.com/jp/products/products.html
また無料で試使用することも可能です。
是非お問い合わせください。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ SQeeLのご案内 ▼━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜プログラマ待望の言語SQeeL＞
●手軽に使える、速い、そしてWEBに適した新言語SQeeL！
フリーソフトSQeeLは、以下のURLより好評ダウンロード中！
既に多くの方々にご利用いただいております。
あなたもSQeeLの世界を体験してみませんか？
http://www.SQeeL.org
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ 連載情報 ▼━━━━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜雑誌連載＞
Oracleのエキスパートとして定評のある弊社のスタッフが執筆しております
連載記事に関してご紹介しています。
現在「DB Magazine」に、Oracleビギナー向け連載が掲載されています。
３月号が発売中ですので、是非ご覧下さい。
http://www.insight-tec.com/jp/topics/magazine.html
上記のURLでタイトルがご覧になれます。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ 書籍ご予約受付中 ▼━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜送料無料でお届けします＞
「Oracle8 プロフェッショナルテクニック」は大好評につき、現在在庫切れと
なっており、ご注文いただいた皆様にはたいへんご迷惑をおかけしております。
まもなく入荷する予定ですので、今しばらくお待ちください。
これからご注文いただく方も、入荷次第発送させていただくということで
予約注文を受付けておりますのでよろしくお願い申し上げます。
また弊社のHPよりお申し込みいただいた方に限り、送料無料でお届けします。
専門書としては異例の速さで増刷が決定するほどの好評をいただい
ている「Oracle8 プロフェッショナルテクニック」をぜひご活用ください。
2000年12月10日に発売された「Oracleデータベース管理を極める13章」の
販売を開始いたしました。「DB Magazine」に連載され大好評だった「体験的・
DBAのお仕事」が加筆され書籍化された同書は、おかげさまで発売以来2週間で
増刷となりました。現在キャンペーン期間中につき、同書に限りましては、
消費税サービス＋送料無料、つまり本体価格のみでご購入いただけます。
http://www.insight-tec.com/jp/topics/books.html
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ ＱＡについて ▼━━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜皆様からのＱＡを受付けております＞
皆様のＱＡにはできるだけ、お答えしたいと思っています。
すべてのＱＡにお答えすることはできないかもしれませんが、
適宜メルマガ内でとりあげていく予定ですので、是非ＱＡをお寄せください。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ 編集者より ▼━━━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
誰だぁ〜！今年も暖冬なんて言ったのは！！寒いよぉ。本当に寒い。先週末
は茅ヶ崎も雪景色。本当にイヤになる...。どちらかといえば夏よりも冬の方
が好きだけど、それでも雪はちょっと困ります。何をするにも不便だし。考
えてみれば、幼い頃は雪が積もったら大喜びだったはず。いったいいつから
雪が嫌いになったんだろう？ by UA
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
登録・解除は以下のURLで行うことができます。
http://www.insight-tec.com/jp/em/mail_magazine.html
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜おら！オラ！Oracle−どっぷり検証生活−＞
発行／編集：株式会社インサイトテクノロジー
http://www.insight-tec.com/jp
マガジンID：0000030093
本メールマガジンに掲載された記事を許可なく転載することを禁じます。
Copyright (c) 1996-2001, Insight Technology, Inc. All rights reserved.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Insight Technology, Inc
Japanese | English