Comments
Description
Transcript
後半 - IBM
<第1.00版 2009年 7月> 第6章 パフォーマンス機能強化(後半) 本書に含まれている情報は、正式なIBMのテストを受けていません。また、明記にしろ、暗黙的にしろ、なんらの保証もなしに配布されるものです。 この情報の使用またはこれらの技術の実施は、いずれも、使用先の責任において行われるべきものであり、それらを評価し、実際に使用する環境に統合する 使用先の判断に依存しています。それぞれの項目は、ある特定の状態において正確であることがIBMによって調べられていますが、他のところで同じまたは同 様の結果が得られる保証はありません。これらの技術を自身の環境に適用することを試みる使用先は、自己の責任において行う必要があります。 © Copyright IBM Japan Systems Engineering Co., Ltd. 2009 DB2 V9.7のパフォーマンス機能強化 • オプティマイザー機能強化 オプティマイザー機能強化 • アクセス・プランの再利用 • 最適化プロファイルの適用の容易化 • OLTPの性能向上 - Statement Concentrator • 統計ビュー取得性能の向上 • データ・ データ・アクセスと アクセスとデータ並行性向上 データ並行性向上 • スキャン効率の向上 - Scan Sharing • 同時実行性の向上 - Currently Committed • パーティション表のパーティション索引 • インラインLOB格納 © 2009 ISE Corporation 内容 • スキャン効率の向上 - Scan Sharing 設定方法 デフォルトの設定 効果のあるケース 複数の表スキャンが同時実行 • OLTPの性能向上 - Statement Concentrator 設定方法 DB構成パラメータ、アプリでの指定 効果のあるケース リテラルを使用した動的SQLの多用 • 同時実行性の向上 - Currently Committed 設定方法 DB構成パラメータ、BINDオプション 効果のあるケース CSの読み取り処理と更新処理の混在 • 統計ビュー取得性能の向上 設定方法 RunstatsのTABLESAMPLE SYSTEMオプション 効果のあるケース 大規模表をJoinしている統計ビューのRunstats © 2009 ISE Corporation ブランク・ ブランク・ページ © 2009 ISE Corporation スキャン効率の向上 -- Scan Scan Sharing Sharing スキャン効率の向上 © 2009 ISE Corporation スキャン効率の向上 - Scan Sharing • 効果が見込めるケース • 複数のセッションから同じ表への表スキャン • 複数のセッションから同じMDC表へのブロック索引スキャン I/Oの I/Oの効率化 • スキャン・シェアリングの動き • 常に先頭からスキャンを開始するのではなく、他のアプリによる現在のスキャンの位 置に応じて、スキャンの開始位置を変更する • • • 他のクエリーによってBufferPool上に読み込まれたページをうまく活用し、同じディスク読み取りが何度も発 生しないようにする I/Oの量を大幅に削減し、クエリーの応答時間、スループットともに向上 スキャン開始位置が変化するため、同じSQLでもアンサーセットの順序は変化する • スキャン・シェアリングの設定 • 特別な設定は不要 • 有効な処理をDB2が自動的に判断するため、特別な設定は不要 © 2009 ISE Corporation 今までの表スキャンの動き HJ HJ NLJ … NLJ Tscan LINEITEM … Tscan CUSTOMER Tscan ORDERS APL 1 表スキャン process 照会 開始 A バッファプール ディスク上 ディスク上のデータページ © 2009 ISE Corporation 今までの表スキャンの動き HJ HJ NLJ … NLJ Tscan CUSTOMER … 照会 開始 表スキャン process A 1 5 B 2 6 3 Tscan CUSTOMER Tscan ORDERS APL 2 表スキャン process 4 バッファプール A A 1 2 3 4 5 6 ディスク上 ディスク上のデータページ © 2009 ISE Corporation 今までの表スキャンの動き AがReadしたページを Bが再度Readしている → 余計なI/O、スローダウン 表スキャン process A 2 1 5 B 8 2 6 3 1 表スキャン process 4 7 バッファプール A B 1 2 3 4 5 6 7 8 ディスク上 ディスク上のデータページ © 2009 ISE Corporation スキャン・シェアリングの動き 表スキャン コーディネータ scan A loc start 1 1 6 A 1 5 6 3 4 バッファプール A 1 2 3 4 5 6 ディスク上 ディスク上のデータページ © 2009 ISE Corporation スキャン・シェアリングの動き 表スキャン コーディネータ scan loc start A 12 1 6 1 B 12 6 6 AがReadしたページを Bは再利用する I/Oが約半分軽減 A B 9 5 1 10 6 11 3 12 4 バッファプール B A 1 2 3 4 5 6 7 8 9 10 11 12 Table pages stored on disk ディスク上 ディスク上のデータページ © 2009 ISE Corporation スキャン・シェアリングの動き 表スキャン コーディネータ scan loc start A 12 1 B 12 6 1 6 Bは、ページ1から 開始ページ前のページ5まで スキャン再開 A B 9 1 5 10 6 11 3 12 4 バッファプール B A 1 2 3 4 5 6 7 8 9 10 11 12 Table pages stored on disk ディスク上 ディスク上のデータページ © 2009 ISE Corporation スキャングループ • 各スキャンは スキャンはスキャングループに スキャングループに自動的に 自動的に割り当てられる • 同一グループ内でScan Sharing有効に機能するように自動制御 • Scannerのスピードに応じたグルーピング(WHERE条件の複雑さなど) • グループ内のScanner同士が離れないように速度を調整 (または再グルーピング) © 2009 ISE Corporation Notes: Explain出力 (抜粋) ( ( ( 2) Access Table Name = COBRA.LINEITEM ID = 7,4 | #Columns = 14 | Compressed Table | Skip Inserted Rows | Avoid Locking Committed Data | Currently Committed for Cursor Stability | May participate in Scan Sharing structures | Scan may start anywhere and wrap, for completion | Fast scan, for purposes of scan sharing management | Scan can be throttled in scan sharing management | Relation Scan | | | Lock Intents | | Table: Intent Share | | Row | Sargable Predicate(s) | | #Predicates = 14 2) | | Return Data to Application | | | Prefetch: Eligible : Next Key Share #Columns = 16 1) Return Data Completion © 2009 ISE Corporation スキャン・シェアリング効果確認 • 複数ユーザーが同じSQLを「時間差」で実行した際の処理速度の向上 • 実行SQL:select * from lineitem1 where L_ORDERKEY = 1 ユーザー数:5人 実行間隔:5秒 処理実行時間が 処理実行時間が 約半分に 約半分に改善! 改善! 結果 実行時間(SS有) 実行時間(SS無) User 1 0:41.70 1:19.50 User 2 0:43.00 1:06.14 User 3 0:42.99 1:10.60 User 4 0:43.60 1:15.59 User 5 0:43.70 1:10.50 単位→ Min:Sec.msec © 2009 ISE Corporation スキャン・シェアリング効果確認 • 複数ユーザーが同じSQLを「同時」に実行した際の処理速度の向上 • 実行SQL:select * from lineitem1 where L_ORDERKEY = 1 ユーザー数:5人 実行時間:5分 スループットは スループットは2倍、 レスポンスは レスポンスは約半分 に改善! 改善! 結果 実行時間(SS有) 実行時間(SS無) 実行回数 レスポンス 実行回数 レスポンス 40 0:41.244 20 1:22.584 単位→ Min:Sec.msec © 2009 ISE Corporation スキャン効率の向上 - Scan Sharing まとめ • スキャン・ スキャン・シェアリングの シェアリングの機能により 機能により、 により、複数の 複数の表スキャン、 スキャン、ブロッ ク索引スキャン 索引スキャンの スキャンのスキャンを スキャンを共有させ 共有させ、 させ、処理効率向上 • 重複した を削減させ 重複した余計 した余計な 余計なI/Oを 削減させ、 させ、スループット、 スループット、レスポンスタイム 向上 • 特別な 特別な設定は 設定は必要なく 必要なく、 なく、デフォルトで デフォルトで有効となる 有効となる機能 となる機能 © 2009 ISE Corporation ブランク・ ブランク・ページ © 2009 ISE Corporation OLTPの性能向上 -- Statement Statement Concentrator Concentrator OLTPの性能向上 © 2009 ISE Corporation OLTPの OLTPの性能向上 - Statement Concentrator DB2 V9.5以前 V9.5以前 – リテラル指定のステートメントをOLTP環境で利用すると、ステートメントのコ ンパイルに高いオーバーヘッドを発生していた • コンパイル済みのステートメントを再利用するためには、完全にステートメント一致 が必要 • 検索条件の値に関わらず同じアクセスプランが選択されるべきステートメントにつ いては、パラメーターマーカーの利用が強く推奨されていた ■リテラルの リテラルの例 String selectString = “SELECT COL1 FROM TABLE1 WHERE COL2=10 and COL3=’AAA’” Stmt.executeQuery(selectString); ■パラメーターマーカーの パラメーターマーカーの例 PreparedStatement selectVal = con.prepareStatement ( “SELECT COL1 FROM TABLE1 WHERE COL2=? and COL3=?”); selectVal.setInt(1, 10); selectVal.setString(2, “AAA”); selectVal.executeQuery(); © 2009 ISE Corporation Statement Concentratorの Concentratorの動き DB2 V9.7 • 任意でリテラルをパラメーターマーカーに置換 • section sharingの増加 および コンパイルの減少 • コンパイルステートメント数の減少 SELECT BALANCE WHERE ACCOUNT_ID = 12345 SELECT BALANCE WHERE ACCOUNT_ID = 11111 SELECT BALANCE WHERE ACCOUNT_ID = 54321 Compile SELECT BALANCE WHERE ACCOUNT_ID = 12121 SELECT BALANCE WHERE ACCOUNT_ID = ? Execute © 2009 ISE Corporation Statement Concentratorの Concentratorの特徴 • メリット • リテラルを使ったステートメントにおけるアクセスプランの共有 • 動的SQLのコンパイル要求時、リテラルをパラメーターマーカーに置き 換えて実行 • リテラル値以外はすべて同一というステートメント間で、コンパイル済 みステートメント再利用 • リテラルを使った動的SQLコンパイルのオーバーヘッド低減 • CPU消費量の削減、応答時間の短縮、スループットの向上 • デメリット • リテラルに適したアクセスパスとならない可能性 • 分散統計上の頻出値であっても、そうでなくても同じアクセスパス • HIGH2KEY、LOW2KEYに近くても、そうでなくても同じアクセスパス © 2009 ISE Corporation Statement Concentratorの Concentratorの設定方法 • STMT_CONC DB構成 構成パラメーター 構成パラメーター • OFF(デフォルト): V9.5までと同様の動作 • LITERALS: Statement Concentratorが有効 • アプリケーションからの アプリケーションからの指定 からの指定 • Java • statementConcentrator (Connection/DataSource プロパティ) • setDBStatementConcentrator (DB2Connectionのメソッド) • CLIや.NetもStatement Concentratorの指定可能 © 2009 ISE Corporation Statement Concentrator利用例 Concentrator利用例 Statement Concentratorの動作確認 – Select id from staff where id=10の形式のSQLを条件を変えながら5回実行後、Dynamic SQLの スナップショット取得 Number of executions Number of compilations Worst preparation time (ms) Best preparation time (ms) Internal rows deleted Internal rows inserted Rows read Internal rows updated Rows written Statement sorts Statement sort overflows Total sort time Buffer pool data logical reads Collected Buffer pool data physical reads Collected = = = = = = = = = = = = = 5 1 31 31 0 0 181 0 0 0 0 0 Not = Not リテラルを リテラルをパラ メーターマー カーに カーに置換 Cached Statementを を 再利用 Buffer pool temporary data logical reads = Not Collected Buffer pool temporary data physical reads = Not Collected Buffer pool index logical reads = Not Collected Buffer pool index physical reads = Not Collected Buffer pool temporary index logical reads = Not Collected Buffer pool temporary index physical reads = Not Collected Buffer pool xda logical reads = Not Collected Buffer pool xda physical reads = Not Collected Buffer pool temporary xda logical reads = Not Collected Buffer pool temporary xda physical reads = Not Collected Total execution time (sec.microsec)= 0.118564 Total user cpu time (sec.microsec) = 0.022547 Total system cpu time (sec.microsec)= 0.004048 Total statistic fabrication time (milliseconds) = 0 Total synchronous runstats time (milliseconds) = 0 Statement text = select id from staff where id=:L0 :L0 © 2009 ISE Corporation Statement Concentrator効果確認(その1) • リテラル使用ステートメントの繰り返しコンパイルを実行 connect to sample Database Connection Information Database server SQL authorization ID Local database alias = DB2/AIX64 9.7.0 = COBRA = SAMPLE create table t1 (a int, b int) DB20000I The SQL command completed successfully. 指定された 指定された回数 された回数だけ 回数だけ、 だけ、 select a,b from t1 where a=値 値 のコンパイルを コンパイルを繰り返すプロシージャー create procedure proc1(in x int) language sql begin declare cnt int default 0; -declare stmt char(50) default ''; -while cnt<x do set stmt = 'select a,b from t1 where a='||char(cnt a='||char(cnt); char(cnt); -prepare s1 from stmt; -set cnt = cnt + 1; -end while; -end DB20000I The SQL command completed successfully. © 2009 ISE Corporation Statement Concentrator効果確認(その1) Statement Concentrator = OFF update db cfg for sample using STMT_CONC off DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. !echo "call proc1(10000);" | db2batch -d sample -o p 3 * Timestamp: Mon Mar 09 2009 16:14:57 JST --------------------------------------------* SQL Statement Number 1: call proc1(10000); * Elapsed Time is: -- 省略 82.740136 seconds -- Total User CPU Time used by agent (s) Total System CPU Time used by agent (s) -- 省略 10000回 10000回の繰り返しを 指定して 指定して実行 して実行 = 78.192606 = 1.348098 -- © 2009 ISE Corporation Statement Concentrator効果確認(その1) Statement Concentrator = Literals Statement Concentratorを Concentratorを有効 update db cfg for sample using STMT_CONC literals にして再実行 にして再実行 DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. !echo "call proc1(10000);" | db2batch -d sample -o p 3 * Timestamp: Mon Mar 09 2009 16:22:47 JST --------------------------------------------* SQL Statement Number 1: call proc1(10000); * Elapsed Time is: -- 省略 6.920103 seconds -- Total User CPU Time used by agent (s) Total System CPU Time used by agent (s) -- 省略 応答時間、 応答時間、CPU時間 CPU時間 ともに大幅 ともに大幅に 大幅に改善! 改善! = 5.925858 = 0.639465 -- © 2009 ISE Corporation Statement Concentratorの効果確認(その2) Statement concentrator = OFF – 3つのStored Procedure (リテラル指定のステートメント繰り返し)を同時実行 – 高いCPUオーバーヘッド、スループットも低い © 2009 ISE Corporation Statement Concentratorの効果確認(その2) Statement concentrator = Literals – 同様のテストを実施 – CPUオーバーヘッド低く、スループットも大幅向上 © 2009 ISE Corporation Statement Concentratorの考慮点 以下のケースでは、Statement Concentratorは機能しない – Parameter Markerの含まれたステートメント – REOPT ALWAYSが指定された場合 Statement Concentratorで置き換えることのできるリテラルは、1ステートメン ト中の先頭の100,000個のみ (あとはリテラルのまま実行) © 2009 ISE Corporation OLTPの の性能向上 - Statement Concentrator まとめ • ステートメント・ ステートメント・コンセントレーターの コンセントレーターの機能により 機能により、 により、リテラルを リテラルを使っ た動的SQLステートメント ステートメントは 動的 ステートメントは、パラメーター・ パラメーター・マーカーを マーカーを使った形 った形 式に自動的に 自動的に置き換えられる • リテラルを ステートメントの リテラルを使った動的 った動的SQLステートメント 動的 ステートメントの繰り返し実行の 実行の負荷 が大幅に 大幅に削減、 削減、オンライン・ オンライン・トランザクション処理 トランザクション処理の 処理の性能が 性能が大幅 向上 • DB構成 構成パラメータ 、またはアプリケーション 構成パラメータ: パラメータ: STMT_CONC、 またはアプリケーションの アプリケーションの プロパティ、 プロパティ、メソッドにより メソッドにより設定可能 により設定可能 © 2009 ISE Corporation ブランク・ ブランク・ページ © 2009 ISE Corporation 並行性の向上 –– Currently Currently Committed Committed 並行性の向上 © 2009 ISE Corporation 並行性制御に関するこれまでの機能強化 • 並行性を ) 並行性を向上させる 向上させる3つの させる つのレジストリー つのレジストリー変数 レジストリー変数( 変数(V8.1/V8.2) • DB2_SKIPINSERTED、DB2_SKIPDELETED、DB2_EVALUNCOMMITTED • インスタンス単位。整合性が崩れるリスクあり。 • 楽観的ロック ) 楽観的ロック( ロック(V9.5) • 参照したデータを直ぐに更新しない場合、参照後にロックを一旦解放する方式 • DB2 V9.5では、RID_BIT()組み込み関数、ROW CHANGE TOKENが利用可能 • Lock Avoidance( (V9.5) ) • Xロックが保持されていても、CSでブロッキングのスキャンはロックウェイトしない • • • • ケース1 : WITH RS USE AND KEEP EXCLUSIVE LOCKS のXロック ケース2 : どの列を変更しなかったUPDATEのXロック ケース3 : 更新された列以外で構成された索引ONLYスキャン 詳細は TechnicalFlash:「DB2 for LUW V9.5のLock Avoidance機能によって、V9.1までは発生していた ロック競合が発生しなくなることがある」(2009-06-09)を参照 © 2009 ISE Corporation DB2 9.7での機能強化 • CS分離 分離レベル 分離レベルの レベルの機能拡張 新たな読み取り一貫性: の導入 一貫性:Currently Committedの • 読み取り一貫性の比較 • Currently Committedの挙動 • Currently Committedを有効に使うための設定 • 考慮点 © 2009 ISE Corporation DB2 (V9.5以前)の読み取り一貫性 • DB2は は最新の 最新のデータを データを読む • 参照処理は、更新処理がコミットされるまで待つ A TRN1 B UPDATE C C’ UPDATE COMMIT D D’ TRN2 TRN2は、TRN1が COMMITするまで WAIT SELECT E F 読むデータは データは常に最新だが 最新だが、 だが、参照処理が 参照処理がロック待機 ロック待機する 待機する可能性 する可能性あり 可能性あり © 2009 ISE Corporation Oracleの読み取り一貫性 (Read Committed) • マルチ・ マルチ・バージョン一貫性制御 バージョン一貫性制御 (Multi-Version Concurrency Control) • 更新処理により、更新前イメージが専用の領域へ書き出される • 参照処理はコミットを待たずに更新前イメージを読む • Select発行時点でコミット済みのデータを読む • 下記の例では、UPDATEがCOMMITされていたとしてもTRN2は更新前データを読む SCN=10 SCN=100 UNDOセグメント B TRN1 UPDATE A SCN=110 UPDATE SCN=98 TRN2 C C’ C D D’ D SCN=29 SELECT E F 読んだデータ んだデータが データが最新とは 最新とは限 とは限らないが、 らないが、参照処理は 参照処理はロック待機 ロック待機しない 待機しない © 2009 ISE Corporation DB2 9.7の読み取り一貫性(Currently Committed) • 未コミットの コミットの更新があっても 更新があってもロック があってもロック待機 ロック待機をしない 待機をしない • 参照処理は、更新処理に伴うロックの開放を待たず、更新前のデータ (コミット済みの最新データ)をログ(ログバッファー)から読む • 常に最新のコミット済みデータを読む A B トランザクションログ (ログバッファー) UPDATE C C’ C UPDATE D D’ D TRN1 TRN2 SELECT E F 参照処理は 参照処理はロック待機 ロック待機せず 待機せず、 せず、かつコミット かつコミット済 コミット済みの最新 みの最新データ 最新データを データを読む © 2009 ISE Corporation 同時実行性の向上: Currently Committed 別アプリから アプリから参照可能 から参照可能か 参照可能か 別アプリから アプリから更新可能 から更新可能か 更新可能か DB2 9.5以前 9.5以前の 以前の 参照中の 参照中の行を Yes Maybe CS 分離レベル 分離レベル 更新中の 更新中の行を No No 別アプリから アプリから参照可能 から参照可能か 参照可能か 別アプリから アプリから更新可能 から更新可能か 更新可能か Oracleの Oracleの 参照中の 参照中の行を Yes Yes Read Committed分離 Committed分離レベル 分離レベル 更新中の 更新中の行を Yes No 別アプリから アプリから参照可能 から参照可能か 参照可能か 別アプリから アプリから更新可能 から更新可能か 更新可能か DB2 9.7以降 9.7以降の 以降の 参照中の 参照中の行を Yes Yes CS分離 CS分離レベル 分離レベル w/CC 更新中の 更新中の行を Yes No Yes:参照 or 更新可 No :ロックウェイト 読み取り処理が 処理が書き込み処理を 処理を妨害しない 妨害しない (Readが (ReadがLockを Lockを取らない) らない) 書き込み処理が 処理が読み取り処理を 処理を妨害しない 妨害しない( しない(Readは ReadはLock行 Lock行をバイパス) バイパス) © 2009 ISE Corporation Currently Committedの挙動 Locklist emp rowid lock rowid empid name office salary 1 X(D) 1 4245 Jones Y2/11 11 3 X(U) 2 6354 Smith A1/21 43 5 X(I) 3 7836 Chan C3/46 21 4 1325 Tata X1/03 33 5 5456 Baum D2/18 22 Log Buffer log DEL: Emp,1,4245,Jones,Y2/11,11 Log Files - UPD: Emp,3,7836,Chan,D2/18 C3/46 INS: Emp,5,5456,Baum,D2/18 Log Archive (TSM) • 読み取り処理が 処理が書き込み処理を 処理をブロックしない ブロックしない INS:Emp,2,6354,Smith,A1/21, 43 INS:Emp,4,1325,Tata,X1/03,33 • CSの読み取り処理は、Commit済みデータにロックを取らない • 書き込み処理が 処理が読み取り処理を 処理をブロックしない ブロックしない • CSの読み取り処理は、最新のコミット済みデータをログから取得するため、書 き込み処理に伴う排他ロックの開放を待つ必要がない • 分離レベル 分離レベルCS レベルCSで CSで有効、 有効、RSでも RSでも未 でも未コミットINSERT コミットINSERTの INSERTのスキップは スキップは有効 © 2009 ISE Corporation Currently Committedの設定方法 • データベース構成パラメーター(CUR_COMMIT) • • • • パッケージ作成時の指定 (BIND コマンド) • • • ON :分離レベルCSはCurrently Committedとして動く(新規作成DBのデフォルト) AVAILABLE :アプリケーションから明示的に指定された場合にCurrently Committed有効 DISABLED :Currently Committed無効 (MigrationされたDBでのデフォルト) CONCURRENTACCESSRESOLUTIONオプションで指定 • USE CURRENTLY COMMITTED :CCが有効 • WAIT FOR OUTCOME :CCが無効 動的SQLであっても、使用するパッケージを明示的にBINDすることで制御可能 セッション単位の指定 • JDBC、CLI、.NET等のアプリ・インターフェースからセッション単位で指定可能 • CUR_COMMIT DB構成 構成パラメーター DB構成パラメーター BINDオプション BINDオプション セッションで セッションで指定 CC設定 設定の 設定の原則 • スコープが狭い設定パラ メータがより優先される • アクセスプラン決定時の設 定がセクションに書き込ま れ、有効になる。 • 静的SQLでは注意 © 2009 ISE Corporation おすすめの設定 • CCを使用したい場合 • 動的SQLでは 動的 では、 では、BLOCKING=ALL • 静的SQLでは では、 静的 では、STATICREADONLY=YES • 組み込みSQLでも でも動的 は存在し 時には両方指定 でも動的SQLは 動的 存在し得るため、 るため、BIND時 には両方指定すること 両方指定すること を推奨 • 静的SQLでは では、 の設定後に 静的 では、CUR_COMMIT=ONの 設定後に再バインドが バインドが必要 • CCを使用したくない場合 • CUR_COMMIT=DISABLEDに に設定 • 静的SQLで で、CUR_COMMIT=ONの の環境で したパッケージ 静的 環境でBINDした したパッケージがある パッケージがある場合 がある場合、 場合、 DISABLEDの の設定後に 設定後に忘れずに再 れずに再バインドする バインドする • 特定のアプリケーションのみCCを使用したくない場合 • 「CCを を使用したい 使用したい場合 したい場合」 場合」の設定に 設定に加えて、 えて、使用したくない 使用したくないアプリケーション したくないアプリケーションのみ アプリケーションのみ、 のみ、 CONCURRENTACCESSRESOLUTION=WAIT FOR OUTCOMEで でバインド © 2009 ISE Corporation 設定の 設定の確認方法 • CCの有効/無効はセクションに書き込まれるため、EXPLAINを 使用して設定内容を確認する。 • 動的SQL 動的SQLの SQLの場合 db2expln -d <DB name> -t -f <SQL file> • 静的SQL 静的SQLの SQLの場合 db2expln -d <DB name> -t -c <package schema> -p <package name> • CCが CCが有効な 有効な場合の 場合の出力例 Access Table Name = TUKIV97.T1 ID = 2,19 | #Columns = 1 | Skip Inserted Rows | Avoid Locking Committed Data | Currently Committed for Cursor Stability ... | Relation Scan | | Prefetch: Eligible | Lock Intents | | Table: Intent Share | | Row : Next Key Share ... © 2009 ISE Corporation Currently Committedが有効にならないケース • カタログ表のアクセス • RCT(Range Clustered Table)のアクセス • 参照制約の適用 • Long Varchar/Vargraphic列の処理(SELECT列、条件等)を含むアクセス • 表レベル/Blockレベルのロック競合 • MDC表からのCell Delete(MDCロールアウト)はロック競合する • Update/Deleteに伴う表アクセス • 読み取り専用、 専用、あいまいなカーソル あいまいなカーソル以外 カーソル以外の 以外のカーソルタイプ • 「未コミットInsert行のスキップ」はWrite処理対象表であっても有効 T1表 表 (索引無し 索引無し) ZZ AA 801 ZZ BB 802 CC ZZ X 803 DD 804 ZZ EE APL2 UPDATE T1 SET COL2=‘ COL2=‘ZZ’ ZZ’ X X 800 X APL1 INSERT VALUES (803,’ (803,’DD’ DD’) COL2 X COL1 © 2009 ISE Corporation どのような設定でCurrently Committedが有効となるか • 動的SQL • CUR_COMMIT DB構成 構成パラメーター で、ブロッキング設定 構成パラメーターが パラメーターがONで ブロッキング設定が 設定がデフォルト 読み取り専用カーソル 専用カーソルでは カーソルではCC有効 では 有効 あいまいな のみスキップ あいまいなカーソルでは カーソルでは未 では未コミットINSERTのみ コミット のみスキップ • CUR_COMMIT DB構成 構成パラメーター で、ブロッキング設定 構成パラメーターが パラメーターがONで ブロッキング設定が 設定がALL 読み取り専用カーソル 有効 専用カーソルとあいまいな カーソルとあいまいなカーソル とあいまいなカーソルの カーソルの両方で 両方でCC有効 • 静的SQL • DB移行後 移行後に に変更したが 移行後にCUR_COMMIT=ONに 変更したが、 したが、パッケージの パッケージの再バインド未実施 バインド未実施 未コミットINSERTのみ のみスキップ コミット のみスキップ • その後 その後、CONCURRENTACCESSRESOLUTION=USE CURRENTLY COMMITTEDで で再バインド実施 バインド実施 未コミットINSERTのみ のみスキップ コミット のみスキップ • さらにSTATICREADONLY=Yを を追加して さらに 追加して再 して再バインド実施 バインド実施 CC有効 有効( もスキップ) 有効(未コミットUpdate/Deleteも コミット スキップ) © 2009 ISE Corporation (参考)カーソルタイプ、パラメータによる動作の違い • 動的SQLの場合 DB CFG BINDオプション CCの有効/無効 Cursor type CUR_COMMIT CONCURRENTACCESSRESOLUTION BLOCKING Insert Upd/Del あいまい ON -/USE CURRENTLY COMMITTED B ○ ○ あいまい ON -/USE CURRENTLY COMMITTED N ○ × あいまい ON -/USE CURRENTLY COMMITTED U ○ × 読み込み専用 ON - 全て ○ ○ 更新を意図 ON - 全て ○ × すべて DISABLED - 全て × × すべて ON WAIT FOR OUTCOME 全て × × ○:参照 ×:ロックウェイト © 2009 ISE Corporation (参考)カーソルタイプ、パラメータによる動作の違い • 静的SQLの場合 Cursor type DB CFG CUR_COMMIT BINDオプション CONCURRENTACC ESSRESOLUTION BIND時の 設定 CCの有効/無効 STATICR EADONLY Insert Update Delete あいまい ON - ON Y ○ ○ あいまい ON - ON N ○ × 読み込み 専用 ON - ON N/Y ○ ○ 更新意図 ON - ON N/Y ○ × すべて ON WFO ON N/Y × × すべて すべて DISABLED ON - - ON DISABLED N/Y N/Y ○ × 備考 あいまいなカーソルでは、 STATICREADONLYD=YES でのみ未コミット UPDATE/DELATEがスキップ される × DISABLEDでも未コミット Insertがスキップされる。 BIND時には構成パラメー ターがONで、あとから DISABLEDになったケース × BIND時には構成パラメー ターがDISABLEDで、あと からONになったケース © 2009 ISE Corporation (参考)既存のレジストリ変数とCurrently Committedとの関係 • CSの のreadスキャン スキャンでは の挙動に スキャンでは、 では、CCの 挙動に包含される 包含される • そのため、レジストリー変数による挙動の変化無し • CSの のWriteスキャン スキャン、 のRead/Writeスキャン スキャンでは スキャン、RSの スキャンでは、 では、 • • Implicit CCの場合のみ有効となる(未コミットINSERTはCCに包含) WAIT FOR OUTCOMEの の設定では 設定では、 では、 • レジストリー変数の効果をパッケージ単位で抑止可能 BINDオプション CC暗黙・明示指定 CC暗黙指定 CC明示指定 CS Read Only CS Write CS Write RS Read/Write RS Read/Write CCに CCに含まれるため まれるため、 ため、 未コミットの コミットの挿入行 挿入行は スキップ レジストリー変数の設定 DB2_SKIPINSERTED CCに CCに含まれるため まれるため、 ため、 未コミットの コミットの挿入行 挿入行は スキップ CCに CCに含まれるため まれるため、 ため、 未コミットの コミットの挿入行 挿入行は スキップ DB2_SKIPDELETED スキップせずに スキップせずに未 せずに未コミッ トの削除行を 削除行を返す 未コミットの コミットの削除行 削除行を スキップ 最新の 最新のコミット済 コミット済みデー タを使って述部評価 って述部評価 未コミットの コミットのデータを データを 使って述部評価 って述部評価 DB2_EVALUNCOMMITTED Wait For Outcome CS/RS コミットを コミットを 待機 コミットを コミットを待機 コミットを コミットを 待機 コミットを コミットを待機 コミットを コミットを 待機 CC暗黙指定:BINDオプションの指定無しに、CUR_COMMIT構成パラメーターにより有効となったCC CC明示指定:BINDオプションで明示的にUSE CURRENTLY COMMITTEDを指定した © 2009 ISE Corporation 並行性の 並行性の向上 – Currently Committed まとめ • Currently Committedの の機能により 機能により、 により、最新の 最新の情報を 情報を提供しな 提供しな がらもアプリケーション がらもアプリケーションの アプリケーションの並行性が 並行性が向上 • 未コミットのINSERT行はスキップ • 未コミットのUPDATE行は更新前データを戻す • 未コミットのDELETE行は、参照対象 (未だDELETEされていない状態が最新の状態であるため) • 既存の 既存のログを ログをベースにした ベースにした仕組 にした仕組みにり 仕組みにり、 みにり、更新と 更新と参照が 参照が競合した 競合した 場合にのみ 場合にのみ、 ログへのアクセス 発生する。 オーバーヘッドが にのみ、ログへの へのアクセスが アクセスが発生する する。オーバーヘッドが 少なく、 なく、パフォーマンスに パフォーマンスに優れている • DB構成 構成パラメータ 、BINDオプション オプションで 構成パラメータ: パラメータ:CUR_COMMIT、 オプションで設定可 能。有効/無効 で確認 有効 無効は 無効はEXPLAINで © 2009 ISE Corporation ブランク・ ブランク・ページ © 2009 ISE Corporation 統計情報取得の高速化 -- 統計ビュー 統計ビュー 統計情報取得の高速化 © 2009 ISE Corporation 統計ビュー(Statistical View) V9.1 • 統計ビュー 統計ビューはどのような ビューはどのような時 はどのような時に使用するのか 使用するのか? するのか? 思った通りの順番で Joinしてくれない・・・ HSJOINして欲しい でも、NLJOINを選択 する・・・ 使って欲しい索引 を使用しない・・・ 統計情報収集 分散統計取得 列グループ 統計取得 統計ビュー作成 SQL変更 パラメータ調整 索引作成 ・ ・ ・ © 2009 ISE Corporation 統計ビュー(Statistical View) • Viewに に対する統計情報収集 )が可能に する統計情報収集( 統計情報収集(Runstats) 可能に V9.1 • より有効なアクセス・パスの選択が期待できる • 表と表との間の関連性は表に対するRunstatsでは対応できない • JOINによる絞り込み見積もりがより正確に • ある表における、列間の関連性は列グループ統計で対応 • 直接Viewに対する照会でなくても有効 • MQTのように、アプリケーションから意識する必要はない © 2009 ISE Corporation 統計ビュー(Statistical View) • ページ・ ページ・レベルでの レベルでのサンプリング でのサンプリングが サンプリングが可能に 可能に V9.7 • Runstats時に「TABLESAMPLE SYSTEM」が有効に • V9.1で行レベルの「TABLESAMPLE BERNOULLI」をサポート • ページレベルのサンプリングにより、Runstats性能向上 • SYSTEMでサンプリング可能な統計ビュー • 原則、統計ビューの対象が単一の表であればサンプリング可能 • 複数の表をJoinしている統計ビューも以下のケースでは可能 • 主キーおよび外部キーの等価述部でJoinしている • 親表に検索条件フィルターがない • 子表が、すべての表の中から識別可能 • 上記の条件を満たしていない統計ビューをサンプリングした場合、 BERUNOLLIが選択される SQL2317W SYSTEM SAMPLING was specified for RUNSTATS but is not supported for the statistical view specified. BERNOULLI SAMPLING was done instead. instead. SQLSTATE=0168V © 2009 ISE Corporation 統計ビューの使用方法① • ビュー作成後 ビュー作成後に 作成後に属性変更 CREATE VIEW ビュー名 AS select ・・・・・; ALTER VIEW ビュー名 ENABLE QUERY OPTIMIZATION; • ENABLE/DISABLE QUERY OPTIMIZATIONオプションで指定 • CREATE VIEWステートメントでは指定できない • ALTER VIEWの実行のために、以下のうち少なくとも1つの条件を 満たしている必要あり • • • • VIEWのスキーマに対するALTERIN特権がある VIEWの定義者がALTER実行者である VIEWに対するCONTROL権限がある SYSADMまたはDBADM権限がある © 2009 ISE Corporation 統計ビューの使用方法② • 統計ビュー 実行 統計ビューに ビューにRunstats実行 例① RUNSTATS ON TABLE ビュー名 ALLOW READ ACCESS; 例② RUNSTATS ON TABLE ビュー名 WITH DISTRIBUTION; 例③ RUNSTATS ON TABLE ビュー名 WITH DISTRIBUTION TABLESAMPLE SYSTEM (10); • 通常の表に対するRunstatsと同様のオプションを指定可能 • ALLOW READ/WRITE ACCESS • WITH DISTRIBUTION • TABLESAMPLE • VIEWの元になる表、VIEW、ニックネームに対して、以下のうち少 なくとも1つの権限が必要 • SELECT特権 • CONTROL権限 • DATAACCESS権限 © 2009 ISE Corporation 統計ビューにできないビュー • 以下の にできない 以下のビューに ビューに対してはENABLEにできない しては • MQTを直接、間接的に参照している • タイプ付きVIEW • aggregation, DISTINCTを含んでいる • UNION, EXCEPT,INTERSECTの操作を含んでいる • scalar aggregate (OLAP)関数を含んでいる © 2009 ISE Corporation 参考) 実行結果 CREATE VIEW SALES_VIEW AS SELECT S.TRANSDATE, S.CITY, S.SKU, S.SALES S.SALES FROM SAMPLTBC.SALES S WHERE S.CITY='Atlanta' DB20000I SQL コマンドが コマンドが正常に 正常に完了しました 完了しました。 しました。 RUNSTATS ON TABLE SAMPLTBC.SALES_VIEW ENABLEにしていな いので、エラー SQL20288N タイプ "View" のオブジェクト "SALES_VIEW" の統計データ 統計データを データを更新できませんでした 更新できませんでした。 できませんでした。理由コード 理由コード = "1"。 "1"。 SQLSTATE=428DY ALTER VIEW SALES_VIEW ENABLE QUERY OPTIMIZATION DB20000I SQL コマンドが コマンドが正常に 正常に完了しました 完了しました。 しました。 RUNSTATS ON TABLE SAMPLTBC.SALES_VIEW DB20000I RUNSTATS コマンドが コマンドが正常に 正常に完了しました 完了しました。 しました。 RUNSTATS ON TABLE SAMPLTBC.SALES_VIEW ALLOW READ ACCESS DB20000I RUNSTATS コマンドが コマンドが正常に 正常に完了しました 完了しました。 しました。 © 2009 ISE Corporation 参考) 実行結果 RUNSTATS ON TABLE SAMPLTBC.SALES_VIEW WITH DISTRIBUTION ON COLUMNS COLUMNS (CITY NUM_FREQVALUES 50 NUM_QUANTILES 100) DB20000I RUNSTATS コマンドが コマンドが正常に 正常に完了しました 完了しました。 しました。 RUNSTATS ON TABLE SAMPLTBC.SALES_VIEW WITH DISTRIBUTION TABLESAMPLE TABLESAMPLE BERNOULLI (10) DB20000I RUNSTATS コマンドが コマンドが正常に 正常に完了しました 完了しました。 しました。 RUNSTATS ON TABLE SAMPLTBC.SALES_VIEW WITH DISTRIBUTION TABLESAMPLE SYSTEM (10) DB20000I RUNSTATS コマンドが コマンドが正常に 正常に完了しました 完了しました。 しました。 V9.7より、SYSTEMのサン プリングがサポートされた © 2009 ISE Corporation 参考) 実行結果 NGの例 CREATE VIEW NG1 (CITY, SALES_TOTAL) AS SELECT CITY, SUM(SALES) FROM FROM SAMPLTBC.SALES GROUP BY CITY DB20000I SQL コマンドが コマンドが正常に 正常に完了しました 完了しました。 しました。 ALTER VIEW NG1 ENABLE QUERY OPTIMIZATION SQL20278W 照会の 照会の処理の 処理の最適化に 最適化にビュー "SAMPLTBC.NG1" を使用できない 使用できない可能性 できない可能性があります 可能性があります。 があります。 SQLSTATE=01667 CREATE VIEW NG2 AS SELECT DISTINCT(CITY) DISTINCT(CITY) FROM SAMPLTBC.SALES DB20000I SQL コマンドが コマンドが正常に 正常に完了しました 完了しました。 しました。 ALTER VIEW NG2 ENABLE QUERY OPTIMIZATION SQL20278W 照会の 照会の処理の 処理の最適化に 最適化にビュー "SAMPLTBC.NG2" を使用できない 使用できない可能性 できない可能性があります 可能性があります。 があります。 SQLSTATE=01667 CREATE VIEW NG3 AS SELECT S.TRANSDATE, S.CITY, S.SKU, S.SALES FROM FROM SAMPLTBC.SALES S WHERE S.CITY='Atlanta' UNION ALL SEL ECT S2.TRANSDATE, S2.CITY, S2.SKU, S2.SALES FROM SAMPLTBC.SALES S2 WHERE S2.CITY='Yonkers' DB20000I SQL コマンドが コマンドが正常に 正常に完了しました 完了しました。 しました。 ALTER VIEW NG3 ENABLE QUERY OPTIMIZATION SQL20278W 照会の 照会の処理の 処理の最適化に 最適化にビュー "SAMPLTBC.NG3" を使用できない 使用できない可能性 できない可能性があります 可能性があります。 があります。 SQLSTATE=01667 aggregation, DISTINCT,UNIONが含ま れているVIEWは、統計情 報収集可能にできない © 2009 ISE Corporation 収集される統計情報 • 通常の 通常の表と同様な 同様な情報が 情報が収集される 収集される • 条件が付与されているビューであれば、対象範囲内の統計情報 が収集される • 以下の 以下のカタログ表 カタログ表に統計情報が 統計情報が収集される 収集される • SYSSTAT.TABLES • SYSSTAT.COLUMNS • SYSSTAT.COLDIST © 2009 ISE Corporation (参考)アクセスパスへの影響 • 直接Viewに に対する照会 直接 する照会でなくても 照会でなくても、 でなくても、より効果的 より効果的な 効果的なアクセス・ アクセス・パ スが選択される 選択される可能性 される可能性が 可能性が高まる SALES表 SALES表 4770行 4770行 INVENTORY表 INVENTORY表 47580行 47580行 CITY= ‘Atlanta' SALES_VIEW 24行 24行 CITY= ‘Atlanta' INV_VIEW 120行 120行 SELECT S.TRANSDATE,S.CITY,S.SKU,S.SALES,I.ITEMS_SOLD FROM SALES S, INVENTORY I WHERE S.CITY = 'Atlanta‘ 'Atlanta‘ AND S.TRANSDATE = I.TRANSDATE AND S.CITY = I.CITY AND S.SKU = I.SKU © 2009 ISE Corporation 解説: • SALES表とINVENTORY表は、それぞれCITY列を持っている • CITY=‘Atlanta’を指定したVIEWを作成 • SALES表の4770件中、 CITY=‘Atlanta’は24件 • INVENTORY表の47580件中、CITY=‘Atlanta’は120件 • CITYのCOLCARDは両表とも31 • よって、 CITY=‘Atlanta’はFilterFacterから換算される件数より、非常に少ない • この状態でSALES表とINVENTORY表のJOINをし、 条件としてCITY=‘Atl anta’を指定した場合のアクセスパスを確認 © 2009 ISE Corporation (参考)アクセスパスへの影響 • View統計情報 統計情報がない 統計情報がない場合 がない場合の 場合のアクセスパス Optimized Statement: ------------------SELECT Q2."TRANSDATE" AS "TRANSDATE", Q2."CITY" AS "CITY", Q2."SKU" AS "SKU", Q2."SALES" AS "SALES", Q1."ITEMS_SOLD" AS "ITEMS_SOLD" FROM SAMPLTBC.INVENTORY AS Q1, SAMPLTBC.SALES AS Q2 WHERE (Q2."SKU" = Q1."SKU") AND ('Atlanta' = Q1."CITY") AND (Q2."TRANSDATE" = Q1."TRANSDATE") AND (Q2."CITY" = 'Atlanta') Access Plan: ----------Total Cost: Query Degree: 135.429 1 Rows RETURN ( 1) Cost I/O | 9.7622 HSJOIN ( 2) 135.429 56.2369 (次ページにつづく) © 2009 ISE Corporation (参考)アクセスパスへの影響 /----------+---------¥ 1534.84 153.871 FETCH FETCH ( 3) ( 7) 100.126 34.4772 51.7433 4.49355 /---+---¥ /---+---¥ 1534.84 47580 153.871 4770 RIDSCN TABLE: SAMPLTBC IXSCAN TABLE: SAMPLTBC ( 4) INVENTORY ( 8) SALES 66.0938 14.9682 21.5867 1.93548 | | 1534.84 4770 SORT INDEX: SAMPLTBC 該当件数は 該当件数は ( 5) IDXSALES_U Filterfacterから Filterfacterから換算 から換算 66.0925 21.5867 | 1534.84 IXSCAN ( 6) 64.3358 21.5867 Extended Diagnostic Information | -------------------------------47580 No extended Diagnostic Information for this statment. INDEX: SAMPLTBC IDXINV_U © 2009 ISE Corporation (参考)アクセスパスへの影響 • View統計情報 統計情報がある 統計情報がある場合 がある場合アクセスパス 場合アクセスパス Optimized Statement: ------------------SELECT Q2."TRANSDATE" AS "TRANSDATE", Q2."CITY" AS "CITY", Q2."SKU" AS "SKU", Q2."SALES" AS "SALES", Q1."ITEMS_SOLD" AS "ITEMS_SOLD" FROM SAMPLTBC.INVENTORY AS Q1, SAMPLTBC.SALES AS Q2 WHERE (Q2."SKU" = Q1."SKU") AND ('Atlanta' = Q1."CITY") AND (Q2."TRANSDATE" = Q1."TRANSDATE") AND (Q2."CITY" = 'Atlanta') Access Plan: ----------Total Cost: Query Degree: Rows RETURN ( 1) Cost I/O | 0.119048 MSJOIN ( 2) 39.7514 5.20421 .(次ページにつづく) 39.7514 1 コストが コストが小さくなっている HSJOINが HSJOINがMSJOINに MSJOINに変更 © 2009 ISE Corporation (参考)アクセスパスへの影響 /-------+------¥ 24 0.00496032 FETCH FILTER ( 3) ( 5) 15.2607 41.9155 2 5.4883 /---+---¥ | 24 4770 120 IXSCAN TABLE: SAMPLTBC FETCH ( 4) SALES ( 6) 7.66585 41.9155 1 5.4883 | /---+---¥ 4770 120 47580 INDEX: SAMPLTBC IXSCAN TABLE: SAMPLTBC IDXSALES_U ( 7) INVENTORY 19.126 2.49306 | 47580 INDEX: SAMPLTBC IDXINV_U INVENTORY表 INVENTORY表のアク セスパスも セスパスも変更 該当件数は 該当件数はViewの Viewの統 計情報から 計情報から入手 から入手 © 2009 ISE Corporation (参考)アクセスパスへの影響 • MQTと と同様、 の出力に が 同様、db2exfmtの 出力に拡張診断情報が 拡張診断情報が出力され 出力され、 され、Statistical Viewが オプティマイゼーションの オプティマイゼーションの過程で 過程で、候補になったかどうかの 候補になったかどうかの情報 になったかどうかの情報が 情報が分かる Extended Diagnostic Information: -------------------------------Diagnostic Identifier: Diagnostic Details: Diagnostic Identifier: Diagnostic Details: Diagnostic Identifier: Diagnostic Details: Diagnostic Identifier: Diagnostic Details: 1 EXP0147W The following statistical view may have been used by the optimizer to estimate cardinalities: "SAMPLTBC"."INV_VIEW". 2 EXP0147W The following statistical view may have been used by the optimizer to estimate cardinalities: "SAMPLTBC"."SALES_VIEW". 3 EXP0148W The following MQT or statistical view was considered in query matching: "SAMPLTBC"."INV_VIEW". 4 EXP0148W The following MQT or statistical view was considered in query matching: "SAMPLTBC". "SALES_VIEW“ 該当された 該当されたView されたView情報 View情報が 情報が表示される 表示される EXP0147W,EXP0148W © 2009 ISE Corporation 参考) RUNSTATS実行時間結果 • 以下の 列100万行 万行ある を定義し の時間を 以下の6列 万行ある表 ある表にViewを 定義し、RUNSTATSの 時間を測定 •行数が増えれば、実行時間が長くなる TABLESAMPLEの •列数も実行時間に影響する 実行時間情報を追加 CHAR_TAB表 CHAR_TAB表 KCOL C1 C2 C3 C4 C5 INTEGER CHAR(20) CHAR(40) CHAR(60) CHAR(80) CHAR(100) 元表の 元表の COUNT(*) 列 View① ① View② ② View③ ③ View④ ④ View⑤ ⑤ All All All All KCOLのみ のみ 行数 1000000 1000 100000 500000 1000000 500000 時間 5.4秒 秒 5.5秒 秒 6.0秒 秒 7.6秒 秒 8.8秒 秒 7.2秒 秒 © 2009 ISE Corporation 統計情報取得の 統計情報取得の高速化 ー 統計ビュー 統計ビュー まとめ • 統計ビュー 統計ビューとは ビューとは、 とは、表間の 表間の関連性を 関連性をオプティマイザに オプティマイザに提供する 提供する機 する機 能 • 表間の関連性の統計を収集することで、 より適切なアクセスパスに • 統計ビュー 文に含まれていなくても効果 統計ビューは ビューは、Select文 まれていなくても効果あり 効果あり • 通常の ()により 通常の表と同様に 同様に、TABLESAMPLE SYSTEM() ()により Runstats時間 時間が 時間が短縮 • EXPLAINにより により、 により、統計ビュー 統計ビューの ビューの統計情報の 統計情報の採用有無を 採用有無を確認 © 2009 ISE Corporation