ORACLE技術研究所～バックナンバー～

　「ORACLE技術研究所」が本になりました！！

　

　書名：「ORACLE技術研究所　知って得するSQL」
　発行元：アルファポリス出版
　著者：榎本茂男
　定価：￥1,600＋税
　ISBN：4-434-08663-4
　出版社のページ

　御協力いただいた皆さん、ありがとうございました。
　全国の書店で絶賛発売中です。

　なお以下のオンライン書店で購入可能です。

　Amazon
　楽天ブックス
　7&Y(セブンアンドワイ)
　bk1

　「ORACLE技術研究所」が本になりました！！

　

　書名：「Oracleはこう動いている。Oracle徹底検証」
　発行元：アルファポリス出版
　著者：榎本茂男
　定価：￥1,600＋税
　ISBN：4434071610
　出版社のページ

　御協力いただいた皆さん、ありがとうございました。
　全国の書店で絶賛発売中です。

　なお以下のオンライン書店で購入可能です。

　Amazon
　楽天ブックス
　7&Y(セブンアンドワイ)
　bk1

「ORACLE技術研究所」は「まぐまぐ」さんから週刊で配信しているメルマガです。
メルマガの登録、解除は、下記のサイトからどうぞ

『ORACLE技術研究所』(ID:0000126828)

なお、読者プレゼントや編集後記はメルマガ限定の特典です。
お読みになりたい方は是非メルマガを登録してください。
また、バックナンバーの記載は発行者の都合により掲載が遅れることが多々あります。
あらかじめ御了承ください。
（メルマガは、ほぼ週刊で発行しております。）

◇仮説の証明

　それでは前述の仮説が正しいかどうか検証してみよう。
　INDEXに存在しない項目が検索条件に入っていることによって性能が劣化し
ているのであれば、その項目を検索条件から外してやれば良いのだ。

　新たに以下のSQLを実行しSQL*Traceを取得する。

　・パターン４
　　select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_1) */ *
　　from INDEX_TEST
　　where COLUMN2 = 1;

　「COLUMN1 = 99999」という条件を外しても検索結果は変わらず100,000件。
　なので他の条件は同一となる。
　この検索でパターン２と同程度の検索性能が出れば仮説は正しいと思える。
　それではSQL*Traceを取得し内容を確認してみよう。

　・パターン４（COLUMN2のみのINDEXを使用。）

　********************************************************************************
　
　select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_1) */ *
　from INDEX_TEST
　where COLUMN2 = 1
　
　call count cpu elapsed disk query current
rows
　------- ------ -------- ---------- ---------- ---------- ----------
----------
　Parse 1 0.26 0.46 0 0 0
0
　Execute 1 0.00 0.00 0 0 0
0
　Fetch 6668 6.20 119.91 100197 106864 0
100000
　------- ------ -------- ---------- ---------- ---------- ----------
----------
　total 6670 6.46 120.38 100197 106864 0
100000
　
　Misses in library cache during parse: 1
　Optimizer mode: ALL_ROWS
　Parsing user id: SYS
　
　Rows Row Source Operation
　------- ---------------------------------------------------
　 100000 TABLE ACCESS BY INDEX ROWID INDEX_TEST (cr=106864 pr=100197 pw=0
time=96560385 us)
　 100000 INDEX RANGE SCAN IND_INDEX_TEST_1 (cr=6864 pr=197 pw=0
time=1748328 us)(object id 59176)
　
　********************************************************************************

　パターン１、パターン２の時と比較してelapsedの値は、どうだろうか？
　totalの行だけ並べて比較してみよう。

　 cpu elapsed disk query current
rows
　 -------- ---------- ---------- ---------- ----------
----------
　パターン１ 6.95 134.81 100197 106864 0
100000
　パターン２ 6.70 124.30 100267 106934 0
100000
　パターン４ 6.46 120.38 100197 106864 0
100000

　結果としてパターン２よりも4Sec.程度速くなった。
　これは物理的なデータの読み込み数（disk）や論理的なデータの読み込み数
（query）の差だろう。

　上記の結果から
　「検索条件に指定されている項目の中にINDEXに含まれていない項目が存在す
　る場合、検索条件に指定されている項目が全てINDEXに含まれているよりも性
　能劣化が起こる。」
ということが言える。

　以上でINDEXに存在しない項目を検索条件に入れた際の性能に対する影響の
検証を終わる。


◇おまけ

　実はパターン３というSQLもTraceを取得していたのを覚えているだろうか？

　・パターン３
　　select /*+ FULL(INDEX_TEST) */ *
　　from INDEX_TEST
　　where COLUMN1 = 99999
　　and COLUMN2 = 1;

　このSQLは性能の参考値としてTraceを取得していた。
　せっかくなので、このSQLもTraceを確認してみよう。

