Hitachi

ノンストップデータベース　HiRDB Version 10 システム運用ガイド（UNIX(R)用）

23.9.1　SQLのチューニング方法

ここでは，実行時間が長いSQL，又は入出力処理が多いSQLのチューニング方法について説明します。

SQLのチューニングの流れを次の図に示します。

図23‒2　SQLのチューニングの流れ

[図データ]

[図データ]
注

処理ボックスの左にある数字はこの後で説明している（　）レベルに対応しています。例えば，5の操作は(5)で説明しています。

〈この項の構成〉

(1)　チューニング目標と監視項目の警告値を決定する

チューニング目標と監視項目の警告値を決定します。システム全体のパフォーマンス（アプリケーションの処理時間，トランザクションコミット数など）を決めて，それに対するチューニング目標を決定してください。また，次に示す監視項目の警告値を決定してください。

ページの先頭へ

(2)　ハードウェアリソースの利用率を監視する

サーバマシンのプロセッサやディスクなどのハードウェアリソースの利用率が高くなったことをチューニングの起点とします。ハードウェアリソースの利用率が高くなると，システムのスループットが低下すると考えられます。そのため，ハードウェアリソースの利用率を監視します。

OSのsarコマンドなど（Linuxの場合にはvmstatコマンドなど）を使用すると，サーバマシンのプロセッサやディスクなどのハードウェアリソースの利用率が高いハードウェアを特定できます。

参考

  • JP1/Performance Management - Agent Option for Platformを使用すると，ハードウェアのリソース利用率を確認できます。JP1/Performance Management - Agent Option for Platformについては，マニュアル「JP1/Performance Management - Agent Option for Platform（UNIX(R)用）」を参照してください。

  • 日立ディスクアレイシステムを使用している場合は，Hitachi Tuning Manager - Agent for RAIDを使用すると，ディスクアレイ装置のパフォーマンスデータを収集できます。Hitachi Tuning Manager - Agent for RAIDについては，マニュアル「Hitachi Tuning Manager - Agent for RAID」を参照してください。

ページの先頭へ

(3)　グローバルバッファプールのヒット率を監視する

グローバルバッファプールのヒット率が低下したことをチューニングの起点とします。pdbuflsコマンドを実行すると，グローバルバッファプールのヒット率を確認できます。

参考

JP1/Performance Management - Agent Option for HiRDBを使用しても，グローバルバッファプールのヒット率を確認できます。JP1/Performance Management - Agent Option for HiRDBについては，マニュアル「JP1/Performance Management - Agent Option for HiRDB」を参照してください。

ページの先頭へ

(4)　アプリケーションのスループット及びSQL実行時間を監視する

アプリケーションのスループットが低下したことをチューニングの起点とします。統計解析ユティリティ（pdsteditコマンド）のシステムの稼働に関する統計情報を取得すると，アプリケーションのスループットを確認できます。

また，SQL実行時間警告出力機能を使用して，SQLの実行時間を監視してください。SQL実行時間警告出力機能については，「SQL実行時間についての警告情報の出力（SQL実行時間警告出力機能）」を参照してください。

参考

JP1/Performance Management - Agent Option for HiRDBを利用しても，アプリケーションのスループットを確認できます。

ページの先頭へ

(5)　表の検索効率や格納効率を監視する

表のデータ配置に乱れが生じていることをチューニングの起点とします。データの検索効率や格納効率が低下した表については，KFPH00212-I又はKFPH22017-Iメッセージが出力されるため，これらのメッセージを監視してください。

参考

  • 表の再編成が必要になる日をHiRDBが予測する再編成時期予測機能を提供しています。再編成時期予測機能については，「表の再編成時期を予測する方法（再編成時期予測機能）」を参照してください。

  • JP1/Performance Management - Agent Option for HiRDBを使用しても，データの検索効率や格納効率が低下した表に関するメッセージを取得できます。

ページの先頭へ

(6)　問題を確認する

(2)〜(4)で監視していた監視項目が警告値を超えた場合，チューニングを行います。OS又はHiRDBのコマンドで監視項目のデータを取得した場合は，以前に取得したデータと現在取得したデータを比較して，警告値を超えたかどうか判断してください。

警告値を超えたハードウェアリソースがプロセッサの場合，OSのコマンドを使用して，HiRDBのプロセスがプロセッサを多く使用しているかどうかを確認してください。

SQL実行時間警告出力機能の警告が出力された場合，(8)に進みチューニングを行ってください。

(5)で監視していた表のデータ配置に乱れが生じてメッセージが出力された場合，(18)に進みチューニングを行ってください。

参考

JP1/Performance Management - Agent Option for Platform，JP1/Performance Management - Agent Option for HiRDB，又はHitachi Tuning Manager - Agent for RAIDを使用した場合，監視項目のデータに警告値を指定して監視できます。また，その警告値を超えた場合，警告を発する機能があります。

ページの先頭へ

(7)　HiRDBファイルシステム領域を特定する

