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

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

今回から、ソートに関しての検証を行なう。ソートがシステムのボトルネックに
なることは、度々ある。例えば、大量更新を行なっていない時間帯にあるデ
ィスクに書き込みが集中していて、調べていくと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)
の累計値の差分をとって実際にソートをしたどうか確認しよう。

１．セッション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;

３．別のセッションからソートを行なっているセッションの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)が
１増える。（リカーシブコールの中にはSORTを行なっているようなSQL文は見
あたらない。なんで増えるが不思議）

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

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

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

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