　********************************************************************************
　
　select /*+ FULL(INDEX_TEST) */ *
　from INDEX_TEST
　where COLUMN1 = 99999
　and COLUMN2 = 1
　
　call count cpu elapsed disk query current
rows
　------- ------ -------- ---------- ---------- ---------- ----------
----------
　Parse 1 0.14 0.55 0 0 0
0
　Execute 1 0.00 0.00 0 0 0
0
　Fetch 6668 5.67 70.95 140847 147442 0
100000
　------- ------ -------- ---------- ---------- ---------- ----------
----------
　total 6670 5.81 71.50 140847 147442 0
100000
　
　Misses in library cache during parse: 1
　Optimizer mode: ALL_ROWS
　Parsing user id: SYS
　
　Rows Row Source Operation
　------- ---------------------------------------------------
　 100000 TABLE ACCESS FULL INDEX_TEST (cr=147442 pr=140847 pw=0
time=40961305 us)
　
　********************************************************************************

　・・・・・・。
　・・・・・・。
　・・・・・・。
　・・・・・・。
　・・・・・・。
　・・・・・・。

　正直、この結果にはちょっとびっくり。
　何がって？
　分かりやすいようにtotalの行だけ他の３つのSQLと並べて比較してみよう。

　 cpu elapsed disk query current
rows
　 -------- ---------- ---------- ---------- ----------
----------
　パターン１ 6.95 134.81 100197 106864 0
100000
　パターン２ 6.70 124.30 100267 106934 0
100000
　パターン３ 5.81 71.50 140847 147442 0
100000
　パターン４ 6.46 120.38 100197 106864 0
100000

　もう御理解いただけただろうか？？
　elapsedの値に注目してほしい。
　TABLE FULL SCANさせたパターン３のSQLがINDEX SCANさせた他のSQLよりも
性能が良い。。。

　一般的に検索を実行する場合、INDEXで抽出対象となるRECORDをTABLE全体の
件数の5%以下に絞り込むことが出来るのであればINDEX SCANの方が性能が良い
と言われている。
　今回の検索ではINDEXでTABLEの件数の1%まで絞り込めるにも関わらずTABLE
FULL SCANの方が性能が良いとは・・・。
　まぁ、ケースバイケースということだな。
　検索を実行する時は、あまり一般論に囚われずちゃんと実環境に則した環境
で性能評価を行った方が良い。

◇ORACLEが検索結果を返すまでの流れ

　ユーザがSELECT文を発行するとORACLE内部では以下のような動作をする。

　１．SHARED_POOL内に同じSQL文が存在するかの確認。
　２．SQL文の構文確認。
　３．データディクショナリから表と列の定義を確認。
　４．ユーザのオブジェクト権限を確認。
　５．実行計画の確定。
　６．実行計画に従いデータ（BLOCK）をDB_BUFFER_CACHEにロード。
　７．結果をユーザに返す。

　上記のうち１～５までがParse。
　６がExecute。
　７がFetchとなる。

　SQL*Traceの結果を見ると７のFetchがelapsedの大部分を占めている。
　約10Sec.の時間差も、ここで発生している。
　なぜ、このような結果に至ったのか？？
　ここからは、あくまで推測でしかないが、この時、ORACLEの内部では以下の
ような動作をしていたと思われる。


　・パターン１（COLUMN2のみのINDEXを使用。）

　INDEX 　　　　　　　　　　　　TABLE
　+--+--+--+　　　　　　　　　　+--+--+--+--+--+
　| | | |　　　　　　　　　　| | | | | |
　+--+--+--+　　　　　　　　　　+--+--+--+--+--+
　| | | |　　　　　　　　　　| | | | | |
　+--+--+--+　　　　　　　　　　+--+--+--+--+--+
　| | | |　　　　　　　　　　| | | | | |
　+--+--+--+　　　　　　　　　　+--+--+--+--+--+
　　｜　　　　　　　　　　　　　　｜　　　　　　　　　　　　
　　｜1.'COLUMN2 = 1'の 　　　　　｜2.'COLUMN2 = 1'のRECORDを
　　｜　RECORDのROWIDを取得し 　　｜　BUFFER_CACHEにロード。
　　｜　BUFFER_CACHEにロード。　　｜
　　↓　　　　　　　　　　　　　　↓
　BUFFER_CACHE
　+-------------------------------------+
　| |
　| |
　| |
　| |
　+-------------------------------------+
　3.TABLEから取得したRECORDの中で'COLUMN1 = 99999'の条件に合致する
　 RECORDをユーザプロセスへ返す。


　・パターン２（COLUMN1、COLUMN2の複合INDEXを使用。）

　INDEX 　　　　　　　　　　　　TABLE
　+--+--+--+--+　　　　　　　　　+--+--+--+--+--+
　| | | | |　　　　　　　　　| | | | | |
　+--+--+--+--+　　　　　　　　　+--+--+--+--+--+
　| | | | |　　　　　　　　　| | | | | |
　+--+--+--+--+　　　　　　　　　+--+--+--+--+--+
　| | | | |　　　　　　　　　| | | | | |
　+--+--+--+--+　　　　　　　　　+--+--+--+--+--+
　　｜　　　　　　　　　　　　　　｜　　　　　　　　　　　　
　　｜1.'COLUMN1 = 99999'かつ 　　｜2.'COLUMN1 = 99999'かつ
　　｜　'COLUMN2 = 1'の 　　　　　｜　'COLUMN2 = 1'の
　　｜　RECORDのROWIDを取得し 　　｜　のRECORDを
　　｜　BUFFER_CACHEにロード。　　｜　BUFFER_CACHEにロード。
　　↓　　　　　　　　　　　　　　↓
　BUFFER_CACHE
　+-------------------------------------+
　| |
　| |
　| |
　| |
　+-------------------------------------+
　3.TABLEから取得したRECORDをユーザプロセスへ返す。


