Comments
Transcript
簡単シリーズ インデックス・アドバイザー 2001/09 SS&WSCC#1
簡単シリーズ インデックス・アドバイザー 2001/09 SS&WSCC#1 1 簡単シリーズ紹介 「簡単シリーズ」は、初めてDB2 UDBを使用する皆様が、マニュアルを見る前に、まず簡単に目を通すことにより大雑把な理解を 得ていただくことを考えて作成しています。それぞれの項目は１５－３０分程度で読み終える量となっていますので、マニュアルを 最初から読むのはなかなか「手間がかかって大変だ」という方は、まず「簡単シリーズ」からお初めください。 「簡単シリーズ」は、システム＆ウェブソリューションセンター 第一ソリューション推進の データマネージメント グループのメン バー５名の分担で作成しています。 下記に担当メンバーの氏名、メールアドレスを記載いたしますので、資料についてのご質問、ご指摘がありましたら下記担当者 までお願いいたします。 「簡単シリーズ」は､順次下記ウェブサイト「DB2 Developer Domain」にて、「カンタン!DB2テクテク第1歩」として掲載しております ので､ご活用ください。また「DB2 Developer Domain」には DB2 UDB についての有益な情報が掲載されていきますので､ぜひ一 度アクセスください。 http://www.ibm.com/jp/software/data/developer/ 簡単シリーズ 執筆者： 堀部直子 [email protected] 長谷川 久 [email protected] 出羽 奏太郎 [email protected] 近藤 靖志 [email protected] 清水 詳士 [email protected] 関 弥生 [email protected] Systems Solution & Web Server Competence Center No.1 DM Group 2 目次 １．インデックス・アドバイザーとは？ １－１．インデックス・アドバイザーの概要 １－２．ワークロード情報 １－３．仮想索引 １－４．データベース情報 ２．インデックス・アドバイザーの起動方法 ３．コントロール・センターによるインデックス・アドバイザーの起動 ３－１．概要 ３－２．ワークロード ３－３．制限 ３－４．計算 ３－５．推奨 ３－６．未使用 ３－７．要約 ４．まとめ ４－１．使用上の考慮点 ４－２．まとめ ５．<参考>コマンドによるインデックス・アドバイザーの実行 Systems Solution & Web Server Competence Center No.1 DM Group 3 １．インデックス・アドバイザーとは？ データベースに適した索引を設計・定義する作業を軽減するためのツールです。 問題のある照会に最適な索引を提示します。 ワークロード・コストの最小化を目指した推奨索引を提示します。 限られた資源内に作成可能で最適な索引を提示します。 未定義の索引を推奨索引の検討対象にすることができます。 Systems Solution & Web Server Competence Center No.1 DM Group 4 １．インデックス・アドバイザーとは？ データベースに適した索引を設計・定義する作業を軽減するためのツールです。 照会・挿入・削除・更新とデータベースに対しては様々なデータ操作が入ります。これらの中から処理の重要性・頻度等を 考慮してバランスよく（索引の数を増やすと、多様な照会のパフォーマンス向上を図ることができますが、削除・挿入処理 に負荷をかけることになります）最適な索引を設計することは重要であり、データベースに対する知識とある程度の作業 時間が必要となります。インデックス・アドバイザーは与えられた情報を元に既存の表に対して、最適な索引を提示するこ とにより、索引の設計・定義作業を軽減します。 問題のある照会に最適な索引を提示します。 問題のあるSQL文をインデックス・アドバイザーに入力するとデータベース情報を元に最適な索引を提示します。DB2が選 択するであろうアクセス・パスを予測することが難しい複雑なSQL文に対しては特に威力を発揮します。 ワークロード・コストの最小化を目指した推奨索引を提示します。 ワークロード情報とデータベース情報を元に、ワークロード・コストの最小化を目指した推奨索引を示します。特定のSQL に最適な索引を提示するばかりではなく、該当の表に対する様々なデータ操作をワークロードとして考慮し、全体的にか かるコストの最小化を目指した索引を提示する機能を持っています。 限られた資源内に作成可能で最適な索引を提示します。 索引をアロケーションするスペースが制限されている環境においても、その範囲内でワークロードに最適な索引を提示す ることができます。 未定義の索引を推奨索引の検討対象にすることができます。 索引を実際に定義せずに推奨索引の検討対象にすることができます。もしこの機能がなければ、DBA（データベース・ア ドミニストレータ）は様々なアクセス・パスを予測し、検討対象に加えたい索引を実際に作成しなければなりません。これに は多くの作業と時間とディスク・スペースが必要となります。索引を実際に作成せずに、インデックス・アドバイザーの検討 対象に加えることができるこの機能によって、多くの作業負荷を軽減することができます。 Systems Solution & Web Server Competence Center No.1 DM Group 5 １－１．インデックス・アドバイザーの概要 SQL 仮想索引 ワークロード 情報 お奨め索引 インデックス・ アドバイザー 既存索引 データベース 情報 お奨め索引 Systems Solution & Web Server Competence Center No.1 DM Group 6 １－１．インデックス・アドバイザーの概要 インデックス・アドバイザーではワークロードと仮想索引という２つの概念が使われます。 ワークロードとは？ データベース・マネージャーが処理しなければならないSQLステートメントの集合です。 ワークロードはSQLステートメントとその頻度によって表されます。 ワークロードで設定されるSQLステートメントはSELECTの他、INSERT、UPDATE、DELETEなどです。 ワークロードの最小化がインデックス・アドバイザーの目標となります。 仮想索引とは？ 実際のデータベースには定義されていない索引です。 ２種類の仮想索引があります。 インデックス・アドバイザーが推奨索引として示した未定義の索引 ユーザーがインデックス・アドバイザーの検討対象に加えたい未定義の索引 インデックス・アドバイザーはワークロード情報とデータベース情報から最適な索引を提示します。 未定義の索引を推奨する場合は作成すべき索引として提示されます。 既にある索引を推奨する場合は保持、ワークロードで使用されない索引に対しては削除対象として提示します。 Systems Solution & Web Server Competence Center No.1 DM Group 7 １－２．ワークロード情報 ワークロード情報は以下の方法で収集されます。 ユーザー SQL EXPLAIN表 仮想索引 ワークロード 情報 お奨め索引 ADVISE_WORKLOAD 表 SQL ステートメント・ キャッシュ インデックス・ アドバイザー 既存索引 データベース 情報 Systems Solution & Web Server Competence Center No.1 DM Group 8 お奨め索引 １－２．ワークロード情報 ワークロード情報は以下の方法で収集されます。 ユーザーによるSQL文入力 インデックス・ウィザードから入力できます。 db2ａｄｖｉｓコマンドのパラメーターとして入力できます。 直接ADVISE_WORKLOAD表に挿入・ロードすることができます。 静的SQL文はシステム・カタログ(SYSIBM.SYSSTMT)から取得することもできます。 クエリー・パトローラーからSQL文を取得することもできます。 EXPLAIN表 インデックス・ウィザードからインポートすることができます。 SQLステートメント・キャッシュ インデックス・ウィザードからインポートすることができます。 インデックス・アドバイザーはインデックス・ウィザード起動時に自動的にGET SNAPSHOTコマンドを発行して SQLステートント・キャッシュの内容をADVISE_WORKLOAD表に書き出します。そのSQLはインポート対象とし て表示されますので、ワークロードに追加することができます。 ワークロード情報はADVISE_WORKLOAD表に蓄積されます。 ADVISE_WORKLOAD表の作成 \SQLLIB\MISCにあるEXPLAIN.DDLファイルを実行します。 インデックス・ウィザードを実行した時に未定義の場合には自動的に作成します。 ワークロードの変更 蓄積されたワークロードはインデックス・ウィザードから簡単に修正できます。 Systems Solution & Web Server Competence Center No.1 DM Group 9 １－３．仮想索引 ユーザー SQL 仮想索引 ワークロード 情報 ADVISE_WORKLOAD 表 お奨め索引 インデックス・ アドバイザー 既存索引 お奨め索引 データベース 情報 Systems Solution & Web Server Competence Center No.1 DM Group 10 ADVISE_INDEX 表 １－３．仮想索引 仮想索引には２つの種類があります。１つは、ユーザーがインデックス・アドバイザーの検討対象に加えた い未定義の索引です。もう１つはインデックス・アドバイザーが検討の結果、推奨索引として提示した未定義 の索引です。それぞれ、以下の方法で作成されます。 検討対象に加えたい仮想索引 ユーザーによる作成 検討対象にしたい索引の構成をADVISE_INDEX表に挿入します。 検討の結果、推奨索引として提示した未定義の索引 インデックス・アドバイザーによる作成 インデックス・アドバイザーが見積りの結果、未定義の索引を推奨したときに作成されます。 仮想索引情報はADVISE_INDEX表に蓄積されます。 ADVISE_INDEX表の作成 \SQLLIB\MISCにあるEXPLAIN.DDLファイルを実行します。 インデックス・ウィザードを実行した時に未定義の場合には自動的に作成します。 Systems Solution & Web Server Competence Center No.1 DM Group 11 １－４．データベース情報 SQL ユーザー データベース ワークロード 情報 仮想索引 お奨め索引 RUNSTATS ユーティリティー インデックス・ アドバイザー ADVISE_WORKLOAD 表 既存索引 お奨め索引 DB2カタログ表 データベース 情報 システム構成 Systems Solution & Web Server Competence Center No.1 DM Group 12 ADVISE_INDEX 表 １－４．データベース情報 データベース情報はDB2カタログの情報とシステム構成パラメータから取得します。 DB2カタログの情報 インデックス・アドバイザーは索引を検討する際に必要な情報を、対象データベースに直接アクセスして 収集するわけではありません。DB2カタログから統計情報を取得します。 インデックス・アドバイザーが最適な索引を検討するためには、データベースの状態をDB2カタログに 反映しておく必要があります。 データベースの状態をDB2カタログに反映するためにはRUNSTATSユーティリティーを実行します。 システム構成パラメータ システム構成パラメータのBUFFPAGESやCPUSPEEDの値やノード数は、インデックス・アドバイザーが 推奨索引を検討する際に考慮されます。 Systems Solution & Web Server Competence Center No.1 DM Group 13 ２．インデックス・アドバイザーの起動方法 インデックス・アドバイザーを起動するには以下の４つの方法があります コントロール・センター コマンド EXPLAIN特殊レジスター CLI Systems Solution & Web Server Competence Center No.1 DM Group 14 ２．インデックス・アドバイザーの起動方法 インデックス・アドバイザーは以下の４つの方法で使用できます。 コントロール・センター これがお奨めの方法です。 各種パラメータをウィザード形式で入力できるので、インデックス・アドバイザーを簡単に使うことができます。 コマンド CLPコマンドラインからdb2advisと入力します。 インデックス・アドバイザーは起動時に以下の3箇所のいずれかからワークロードの情報を入手します。 コマンドライン テキスト・ファイル ADVISE_WORKLOADテーブル SQLコンパイラー(EXPLAIN特殊レジスター) SET CURRENT EXPLAIN MODE RECOMMENDED INDEXES RECOMMENDED INDEXESによる指定 SQLコンパイラーが索引を推奨できるようにします。このEXPLAINモードで実行される照会はすべて、推奨さ れた索引をADVISE_INDEX表に書き出します。さらに、推奨された索引を使用する方法を示すためにEXPLAIN 表に情報が入りますが、そのステートメントのコンパイルや実行は行われません。 SET CURRENT EXPLAIN MODE EVALUATE INDEXESによる指定 SQLコンパイラーが索引を評価できるようにします。評価される索引はADVISE_INDEX表から読み込まれ、 EVALUATE=Yというマークが付けられます。最適化プログラムはカタログの値に基づく仮想索引を生成します。こ のEXPLAINモードで実行される照会は全て、仮想索引に基づいて見積もられた統計を使用してコンパイルされ、 最適化されます。ステートメントは実行されません。 CLI（Call Level Interface) CLIインターフェースを使用したアプリケーションを作成することにより、プログラムの中からインデックス・アドバイザーを 使用することも可能です。 Systems Solution & Web Server Competence Center No.1 DM Group 15 ３．コントロール・センターによるインデックス・アドバイザーの起動 コントロール・センターを起動します。 コントロール・センターのオブジェクト・ツリー を展開して索引フォルダーを出します。 索引フォルダーをマウスで右クリックし、 「作成」→「ウィザード」を選択します。 索引ウィザードが開きます。 これがインデックス・アドバイザーのウィンドウ になります。 Systems Solution & Web Server Competence Center No.1 DM Group 16 ３－１．概要 操作対象のデータベースに接続され ていることを確認します。 以下、ステップを順次実行していきま す。 Systems Solution & Web Server Competence Center No.1 DM Group 17 ３－２－１．ワークロード 新規にワークロードを定義する場合には 「ワークロードの作成」を選択します。 ワークロード名を入力し、「インポート」あ るいは「追加」からSQL文と頻度を選択・ 入力します。 ここで入力した頻度をインデックス・アド バイザーは相対加重として計算に使用し ますので、重要なSQL文については頻度 を高くします。 作成されたワークロードは ADVISE_WORKLOAD表に格納されます。 定義済みのワークロードを編集する場合 には「最適化するワークロード」から該当 のワークロードを選択し「ワークロードの 変更」を選びます。 Systems Solution & Web Server Competence Center No.1 DM Group 18 ３－２－２．ワークロード 「インポート」ウィンドウから ADVISE_WORKLOAD表の内容(「最新の SQLステートメント」)またはEXPLAIN表 内のSQL文を分析対象としてワｰクロー ドに加えることができます。 入力後、「了解」ではなく「適用」をクリッ クするとウィンドウが閉じずに続けて入 力することができます。 Systems Solution & Web Server Competence Center No.1 DM Group 19 ３－３．制限 索引をアロケーションするディスク・スペースに制約がある場合には入力します。 インデックス・アドバイザーは分析の結果、推奨する索引のアロケーション・サイズがここで指定した値よりも大きい場合には、推 奨索引として提示しませんので、余裕がある場合には指定しないことをおすすめします。また、ディスク・スペースに制約がある 場合でも、インデックス・アドバイザーが推奨する索引には必要な大きさも表示されますので、実際に索引を作成するかどうかの 判断はその時に可能です。 Systems Solution & Web Server Competence Center No.1 DM Group 20 ３－４．計算 インデックス・アドバイザーがコストを見積 り、最適な索引を分析するための実行時 間と、実行タイミングを指定します。 実行タイミングの指定 「今」か「後で開始する」を指定できます。 「後で開始する」を選択した場合は「完了」 をクリックするとタイマーの設定をするウィ ンドウが出てきます。 実行時間 インデックス・アドバイザーの実行時間に 制限を与えることができます。分析時間に 制限を与えない方がより最適な索引を提 示することができますが、システムの運用 時間、テスト・スケジュールの関係から、 終了時間がはっきりしていた方がいい場 合もあります。 Systems Solution & Web Server Competence Center No.1 DM Group 21 ３－５．推奨 インデックス・アドバイザーが推奨 する索引を表示します。 索引の列構成やデータの昇順 （＋）、降順( - ）、索引のアロケー ション・サイズが表示されます。 推奨索引を使用した場合としない 場合とのワークロードにかかる実 行時間が抽象的な測定単位で表 示されます。 詳細ウィンドウでは、個々のSQL 文についての比較をみることがで きます。 DBAは、アロケーション・サイズや 作成される索引の数、パフォーマ ンスの向上がどの程度見込める か等の情報から、実際に作成す るか否かを判断します。作成しな い場合には、索引の作成欄の チェックをはずしておきます。 Systems Solution & Web Server Competence Center No.1 DM Group 22 ３－６．未使用 既存の索引のなかで、検討対象の ワークロードでは使用されない索引が 表示されます。 ここで表示される索引は検討対象の ワークロード以外のところで使用され ている可能性があるので、削除対象に するか否かの判断には注意が必要で す。 対象索引がない場合はこのウィンドウ は表示されません。 Systems Solution & Web Server Competence Center No.1 DM Group 23 ３－７．要約 インデックス・アドバイザーが提示 した索引をユーザーが選択したオ ペレーション（作成・保持・削除）毎 にまとめて表示します。 この一覧に従って、索引の作成と 統計情報の取得あるいは索引の 削除を行うDDLのスクリプトが作成 されます。 Systems Solution & Web Server Competence Center No.1 DM Group 24 ４－１．使用上の考慮点 クラスター索引については。。。 インデックス・アドバイザーはワークロードに適した索引を提示してくれます。しかしながら、全ての索引の設計をインデックス・アド バイザーは担うことはできません。インデックス・アドバイザーは既存の表情報を使用して索引を検討します。そこには既にデータ が設計した順に並んでいますので、クラスター索引の設計はDBAがしておく必要があります。 索引の数に注意 多様な照会のパフォーマンス向上を求めると推奨索引の数は多くなりがちです。予め表あたりに作成する索引数の基準を設け て、運用・保守作業が煩雑にならないようにすることも重要です。 索引作成、最後の判断は。。。 SQL文によっては索引経由のアクセスではなく、直接データをスキャンした方が効率がよい場合もあります。例えば、"SELECT * FROM テーブル名"に対してインデックス・アドバイザーを実行したところ、「DBA1352 索引ウィザードがワークロードのパフォーマ ンスを改善する索引を推奨できませんでした」が返されました。しかしながら、別のケースでは、表の列構成に近い数の列構成か ら成る索引を推奨する場合もあります。インデックス・アドバイザーが推奨する索引を実際に定義するか否かの判断をDBAには求 められています。 他に影響を与えないために。。。 インデックス・アドバイザーが最適な索引の分析にかかる負荷を考慮する必要があります。システムに影響を与えないために、別 マシンのDB2でインデックス・アドバイザーを使用することも考えられます。この時には、データベースのカタログ統計をコピーする 機能をもつdb2lookツールを使用します。db2lookツールは対象データベースに対して実行することにより、別マシン上のデータ ベースのカタログ統計を対象データベースのものと一致させるのに必要な更新ステートメントを生成します。これを別マシン上の データベースで実行し、そこでインデックス・アドバイザーを実行します。 db2lookコマンド例： db2look -d データベース名 -m -f -o アウトプットファイル -m：カタログ統計情報を反映するためのUPDATE文の生成 -f：データベースおよびデータベース・マネージャー構成パラメータを反映するためのUPDATE文の生成 Systems Solution & Web Server Competence Center No.1 DM Group 25 ４－２．まとめ インデックス・アドバイザーは有用なツール 複雑なSQL文のアクセス・パスを予測し、最適な索引を設計するためには、熟練したスキルと忍耐力が 必要です。これを自動的に算出するインデックス・アドバイザーはたいへん便利な機能です。インデック ス・アドバイザーに索引設計の全てを任せることはできませんが、DBAの片腕の役割を十分に果たす ツールです。前述の考慮点を念頭におきながら、大いに利用してみてはいかがでしょうか。 Systems Solution & Web Server Competence Center No.1 DM Group 26 ５－１．<参考> コマンドによるインデックス・アドバイザーの実行 コマンドによる起動 コマンド構文 >>-db2advis----d--database-name----+--------------------+-------> +--w--workload-name--+ +--s--"statement" ----+ '--i--filename---------' >-----+---------------------------+---+-----------------+-------> '--a--userid--+----------+----' '--l--disk-limit------' '-/passwd--' >-----+----------------------+---+-----+----------------------->< '--t--max-advise-time----' '--h--' Systems Solution & Web Server Competence Center No.1 DM Group 27 ５－２．<参考> コマンドによるインデックス・アドバイザーの実行 インデックス・ウィザードで実行した内容をコマンドラインから実行することができます。 コマンド・パラメーター -d database-name ： 接続の確立先のデータベースの名前を指定します。 -w workload-name ： 索引がアドバイスされるワークロードの名前を指定します。 この名前はADVISE_WORKLOAD 表で使用されます。 -s "statement" ： 索引がアドバイスされる単一の SQL ステートメントのテキストを指定します。 ステートメントは必ず二重引用符で囲んでください。 -i filename ： 1つ以上の SQL ステートメントが入っている入力ファイルの名前を指定します。省略時は標準入力です。 注釈テキストは、 各行の先頭に 2 つのハイフンを付けて -- <注釈> で表します。ステートメントは必ずセミコロンで区切ってください。 ワークロード中の各ステートメントが実行される頻度は、 次の行を入力ファイルに挿入することによって変更できます。 --#SET FREQUENCY <x> 頻度は、ファイル中何回でも更新できます。 -a userid/passwd ： データベースへの接続に使用する名前およびパスワード。 パスワードが指定される場合、斜線 (/) を含めなければなりません。 -l disk-limit ： 既存のスキーマですべての索引に使用可能な最大 MB を指定します。 省略時値は、区画(64GB) ごとの索引の最大サイズにおける、 データベース・マネージャー限度です。 -t max-advise-time ： 最大許可時間 (分) を指定し、操作を完了します。 省略時値は 10 です。 無制限の時間は、ゼロの値によって指定されます。 -h ： ヘルプ情報を表示します。 このオプションを指定すると、他のすべてのオプションは無視され、ヘルプ情報だけが表示されます。 実行例：コマンドラインからの入力 db2advis -d sample -i sample.sql -t 0 SAMPLE.SQLファイル --#SET FREQUENCY 100 select count(*) from sales where region = ?; --#SET FREQUENCY 3 select projno, sum(comm) tot_comm from employee, emp_act where employee.empno = emp_act.empno and employee.job='DESIGNER' group by projno order by tot_comm desc; --#SET FREQUENCY 50 select * from sales where sales_date = ?; Systems Solution & Web Server Competence Center No.1 DM Group 28 ５－３．<参考> コマンドによるインデックス・アドバイザーの実行 結果 Calculating initial cost (without recommmended indexes) [62.331280] timerons Initial set of proposed indexes is ready. Found maximum set of [2] recommended indexes Cost of workload with all indexes included [29.795755] timerons cost without index [0] is [58.816662] timerons. Derived benefit is [29.020907] cost without index [1] is [33.310373] timerons. Derived benefit is [3.514618] total disk space needed for initial set [2] MB total disk space constrained to [-1] MB 2 indexes in current solution [62.331280] timerons (without indexes) [29.795755] timerons (with current solution) [%52.20] improvement 推奨索引を使うこと による改善率 Trying variations of the solution set. Time elapsed. LIST OF RECOMMENDED INDEXES =========================== index[1], 1MB CREATE INDEX WIZ119 ON VALENTIN.SALES (SALES_DATE DESC) 推奨索引 index[2], 1MB CREATE INDEX WIZ63 ON VALENTIN.SALES (REGION DESC) =========================== Index Advisor tool is finished. Systems Solution & Web Server Competence Center No.1 DM Group 29