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

＜共有プールに関する検証　その2＞　ペンネーム　ダーリン

–　V$SQLAREA　と　負荷の高いSQL　-－

今週も、先週に続いて共有プールを覗いていこう。

先週は、動的パフォーマンスビューのV$SQLAREA表を使って類似SQL文を見つけ
る方法と、CURSOR_SHARINGを設定して、バインド変数の利用と同等の効果を実
現する方法を紹介した。

さて、今回はそのV$SQLAREA表から負荷の高いSQLを見つけてみよう。

V$SQLAREA表には、SQLの負荷を見極めるために有効な情報として以下のような
統計データがある。

===　　===

SHARABLE_MEM
EXECUTIONS
LOADS
FIRST_LOAD_TIME
PARSE_CALLS
DISK_READS
BUFFER_GETS
ROWS_PROCESSED
ADDRESS
HASH_VALUE

上記のカラムはいずれも重要な情報であるが、負荷が高いかどうかを見極め
るためには、”DISK_READS”と、”BUFFER_GETS”の値が重要な意味を持つ。

“DISK_READS”は文字通り、そのSQLが取得したデータの内、ディスクから取得
したデータ量（Oracle BLOCKS）をあらわしている。
当めるまがでもたびたび登場するが、ディスクからのデータ取得はメモリか
らの取得と比較してかなり時間が掛かる。よってこの値が大きいSQLが負荷の
高いSQLの第一候補となる。

ところが昨今のシステムでは、ディスクからのデータ読み込みが発生してい
ないのに（”DISK_READS”の値はさほど大きくないのに）レスポンスが悪いな
どという珍（？）現象が起こっている。でも、ディスク読み込みが発生して
いないからSQLには問題ないように見える。このような環境では、おそらく巨
大に設定したデータベースバッファ上にほとんどのデータを載せていること
が予想されるが、「なんだ理想的な状態じゃないか。」とは思わないでほし
い。

メモリ上で処理が完結すること自体は問題ないが、このメモリ上で、全件検
索のSQL文が乱発されている場合は大いに問題である。いくらハードウェアの
性能が向上したからといっても、これではきりがない。
また、これらはディスクからのデータ読み込みを引き起こす予備軍でもある。
データ件数が変われば、とたんに物理読み込みを引き起こすSQLに変貌する。

“DISK_READS”が発生しているSQLはまさしく「氷山の一角に過ぎない」のであ
る。

言い換えれば、SQL文自体の負荷を見極めるためには、実は”DISK_READS”より
も”BUFFER_GETS”が重要であるということだ。。

いずれにしても、以下のSQL文を参考にしてそれぞれの値が大きいSQL文を特
定してみよう。

負荷の高いSQL文を確認するSQL文?(DISK_READSが大きいSQL文の取得)

  1  select sql_text
  2        ,executions
  3        ,disk_reads
  4        ,buffer_gets
  5  from v$sqlarea
  6  where decode(executions ,0,disk_reads,disk_reads/executions)
  7         &gt; (select avg(decode(executions,0,disk_reads
  8                                        ,disk_reads/executions))
  9                   + stddev(decode(executions,0,disk_reads
 10                                        ,disk_reads/executions))
 11            from v$sqlarea)
 12* and parsing_user_id != 0
SQL> /

SQL_TEXT
---------------------------------------------------------------------
EXECUTIONS DISK_READS BUFFER_GETS
---------- ---------- -----------
select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO from scott
.t_emp where EMPNO = 7900
         8        868        6973

負荷の高いSQL文を確認するSQL文?(BUFFER_GETSが大きいSQL文の取得)

  1  select sql_text
  2        ,executions
  3        ,disk_reads
  4        ,buffer_gets
  5  from v$sqlarea
  6  where decode(executions ,0,buffer_gets,buffer_gets/executions)
  7         &gt; (select avg(decode(executions,0,buffer_gets
  8                                        ,buffer_gets/executions))
  9                   + stddev(decode(executions,0,buffer_gets
 10                                             ,buffer_gets/executions))
 11            from v$sqlarea)
 12* and parsing_user_id != 0
SQL> /

SQL_TEXT
--------------------------------------------------------------------
EXECUTIONS DISK_READS BUFFER_GETS
---------- ---------- -----------
BEGIN DBMS_OUTPUT.DISABLE; END;
         2          0         608

select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO from scott
.t_emp where EMPNO = 7900
         8        868        6973

上記のSQL文を実行すると、1SQL実行あたりのDISK_READSまたはBUFFER_GETS
が共有プールに存在するSQL全体の[平均値]+[標準偏差]を超えるSQL文を取得
することが出来る。

ここで取得されたSQL文について全件検索を行っていないかどうか、実行計画
を取得して見ることをお勧めする。

上記では”1実行あたりの負荷が高い”という観点で「負荷の高いSQL文」を取
得した。もうひとつ、注意していただきたい点がある。”1実行あたりの負荷”
はそれほど高くはないが、実行回数が極めて多いSQLである。

最近多いWEBシステムのバックで稼動するデータベースの場合は、このケース
で負荷が掛かることが多いのではないだろうか。
これに該当するようなSQLを取得する場合は”EXECUTIONS”と”BUFFER_GETS”の
値が大きいSQLを取得すればよいだろう。

ただし、WEB系のシステムで稼動するデータベースの場合、接続数などSQL文
以外の問題が潜んでいる場合も多い。

今回、メモリ上にデータが載っていても全件検索が行われているようだと問
題だということを述べた。そこで次回は、全件検索が問題となる一例を
V$LATCH表から見てみよう。

以上　ボディーボーダーいっぱいの茅ヶ崎にて

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

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