ソートに関する検証 その1

~ソートに関する検証 その1 ~
ペンネーム ちゃむ

今回から、ソートに関しての検証を行なう。ソートがシステムのボトルネックに
なることは、度々ある。例えば、大量更新を行なっていない時間帯にあるデ
ィスクに書き込みが集中していて、調べていくとHEAVYなSORTを伴うSQL文によって
一時表領域(ソート用の表領域)の書き込み量が激しいなんてことはよくある。

では、どのような処理がソートを伴なうのか見ていこう。
具体的に言うと、ORDER BY、GROUP BY、DISTINCT、CREATE INDEX、UNION、INTERSECT、
MINUS、ソート・マージ結合、ANALYZEコマンドなどがソートを伴う処理である。

ソートを伴う処理を実行して実際にソートを行なったかどうかを確認してみよう。
でもその前に、V$SYSSTATのソートに関する統計情報の説明をしよう。

<V$SYSSTATからメモリーソート率を求める>

sorts (memory)  メモリー上でソートした回数
sorts (disk)    ディスク上でソートした回数
sorts (rows)    ソートされた行の合計数

V$SYSSTATでsorts (memory) とsorts (disk)の情報をとってソート全体の回数に
対してメモリー上で何パーセント行われているか(メモリーソート率)をチェック
する方法は以下のSQLで確認できる。(メモリーソート率)
メモリーソート率 → sorts (memory)×100 /(sorts (memory) + sorts (disk))

SELECT A.VALUE * 100 / (A.VALUE+B.VALUE) MEM_SORT_RATE FROM V$SYSSTAT A,V$SYSSTAT B
WHERE A.NAME='SORTS (MEMORY)' AND B.NAME='SORTS (DISK)';

MEM_SORT_RATE
-------------
    99.969947

実際は、上記のSQL文よりも、チューニングしたい時間帯でvalueの差分を取ってメモリーソ
ート率を求めるほうが有効である。
(上記のSQLはORACLEが起動してからのメモリーソート率である。)

<実際にソートを伴なう処理かどうかを確認する方法>

実際にソートを行なう処理かを確認する方法としては、システム全体の統計情報を
格納しているV$SYSSTATではなく、セッション別に統計情報を格納してあるV$SESSTAT
で見ることにする。その統計情報の中のsorts (memory)、sorts (disk)、sorts (rows)
の累計値の差分をとって実際にソートをしたどうか確認しよう。

1.セッションIDを確認する。(ソートをする処理に対して発行する)

SELECT SID FROM V$SESSION WHERE AUDSID =(SELECT USERENV('SESSIONID') FROM DUAL);

      SID
---------
       10

もし、V$MYSTATへのSELECT権限があるなら以下のSQLでもわかる。

SELECT SID FROM V$MYSTAT WHERE ROWNUM < 2;

2.そのセッションからソートをすると思われる処理を実行する。

以下 ソート処理の例

A.ORDER BY

SELECT * FROM EMP ORDER BY ENAME;

B.GROUP BY

SELECT DEPTNO ,SUM(NVL(COMM,0)) FROM EMP GROUP BY DEPTNO;

C.DISTINCT

SELECT DISTINCT COMM FROM EMP;

D.INDEX作成

CREATE INDEX EMP_ENAME ON EMP(ENAME);

E.ANALYZE処理

ANALYZE TABLE T10MAN_ORG COMPUTE STATISTICS;

F.ソートマージジョイン

SELECT /*+ USE_MERGE(E)  USE_MERGE(D) FULL(D) */ 
 * FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO;

3.別のセッションからソートを行なっているセッションのSORT回数を検索する。

先ほど検索したSID=10で絞り込むことがポイントである。

SELECT N.NAME,SES.VALUE FROM V$SESSTAT SES,V$STATNAME N 
WHERE SID=10 AND SES.STATISTIC#=N.STATISTIC# AND N.NAME LIKE 'sorts%';

上記のSQL文でソート前とソート後の統計情報の値を確認する。

実例

<ソート前の統計情報確認>

セッションID=10以外のセッションより

SELECT N.NAME,SES.VALUE FROM V$SESSTAT SES,V$STATNAME N 
WHERE SID=10 AND SES.STATISTIC#=N.STATISTIC# AND N.NAME LIKE 'sorts%';

NAME                  VALUE
--------------------- ---------
sorts (memory)        37
sorts (disk)          0
sorts (rows)          251

<実際にソートの処理を実行する>

セッションID=10のセッションより

SELECT * FROM EMP ORDER BY ENAME

12行が選択されました。

(NT版のEMP表を作成するscott.sqlというスクリプトは、そのままではEMPNO
=7788と7876のデータはINSERT文で失敗するバージョンがある。みなさんの環境
では14件かもしれないが私の環境では14-2=12件しか入っていない。 UNIX版で
はutlsampl.sqlかな 豆豆知識)

<ソート後の統計情報確認>

セッションID=10以外のセッションより

SELECT N.NAME,SES.VALUE FROM V$SESSTAT SES,V$STATNAME N 
WHERE SID=10 AND SES.STATISTIC#=N.STATISTIC# AND N.NAME LIKE 'sorts%';

NAME                  VALUE
--------------------- ---------
sorts (memory)        38
sorts (disk)          0
sorts (rows)          263

よって

sorts (memory)=38-37=1回
sorts (disk)  =0-0=0回
sorts (rows)  =263-251=12行

これは、メモリ上でソートを1回行なったことを示す。

また、共有SQL上に載っていないSQL文に関しては、sorts (memory)の値が増え
てしまう。例えば、ソートを伴なわないSELECT * FROM EMP;などというSQL文
でも、共有SQL上に載っていない(キャッシュミスする)とsorts (memory)が
1増える。(リカーシブコールの中にはSORTを行なっているようなSQL文は見
あたらない。なんで増えるが不思議)

この方法は、ソート回数に限らず、他の統計情報に対しても同様なやり方で
利用できるので応用していただきたい。

ORACLE OPEN WORLDでインサイトテクノロジーのブースは見ていただきましたか?
抽選で弊社の書籍をお渡しする予定でしたが、非常に混雑してしまったので、
途中で取りやめました。申し訳ありません。その代わりといってはなんですが、
今回のアンケートに答えていただき、抽選に参加していただければと思います。

以上 忘年会の予約がなかなかとれない 茅ヶ崎にて