SQL文によるデータベースの検索所要時間の予測方法及びプログラム
【課題】開発環境でのSQL文による検索所要時間をもとに、ハードウェア性能などが異なった環境でのSQL文によるデータベースの検索所要時間を予測することができる方法及びプログラムを提供すること。
【解決手段】開発環境のシステムにおいて検索所要時間予測対象のSQL文を実行し、当該SQL文が使用した全てのテーブル及び各テーブルのデータレコード件数及び1レコード長の情報を取得する。また、前記SQL文を開発環境のシステムで実行した時の実行計画、解析時間、実行時間、データ読み出し時間、データ読み出しサイズ、検索結果行数に関する情報を取得する。さらに、新たな環境のシステムと開発環境のハードウェア性能比を取得する。これらの情報により、前記SQL文による前記新たな環境のシステムにおけるデータベースの検索所要時間を予め設定した予測計算式に従って算出して出力する。
【解決手段】開発環境のシステムにおいて検索所要時間予測対象のSQL文を実行し、当該SQL文が使用した全てのテーブル及び各テーブルのデータレコード件数及び1レコード長の情報を取得する。また、前記SQL文を開発環境のシステムで実行した時の実行計画、解析時間、実行時間、データ読み出し時間、データ読み出しサイズ、検索結果行数に関する情報を取得する。さらに、新たな環境のシステムと開発環境のハードウェア性能比を取得する。これらの情報により、前記SQL文による前記新たな環境のシステムにおけるデータベースの検索所要時間を予め設定した予測計算式に従って算出して出力する。
【発明の詳細な説明】
【技術分野】
【0001】
本発明は、DBMS(DataBase Management System:データベース管理システム)において、SQL文(Structured Query Language:構造化問合せ言語)によるデータベースの検索所要時間を予測する方法及びプログラムに関するものである。
【背景技術】
【0002】
顧客情報や営業情報の履歴を管理するCRMシステム等のデータベースには、膨大な件数のデータが格納されている。
図13は、従来におけるCRMシステムの概要を示すシステム構成図であり、クライアント1301のクライアントアプリケーション1302からの検索要求に対し、サーバ1303にはデータベース1306の顧客情報履歴1307や営業情報履歴1308を検索するサーバアプリケーション1304、1305が実装されており、これらサーバアプリケーション1304,1305はSQL文によってデータベース1306を検索し、検索結果を要求元のクライアントアプリケーション1302に返すようになっている。
このようなシステムにあっては、サーバアプリケーション1304,1305から実行されるSQL文も、検索処理によって異なる。具体的には、汎用的な検索を行って大量の検索結果を返すSQL、指定された検索条件をもとに少量の検索結果を返すSQLと様々である。
このうち、大量の検索結果を返すサーバアプリケーション1304のSQLは、CPUやメモリ(データベースバッファ)等のシステムリソースを大量に消費し、所要時間も長いため、他のサーバアプリケーション1305で実行するSQL文に影響を及ぼす可能性がある。
そのため、当該SQL文を実行するサーバアプリケーション1304,1305は、本番環境へリリースする前に、本番環境での当該SQL文による検索所要時間を事前に確認しておくことが望まれる。
【0003】
このような技術に関連し、プログラムの実行時間を予測する方法の先行技術文献としては次の3つの特許文献が挙げられる。
【特許文献1】特開2002−276361号
【特許文献2】特開平9−305556号
【特許文献3】特開平5−3341241号
【発明の開示】
【発明が解決しようとする課題】
【0004】
しかし、本番環境のシステムを使用したSQL文による検索所要時間を予測して確認するテストは、他のテスト要件との日程調整やセキュリティ等の観点から、実施機会が制限されることが多い。
開発環境のシステムを使用することも可能であるが、開発環境のシステムでは本番環境相当のデータ件数を格納することができない場合が多い。この場合、開発環境のシステムはアプリケーション開発に最低限必要なデータだけを抽出した環境となっており、予測結果と本番環境での検索所要時間とは大きく異なる場合が多く、本番環境に近似した検索所要時間を予測することができない。
さらに、CPUクロック数やメモリ(データベースバッファ)サイズ、ディスクI/O速度といったハードウェアの性能が異なる場合が多いので、上記と同様に、本番環境に近似した検索所要時間を予測することができない。
【0005】
本発明の目的は、開発環境でのSQL文による検索所要時間をもとに、検索対象のデータ件数やハードウェア性能が異なった本番環境などでのSQL文によるデータベースの検索所要時間を予測することができる方法及びプログラムを提供することにある。
【課題を解決するための手段】
【0006】
上記目的を達成するために、本発明に係るSQL文によるデータベースの検索所要時間予測方法は、開発環境のシステムにおいて検索所要時間予測対象のSQL文を実行し、当該SQL文が使用した全てのテーブル及び各テーブルのデータレコード件数及び1レコード長の情報を取得し、第1の記憶手段に記憶する第1のステップと、前記SQL文を開発環境のシステムで実行した時の実行計画、解析時間、実行時間、データ読み出し時間、データ読み出しサイズ、検索結果行数に関する情報を取得し、第2の記憶手段に記憶する第2のステップと、検索所要時間の予測対象の新たな環境のシステムと開発環境のシステムとのメモリ転送速度比、ディスク転送速度比、CPUクロック数比、新たな環境でのデータキャッシュサイズ、ソートキャッシュサイズ、ディスクI/O速度に関する情報を取得して第3の記憶手段に記憶する第3のステップと、前記第1〜第3の記憶手段に記憶された情報を読み出し、前記SQL文による前記新たな環境のシステムにおけるデータベースの検索所要時間を予め設定した予測計算式に従って算出して出力する第4のステップとを備えることを特徴とする。
【0007】
また、本発明に係るSQL文によるデータベースの検索所要時間予測プログラムは、第1の記憶手段から、開発環境のシステムにおける検索所要時間予測対象のSQL文を実行結果によって収集された当該SQL文が使用した全てのテーブル及び各テーブルのデータレコード件数及び1レコード長の情報を読出す第1のステップと、第2の記憶手段から、前記SQL文を開発環境のシステムで実行した時の実行計画、解析時間、実行時間、データ読み出し時間、データ読み出しサイズ、検索結果行数に関する情報を読出す第2のステップと、第3の記憶手段から、検索所要時間の予測対象の新たな環境のシステムと開発環境のシステムとのメモリ転送速度比、ディスク転送速度比、CPUクロック数比、新たな環境でのデータキャッシュサイズ、ソートキャッシュサイズ、ディスクI/O速度に関する情報を読出す第3のステップと、これら第1〜第3の記憶手段からの読出し情報に基づき、前記SQL文による前記新たな環境のシステムにおけるデータベースの検索所要時間を予め設定した予測計算式に従って算出して出力する第4のステップとをコンピュータに実行させることを特徴とする。
【発明の効果】
【0008】
本発明によれば、ある環境でSQLを実行した時のSQL文によるデータベースの検索所要時間や、その環境とのハードウェア性能比をもとに、レコード件数やハードウェア性能が異なる環境での当該SQL文によるデータベースの検索所要時間をほぼ正確に予測することができる。
【発明を実施するための最良の形態】
【0009】
以下、本発明を図面に基づいて詳細に説明する。
図1は、本発明に係る方法を実施する所要時間予測計算装置の実施の形態を示す構成図である。
この実施形態の所要時間予測計算装置10は、CPU11、入力装置12、メモリ13、出力装置14を備え、メモリ13には、開発環境レコード件数記憶手段15、開発環境SQL情報記憶手段16、ハードウェア性能比記憶手段17に記憶された情報を読出し、開発環境とは異なる新たなSQL文の実行環境(以下、本番環境と言う)におけるデータベースの検索所要時間(以下、SQL所要時間と略記する)を予測するSQL所要時間予測計算プログラム14が記憶されている。
【0010】
ここで、SQL所要時間を予測する上での前提条件は、以下の通りである。
まず、本番環境でのデータ分布は、開発環境システムとのデータ分布から変化しないと仮定する。テーブル結合は2テーブルまでとし、結合方式は図11の説明図に示すネステッドループ結合、もしくは図12の説明図に示すソート・マージ結合とする。
ネステッドループ結合とは、図11の説明図に示すように、開発環境において外部表Aと内部表Bがあった場合に、(1)まず、外部表Aを検索し、(2)外部表Aの結合項目値をもとに、内部表Bを検索し、(3)結合した検索結果を返す、というものである。
このネステッドループ結合では、開発環境での読み出しデータサイズは、A×Bに近似する。
そのため、外部表Aおよび内部表Bの値が、本番環境でそれぞれm倍およびn倍になった場合、読み出しデータサイズは、mA×nBに近似する。
よって、本番環境での読み出しデータサイズ
≒(開発環境での読み出しデータサイズ)×(mA×nB)÷(A×B)
に近似することになる。
【0011】
ソート・マージ結合とは、図12の説明図に示すように、開発環境において外部表Aと内部表Bがあった場合に、(1)まず、外部表Aを検索し、(2)外部表Bをソートし、(3)内部表Bを検索し、(4)内部表Bをソートし、(5)外部表Aの結合項目値をもとに、内部表Bを検索し、(6)外部表Aの結合項目値をもとに、内部表Bを検索し、結合した検索結果を返す、というものである。
このソート・マージ結合では、開発環境での読み出しデータサイズは、A+Bに近似する。
そのため、外部表Aおよび内部表Bの値が、本番環境ではそれぞれm倍およびn倍になった場合、読み出しデータサイズは、mA+nBに近似する。
よって、本番環境の読み出しデータサイズ
≒(開発環境での読み出しデータサイズ)×(mA+nB)÷(A+B)
に近似することになる。
【0012】
本発明では、開発環境でのSQL実行結果と開発環境とのハードウェア性能比などをもと本番環境でのSQL所要時間を予測する。
なお、SQL所要時間は、以下の合計時間となる。
(1)SQL解析時間
SQLを元にSQL実行計画を決定する。
(2)SQL実行時間
SQL実行計画を元にSQLを実行する。
(3)データ読み出し時間
実行されたSQLにより、データを読み取り、検索結果を返す。
【0013】
本発明では、まず、事前準備作業として、図5のフローチャートに示すような手順で、開発環境における情報を取得し、各記憶手段15,16,17に記憶させる。
まず、所要時間を予測したいSQL文40を開発環境システム20で実行する(ステップ501)。そして、当該SQL実行時のSQLトレースを取得しておく(ステップ502)。
次に、取得したSQLトレースから、SQL文で使用したテーブルを確認し、該当するテーブルの開発環境レコード件数および1レコード長をデータベース上の管理情報から取得する。取得した情報は、開発環境レコード件数記憶手段15に記憶させる(ステップ503)。
また、開発環境SQL情報記憶手段16へ、実行計画、SQL解析時間、SQL実行時間、データ読み出し時間、データ読み出しサイズ、検索結果行数を入力して記憶させる(ステップ504)。
さらに、ハードウェア性能情報記憶手段17へ、本番環境システム30と開発環境システム20とのメモリI/O速度比、本番環境システム30と開発環境システム20とのディスクI/O速度比、本番環境システム30と開発環境システム20とのCPUクロック数比、本番環境システム30のデータバッファサイズ、本番環境システム30でのソートバッファサイズ、本番環境システム30でのディスクI/O速度を入力装置11から入力して記憶させる(ステップ505)。
以上で、事前の情報取得は完了である。
【0014】
これにより、開発環境レコード件数記憶手段15には、図2に示すように、SQL文が使用したテーブル名201毎に、開発環境レコード件数202、1レコード長203の情報が記憶される。
また、開発環境SQL情報記憶手段16には、図3に示すように、所要時間予測SQL301毎に、実行計画302、SQL解析時間303、SQL実行時間304、データ読み出し時間305、データ読み出しサイズ606、検索結果行数307から成る情報が記憶される。
また、ハードウェア性能情報記憶手段17には、図4に示すように、本番環境などの新たな環境401ごとに、開発環境とのメモリI/O速度比402、開発環境とのディスクI/O速度比403、開発環境とのCPUクロック数比404、本番環境のデータバッファサイズ405、本番環境でのソートバッファサイズ406、本番環境でのディスクI/O速度407から成る情報が記憶される。
【0015】
次に、図6〜図10のフローチャートに示す処理によって、本番環境システム30でのSQL実行時間を予測する。
まず、所要時間を予測したいSQL文40をSQL所要時間予測計算プログラム14に入力する(ステップ511)。
これにより、当該プログラム14は、開発環境SQL情報記憶手段16を参照し、当該SQL文40について、実行計画、SQL解析時間、SQL実行時間、データ読み出し時間、データ読み出しサイズの情報を取得する(ステップ512)。
また、当該プログラム14は、ハードウェア性能情報記憶手段17を参照し、メモリI/O速度比、CPUクロック数比、本番環境のデータバッファサイズ、本番環境ソートバッファサイズ、本番環境ディスクI/O速度から成る情報を取得する(ステップ513)。
次に、前記の情報を取得した後、開発環境システム20でのSQL解析時間をCPUクロック数比で除算し、本番環境システム30での当該SQL解析時間を算出する(ステップ514)。
さらに、開発環境システム20でのSQL実行時間をCPUクロック数比で除算し、本番環境システム30での当該SQL実行時間を算出する(ステップ515)。
これまでが、本番環境システム30でのSQL解析時間およびSQL実行時間の算出工程となる。
【0016】
続いて、本番環境システム30でのデータ読み出し時間を算出する。
まず、当該プログラム14は、入力したSQL文40から当該SQL文40が使用するテーブル名を取得する(ステップ516)。
次に、当該プログラム実行者による入力装置12の入力操作により、SQL文40が使用するテーブルの本番環境のレコード件数と本番環境のデータバッファサイズを受付ける(ステップ517)。
すると、当該プログラム14は、入力された「SQL文40が使用するテーブル数」が単一テーブルの検索か否かを確認する(ステップ521)。
テーブル数が1つの場合、以下の「数式1」より、本番環境システム30でのデータ読み出し時間を算出する(ステップ522)。
【数1】
【0017】
次に、前記の「数式1」から、本番環境システム30でのデータ読み出し時間を算出したが、ディスクI/Oが発生する場合は、その分読み出し時間が長くなる。そのため、ディスクI/Oが発生する可能性があるかを確認する。
そこで、次の「数式2」により、本番環境システム30でのデータ読み出しサイズを算出する(ステップ523)。
【数2】
【0018】
次に、本番環境システム30でのデータ読み出しサイズが、本番環境システム30のデータバッファサイズより小さいか確認する(ステップ531)。
もし、データ読み出しサイズの方が大きい場合は、ディスクI/Oが発生するため、次の「数式3」により、本番環境システム30でのデータ読み出し時間を修正する(ステップ532)。
【数3】
【0019】
さらに、ソート処理があるかを確認する。この時、本番環境システム30でのデータ読み出しサイズが本番環境システム30のソートバッファサイズより小さいかもあわせて確認する(ステップ533)。
ソート処理があり、本番環境システム30でのデータ読み出しサイズが本番環境のソートバッファサイズより小さい場合は、ディスクソートが発生するため、以下の「数式4」より、本番環境システム30でのデータ読み出し時間を修正する(ステップ534)。
【数4】
【0020】
以上の工程により、単一テーブルの場合の本番環境システム30でのデータ読み出し時間の予測は完了である。
そして、算出した本番環境システム30でのSQL解析時間、SQL実行時間およびデータ読み出し時間の合計が、本番環境システム30での単一テーブルのSQL実行時間となる。
【0021】
次に、2テーブル結合の場合のデータ読み出し時間の算出方法について説明する。
まず、テーブル結合方式を確認する(ステップ541)。
テーブル結合方式がソート・マージ結合の場合は、図12の説明図で説明したように、結合するテーブルをそれぞれソートした後に、結合項目によって結合を行い、本番環境システム30での読み出しデータサイズを算出する(ステップ542)。
この場合、本番環境システム30での読み出しデータサイズは、以下の「数式5」式より算出した値に近似される。
【数5】
【0022】
さらに、本番環境システム30のハードウェア性能比を考慮するため、前記で算出した本番環境でのデータ読み出し時間を以下の「数式6」より修正する(ステップ543)。
【数6】
【0023】
続いて、単一テーブルの場合と同様に、データバッファおよびソートバッファでのディスクI/Oがあるかを確認し、必要に応じて本番環境システム30でのデータ読み出し時間を修正する。
一方、ネステッドループの場合は、図11の説明図で説明したように、外部表Aの各レコードの結合項目をもとに内部表Bを検索する。
この場合、本番環境システム30での読み出しデータサイズは、以下の「数式7」により算出した値に近似される(ステップ544)。
【数7】
【0024】
さらに、ソート・マージの場合と同様に、本番環境システム30のハードウェア性能比を考慮するため、前記で算出した本番環境システム30でのデータ読み出し時間を以下の「数式8」より修正する(ステップ545)。
【数8】
【0025】
前記で算出した本番環境システム30でのデータ読み出し時間は、ディスクI/Oが発生する場合を考慮されていないので、本番環境システム30でのデータ読み出しサイズが本番環境システム30のデータバッファサイズより小さいか確認する(ステップ546)。
【0026】
本番環境システム30でのデータ読み出しサイズが本番環境システム30のデータバッファサイズより大きい場合、以下の「数式9」により、本番環境システム30でのデータ読み出し時間を修正する(ステップ547)。
【数9】
【0027】
前記の修正後は、ソート時のディスクI/Oを確認する(ステップ551)。
ソート処理がある場合は、ネステッドループでは、結合したテーブルの一致した行のみをソートするので、一致した行は、検索結果行数×(外部表の1レコード長+内部表の1レコード長)×本番環境のレコード件数÷開発環境のレコード件数となる。上記の情報は、開発環境SQL情報記憶手段16と開発環境レコード件数記憶手段15より取得する。
そこで、以下の「数式10」により、ネステッドループにおける本番環境システム30での想定ソートサイズを算出する(ステップ552)。
【数10】
【0028】
さらに、前記で算出した本番環境システム30での想定ソートサイズが本番環境システム30のソートバッファサイズより小さいかを確認する(ステップ553)。
本番環境システム30での想定ソートサイズが本番環境システム30のソートバッファサイズより大きい場合は、以下の「数式11」により、本番環境システム30でのデータ読み出し時間を修正する(ステップ554)。
【数11】
以上で、2テーブル結合時の本番環境システム30でのデータ読み出し時間は完了である。
【0029】
(具体例)
以下に、この発明の具体例を挙げて説明する。
まず、本番環境で所要時間を予測したいSQL文を開発環境システム20で実行する。実行するSQL文は、図3の1行目に記載している「select * from A;」とする。このSQL文の情報は、当該SQL文実行時のSQLトレースから取得し、図3の開発環境SQL情報記憶手段16へ入力する。
入力内容は、図3の1行目に記載の通り、実行計画はテーブルAの検索、SQL解析時間は0.01秒、SQL実行時間は0.01秒、データ読み出し時間は5.00秒、データ読み出しサイズは5MB、検索結果行数は10000件となる。
【0030】
次に、ハードウェア性能情報記憶手段17へ情報を入力する。
図4の1行目に記載の通り、開発環境システム20とのメモリI/O速度比は1、開発環境システム20とのディスクI/O速度比は5、開発環境システム20とのCPUクロック数比は2、本番環境システム30でのデータバッファサイズは1GB、本番環境システム30でのソートバッファサイズは10MB、本番環境システム30でのディスクI/O速度は100MB/secとなる。
【0031】
次に、本番環境システム30でのSQL解析時間とSQL実行時間を算出する。開発環境システム20での値は、それぞれ0.01秒であるため、それぞれCPUクロック数比で除算すると、0.01秒(SQL解析時間、もしくは、SQL実行時間)÷2(CPUクロック数比)=0.005秒となる。
【0032】
さらに、データ読み出し時間を算出するため、本番環境のレコード件数を入力する。なお、当該SQLは、単一テーブル検索であるため、開発環境のデータ読み出し時間に、本番環境システム30と開発環境システム20のレコード件数比率を乗算し、メモリI/O速度比とCPUクロック数比で除算する。
開発環境システム20のデータ読み出し時間が、5.00秒であるため、5.00秒(開発環境のデータ読み出し時間)×50000(本番環境のレコード件数)÷10000(開発環境のレコード件数)÷1(メモリI/O速度比)÷2(CPUクロック数比)=12.5秒となる。
また、当該テーブルの本番環境システム30でのデータ読み出しサイズは、5MB(開発環境でのデータ読み出しサイズ)×50000(本番環境のレコード件数)÷10000(開発環境のレコード件数)=25MBとなる。
これは、本番環境システム30のデータバッファサイズ1GBよりも小さいため、ディスクI/Oは発生しない。よって、前記で算出した本番環境システム30でのデータ読み出し時間は修正不要である。
また、当該SQLでは、ソートが発生しないため、当該SQLの本番環境システム30でのデータ読み出し時間算出は、以上で完了となる。
よって、当該SQL文の本番環境システム30での実行時間は、12.5秒であると予測される。
【図面の簡単な説明】
【0033】
【図1】本発明を実施する計算装置の実施の形態を示す構成図である。
【図2】開発環境レコード件数記憶手段の記憶内容の例を示す図である。
【図3】開発環境SQL情報記憶手段の記憶内容の例を示す図である。
【図4】ハードウェア性能情報記憶手段の記憶内容の例を示す図である。
【図5】SQL所要時間を予測するプログラムの処理を示すフローチャートである。
【図6】図5の続きを示すフローチャートである。
【図7】図6の続きを示すフローチャートである。
【図8】図7の続きを示すフローチャートである。
【図9】図8の続きを示すフローチャートである。
【図10】図9の続きを示すフローチャートである。
【図11】ネステッドループ処理の概要説明図である。
【図12】ソート・マージ処理の概要説明図である。
【図13】従来におけるCRMシステムの概要を示すシステム構成図である。
【符号の説明】
【0034】
10 所要時間予測計算装置
14 SQL所要時間予測計算プログラム
15 開発環境レコード件数記憶手段
16 開発環境SQL情報記憶手段
17 ハードウェア性能比記憶手段
20 開発環境システム
30 本番環境システム
40 SQL文
【技術分野】
【0001】
本発明は、DBMS(DataBase Management System:データベース管理システム)において、SQL文(Structured Query Language:構造化問合せ言語)によるデータベースの検索所要時間を予測する方法及びプログラムに関するものである。
【背景技術】
【0002】
顧客情報や営業情報の履歴を管理するCRMシステム等のデータベースには、膨大な件数のデータが格納されている。
図13は、従来におけるCRMシステムの概要を示すシステム構成図であり、クライアント1301のクライアントアプリケーション1302からの検索要求に対し、サーバ1303にはデータベース1306の顧客情報履歴1307や営業情報履歴1308を検索するサーバアプリケーション1304、1305が実装されており、これらサーバアプリケーション1304,1305はSQL文によってデータベース1306を検索し、検索結果を要求元のクライアントアプリケーション1302に返すようになっている。
このようなシステムにあっては、サーバアプリケーション1304,1305から実行されるSQL文も、検索処理によって異なる。具体的には、汎用的な検索を行って大量の検索結果を返すSQL、指定された検索条件をもとに少量の検索結果を返すSQLと様々である。
このうち、大量の検索結果を返すサーバアプリケーション1304のSQLは、CPUやメモリ(データベースバッファ)等のシステムリソースを大量に消費し、所要時間も長いため、他のサーバアプリケーション1305で実行するSQL文に影響を及ぼす可能性がある。
そのため、当該SQL文を実行するサーバアプリケーション1304,1305は、本番環境へリリースする前に、本番環境での当該SQL文による検索所要時間を事前に確認しておくことが望まれる。
【0003】
このような技術に関連し、プログラムの実行時間を予測する方法の先行技術文献としては次の3つの特許文献が挙げられる。
【特許文献1】特開2002−276361号
【特許文献2】特開平9−305556号
【特許文献3】特開平5−3341241号
【発明の開示】
【発明が解決しようとする課題】
【0004】
しかし、本番環境のシステムを使用したSQL文による検索所要時間を予測して確認するテストは、他のテスト要件との日程調整やセキュリティ等の観点から、実施機会が制限されることが多い。
開発環境のシステムを使用することも可能であるが、開発環境のシステムでは本番環境相当のデータ件数を格納することができない場合が多い。この場合、開発環境のシステムはアプリケーション開発に最低限必要なデータだけを抽出した環境となっており、予測結果と本番環境での検索所要時間とは大きく異なる場合が多く、本番環境に近似した検索所要時間を予測することができない。
さらに、CPUクロック数やメモリ(データベースバッファ)サイズ、ディスクI/O速度といったハードウェアの性能が異なる場合が多いので、上記と同様に、本番環境に近似した検索所要時間を予測することができない。
【0005】
本発明の目的は、開発環境でのSQL文による検索所要時間をもとに、検索対象のデータ件数やハードウェア性能が異なった本番環境などでのSQL文によるデータベースの検索所要時間を予測することができる方法及びプログラムを提供することにある。
【課題を解決するための手段】
【0006】
上記目的を達成するために、本発明に係るSQL文によるデータベースの検索所要時間予測方法は、開発環境のシステムにおいて検索所要時間予測対象のSQL文を実行し、当該SQL文が使用した全てのテーブル及び各テーブルのデータレコード件数及び1レコード長の情報を取得し、第1の記憶手段に記憶する第1のステップと、前記SQL文を開発環境のシステムで実行した時の実行計画、解析時間、実行時間、データ読み出し時間、データ読み出しサイズ、検索結果行数に関する情報を取得し、第2の記憶手段に記憶する第2のステップと、検索所要時間の予測対象の新たな環境のシステムと開発環境のシステムとのメモリ転送速度比、ディスク転送速度比、CPUクロック数比、新たな環境でのデータキャッシュサイズ、ソートキャッシュサイズ、ディスクI/O速度に関する情報を取得して第3の記憶手段に記憶する第3のステップと、前記第1〜第3の記憶手段に記憶された情報を読み出し、前記SQL文による前記新たな環境のシステムにおけるデータベースの検索所要時間を予め設定した予測計算式に従って算出して出力する第4のステップとを備えることを特徴とする。
【0007】
また、本発明に係るSQL文によるデータベースの検索所要時間予測プログラムは、第1の記憶手段から、開発環境のシステムにおける検索所要時間予測対象のSQL文を実行結果によって収集された当該SQL文が使用した全てのテーブル及び各テーブルのデータレコード件数及び1レコード長の情報を読出す第1のステップと、第2の記憶手段から、前記SQL文を開発環境のシステムで実行した時の実行計画、解析時間、実行時間、データ読み出し時間、データ読み出しサイズ、検索結果行数に関する情報を読出す第2のステップと、第3の記憶手段から、検索所要時間の予測対象の新たな環境のシステムと開発環境のシステムとのメモリ転送速度比、ディスク転送速度比、CPUクロック数比、新たな環境でのデータキャッシュサイズ、ソートキャッシュサイズ、ディスクI/O速度に関する情報を読出す第3のステップと、これら第1〜第3の記憶手段からの読出し情報に基づき、前記SQL文による前記新たな環境のシステムにおけるデータベースの検索所要時間を予め設定した予測計算式に従って算出して出力する第4のステップとをコンピュータに実行させることを特徴とする。
【発明の効果】
【0008】
本発明によれば、ある環境でSQLを実行した時のSQL文によるデータベースの検索所要時間や、その環境とのハードウェア性能比をもとに、レコード件数やハードウェア性能が異なる環境での当該SQL文によるデータベースの検索所要時間をほぼ正確に予測することができる。
【発明を実施するための最良の形態】
【0009】
以下、本発明を図面に基づいて詳細に説明する。
図1は、本発明に係る方法を実施する所要時間予測計算装置の実施の形態を示す構成図である。
この実施形態の所要時間予測計算装置10は、CPU11、入力装置12、メモリ13、出力装置14を備え、メモリ13には、開発環境レコード件数記憶手段15、開発環境SQL情報記憶手段16、ハードウェア性能比記憶手段17に記憶された情報を読出し、開発環境とは異なる新たなSQL文の実行環境(以下、本番環境と言う)におけるデータベースの検索所要時間(以下、SQL所要時間と略記する)を予測するSQL所要時間予測計算プログラム14が記憶されている。
【0010】
ここで、SQL所要時間を予測する上での前提条件は、以下の通りである。
まず、本番環境でのデータ分布は、開発環境システムとのデータ分布から変化しないと仮定する。テーブル結合は2テーブルまでとし、結合方式は図11の説明図に示すネステッドループ結合、もしくは図12の説明図に示すソート・マージ結合とする。
ネステッドループ結合とは、図11の説明図に示すように、開発環境において外部表Aと内部表Bがあった場合に、(1)まず、外部表Aを検索し、(2)外部表Aの結合項目値をもとに、内部表Bを検索し、(3)結合した検索結果を返す、というものである。
このネステッドループ結合では、開発環境での読み出しデータサイズは、A×Bに近似する。
そのため、外部表Aおよび内部表Bの値が、本番環境でそれぞれm倍およびn倍になった場合、読み出しデータサイズは、mA×nBに近似する。
よって、本番環境での読み出しデータサイズ
≒(開発環境での読み出しデータサイズ)×(mA×nB)÷(A×B)
に近似することになる。
【0011】
ソート・マージ結合とは、図12の説明図に示すように、開発環境において外部表Aと内部表Bがあった場合に、(1)まず、外部表Aを検索し、(2)外部表Bをソートし、(3)内部表Bを検索し、(4)内部表Bをソートし、(5)外部表Aの結合項目値をもとに、内部表Bを検索し、(6)外部表Aの結合項目値をもとに、内部表Bを検索し、結合した検索結果を返す、というものである。
このソート・マージ結合では、開発環境での読み出しデータサイズは、A+Bに近似する。
そのため、外部表Aおよび内部表Bの値が、本番環境ではそれぞれm倍およびn倍になった場合、読み出しデータサイズは、mA+nBに近似する。
よって、本番環境の読み出しデータサイズ
≒(開発環境での読み出しデータサイズ)×(mA+nB)÷(A+B)
に近似することになる。
【0012】
本発明では、開発環境でのSQL実行結果と開発環境とのハードウェア性能比などをもと本番環境でのSQL所要時間を予測する。
なお、SQL所要時間は、以下の合計時間となる。
(1)SQL解析時間
SQLを元にSQL実行計画を決定する。
(2)SQL実行時間
SQL実行計画を元にSQLを実行する。
(3)データ読み出し時間
実行されたSQLにより、データを読み取り、検索結果を返す。
【0013】
本発明では、まず、事前準備作業として、図5のフローチャートに示すような手順で、開発環境における情報を取得し、各記憶手段15,16,17に記憶させる。
まず、所要時間を予測したいSQL文40を開発環境システム20で実行する(ステップ501)。そして、当該SQL実行時のSQLトレースを取得しておく(ステップ502)。
次に、取得したSQLトレースから、SQL文で使用したテーブルを確認し、該当するテーブルの開発環境レコード件数および1レコード長をデータベース上の管理情報から取得する。取得した情報は、開発環境レコード件数記憶手段15に記憶させる(ステップ503)。
また、開発環境SQL情報記憶手段16へ、実行計画、SQL解析時間、SQL実行時間、データ読み出し時間、データ読み出しサイズ、検索結果行数を入力して記憶させる(ステップ504)。
さらに、ハードウェア性能情報記憶手段17へ、本番環境システム30と開発環境システム20とのメモリI/O速度比、本番環境システム30と開発環境システム20とのディスクI/O速度比、本番環境システム30と開発環境システム20とのCPUクロック数比、本番環境システム30のデータバッファサイズ、本番環境システム30でのソートバッファサイズ、本番環境システム30でのディスクI/O速度を入力装置11から入力して記憶させる(ステップ505)。
以上で、事前の情報取得は完了である。
【0014】
これにより、開発環境レコード件数記憶手段15には、図2に示すように、SQL文が使用したテーブル名201毎に、開発環境レコード件数202、1レコード長203の情報が記憶される。
また、開発環境SQL情報記憶手段16には、図3に示すように、所要時間予測SQL301毎に、実行計画302、SQL解析時間303、SQL実行時間304、データ読み出し時間305、データ読み出しサイズ606、検索結果行数307から成る情報が記憶される。
また、ハードウェア性能情報記憶手段17には、図4に示すように、本番環境などの新たな環境401ごとに、開発環境とのメモリI/O速度比402、開発環境とのディスクI/O速度比403、開発環境とのCPUクロック数比404、本番環境のデータバッファサイズ405、本番環境でのソートバッファサイズ406、本番環境でのディスクI/O速度407から成る情報が記憶される。
【0015】
次に、図6〜図10のフローチャートに示す処理によって、本番環境システム30でのSQL実行時間を予測する。
まず、所要時間を予測したいSQL文40をSQL所要時間予測計算プログラム14に入力する(ステップ511)。
これにより、当該プログラム14は、開発環境SQL情報記憶手段16を参照し、当該SQL文40について、実行計画、SQL解析時間、SQL実行時間、データ読み出し時間、データ読み出しサイズの情報を取得する(ステップ512)。
また、当該プログラム14は、ハードウェア性能情報記憶手段17を参照し、メモリI/O速度比、CPUクロック数比、本番環境のデータバッファサイズ、本番環境ソートバッファサイズ、本番環境ディスクI/O速度から成る情報を取得する(ステップ513)。
次に、前記の情報を取得した後、開発環境システム20でのSQL解析時間をCPUクロック数比で除算し、本番環境システム30での当該SQL解析時間を算出する(ステップ514)。
さらに、開発環境システム20でのSQL実行時間をCPUクロック数比で除算し、本番環境システム30での当該SQL実行時間を算出する(ステップ515)。
これまでが、本番環境システム30でのSQL解析時間およびSQL実行時間の算出工程となる。
【0016】
続いて、本番環境システム30でのデータ読み出し時間を算出する。
まず、当該プログラム14は、入力したSQL文40から当該SQL文40が使用するテーブル名を取得する(ステップ516)。
次に、当該プログラム実行者による入力装置12の入力操作により、SQL文40が使用するテーブルの本番環境のレコード件数と本番環境のデータバッファサイズを受付ける(ステップ517)。
すると、当該プログラム14は、入力された「SQL文40が使用するテーブル数」が単一テーブルの検索か否かを確認する(ステップ521)。
テーブル数が1つの場合、以下の「数式1」より、本番環境システム30でのデータ読み出し時間を算出する(ステップ522)。
【数1】
【0017】
次に、前記の「数式1」から、本番環境システム30でのデータ読み出し時間を算出したが、ディスクI/Oが発生する場合は、その分読み出し時間が長くなる。そのため、ディスクI/Oが発生する可能性があるかを確認する。
そこで、次の「数式2」により、本番環境システム30でのデータ読み出しサイズを算出する(ステップ523)。
【数2】
【0018】
次に、本番環境システム30でのデータ読み出しサイズが、本番環境システム30のデータバッファサイズより小さいか確認する(ステップ531)。
もし、データ読み出しサイズの方が大きい場合は、ディスクI/Oが発生するため、次の「数式3」により、本番環境システム30でのデータ読み出し時間を修正する(ステップ532)。
【数3】
【0019】
さらに、ソート処理があるかを確認する。この時、本番環境システム30でのデータ読み出しサイズが本番環境システム30のソートバッファサイズより小さいかもあわせて確認する(ステップ533)。
ソート処理があり、本番環境システム30でのデータ読み出しサイズが本番環境のソートバッファサイズより小さい場合は、ディスクソートが発生するため、以下の「数式4」より、本番環境システム30でのデータ読み出し時間を修正する(ステップ534)。
【数4】
【0020】
以上の工程により、単一テーブルの場合の本番環境システム30でのデータ読み出し時間の予測は完了である。
そして、算出した本番環境システム30でのSQL解析時間、SQL実行時間およびデータ読み出し時間の合計が、本番環境システム30での単一テーブルのSQL実行時間となる。
【0021】
次に、2テーブル結合の場合のデータ読み出し時間の算出方法について説明する。
まず、テーブル結合方式を確認する(ステップ541)。
テーブル結合方式がソート・マージ結合の場合は、図12の説明図で説明したように、結合するテーブルをそれぞれソートした後に、結合項目によって結合を行い、本番環境システム30での読み出しデータサイズを算出する(ステップ542)。
この場合、本番環境システム30での読み出しデータサイズは、以下の「数式5」式より算出した値に近似される。
【数5】
【0022】
さらに、本番環境システム30のハードウェア性能比を考慮するため、前記で算出した本番環境でのデータ読み出し時間を以下の「数式6」より修正する(ステップ543)。
【数6】
【0023】
続いて、単一テーブルの場合と同様に、データバッファおよびソートバッファでのディスクI/Oがあるかを確認し、必要に応じて本番環境システム30でのデータ読み出し時間を修正する。
一方、ネステッドループの場合は、図11の説明図で説明したように、外部表Aの各レコードの結合項目をもとに内部表Bを検索する。
この場合、本番環境システム30での読み出しデータサイズは、以下の「数式7」により算出した値に近似される(ステップ544)。
【数7】
【0024】
さらに、ソート・マージの場合と同様に、本番環境システム30のハードウェア性能比を考慮するため、前記で算出した本番環境システム30でのデータ読み出し時間を以下の「数式8」より修正する(ステップ545)。
【数8】
【0025】
前記で算出した本番環境システム30でのデータ読み出し時間は、ディスクI/Oが発生する場合を考慮されていないので、本番環境システム30でのデータ読み出しサイズが本番環境システム30のデータバッファサイズより小さいか確認する(ステップ546)。
【0026】
本番環境システム30でのデータ読み出しサイズが本番環境システム30のデータバッファサイズより大きい場合、以下の「数式9」により、本番環境システム30でのデータ読み出し時間を修正する(ステップ547)。
【数9】
【0027】
前記の修正後は、ソート時のディスクI/Oを確認する(ステップ551)。
ソート処理がある場合は、ネステッドループでは、結合したテーブルの一致した行のみをソートするので、一致した行は、検索結果行数×(外部表の1レコード長+内部表の1レコード長)×本番環境のレコード件数÷開発環境のレコード件数となる。上記の情報は、開発環境SQL情報記憶手段16と開発環境レコード件数記憶手段15より取得する。
そこで、以下の「数式10」により、ネステッドループにおける本番環境システム30での想定ソートサイズを算出する(ステップ552)。
【数10】
【0028】
さらに、前記で算出した本番環境システム30での想定ソートサイズが本番環境システム30のソートバッファサイズより小さいかを確認する(ステップ553)。
本番環境システム30での想定ソートサイズが本番環境システム30のソートバッファサイズより大きい場合は、以下の「数式11」により、本番環境システム30でのデータ読み出し時間を修正する(ステップ554)。
【数11】
以上で、2テーブル結合時の本番環境システム30でのデータ読み出し時間は完了である。
【0029】
(具体例)
以下に、この発明の具体例を挙げて説明する。
まず、本番環境で所要時間を予測したいSQL文を開発環境システム20で実行する。実行するSQL文は、図3の1行目に記載している「select * from A;」とする。このSQL文の情報は、当該SQL文実行時のSQLトレースから取得し、図3の開発環境SQL情報記憶手段16へ入力する。
入力内容は、図3の1行目に記載の通り、実行計画はテーブルAの検索、SQL解析時間は0.01秒、SQL実行時間は0.01秒、データ読み出し時間は5.00秒、データ読み出しサイズは5MB、検索結果行数は10000件となる。
【0030】
次に、ハードウェア性能情報記憶手段17へ情報を入力する。
図4の1行目に記載の通り、開発環境システム20とのメモリI/O速度比は1、開発環境システム20とのディスクI/O速度比は5、開発環境システム20とのCPUクロック数比は2、本番環境システム30でのデータバッファサイズは1GB、本番環境システム30でのソートバッファサイズは10MB、本番環境システム30でのディスクI/O速度は100MB/secとなる。
【0031】
次に、本番環境システム30でのSQL解析時間とSQL実行時間を算出する。開発環境システム20での値は、それぞれ0.01秒であるため、それぞれCPUクロック数比で除算すると、0.01秒(SQL解析時間、もしくは、SQL実行時間)÷2(CPUクロック数比)=0.005秒となる。
【0032】
さらに、データ読み出し時間を算出するため、本番環境のレコード件数を入力する。なお、当該SQLは、単一テーブル検索であるため、開発環境のデータ読み出し時間に、本番環境システム30と開発環境システム20のレコード件数比率を乗算し、メモリI/O速度比とCPUクロック数比で除算する。
開発環境システム20のデータ読み出し時間が、5.00秒であるため、5.00秒(開発環境のデータ読み出し時間)×50000(本番環境のレコード件数)÷10000(開発環境のレコード件数)÷1(メモリI/O速度比)÷2(CPUクロック数比)=12.5秒となる。
また、当該テーブルの本番環境システム30でのデータ読み出しサイズは、5MB(開発環境でのデータ読み出しサイズ)×50000(本番環境のレコード件数)÷10000(開発環境のレコード件数)=25MBとなる。
これは、本番環境システム30のデータバッファサイズ1GBよりも小さいため、ディスクI/Oは発生しない。よって、前記で算出した本番環境システム30でのデータ読み出し時間は修正不要である。
また、当該SQLでは、ソートが発生しないため、当該SQLの本番環境システム30でのデータ読み出し時間算出は、以上で完了となる。
よって、当該SQL文の本番環境システム30での実行時間は、12.5秒であると予測される。
【図面の簡単な説明】
【0033】
【図1】本発明を実施する計算装置の実施の形態を示す構成図である。
【図2】開発環境レコード件数記憶手段の記憶内容の例を示す図である。
【図3】開発環境SQL情報記憶手段の記憶内容の例を示す図である。
【図4】ハードウェア性能情報記憶手段の記憶内容の例を示す図である。
【図5】SQL所要時間を予測するプログラムの処理を示すフローチャートである。
【図6】図5の続きを示すフローチャートである。
【図7】図6の続きを示すフローチャートである。
【図8】図7の続きを示すフローチャートである。
【図9】図8の続きを示すフローチャートである。
【図10】図9の続きを示すフローチャートである。
【図11】ネステッドループ処理の概要説明図である。
【図12】ソート・マージ処理の概要説明図である。
【図13】従来におけるCRMシステムの概要を示すシステム構成図である。
【符号の説明】
【0034】
10 所要時間予測計算装置
14 SQL所要時間予測計算プログラム
15 開発環境レコード件数記憶手段
16 開発環境SQL情報記憶手段
17 ハードウェア性能比記憶手段
20 開発環境システム
30 本番環境システム
40 SQL文
【特許請求の範囲】
【請求項1】
開発環境のシステムにおいて検索所要時間予測対象のSQL文を実行し、当該SQL文が使用した全てのテーブル及び各テーブルのデータレコード件数及び1レコード長の情報を取得し、第1の記憶手段に記憶する第1のステップと、前記SQL文を開発環境のシステムで実行した時の実行計画、解析時間、実行時間、データ読み出し時間、データ読み出しサイズ、検索結果行数に関する情報を取得し、第2の記憶手段に記憶する第2のステップと、検索所要時間の予測対象の新たな環境のシステムと開発環境のシステムとのメモリ転送速度比、ディスク転送速度比、CPUクロック数比、新たな環境でのデータキャッシュサイズ、ソートキャッシュサイズ、ディスクI/O速度に関する情報を取得して第3の記憶手段に記憶する第3のステップと、前記第1〜第3の記憶手段に記憶された情報を読み出し、前記SQL文による前記新たな環境のシステムにおけるデータベースの検索所要時間を予め設定した予測計算式に従って算出して出力する第4のステップとを備えることを特徴とするSQL文によるデータベースの検索所要時間予測方法。
【請求項2】
第1の記憶手段から、開発環境のシステムにおける検索所要時間予測対象のSQL文を実行結果によって収集された当該SQL文が使用した全てのテーブル及び各テーブルのデータレコード件数及び1レコード長の情報を読出す第1のステップと、第2の記憶手段から、前記SQL文を開発環境のシステムで実行した時の実行計画、解析時間、実行時間、データ読み出し時間、データ読み出しサイズ、検索結果行数に関する情報を読出す第2のステップと、第3の記憶手段から、検索所要時間の予測対象の新たな環境のシステムと開発環境のシステムとのメモリ転送速度比、ディスク転送速度比、CPUクロック数比、新たな環境でのデータキャッシュサイズ、ソートキャッシュサイズ、ディスクI/O速度に関する情報を読出す第3のステップと、これら第1〜第3の記憶手段からの読出し情報に基づき、前記SQL文による前記新たな環境のシステムにおけるデータベースの検索所要時間を予め設定した予測計算式に従って算出して出力する第4のステップとをコンピュータに実行させることを特徴とするSQL文によるデータベースの検索所要時間予測プログラム。
【請求項1】
開発環境のシステムにおいて検索所要時間予測対象のSQL文を実行し、当該SQL文が使用した全てのテーブル及び各テーブルのデータレコード件数及び1レコード長の情報を取得し、第1の記憶手段に記憶する第1のステップと、前記SQL文を開発環境のシステムで実行した時の実行計画、解析時間、実行時間、データ読み出し時間、データ読み出しサイズ、検索結果行数に関する情報を取得し、第2の記憶手段に記憶する第2のステップと、検索所要時間の予測対象の新たな環境のシステムと開発環境のシステムとのメモリ転送速度比、ディスク転送速度比、CPUクロック数比、新たな環境でのデータキャッシュサイズ、ソートキャッシュサイズ、ディスクI/O速度に関する情報を取得して第3の記憶手段に記憶する第3のステップと、前記第1〜第3の記憶手段に記憶された情報を読み出し、前記SQL文による前記新たな環境のシステムにおけるデータベースの検索所要時間を予め設定した予測計算式に従って算出して出力する第4のステップとを備えることを特徴とするSQL文によるデータベースの検索所要時間予測方法。
【請求項2】
第1の記憶手段から、開発環境のシステムにおける検索所要時間予測対象のSQL文を実行結果によって収集された当該SQL文が使用した全てのテーブル及び各テーブルのデータレコード件数及び1レコード長の情報を読出す第1のステップと、第2の記憶手段から、前記SQL文を開発環境のシステムで実行した時の実行計画、解析時間、実行時間、データ読み出し時間、データ読み出しサイズ、検索結果行数に関する情報を読出す第2のステップと、第3の記憶手段から、検索所要時間の予測対象の新たな環境のシステムと開発環境のシステムとのメモリ転送速度比、ディスク転送速度比、CPUクロック数比、新たな環境でのデータキャッシュサイズ、ソートキャッシュサイズ、ディスクI/O速度に関する情報を読出す第3のステップと、これら第1〜第3の記憶手段からの読出し情報に基づき、前記SQL文による前記新たな環境のシステムにおけるデータベースの検索所要時間を予め設定した予測計算式に従って算出して出力する第4のステップとをコンピュータに実行させることを特徴とするSQL文によるデータベースの検索所要時間予測プログラム。
【図1】
【図2】
【図3】
【図4】
【図5】
【図6】
【図7】
【図8】
【図9】
【図10】
【図11】
【図12】
【図13】
【図2】
【図3】
【図4】
【図5】
【図6】
【図7】
【図8】
【図9】
【図10】
【図11】
【図12】
【図13】
【公開番号】特開2007−172516(P2007−172516A)
【公開日】平成19年7月5日(2007.7.5)
【国際特許分類】
【出願番号】特願2005−372642(P2005−372642)
【出願日】平成17年12月26日(2005.12.26)
【出願人】(000233055)日立ソフトウエアエンジニアリング株式会社 (1,610)
【Fターム(参考)】
【公開日】平成19年7月5日(2007.7.5)
【国際特許分類】
【出願日】平成17年12月26日(2005.12.26)
【出願人】(000233055)日立ソフトウエアエンジニアリング株式会社 (1,610)
【Fターム(参考)】
[ Back to top ]