SSDに関する検証 その6

<緊急特集!!SSDに関する検証 その6>
ペンネーム: ミラニスタ

磁気ディスクの代わりに半導体メモリにデータを記録するストレージ装置
SSD (Solid State Disk) の検証を行っています。

検証製品(富士ゼロックス株式会社様製 GigaExpress)の紹介URL⇒
http://www.fujixerox.co.jp/product/gigaexpress/

SSD ミニ・クイズに多数ご応募いただき、誠にありがとうございました。
回答をお送りいただいた方の内、抽選で10名様に富士ゼロックス様ご提供の
ノベルティグッズをお送りいたします。
ご好評につき、第2弾も企画させていただきますのでお楽しみに!!

▼ 前回のおさらい

限られた容量(検証機は 16GB)の SSD を有効活用する目的で、UNDO と
REDO のみを SSD 化し、3万件の Insert を同時 20セッションで実行させた場
合の結果を確認しました。

Config1 : 19m 47s
Config3-2 : 5m 16s (Config1 比 3.8倍)

全く同じ処理でも約 4 倍の性能向上を確認することができました。
ここまでの結論:「SSD は、UNDO と REDO で使おう!!」

▼ 検証4:一時表領域も SSD 化してみる!!

一時表領域は、いろいろな目的で利用されますが、ここではソート用ワーク
エリアとしての利用を考えてみます。

○ 参考:ソートに関する検証バックナンバー
http://www.insight-tec.com/?s=%E3%82%BD%E3%83%BC%E3%83%88%E3%81%AB%E9%96%A2%E3%81%99%E3%82%8B%E6%A4%9C%E8%A8%BC&lang=ja
ソートに関する検証
新・ソートに関する検証

◆ 検証環境

150万件のランダムデータを格納した SORT_TEST というテーブルを作成しま
した。

○ 統計情報

SQL> select TABLE_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN from user_tables
     where TABLE_NAME='SORT_TEST';

TABLE_NAME     NUM_ROWS     BLOCKS AVG_ROW_LEN
------------ ---------- ---------- -----------
SORT_TEST       1500000     174480         825
                              ↓
          Block Size=8k → Size=約 1.3GB
ファイル                       Config3-2
------------------------------ -------------
SYSTEM 表領域                  磁気ディスク
ユーザ表領域                   磁気ディスク
UNDO 表領域                    SSD(2GB)
一時表領域                     磁気ディスク
オンライン REDO ログファイル   SSD(150MB * 3)

この状態で、全件ソートさせた時の結果を検証しました。

◆ 検証4-1 WORK_AREA_SIZE_POLICY=AUTO

1500000行が選択されました。

経過: 00:04:10.01

実行計画
----------------------------------------------------------
Plan hash value: 2903914677

-----------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |   111K|   106M|       | 27126   (1)|
|   1 |  SORT ORDER BY     |           |   111K|   106M|   249M| 27126   (1)|
|   2 |   TABLE ACCESS FULL| SORT_TEST |   111K|   106M|       |  3571   (1)|
-----------------------------------------------------------------------------

統計
----------------------------------------------------------
       1356  recursive calls
        548  db block gets
     173846  consistent gets
     347213  physical reads
        168  redo size
  137994105  bytes sent via SQL*Net to client
    1100374  bytes received via SQL*Net from client
     100001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)     ←*
    1500000  rows processed

* ディスクソートが発生し、4分10秒かかっています。

◆ 検証4-2 WORK_AREA_SIZE_POLICY=AUTO

同じ条件で、一時表領域を SSD 化してみました。

ファイル                       Config4-2
------------------------------ -------------
SYSTEM 表領域                  磁気ディスク
ユーザ表領域                   磁気ディスク
UNDO 表領域                    SSD(2GB)
一時表領域                     SSD(4GB)
オンライン REDO ログファイル   SSD(150MB * 3)

結果は。。。

1500000行が選択されました。

経過: 00:02:10.47

実行計画
----------------------------------------------------------
Plan hash value: 2903914677

-----------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |   111K|   106M|       | 27126   (1)|
|   1 |  SORT ORDER BY     |           |   111K|   106M|   249M| 27126   (1)|
|   2 |   TABLE ACCESS FULL| SORT_TEST |   111K|   106M|       |  3571   (1)|
-----------------------------------------------------------------------------