(6)でハードウェアリソースや，グローバルバッファプールのヒット率が，警告値を超えた場合，そのハードウェアリソースがどのHiRDBファイルシステム領域に関係するかを特定します。警告値を超えたハードウェアリソースや，グローバルバッファプールとHiRDBファイルシステム領域の種類との関係情報を取得するには，各レイヤ間の情報をまとめる必要があります。

ディスクボリュームとHiRDBファイルシステム領域間，HiRDBファイルシステム領域とHiRDBファイル間，及びHiRDBファイルとRDエリア間の関係は，ディクショナリ表のSQL_PHYSICAL_FILESを調べると特定できます。また，インナレプリカ機能を使用している場合は，SQL_IOS_GENERATIONS表を調べてください。

作業表用ファイル用のHiRDBファイルシステム領域の場合は，pdworkオペランドの指定値を調べるとHiRDBファイルシステム領域名が分かります。さらに，ディクショナリ表のSQL_TABLES表又はSQL_INDEXES表を調べると，RDエリアと表（又はインデクス）の関係を特定できます。

参考

  • HiRDB CMを使用すると，次の図に示すように，ディスクボリュームから表（又はインデクス）までの関係情報を取得できます。

  • 日立ディスクアレイシステムを使用している場合，Hitachi Tuning Manager - Storage Mapping Agentを使用すると，ディスクアレイシステム内のポートや，論理デバイス間の関係情報を取得できます。Hitachi Tuning Manager - Storage Mapping Agentについては，マニュアル「Hitachi Tuning Manager - Storage Mapping Agent」を参照してください。

    図23‒3　HiRDB CMによる関係情報の取得例

    [図データ]

ページの先頭へ

(8)　グローバルバッファプールのバッファ面数のチューニングをする

グローバルバッファプールのバッファ面数が小さいため，システムのスループットが低下したり，ハードウェアリソースの利用率が高くなったりしている可能性があります。

(7)で特定したHiRDBファイルシステム領域と関係のあるグローバルバッファプールのバッファ面数のチューニングを行ってください。グローバルバッファプールのヒット率が低下したことがチューニングの起点の場合は，該当するグローバルバッファプールのバッファ面数をチューニングしてください。アプリケーションのスループット及びSQLの実行時間がチューニングの起点の場合は，すべてのグローバルバッファプールのバッファ面数をチューニングしてください。

グローバルバッファプールのバッファ面数については，マニュアル「HiRDB システム導入・設計ガイド」を参照してください。グローバルバッファプールのチューニングについては，「グローバルバッファプールのチューニング」を参照してください。

ページの先頭へ

(9)　チューニング目標を達成したか確認する

(8)で行ったチューニングによって，チューニング目標を達成した場合は，チューニングを終了します。達成できていない場合は，次の手順に進んでチューニングを続けてください。

ページの先頭へ

(10)　チューニング対象のSQLを特定する

チューニング対象のSQLを特定します。また，そのSQLのチューニング目標を設定してください。

pdobilsコマンドでSQLオブジェクト用バッファの統計情報を取得し，次に示す状況のSQLをチューニング対象とします。SQLオブジェクト用バッファの統計情報を取得すると，各SQLの実行時間，実行回数，及び入出力回数が確認できます。

また，各SQLの目標実行時間，目標実行回数，及び目標入出力回数をチューニング目標として決めてください。

ページの先頭へ

(11)　チューニング対象のSQLに排他待ちが発生しているか確認する

統計解析ユティリティ（pdsteditコマンド）のシステムの稼働に関する統計情報を取得して，排他待ちが発生しているか確認してください。排他待ちが発生している場合，サーバの排他制御の状態を定期的に取得し，排他待ちが発生しているか確認します。また，排他待ちが発生している資源を該当するSQLが使用している場合は，チューニング対象のSQLに排他待ちが発生していると判断できます。

排他待ちが発生している場合は，(12)でアプリケーションのチューニング（排他制御のチューニング）を行って排他待ちを解消してください。排他待ちが発生していない場合は，(14)で最適化情報の見直しを行ってください。

ページの先頭へ

(12)　アプリケーションのチューニング（排他制御のチューニング）をする

チューニング対象のSQLに排他待ちが発生している場合，アプリケーションのチューニング（排他制御のチューニング）を行います。アプリケーションのチューニング（排他制御のチューニング）については，マニュアル「HiRDB UAP開発ガイド」の排他制御を参照してください。

ページの先頭へ

(13)　排他待ちが解消したか確認する

アプリケーションのチューニングによって，チューニング対象のSQLに発生していた排他待ちが解消したか確認してください。確認方法については，(11)を参照してください。

排他待ちが解消した場合は，(17)に進みチューニングの目標を達成したか確認してください。排他待ちが解消しない場合は，(14)に進み最適化情報の見直しを行ってください。

ページの先頭へ

(14)　最適化情報の見直し