　２つのパターンの工程を比較すると3.に大きな違いがある。
　パターン２の場合はINDEXのみで対象のRECORDを特定できる。
　対してパターン１の場合はTABLEまで読み込まないと対象のRECORDを特定
できない。
　おそらく、この差がelapsedに顕れたのだろう。

　来週は、この仮説が正しいことを証明するための検証を実施する。

◇検証内容

　作成したTABLE、INDEX_TESTに対して以下のSQLを実行する。

　・パターン１
　　select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_1) */ *
　　from INDEX_TEST
　　where COLUMN1 = 99999
　　and COLUMN2 = 1;

　・パターン２
　　select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_2) */ *
　　from INDEX_TEST
　　where COLUMN1 = 99999
　　and COLUMN2 = 1;

　パターン１の場合、Hint句で指定されたINDEXが持っている項目はCOLUMN2
のみ。
　（検索条件に指定されている項目の中にINDEXに含まれていない項目が存在
　する。）
　パターン２の場合、Hint句で指定されたINDEXはCOLUMN1とCOLUMN2、両方の
項目を持っている。
　（検索条件に指定されている項目が全てINDEXに含まれている。）
　両パターン共、指定されている検索条件は同一。
　そして両パターン共、抽出してくる件数は100,000件。
　２つのSQLを実行する際にSQL*Traceを取得し実行時間に差が出るかを検証
する。

　果たして検索性能に差が出るだろうか？？
　抽出してくる件数が一緒であれば、さほど性能に差は出ないように思われる。
　IND_INDEX_TEST_1はCOLUMN2のみで構成されるINDEXなので、IND_INDEX_TEST
_2に比べオブジェクトのサイズが小さいはず。
　それを考慮するとパターン１のほうが若干、速いだろうか？？

　ついでに、参考値としてTABLE Full Scanさせた場合のTraceも取得してお
こう。

　・パターン３
　　select /*+ FULL(INDEX_TEST) */ *
　　from INDEX_TEST
　　where COLUMN1 = 99999
　　and COLUMN2 = 1;

　それぞれのSQLを発行する直前に
　ALTER SYSTEM FLUSH BUFFER_CACHE;
　ALTER SYSTEM FLUSH SHARED_POOL;
を発行しBUFFER_CACHEとSHARED_POOLを空っぽにした。
　言うまでも無いが、これはBLOCKがメモリに乗ってしまうことにより正確な
性能が測れなくなることを防ぐ為。


◇検証結果

　それでは取得したSQL*Traceの内容を確認してみよう。

　・パターン１（COLUMN2のみのINDEXを使用。）

　********************************************************************************
　
　select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_1) */ *
　from INDEX_TEST
　where COLUMN1 = 99999
　and COLUMN2 = 1
　
　call count cpu elapsed disk query current rows
　------- ------ -------- ---------- ---------- ---------- ---------- ----------
　Parse 1 0.21 0.41 0 0 0 0
　Execute 1 0.00 0.00 0 0 0 0
　Fetch 6668 6.73 134.39 100197 106864 0 100000
　------- ------ -------- ---------- ---------- ---------- ---------- ----------
　total 6670 6.95 134.81 100197 106864 0 100000
　
　Misses in library cache during parse: 1
　Optimizer mode: ALL_ROWS
　Parsing user id: SYS
　
　Rows Row Source Operation
　------- ---------------------------------------------------
　 100000 TABLE ACCESS BY INDEX ROWID INDEX_TEST (cr=106864 pr=100197 pw=0 time=106405267 us)
　 100000 INDEX RANGE SCAN IND_INDEX_TEST_1 (cr=6864 pr=197 pw=0 time=19448260 us)(object id 59176)
　
　********************************************************************************

　・パターン２（COLUMN1、COLUMN2の複合INDEXを使用。）

　********************************************************************************
　
　select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_2) */ *
　from INDEX_TEST
　where COLUMN1 = 99999
　and COLUMN2 = 1
　
　call count cpu elapsed disk query current rows
　------- ------ -------- ---------- ---------- ---------- ---------- ----------
　Parse 1 0.17 0.40 0 0 0 0
　Execute 1 0.00 0.00 0 0 0 0
　Fetch 6668 6.53 123.89 100267 106934 0 100000
　------- ------ -------- ---------- ---------- ---------- ---------- ----------
　total 6670 6.70 124.30 100267 106934 0 100000
　
　Misses in library cache during parse: 1
　Optimizer mode: ALL_ROWS
　Parsing user id: SYS
　
　Rows Row Source Operation
　------- ---------------------------------------------------
　 100000 TABLE ACCESS BY INDEX ROWID INDEX_TEST (cr=106934 pr=100267 pw=0 time=113771208 us)
　 100000 INDEX RANGE SCAN IND_INDEX_TEST_2 (cr=6934 pr=267 pw=0 time=645296 us)(object id 59181)
　
　********************************************************************************