統計
----------------------------------------------------------
       1355  recursive calls
         22  db block gets
     173844  consistent gets
     313027  physical reads
        116  redo size
  137994105  bytes sent via SQL*Net to client
    1100374  bytes received via SQL*Net from client
     100001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
    1500000  rows processed

実行計画は同じ(特に Cost に注意)ですが、実行時間は半分になっていま
す。
さすが、SSD!と言いたいところですが、ソートの場合はもっと別のチュー
ニングポイントがあります。

◆ 検証4-3 WORK_AREA_SIZE_POLICY=MANUAL、SORT_AREA_SIZE=(メチャメチャ
巨大!!)

そうです。ソートエリアを大きくしてメモリソートさせるようにしてみま
しょう。

ところで、SORT_AREA_SIZEってどこまで大きくできるものなんでしょうか?
(デフォルト 64K)

思い切って 2GB = 2147483648 Byte にしてみましょう!

SQL> alter system set sort_area_size=2147483648 deferred;
alter system set sort_area_size=2147483648 deferred
                                *          ^^^^^^^^おまじない
エラー行: 1: エラーが発生しました。
ORA-02017: 整数値が必要です。

ん~っ、やっぱり無謀でしたか。

それでは、1 Byte 少ない値でリトライ!!

SQL> alter system set sort_area_size=2147483647 deferred;

システムが変更されました。

あっさりできてしまいました。

接続し直して変更を確認してみましょう。

SQL> conn / as sysdba
接続されました。

SQL> show parameter sort_area_size

NAME                                 TYPE
------------------------------------ ---------------------------------
sort_area_size                       integer

VALUE
------------------------------
2147483647

さあ、この状態でソートを実行させてみましょう。(一時表領域は磁気ディス
クに戻しています。)

ファイル                       Config4-3
------------------------------ -------------
SYSTEM 表領域                  磁気ディスク
ユーザ表領域                   磁気ディスク
UNDO 表領域                    SSD(2GB)
一時表領域                     磁気ディスク
オンライン REDO ログファイル   SSD(150MB * 3)

結果は。。。

1500000行が選択されました。

経過: 00:02:06.94

実行計画
----------------------------------------------------------
Plan hash value: 2903914677

---------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |   111K|   106M|  3584   (1)|
|   1 |  SORT ORDER BY     |           |   111K|   106M|  3584   (1)|
|   2 |   TABLE ACCESS FULL| SORT_TEST |   111K|   106M|  3571   (1)|
---------------------------------------------------------------------

統計
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     173844  consistent gets
     136040  physical reads
        116  redo size
  137994105  bytes sent via SQL*Net to client
    1100374  bytes received via SQL*Net from client
     100001  SQL*Net roundtrips to/from client
          1  sorts (memory)   ←*
          0  sorts (disk)
    1500000  rows processed

どうですか。メモリソートが実行され、若干早い結果となりました。

また、SSD 上に確保した一時表領域が小さいと

select * from sort_test
*
行1でエラーが発生しました。:
ORA-01114: ファイル%s(ブロック番号%s)への書込みI/Oエラーが発生しました。

というエラーが発生して、問合せが異常終了してしまいます。

ソートのチューニングに関しては、ディスクソートを SSD で高速化するより、
SORT_AREA_SIZE を一時的に大きくした方がよさそうです。

結論:SSD でディスクソートは早くなるが、SORT_AREA_SIZE を一時的に大き
くできるのであればメモリソートの方がよい。

今回はここまで。

次回は、メーカー(富士ゼロックス株式会社様)の方に、製品に関する質問
を投げてみたいと思います。
読者の皆さんの中でご質問のある方は、mailto:letter@insight-tec.co.jp
(担当:三原)までお願いいたします。
メールを頂いた方全員に、弊社特製携帯ストラップを差し上げます。
(2007/7/27締め切り。送付先も併せてお書きください。)

次々回は、「DB バッファ・キャッシュを大きくすればするほど、パフォー
マンスは向上するか!?その時 SSD は?」という検証を行う予定です。

日本代表、技ありシュート決めた高原!なぜ PK 戦で外す!?

オフィスから江ノ島が見える、茅ヶ崎にて