Exadataをフル活用しよう！Smart Scanでさらに性能を引き上げるチューニング方法

Exadata チューニング

アシストの笠原です。

Oracle Exadata Database Machineは、最新のハードウェアに加えOracle Databaseの性能向上に寄与するソフトウェア機能が豊富に搭載されています。そのまま使っても高性能ですが、独自機能を使いこなすことで更に性能を引き上げることができます。

今回、弊社が実施しているExadataのSQLチューニングの基本的なプロセスについて全3回に渡ってご紹介します。

Exadataをフル活用しよう！性能をさらに引き上げるチューニング方法(Smart Scan 編)　★本記事★
Exadataをフル活用しよう！性能をさらに引き上げるチューニング方法(パラレルクエリー編)
Exadataをフル活用しよう！性能をさらに引き上げるチューニング方法(ハッシュ結合編)

まず、第1回の今回は最も代表的な機能といえる Smart Scanの機能概要と実際に実行計画などを含めて効果を見ていきたいと思います。なお、Exadataの独自機能はExadata Cloud@CustomerやExadata Cloud Serviceなどクラウド環境でも利用できます。

性能向上に寄与するExadataの独自機能

さて、Exadataには下記の５つの特長的な機能があります。

Exadata 特長的な機能

Smart Scan: 大量データの検索や絞り込みをストレージサーバに任せる機能。
データベースではCPU負荷が低減し、表の結合や整列を実施。
　
Hybrid Columnar Compression: データを列ごとに格納して圧縮する機能。列単位でデータが格納され、似たような値のデータが圧縮されブロックの収納効率が向上する。全表検索に最適。
　
Storage Index: Storage Serverのメモリ内に表データのサマリ情報を格納し、where句の条件にマッチする部分のみにアクセスすることでディスクI/Oを削減する機能。
　
Smart Flash Cache: Storage Serverに搭載されているキャッシュ機能。
　
I/O Resource Management: データベース毎にCPUやストレージの使用帯域を制限する機能。

これらの機能で最も重要視されるのが、「Smart Scan」です。
この機能をいかに活用するのか、が性能向上のポイントです。

Smart Scanはデータの検索をストレージにオフロード

Smart Scanは大量データの検索や絞り込みをストレージサーバ側で実施する機能です。

ストレージサーバ側でデータを絞り込み、必要なデータを返すことによって、DBサーバ側のCPU使用率や、ネットワークのデータ転送量が低減します。これにより、大量データの検索パフォーマンスを向上させることができます。

Exadata Smart Scan

Smart Scanの活用度を高めるにはフルスキャン

IAサーバの場合、チューニングの定石は、

バッファキャッシュにキャッシュさせる
索引スキャンをさせる

です。

ExadataではストレージサーバにIOをオフロードすること、すなわち、Smart Scanをいかに活用できるか、がポイントです。
Smart Scanが機能する条件は、次の２つの処理のいずれかを発生させるだけです。

テーブルフルスキャン、索引フルスキャン
パラレルクエリ

この点が従来のパフォーマンス・チューニングの定石とは異なるため、Smart Scanの有効活用に苦労しているお客様が多い印象です。

では、実際にどうすれば良いのか？というと、以下のいずれかの方法で対応可能です。

索引を物理的に削除する
主キーやユニーク索引以外を削除します
不可視索引を利用する
索引を削除せず、不可視属性で無効化して影響度を確認します
NO_INDEXヒントやFULLヒントなどヒント句を利用する
SQL毎にヒント句を追加してフルスキャンへ誘導します

大量データの検索では、索引を使用せずフルスキャンした方が速い。
Exadata環境下でのSmart Scanを利用する指標は以下のとおりです。

選択率 1～10%以上 … Smart Scan推奨
選択率 1%未満 … 索引スキャン推奨

通常は選択率10%未満で索引スキャンを推奨していますが、Exadataではフルスキャンの性能が高いため、上記の指標を基本に 索引アクセスの見直し からSmart Scanの活用度を高めます。

続いて、実際に検証した結果をもとに効果をご紹介したいと思います。

Smart Scanの効果を検証してみた

検証環境

今回、検証にあたって用意したデータは以下のとおりです。
約7億件のデータを持つテーブルで検証を実施しています。

　
　TABLE_NAME           NUM_ROWS        TBL SIZE(GB)
　-------------------- --------------  ----------------
　ORDER_ITEMS               716651816        5.38645935

索引を使ったクエリーの処理時間