　まず、実行計画を確認してみよう。
　どちらのSQLも間違いなく指定したINDEXを使用して検索をしている。

　続いてelapsedを比較してみよう。
　このままだと見難いのでtotalだけ抜き出して並べてみる。

　 cpu elapsed disk query current rows
　 -------- ---------- ---------- ---------- ---------- ----------
　パターン１ 6.95 134.81 100197 106864 0 100000
　パターン２ 6.70 124.30 100267 106934 0 100000

　・・・・・・あれ？
　なんか、おかしいな。
　disk、queryはパターン１の方が少ない。
　これは当初の予想通りIND_INDEX_TEST_1の方がCOLUMN1を持っていない分、
OBJECTのサイズが小さいのだろう。
　にも関わらず、elapsedはパターン１の方が大きい値を示している。
　つまり、それだけ時間がかかっているということになる。
　その差、約10秒。
　この時間差は、なんだろう？？

　測定誤差かも？と思い何度か計測し直してみたが、やはり同様の差が出てし
まう。
　では、この結果を正として、こうなる理由を考えてみよう。

　続きは来週・・・

◇INDEXに存在しない項目を検索条件に入れた際の性能に対する影響

　TABLEからRECORDをSELECTする際、WHERE以下にSELECTしてくるRECORDの条件
を記述することができる。
　（SELECT文の機能：選択）

　この時、
　・検索条件に指定されている項目が全てINDEXに含まれている場合。
　・検索条件に指定されている項目の中にINDEXに含まれていない項目が存在
　する場合。
の２パターンで検索性能が、どの程度変化するのか検証してみたいと思う。

◇検証環境作成

　検証環境：
　RDBMS:ORACLE10.1.0
　OS:WindowsXP
　OptimizeMode=ALL_ROWS
　（統計情報未取得）

　まずはTABLEの作成。

　SQL> create table
　 2 INDEX_TEST(
　 3 COLUMN1 number(5),
　 4 COLUMN2 number(5),
　 5 COLUMN3 char(10),
　 6 COLUMN4 char(10),
　 7 COLUMN5 char(10),
　 8 COLUMN6 char(10),
　 9 COLUMN7 char(10),
　 10 COLUMN8 char(10),
　 11 COLUMN9 char(10),
　 12 COLUMN10 char(10))
　 13 tablespace user03;
　
　Table created.

　続いてデータの作成。

　SQL> begin
　 2 for i1 in 1..100000 loop
　 3 for i2 in 1..100 loop
　 4 insert into INDEX_TEST(
　 5 COLUMN1,
　 6 COLUMN2,
　 7 COLUMN3,
　 8 COLUMN4,
　 9 COLUMN5,
　 10 COLUMN6,
　 11 COLUMN7,
　 12 COLUMN8,
　 13 COLUMN9,
　 14 COLUMN10)
　 15 values
　 16 (99999,
　 17 i2,
　 18 'AAAAAAAAAA',
　 19 'BBBBBBBBBB',
　 20 'CCCCCCCCCC',
　 21 'DDDDDDDDDD',
　 22 'EEEEEEEEEE',
　 23 'FFFFFFFFFF',
　 24 'GGGGGGGGGG',
　 25 'HHHHHHHHHH');
　 26 end loop;
　 27 end loop;
　 28 end;
　 29 /
　
　PL/SQL procedure successfully completed.

　COLUMN1とCOLUMN3～10は全てのRECORDに同じ値が入っている。
　COLUMN2の値は1～100までの繰り返し。（行番号：2）
　TABLEの総件数は100＊100000で10,000,000件。（行番号：2＊行番号：3）
　条件式にCOLUMN2=1と記述すればTABLEの総件数の1％を取得できるように
した。

　次に、このTABLEに対してINDEXを作成する。

　SQL> create index IND_INDEX_TEST_1
　 2 on INDEX_TEST(COLUMN2)
　 3 tablespace index01;
　
　Index created.

　SQL> create index IND_INDEX_TEST_2
　 2 on INDEX_TEST(COLUMN1,COLUMN2)
　 3 tablespace index01;
　
　Index created.

　これで環境の作成は、おしまい。

　続きは来週・・・

素人なりのORACLE検証書籍化に伴い書籍に掲載される内容はH.P.で公開できなくなりました。
対象は以下のようになります。

・INDEXは、どこまで絞れるのか？？
・INDEXは行連鎖、行移行するのか？
・ORACLEはBLOCK LEVEL LOCK可能か？
・BITMAP INDEXの謎に迫る！！
・インスタンスが起動しない。。。（書籍版のみ掲載）

これらの内容をお読みになりたい方は11月末下旬発売予定の書籍版を購入してくださるようお願いいたします。

◇INDEXを使いたい！！



　読者の方から質問のメールを受けました。

　普段、このようなことがないので非常に嬉しいです♪



　↓以下、いただいたメールより抜粋。



-------------------------------------------------------------------------------------------

