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

~ソートに関する検証 その9 最終回 ~  ペンネーム ちゃむ

今回でソートに関する検証を最後にする。
今回は、ソートに関する動的パフォーマンス表である下記の2つを紹介する。
おそらく、以下のバージョンから参照できる。

v$sort_segment → Oracle7.3から
v$sort_usage   → Oracle8から

1.v$sort_segmentについて

ソートセグメントとその使用状況を確認するには、V$SORT_SEGMENTを確認する。
但し、この情報は、TEMPORARY(専用一時表領域)の表領域に作成された一時セグ
メントのみである。

SQL> desc v$sort_segment

 名前                                                  NULL?    型
 ----------------------------------------------------- -------- -------------

 TABLESPACE_NAME                                                VARCHAR2(31)
 SEGMENT_FILE                                                   NUMBER
 SEGMENT_BLOCK                                                  NUMBER
 EXTENT_SIZE                                                    NUMBER
 CURRENT_USERS                                                  NUMBER
 TOTAL_EXTENTS                                                  NUMBER
 TOTAL_BLOCKS                                                   NUMBER
 USED_EXTENTS                                                   NUMBER
 USED_BLOCKS                                                    NUMBER
 FREE_EXTENTS                                                   NUMBER
 FREE_BLOCKS                                                    NUMBER
 ADDED_EXTENTS                                                  NUMBER
 EXTENT_HITS                                                    NUMBER
 FREED_EXTENTS                                                  NUMBER
 FREE_REQUESTS                                                  NUMBER
 MAX_SIZE                                                       NUMBER
 MAX_BLOCKS                                                     NUMBER
 MAX_USED_SIZE                                                  NUMBER
 MAX_USED_BLOCKS                                                NUMBER
 MAX_SORT_SIZE                                                  NUMBER
 MAX_SORT_BLOCKS                                                NUMBER
 RELATIVE_FNO                                                   NUMBER

ここで、TOTAL_EXTENTS、TOTAL_BLOCKS、USED_EXTENTS、USED_BLOCKS 、
FREE_EXTENTS、FREE_BLOCKSという列がある。
~_EXTENTSと~_BLOCKSはそれぞれ、エクステント数とブロック数である。
これらの列の関係を~_BLOCKSを用いて以下のURLで図示する(~_EXTENTSも同様)。

TOTAL_BLOCKSとMAX_BLOCKSとMAX_USED_BLOCKSは今までの検証では、数値的には同
じ値を示しているので、TOTAL_BLOCKSだけおさえておけばいいのではないだろうか?
MAX_SORT_BLOCKS列は今までのディスクソート処理のうち、一番大きいソート
ブロックサイズを示す。

このようにv$sort_segmentは一時表領域(PERMANENT)の情報は見れないので注意
してほしい。一時表領域(PERMANENT)が見れないのは、ソート終了後、毎回解放
してしまうからである。

2.v$sort_usageについて

セッションレベルのソートの状況を更に細かく取得したい場合、このv$sort_usage
で確認できる。これは、一瞬しか見れない情報なので、ソートが多発しているときに
見ると有効である。また、V$SORT_SEGMENTとは違い専用一時表領域(TEMPORARY)の
情報も、一時表領域(PERMANENT)の情報も見ることができる。

SQL> DESC V$SORT_USAGE

 名前                                                  NULL?    型
 ----------------------------------------------------- -------- ----------

 USER                                                           VARCHAR2(3
 SESSION_ADDR                                                   RAW(4)
 SESSION_NUM                                                    NUMBER
 SQLADDR                                                        RAW(4)
 SQLHASH                                                        NUMBER
 TABLESPACE                                                     VARCHAR2(3
 CONTENTS                                                       VARCHAR2(9
 SEGTYPE                                                        VARCHAR2(9
 SEGFILE#                                                       NUMBER
 SEGBLK#                                                        NUMBER
 EXTENTS                                                        NUMBER
 BLOCKS                                                         NUMBER
 SEGRFNO#                                                       NUMBER

ここで、重要な列はCONTENTS列とEXTENTS列とBLOCKS列であろうか。
CONTENTSは、専用一時表領域(TEMPORARY)か一時表領域(PERMANENT)を示す項目である。
EXTENTS列とBLOCKS列はソート処理時のエクステント数とブロック数である。

V$SORT_USAGEは、ソートを行なっている最中に取れる情報である。これをV$SESSIONと
V$SQLでジョインすることにより、どの一時表領域で、どのユーザが、どのプログラム
より、どのSQL文でディスクソートのために何ブロック使用しているかがわかる。

SELECT SORT.TABLESPACE,SORT.CONTENTS,SES.USERNAME,SES.PROGRAM,
       SQL.SQL_TEXT,SORT.BLOCKS FROM V$SESSION SES,
       V$SORT_USAGE SORT,V$SQL SQL
       WHERE SES.SADDR=SORT.SESSION_ADDR AND SES.SQL_ADDRESS=SQL.ADDRESS;

SQL> col TABLESPACE format a8
SQL> col CONTENTS  format a10
SQL> col USERNAME  format a8
SQL> col PROGRAM  format a10
SQL> col SQL_TEXT format a30
SQL> col BLOCKS format 999

TABLESPA CONTENTS   USERNAME PROGRAM    SQL_TEXT                       BLOCKS
-------- ---------- -------- ---------- ------------------------------ ------
TEMP1    TEMPORARY  CHAMU1   popsql.exe SELECT  EMPNO, ENAME, JOB, MGR    100
                                        , HIREDATE, SAL, COMM, DEPTNO
                                              FROM  T1MAN_1 ORDER BY S
                                        AL,COMM,DEPTNO

BLOCKSは、ソートしている最中にどんどん上がっていく。
これで、ソート負荷の高いSQLは見つけることができるだろう。

v$sort_segmentは主に累積値等を示し、v$sort_usageはセッションの一瞬の情報を
格納するという違いがあるので注意!!

以上 兄へ ホットプレートは茅ヶ崎においておくよ。 茅ヶ崎にて

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ Oracle入門生活 ▼━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
<Oracle入門> ペンネーム モンキーターン

初心者の心をつかむべく、開始されたこの入門生活….。
しかし、今ひとつ初心に戻りきれなかったモンキーターンは、
もう1度ピュアな心を取り戻すべく、現在放浪の旅に出ております。
入門生活再開まで、今しばらくお待ちください。