Comments
Description
Transcript
第４章 アクセス経路の選択 1 アクセス経路 1-7 区画内並列処理
ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 第４章 アクセス経路の選択 ＜第１．００版＞２００１年２月 お断り：当資料は、DB2 UDB V7.1（AIX,NT,OS2) をベースに作成されています。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 1 アクセス経路 1-1 SQLコンパイラーの概要 1-2 最適化クラス 1-3 アクセス経路の種類 1-7 区画内並列処理 区画内並列スキャン 並列ソート 1-8 区画間並列のジョイン 表スキャン マッチング索引スキャン 複数索引アクセス 索引のみアクセス (参考)サマリー表スキャン 併置結合 外部表の指示結合 内部表の指示結合 内部表および外部表の指示結合 外部表の同報通信結合 内部表の同報通信結合 1-4 先読み 順次先読み 順次検出 リスト先読み 1-9 連合データベースのコンパイラー 連合データベースのコンパイラー概要 Pushdown分析 グローバル最適化 パフォーマンス関連の情報収集 (参考)パフォーマンスチューンング例 1-5 JOIN ネステッド・ループ・ジョイン マージ・ジョイン ハッシュ・ジョイン 1-6 述語の種類 述語の種類 ソートのタイプ 処理の概要 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 1-2 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 2 システム・カタログの統計情報 2-3 詳細な索引統計情報 2-1 統計情報の収集 詳細な索引統計情報の使用 PAGE_FETCH_PAIRS統計収集の例 統計情報の収集 RUNSTATSユーティリティー 更新されるカタログ表 2-4 ユーザー更新が可能なカタログ統計 2-2 非一様分布統計情報 非一様分布統計情報の使用 頻出値統計情報の取得例 頻出値統計情報を使用した行数の見積もり例 行数の見積もり計算（頻度の統計情報がある場合） 行数の見積もり計算（頻度の統計情報がない場合） 行数の見積もり計算結果 変位値統計情報の取得例 変位値統計情報を使用した行数の見積もり例 行数の見積もり計算（変位値の統計情報がある場合） 行数の見積もり計算（変位値の統計情報がない場合） 行数の見積もり計算結果 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 3 Explainツール 3 Explain機能 3-1 概念および目的 使用目的 Explainの概念 3-2 Explainツールの選択 3-3 Visual Explainの起動 3-4 アクセス・プラン・グラフ アクセス・プラン・グラフの見方 SQLテキスト 各種詳細情報 演算子(Operator) 3-5 索引の作成によるチューニング 3-6 カタログ情報更新によるチューニング 3-7 Explainの考慮点 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 3-4 ) 統計情報の更新 "TABLE1" の表統計を更新する例 ユーザーが更新可能な統計情報 実動データベースのモデル化 （参考） db2look 出力例 ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 アクセス経路 ＜第１．００版＞２００１年２月 お断り：当資料は、DB2 UDB V7.1（AIX,NT,OS2) をベースに作成されています。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 1 アクセス経路 1-1 SQLコンパイラーの概要 1-2 最適化クラス 1-3 アクセス経路の種類 表スキャン マッチング索引スキャン 複数索引アクセス 索引のみアクセス (参考)サマリー表スキャン 1-7 区画内並列処理 区画内並列スキャン 並列ソート 1-8 区画間並列のジョイン 併置結合 外部表の指示結合 内部表の指示結合 内部表および外部表の指示結合 外部表の同報通信結合 内部表の同報通信結合 1-4 先読み 順次先読み 順次検出 リスト先読み 1-9 連合データベースのコンパイラー 連合データベースのコンパイラー概要 Pushdown分析 グローバル最適化 パフォーマンス関連の情報収集 (参考)パフォーマンスチューンング例 1-5 JOIN ネステッド・ループ・ジョイン マージ・ジョイン ハッシュ・ジョイン 1-6 述語の種類 述語の種類 ソートのタイプ 処理の概要 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 5-6 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-1 SQLコンパイラーの概要 OSS: Operating System Services DB2コンポーネント BSU Locks Trans Mgmt DPS Comm DB2RA RDS Interpreter DMS LOB Mgr Catalog Services Index Mgr Applications APM LF Mgr Raw Storage Mgr Mem Mgmt BPS Trace File Storage Mgr Wait/ Post Logging EDU mgmt Process Model, instance + DB initialization, interrupts Latching Optimizer Sort (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-1 SQLコンパイラーの概要 代表的なDB2 コンポーネントです。以下に各コンポーネントとその役割をリストします BSU (ベース・システム・ユーティリティー) DB2インスタンスのメイン・エンジン DB2のプロセスモデルを管理 CCI (コミュニケーション・レイヤー) コミュニケーション管理 RDS (リレーショナル・データ・サービス) SQL文の解釈/構文の検査 データ取得のための最適なアクセスプランの決定 アクセスプラン作成 (APM Access Plan Manager) DMS (データ・マネージメント・サービス) アクセスプランを利用してデータを取得 以下のコンポーネントをコールすることがあります インデックス・スキャンのためにインデックス・マネージャー ソートのためにソート・リスト・サービス(SLS) データ・リンク・アクセスのために、ファイル・マネージャー BPS(バッファープール・サービス) バッファープールに対するデータ操作を行います バッファーページにデータがない場合はディスクからページが取得されます DPS(データ・プロテクション・サービス) データの整合性保証のためのメカニズムを提供します LockingとLoggingをふくみます OSS(オペレーティング・システム・サービス) 全てのオペレーティング・システム・コールを行います メモリー管理、ファイルI/O、セマフォアを使用してのイベントの管理、プロセス/スレッドの作成など、様々なものが含まれま す (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 7-8 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-1 SQLコンパイラーの概要 SQL照会 SQLコンパイラー 照会の構文解析 意味の検査 照会の書き直し 照会 グラフ・ モデル アクセス・プランの 最適化 アクセス・ プラン Explain表 Visual Explain 実行可能コードの 生成 プランの実行 実行可能プラン db2expln db2exfmt (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-1 SQLコンパイラーの概要 SQLコンパイラーはSQL文を受け取り実行可能なアクセス・プランを生成します。 この時、SQLコンパイラーは図のようにいくつかのステップで行われます。 この図で示すように照会グラフ・モデル(Query Graph Model)がSQLコンパイラーの主要要素です。 この照会グラフ・モデルとはメ モリー上に展開されるSQL文の内部表現でコンパイル処理を通して使用されます。 各ステップについて説明します。 照会の構文解析(Parse Query ) SQL照会を分析して構文の妥当性検査(Syntax Check)を行ないます。 解析が終了すると照会の内部表現である照会グ ラフ・モデルが作成されます。 １つの文(SQL文)を文法(構文)的に解析することから、このステップのプログラムをパー サーという事もあります。 意味の検査(Check Semantics) 構文の妥当性検査が終了した後に意味上の検査を行ないます。データ・タイプの妥当性検査などが含まれます。 このス テップでも機能上の意味が照会グラフ･モデルへ書き込まれます。 書き込まれるものには照会ブロック、副照会、相関、派 生表、式、データ･タイプ、データ･タイプ変換、コード・ページ変換などが照会の意味に関するもの全てが含まれます。 照会書き直し(ReWrtie Query) 照会グラフ･モデルの情報を利用して最適化しやすいように変更します。 アクセス・プランの最適化 オプティマイザーは候補となる実行プランを多数作成し実行コストの見積りが最も小さいプランを選択します(コストベース の最適化)。 統計情報を含む広範囲にわたる要因に関する情報を獲得/使用します。オプティマイザーは出力としてアク セス・プランを生成します。 これはExplain表にキャプチャーされる情報の基礎データになります。 実行可能コードの生成 アクセス・プランと照会グラフ･モデルを使用して実行可能なコードの生成を行ないます。 この生成のステップでも1つの照 会で1回だけで済む式が繰り返し実行されないようにする最適化が行われます。 静的SQLのアクセス・プランに関する情 報はシステム・カタログに保存され、実行時に使用されます。 db2explnツールが使用するのはこの情報です。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 9-10 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 解説： 1-1 SQLコンパイラーの概要 オプティマイザーが使用する情報 データベース・マネージャー構成パラメータ CPUSPEED MAX_QUERYDEGREE データベース構成パラメータ BUFFPAGE AVG_APPLS SORTHEAP LOCKLIST MAXLOCKS NUM_FREQVALUES NUM_QUANTILES INTRA_PARALLEL DFT_DEGREE DFT_QUERYOPT DFT_PREFETCH_SZ BIND/PREPコマンドオプション OPTIMAIZATION LEVEL ISOLATION BLOCKING (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-1 SQLコンパイラーの概要 SYSCAT.TABLESPACES TBSPACETYPE EXTENTSIZE PREFETCHSIZE OVERHEAD TRANSFERRATE PAGESIZE SYSCAT.TABLES COLCOUNT CARD NPAGES FPAGES OVERFLOW SYSCAT.INDEXES UNIQUERULE COLCOUNT NLEAF NLEVELS CLUSTERRATIO PAGE_FETCH_PAIRS DENSITY SEQUENTIAL_PAGE (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 11-12 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 解説： 1-1 SQLコンパイラーの概要 SYSCAT.COLUMNS COLCARD HIGH2KEY LOW2KEY SYSCAT.FUNCTIONS IOS_PER_INVOC INSTS_PERINVOC IOS_PER_ARGBYTE INSTS_PERARGBYTE SYSCAT.COLDIST TYPE SEQNO COLVALUE VALCOUNT SYSCAT.BUFFERPOOLS NPAGES PAGESIZE (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 第４章( 13-14 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-2 最適化クラス 最適化クラスの調整 0 最小限の最適化 1 DB2/6000バージョン1相当の最適化 2 貪欲型結合列挙(Greedy join enumeration)を使用する以外は、ほぼクラス5と同様の働き OLAP環境のように非定型で非常に複雑な照会を行う場合にお勧め 3 中程度の量の最適化 DB2(MVS/ESA版、OS/390版)の照会最適化に最も近い クラス3以降は動的プログラミング結合列挙(Dynamic programming join enumeration)を使用 5 大幅な最適化を使ってアクセス・プランを生成 使用可能な全ての統計情報を使用 オプティマイザーが複合動的SQL照会(complex dynamic SQL queries)の最適化コストが膨大になる可能性があると 判断した場合には最適化を縮小します。 7 複合動的SQL照会での最適化の縮小をしないことを除けばクラス5と同様 9 全ての最適化技法を使用 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-2 最適化クラス 最適なアクセス・プランを探すために使用する時間の長さが決まります 一般的には省略時の5で十分な最適化が得られます。 使用可能な値は0、1、2、3、5、7、9 0、9は特別な環境で使用 簡単なSQLには1、2 複雑なSQLには3、5 Net.Commerce、OLAPには2を推奨 静的SQLでは、最良の実行可能パスを作るため高目に設定します。 動的SQLには2つの考え方ができます。 一般には実行時間の短いものには低めの設定、長いものには高目の設定が向いていま す。 SQLのコンパイルの時間と実行の時間および必要な資源の兼ね合いで決める必要があります。 低く設定する目的 最適化の時間を短縮するため 高く設定する目的 オプティマイザーの機能をより多く利用するため 実行時間を短くするため ステートメントのPrepare時間の増加をまねくため注意が必要 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 15-16 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-2 最適化クラスの設定 静的SQL PRECOMPILE PROGRAM(PREP)コマンドのQUERYOPTパラメータで指定 BINDコマンドのQUERYOPTパラメータで指定 PREP/BINDコマンドで指定されていない場合、パッケージ作成時のDFT_QUERYOPTが省略 時値として使用 優先度の低い方から DFT_QUERYOPT < PREPコマンドのQUERYOPT < BINDコマンドの QUERYOPTの順 静的SQLはCURRENT QUERY OPTIMAIZATION特殊レジスターを使用しない 動的SQL SET CURRENT QUERY OPTIMAIZATION文で設定 DFT_QUERYOPTにより省略時値が設定される(省略時値 5) CLI 動的SQL同様 db2cli.iniのDB2OPTIMIZATIONキーワードを設定可能 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 第４章( 17-18 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-3 アクセス経路の種類 アクセス経路 より早く効率のよいデータ処理を行う目的で、データの取り出し方法を決めたもので索引の使 用とデータページの取り出し方法により分類されます。 アクセス経路の種類 基本的なアクセス経路には以下のような種類がある 表スキャン マッチング索引スキャン 2-1 2-2 2-3 2-4 マッチング・ユニーク索引アクセス マッチング・クラスター済み索引スキャン マッチング・非クラスター済みスキャン INリスト索引スキャン ノンマッチング・クラスター済み索引スキャン 複数索引アクセス 索引のみのアクセス (参考)サマリー表スキャン (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-3 アクセス経路の種類 ブランク・ページ ブランク・ページです (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 19-20 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-3 アクセス経路の種類 1.表スキャン SQL文(例) SELECT * FROM T1 WHERE COL3 > 0 索引 データ・ページ (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-3 アクセス経路の種類 1.表スキャン 関係スキャン(Relation Scan)と呼ばれることもあります 表全体を順次に読み込み条件に合致するかを検査します 通常、順次先読みが行われます 索引がない場合に表スキャンになります 索引を使用するより効果的と判断された場合に選択されます (例1)表自体が非常に小さい (例2)索引クラスター化の程度が非常に低い (例3)条件に該当するレコードの割合が非常に高い (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 21-22 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-3 アクセス経路の種類 2.マッチング索引スキャン 1. マッチング・ユニーク索引アクセス SQL文(例) SELECT * FROM T1 WHERE COL1 = 100 索引(COL1) 100 ユニーク索引 データ・ページ X (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-3 アクセス経路の種類 2-1. マッチング・ユニーク索引アクセス 述語にマッチしたユニーク索引を使用します 索引のクラスター率に左右されません 該当のレコードのRIDを調べ、データページから1行取り出します 行を取り出した後、他の条件について検査します (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 23-24 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-3 アクセス経路の種類 2. マッチング索引スキャン 2. マッチング・クラスター済み索引スキャン SQL文(例) SELECT * FROM T1 WHERE COL1 BETWEEN 50 AND 60 AND COL2 < 50 索引(COL1) 50 60 クラスター率が高い データ・ページ XX X X X X (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-3 アクセス経路の種類 2-2. マッチング・クラスター済み索引スキャン 述語にマッチしたクラスター済み索引を使用したスキャンを行います クラスター率の高い索引を使用し、データページが物理的な並び順にとりだされるため、I/O回数が軽減されます 該当のレコードのRIDを調べ、データページから取り出した後、他の条件について検査します(上記の例ではcol2 < 50) (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 25-26 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-3 アクセス経路の種類 2. マッチング索引スキャン 3. マッチング・非クラスター済み索引スキャン SQL文(例) SELECT * FROM T1 WHERE COL1 BETWEEN 50 AND 60 AND COL2 < 50 索引(COL1) 50 60 クラスター率が低い データ・ページ X X XX X X (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-3 アクセス経路の種類 2-3. マッチング・非クラスター済み索引スキャン 述語にマッチした非クラスター済み索引を使用したスキャンを行います。 クラスター率の低い索引を使用し、データページに対するランダムなアクセスが行われるため、1行につき1回のI/Oが発生し ます。 該当のレコードのRIDを調べ、そのたびにデータページから取り出した後、他の条件について検査します(上記の例ではcol2 < 50)。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 27-28 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-3 アクセス経路の種類 2. マッチング索引スキャン 4. INリスト索引スキャン SQL文(例) SELECT * FROM T1 WHERE COL1 IN (100 , 200) 索引(COL1) 100 200 データ・ページ X X 索引ORingが行われる場合もある (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-3 アクセス経路の種類 2-4. INリスト索引スキャン 述語にIN句がある場合に、IN句のリストの値ごとに索引を使用します。 INリスト索引スキャンはマッチング索引スキャンの特別なケースです。 索引ORingが行われる場合もあります(索引ORingについては複数索引アクセスを参照)。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 29-30 ) X ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-3 アクセス経路の種類 3. ノンマッチング・クラスター済み索引スキャン SQL文(例) SELECT * FROM T1 WHERE COL5 = 100 索引(COL1) クラスター率が高い データ・ページ X X X (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-3 アクセス経路の種類 3. ノンマッチング・クラスター済み索引スキャン 述語にマッチしないクラスター済み索引を使用したスキャンです。 述語には索引列が使用されていないが、クラスター率の高い索引を使用して表をアクセスすることにより物理I/Oが軽減され ます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 31-32 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-3 アクセス経路の種類 4. 複数索引アクセス SQL文(例) SELECT * FROM T1 WHERE COL1 = 100 OR COL2 > 200 索引(COL1) 索引(COL2) 100 200 論理和 90 100 120 RID1,RID6,RID9 RID1 RID4 RID6 RID9 RID19 データ・ページ X 199 245 345 444 RID4,RID6 RID19 RID9 XX X (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-3 アクセス経路の種類 4. 複数索引アクセス 1つの表に対して複数の索引を利用できる場合、索引ORing/索引ANDingという手法を使用することがあります。 索引ORingはORまたはINを使用する述語で使用され、重複するRIDを除くために用いられます。 索引ANDingはANDを使用する述語で使用され、重複するRIDを得るために用いられます。 上記の図は索引ORingを用いた例です。 1. 索引(COL1)によるCOL1=100の条件を満たすレコードのスキャン 2. 索引(COL2)によるCOL2>200の条件を満たすレコードのスキャン 3. 1と2の論理和がとられRID順にソートされる (索引ORing) 4. RIDに対応するデータページが物理的な並び順に取り出され該当行が取り出される(リスト先読み) 索引ANDingの場合は3のステップが論理積になります。 この場合、上記のSQL文の例は SELECT * FROM T1 WHERE COL1 = 100 AND COL2 > 200 3のステップのRIDのリストはRID6,RID9になります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 33-34 ) X ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-3 アクセス経路の種類 4. 複数索引アクセス(動的ビットマップANDing) SQL文(例) SELECT * FROM T1 WHERE COL1 BETWEEN 20000 AND 30000 AND COL2 BETWEEN 2000 AND 3000 COL1 BETWEEN 20000 AND 30000 COL2 BETWEEN 2000 AND 3000 0 1 1 1 ・・・・・・・・・・・・・・・・・・・・ 1 1 0 0 1 1 0 1 ・・・・・・・・・・・・・・・・・・・・ 0 1 1 1 論理積 0 1 0 1 ・・・・・・・・・・・・・・・・・・・・ 0 1 0 0 RID2 RID4 １コマが1行に相当 '1'：条件を満たす '0'：条件を満たさない (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-3 アクセス経路の種類 4. 複数索引アクセス(動的ビットマップANDing) 表のカーディナリティーが十分大きく、それぞれの条件において条件を満たす件数が多い場合、動的ビットマップANDingとい う手法が取られる場合があります。 RIDをハッシュして動的にビットマップを作成します。 その行が条件を満たす/満たさないをビットのOn/Offで表現します。 ビットがOnのものを逆ハッシュすることによりRIDを取得し、そのRIDを使用して行を取得されます。その後、述語について再検 査が行われます。 例 1. 索引(COL1)を使用してCOL1 BETWEEN 20000 AND 30000を満たすレコードをスキャン 2. RIDをハッシュしてビットマップを作成 3. 索引(COL2)を使用してCOL2 BETWEEN 2000 AND 3000を満たすレコードをスキャン 4. RIDをハッシュしてビットマップを作成 5. 2、4で作成されたビットマップをAND操作する 6. 結果のビットマップよりビットがOn(1)の行を結果としてフェッチする (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 35-36 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-3 アクセス経路の種類 5. 索引のみのアクセス SELECT COL1, COL2 FROM T1 WHERE COL2 = 100 SQL文(例) 索引(COL1,COL2) 必要な情報はすべて索引上にある データページにはアクセスしない データ・ページ (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-3 アクセス経路の種類 5. 索引のみアクセス(INDEX ONLY ACCESS) 索引のみにアクセスし、データページにはアクセスしません。 索引上に必要なデータが全て含まれている場合に選択されます。 意図的に索引のみアクセスをさせることにより一部の照会のパフォーマンスの改善がはかることができます。 この手法をUNIQUE索引にも適用するためCREATE INDEXのINCLUE文節を用いることが可能です。 INCLUDEされるカラムはユニーク性の検査から除外されます。 CREATE INDEX INCLUDE文節の例 CREATE UNIQUE INDEX IDX1 ON T1 (COL1 ASC) INCLUDE (COL2 ASC, COL3 ASC) 索引(IDX1) COL1 ユニーク性検査あり COL2 COL3 ユニーク性検査なし (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 37-38 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-3 アクセス経路の種類 サマリー表スキャン 基礎表にアクセスするのではなくサマリー表のデータを使用して照会できると、オプティマイ ザーが判断すると照会の書き直しを行いサマリー表のデータをアクセスする 最適化レベル5以上が必要 動的SQLのみに使用可能 Query #1 Query #2 Query #3 Query #4 Query #5 Query #n Summary Table Base Table #1 Base Table #2 Base Table #3 Base Table #4 ..... Base Table #n (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-3 アクセス経路の種類 サマリー表とは 照会結果をベースにした定義です。 単一表または複数表に存在するデータに基づいて、事前に照会処理された結果を含む表です。 サマリー表の効果 事前に集約計算/処理されたデータを使用するため、パフォーマンスの向上とシステム・スループットの向上が見込まれます。 照会で得られるべき結果がサマリー表に全て含まれない場合でも、照会の結果の一部または過程が既に計算されていれ ば、巨大な基礎表をアクセスするよりパフォーマンスの向上が見込まれます。 サマリー表の使用 最適化レベル5以上が必要 動的SQLのみに使用可能 サマリー表への反映時期を指定 REFRESH DEFERRED REFRESH IMMEDIATE (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 39-40 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-4 先読み(Prefetch) 先読みとは 複数の索引ページおよびデータページを後で使用するという前提でディスクからバッファー・ プールに事前に取り出しておく仕組みの事 １ページずつ取り出す事に比べ入出力待ちの時間が減少するためパフォーマンスの向上が 見込める I/O回数の軽減、CPU資源の有効活用 先読みには順次先読みとリスト先読みがある 順次先読み リスト先読み これらは通常の読み取りを補うもの 通常の読み取り時は、データの１ページが転送される (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-4 先読み(Prefetch) 先読みとは ここでは以下のトッピックについて説明します。 順次先読み(Sequential Prefetch) アプリケーションでページが必要になる前に、連続したページをバッファープールに読み込む仕組みの事 順次検出(Sequential Detection) データベース・マネージャーが入出力をモニターし、先読みを活動化したり非活動化すること リスト先読み(List Prefetch) 必要なデータ・ページが連続していない場合でも、効果的にデータ・ページにアクセスする方法の１つ (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 41-42 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-4 先読み 順次先読み(Sequential Prefetch) 1回の入出力操作で複数の連続したページをバッファー・プールに読み込む アプリケーション実行時のオーバーヘッドの大幅な短縮 複数の入出力操作を並列に行うことによりさらに短縮 データベース・マネージャーが実行を決定 PREFETCHSIZE CREATE/ALTER TABLESPACE SQL文のPREFETCHSIZE文節 PREFETCHSIZE = EXTENTSIZE * コンテナーの数 POOL_ASYNC_DATA_READS(バッファー・プール非同期データ読み取り) データベース・システム・モニターにより提供されるデータ要素 このデータ要素を調べることにより先読みが行われている事を確認 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-4 先読み 順次先読み(Sequential Prefetch) 1回の入出力操作で複数の連続したページをバッファー・プールに読み込みます。 I/O待ちの大幅な短縮によりCPU資源の有効活用がはかれます。 複数の表スペース・コンテナーを別物理DISKに配置することにより入出力操作を並列に行うことができます。 データベース・マネージャーが実行を決定します。 データベース・マネージャーが順次I/Oが適切であり順次先読みを行うことによりパフォーマンスが向上すると判断した時 に行われます。 PREFETCHSIZE CREATE/ALTER TABLESPACE SQL文のPREFETCHSIZE文節で指定します。 システム・カタログSYSCAT.TABLESPCES(PREFETCHSIZE)に収められます。 推奨値は(PREFETCHSIZE = EXTENTSIZE * コンテナーの数)となります。プリフェッチ・サイズがエクステント・サイズの倍 数である場合は、データベース・マネージャーは入出力を並行して行うことができるからです。入出力を並行して行う条件 として以下も満たしている必要があります。 プリフェッチ中のエクステントが異なる物理装置上にある 複数の入出力サーバーが構成されている (num_ioservers) 省略時値はデータベース構成パラメータ(DFT_PREFETCH_SZ)により決定されます。 指定した値以下になることがあります。 データベース・マネージャーがバッファー・プールの使用をモニターしているため他のUOWで必要なページを除去しない様に しているため、指定値より小さくなることがあります。 順次先読みが行われているにもかかわらず、I/O待ちが生じている場合、PREFETCHSIZEを大きくすることによりパフォー マンスの向上がみこまれます。 EXTENTSIZE CREATE TABLESPACE SQL文のPREFETCHSIZE文節で指定します。 システム・カタログSYSCAT.TABLESPCES(EXTENTSIZE)に収められます。 省略時値はデータベース構成パラメータ(DFT_EXTENT_SZ)により決定されます。 POOL_ASYNC_DATA_READS(バッファー・プール非同期データ読み取り) データベース・システム・モニターにより提供されるデータ要素です。 このデータ要素を調べることにより先読みが行われている事を確認する事ができます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 43-44 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-4 先読み 順次検出(Sequential Detection) データベース・マネージャーにより検出 SEQDETECT=YES 索引ページ、データページの両方に適用 順次先読みが起こる場合 最初は索引スキャンであったがほとんど順次I/Oとなってしまった場合 (例)SELECT NAME FROM TABLE1 WHERE EMPNO BETWEEN 100 AND 5000) 区画内並列操作の場合、順次検出を行いやすくなる (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-4 先読み 順次検出(Sequential Detection) データベース・マネージャーにより検出されます。 データベース・マネージャーが入出力をモニターし、順次に近いアクセスをしているかどうかを判断します。 この判断を元に動的に先読みを活動化/非活動化をおこないます。 これは順次先読みがパフォーマンス向上につながるかどうかが事前に判定しにくい場合に起こります。 SEQDETECT=YES データベース構成パラメータです。 省略時値はYESです。 特別の場合を除いてYESを指定しておく事をお勧めします。 索引ページ、データページの両方に適用されます。 順次先読みが起こるのは最初は索引スキャンであったがほとんど順次I/Oとなってしまった場合です。ここでは2つの例を挙げ ておきます。 SELECT NAME FROM EMPLOYEE WHERE EMPNO BETWEEN 100 AND 30000 この例の場合、オプティマイザーはEMPNO列の索引を使用するアクセス・パスを選択する事があります。 この索引の クラスター率が高いとデータ・ページの読み取りはほぼ順次となり先読みが行われます。 この例では、さらに索引ペー ジの先読みが行われます。データベース・マネージャーが索引ページの順次ページ読み取りが行われている事を検出 した場合には、索引ページの先読みが行われます。 DO WHILE(1) トランザクションファイルから1レコードを読み込む ホスト変数HVに値を設定する SELECT * FROM T WHERE PKEY = ：HV ; END ; SQL文は単独のSELECT文で、しかも主キー索引への等号述語の場合、アクセス経路はユニーク索引アクセスになり ます。 それをDOループの中で繰り返し、索引のクラスター率が高く、かつHVの値がキー順に与えられていた場合、 データ・ページの読み取りはほぼ順次となり先読みが行われます。 区画内並列操作の場合、データベース・マネージャーは先読みを積極的に行います。 入出力操作をしているサブ・エージェントが入出力待ちに入ると、関連するサブ・エージェントも待機しなければならなくな るため、並列処理のコストが高くなってしまうからです。 この状態を回避するため、区画内並列操作の場合は、順次検出の条件の緩和を行います。 サブ・エージェントの数が多いほど、順次検出の条件の緩和の度合いも大きくなります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 45-46 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-4 先読み リスト先読み(List Prefetch) SQL文(例) SELECT * FROM T1 WHERE COL1 = 100 OR COL2 > 200 索引(COL1) 索引(COL2) 100 200 論理和 90 100 120 RIDの取り出し RID1,RID6,RID9 RID1 RID4 RID6 RID9 RID19 データ・ページ X 199 245 345 444 RID4,RID6 RID19 RID9 効果的なデータへのアクセス XX X X (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-4 先読み リスト先読み 必要なデータ・ページが連続していない場合/クラスター率が低い場合でも、効果的にデータ・ページにアクセスする方法の１ つです。 リスト先読みの動き １つあるいは複数の索引を使用したマッチング索引スキャンを行い必要な行データを取り出すためのRIDのリストを取り出 します。 RIDがページ番号順にソートされ、重複行が除かれます。 ソートされたRIDリストの順にデータ・ページが取り出されます。 この時、１回のI/Oで複数ページが取り出されます。 通常の索引スキャンとの違い 結果セットの行の並びが、使用された索引の順と異なります。 それは、ページ番号順にソートされたRIDを使用するためです。 リスト先読みが行われる場合 クラスター率が低い場合、I/O回数を軽減するために使用されます。 複数索引アクセスをする場合に選択されやすくなります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 47-48 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-5 ジョイン(JOIN/結合) ジョイン(JOIN/結合) ジョインとは１つの表の行を他の１つまたは複数の表の行に結び付けることをいう 一般的には共通する項目(カラム)の値の一致により表と表を合成する処理の事をいう 複数表のジョイン ３つ以上の表をジョインする場合でも、オプティマイザーは２つの表だけをジョインする 必要であれば一時的な中間表を作成する １つの表を外部表、もう一方の表を内部表として選択する 外部表 はじめに１度だけアクセスする表 内部表 １回または複数回アクセスする表 複数回アクセスされるかどうかは結合方法や使用する索引による ２つの結合方法 結合述部の存在、表と索引の統計により選択される ネステッド・ループ・ジョイン(Nested Loop Join) マージ・ジョイン(Merge Join) ハッシュ・ジョイン(Hash Join) (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 第４章( 49-50 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-5 ジョイン(JOIN/結合) ネステッド・ループ・ジョイン(Nested Loop Join) (1)内部表のスキャンによる 例) SELECT COL1, COL2, COLB FROM T1, T2 WHERE COL1 = COLA 外部表 T1 COL1 COL2 3 2 3 1 内部表 T2 結合表 COLA COLB COL1 COL2 COLB T A 3 A B C 2 3 2 S 3 T 2 B S 2 2 U 2 V B C U 1 2 3 T 3 1 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-5 ジョイン(JOIN/結合) ネステッド・ループ・ジョイン(Nested Loop Join) ネステッド・ループ・ジョインは次のいずれかの方法で実行されます。 (1) 内部表をスキャンすることにより検索する方法 (2) 外部表のアクセスされる各行ごとに、索引検索を行う方法 (1)内部表のスキャンによる結合 結合方法 外部表から1行取り出す 外部表の結合列の値を探して結合する 内部表の最後の行まで探す これを外部表の行数分繰り返す(図中の①/②/③は外部表の各行に相当) ＊外部表の事前ソート 外部表を結合前にソートした方が効率がよいとオプティマイザーが判断した場合、事前に外部表のソートが行われます。 外部表を結合列でソートしておくことにより、内部表に関するI/O回数が軽減される場合に行われます。 GROUP BY、DISTINCT、ORDER BY、またはマージ・ジョインなどのように後に必ずソートが発生する場合で、あらかじめ ソートしておいた方が効率がよい場合に行われます。 ＊ローカル述語の適用時期 ローカル述語とはデータ・ページからデータを取り出した後に適用する必要がある述語の事をいいます。 外部表は結合前に適用する 内部表は結合後に適用する (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 51-52 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-5 ジョイン(JOIN/結合) ネステッド・ループ・ジョイン(Nested Loop Join) (2)内部表の索引検索による 例) SELECT COL1, COL2, COLB FROM T1, T2 WHERE COL1 > COLA 外部表 T1 COL1 COL2 3 A 2 3 B C T2索引 1 2 3 内部表 T2 結合表 COLA COLB COL1 COL2 COLA COLB 1 2 4 S 3 T 3 2 U 4 1 V 3 3 2 3 3 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-5 ジョイン(JOIN/結合) ネステッド・ループ・ジョイン(Nested Loop Join) (2)内部表の索引検索による結合 外部表から1行取り出し内部表のデータを検索するという意味で基本的な動きは同じです。 内部表の検索に索引を使用するため、無駄なデータページへのアクセスがさけられます。 結合方法 外部表から1行取り出す 外部表の結合列の値を内部表の索引を検索して結合する これを外部表の行数分繰り返す(図中の①/②/③は外部表の各行に相当) 適用 述部が以下の形式で存在している場合に指定された述部に対して使用されます。 ［外部表に関する式］ ［比較演算子］ ［内部表の列］ (例) OUTER.COL1 + OUTER.COL2 <= INNER.COLA OUTER.COL3 < INNER.COLB (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 53-54 ) A A B C C 1 V 1 2 U 1 1 1 V V 2 3 2 U 3 ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-5 ジョイン(JOIN/結合) マージ・ジョイン(Merge Join) 例) SELECT COL1, COL2, COLB FROM T1, T2 WHERE COL1 = COLA 外部表 T1 COL1 COL2 内部表 T2 スキャンのイメージ図 COLA COLB 外部表 3 A 2 S 1 2 3 B 3 T 2 C 2 U 3 1 V 等価結合述部で ソートされた一時表 COL1 COL2 2 B 3 3 A 1 2 3 結合表 COLA COLB 1 内部表 2 C 3 COL1 COL2 COLB 1 V 2 B S 1 2 2 S 2 3 B A U 1 T 2 3 C T 3 3 U T (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-5 ジョイン(JOIN/結合) マージ・ジョイン(Merge Join) 結合方法 結合される列は並び替えの後、同時に、また上から順番にスキャンされます。 外部表は1回だけスキャンされます。 外部表に重複する値がない場合は内部表のスキャンも1度だけとなります。 外部表に重複する値がある場合は内部表の該当する列が重複している回数だけスキャンされます。 結合する表を結合列でソートし、必要であれば一時スペースに置かれます。 外部表から1行取り出し、結合列に一致する内部表の行を結合します。(①) 一致しない場合には外部表から1行取り、内部表の続きから検査/結合を行います。(②) 上記の操作を繰り返し行います。 外部表の値が重複する場合(外部表から取り出した値が前回取り出した値と同じ場合)、内部表のスキャンを前回と同じと ころから行います。(③) 適用について table1.column = table2.column という形式の述部が必要(等価結合述部)です。 ソートによる、または索引アクセスにより結合する列の並び替えが必要になります。 結合列をロング列(LONG VARCHAR、LONG VARGRAPHIC)やLOB列にすることはできません。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 55-56 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-5 ジョイン(JOIN/結合) ハッシュ・ジョイン(Hash Join) 例) SELECT * FROM SIBLINGS S, OCCUPATIONS O WHERE S.JOBID = O.JOBID db2set DB2_HASH_JOIN=yes ソート・ヒープ 外部表 ... ... ... ... ... ... ... ... DESCRIPTION Civil Engineer Interpreter CPA Biotechnician Medical Technologist Police Nurse Computer Analyst 内部表 JOB_ID 3 2 8 4 5 6 7 1 HASH JOB_ID 8 8 3 4 2 1 1 5 6 7 FIRSTNAME Debbie Tad Donald Doreen Donna Doug David Debbie Dennis Diane ... ... ... ... ... ... ... ... ... ... Bucket 1 Bucket 2 Bucket 3 HASHコードごとの バケツにおかれる JOB_IDについてのＨＡＳＨアルゴリズムに基づいて、内部表の行がメモリー・バケツに置かれる 外部表の行がスキャンされ、JOB_IDがＨＡＳＨされ、ハッシュ・バケツにある行に対してつき合 わされる。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-5 ジョイン(JOIN/結合) ハッシュ・ジョイン(HASH Join) 結合方法 内部表がスキャンされ結合列の値をハッシュしたハッシュ・コードにより分割されソート・ヒープにおかれます。 外部表をスキャンし結合列の値をハッシュし、ハッシュ・コードが一致するバケツを検索します。 一致するバケツがあれば、実際の結合列の値を比較/結合を行います。 適用 table1.column = table2.column という形式でタイプが同一である1つ以上の述部が必要です。 タイプがCHARであれば長さが同じ タイプがDECIMALであれば精度/位取りが同じ 結合列をロング列(LONG VARCHAR、LONG VARGRAPHIC)やLOB列にすることはできません。 DB2_HASH_JOINレジストリ変数が'yes'であることが必要です。 考慮点 索引のない表に対してハッシュ・ジョインは効果的です。 SORTHEAP(データベース構成パラメータ)とSHEAPTHRES(データベース・マネージャー構成パラメータ)のチューニングを 考慮する必要があります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 57-58 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-5 ジョイン(JOIN/結合) 内部表と外部表の決定の要因 ネステッド・ループ・ジョイン サイズ 述語 バッファリング 索引 順序の要件 マージ・ジョイン 重複行 ハッシュ・ジョイン サイズ (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-5 ジョイン(JOIN/結合) 内部表と外部表の決定 ネステッド・ループ・ジョイン サイズ 内部表の再アクセスする回数を減らすため、通常は小さい方の表が外部表となる可能性が高くなります。 先読みにより非常に効率良く行にアクセスできる場合は、逆に大きい表が外部表になることがあります。 述語 選択述語を表に適用できる場合は、内部表にアクセスする行数が減るため、外部表として選択される可能性が高くなりま す。 バッファリング 外部表の各行について内部表全体をスキャンしなければならない場合(つまり、内部表に対して索引参照を実行できない 場合)、バッファリングを利用できるようにするため、小さい表を内部表として選択される可能性が高くなります。 これは表のサイズやバッファー・プール・サイズにより影響されるます。 索引 どれか1つの表上で索引検索を実行できる場合、その表が内部表としての使用に適します。 順序の要件 ORDER BYのように順序付けがしてされている場合、 その列を持つ表が外部表になる場合があります。 (例1) ジョインの後にソートするより、ジョインの前に外部表をソートした方がコストが低いと見積もられた場合 (例2)該当のカラムに索引があり、それを用いて表にアクセスできる場合 例のように指定された順序どおりに外部表としてアクセスできれば、ジョインの結果が順序どおりになっているため、コスト が低くなるからです。 マージ・ジョイン 重複行 重複が少ない表の方が外部表として選択される傾向があります。 ただし、マージ・ジョインの場合、内部表/外部表の選択は、さほど重要ではありません。 ハッシュ・ジョイン サイズ ハッシュ・ジョインの場合、内部表はソート・ヒープに置かれます。 ソート・ヒープが不足すると分割され一時表へと書きださ れるためパフォーマンスが劣化します。 オプティマイザーはこれを避けるため２つの表のうち小さい方を内部表として扱う 可能性が高くなります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 59-60 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-6 述語の種類 述語の種類 範囲区切り述語 索引探索引数述語 データ探索引数述語 その他の述語 ステージ1述語/ステージ2述語 処理効率 特性 範囲区切り 索引探索引数 データ探索引数 その他 索引入出力の低 減 データ・ページ入 出力の低減 YES NO NO NO YES YES NO NO 内部的に渡され る行数の低減 YES YES YES NO 修飾行数の低減 YES YES YES YES (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-6 述語の種類 述語は"いつどのように評価されるか"により4つのカテゴリーに分類されます。 (処理効率の良い順) 範囲区切り述語 (Range Delimiting Predicates) 索引探索引数述語 (Index SARGable Predicates) データ探索引数述語 (Data SARGable Predicates) その他の述語 (Residual Predicates) (注)SARGableとはSearch Argument-able(引数によって探索可能)を意味する造語 ステージ1述語/ステージ2述語 ステージ1述語 データ・マネージメント・サービス(DMS)により処理されます 索引が使用される可能性があります。 範囲区切り述語 索引探索引数述語 データ探索引数述語 ステージ2述語 DMSの処理から戻ってきた後、リレーショナル・データ・サービス(RDS)により処理されます 索引は使用されません その他の述語 処理効率 照会に述語を使うと、その照会を満たすために読み取られるデータの量を減らすことができます。 述語のカテゴリーが異なる と照会のパフォーマンスに影響を与えるため、オプティマイザーはこの影響を計算します。 上記の表はそれぞれの種類の述語がパフォーマンスに与える影響を記述しています。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 61-62 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-6 述語の種類 索引探索引数述語 範囲区切り述語 TABLE TBL1:SALES_DATE DATE, SALES_PERSON VARCHAR, REGION VARCHAR, SALES INTEGER INDEX IX1: SALES_DATE ASC, SALES_PERSON ASC, SALES ASC SELECT * FROM TBL1 WHERE SALES_DATE >= '1999 -03-29' AND SALES_DATE <= '1999 -03-31' AND SALES_PERSON >= 'LAA' AND SALES_PERSON <= 'LGG' SELECT * FROM TBL1 WHERE SALES >= 3 AND SALES <= 18 索引 IX1 SALES_DATE SALES_PERSON SALES SALES_DATE >= '1999 -03-29' AND SALES_PERSON >= 'LAA' AND SALES_PERSONによる スクリーニング SALES_DATE <= '1999 -03-31' AND SALES_PERSON <= 'LGG' 1999-03-28 1999-03-28 1999-03-29 1999-03-29 1999-03-29 1999-03-30 1999-03-30 1999-03-30 1999-03-31 1999-03-31 1999-03-31 1999-04-01 1999-04-01 1999-04-01 GOUNOT LEE GOUNOT LEE LUCCHESSI GOUNOT LEE LUCCHESSI GOUNOT LEE LUCCHESSI GOUNOT LEE LEE 1 3 1 3 3 18 3 2 1 3 1 1 3 4 SALESによるスクリーニング (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-6 述語の種類 範囲区切り述語 範囲を限定することができる述語のことをいいます。 つまり、索引スキャンのキー値を開始または停止(または、その両方)で きる述語です。 図の例ではSALES_DATEとSALES_PERSONが範囲区切りとして機能しています。 例えば、SALES_DATEだけが機能 してい る場合であれば索引をスキャンする範囲は1999-03-29<=SALES_DATE<=1999-03-31となりますが、SALES_PERSONが範囲 区切りとして機能しているため、索引から読み込む行数が2行少なく済んでいます。 区切られた範囲がすべて該当行になることを差しているのではありません。図の例で説明するとSALES_DATEと SALES_PERSONで限定した範囲を索引から読み込んだ後にSALES_PERSONでスクリーニングする必要があります。 上記のように範囲区切り述語は索引から読み込むデータ量自体を低減することができます。 索引使用可能な等号述語 1. WHERE節の述語に、索引の最初の列から使用されていること 2. 索引列に対する等号述語が以下を比較対照としていること 定数、ホスト変数、評価結果が定数になる式 索引列に対する'IS NULL'または'IS NOT NULL' 基本的な副照(ANY、ALL、SOMEを含まず、相関副照会がない) 索引使用可能な不等号述語 1. WHERE節の述語に、索引の最初の列から使用されていること 2. 定数、ホスト変数、評価結果が定数になる式に対して評価する式を右辺とする以下の不等式 狭義不等号述語(Strict Inequality Predicates) '<'および'>' 1つの索引列だけが考慮されます 広義不等号述部(Inclusive Inequality Predicates) '>='、'<='、BETWEEN、LIKE 複数の索引列が考慮されます 索引探索引数述語 範囲を限定することはできないが、述語を評価するのに基礎表のデータを読み込むのではなく、索引データを使用するタイプ のものです。 索引データで評価した後に基礎表にアクセスするため、アクセスするデータページの数が少なくなります。 索引探索引数述語は索引から読み込むデータ量は低減できませんが、データページからのI/Oを低減することができます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 63-64 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 解説： 1-6 述語の種類 データ探索引数述語 (Data SARGable Predicates) 索引マネージャーで評価できないがデータ・マネージメント・サービス(DMS)で評価できる述語です 索引では評価できず、基礎表の個々の行をアクセスする必要があります。 つまり、評価すべき列が索引上にない場合が該当 します。 図の例の表/索引で説明すると'SELECT * FROM TBL1 WHERE REGION = 'Quebec'のようなケースがあてはまります。 その他の述語 (Residual Predicates) 一般にその他の述部は、基本表の単純アクセスを超えた入出力を必要とするものです。 例としては以下のものが挙げられます 相関副照会を使用するもの 多値副照会(ANY、ALL、SOME、またはINによる副照会)を使用するもの LONG VARCHAR、LONG VARGRAPHIC、LOBのデータを読み取るもの これらの述語はリレーショナル・データ・サービス(RDS)により評価されます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ソートのための索引スキャン(参考) ソートのための索引スキャン 今まで説明してきた4つのカテゴリーとは別に索引の有効な利用形態としてソートのための索引スキャンがあります。 ソートする列の順番が索引列の順番と同じであり、索引の1列目から始まっていることが必要です。 ソートはORDER BY、DISTINCT、GROUP BY、"= ANY" 副照会、"> ALL"副照会、"< ALL"副照会、INTERSECTまたは EXCEPT、UNIONなどの操作で必要になります。 図の例の場合、以下のような照会文であれば、索引を使用してアクセスすれば、必要なソートが行えることになります。 SELECT * FROM TBL1 ORDER BY SALES_DATE, SALES_PERSON, SALES 索引キー列の評価結果が定数になるケースは、例外的に索引の1列目以外の列によるソートが可能です。 SELECT * FROM TBL1 WHERE SALES_DATE = '1999-04-01' AND 'SALES_PERSON = 'LEE' ORDER BY SALES この例の場合、SALES_DATEとSALES_PERSONは必ず同じ値になり、結果としてこの列についてはソートされたのと同 じ状態になります。 つまり下のSQL文と同じと考えることができるわけです。 SELECT * FROM TBL1 WHERE SALES_DATE = '1999-04-01' AND 'SALES_PERSON = 'LEE' ORDER BY SALES_DATE, SALES_PERSON, SALES ユニーク索引を使用することにより順序要件を切り捨てることができる場合があります。 SELECT * FROM TBL1 ORDER BY SALES_DATE, SALES_PERSON, SALES 例えば、このSQL文でSALES_DATEがユニーク索引であると仮定しましょう。 その場合SALES_DATAは固有であるため、それより後ろの列、SALES_PERSONおよびSALESについてのソートが不要 になります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 65-66 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-6 述語の種類 ソートのタイプ ソート・フェーズ ソート結果を戻すフェーズ ソート・ヒープ ソート・ルーチン 一時表 オーバー・フロー パイプ 非パイプ 一時表 受け取りルーチン (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-6 述語の種類 ソートしている最中(ソート・フェーズ)と、ソートの結果を戻すとき(ソート結果を戻すフェーズ)の振る舞いで下記のようにソートをタ イプ分けすることができます。 ソート・フェーズ オーバー・フローありとオーバー・フローなし ソートされるデータがソート・ヒープに収まらない場合、一時表にオーバー・フローします。 この場合、ソート・ヒープに収ま る量のサブセットに分けられ、サブセットの単位でソートが実行され、これらをマージすることにより結果を得ることになりま す。 オーバー・フローしないソートの方が効率よく実行されます。 ソート結果を戻すフェーズ パイプ・ソートと非パイプ・ソート データの最終ソート結果が1回の順次引き渡しで読み取り可能な場合には、結果をパイプ処理をすることができます。 そ れが可能でない場合は、結果を保管する一時表を作成します。 パイプ処理は一時表を作成する非パイプ処理より高速で 処理することができます。 オプティマイザーは可能であればパイプ処理することを選択します。 オプティマイザーはアクセス・プランを選択する際に、ソートによるマフォーマンスの影響を考慮します。 ソートは”ソートのための 索引スキャン”に使用する索引がない場合、または索引を使用するよりコストが低いとオプティマイザーが判断した場合に行われ ます。 ソートに影響を与えるパラメータ ソートヒープ・サイズ(sortheap) データベース構成パラメータです ソートごとに使用する記憶域量の最大値を指定します ソートを実行するために必要となった時点で割り振られ、ソートが終了したときに開放されます ソートヒープのしきい値(sheapthres) データベース・マネージャー構成パラメータです インスタンスでの私用のソート・ヒープの合計の目安、および、データベースでの共用のソートヒープの合計の制限 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 67-68 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-6 述語の種類 処理の概要 ユーザー・アプリケーション リレーショナル・データ・サービス(RDS) オプティマイザー その他の述語 カタログ・サービス プッシュ・ダウン データ・マネージメント・サービス(DMS) データ検索引数述語 Sort List Servics 索引マネージャー 範囲区切り述語 索引検索引数述語 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-6 述語の種類 処理の概要 述語はいくつかのコンポーネントにより処理されます。 ここでは、述語の処理にかかわる代表的なコンポーネントについて説 明します。 リレーショナル・データ・サービス(RDS) アプリケーションからのSQL要求の受け渡し ステートメントのオプティマイズ オプティマイザーはRDSの１つのコンポーネントですが、その成り立ちや役割から、独立したコンポーネントのように表現さ れることがあります。 アクセス・プランを作成し、各述語を処理すべきコンポーネントを決定/呼び出しを行います。 ’その他の述語’の評価はRDSで行われます。 カタログ・サービス RDSのコンポーネント１つで、RDS/オプティマイザーへシステム・カタログへアクセスするサービスを行います。 データ管理サービス(DMS) RDSからアクセス・プランを受け取り、データを取得します。 必要であれば索引マネージャーを呼び出します。 索引マネージャーの処理を受け、バッファープール・サービスを呼び出してデータ・ページを読み取ります。 データ探索引数述語の評価はDMSで行われます。 索引マネージャー DMSのコンポーネントの1つで索引についてのサービスを提供します。 範囲区切り述語、索引探索引数述語の評価は、索引マネージャーで行われます。 ソート・リスト・サービス ソートを行うコンポーネントです。 通常はDMSからデータを受けたRDSにより呼び出される。 オプティマイザーによりプッシュ・ダウンが選択された場合には、 DMSから直接呼び出されることもある。 プッシュダウン(Push Down) ここで説明するプッシュ・ダウンとは、通常はDMSからRDSに制御を戻してから次のコンポーネントを呼び出すものをオプティ マイザーの判断によりRDSに制御を戻さずDMSから直接呼び出す事をいいます。 図の例ではDMSにより取得されたデータのソートをするために、DMSが直接ソート・リスト・サービスを呼び出しています。 例としては、GROUP BYの処理があります。 ソート処理＋集約の2つの処理が1回のDMSの呼び出しで行われます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 69-70 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-7 区画内並列処理 区画内並列処理の設定 データベース・マネージャー構成パラメータ INTRA_PARALLEL MAX_QUERYDEGREE データベース構成パラメータ DFT_DEGREE プリコンパイル/バインドのオプション 並行度=4の例 DEGREE 特殊レジスター INTRA_PARALLEL = YES DFT_DEGREE=4 CURRENT DEGREE db2cli.iniのキーワード DB2DEGREE コーディネーター・ エージェント コマンド SELECT ・・・ SET SET RUNTIME DEGREE FOR サブ・エージェント (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-7 区画内並列処理 区画内並列処理とは1つの検索を複数のパーツに分割して実行する方法で、SMPの環境で検索の効率をあげることが可能です。 ユーザーは並行度を指定することができます。 区画内並行処理の設定 データベース・マネージャー構成パラメータ INTRA_PARALLEL=YES データベース・マネージャーが区画内並列処理機能を使用できるか否かを指定します。 MAX_QUERYDEGREE=nまたはANY データベース・マネージャー構成パラメータです。区画内並列処理の最大並行度を指定します。SQLステートメントはそ の実行時にはこの数をこえる並行操作を使用することはありません。 ANYの場合はオプティマイザーが決定した並列度 を使用することを示します。 データベース構成パラメータ DFT_DEGREE=nまたは-1 この値がCURRENT DEGREE特殊レジスターおよびDEGREEバインド・オプションの省略時値になります。 1の場合は並 列処理を行わない事、-1の場合はプロセッサー数と照会のタイプによりオプティマイザーが決定する事を意味します。 プリコンパイル/バインドのオプション(静的SQLのために) DEGREE=nまたは-1 DFT_DEGREEと同じ指定の仕方になります。省略時にはDFT_DEGREEが使用されます。 特殊レジスター（動的SQLのために) CURRENT DEGREE SQL文"SET CURRENT DEGREE n"で変更する事ができます。 DFT_DEGREEと同じ指定の仕方になります。 db2cli.iniのキーワード(CLI/ODBCアプリケーションのために) DB2DEGREE "SET CURRENT DEGREE n"を最初に発行します。 0はこのセットコマンドを発行しない事を、ANYは場合はプロセッ サー数と照会のタイプによりオプティマイザーが決定する事を意味します。 コマンド SET RUNTIME DEGREE FOR 活動中のアプリケーションの最大並行度を変更するのに使用します。 アプリケーションごとにも指定できますが、FOR ALLを指定すると、インスタンス全体に影響します。 実際の実行時並行度は次の 3 つのうち、最も低いものです。 MAX_QUERYDEGREEデータベース・マネージャー構成パラメーター 静的SQLではDEGREEプリコンパイル/バインドのオプション、動的SQLではCURRENT DEGREE特殊レジスター SET RUNTIME DEGREE FORコマンドで指定された並行度 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 71-72 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-7 区画内並列処理 区画内並列スキャン 並列表スキャン INTRA_PARALLEL = YES DFT_DEGREE=4 コーディネーター・ エージェント 並列索引スキャン コーディネーター・ エージェント 共有メモリー 共有メモリー サブ・ エージェント Record 0 100 200 300 400 索引 データページ ページ範囲による割り当て、データページで分割 レコードの範囲による割り当て、行で分割 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-7 区画内並列処理 区画内並列スキャン 並列処理の場合、表からデータをどのように取得するかを説明します。 並列処理の場合のアクセス経路という事になりま す。 並列処理の場合も順次処理の時と同様に大きくは表スキャンと索引スキャンに分かれます。 コーディネーター・エージェントと各サブ・エージェントの間のデータの交換は共有メモリーを介して行われます。 並行度の数(例では4つ)のサブ・セクションに分割して実行します。 例ではあらかじめ4つに分けているような図になっていますが、実際には小分けにされたセクションをサブ・エージェントが 次々に処理していく事によってサブ・エージェント間でロード・バランスが行われています。 データは実行時に動的に分割されます。 スキャンの細分度(1回にサブ・エージェントが処理する範囲の大きさ)はオプティマイザーにより決められます。 並列表スキャン 同じ表上で並列して実行されます。 表はページ範囲で割り当てられデータページで分割されます。分割後、そのページ範囲がサブ・エージェントに割り当てら れます。 サブ・エージェントは割り当てられた範囲をスキャンし、その現行の範囲での作業が完了した時点で別の範囲が 割り当てられます。 並列索引スキャン 同じ表/索引上で並列して実行されます。 索引は索引キーの値とそのキーの値のエントリー数に基づき、レコード範囲で割り当てられます。この場合、データページ で分割はなく行で分割されます。分割後、そのレコード範囲がサブ・エージェントに割り当てられます。 サブ・エージェント は割り当てられた範囲をスキャンし、その現行の範囲での作業が完了した時点で別の範囲が割り当てられます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 73-74 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-7 区画内並列処理 並列ソートの方式 並列ソート - Round Robin ‘n’ は intra-partition agentの数 ラウンド・ロビン・ソート 均等再配分 ‘n’個の sort出力領域が作成される データは均等に配分される バランス処理が許される 区分ソート ‘n’個の sort出力領域が作成される データの値によって同じ領域に ハッシュされる joins や aggregation処理で使用される ハッシュ再分配 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-7 区画内並列処理 DB2はSMPシステムにおいて４つのソート方式で共有メモリーを使用します。 共有メモリーはSMPエージェントが共通の入出力領域として使用する事ができ、各SMPエージェントはそれを個々の入出力領 域として持つ事ができます。以下にソート方式について記述します。 ラウンド・ロビン・ソート(Round-Robin Sort)： これは効率的な共有メモリーソートで、全てのSMPエージェント（Subagents)に対してデータを分配します。この方法はラウン ド･ロビン型アルゴリズムを使用してデータを均等に分配します。ソートの出力領域は各Subagents毎にメモリーを作成します。 ソートの挿入フェーズではSubAgentsは順番にそれぞれのソート出力メモリー領域にデータの挿入を行います。これにより、よ り均等にデータを分配することができます。 区分ソート(Partitioned Sort)： 区分ソートは、SubAgent毎にソート出力領域が作成される点ではラウンド・ロビン方式と似た動きをします。SubAgentsはソー ト列にハッシュ関数を適用して行をどのソート領域に挿入するか決定します。 このように列の値によってデータを分配すること により、後でマージ結合や集約処理のために使用します。 例えば、マージ結合の内部と外部が区分ソートの場合１つの SubAgentは対応する内部と外部のソート出力領域を結合することでマージ結合を行う事ができます。 この2つのソートはサブ・エージェントごとにソート出力領域が作成されるという共通点をもっており、この2つを同じ括りとしてラウン ド・ロビン・ソートという事があります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 75-76 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-7 区画内並列処理 並列ソート - Shared ‘n’ は並行度 複写ソート 均等再配分 1個の sort出力領域が作成される ‘n’個の subsection pieceが sort結果を読み込む それぞれが全ての結果を読み込む データ量が少ないとき使用される 共有ソート 1個の sort出力領域が作成される ‘n’個の subsection pieceが sort結果を読み込む 全てが結果を並列に読み込む 均等配分を助ける 均等再配分 cf078109 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-7 区画内並列処理 もし全てのSubAgentsが全てのデータを必要とするような場合、複写ソートまたは共有ソートが選択されます。複写ソートはデータ 量が少ない場合に使用され、共有ソートはソート結果が大きい場合に使用されます。 複写ソート(Replicated Sort)： 複写共有ソートは、全てのSubagentsが全てのソート出力の行を必要とする場合に使用されます。１つのソートがメモリーに作 成され、SubAgentsはソート領域への挿入時に同期化されます。 ソート処理が終了すると、各SubAgentsがソート領域から全 てのデータを読み取ります。 このソートは行数が少ない場合に使用されます。 共用ソート(Shared Sort)： 動的共有ソートは、ソート出力領域を並列読み込みを行う点を除いては複写ソートと同じです。共有ソートはラウンド・ロビン ソートと同じようにSubAgents間にデータを均等に分配します。 この２つのソートは全てのサブ・エージェントがデータを共有することから、この2つを括り単に共有ソートという場合があります。 こ の場合、それぞれ複写共有ソート、動的共有ソートと言い分けます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 77-78 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-7 区画内並列処理 区画内並列処理の考慮点 並列処理を行う事により、かえって遅くなる事がある 実行時には、バインド時にオプティマイザーが選択したよりも、低い並列度になる可能性があ る 並列処理はSMPの場合に、単一プロセッサーより速く、照会処理を実行 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-7 区画内並列処理 並行処理はより多くの資源を必要とするため、システムへのストレスは大きくなります。 また、大量のデータを多くの資源を効率よ く使用してより良いスループットをあげるのに適した仕組みであるため、多くのユーザーにより定型化された処理を行うOLTPでは 適しません。 OLAP等のように大量のデータを比較的小人数で扱うような場合に使用を検討した方がよいでしょう。 DEGREE = ANYで照会処理を実行した場合、データベース・マネージャーは、照会処理の内容や、プロセッサーの数を含むいくつ かの要素を基にして、イントラ・パーティション・パラレルの度合いを選択します。実行時に使用されるパラレルの度合いは、これら の要素次第では、プロセッサーの数よりも小さくなる可能性があります。 パラレリズムの度合いは、ステートメントがコンパイルされた時にオプチマイザーにより決定され、さらに、データベースの活動度 合い次第で、実行前に調整されます。パラレリズムの度合いは、システムの稼働率が高い場合には、オプチマイザーが選択した ものよりも低くなる可能性があります。これは、イントラ・パーティション・パラレルが積極的にシステム資源を使用して、照会処理 の処理時間を削減するために、他のデータベース・ユーザーのパフォーマンスに影響を与えてしまうことがありうるからです。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 79-80 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-8 区画間並列のジョイン 区分データベースにおけるジョイン(JOIN/結合) Collocated Join(併置結合) Directed Outer Join(外部表の指示結合) Directed Inner Join(内部表の指示結合) Directed Inner and Outer Join(内部表および外部表の指示結合) Broadcast Outer Join(外部表の同報通信結合) Broadcast Inner Join(内部表の同報通信結合) (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-8 区画間並列のジョイン Collocated Join(併置結合) ジョインするテーブルの分割キーが同じ 同一ノード内でのジョインが行われる ノード間通信が発生しない Directed Outer Join(外部表の指示結合) ジョインするテーブルの分割キーが異なる 一方のテーブルの分割キーを他方に合わせて内部的にデータを分割し直す Directed Inner Join(内部表の指示結合) 結合列を元に内部表から行をハッシングする Directed Inner and Outer Join(内部表および外部表の指示結合) ジョインするテーブルの分割キーが異なる 両方のテーブルで新たに分割キーを設定して内部的にデータを分割し直す ノード間通信が多量に発生する Broadcast Outer Join(外部表の同報通信結合) ジョインするテーブルが比較的小規模の場合、選択される テーブル自体をジョインするテーブルがあるノードへ転送する Broadcast Inner Join(内部表の同報通信結合) 内部表全体を他の表を含むすべてのノードにブロードキャストする (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 81-82 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-8 区画間並列のジョイン Collocated Join(併置結合) WHERE ORDERS.ORDERKEY = LINEITEM.ORDERKEY コーディネーター・ノード JOINキー ORDERS ORDERKEY LINEITEM ORDERKEY 分割キー同士のJOIN Node0 SELECT Node1 ORDERSを走査 述部適用 q1を読み取る 処理 結果を戻す 分割キー YES YES JOIN LINEITEMを走査 述部適用 ORDERSを走査 述部適用 JOIN LINEITEMを走査 述部適用 q1 q1 ジョインすべき行は全て同じノードにあるので全ての Join はロ−カルで行う (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-8 区画間並列のジョイン Collocated Join(併置結合) Orderkeyでテ−ブルをJoinする時UDBは両方のテ−ブルがJoinキ−で区画されていることを認識します。 ORDERSテ−ブルの行がJOINするキーが同じ値を持ったLINEITEMテ−ブルの行と同じノ−ドにあります。 全てのJoinはロ−カルで行われ、Joinする目的で他のノ−ドに行を送ることはありません。 Collocated Joinのアクセス・パスにするために反転サマリー表(Reversal Summary Table)を作成する事ができます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 83-84 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-8 区画間並列のジョイン Directed Outer Join(外部表の指示結合) WHERE ORDERS.ORDERKEY = LINEITEM.ORDERKEY コーディネーター・ノード JOINキー 分割キー ORDERS ORDERKEY NO LINEITEM ORDERKEY YES 内部表が分割キーのJOIN Node0 SELECT Node1 ORDERSを走査 述部適用 ORDERKEYを ハッシュ ｑ２への書き込み q1を読み取る 処理 結果を戻す ORDERSを走査 述部適用 ORDERKEYを ハッシュ ｑ２への書き込み q2 q2 q2 LINEITEMを走査 述部適用 ｑ２からの読込み JOIN ｑ１への書き込み ハッシュ LINEITEMを走査 述部適用 ｑ２からの読み込 み JOIN ｑ１への書き込み q1 q1 違うキ−で配置されている外部表(ORDERS)を内部表(LINEITEM)の区分化属性に基づいて再ハッシュ (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-8 区画間並列のジョイン Directed Outer Join(外部表の指示結合) UDBは片方の表(LINEITEM)がジョイン・カラム(ORDERKEY)で区画され,もう片方の表(ORDERS)はジョイン・カラム (ORDERKEY)と違うキ−で区画されていることを認識します。 外部表の各行を、 内部表の区分化属性に基づいて、内部表のデータベース区画のいずれか 1 つに送ります。 ジョインは、各データベース区画上で行われます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 85-86 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-8 区画間並列のジョイン Directed Inner Join(内部表の指示結合) WHERE ORDERS.ORDERKEY = LINEITEM.ORDERKEY コーディネーター・ノード Node0 SELECT q1を読み取る 処理 結果を戻す q2 JOINキー 分割キー ORDERS ORDERKEY YES LINEITEM ORDERKEY NO 外部表が分割キーのJOIN Node1 ORDERSを走査 述部適用 ORDERKEYを ハッシュ ｑ２への書き込み ORDERSを走査 述部適用 ORDERKEYを ハッシュ ｑ２への書き込み LINEITEMを走査 述部適用 ORDERKEYをハッ シュ ｑ３への書き込み q３ ｑ２からの読込み ｑ３からの読込み JOIN ｑ１への書き込み q2 q３ ハッシュ LINEITEMを走査 述部適用 ORDERKEYをハッ シュ ｑ３への書き込み q３ ｑ２からの読込み ｑ３からの読込み JOIN ｑ１への書き込み q1 q1 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-8 区画間並列のジョイン Directed Inner Join(内部表の指示結合) UDBは片方の表(ORDERS)がジョイン・カラム(ORDERKEY)で区画され,もう片方の表(LINEITEM)はジョイン・カラム (ORDERKEY)と違うキ−で区画されていることを認識します。 オプティマイザに内部表と決定した表(LINEITEM)の各行を、 外部表(ORDERS)の区分化属性に基づいて(ハッシュされ)外部 結合表のデータベース区画のいずれか 1つに送ります。 結合は、各データベース区画上で行われます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 87-88 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-8 区画間並列のジョイン Directed Inner and Outer Join(内部表および外部表の指示結合) WHERE ORDERS.ORDERKEY = LINEITEM.ORDERKEY コーディネーター・ノード Node0 SELECT q1を読み取る 処理 結果を戻す q2 JOINキー 分割キー ORDERS ORDERKEY NO LINEITEM ORDERKEY NO 分割キーではないカラム同士のJOIN Node1 ORDERSを走査 述部適用 ORDERKEYを ハッシュ ｑ２への書き込み LINEITEMを走査 述部適用 ORDERKEYをハッ シュ ｑ３への書き込み ORDERSを走査 述部適用 ORDERKEYを ハッシュ ｑ２への書き込み q2 q2 q３ q３ ｑ２からの読込み ｑ３からの読込み JOIN ｑ１への書き込み LINEITEMを走査 述部適用 ORDERKEYをハッ シュ ｑ３への書き込み q３ ハッシュ ｑ２からの読込み ｑ３からの読込み JOIN ｑ１への書き込み q1 q1 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-8 区画間並列のジョイン Directed Inner and Outer Join(内部表および外部表の指示結合) オプティマイザによって決定された外部表/内部表いずれの表の行もジョイン・キーによってハッシュされたデータベース区画 のいずれか 1つに送ります。 結合はデータを各データベース区画へ送信した後、各データベース区画上で行われます。 ジョインするキーがいずれもパーティション・キーではない場合に選択されます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 89-90 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-8 区画間並列のジョイン Broadcast Outer Join(外部表の同報通信結合) JOINキー 分割キー ORDERS ORDERKEY NO LINEITEM ORDERKEY NO 分割キーではないカラム同士のJOIN WHERE ORDERS.ORDERKEY = LINEITEM.ORDERKEY コーディネーター・ノード Node0 SELECT Node1 ORDERSを走査 述部適用 ｑ２への書き込み q1を読み取る 処理 結果を戻す ORDERSを走査 述部適用 ｑ２への書き込み q2 q2 q2 q2 LINEITEMを走査 述部適用 ｑ２からの読み込 み JOIN ｑ１への書き込み 同報通信 LINEITEMを走査 述部適用 ｑ２からの読み込 み JOIN ｑ１への書き込み q1 q1 一つのテーブルの全行(各ノードデ適用できる述部があれば適用後)を全てのノードに送信 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-8 区画間並列のジョイン Broadcast Outer Join(外部表の同報通信結合) オプティマイザに外部表と決定された表の全て(各ノードデ適用できる述部があれば適用後)の行をジョインするテーブルの存 在するノードへ送信します。 ジョインするキーがいずれもパーティション・キーではない場合に選択されます。 この結合方式が最も費用対効果が良い結合方式であると判断された場合も選択されます。 典型的な例としては、非常に大きな表が 1 つと非常に小さな表が 1 つあり、 どちらの表も結合述部列上で区分化されていな い場合です。 両方の表を区分化するよりも、 小さな表を大きな表に同報通信するほうがコストが低くなる可能性があります。 同報通信を回避し、Collocated Joinのアクセス・パスにするために複写サマリー表(Riplicated Summary Table)を作成する事 ができます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 91-92 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-8 区画間並列のジョイン Broadcast Inner Join(内部表の同報通信結合) WHERE ORDERS.ORDERKEY = LINEITEM.ORDERKEY コーディネーター・ノード JOINキー 分割キー ORDERS ORDERKEY NO LINEITEM ORDERKEY NO 外部表が分割キーのJOIN Node0 SELECT Node1 ORDERSを走査 述部適用 ｑ２への書き込み q1を読み取る 処理 結果を戻す q2 ORDERSを走査 述部適用 ｑ２への書き込み LINEITEMを走査 述部適用 ｑ３への書き込み q３ ｑ２からの読込み ｑ３からの読込み JOIN ｑ１への書き込み q2 q３ 同報通信 LINEITEMを走査 述部適用 ｑ３への書き込み q３ ｑ２からの読込み ｑ３からの読込み JOIN ｑ１への書き込み q1 q1 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-8 区画間並列のジョイン Broadcast Inner Join(内部表の同報通信結合) オプティマイザに内部表と決定された表の全て(各ノードデ適用できる述部があれば適用後)の行をジョインするテーブルの存 在するノードへ送信します。 ジョインするキーがいずれもパーティション・キーではない場合に選択されます。 同報通信を回避し、Collocated Joinのアクセス・パスにするために複写サマリー表(Riplicated Summary Table)を作成する事 ができます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 93-94 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-9 連合データベースのSQLコンパイラー 連合データベースのコンパイラー概要 SQL照会 リモート SQL 生成 照会の構文解析 (Parser) 照会の書き直し (Query Rewrite) グローバル 最適化 Pushdown 分析 (Query Optimizer) 実行可能 コードの生成 実行可能 プラン プラン実行 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-9 連合データベースのSQLコンパイラー 連合データベースのコンパイラー概要 この図は連合データベースでのSQLコンパイラーの概要を示したものです。 単一データベースのSQLコンパイラーとは、リモート・データソースに対してどの処理が実行できるかを分析するPushdown分 析と、リモート・データソースで実行するSQL文を生成するためのリモートSQL生成の部分が大きく違います。 連合データベース(ニックネームを使用したSQL実行)での最適化は、複数のデータソースにまたがる処理を最適化するという 意味でグルーバル最適化といいます。 広義では連合データベースでのSQLコンパイラーの仕組み全般を指し、狭義ではオプ ティマイザー(Query Optimizer)の部分を指します。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 95-96 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-9 連合データベースのSQLコンパイラー Pushdown分析 アプリケーションから 入力されたSQL文 SQL文をOracleへPushdown システムカタログ SELECT COUNT(*) FROM ORA100.O_RATE WHERE STATE='CA' (in UDB v7 SQL) UDB v7 SELECT COUNT(*) FROM SHIPPING.O_RATE WHERE STATE='CA' (in Oracle SQL) １レコード ORACLE CATALOG Oracle １レコード 関数やデータの絞込みについて、リモート・データソースで実行可能であるかを分析 Pushdownできない場合は全件をUDBに取り込む必要がある データソースにより機能/制限/限界/仕様/照合順序の違いがあるため、全てを Pushudownする事はできない場合がある 一般にPushdownした方がパフォーマンスがよい (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-9 連合データベースのSQLコンパイラー Pushdown分析 Pushdown分析はリモート・データ・ソースで特定の操作を実行できるかどうかを分析します。 分析結果はオプティマイザーに通知さ れ最適化の材料として使用されます。 Pushdown分析が必要な理由はSQL文といってもデータソース間で様々な違いがあるためです。 例としては以下のようなものがあ ります。 機能(SQL Capablility) 関係演算子、システムまたはユーザー関数、 または SQL 演算子 (GROUP BY、ORDER BY など) などの関数がサポートされ ているか、また、サポートされていたとしても機能(意味)は同じか？ 制限(SQL Restriction) ことなるSQL制限が存在する場合。 パラメーター・マーカーのバインド機構など。 限界(SQL Limits) 整数の最大値/最小値などのSQLの制限値の違いによるもの。 データソース特有の取り扱い(Server Specifics) たとえばソート時のNULLの扱いやキャラクター属性のデータの最後のブランクの扱い等 照合順序/比較 一般に数値比較の場合は問題になりません。 キャラクター属性の場合データソースにより照合順序が異なる場合があります。 例えば同じUDB同士であってもデータベース作成時のCOLLATE USINGの指定で変わります。 PUSHDOWNができない関数は、照会パフォーマンスに多大に影響することがあります。 選択述部を、データ・ソースで評価するの ではなく、 ローカルに評価するときの影響を考慮する必要があります。 この方法を使う場合、DB2 はリモート・データ・ソースから 表全体を検索し、 述部に対してローカルにフィルターしなければならないことがあります。 ネットワークに制約があり、なおかつ表 が大きい場合、 照会パフォーマンスに影響する場合があります。 PUSHDOWNされない演算子も、照会パフォーマンスに多大に影響することがあります。 たとえば、GROUP BY 演算子によってリ モート・データ・ソースをローカルに集約すると、 DB2 はリモート・データ・ソースから表全体を検索しなければならない場合がありま す。 一般にPushdownした方がパフォーマンスがよいとされます。 上記の図で説明すると．． 例えば条件節がSTATE > 'CA'でありリモート・データソースと照合順序が異なる場合、全件をローカルで処理する必要がありま す。 STATEというカラムに索引が存在した場合、リモート・データソースでのパフォーマンスが向上します。 "COUNT(*)"の扱いがリモート・データソースと異る場合、STATE = 'CA'に合致するレコードをDB2に送る必要があります。 (参考) SQL Compensation： リモートのサーバーで適用不可能な機能は、DJ/Fedが代替して適用する事 透過性を呼び出し側のアプリケーションに提供するために必要な仕組み (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 97-98 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-9 連合データベースのSQLコンパイラー グローバル最適化 グローバルな観点でのアクセス・ストラテジーを決定する 異種分散の照会は、元のSQLを各サーバーごとへのSQL文に分解し、返された結果を再結 合する方法を決定する グローバル最適化に影響する特性 サーバー特性 サーバー・オプション cpu_ratio/io_ratio/comm_rate/collating_sequence/plan_hints ニックネーム特性 統計情報(SYSSTAT.TABLES、SYSSTAT.INDEXES) CREATE INDEX ......... SPECIFICATION ONLY Pushdownに影響する特性 サーバー特性 サーバー・オプション pushdown/collating_sequence/varchar_no_trailing_blanks ニックネーム特性 ニックネーム列オプション numeric_string varchar_no_tailing_blanks (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-9 連合データベースのSQLコンパイラー グローバル最適化 リモート・データソースを含むグローバルな観点でのアクセス・ストラテジーを決定します。 異種分散の照会は、元のSQLを各 サーバーごとへのSQL文に分解し、返された結果を再結合する方法を決定します。 Pushdown分析の結果はグローバル最適化に推奨値を提供します。 たとえPushdown可能と判断しても、コストが高くなると判 断されればPushdownは選択されません。 グローバル最適化に影響する特性 様々な要因が関係しますが、ここでは主な要因としてサーバー特性とニックネーム特性について説明します。 サーバー特性としてはサーバー・オプションが挙げられます。 サーバー・オプションはCREATE SERVER/ALTER SERVER SQLステートメントで永続的に指定します。 SET SERVER OPTION SQLステートメントを実行することにより一時的にサー バー特性の変更を行うことが可能です。 cpu_ratio： CPUスピードの相対値 (対 UDB) io_ratio ： IOスピードの相対値 (対 UDB) comm_rate ： UDBとリモート・データソース間の通信速度（MByte) collating_sequence： UDBとリモート・データソース間の照合順序の相違 plan_hints ： プラン・ヒントを使用可能にするかどうかの指定 ニックネーム特性としては統計情報が挙げられます。 統計情報は単一データベースの場合と同様SYSSTAT.TABLES、 SYSSTAT.INDEXESに納められます。 索引は”CREATE INDEX ......... SPECIFICATION ONLY”のように作成されますが、この 索引の実体を作成されるわけではなく索引のエントリーのみが作成されます。 ニックネームが初めて作成される場合、 DB2 が認識できる形式の基礎表用の索引がデータ・ソースにあれば、 索引の指定が生成されます。 次の場合は、索引を手動で 作成する事を考慮してください。 DB2 が、ニックネームの作成時にデータ・ソースから索引情報を検索できない場合 基礎表の索引が、その表のニックネームが作成された後に追加された場合 視点のニックネームのために索引が必要な場合 結果として、パフォーマンスが向上する場合(チューニングとして) DB2はニックネームに関する統計を自動的に更新する機能は持っていないため更新するには以下の 2 つから処置を選択し ます。 データ・ソースで RUNSTATS と同等の機能を実行する。 その後、現在のニックネームを除去し、 ニックネームを再作成し ます。 SYSSTAT.TABLES 視点の統計を手動で更新します。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 99-100 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 解説： 1-9 連合データベースのSQLコンパイラー Pushdownに影響する特性 Pushdownに影響サーバー特性としてはサーバー・オプションが挙げられます。 pushdown ： pushdownを考慮するかの指定 collating_sequence ： 照合順序がUDBと同じかの指定 varchar_no_trailing_blanks： 可変長末尾のブランクを無視するかどうかの指定 ニックネーム特性はALTER NICKNAME SQLステートメントで指定できる列オプションが挙げられます。 numeric_string ： 数値データのストリングだけが含む事を前提とし照合順序の違いを無視します。 varchar_no_tailing_blanks ： 特定のVARCHAR 列に後書きブランクがない事を前提に最適化します。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 第４章( 101-102 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 1-9 連合データベースのSQLコンパイラー パフォーマンス関連の情報収集 Remote SQLの確認 Visual Explain ダイナミック・エクスプレイン（dynexpln) データベース処理情報 スナップショット・モニター get snapshot for all remote_applications SQL Statement: select a.id, a.name, b.deptname from xstaff a, org b where a.id > 200 Estimated Cost = 51 Estimated Cardinality = 280 Distributed Subquery #1 | #Columns = 2 Nested Loop Join | Access Table Name = MOTOKI.ORG ID = 2,2 | | #Columns = 1 | | Relation Scan | | | Prefetch: Eligible | | Lock Intents | | | Table: Intent Share | | | Row : Next Key Share | | Return Data to Application | | | #Columns = 3 Return Data Completion Distributed Subquery #1: Server: MOTOKIXUDBV7 (DB2/6000 7.1.0) Subquery SQL Statement: SELECT A0."ID", A0."NAME" FROM "UDBV7"."STAFF" A0 WHERE (200 < A0."ID") Nicknames Referenced: MOTOKI.XSTAFF ID = 33 Base = UDBV7.STAFF #Output Columns = 2 End of section (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-9 連合データベースのSQLコンパイラー パフォーマンス関連の情報収集 Remote SQLの確認はパフォーマンス・チューニングをする上で非常に大切です。 確認にはExplainツールが使用できます。 確認 のポイントは適切にpushdownが選択されている事と、ジョインが効率よく行われているかです。 Visual Explain ニックネームからの読み込みは"RSCAN"というV7からの新しい演算子によってあらわされています。 この演算子の入力 引数のリモート照会テキストとしてRemote SQLを確認することができます。 ダイナミック・エクスプレイン（dynexpln) 上記の図はdynexplnの例です。 ニックネームへのアクセスが含まれる場合には'-r'オプションを指定する必要がありま す。 上記の図は次のコマンドにより取得されたものです。dynexpln -d sample -r -q "select a.id, a.name, b.deptname from xstaff a, org b where a.id > 200" ニックネームへのアクセスは”Distributed Subquery #1”のであらわされています。 データベース処理情報 連合データベースの情報は、一部、スナップショット・モニターで取得することが可能です。 get snapshot for all remote_applications (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 103-104 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 解説： 1-9 連合データベースのSQLコンパイラー (参考)パフォーマンスチューンング例 varchar_no_trailing_blank 省略時はN OracleのVARCHAR,VARCHAR2はデータの末尾にブランクが入っているとデータ長に含めデータとして解釈する。 例）C1='ABC '(4bytes)のデータがある場合 C1='ABC' ---> not found ORACLE C2='ABC' ---> found DB2 SQLステートメント ： SELECT * FROM STAFFORA8 WHERE NAME='Smiths' varchar_no_trailing_blank='N'の場合 SELECT A0."ID", A0."NAME", A0."DEPT", A0."JOB", A0."YEARS", A0."SALARY", A0."COMM" FROM "SCOTT"."STAFF" A0 WHERE (RTRIM(A0."NAME") = 'Smiths') <===RTRIM関数が使用され、索引が使用されない varchar_no_trailing_blank='Y'の場合 SELECT A0."ID", A0."NAME", A0."DEPT", A0."JOB", A0."YEARS", A0."SALARY", A0."COMM" FROM "SCOTT"."STAFF" A0 WHERE (A0."NAME" = 'Smiths') (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 1-9 連合データベースのSQLコンパイラー (参考)パフォーマンスチューンング例 Collating_Sequence 省略時はN 照合順序が違う場合、キャラクター・タイプ列へのMAX(),MIN(),'>','<'、ORDER BY等の大小比較が必要なものがpushdownされない SQLステートメント ： select MAX(name) from STAFFORA8 Collating_Sequence='N'の場合 SELECT A0."NAME" FROM "SCOTT"."STAFF" A0 WHERE (A0."NAME" IS NOT NULL) <===MAX関数が使用されないため、Name列を全件を取得する Collating_Sequence='Y’の場合 SELECT MAX( A0."NAME") FROM "SCOTT"."STAFF" A0 COLSEQを Y に設定できるケース リモート・データソースがOracleの場合 UDBのCOLLATEの指定がIDENTITYであり、CODESETおよびTERRITIRYの指定がOracleのコードページと一致している場合 リモート・データソースがPC&Unix系のUDBの場合 UDBのCOLLATEの指定、CODESETおよびTERRITIRYの指定が一致している場合 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 105-106 ) ＤＢ２ ＵＤＢ（PC&Unix)デザイン・ガイド アクセス経路の選択 用語対応表 本書での用語 英語 日本語マニュアルでの表記 非一様分布統計 Distribution Statistics 分布統計 オプティマイザー optimizer 最適化プログラム スキャン scan 走査 索引ORing Index ORing 索引OR操作または索引OR結合 索引ANDing Index ANDing 索引AND操作または索引AND結合 動的ビットマップANDing dynamic bitmap ANDing 動的ビットマップAND結合 先読み prefetch 事前取り出し サマリー表 Summary Table 要約表 ジョイン JOIN 結合 ネステッド・ループ・ジョイン Nested Loop Join ネストされたループ結合 ソート sort 分類 オーバー・フロー overflow 桁あふれ ユニーク索引 unique index 固有索引 プッシュダウン Push Down 後入れ先出し 並列表スキャン parallel relational scan 並列リレーショナル走査 並列索引スキャン parallel index scan 並列索引走査 索引探索引数述語 Index SARGable Predicates 索引検索引数述語 データ検索引数述語 Data SARGable Predicates データ探索引数述語 複写サマリー表 replicated summary table 複製要約表/複写要約表 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 用語対応表 本書での用語 英語 日本語マニュアルでの表記 反転表/反転サマリー表 Pushdown分析 Reversal table/ Repartitioned table Pushdown Analisis 後入れ先出し分析 連合データベース Federated Database 連合データベース リモートSQL生成 Remote SQL Generation リモートSQL生成 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第４章( 107-108 )