従来のIAサーバでは、性能を出すために索引を利用するケースを例に7億件のテーブルの中から索引を使って1割未満のデータを参照するSQLを実行してみます。

　
　SQL>  select  count(return_date) from order_items
　         where dispatch_date between to_date('2012/03/01','yyyy/mm/dd') and to_date('2012/03/15','yyyy/mm/dd');
　
　COUNT(RETURN_DATE)
　------------------
　           1773628
　
　Elapsed: 00:02:29.98
　
　Execution Plan
　----------------------------------------------------------
　Plan hash value: 2988084740
　
　---------------------------------------------------------------------------------------------------------------------------
　| Id  | Operation                                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
　---------------------------------------------------------------------------------------------------------------------------
　|   0 | SELECT STATEMENT                            |             |     1 |    10 |   714K  (1)| 00:00:28 |       |       |
　|   1 |  SORT AGGREGATE                             |             |     1 |    10 |            |          |       |       |
　|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDER_ITEMS |    87M|   833M|   714K  (1)| 00:00:28 | ROWID | ROWID |
　|   3 |    SORT CLUSTER BY ROWID                    |             |    87M|       |   285K  (1)| 00:00:12 |       |       |
　|*  4 |     INDEX RANGE SCAN                        | TEST_IDX    |    87M|       |   285K  (1)| 00:00:12 |       |       |
　---------------------------------------------------------------------------------------------------------------------------
　
　
　Predicate Information (identified by operation id):
　---------------------------------------------------
　
　   4 - access("DISPATCH_DATE">=TO_DATE(' 2012-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
　              "DISPATCH_DATE"<=TO_DATE(' 2012-03-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
　
　Statistics
　----------------------------------------------------------
　          1  recursive calls
　          0  db block gets
　    1816079  consistent gets
　    1250021  physical reads
　   95004544  redo size
　        563  bytes sent via SQL*Net to client
　        546  bytes received via SQL*Net from client
　          2  SQL*Net roundtrips to/from client
　          3  sorts (memory)
　          0  sorts (disk)
　          1  rows processed

実行時間は2分29秒（149秒）、インデックスレンジスキャンで索引を範囲検索し、物理読込回数は1,250,021回でした。

Smart Scanに変更した場合の処理時間

それでは、このSQLを索引を使わずにフルスキャンしてみましょう。
はたしてどうなるでしょうか！？

　
　SQL> set timing on
　SQL> set autotrace on
　SQL> select /*+ NO_INDEX(order_items) */ count(return_date) from order_items
　     where dispatch_date between to_date('2012/03/01','yyyy/mm/dd') and to_date('2012/03/15','yyyy/mm/dd');
　
　  COUNT(*)
　----------
　  1773628
　
　Elapsed: 00:00:07.07
　
　Execution Plan
　----------------------------------------------------------
　Plan hash value: 622085761
　
　-----------------------------------------------------------------------------------------------------------
　| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
　-----------------------------------------------------------------------------------------------------------
　|   0 | SELECT STATEMENT            |             |     1 |    10 |   196K  (3)| 00:00:08 |       |       |
　|   1 |  SORT AGGREGATE             |             |     1 |    10 |            |          |       |       |
　|   2 |   PARTITION HASH ALL        |             |    87M|   833M|   196K  (3)| 00:00:08 |     1 |    32 |
　|*  3 |    TABLE ACCESS STORAGE FULL| ORDER_ITEMS |    87M|   833M|   196K  (3)| 00:00:08 |     1 |    32 |
　-----------------------------------------------------------------------------------------------------------
　
　Predicate Information (identified by operation id):
　---------------------------------------------------
　
　   3 - storage("DISPATCH_DATE">=TO_DATE(' 2012-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
　              "DISPATCH_DATE"<=TO_DATE(' 2012-03-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
　       filter("DISPATCH_DATE">=TO_DATE(' 2012-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
　              "DISPATCH_DATE"<=TO_DATE(' 2012-03-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
　
　
　Statistics
　----------------------------------------------------------
　          0  recursive calls
　          0  db block gets
　     700709  consistent gets
　     700549  physical reads
　          0  redo size
　        563  bytes sent via SQL*Net to client
　        535  bytes received via SQL*Net from client
　          2  SQL*Net roundtrips to/from client
　          0  sorts (memory)
　          0  sorts (disk)
　          1  rows processed

表をフルスキャンする動作に変わり、 物理読込回数は700,549回まで減少、 実行時間は95%短縮して7秒で完了しました。 結果として 約21倍高速化 、Exadata環境ではSmart Scanに変更することでフルスキャンの方が圧倒的に速い結果となりました！

Exadata Smart Scan 検証結果

Smart Scanによる絶大な性能向上

ここでポイントとなっているのは、実行計画で出てくる「TABLE ACCESS STORAGE FULL」です。これはExadata 固有の処理で、 フルスキャンをストレージサーバに任せて、対象データのみデータベースサーバが読み込む という処理です。
データベースサーバ側ではデータの集計(今回はデータのカウント)や結合処理に専念できるようになります。

しかしながら、インデックススキャンしなくても良いか、というとそういう訳でもありません。表から1行のみ読んでくるようなオンライン系のSQLはExadata環境においてもインデックススキャンの方が速いです。

第1回目の記事はいかがでしたでしょうか。Exadataの概要とSmart Scanの効果について皆様の理解が深まれば幸いです。
次回は、処理をさらに高速化させる方法を紹介予定です。お楽しみに。

Exadataに関するお問い合わせはこちらから

執筆者情報

笠原克俊

2003年入社。10年以上Oracle Databaseの技術サポートに従事したのちフィールドエンジニア部隊に配属。Exadata X7、X8、X8M、Exadata Cloud at Customer（ExaCC）を使用した基幹システムのリプレースプロジェクトに携わる。お客様の要件にそった設計、最適化、運用支援、データ移行、DB・SQLチューニングまで幅広く対応。...show more

■本記事の内容について
　本記事に示した定義及び条件は変更される場合があります。あらかじめご了承ください。

■商標に関して
　・Oracle®、Java、MySQL及びNetSuiteは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
　・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
　　文中の社名、商品名等は各社の商標または登録商標である場合があります。