　SQLのWhere句に「||（パイプ）」を使った連結文字列を使用した場合にインデックスが効きません。

　本などで調べると「||」はインデックスが効かない、と確かに記述してあるのですが、対処法とかありますか？

　FunctionINDEXで「||」で連結させたキーを設定できるのかな？と思って試してみたのですがエラーがでてインデックス作成ができませんでした。ほかにいい方法があれば教えてください。



　tblA

　+---------+---------+---------+---------+

　|A_1(CHAR)|A_2(CHAR)|A_3(CHAR)|A_4(CHAR)|

　+---------+---------+---------+---------+

　| H1601| 01| AAA| 123|

　| H1601| 15| BBB| 123|

　| H1602| 03| CCC| 123|

　| H1603| 18| DDD| 123|

　| H1607| 24| EEE| 123|

　| H1607| 31| FFF| 123|

　+---------+---------+---------+---------+



　上記のようなテーブルがあったとして、以下のようなSQLを実行したいのですが・・・。



　SELECT * FROM tblA WHERE A_1 || A_2 > 'H160301'



　今、インデックスはA_1だけのものとA_1,A_2のものの2パターン作っているのですが、トレースで確認してもFULL検索になっています。

-------------------------------------------------------------------------------------------



　なるほど。

　確かに、このSQLだとINDEXを使えないなぁ。

　では、ちょっと対策を考えてみよう。





◇検証環境構築



　検証環境：

　RDBMS:ORACLE10.1.0

　OS:WindowsXP

　OptimizeMode=ALL_ROWS

　（統計情報未取得）



　--TABLEの作成

　create table tblA (A_1 CHAR(5),A_2 CHAR(2),A_3 CHAR(3),A_4 CHAR(3));



　--RECORDの追加

　insert into tblA values ('H1601','01','AAA','123');

　insert into tblA values ('H1601','15','BBB','123');



　insert into tblA values ('H1602','03','CCC','123');

　insert into tblA values ('H1603','18','DDD','123');

　insert into tblA values ('H1607','24','EEE','123');

　insert into tblA values ('H1607','31','FFF','123');



　commit;



　--INDEXの作成

　create index ind_tblA_1 on tblA(A_1);

　create index ind_tblA_2 on tblA(A_1,A_2);





◇問題



　さて、ここで問題。

　要件を満たしつつINDEXを使用した検索にするには、どうしたら良いか？





◇正解への道のり



　最初、このSQLを見た時、



　SELECT * FROM tblA WHERE A_1 > 'H1603' and A_2 > '01'



　で、いけるんじゃないか？？

　なーんだ、簡単簡単

と思った（間違い）

　よーく見るとCOLUMN A_1は年度と月、COLUMN A_2は日付を示していることがわかる。

　つまり要件は

　「指定した日付以降のデータを取得する」

ということになる。



　もし、



　SELECT * FROM tblA WHERE A_1 > 'H1603' and A_2 > '01'



と、やってしまうと本当はH16/03/01以降のデータを抽出したいはずなのにH16/04/01以降のデータしか抽出できない。



　で、どうするか？？

　考え方を、ちょっと変えてみる。

　H16/03/01以降というのは

　・H16/03/02～H16/03/31

と

　・H16/04/01～

の２つに分割することができる。



　ということは、つまり

　・A_1 = 'H1603' and A_2 > '01'

　・A_1 > 'H1603'

の２つの条件のうち、どちらかを満たすという式にすれば良いワケだ。

　SQLにすると以下のようになる。



　SELECT *

　FROM tblA

　WHERE (A_1 > 'H1603')

　or (A_1 = 'H1603' and A_2 > '01')



　これが正解。

　条件式を２つに分割して要件を満たす。

　これを「困難の分割」と呼ぼう。（探偵学園Ｑ風）





◇実行計画を確認してみよう！



　では、作成したSQLの実行計画を確認してみよう。



　比較の為、まずは変更前のSQLから。



　SQL> SELECT * FROM tblA WHERE A_1 || A_2 > 'H160301';



　Execution Plan

　----------------------------------------------------------

　 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=51)

　 1 0 TABLE ACCESS (FULL) OF 'TBLA' (TABLE) (Cost=2 Card=1 Bytes=51)



　いや、もう気持ち良いくらいTABLE FULL SCANが出てる。



　では、続いて修正したSQLの確認。



　SQL> SELECT *

　 2 FROM tblA

　 3 WHERE (A_1 > 'H1603')

　 4 or (A_1 = 'H1603' and A_2 > '01');



　Execution Plan

　----------------------------------------------------------

　 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=3 Bytes=51)

　 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TBLA' (TABLE) (Cost=2 Card=3 Bytes=51)

　 2 1 INDEX (RANGE SCAN) OF 'IND_TBLA_2' (INDEX) (Cost=1 Card=3)



　こちらは、ちゃんとINDEX RANGE SCANになっている。

　ちなみにRULE BASEの場合も確認してみた。



　SQL> SELECT /*+ RULE */ *

　 2 FROM tblA

　 3 WHERE (A_1 > 'H1603')

　 4 or (A_1 = 'H1603' and A_2 > '01');



　Execution Plan

