┏┏┏┏┏━━━━━━━━━━━━━━━━━━━━━━━━…・・ ┏━
┏┏┏┏┛ 2002.07.17 ┏┛┛
┏┏┏┛ ☆おら！オラ！Oracle -どっぷり検証生活-★ ┏┛┛┛
┏┏┛ ┏┛┛┛┛
┏┛・・…━━━━━━━━━━━━━━━━Vol.113━…・・┏┛┛┛┛┛
◇目次◇
■Oracle検証生活・・・共有プール領域に関する検証 その１
■お知らせ・・・○ＱＡについて
■編集者より
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
■□注意事項□■
本文中にテーブルが含まれていますので、お読みになる際はMSゴシッ
ク等、等幅フォントをお使いただくことをお勧めします。
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 Oracle 検証生活 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
＜共有プールに関する検証 その１＞ ペンネーム ダーリン
-- 類似SQLとCURSOR_SHARING -−
データベースバッファに続いて、今週からは共有プール領域に関する検証を行
ってみよう。
データベースを利用するためにはまずSQL文を発行しないことには、何も始ま
らない。データの登録然り、登録したデータの閲覧もまた然りである。 とこ
ろが、このSQL文が曲者で安直なSQLを発行してしまうとデータベースの処理
に負荷をかけてしまうのはご存知のとおりである。
これらのSQL文はインタプリタ言語と同様、クライアントから発行されるごと
に翻訳され、その翻訳結果を元にOracleがその処理を実行する。 "但し"、お
なじSQLがすでに実行されている場合は、これに当てはまらず、すでに翻訳さ
れている結果を元にOracleは処理を行う。つまり翻訳処理が省かれることにな
る。
実は、これが簡単にはじめることの出来る"SQLチューニング"の第一歩かもし
れない。翻訳処理自体は、アプリケーション開発者がまったく手をつけられ
ない部分であり、この負荷を低減できるのは、極力発行するSQLの種類を減ら
すこと以外にない。
特に、固有値が埋め込まれているSQL文は、出来る限りバインド変数を用いた
形に書き換えるべきである。
このようなSQL文を共有メモリ上から簡易的に見つけてみよう。
まず、以下のようなSQL文を発行する。(文末「類似SQLの発行スクリプト」参照)
(ページ幅の都合上改行を入れています。）
SQL_TEXT
---------------------------------------------------------------------
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL
FROM EMP WHERE EMPNO = 0
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL
FROM EMP WHERE EMPNO = 1
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL
FROM EMP WHERE EMPNO = 10
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL
FROM EMP WHERE EMPNO = 100
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL
FROM EMP WHERE EMPNO = 101
<省略 (200 SQL発行）>
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP WHERE EMPNO = 0
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP WHERE EMPNO = 1
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP WHERE EMPNO = 10
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP WHERE EMPNO = 11
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP WHERE EMPNO = 12
<省略 (100 SQL発行）>
上記のSQL文を発行してから、類似SQLの取得を試みる。
V$SQLAREA表から、類似SQLを見つけるSQL文
1 SELECT COUNT(HASH_VALUE) COPIES, SUBSTRB(SQL_TEXT,1,64) SQL_TEXT
2 FROM V$SQLAREA
3* GROUP BY SUBSTRB(SQL_TEXT,1,64) ORDER BY COPIES DESC
SQL> /
COPIES SQL_TEXT
------ ----------------------------------------------------------------
200 SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL FROM EMP WHERE EMPN
100 SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM
2 select u.name, o.name, trigger$.sys_evts, trigger$.type# from ob
1 select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
1 select local_tran_id, global_tran_fmt, global_oracle_id, global_
< 省略 >
上記の結果のうち上位2つのSQLは、類似したSQLがそれぞれ200個、100個発行さ
れていることをあらわしている。
実際に発行したSQL文はともにWHERE句に指定した値のみが異なるだけである。
バインド変数を用いて、ともにSQL文を共有するように努めるべきである。
ところで"CURSOR_SHARING"というパラメータをご存知だろうか？
類似したSQLであっても通常カーソルは共有されない。ところがこのパラメー
タを"SIMILAR"または"FORCE"に設定することで、一部リテラルが異なるSQLで
あってもカーソルの共有化が図れるというものである。
早速試してみよう。
SYSユーザーで、以下のSQLを実行する。
SQL> ALTER SYSTEM SET CURSOR_SHARING=FORCE;
システムが変更されました。
先ほど同じ様に、類似したSQL文を発行したあとで類似SQLを取得してみる。
COPIES SQL_TEXT
------ ----------------------------------------------------------------
200 SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL FROM EMP WHERE EMPN
100 SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM
2 select u.name, o.name, trigger$.sys_evts, trigger$.type# from ob
1 select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
1 select local_tran_id, global_tran_fmt, global_oracle_id, global_
< 省略 >
その結果は・・・むむ！？ 変わっていない。つまり、バインド変数化したSQL
すら発行されていないことになる。
「意味ないじゃん！」と早合点してはいけない。
これは、"CURSOR_SHARING"を設定した場合に、必ずバインド変数化したSQLを
発行するとは限らないことを意味している。すでにそのSQLが存在していれば、
バインド変数化はせずにそのSQLを使用するのである。
では、FLUSHして再度同様の確認を行うと、
COPIES SQL_TEXT
------ ----------------------------------------------------------------
2 select i.obj#, i.flags, u.name, o.name from sys.obj$ o, sys.
2 select u.name, o.name, trigger$.sys_evts, trigger$.type# from ob
1 SELECT MIN(TIME_MP), COUNT(*) FROM SMON_SCN_TIME WHERE THREAD =
1 UPDATE SMON_SCN_TIME SET SCN_WRP=:1, SCN_BAS=:2, TIME_MP=:3, TIM
1 alter system flush shared_pool
-> 1 SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL FROM EMP WHERE EMPN
-> 1 SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM
1 select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) fro
ごらんのとおり類似SQLが減少することになる。
果たして、SQL全文はどのようになっているのだろう。
****** CURSOR_SHARING = FORCE 前 **************
SQL_TEXT
----------------------------------------------------------------
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL FROM EMP WHERE EMPN
O = 104
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM
EMP WHERE EMPNO = 0
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
****** CURSOR_SHARING = FORCE 後 **************
SQL_TEXT
----------------------------------------------------------------
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL FROM EMP WHERE EMPN
O = :"SYS_B_0"
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM
EMP WHERE EMPNO = :"SYS_B_0"
もともとリテラルで記述されていた部分は、システムで割り振られたバインド
変数で置き換えられている。
アプリケーション開発時に注意していれば、多数にも及ぶ類似SQLの発行は防
げるだろうが、すでにカットオーバーされた今では、すぐに手が打てない場合
もあるだろう。そうでなくても興味のある方はぜひおためしあれ。
いずれにしても、まずはテスト環境で。。
今回参照したV$SQLAREA表はSQLに関する統計情報を見るために使うことが出
来る。これらの統計情報をもとに実行回数や、物理読み込みの多いSQL文、い
わゆる負荷の高いSQL文を見つけることが出来る。
次回は、このV$SQLAREA表を覗いてみよう。
「類似SQLの発行スクリプト」
--- POPSQL START -----------------
CONNECT SCOTT/TIGER
LOOP(I=0;I<100;I++)
SAMPLE SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP WHERE EMPNO = \I\;
ENDLOOP
LOOP(I=0;I<200;I++)
SAMPLE SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL
FROM EMP WHERE EMPNO = \I\;
ENDLOOP
EXIT
--- POPSQL END -------------------
以上 台風一過の茅ヶ崎にて
◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
一緒に検証してみませんか？
http://www.insight-tec.com/jp/html/company/career.html
◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 ＱＡについて 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
＜皆様からのＱＡを受付けております＞
皆様のＱＡにはできるだけ、お答えしたいと思っています。
すべてのＱＡにお答えすることはできないかもしれませんが、
適宜メルマガ内でとりあげていく予定ですので、是非ＱＡをお寄せください。
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 編集者より 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
カエルの鳴き声がよく聞こえる季節になってきましたね。遠くから聞いてい
るぶんには全然、問題ないのですが・・。東京から遊びに来た友達はカエル
が珍しいらしく、急に捕まえに行く話になってしまい。カエル好きの友達を
呼び出して茅ヶ崎近辺をドライブすることに。鳴き声が大きくなるにつれて
私が小さくなっていったのでさすがに捕まえることはなかったのですが・・
二度とこの２人とは夏に会うのはやめようと心に決めた一日でしたっ。
by TI
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
登録・解除は以下のURLで行うことができます。
http://www.insight-tec.com/jp/html/ora3/ora3.html
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜おら！オラ！Oracle−どっぷり検証生活−＞
発行／編集：株式会社インサイトテクノロジー
http://www.insight-tec.com
マガジンID：0000030093
本メールマガジンに掲載された記事を許可なく転載することを禁じます。
Copyright (c) 1996-2002, Insight Technology, Inc. All rights reserved.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Insight Technology, Inc
Japanese | English