共有プール領域に関する検証 その１
＜共有プールに関する検証 その１＞ ペンネーム ダーリン
– 類似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
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
上記の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 -------------------
以上 台風一過の茅ヶ崎にて