　----------------------------------------------------------

　 0 SELECT STATEMENT Optimizer=HINT: RULE

　 1 0 CONCATENATION

　 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TBLA' (TABLE)

　 3 2 INDEX (RANGE SCAN) OF 'IND_TBLA_1' (INDEX)

　 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TBLA' (TABLE)

　 5 4 INDEX (RANGE SCAN) OF 'IND_TBLA_2' (INDEX)



　RBOの場合、２つのINDEXを使い分けて結果をCONCATENATEしているようだ。





◇他に方法は無いのかな？？



　とりあえずは解決したのだが、他に方法は無いのだろうか？？

　思いつく方法を試してみる。



　・||の代わりに+を使ったらどうだろう？？



　　プログラム言語によっては+を使用することで文字列の結合をしてくれる。

　　SQLでは、どうなるのか試してみよう。



　　SQL> SELECT * FROM tblA WHERE A_1 + A_2 > 'H160301';

　　SELECT * FROM tblA WHERE A_1 + A_2 > 'H160301'

*

　　ERROR at line 1:

　　ORA-01722: invalid number



　　ん？

　　エラーが出てしまった。

　　エラーの内容を調べてみよう。



　　エラー名

　　　数値が無効です。

　　原因

　　　数値列が無効であるため、文字列から数値への変換は失敗しました。

　　処置

　　　関数または式の中の文字列を確認してください。



　　あー、どうやらA_1とA_2を文字列から数値型に変換して計算しようとしているようだ。

　　A_1の値は'H1603'なので数値型に変換できない。

　　まぁ、仮にA_1に変換できる値が入っていたとしても期待していた結果は得られない。



　・||の代わりに&を使ったらどうだろう？？



　　プログラム言語によっては&を使用することで文字列の結合をしてくれる。

　　SQLでは、どうなるのか試してみよう。



　　SQL> SELECT * FROM tblA WHERE A_1 & A_2 > 'H160301';

　　Enter value for a_2:



　　おや？

　　なんか聞かれてきた。

　　どうやら& A_2が変数として認識されてしまったようだ。

　　これもダメだな。。。



　　誰か良い方法を知ってたら教えてください。

◇便利な新機能



　ORACLE10gから追加された新機能で非常に便利なコマンドを発見したので紹介したい。

　主にデバッグ用にしか使用できないが・・・。

　コマンドを見るとある程度、どのような内容か推測できるかもしれない。



　ALTER SYSTEM FLUSH BUFFER_CACHE;



　9iまではSHARED_POOLのFLUSHしか実行できなかった。

　しかし、10gからはBUFFER_CACHEのFLUSHも実行できるようになった。

　これで、性能検証の際、バッファに乗ったBLOCKを追い出す為にDBの再起動をする必要がなくなった。

　実はVol.19　ORACLE10g基礎性能検証～追加検証（解答編）～では、このコマンドを多用している。

　このコマンドのおかげで性能試験は本当に楽になったと言える。





◇BUFFER_CACHEって？



　ORACLE Silver Fellow（ORACLE入門）の内容だが簡単に説明を・・・。



　ORACLEのインスタンスはORACLEのバックグラウンドプロセスとSGAから構成されている。

　SGAとはORACLEの使用するメモリ空間のことでSHARED_POOLとBUFFER_CACHEから構成されている。

　BUFFER_CACHEは実データを読み込む為のメモリ空間。

　ORACLEに対する、検索、更新は全てこのBUFFER_CACHEを介して行なう事になる。





◇検証方法



　検証環境

　RDBMS:ORACLE10.1.0

　OS:Windows2000

　Optimizer mode: ALL_ROWS



　今回の検証は「ALTER SYSTEM FLUSH BUFFER_CACHE;」コマンドによりデータがキャッシュから追い出されていることを確認する。

　手順は以下の通り。



　/* sysにてログイン */

　/* DBの再起動 */



　shutdown;



　startup;



　/* 以下、作業用ユーザにてログイン */

　/* SQL Traceの取得 */



　alter session set sql_trace = true ;



　/* SQL1 */



　SELECT SUM(COLUMN3) FROM TEST_2;



　/* SQL2 */



　SELECT SUM(COLUMN3) FROM TEST_2;



　/* sysにて実行 */

　/* FLUSH BUFFER_CACHE */



　ALTER SYSTEM FLUSH BUFFER_CACHE;



　/* 作業用ユーザにて実行 */

　/* SQL3 */



　SELECT SUM(COLUMN3) FROM TEST_2;



　/* SQL Traceの終了 */



　alter session set sql_trace = false ;



　SQL1、SQL2、SQL3は同じSQLに見えて微妙に違う。

　SELECT句とSUM関数の間のスペースの数が、それぞれ

　　SQL1　・・・　1個

　　SQL2　・・・　2個

　　SQL3　・・・　3個

と、なっている。

　これは、3つのSQLを異なるSQLであるとORACLEに認識させる為。



　なぜ異なるSQLだと認識させる必要があるかと言うと、そのほうがTraceFileが見やすくて便利だからである。



　SQL1は再起動直後の為、DiskからBLOCKを読み込む。