最適化情報を収集，登録しているかどうかを確認してください。最適化情報収集ユティリティの最適化情報パラメタファイルへの最適化情報の退避を実行して，対象となるSQLで使用している表の最適化情報が，ディクショナリ表に登録されているかどうかで確認できます。最適化情報パラメタファイルへの最適化情報の退避については，「最適化情報パラメタファイルへの最適化情報の退避」を参照してください。

確認の結果，最適化情報を収集，登録していない場合は，(15)のSQLのチューニングへ進んでください。

最適化情報を収集，登録している場合は，次のどれかの対処をしてください。

ページの先頭へ

(15)　SQLのチューニングをする

SQLのチューニング方法を次の表に示します。

表23‒1　SQLのチューニング方法

SQLの特性

想定要因

詳細

対策

データページ，インデクスページ，ディレクトリページの実READ回数，又は参照回数の合計回数が多い（設計時よりも入出力回数が多い，又は設計時よりも実行時間が長い）

表へのアクセスパスが不適切

表の検索範囲が広いのにテーブルスキャンを実施している

マニュアル「HiRDB コマンドリファレンス」のアクセスパス表示ユティリティのチューニングの考え方を参照してください。

絞り込める条件を指定している検索で，テーブルスキャンを実施している（利用されるインデクスが適切でない）

インデクスの検索範囲が広い（必要以上の範囲検索を実施している）

サーチ条件がない，又はサーチ条件が有効でないためインデクスのサーチ範囲が絞り込めていない

ANDを使用して複数の述語を指定した検索を実施している

結合処理方法が不適切

ネストループジョイン時の内表の結合キーのインデクスが適切でない

外結合するときの利用されるインデクスが適切でない

ネストループジョイン時の転送方法がBROADCAST転送又はKEY RANGE PARTIAL BROAD CAST転送になっている（HiRDB/パラレルサーバの場合）

作業表用ファイルへのREAD回数，又はWRITE回数がある（設計時よりも実行時間が長い）

結合処理方法が不適切

ハッシュジョインの内表件数や副問合せの検索件数が多いため，バケット分割が複数回実行されている

マニュアル「HiRDB UAP開発ガイド」のハッシュジョイン，副問合せのハッシュ実行を適用する場合の準備を参照してください。

ソートマージジョインの実施で，大量のデータをソートしている

マニュアル「HiRDB コマンドリファレンス」のアクセスパス表示ユティリティのチューニングの考え方を参照してください。

クロスジョインを実施している

ソート処理実施

ORDER BY処理のためにソート処理を実施している

ページの先頭へ

(16)　SQLのチューニング目標を達成したか確認する

チューニング対象のSQLがチューニング目標を達成したか確認してください。SQLオブジェクト用バッファの統計情報を取得し，各SQLの実行時間，実行回数，入出力回数が目標以内であるか確認してください。目標を達成できない場合は，(15)に戻って再度SQLのチューニングを行ってください。

ページの先頭へ

(17)　チューニング目標を達成したか確認する

チューニング目標を達成したか確認してください。目標を達成した場合はチューニングを終了します。目標を達成できない場合は，(10)の手順に戻ってチューニング対象のSQLを再度特定し，チューニングを行ってください。

SQLのチューニング，又はアプリケーションのチューニングで効果が出ない場合は，(18)に進みデータベースのチューニングを行ってください。

ページの先頭へ

(18)　データベースのチューニングをする

SQLのチューニング，又はアプリケーションのチューニングで効果が出ない場合は，データベースのチューニングを実施します。次に示すチューニングを行ってください。

ページの先頭へ

(19)　チューニングの目標を達成したか確認する

データベースのチューニングによって，チューニングの目標を達成したか確認してください。目標を達成した場合はチューニングを終了します。目標を達成できない場合は，(10)に戻ってチューニング対象のSQLを再度特定し，チューニングを行ってください。

データベースのチューニングで効果が出ない場合は，次に進みハードウェアリソースのチューニングを行ってください。

ページの先頭へ

(20)　ハードウェアリソースのチューニングをする

ハードウェアリソースを追加又は増強します。OSのsarコマンドなど（Linuxの場合にはvmstatコマンドなど）を使用して，プロセッサの利用率，及びディスクの利用率を取得し，利用率が最も高いハードウェアリソースを追加するか，又は増強してください。

利用率が最も高いハードウェアリソースがプロセッサの場合は，より高性能なプロセッサに変更すると処理時間を短縮できます。また，利用率が最も高いハードウェアリソースがディスクの場合は，より高性能なディスクに変更するか，又はディスクアレイ装置を使用しているときはディスクを追加（スピンドル数を増加）すると，入出力時間を短縮できます。

参考

  • JP1/Performance Management - Agent Option for Platformを利用してもプロセッサや，ディスクボリュームの利用率を確認できます。

  • 日立ディスクアレイシステムを使用している場合，Hitachi Tuning Manager - Agent for RAIDを使用すると，ディスクアレイシステムのパフォーマンスデータを収集できます。

ページの先頭へ

商品名称に関する表示

All Rights Reserved. Copyright (C) 2018, 2022, Hitachi, Ltd.