catch-img

Oracle SQLチューニング Season2（第32回）第6章 大量のデータ処理性能改善方法 （5/5）

Oracle技術情報

Oracle SQLチューニング

　今回のSQLチューニング 2nd Season（第32回）は「第6章 大量のデータ処理性能改善方法」と題しまして計5回シリーズの第5回目として解説していきます。

　今回のテーマは「大量のデータに対するSummary戦略」です。

　では、早速始めましょう。

　

6.5 大量のデータに対するSummary戦略

　大量のデータを扱う業務は通常、SUM、AVG、RANK、COUNTなどの集合関数や統計関数の使用が続きます。これにより、ユーザーが望むデータは常に精製された簡単な結果だけが抽出されることになります。　内部的に処理対象となるデータ件数は数十、数百万件のデータであるのですが、これを実際にすべて抽出して直接Reportingする作業を行うユーザーはいないからです。

　ユーザーの立場からすると、　「私が確認したいデータはわずか数行（数件）に過ぎないのに、応答時間はなぜいつもこんなに遅いのか？」という疑問が湧いてくると思います。

　一方、パフォーマンスを向上させるべき立場としては、「目に見えるわずかな行数（件数）だけを考えて、その裏側にある処理されるべき数多くのデータをなぜ考えられないのか？」という疑問が湧いてくると思います。

　この2つの見解についての衝突は常に存在します。
　もちろん、ユーザーの立場に合わせて、できるだけ応答時間の改善に焦点を当てなければならないのはあるものの、様々な方策で対処したとしても大きな改善効果を引き出すことができない場合も数多く存在する事は事実です。
　したがって、このような環境にも適合することができる性能改善案の策定をすることが必要なのです。

　もし、精製された目的のデータ抽出をするためにソーステーブルに膨大な量のデータを読み込んでGroupingするのではなく、ソーステーブルに対してGroupingされているテーブルをあらかじめ生成し、そのデータを読み込んで目的の結果をユーザーに提供することができれば、この問題を円滑に解消できるのではないでしょうか？

　この考え方はとても合理的であると言えます。実際にこの案を可能にしてくれるのが、Mviewと集計テーブルの構成です。 

　Mviewと集計テーブルは、作成方法や運営方法などの違いはありますが、その核心は、ソーステーブルのデータを対象に業務特性に合わせてGroupingして保存し、文字通りソーステーブルに対する集計データを持つObjectという共通点があります。

　Mviewと集計テーブルのそれぞれが持つ特性を把握し、いつ、どのように、どのような業務に適用できるか？を賢明に判断できるようになることが、今回のテーマの核心部分です。


6.5.1 Mview

　Mviewは、Materialized Viewの略です。
　具体化されたビューを意味し、その核心は一般的なViewとは違って、それ自体がデータを保存しているViewであることを意味します。
　先ほども説明したことと重複しますが、MviewはAggregate Operationを実行しなければならない負荷の大きいSQLに使われるテーブルにあらかじめGroupingされたデータを保存しておくことで、性能改善を目的として主に使われます。

[ テストテーブル生成スクリプト ]

CREATE TABLE sales_info AS
SELECT a.*
FROM   sh.sales a ,
       (
        SELECT LEVEL
        FROM   dual
        CONNECT BY LEVEL <= 100
       ) b ;

[ パフォーマンス問題 SQL ]

SELECT ROWNUM no ,
       sales_sum ,
       cust_id
FROM   (
        SELECT SUM( s.amount_sold ) sales_sum ,
               s.cust_id cust_id
        FROM   sales_info s ,
               customers c
        WHERE  s.cust_id = c.cust_id
        AND    s.time_id >= TO_DATE( :from_time, 'yyyymmdd' )
        AND    s.time_id <= TO_DATE( :to_time  , 'yyyymmdd' )
        GROUP  BY s.cust_id
        ORDER  BY 1. DESC
       )
WHERE  ROWNUM <= 10;