　SQL2はSQL1を実行した際にBLOCKがキャッシュに乗っている為、Diskから読み込むBLOCKの数が減っているはず。

　（必要なBLOCKが全てキャッシュに乗っていればDiskは0となる。）

　SQL3はBUFFER_CACHEのFLUSHを行なった後に実行する為、SQL1と同等のDiskを読み込むはず。





◇検証結果



　以下が取得したTraceFileの中身。



　********************************************************************************



　SELECT SUM(COLUMN3) FROM TEST_2





　call count cpu elapsed disk query current rows

　------- ------ -------- ---------- ---------- ---------- ---------- ----------

　Parse 1 0.01 0.04 0 0 0 0

　Execute 1 0.00 0.00 0 0 0 0

　Fetch 2 0.04 0.14 497 500 0 1

　------- ------ -------- ---------- ---------- ---------- ---------- ----------

　total 4 0.06 0.19 497 500 0 1



　Misses in library cache during parse: 1

　Optimizer mode: ALL_ROWS

　Parsing user id: 64



　Rows Row Source Operation

　------- ---------------------------------------------------

　 1 SORT AGGREGATE (cr=500 pr=497 pw=0 time=145739 us)

　 100000 TABLE ACCESS FULL TEST_2 (cr=500 pr=497 pw=0 time=214712 us)



　********************************************************************************



　SELECT SUM(COLUMN3) FROM TEST_2





　call count cpu elapsed disk query current rows

　------- ------ -------- ---------- ---------- ---------- ---------- ----------

　Parse 1 0.00 0.00 0 0 0 0

　Execute 1 0.00 0.00 0 0 0 0

　Fetch 2 0.03 0.02 0 500 0 1

　------- ------ -------- ---------- ---------- ---------- ---------- ----------

　total 4 0.03 0.02 0 500 0 1



　Misses in library cache during parse: 1

　Optimizer mode: ALL_ROWS

　Parsing user id: 64



　Rows Row Source Operation

　------- ---------------------------------------------------

　 1 SORT AGGREGATE (cr=500 pr=0 pw=0 time=28258 us)

　 100000 TABLE ACCESS FULL TEST_2 (cr=500 pr=0 pw=0 time=200057 us)



　********************************************************************************



　SELECT SUM(COLUMN3) FROM TEST_2





　call count cpu elapsed disk query current rows

　------- ------ -------- ---------- ---------- ---------- ---------- ----------

　Parse 1 0.00 0.00 0 0 0 0

　Execute 1 0.00 0.00 0 0 0 0

　Fetch 2 0.04 0.13 497 500 0 1

　------- ------ -------- ---------- ---------- ---------- ---------- ----------

　total 4 0.04 0.13 497 500 0 1



　Misses in library cache during parse: 1

　Optimizer mode: ALL_ROWS

　Parsing user id: 64



　Rows Row Source Operation

　------- ---------------------------------------------------

　 1 SORT AGGREGATE (cr=500 pr=497 pw=0 time=132281 us)

　 100000 TABLE ACCESS FULL TEST_2 (cr=500 pr=497 pw=0 time=308852 us)



　********************************************************************************



　わかりにくいのでtotalだけ注目して見てみよう。



　 call count cpu elapsed disk query current rows

　 ------- ------ -------- ---------- ---------- ---------- ---------- ----------

　SQL1 total 4 0.06 0.19 497 500 0 1

　SQL2 total 4 0.03 0.02 0 500 0 1

　SQL3 total 4 0.04 0.13 497 500 0 1



　SQL1ではインスタンス起動後、初めての検索なのでDiskから497Block読み込んでいる。

　SQL2では検索に必要となるDataBlockが全てBUFFER_CACHEに載っているので、Diskから読み込みを行なう必要が無い。

　よってDiskが0になっている。

　SQL3では直前に「ALTER SYSTEM FLUSH BUFFER_CACHE;」コマンドが実行されている為、再度DiksからDataBlockを読み込む必要がある。

　このためSQL1と同じくDiskの値が497となる。

　予想通りの結果。



　上記の結果から「ALTER SYSTEM FLUSH BUFFER_CACHE;」コマンドを実行することによりBUFFER_CACHE上のDataBlockが追い出されている事を確認できた。

◇何を検証するのか？



　ORACLEに対する問い合わせを実行すると以下のような処理が内部的に行われる。



　１．SHARED_POOLに実行計画が存在するかのチェック



　　　↓１でSHARED_POOLに実行計画が存在した場合



　２．必要なデータがDB_CACHEに存在するかチェック

　３．２で存在しない場合、Diskから読み込み

　４．結果をユーザに返す



　　　↓１でSHARED_POOLに実行計画が存在しない場合



　２．SQL構文のチェック（OBJECTの存在チェック）

　３．１で存在しない場合は実行計画の作成

　４．必要なデータがDB_CACHEに存在するかチェック

　５．４で存在しない場合、Diskから読み込み

　６．結果をユーザに返す



　ざっくりとではあるが、このような流れとなる。

　今回、検証するのは１に関わる部分。

　SHARED_POOLに存在するSQLと同一のSQLが発行された場合、実行計画の作成処理をSkipする。

