Comments
Transcript
4. パフォーマンスの更なる向上 -照会オプティマイザーの機能強化によるパフォーマンス向上
<第1.00版 2012年 9月> 4. パフォーマンスの更なる向上 -照会オプティマイザーの機能強化によるパフォーマンス向上 本資料掲載事項は、ある特定の環境・使用状況においての正確性がIBMによって確認されていますが、すべての環境において同様の結果が得られる保証は ありません。これらの技術を自身の環境に適用する際には、自己の責任において十分な検証と確認を実施いただくことをお奨めいたします。 © Copyright IBM Japan Systems Engineering Co., Ltd. 2012 1 © 2012 IBM Corporation 内容 • 照会オプティマイザーの機能強化 • 索引スキャンの高速化 • スタースキーマに基づく照会パフォーマンスの改善 • マルチコアH/Wの性能を引き出す並列処理の強化 (Intra parallel) • 統計収集/統計ビューの機能拡張 • 索引サンプリングの機能強化 • 統計ビューの機能拡張 2 © 2012 IBM Corporation 照会オプティマイザーの機能強化 3 © 2012 IBM Corporation 照会オプティマイザーの機能強化 索引スキャンの高速化 ジャンプ・スキャン スマート索引プリ フェッチング スマート・データ・プリ フェッチング スタースキーマに基づく照会パフォーマンスの改善 スタースキーマの検出アルゴリズ ムの改良 4 ジグザグ・ジョイン新手法の採用 による性能向上 © 2012 IBM Corporation 照会オプティマイザー機能強化(1) • 索引スキャンを高速化する新しい3つのアクセス方式 ジャンプ・スキャン •索引のカラム定義のギャップに対応した索引アクセスの性能向上 スマート索引プリフェッチング •リーフページのディスク上の並びが、索引キーの順序とマッチして いない索引におけるリーフページ先読みの性能向上 スマート・データ・プリフェッチング •クラスター化率の低い索引経由のデータフェッチの性能向上 5 © 2012 IBM Corporation ジャンプ・スキャン • ギャップがある索引へのアクセス方式の効率化 • これまで索引の全スキャンとなっていた、「索引の下位キー のみが指定された条件句」に対して効果的 V10.1では、条件C/Dで効率的な索引スキャンが可能となった 条件句の内容 V9.7以前 V10.1 索引定義列 A. 全列指定 SELECT xxx WHERE A = 1 and B = 2 and C = 3 (A, B, C ) B. 上位キー指定 SELECT xxx WHERE A = 1 and B = 2 and C = 3 (A, B, C ) C. 第1キー指定なし SELECT xxx WHERE A = 1 and B = 2 and C = 3 索引 全スキャン D. 第2キー指定なし SELECT xxx WHERE A = 1 and B = 2 and C = 3 索引 全スキャン (A, B, C ) 6 (A, B, C ) © 2012 IBM Corporation ジャンプ・スキャンによる索引の統合例 • これまでは、同じ表へのアクセスを行うSQLであっても、短い応答時間を必要とする場 合、個別に索引が必要とされるケースが多かった • DB2 V10.1では、索引スキャン方式の改善により統合できるケースが増加 V9.7までは、短い応答時間確保のためには、SQLごとに最適化した索引が必要 SELECT shop_id, order_quantity FROM order WHERE shop_id = 1 and order_quantity > 100 SELECT shop_id, order_quantity FROM order WHERE order_quantity > 1000 shop_id, order_quantity order_quantity V10.1では、一つの索引を2つのSQLで利用しつつ、効率的な索引スキャンが維持可能 SELECT shop_id, order_quantity FROM order WHERE shop_id = 1 and order_quantity > 100 SELECT shop_id, order_quantity FROM order WHERE order_quantity > 1000 7 shop_id, order_quantity © 2012 IBM Corporation 索引の“ギャップ”が存在しても効率的なスキャンが可能 • 下記のSQLでは索引の第一キーである”shop_id”が絞り込み条件に指定されてい ない • DB2 V10.1では、第二キーである”order_quantity”の値が絞り込み条件に適合す るページのみをスキャンできる • DB2 V9.7以前では、索引全体のスキャンとなる shop_id = 1 and order_quantity > 1000 OR shop_id = 2 and order_quantity > 1000 OR shop_id = 3 and order_quantity > 1000 実行されるSQL SELECT shop_id, order_quantity FROM order WHERE order_quantity > 1000 1-3 CREATE INDEX quantity_ix ON TABLE order (shop_id, order_quantity) shop_idの値 order_quantityの 範囲 8 1 1400 索引アクセス方式のイメージ 2-3 1-2 1 1 401700 7014000 shop_idの値が条件指定され ているかのように、必要な部分 のみleaf pageをスキャン 2 1200 2 2 201300 301900 3 1500 3 5016000 … © 2012 IBM Corporation 照会オプティマイザー機能強化(1) • 索引スキャンを高速化する新しい3つのアクセス方式 ジャンプ・スキャン •索引のカラム定義のギャップに対応した索引アクセスの性能向上 スマート索引プリフェッチング •リーフページのディスク上の並びが、索引キーの順序とマッチして いない索引におけるリーフページ先読みの性能向上 スマート・データ・プリフェッチング •クラスター化率の低い索引経由のデータフェッチの性能向上 9 © 2012 IBM Corporation 索引ページの先読み ~順次アクセスの検知による先読み CREATE INDEX order_ix ON TABLE order (order_id) INCLUDE (order_quantity) SELECT order_id, order_quantity FROM order WHERE order_id BETWEEN 1000 AND 6000 順次アクセスの検知により 索引リーフページを 先読みする。 9 Index on disk 1 6 2 7 3 8 4 9 5 8 7 索引ページ番号 1 2 3 4 5 6 order_id の範囲 10001999 20002999 30003999 40004999 50005999 60006999 10 … © 2012 IBM Corporation V9.7以前の課題: 大量のIUD発生によりばらばらになった 索引ページは先読みができなくなる CREATE INDEX order_ix ON TABLE order (order_id) INCLUDE (order_quantity) 索引リーフページのスキャ ンが順次アクセスにならな いため、先読みを行うこと ができない。 ⇒ 索引の再編成が必要 SELECT order_id, order_quantity FROM order WHERE order_id BETWEEN 1000 AND 6000 9 Index on disk 1 6 2 7 3 8 4 9 5 8 7 索引ページ番号 1 2 6 4 5 3 order_id の範囲 10001999 20002999 30003999 40004999 50005999 60006999 11 … © 2012 IBM Corporation 追加LOADやIUDの繰り返しによる性能の劣化 (V9.7以前) • 追加ロードや行挿入、更新、削除の繰り返しにより、索引ページや表 データページの先読み性能が悪化 • 索引リーフページのディスク上の格納順序が索引キーの順序とマッ チしなくなるため、必要なページの先読みができなかったり、不要な ページの先読みが発生 • 表データの並びが索引キーの順序とマッチしなくなるため、必要な データページの先読みができなかったり、不要なページの先読みが 発生 • 統計情報が古い場合、上記のような変化をDB2は知ることができなかっ た • パフォーマンスの回復のためには、索引や表の再編成が必要 12 © 2012 IBM Corporation V10.1による解決: スマート索引プリフェッチング ~順次読み取りとリード・アヘッド・プリフェッチの併用~ Index on disk 順次性検知のプリフェッチ 1 6 2 7 3 8 4 9 5 リード・アヘッド・プリフェッチの方が有 効であるとの判断 リード・アヘッド・プリフェッチ ノン・リーフページ8 は次の必要とされて いる2リーフページが 5と3であることを示し ている⇒これらの ページを先読みする 9 8 7 Index Page # 1 2 6 4 5 3 order_id range 10001999 20002999 30003999 40004999 50005999 60006999 13 … © 2012 IBM Corporation スマート・データ・プリフェッチング ~データページ先読みの効率化~ • この例では索引は編成化されているがデータはクラスター化されていな い状況 Data on disk • 索引スキャンはリーフページを11, 12, 13, 14, 15, 16に順次に読む • データのフェッチはリーフページの情報によって、 1 7 1,2,3,4,9,7,5,10,6,12,8,11の順番に読む 2 8 3 9 4 5 6 10 11 12 19 18 17 11 12 1 2 3 4 リード・アヘッド・プリフェッチ・ページ として 5, 10, 6, 12, 8, 11 は プリフェッチされる 13 5 14 15 16 … 6 7 8 9 10 11 12 順次性検知のプリフェッチ リード・アヘッドプリフェッチの方が有 効であるとの判断 Sequential pre-fetching 14 リード・アヘッド・プリ フェッチの開始点 リード・アヘッド・プリフェッチ © 2012 IBM Corporation 従来の先読み機能との比較 順次アクセス検出による先読み リード・アヘッド・プリフェッチ 先読み発生のト リガー 物理的に連続した索引ページ、データページ のアクセス発生によって起動 順次アクセス検出による先読み処理 が非効率であると判断したときに切り 替える 先読みの対象 順次アクセスした索引ページやデータページ の先に物理的に配置されている索引ページ やデータページ 実行中の索引スキャンやフェッチに必 要な索引ページ、データページを選択 して先読み 特徴 アクセス対象の索引ページやデータページ がディスク上に連続して配置されている場合 には最も効率の良い先読みが可能。 必要なページのみを選択して先読み するため、アクセス対象の索引ページ、 データページが非連続に配置されて いる場合でも先読みが可能。 非連続的に配置されている場合には、先読 みができない、または効率が下がる。 DB2は、順次アクセス検出による先読みを優先して実施し、それが非効率的な場合に自 動的にリード・アヘッド・プリフェッチに切り替える。 15 © 2012 IBM Corporation (参考)スマート索引プリフェッチングの効果検証 • シナリオ: • 100万件のデータの入ったリーフページの全件スキャンにて比較 • 索引ページはランダムに並んでいるため従来の順次アクセスの検出による先読みがで きない • 実行DDL (1~100万のデータをランダムに投入) create table t1(a bigint , b bigint,c bigint,d bigint); create index x1 on t1(a,b,c); insert into t1 with temp(a) as (values(1) union all select a+1 from temp where a<1000000 ) select a,a,a,a from temp order by rand(); • 実行SQL (索引リーフページの全スキャン) select count(*) from t1; 16 © 2012 IBM Corporation (参考)db2explnの出力 Statement: select count(*) from t1 Section Code Page = 1208 Estimated Cost = 5246.881348 Estimated Cardinality = 1.000000 Access Table Name = DB2V10.T1 ID = 3,6 | Index Scan: Name = DB2V10.X1 ID = 1 | | Regular Index (Not Clustered) | | Index Columns: | | | 1: A (Ascending) | | | 2: B (Ascending) | | | 3: C (Ascending) | #Columns = 0 | Skip Inserted Rows | Avoid Locking Committed Data | Currently Committed for Cursor Stability | #Key Columns = 0 | | Start Key: Beginning of Index | | Stop Key: End of Index | Index-Only Access | Index Prefetch: Sequential(5236), Readahead | Lock Intents | | Table: Intent Share | | Row : Next Key Share 17 | Sargable Index Predicate(s) | | Predicate Aggregation | | | Column Function(s) Aggregation Completion | Column Function(s) Return Data to Application | #Columns = 1 End of section Optimizer Plan: Operator (ID) RETURN ( 1) | GRPBY ( 2) | IXSCAN ( 3) | Index: DB2V10 X1 © 2012 IBM Corporation (参考)スマート索引プリフェッチングの効果 • V9.7 (先読みなし) 応答時間は1.29秒 $ echo "select count(*) from t1;" | ¥ db2batch -d sample -o r 0 p 3 発生した物理読み取 りは全て同期Read ~結果抜粋~ * Elapsed Time is: 1.295826 seconds Buffer pool index logical reads Buffer pool index physical reads Asynchronous pool index page reads Total buffer pool read time (milliseconds) Total elapsed asynchronous read time Asynchronous index read requests = = = = = = 20964 10479 0 1038 0 0 • V10.1 (リード・アヘッド・プリフェッチ) $ echo "select count(*) from t1;" | ¥ db2batch -d sample -o r 0 p 3 応答時間は0.51秒 発生した物理読み取り の93%は非同期Read ランダムに並んだ索引ページを、リード・アヘッド・プリ フェッチによって先読みした 18 ~結果抜粋~ * Elapsed Time is: 0.517317 seconds Buffer pool index logical reads Buffer pool index physical reads Asynchronous pool index page reads Total buffer pool read time (milliseconds) Total elapsed asynchronous read time Asynchronous index read requests = = = = = = 16624 10540 9891 842 711 5097 © 2012 IBM Corporation (参考)スマート・データ・プリフェッチングの効果検証 • シナリオ: • 1000万件のデータの入った表から1万行を索引スキャンにてフェッチ • データ行はランダムに並んでいるため、データページの先読みができない • 実行DDL (1~1000万のデータをランダムにロード) create table t1(a int , b int, c char(100)); create index x1 on t1(a); declare c1 cursor for with temp(a) as (values(1) union all select a+1 from temp where a<10000000 ) select a/10000,a,'' from temp order by rand(); load from c1 of cursor replace into t1; • 実行SQL (索引スキャンによる10000行のフェッチ) select * from t1 where a=10; 19 © 2012 IBM Corporation (参考)db2explnの出力 Statement: select * from t1 where a=10 | Sargable Predicate(s) | | Return Data to Application | | | #Columns = 3 Return Data Completion End of section Section Code Page = 1208 Estimated Cost = 42345.226562 Estimated Cardinality = 9990.009766 Access Table Name = DB2V10.T1 ID = 3,5 | Index Scan: Name = DB2V10.X1 ID = 1 | | Regular Index (Not Clustered) | | Index Columns: | | | 1: A (Ascending) | #Columns = 2 | Skip Inserted Rows | Avoid Locking Committed Data | Currently Committed for Cursor Stability | Evaluate Predicates Before Locking for Key | #Key Columns = 1 | | Start Key: Inclusive Value | | | 1: 10 | | Stop Key: Inclusive Value | | | 1: 10 | Data Prefetch: Sequential(6208), Readahead | Index Prefetch: Sequential(10), Readahead | Lock Intents | | Table: Intent Share | | 20Row : Next Key Share Optimizer Plan: Operator (ID) RETURN ( 1) | FETCH ( 2) / ¥ IXSCAN Table: ( 3) DB2V10 | T1 Index: DB2V10 X1 © 2012 IBM Corporation (参考)スマート・データ・プリフェッチングの効果 • V9.7 (先読みなし) 応答時間は2.39秒 $ echo “select * from t1 where a=10;" | ¥ db2batch -d sample -o r 0 p 3 ~結果抜粋~ * Elapsed Time is: 2.397990 seconds Buffer pool data logical reads Buffer pool data physical reads Asynchronous pool data page reads Buffer pool index logical reads Buffer pool index physical reads Asynchronous pool index page reads Total buffer pool read time (milliseconds) 発生した物理読み取 りは全て同期Read = = = = = = = 19657 9645 0 64 20 0 2210 応答時間は0.67秒 発生した物理読み取り の94%は非同期Read ランダムに並んだデータページを、リード・ア ヘッド・プリフェッチによって先読みした 21 • V10.1 (リード・アヘッド・プリフェッチ) $ echo “select * from t1 where a=10;" | ¥ db2batch -d sample -o r 0 p 3 ~結果抜粋~ * Elapsed Time is: 0.670697 seconds Buffer pool data logical reads Buffer pool data physical reads Asynchronous pool data page reads Buffer pool index logical reads Buffer pool index physical reads Asynchronous pool index page reads Total buffer pool read time (milliseconds) Total elapsed asynchronous read time Asynchronous data read requests Asynchronous index read requests = = = = = = = = = = 16280 9688 9121 88 24 5 1909 1818 5699 1 © 2012 IBM Corporation 照会オプティマイザー機能強化(1) • 索引スキャンを高速化する新しい3つのアクセス方式 ジャンプ・スキャン 索引数の削減 •索引のカラム定義のギャップに対応した索引アクセスの性能向上 スマート索引プリフェッチング •リーフページのディスク上の並びが、索引キーの順序とマッチして いない索引におけるリーフページ先読みの性能向上 索引の再編成の必 要性削減 スマート・データ・プリフェッチング •クラスター化率の低い索引経由のデータフェッチの性能向上 テーブルの再編成 の必要性削減 22 © 2012 IBM Corporation 照会オプティマイザー機能強化(2) • スタースキーマに基づく照会パフォーマンスの改善 スタースキーマの検出アルゴリズムの改良 •ユニーク属性に基づいたスタースキーマ構造を自動検知 •プライマリー・キー、ユニーク索引やユニーク制約を使用する 新しい結合方式:ジグザグ・ジョインによる性能向上 •ファクト表と複数のディメンジョン表の結合 •複合索引にギャップがある場合にもジャンプ・スキャンとの併用で 性能低下を回避 23 © 2012 IBM Corporation 典型的なスタースキーマの照会:デカルト・ハブ結合方式 • 例 select count(quantity_sold) from daily_sales s, product prod, customer c, promotion promo where prod.category = 42 and promotype = 2 and c.age_level = 4 and c.income_level = 5 and prod.prodkey = s.prodkey and c.custkey = s.custkey and promo.promokey = s.promokey • 直積との突き合わせのプラン nljn __________|__________ ディメンションの 直積 | 複合索引(FACT表) (custkey, prodkey, promokey) | join iscan ______|______ | FACT表にはこのうち、わずか1400 | | daily_sales の組み合わせが存在する join promotion ______|____ | | customer product 320万の組み合わせがある (そしてFACT表を走査する) 24 © 2012 IBM Corporation 新しい結合方式: ジグザグ・ジョイン • 動作の流れ 1. ディメンションの直積を作成 2. ファクト表の複合索引情報をディメン ションキーの直積へフィードバック 3. 4. ディメンションキーの直積 直積のうち、ファクト表にデータがない 部分は走査をスキップ ディメンション表とファクト表の間をジグザグ に行き来する(2~3の繰り返し) データがない部分の ディメンション表のキー 走査をスキップ ※ファクト表にディメン ションキーの複合列索引 があることが前提 25 d1 d2 1 1 2 3 3 4 4 5 … … d1 d2 1 1 1 3 1 4 1 5 2 1 2 2 データがない部分の 走査をスキップ ファクト表の 複合列索引 1 2 3 4 3 6 4 2 5 3 1 3 3 3 4 … … 5 f1 f2 1 1 2 2 3 3 4 4 5 5 … … 走査 照合 JOIN: d1=f1 and d2=f2 © 2012 IBM Corporation ジクザグ・ジョインを発生する複合列索引 • 前提条件: 尐なくとも2つのディメン ション表とジョインするための複合索 引をファクト表に用意する必要がある • D3 (D) D スタースキーマの照会例 • D1はプライマリキーAをもつ • D2は複合プライマリキー (B,C)をもつ • D3はプライマリキーDをもつ • これらのプライマリキーはファクト表との 等結合に使用される A D1 (A) B,C D2 (B,C) Fact D1とD2、D2とD3、D3とD1の3 つのうち、2つのジョインを満た すための複合索引がすべて必要 ファクト表の索 引定義例 (A,D), (B,C,D) (A,B,C,D), (A,C,B,D) (A,B), (C,D) (B,A,C) 条件を満たしている か はい はい いいえ いいえ 理由 索引2つで、3つの ディメンションをカー バーしている 索引1つで、完全に 3つのディメンション をカーバーしている 索引はD2のディメン ジョン表を完全にカ バーできていない 複合索引で、BとCが 連続していない 26 © 2012 IBM Corporation 複合索引列にギャップがある場合のジクザグ・ジョイン • ジャンプ・スキャンの利用 • 異なるディメンション表のセットに対するスタースキーマの照会に対して個別に索 引を定義する必要はない。V10.1では複合索引列のギャップへ対応可能 • 例えば、ファクト表の複合列索引(A, C, B)はC列とのジョインはないが、以下の スタースキーマ照会ではジグザグ・ジョインを適用可能 D1 (A) • B Fact D2 (B) ファクト表の索引にギャップがありジグザク・ジョインが選択された場合には、 db2exfmt出力のIXSCAN演算子の詳細に以下の引数が表示される Arguments: -------------JUMPSCAN: TRUE Gap Info: --------------------Index Column 0: Index Column 1: Index Column 2: 27 A (JumpScan Plan) Status --------------------No Gap Positioning Gap No Gap © 2012 IBM Corporation 照会オプティマイザー機能強化(2) • スタースキーマに基づく照会パフォーマンスの改善 スタースキーマの検出アルゴリズムの改良 •ユニーク属性に基づいたスタースキーマ構造を自動検知 スキーマ構造を正確 に認識し、適切なアク セス・プランを選択! •プライマリー・キー、ユニーク索引やユニーク制約を使用する 新しい結合方式:ジグザグ・ジョインによる性能向上 •ファクト表と複数のディメンジョン表の結合 •複合索引にギャップがある場合にもジャンプ・スキャンとの併用で 性能低下を回避 スキャン効率を良くし てSQLの性能向上! 28 © 2012 IBM Corporation マルチコアH/Wの性能を引き出す 並列処理の強化(Intra parallel) 29 © 2012 IBM Corporation Intra parallel機能のポジション • Intra parallel機能が必要となる背景 • 1エージェント(db2agent)が使用可能なCPUは1つまで • 1SQLだけで多くのCPUリソースを使用するケースではCPUの処 理性能が実行時間の限界を決める • 分析業務で使用されるJOIN数が多いSQL、大量読み込みを行う SQLなど DBサーバー(16CPU) DBサーバーに CPUが16個あっ ても有効活用でき ない DPFにする程の 大規模DBでもな いし・・・ データベース DB2エージェント OSのスレッド 30 分析用SQL (SELECT... FROM...) 分析担当者 © 2012 IBM Corporation Intra parallel 機能概要 • 機能の特徴 • SMP環境にて1SQLを複数プロセッサで並列処理 • DPFでなくとも、シングルDBで並列処理を実現 • お客様にとっての価値 • DBサーバーのCPUを有効活用して処理時間を短縮 DBサーバー(16CPU) DB2サブエージェント 複数プロセッサで 並行処理できるか ら処理が速い! Happy ! データベースDB2サブエージェント DB2サブエージェント DB2サブエージェント 31 分析用SQL (SELECT... FROM...) 分析担当者 © 2012 IBM Corporation Intra parallelの機能強化 • 3つの機能強化でより使いやすく!より効率的に! より細やかな単位で設定が可能に •アプリケーション、ワークロード単位での制御を実現 ワークロードのアンバランス補正 •サブエージェント間でのワークロードを均一化 パーティション索引の並列スキャン •パーティション索引に対する並列スキャンを新たに 追加 32 © 2012 IBM Corporation より細やかな単位で設定が可能に • インスタンス単位に加え、アプリケーション/ワークロード単 位での設定を新たに提供 • アプリケーション単位の設定 • ADMIN_SET_INTRA_parallel プロシージャー CALL SYSPROC.ADMIN_SET_INTRA_parallel (‘YES’|‘NO’) • ワークロード単位の設定 CURRENT DEGREE特殊レ ジスター、BINDオプション DEGREE、DFT_DEGREE (DB CFG)で並列度を設 定可 • WORKLOADのMAXIMUM DEGREE属性 ALTER WORKLOAD WL_REPORT MAXIMUM DEGREE 4 1SQLが大量READするレ ポート処理を並列度4で実行 • 定義の優先順位 • WORKLOAD、プロシージャー、INTRA_PARALLEL (DBM CFG)の順で優先される 33 © 2012 IBM Corporation ワークロードのアンバランス補正(1) • サブエージェント間でのワークロードを均一化し、Intra parallelを有 効に利用する • V9.7以前 • サブエージェント間のワークロードのアンバランス • データのフィルタリング対象のバラつきが原因 • 次の読み込みが待たされるエージェントが発生 • ジョインや高コストの処理では効率の悪さが拡大 イントラ・パラレル処理を有効に利用できない場合があった • V10.1 • 新しいアクセス・プランのオペレータ:REBAL(Rebalance)を導 入 • ワークロードのリバランスのため、エージェント間で行を転送する • アイドルのサブエージェントがREBAL処理をする 34 © 2012 IBM Corporation ワークロードのアンバランス補正(2) ワークロードに偏りのある 並列処理 リバランスされたデータストリーム LTQ 1CPUのみ Busy 以前のIXScan を複数のサブエー ジェント間でデータ 再分配 NLJN Fetch IXScan LTQ NLJN REBAL Fetch IXScan Index: X1 IXScan Table: T1 IXScan Table: T1 Index: X1 Index: X1 35 Index: X1 © 2012 IBM Corporation パーティション索引の並列スキャン • パーティション索引に対する並列スキャンを新たに追加 • サブエージェントはパーティション索引のキーの範囲でアサインされる • 処理が終了すると新しいレコードの範囲をサブエージェントにアサインする 索引 パーティション サブエージェントは レコードの範囲で アサインされる データ パーティション 処理完了したサブエージェントは、新しいレンジがアサインされる Jan Apr Jul Oct Feb May Aug Nov Mar Jun Sep Dec 2010 2010 2010 2010 パーティション1 パーティション2 パーティション3 36 パーティション4 © 2012 IBM Corporation Intra parallelの機能強化 • 3つの機能強化でより使いやすく!より効率的に! より細やかな単位で設定が可能に •アプリケーション、ワークロード単位での制御を実現 業務単位での設定が可能に! ワークロードのアンバランス補正 •サブエージェント間でのワークロードを均一化 ワークロードを均一化してCPUを有効活用! パーティション索引の並列スキャン •パーティション索引に対する並列スキャンを新たに 追加 並列スキャンでCPUを有効活用! 37 © 2012 IBM Corporation 統計収集/統計ビューの機能拡張 38 © 2012 IBM Corporation RUNSTATSサンプリング機能のポジション • RUNSTATSサンプリング機能が必要となる背景 • ビッグデータ時代では表サイズは益々大きくなる - サイズ増大に伴ってメンテナンス時間が比例的に増大 - 統計情報収集、再編成など 2015年 販売履歴表 ・ユーザー数が増えて取引数が増加 ・分析用の新規アプリケーション開発に伴 い、列定義を追加 メンテナンスに時間 が必要と言っても 限度がある 2012年 販売履歴表 表サイズの増大 実行中はI/O負荷が かかって他処理へ 影響が出てしまう 39 管理者 © 2012 IBM Corporation RUNSTATSサンプリング機能概要 • 機能の特徴 • 全レコードの代わりにサンプリングしたレコードを対象に統計情報を 収集して読み込み量を減らすことで処理時間を短縮できる • お客様にとっての価値 • メンテナンス枠が限られる場合においても、表サイズの影響を最小 化してメンテナンスを時間内に完了できる サンプリング 無 全レコードを READ サンプリングを行 うことで運用時間 を短縮! Happy ! サンプリング 有 一部のみを READ メンテナンスが理 由で発生するリ ソース消費を低減 管理者 40 © 2012 IBM Corporation 索引サンプリングをより柔軟に • 表で使用可能なサンプリング方式を索引でも提供 • ページ・レベル・サンプリング: SYSTEM • 全ページのうち、対象とするパーセントを指定し、統計情報収集 • より短時間のサンプリング • 行レベル・サンプリング: BERNOULLI 時間ではなく、正確さ を優先することも可能 • 全行のうち、対象とするパーセントを指定し、統計情報収集 • より正確なサンプリング コマンド構文 Table Sampling Options: |--TABLESAMPLE--+-BERNOULLI-+--(--numeric-literal--)------------> '-SYSTEM----' >--+-----------------------------------+------------------------| '-REPEATABLE--(--integer-literal--)-' Index Sampling Options: |--INDEXSAMPLE--+-BERNOULLI-+--(--numeric-literal--)------------| '-SYSTEM----' RUNSTATS ON TABLE DB2INST1.STOCK1 WITH DISTRIBUTION AND DETAILED INDEXES ALL TABLESAMPLE BERNOULLI(10) INDEXSAMPLE BERNOULLI(10) 41 コマンド例 © 2012 IBM Corporation 統計ビュー機能拡張 • 統計ビューをより使いやすくする4つの新機能 数式の統計取得可能 •式や関数が含まれる列統計の収集 V9.1:統計ビュー登場 V9.7: サンプリング可能 Star Joinで必要となる統計ビューの低減 •参照制約を使用し、結合列ごとの統計ビューの作成を回避 列グループ統計が収集可能 •アクセス・プラン作成時に統計ビューの列グループ統計を使用 自動Runstatsで統計ビューの統計収集可能 •保守対象のオブジェクトとして統計ビューを追加 42 © 2012 IBM Corporation 数式の統計取得可能 • より正確な統計収集 create view sv as (select ucase(c1) as c1, c2 from t1 where t1.c3 < 10); alter view sv enable query optimization; runstats on table schema.sv with distribution; select * from t1,t2 where t2.c2 = ucase(t1.c1) and t1.c2 > 5 and t1.c3 < 10 • 正確なFilter Factor、カーディナリティ見積もりのためには、SVビューの C1列の統計情報が必要 ~以前のリリース~ Ucase(c1)の統計情報は デフォルト値採用 43 Ucase(c1)の統計情報は 統計ビューの統計採用 © 2012 IBM Corporation 統計ビューを尐なくすることが可能 • V9.7以前では、Star Joinの際に複数の統計ビューが必要だっ た select * from f, d1, d2, d3 where f.fk1 = d1.pk and f.fk2 = d2.pk and f.fk3 = d3.pk and d1.c1=’ON’ and d2.c2>=’2009-01-01’ • 統計ビュー:(f,d1,d2,d3),(f,d1),(f,d2),(f,d3)が必要 • 統計ビューの設計が煩雑 • 複数の統計ビューのメンテナンスコスト増 • 複数の統計ビューによるコンパイルコスト増 • 参照制約があれば複数の統計ビューの用意が不要 • 統計ビュー:(f,d1,d2,d3) が必要 • (f,d1),(f,d2),(f,d3)は参照制約により推測される 44 © 2012 IBM Corporation 列グループ統計が統計ビューでも有効 • 列グループ統計 • 目的: 2つ以上の列が条件指定されたSQLで、その列に相関関係がある時、フィル ターファクターを正しく見積もるための統計情報 • (例)T1表のC1列、C2列と、 T2表のC1列、C2列の値に相関関係がある select * from T1, T2 where T1.c1=T2.c1 and T1.c2=T2.c2 • (例)T1表のC1列とC2列の値に相関関係がある select * from T1 where c1=5 and c2=‘b’ • 収集方法: RUNSTATSコマンドのon cloumnsオプションで指定 create view sv2 as (select t1.c1, t1.c2 from t1,t2 where t1.c3=t2.c3); alter view sv2 enable query optimization; runstats on table db2.sv2 on columns((c1,c2)); V9.7でも収集できたが、 オプティマイザは利用できなかった 45 © 2012 IBM Corporation 自動Runstatsで統計ビューの統計収集可能 • 統計ビューも自動Runstatsの対象とすることができる • AUTO_STATS_VIEWSをONとする • AUTO_MAINT、AUTO_TBL_MAINT、AUTO_RUNSTATSがON前提 • 「runstats on view <view_name> with distribution」が実行される Automatic maintenance (AUTO_MAINT) = ON Automatic database backup (AUTO_DB_BACKUP) = OFF Automatic table maintenance (AUTO_TBL_MAINT) = ON Automatic runstats (AUTO_RUNSTATS) = ON Real-time statistics Statistical views (AUTO_STMT_STATS) = ON (AUTO_STATS_VIEWS) = OFF Automatic statistics profiling Statistics profile updates Automatic reorganization 46 (AUTO_STATS_PROF) = OFF (AUTO_PROF_UPD) = OFF (AUTO_REORG) = OFF © 2012 IBM Corporation 統計ビュー機能拡張 • 統計ビューをより使いやすくする4つの新機能 数式の統計取得可能 詳細な列統計収集でより最適 なアクセス・プランに! •式や関数が含まれる列統計の収集 不要なオブジェクトをな くし、より使いやすく! Star Joinで必要となる統計ビューの低減 •参照制約を使用し、結合列ごとの統計ビューの作成を回避 列グループ統計が収集可能 列グループ統計の使用でより 最適なアクセス・プランに! •アクセス・プラン作成時に統計ビューの列グループ統計を使用 自動Runstatsで統計ビューの統計収集可能 •保守対象のオブジェクトとして統計ビューを追加 47 自動保守で運用を楽 に! © 2012 IBM Corporation