共有プール領域に関する検証 その1

<共有プールに関する検証 その1> ペンネーム ダーリン

– 類似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 -------------------

以上 台風一過の茅ヶ崎にて