　これはムダな処理を無くし少しでもスループットを速くするため。



　この同一のSQLという定義が、かなり厳密で一言一句、同じでなければならない。

　アルファベットの大文字、小文字の違いすら許してもらえないのだ。



　例えば、

　SELECT SYSDATE FROM DUAL;

と、

　select sysdate from dual;

は、異なるSQLとして認識されてしまう。

　（ORACLEの仕様）



　また、

　SELECT * FROM EMP WHERE EMP_NO = 1

と、

　SELECT * FROM EMP WHERE EMP_NO = 2

も、異なるSQLとして処理される。

　（BIND変数を使用したSQLであれば、同一のSQLとして処理される。）



　今回は、この同一SQL判断がどこまで厳密か検証してみることにした。





◇SHARED_POOLって？？



　一応、簡単な説明を・・・

　SHARED_POOLはORACLEのSGAを形成する主要なメモリ領域の１つ。

　SQLに対する実行計画や、実行計画をたてるのに必要となるTABLEやINDEXの情報などがロードされている。





◇検証方法



　検証環境

　RDBMS:ORACLE9.0.1

　OS:Windows2000

　OptimizeMode=Choose

　（AnalyzeしていないためRBOで動作）



　SELECT SYSDATE FROM DUAL;

　　　

　SELECT SYSDATE

　FROM

　DUAL;



　SELECT SYSDATE FROM DUAL;

　　　

　上記３種類のSQLを実行しSQL*Traceを取得。

　もし全て同一のSQLとして判断されるのであればParseが１回、Executeが３回となるだろう。

　全て別のSQLとして認識されるのであればParse、Execute共に３回となるはずだ。



　予想としては、

　SELECT SYSDATE FROM DUAL;

と、

　SELECT SYSDATE

　FROM

　DUAL;

くらいは同一のSQLとして処理されるのではないかなぁ？？と。





◇検証結果



　取得したTraceFileの中身が以下の内容。



　 1 SELECT SYSDATE

　 2 FROM

　 3 DUAL

4

　 5 call count cpu elapsed disk query current rows

　 6 ------- ------ -------- ---------- ---------- ---------- ---------- ----------

　 7 Parse 1 0.00 　 0.42 0 0 0 0

　 8 Execute 1 0.00 0.47 0 0 0 0

　 9 Fetch 2 0.00 0.32 0 1 2 1

　10 ------- ------ -------- ---------- ---------- ---------- ---------- ----------

　11 total 4 0.00 1.21 0 1 2 1

12

　13 Misses in library cache during parse: 1

　14 Optimizer goal: CHOOSE

　15 Parsing user id: SYS

16

　17 Rows Row Source Operation

　18 ------- ---------------------------------------------------

　19 1 TABLE ACCESS FULL DUAL

20

　21 ********************************************************************************

22

　23 SELECT SYSDATE)

　24 FROM DUAL

25

　26 call count cpu elapsed disk query current rows

　27 ------- ------ -------- ---------- ---------- ---------- ---------- ----------

　28 Parse 1 0.00 0.06 0 0 0 0

　29 Execute 1 0.00 0.02 0 0 0 0

　30 Fetch 2 0.00 0.00 0 1 2 1

　31 ------- ------ -------- ---------- ---------- ---------- ---------- ----------

　32 total 4 0.00 0.08 0 1 2 1

33

　34 Misses in library cache during parse: 1

　35 Optimizer goal: CHOOSE

　36 Parsing user id: SYS

37

　38 Rows Row Source Operation

　39 ------- ---------------------------------------------------

　40 1 TABLE ACCESS FULL DUAL

41

　42 ********************************************************************************

43

　44 SELECT SYSDATE

　45 FROM

　46 DUAL

47

　48 call count cpu elapsed disk query current rows

　49 ------- ------ -------- ---------- ---------- ---------- ---------- ----------

　50 Parse 1 0.00 0.08 0 0 0 0

　51 Execute 1 0.00 0.00 0 0 0 0

　52 Fetch 2 0.00 0.00 0 1 2 1

　53 ------- ------ -------- ---------- ---------- ---------- ---------- ----------

　54 total 4 0.00 0.08 0 1 2 1

55

　56 Misses in library cache during parse: 1

　57 Optimizer goal: CHOOSE

　58 Parsing user id: SYS

59

　60 Rows Row Source Operation

　61 ------- ---------------------------------------------------

　62 1 TABLE ACCESS FULL DUAL

63

　64 ********************************************************************************



　どうやら全て別のSQLとして認識されたようだ。

　1行目からが改行を挿入したSQL。

　23行目からが普通のSQL。

　44行目からが各単語の間にスペースを３文字入れたSQL。



　改行してるのと普通のは同じSQLとして判断してくれると思ったんだけどなぁ。。。

　おそらくORACLE内部では、

　'SELECT SYSDATE FROM DUAL'

と、

　'SELECT SYSDATE 改行コード FROM 改行コード DUAL'

を比較しているのだろう。



　今回の検証結果としては、

　間にスペースや改行を入れてもORACLEは別SQLとして判断する。

ということになった。