株式会社インサイトテクノロジー 発行
http://www.insight-tec.com/jp
━━━━━━━━━━━━━━━━━━━━━━━━━ Vol.17＆18合併号
☆☆☆ おら！オラ！ Oracle −どっぷり検証生活− ☆☆☆
2000.08.09
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
他ではなかなか得られない、マニュアルを読んでもわからない、
そういったOracleに関する技術情報をお届けするメルマガです。
実際に検証した結果も交えてお伝えしていきます。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
★★ ┏━━━━━━━━━━━━━━━━━━━━━━━┓ ★★
★★ ┃ Vol.17＆18合併号！検証生活は超大作（？）です!┃ ★★
★★ ┗━━━━━━━━━━━━━━━━━━━━━━━┛ ★★
★★ ということで、お察しの通り次週(8/16）は休刊となります。 ★★
★★ 悪しからずご了承ください。よろしくお願いいたします。 ★★
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
<<目次>>
■Oracle検証生活・・・続・INDEXに関する検証 その１
■お知らせ・・・○Oracle管理ツール ○連載情報
○書籍ご予約受付中 ○ＱＡについて
■編集者より
■■注意事項！！■■
本文中にテーブルが含まれていますので、お読みになる際はMSゴシック等、
等幅フォントをお使いただくことをお勧めします。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ Oracle検証生活 ▼━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜続・INDEXに関する検証 その１＞ ペンネーム つけまい＆モンキーターン
--- UPDATEとINDEXの関係 ---
以前、「おら！オラ！ Oracle −どっぷり検証生活−」宛に、以下の質問が寄
せられた。
教えて下さい。
INDEXとして定義された項目を、同一の値で UPDATEすると INDEXはどうなりま
すか。何も変わらないのでしょうか？それとも内部的に更新されてどこかに影
響が出るのでしょうか？
読者の方々は、どのような結果を予測されたであろうか？
1．DATA（テーブル）および INDEX共に更新（上書き）される
2．DATA（テーブル）および INDEX共に更新（上書き）されない
3．DATA（テーブル）のみが更新（上書き）され、INDEXは更新されない
4．INDEXのみが更新（上書き）され、DATA（テーブル）は更新されない
答え ⇒ 3
実データが格納されている TABLEに対しては、データブロックが変更される、
つまり同じ値に上書きされるのだが、INDEXに対しては物理ライトが発生しな
い、つまり上書きされないことが検証の結果から判明した。
それでは、今回行った検証結果を、順に紹介していこう。
--- どっぷり検証生活 START！ ---
同一の値で UPDATEした場合、INDEXに対する変更は行われないという根拠を、
BLOCK_DUMPの中身や PHYSICAL WRITE（物理書き込み）の回数などを基に、
裏付けを行っていく。
＜＜＜検証用テーブルとインデックスの作成＞＞＞
1．ユーザー SCOTTで EMP_TESTと言うテーブルを作成（中身は EMP表と同じ）
2．表の項目 EMPNOに対して、EMP_TEST_INDEXと言う INDEXを作成
（INDEXは別の表領域に作成）
＜＜＜検証に用いたSQL文＞＞＞
SQL> UPDATE EMP_TEST SET EMPNO = 7369 WHERE EMPNO = 7369 ;
1行が更新されました。
SQL> commit ;
コミットが完了しました。
SQL> alter system checkpoint ;（alter system権限が必要）
システムが変更されました。
注：Oracleは、ユーザがコミットを行った直後はデータ・ファイルへの書き戻
し作業を行わない。あるタイミングでまとめて書き戻す「遅延書き込み」
を行っている。つまり、ある程度まとまった更新データを一度に書き戻す
ことによって、データ・ファイルに対する物理I/Oを削減しているのであ
る。そのため、検証を行う上で、書き戻しが完了していないブロック
（ダーティ・ブロック）がデータベース・バッファ上に残っていては正確
な検証結果が得られないため、チェック・ポイントを発生させる必要があ
る。ちなみに、チェック・ポイントが発生すると、データベース・バッ
ファ上のダーティ・ブロックは、すべてデータ・ファイルへ書き戻される。
なお、チェック・ポイントが発生すると、すべてのデータ・ファイルの
ヘッダーに存在するチェック・ポイント情報やSCNが更新される。
SCN = System Change Number：
トランザクションごとに、シーケンシャルに割り振られる番号。このSCN
を基に、トランザクションを再現し、最新のチェック・ポイントまでの回
復を行う。
＜＜＜BLOCK_DUMPの取得方法＞＞＞
テーブル EMP_TESTの項目 EMPNOを、7369から7369（同じ値）に UPDATEすると、
Oracleブロックに対して、どのような処理が施されるのであろうか。
BLOCK_DUMPを基に、確認して見よう。
注：BLOCK_DUMPは、Oracleのバージョンによって取得方法が異なるので、注意
が必要である。
＜Oracle 7の場合＞
1．DBA_EXTENTSからファイル番号とブロック番号を取得する
SQL> SELECT SEGMENT_NAME,
FILE_ID,
BLOCK_ID
FROM DBA_EXTENTS
WHERE OWNER = 'SCOTT'
AND SEGMENT_NAME LIKE 'EMP_TEST%' ;
SEGMENT_NAME FILE_ID BLOCK_ID
--------------- -------- --------
EMP_TEST 8 2
EMP_TEST_INDEX 9 2
2．10進数のデータ・ブロック・アドレスを取得する
dbms_utility.make_data_block_addressファンクションを使用することで、簡
単にデータ・ブロック・アドレスを求めることができる。
dbms_utility.make_data_block_address(<ファイル番号>,<ブロック番号>)
引数には、10進数のファイル番号とブロック番号を指定する。実行すると、10
進数のデータ・ブロック・アドレスが返される。
SQL> SELECT dbms_utility.make_data_block_address(9,3) DBA
2> FROM DUAL;
DBA
--------
37748739
注：上記の SQL文で、ブロック番号の所に「2」ではなく「3」を指定している
ことにお気付きいただけただろうか。EMP_TEST_INDEX（EMP_TEST）は、
EMP表（データ件数14レコード）を元に作成しているため、TABLEおよび
INDEXは共に1ブロックに納まっている。また、TABLEおよび INDEXの先頭
には、必ずセグメント・ヘッダー（1ブロック）が存在するため、実デー
タが格納されているのは2番目のブロック以降ということになる。した
がって、この場合実データが格納されている先頭のブロック番号「3」を
指定しなければならない。
3．BLOCK_DUMPを取得する
以下に、BLOCK_DUMPを取得するための SQL文を示す（Oracle 7の場合）
SQL> ALTER SESSION SET EVENTS
2> 'immediate trace name blockdump level 37748739' ;
セッションが変更されました。
初期化パラメータ user_dump_dest で指定したディレクトリに、BLOCK_DUMPの
結果が出力される（日付が最新のファイル）。
＜＜＜Oracle 8以降の場合＞＞＞
1．DBA_EXTENTSからファイル番号とブロック番号を取得する
⇒ ＜Oracle 7の場合＞の「1．DBA_EXTENTSからファイル番号〜」を参照
2.BLOCK_DUMPを取得する
以下に、BLOCK_DUMPを取得するための SQL文を示す（Oracle 8の場合）
ALTER SYSTEM DUMP DATAFILE <ファイル番号> BLOCK <ブロック番号> ;
SQL> ALTER SYSTEM DUMP DATAFILE 9 BLOCK 3 ;
システムが変更されました。
初期化パラメータ user_dump_dest で指定したディレクトリに、BLOCK_DUMPの
結果が出力される（日付が最新のファイル）。
＜＜＜BLOCK_DUMPの結果＞＞＞
同一の値（7369⇒7369）で UPDATEする前のデータ・ブロック・ダンプの結果
Start dump data blocks tsn: 7 file#: 8 minblk 3 maxblk 3
buffer tsn: 7 rdba: 0x02000003 (8/3)
scn:0x0000.00b5af49 seq:0x01 flg:0x02 tail:0xaf490601
~~~~~~~~~~~~~~~~~~~（1）
frmt:0x02 chkval:0x0000 type:0x06=trans data
以下省略
同一の値（7369⇒7369）で UPDATEした後のデータ・ブロック・ダンプの結果
Start dump data blocks tsn: 7 file#: 8 minblk 3 maxblk 3
buffer tsn: 7 rdba: 0x02000003 (8/3)
scn:0x0000.00b5af55 seq:0x01 flg:0x02 tail:0xaf550601
~~~~~~~~~~~~~~~~~~~（2）
frmt:0x02 chkval:0x0000 type:0x06=trans data
以下省略
同一の値（7369⇒7369）で UPDATEする前の
インデックス・ブロック・ダンプの結果
Start dump data blocks tsn: 8 file#: 9 minblk 3 maxblk 3
buffer tsn: 8 rdba: 0x02400003 (9/3)
scn:0x0000.00b47688 seq:0x01 flg:0x00 tail:0x76880601
~~~~~~~~~~~~~~~~~~~（3）
frmt:0x02 chkval:0x0000 type:0x06=trans data
以下省略
同一の値（7369⇒7369）で UPDATEした後の
インデックス・ブロック・ダンプの結果
Start dump data blocks tsn: 8 file#: 9 minblk 3 maxblk 3
buffer tsn: 8 rdba: 0x02400003 (9/3)
scn:0x0000.00b47688 seq:0x01 flg:0x00 tail:0x76880601
~~~~~~~~~~~~~~~~~~~（4）
frmt:0x02 chkval:0x0000 type:0x06=trans data
以下省略
各ブロック・ダンプの結果のSCNに注目していただきたい。
データ・ブロック・ダンプ
UPDATE前 ⇒ scn:0x0000.00b5af49（1）
UPDATE後 ⇒ scn:0x0000.00b5af55（2）
インデックス・ブロック・ダンプ
UPDATE前 ⇒ scn:0x0000.00b47688（3）
UPDATE後 ⇒ scn:0x0000.00b47688（4）
UPDATE後、「データ・ブロックの SCNは更新されているが、インデックス・ブ
ロックのSCNは更新されていない。」
この結果は、同じ値で更新した場合、データ・ブロックに対しては更新（上書
き）するが、インデックス・ブロックに対しては更新（上書き）しないという
ことを裏付ける結果と言えよう（実データが格納されている部分のダンプを見
ただけでは、上書きされたか否かを確認することはできない）。
実際に、REDOログ・ファイルのダンプを取得して中身を確認してみたが、やは
りインデックス・ブロックに関する更新履歴は存在しなかった（チェック・ポ
イント発生時の、ヘッダーに対する SCNの更新は UPDATE処理とは無関係であ
る）。
＜＜＜PHYSICAL WRITEの結果＞＞＞
では次に、各データ・ファイル（データ、インデックス）に対して、どれだけ
の PHYSICAL WRITE（物理書き込み）が発生したかを確認してみよう。
SQL> SELECT SEGMENT_NAME,
FILE_ID,
FROM DBA_EXTENTS
WHERE OWNER = 'SCOTT'
AND SEGMENT_NAME LIKE 'EMP_TEST%' ;
SEGMENT_NAME FILE_ID
--------------- --------
EMP_TEST 8
EMP_TEST_INDEX 9
データ・ブロックに対する物理書き込みブロック数を調べるSQL
SQL> SELECT PHYBLKWRT FROM V$FILESTAT WHERE FILE# = 8 ;
インデックス・ブロックに対する物理書き込みブロック数を調べるSQL
SQL> SELECT PHYBLKWRT FROM V$FILESTAT WHERE FILE# = 9 ;
上記の SQL文の結果は累計値となっているので、UPDATE文の前後で SQL文を発
行し、差分を求める必要がある。
結果：データ・ブロック = 2 BLOCK WRITE
インデックス・ブロック = 1 BLOCK WRITE
内訳を説明すると、チェック・ポイント時のヘッダーに対する書き込みが、そ
れぞれ1ブロックずつ、UPDATEによる書き込みが、データ・ブロックに対して
のみ1ブロック行われている。この結果からも、インデックス・ブロックに対
する更新処理が行われていない様子が伺える。
次回は、INDEXとして定義された項目が、異なる値で更新された場合の UPDATE
と INDEXの関係について、検証を交えながら説明する。
どのような結果になるか、読者の方々もお盆休みを利用して、考えて（検証）
みてはいかかであろうか。
＜余談＞
INDEXは使い方によっては、何倍ものパフォーマンスを得ることができるが、
使い方を間違えると意味が無くなるだけでなく、パフォーマンスを劣化させて
しまう原因にもつながる恐れがある。
しかし、DBAの運用次第では、すばらしい武器になり得る事は事実である。
頭とINDEXは使いよう！
猛暑 茅ヶ崎にて
〜続・INDEXに関する検証 その１〜
by つけまい＆モンキーターン
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ Oracle 管理ツール Performance Insight ▼━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Oracleを知り尽くしたメンバーが開発したOracleパフォーマンス監視ツール
の決定版。それがPerformance Insightです。インサイトテクノロジーの技術
者の知恵とノウハウがここに結集！
パフォーマンス監視だけでなくOracleを使用しているシステムの運用、管理、
そして開発にも役立つ機能がいっぱいです。
詳しくは以下のURLをご覧ください。
http://www.insight-tec.com/jp/products/products.html
また無料で試使用することも可能です。
是非お問い合わせください。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ 連載情報 ▼━━━━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜雑誌連載：体験的・DBAのお仕事（DB Magazine（翔泳社）)＞
Oracleのエキスパートとして定評のある弊社のスタッフが執筆しております
連載記事です。Oracle関連業務に携わる方、是非ご一読ください。
９月号が発売中です。
http://www.insight-tec.com/jp/topics/magazine.html
上記のURLで各回のタイトルがご覧になれます。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ 書籍ご予約受付中 ▼━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜送料無料でお届けします＞
「Oracle8 プロフェッショナルテクニック」は大好評につき、現在在庫切れと
なっており、ご注文いただいた皆様にはたいへんご迷惑をおかけしております。
まもなく入荷する予定ですので、今しばらくお待ちください。
これからご注文いただく方も、入荷次第発送させていただくということで
予約注文を受付けておりますのでよろしくお願い申し上げます。
また弊社のHPよりお申し込みいただいた方に限り、送料無料でお届けします。
専門書としては異例の速さで増刷が決定するほどの好評をいただい
ている「Oracle8 プロフェッショナルテクニック」をぜひご活用ください。
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-2000, Insight Technology Inc., All rights reserved.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Insight Technology, Inc
Japanese | English