----------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name         | Starts | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |              |      1 |     10 |00:00:09.40 |     443K|    443K|
|*  1 |  COUNT STOPKEY            |              |      1 |     10 |00:00:09.40 |     443K|    443K|
|   2 |   VIEW                    |              |      1 |     10 |00:00:09.40 |     443K|    443K|
|*  3 |    SORT ORDER BY STOPKEY  |              |      1 |     10 |00:00:09.40 |     443K|    443K|
|   4 |     HASH GROUP BY         |              |      1 |   1118 |00:00:09.40 |     443K|    443K|
|*  5 |      HASH JOIN            |              |      1 |   2280K|00:00:08.65 |     443K|    443K|
|   6 |       INDEX FAST FULL SCAN| CUSTOMERS_PK |      1 |  55500 |00:00:00.01 |     122 |      0 |
|*  7 |       TABLE ACCESS FULL   | SALES_INFO   |      1 |   2280K|00:00:07.10 |     443K|    443K|
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   5 - access("S"."CUST_ID"="C"."CUST_ID")
   7 - filter(("S"."TIME_ID">=TO_DATE(' 2001-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 
AND "S"."TIME_ID"<=TO_DATE(' 2001-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
例えば、上記のように各地域別、特定の製品の販売価格の合計を求めるSQLを実行する場合、多数のデータを対象としなければならないため、そのテーブルをTable Full Scanで処理するしかなく、そのテーブルのSizeが大きい場合には、それに伴うOverheadが発生することが避けられません。
この場合、あらかじめ集計されたデータが保存されたMviewがあれば、性能的な面でのStatにどれだけ大きな減少効果を見ることができるでしょうか？

[ MView 生成スクリプト ]

CREATE MATERIALIZED VIEW TOP_SALES_INFO_MV
       BUILD IMMEDIATE -- BUILD IMMEDIATE, BUILD DEFERRED 選択.
       REFRESH
       COMPLETE        -- FORCE, COMPLETE, FAST, NEVER 選択.
       ON DEMAND       -- ON DEMAND, ON COMMIT 選択.
       ENABLE QUERY REWRITE
AS
SELECT sales_sum sales_sum ,
       cust_id cust_id ,
       from_time from_time ,
       to_time to_time
FROM   (
        SELECT SUM( s.amount_sold ) sales_sum ,
               TO_CHAR( MIN( time_id ) , 'yyyymmdd' ) from_time ,
               TO_CHAR( MAX( time_id ) , 'yyyymmdd' ) to_time ,
               s.cust_id cust_id
        FROM   sales_info s ,
               customers c
        WHERE  s.cust_id = c.cust_id
        GROUP  BY s.cust_id ,
               TO_CHAR( time_id , 'yyyymm' )
        ORDER  BY 1 DESC
       );


Mview生成スクリプトの説明：

  • BUILD IMMEDIATE : Mviewの生成時に該当データを収集する機能。
  • BUILD DEFFERRED : Mviewの生成はしますが、そのデータは後で収集するようにする機能
  • REFRESH : ソーステーブルとMviewのデータを同期する機能で、On-CommitとOn-Demand方式があります。
  • On Commit : ソーステーブルにCommitが発生するたびにRefreshを実行します。
  • On Demand : ユーザーがRefreshをDBMS_XPLANパッケージを実行します。
  • ENABLE QUERY REWRITE : Query Rewriteを考慮できるようにする機能。
  • AS : AS構文の後に必要なSQL構文技術

　ただし、Refresh方法はソーステーブルの性格を考慮して適用する必要があります。
Transactionが頻繁に発生するテーブルのMviewをOn-Commit方式で指定すれば、Mview Refreshも頻繁に発生するので、これに対するコストがさらに増加します。
　したがって、Mviewを通じてどの程度のリアルタイムデータを保証するかを戦略的に決定して適用する必要があるのです。

[ TOP_SALES_INFO_MV に Indexを作成]
	   
CREATE INDEX top_sales_info_mv_ix_01 ON top_sales_info_mv( from_time , to_time ) ;	

[ パフォーマンス問題 SQL – Mview 適用 ]

:from_time = '20011201’
:to_time   = '20011231’

SELECT ROWNUM RANK ,
       sales_sum ,
       cust_id
FROM   (
        SELECT sales_sum sales_sum ,
               cust_id cust_id ,
               from_time from_time ,
               to_time to_time
        FROM   top_sales_info_mv
        WHERE  from_time >= :from_time
        AND    to_time   <= :to_time
        ORDER  BY 1 DESC
       )
WHERE rownum <= 10

----------------------------------------------------------------------------------------------
| Id  | Operation               | Name              | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                   |      1 |     10 |00:00:00.01 |       7 |
|*  1 |  COUNT STOPKEY          |                   |      1 |     10 |00:00:00.01 |       7 |
|   2 |   VIEW                  |                   |      1 |     10 |00:00:00.01 |       7 |
|*  3 |    SORT ORDER BY STOPKEY|                   |      1 |     10 |00:00:00.01 |       7 |
|*  4 |     MAT_VIEW ACCESS FULL| TOP_SALES_INFO_MV |      1 |   1118 |00:00:00.01 |       7 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   4 - filter(("FROM_TIME">=:FROM_TIME AND "TO_TIME"<=:TO_TIME))

　Mviewがあらかじめ生成されており、これをソーステーブルの代わりに使用できるように誘導してみると、実に驚くべき効果を見ることができることが確認されました。

　このように活用することができれば、業務の特性を把握した上で可能なテーブルにMviewを生成しておき、生成されたMviewを積極的に応用して活用することで、Database全般にわたる負荷の解消に大きな助けとなることは、間違いありません。


6.5.2 集計テーブル

　先に説明したMviewと集計テーブルの生成目的は同じです。
　ただ、テーブルを構成するデータを収集する方法が違うだけです。
　一般的にMviewより集計テーブルがより汎用的に使われているので、皆さんにはより身近な概念だと思います。

[ テストテーブル生成スクリプト ]

CREATE TABLE sales_info AS
SELECT a.*
FROM   sh.sales a ,
      ( SELECT LEVEL  FROM   dual  CONNECT BY LEVEL <= 100 ) b ;

[ パフォーマンス問題  SQL ]

SELECT ROWNUM no ,
       sales_sum ,
       cust_id
FROM   (
        SELECT SUM( s.amount_sold ) sales_sum ,
               s.cust_id cust_id
        FROM   sales_info s ,
               customers c
        WHERE  s.cust_id = c.cust_id
        AND    s.time_id >= TO_DATE( :from_time, 'yyyymmdd' )
        AND    s.time_id <= TO_DATE( :to_time  , 'yyyymmdd' )
        GROUP  BY s.cust_id
        ORDER  BY 1. DESC
       )
WHERE  ROWNUM <= 10;

----------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name         | Starts | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |              |      1 |     10 |00:00:09.40 |     443K|    443K|
|*  1 |  COUNT STOPKEY            |              |      1 |     10 |00:00:09.40 |     443K|    443K|
|   2 |   VIEW                    |              |      1 |     10 |00:00:09.40 |     443K|    443K|
|*  3 |    SORT ORDER BY STOPKEY  |              |      1 |     10 |00:00:09.40 |     443K|    443K|
|   4 |     HASH GROUP BY         |              |      1 |   1118 |00:00:09.40 |     443K|    443K|
|*  5 |      HASH JOIN            |              |      1 |   2280K|00:00:08.65 |     443K|    443K|
|   6 |       INDEX FAST FULL SCAN| CUSTOMERS_PK |      1 |  55500 |00:00:00.01 |     122 |      0 |
|*  7 |       TABLE ACCESS FULL   | SALES_INFO   |      1 |   2280K|00:00:07.10 |     443K|    443K|
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   5 - access("S"."CUST_ID"="C"."CUST_ID")
   7 - filter(("S"."TIME_ID">=TO_DATE(' 2001-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 
AND "S"."TIME_ID"<=TO_DATE(' 2001-12-31
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))


　上記SQLは、Mviewを説明する時のSQL例です。
　これをMviewではなく集約テーブルで構成したものと仮定して、その集約テーブルにAccessできるように誘導してみると、結果は下記のようにものになります。

[ 集計テーブルの作成 ]

CREATE TABLE TOP_SALES
(
   cust_rank         varchar2(100) ,
   sales_sum         varchar2(100) ,
   cust_id           varchar2(100) ,
   from_time         varchar2(8),
   to_time           varchar2(8)
);

CREATE INDEX sales_ix_01 ON top_sales( from_time , to_time );

[ 集計データの作成 ]

:from_time = '20011201'
:to_time   =  20011231'

INSERT
INTO   top_sales
SELECT ROWNUM no ,
       sales_sum ,
       cust_id ,
       :from_time ,
       :to_time
FROM   (
        SELECT SUM( s.amount_sold ) sales_sum ,
               s.cust_id cust_id
        FROM   sales_info s ,
               customers c
        WHERE  s.cust_id = c.cust_id
        AND    s.time_id >= TO_DATE( :from_time , 'yyyymmdd' )
        AND    s.time_id <= TO_DATE( :to_time   , 'yyyymmdd' )
        GROUP  BY s.cust_id
        ORDER  BY 1. DESC
       )
WHERE  ROWNUM <= 10; -- 1ヶ月に相当するTop 10集計

[ パフォーマンス問題 SQLを生成した集計テーブルを活用して実行 ]

SELECT cust_rank ,
       sales_sum ,
       cust_id
FROM   top_sales
WHERE  from_time >= :from_time
AND    to_time   <= :to_time

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |      1 |     10 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TOP_SALES   |      1 |     10 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN          | SALES_IX_01 |      1 |     10 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("FROM_TIME">=:FROM_TIME AND "FROM_TIME" IS NOT NULL)
       filter("TO_TIME"<=:TO_TIME)

　Mviewの場合と同様、性能上における大きな改善効果が期待できます。
　集計テーブルは、ユーザーが必要に応じて特定の時間にソーステーブルに対する集計テーブルを手動で構成するテーブルを指します。

　もちろん、すべての場合において直接手動で構成するのではなく、ほとんどの場合では、DBMS_JOB、DBMS_SCHEDULERなどに登録した上で、一定の時間に集約テーブルを構成するように運営します。

　集計テーブルで重要なポイントは、データ同期の程度となります。
　つまり、日単位あるいは時間単位など、ソーステーブルの変更が発生した部分に対する同期の程度の違いによって、日次バッチ、時間バッチなどに登録して同期を行うことです。

　もし、ほぼリアルタイムでソーステーブルに対する変更を集計テーブルに反映しなければならない場合には、集計テーブルの頻繁な構成が必要となってくるため、むしろこれによる負荷がより負担になる状況が発生する可能性があります。

　その逆の場合を考えてみるならば、戦略的な対処が可能です。
　比較的ユーザーの接続が少ない時間帯に日単位の配置で集計テーブルを構成しておけば、業務時間帯にその集計テーブルを活用するのに大きな負担はなく、集計テーブルを構成する負荷も微々たるレベルにとどまるでしょう。



　今回をもちまして、2シーズンにわたってお届けしてきました「SQLチューニングブログ」は終了となります。
　このブログが、皆様のDBA Lifeにとって少しでも有益な情報源となっていたら幸いです。
　また違う形で皆様とお会いできればと考えております。これまでのご愛読、本当にどうもありがとうございました。




前の記事

prev-article-image

MaxGaugeのプロセス

次の記事

next-article-image

Oracle SQLチューニング Season2（第31回）第6章 大量のデータ処理性能改善方法 （4/5）

CONTACT

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします

お役立ち資料は
こちらから

資料ダウンロード

不明点がある方は、
こちらからお問い合わせください

お問い合わせ

お電話でのお問い合わせはこちら

03-3518-0771

平日 10時～18時

人気記事ランキング

タグ一覧

logo_img
ページトップへ戻る

©EXEM JAPAN Corp. All Rights Reserved.