X

オラクルエンジニア通信では、オンプレミスからクラウドまで、オラクルテクノロジーの最新情報をお届けします

SQLパフォーマンス・チューニング 始めの一歩

当コラムでは、パフォーマンスでお困りの皆様に、パフォーマンス問題への対処方法をガイドいたします。筆者はOracle Directでパフォーマンス関連のご相談を承っているエンジニアです。皆様からお伺いしたパフォーマンス関連のお悩みを元に、初心者をターゲットにして実際のチューニング作業の全体イメージをつかんで頂くことを主眼としております。また、チューニング作業を行う上で、Statspackというツールを使用する方法についてご紹介します。

 

目次

 

資料でお読みになりたい方へ

当コラムの内容をカバーしていますので、ぜひご覧ください。

 

チューニング作業の流れ

 

いざチューニング、その前に

パフォーマンスが落ちた、という時に、まず最初に確認するのは、業務要件やシステム要件です。こちらにはシステムを構築する際に参考にされる目標、例えば「どれくらいでレスポンスが返る必要があるのか」などが示されています。

具体的には、バッチ処理は午前7時までに終了する、ですとか、WEBページの検索結果は3秒以内で表示する、といったことです。

この数値が最終的なパフォーマンス・チューニングのゴールになります。

また、システム構築当初は問題なかったのに、運用をしていくうちにデータ量やユーザ数が増えてレスポンスが低下してしまった、という場合もあります。この時、当初に比べてレスポンスが低下してしまうということで、体感としては「遅い」となるのですが、最初に定義されているシステム要件の範囲内であれば、まずはチューニングの対象から外す、という判断をすることもできます。「遅い」というのは主観的な感覚でもあるので、明確な数値の定義を元にチューニングターゲットを決め、また達成すべき目標も明確に定義します。そうすることで、ある意味きりのないパフォーマンス・チューニングの範囲を区切り、成果を明確にすることができます。

 

実際に何をしていけばいいのか

チューニングの対象を明確にし、達成すべき目標も確認すれば、いよいよ目標に向かって作業を進めていくことになります。
ここで、下図をご覧下さい。チューニング作業の一連の流れを示しています。

図1 チューニング作業の流れ

図1 チューニング作業の流れ

要件を確認して、達成すべきゴールを確認したら、チューニング作業に入ります。

図1をご覧頂いておわかりのように、チューニング作業というのはまず問題が発生しているのは何が原因なのかを判別します(モニタリング+ボトルネック特定)。そして、その問題に対する対応措置を実施していきます(適切なチューニングの実施)。

問題がどこで発生しているのかを特定するために、状況の確認をすることをモニタリングといいます。モニタリングの手法としては、何を対象としているのかで方法が異なります(後述)。結果、ボトルネックとなる原因が特定できれば、それに対して対応措置を施します。例えば、キャッシュヒット率が低いというのがボトルネックならば、メモリサイズを大きくする、というのが一つの対応措置になります。

対応措置を施した結果、最初に確認した目標値をクリアしたかどうかを確認します(効果をチェック)。ここで目標達成していればチューニングは終了、まだ達成していなければ更に問題がどこにあるのかを特定するために再度モニタリングをして、対応措置を施し効果を確認・・・を繰り返していきます。目標に達成するまでこの循環が続くことになります。

 

モニタリング手法

ボトルネックを判別するときに、Oracle Databaseの状況をモニタリングするにはいくつかの方法があります。Oracle Databaseの全体の稼動状況を確認するには、こちらの2つの方法がよく使用されます。

  • Statspack
  • Oracle Enterprise Manager 10g

Statspack とは、Oracle 8i(8.1.6)からご利用頂けるOracle Databaseの稼動状況レポートのことです。Statspackは、"Statistic"と"Package"を合わせて作られた名称です。Statisticとは統計値、PackageとはPL/SQLコードやSQLスクリプトの集合をさしています。まさに言葉の通り、Statspackとは統計値を表示するためのスクリプト群のことです。ライセンスに関係なく、Standard Edition One, Standard Edition, Enterprise Edition全てのライセンスでご利用いただけます。データベースが行っている処理の中で、時間がかかっているのはどの処理なのか、メモリのヒット率はどれくらいか、SQLの中で時間がかかっているのはどれか...など様々な情報をレポートとして作成してくれます。

また、Oracle Database 10gからはGUIの運用管理ツールであるOracle Enterprise Manager 10gがお勧めです。もちろんOracle Database 10g 以降もStatspackはご利用頂けるのですが、Statspackは数字や文字といったデータの羅列で、必要な情報を読み解くのには知識や経験が求められます。一方Oracle Enterprise Manager10gでは、データベースの稼動情報レポートがグラフで表示されたり、ボトルネック項目に対して対応措置のアドバイスをしてくれます。ボトルネック診断やアドバイスはEnterprise EditionにDiagnostic pack、Tuning Packといった別途ライセンスが必要になり、Oracle Database 10g以降が対象のバージョンとなります。

両者の比較については、こちらのサイトで詳しく紹介されていますので、興味がおありの方は是非ご一読ください。

Oracleデータベース性能対策機能 ~StatspackとDiagnostics Packを使いこなす~

▲ ページTOPに戻る

 
 

Statspack

パフォーマンスが落ちた、といってOracle Directにご相談頂く機会は実はたくさんあります。その時に、まず状況確認のためにご案内するのがStatspackレポートを取得して頂くことです。

Enterprise Manager 10gをご利用頂ければもちろん最もよいのですが、利用できるバージョンが10g(10.1.0)以上であることや、ライセンスが必要になる機能を利用することになるため、全てのお客様でご利用頂ける稼動状況診断としてStatspackをご案内しています。上述しましたが、StatspackならOracle 8i(8.1.6)から使用可能ですし、ライセンスは問わずStandard Edition Oneからご利用頂けます。

パフォーマンスでお困りのお客様にStatspackをご案内すると、よく頂くご心配としては大きく以下の2つがあります。

  • Statspackを取得するのは難しいんじゃないのか
  • ただでさえパフォーマンスに困っているのに、稼動情報レポートを取得することで余計な負荷が加わるんじゃないのか

これらについて、一つずつ見ていきましょう。

 

Statspackは難しい!?

確かに、取得されたStatspackレポートは文字と数字が一覧でずらっと表示されて、読み解くにはある程度のスキルが必要となってきます。しかし、レポートを抽出するという部分までは、実はそんなに難しい作業をしているわけではないのです。Statspackを取得するまでの一連の流れを見てみましょう。

alt="図2 Statspack取得の流れ" width="500" height="274">

図2 Statspack取得の流れ

ご覧頂いてお分かりのように、データベースに対して行っている作業の内容は、表領域の作成・ユーザの作成・表の作成など、通常の運用で行う作業と同じです。また、Statspack用のユーザの作成と表の作成は既存環境に存在するStatspackインストール用スクリプトで自動的に作成されますので、実際に手を動かして作成する必要があるのは表領域のみということになります。

表領域の作成は、SQLコマンドを用いて作成することももちろん可能ですが、Enterprise Managerの無償のライセンス範囲で操作して頂ける範疇になります。Standard EditionやStandard Edition Oneをご利用の方でも、表領域の作成はEnterprise ManagerからGUIの操作で行うことが可能です。また、Oracle Database 10g以前のバージョンであっても、対応するEnterprise Managerから表領域を作成することが可能です。ご参考までに、Enterprise Manager 10gとEnterprise Manager 9iから表領域を作成する画面をご紹介します。表領域の作成においてのEnterprise Manager 11gの画面はEnterprise Manager 10gのものとほとんど変わりません。

alt="図3 Enterprise Manager 10g 表領域作成画面" width="351" height="268">

図3 Enterprise Manager 10g 表領域作成画面

alt="図4 Enterprise Manager 9i 表領域作成画面" width="429" height="408">

図4 Enterprise Manager 9i 表領域作成画面

GUIを利用するか、SQLコマンドを利用するか方法は問いませんが、まずはStatspackのデータが必要な表領域を作成します。その後でインストール作業になるのですが、Statspackのインストール用スクリプトはデータベースインストール時に同時に配布されているので、実は既存環境に存在しています。それを実行するだけで専用ユーザや表は作成されますので、Statspackの事前準備は完了です。

準備が整ったら、実際に稼動情報を取得し、レポートを作成する作業に移ります。稼動情報のことをスナップショットと呼びます。スナップショットの取得やレポート作成も、既存のプロシージャを呼び出すだけで簡単に実行することができます。

このように実際の作業として行って頂くのは、通常データベースの運用管理でやっているのと同じように、表領域を作成することと、既存環境に存在するスクリプトを実行することでレポートの作成までを行うことになります。

具体的な手順については、既出のコラムに詳細が記載されております。

Oracleデータベース性能対策機能 ~StatspackとDiagnostics Packを使いこなす~インストールレポート作成

 

余計な負荷がかかるんじゃないの!?

確かに現在システムのパフォーマンスにお困りの方に相談をお受けしているのですから、当然こういった疑問は出てくることでしょう。

事前準備としてStatspackのインストールをしていただきますが、Statspack をインストールしたからといって、サーバーの負荷に大きな影響を与えるといった事は特にございません。

こちらの作業イメージ図をご覧下さい。

alt="図5 Statspackレポート作成までの流れ" width="500" height="167">

図5 Statspackレポート作成までの流れ

図5は、軸が右に進むに連れて時間が経過していくことを表しています。また、水色の網掛けで示している時間対はシステムにかかる負荷が少ない・もしくは通常時であることを示し、赤い網掛けで示している部分はシステムにかかる負荷がピークである状態を示したイメージ図です。

インストール作業はユーザや表を作成する処理を伴いますので確かに負荷は全くないわけではありませんが、夜間や早朝などのシステム負荷が比較的低い時間帯に実行して頂くことが可能です(1.準備)。インストールが完了して環境が整ってしまえば、スナップショットの取得などを実行しない限り、Statspackをインストールしたからといってサーバーの負荷に大きな影響を与えるといった事は特にございません。スナップショットの取得やレポート作成などの作業を行わない限り、勝手に何かが起動している、というようなことはありません。

次に、スナップショットの取得やレポートの作成を行う際の負荷についてご紹介します。図5よりレポートの作成は、負荷の低い時間帯にスケジュールすることで負荷が特に高まるのを回避できることがわかります(3.レポート作成)。Statspackのインストール作業や、レポートの作成については、負荷の少ない時間を選ぶことで負荷の高騰を避けることが可能です。

一方で、スナップショットについてはピーク時など負荷のかかっている時間帯に取得するとあります(2.スナップショット取得)。

では、このスナップショットの取得についてです。

スナップショットの取得時に行う実際の作業としては、既存のスクリプトを実行するだけです。この時データベース内部では、動的パフォーマンスビュー、またの名をV$ビューという領域に格納されたメモリ上に存在する稼動状況のデータをStatspack用の表に保存するという処理が行われています。データベース稼動時には、その稼動状況をV$ビューという領域に一時的に取得しています。これはStatspack取得の有無に関わらず同じです。このV$ビューのデータを、Statspack用の表に保存するという動作ですので、データ量にもよりますがスナップショットの取得処理事態にはさほど負荷がかかるものではありません。

alt="図6 作業に伴い作成されるもの" width="500" height="280">

図6 作業に伴い作成されるもの

実は、スナップショットを取得する際には、どこまで詳細にデータを取得するのかというレベルを設定することができます。このレベルの設定により、取得されるデータ量が異なってきます。より詳細なデータをとりたい場合にはレベルを高く設定します。Oracle Databaseのバージョンによってこのレベルの段階は異なります。以下がOracle Database 10g(10.2)でのレベルと取得できるデータの一覧です。

alt="図7 Oracle Database10gスナップショットレベル一覧" width="500" height="217">

図7 Oracle Database10gスナップショットレベル一覧

Statspackが登場したOracle Database 8iから、最新バージョンのOracle Database 11gに至るまで、デフォルトで設定されているスナップショットレベルはLevel5です。

必要としている情報にもよりますが、デフォルトで設定されているレベルでもデータベース全体の稼動状況を判断することが可能です。通常の稼動診断時にはデフォルトのLevel5で取得をしておいて、SQLの統計情報詳細(実行計画のこと)なども一緒にとりたいとなってくればLevel6、データアクセスがどの表や索引に発生しているかなどといったセグメント情報が必要な場合はLevel7、といったように、必要なデータに合わせてLevelを変更することが可能です。まず最初に状況把握で全体を見るという意味では、デフォルトのLevel5でも問題ありません。

最も詳細なデータが取得できるLevel 10のレポートは、情報量が多い代わりに取得する際に必要なリソースも非常に多くなってきます。Level10のレポートについてはOracleのサポートセンターから指示があった時など、特別な場合にのみ使用するものだとお考え頂ければと存じます。

ご参考までに、StatspackのREADMEファイル($ORACLE_HOME/RDBMS/admin/spdoc.txt)からの、Level10に関する注意書きを原文のまま転記しておきます。

Data gathered at this level can sometimes cause the snapshot to take longer to complete i.e. this level can be resource intensive, and should only be used when advised by Oracle personnel.

また、Oracleのサポートセンターにおいて過去対応した事例からは、Level10以外のスナップショット取得時に負荷を大きく与えることがあるという情報はございませんでした。

すなわち、特別な場合を除いて、通常Statspackを用いて分析するのに必要なレポートを作成するために稼動させるだけでは、特に負荷を大きく与えるという事例はないと考えることができます。もちろん負荷状況はシステム構成、サーバースペック、運用状況やスナップショットレベルの設定等に影響される値であり、正確な情報はテスト環境等で検証をして頂くことが必要とはなりますが、一応の目安にはして頂けるのではないでしょうか。以上を参考に、恐れずにスナップショットを取得していただければと存じます。

 

Statspackレポートを読み解く

レポートを取得して、状況を判断するための材料はこれでそろいました。

このようなStatspackレポートが出来上がってきます。

alt="図8 Statspackレポートの出力例" width="298" height="329">

図8 Statspackレポートの出力例

しかし、いざレポートを見ても、必要な情報がどこにあるのかわからない。表示されている数字が何を意味しているのかがわからない。これではせっかくの努力が水の泡になってしまいます。

そこで、私どもOracle Directでは、無償でStatspackレポートを解析するという技術サービスを提供しています。その名も「パフォーマンス・クリニックサービス」です。

サービスの内容は、お客様に取得頂いたStatspackレポートと、OSやH/Wの情報、アプリケーションの簡単な用途などを記載頂いたヒアリングシートを記入の上でOracle Directまで送信頂き、その内容を診断するというものです。

ここで必要なStatspackはデフォルトのLevel5で取得されたもので問題ございません。上述の「図2 Statspack取得の流れ」にある3段階の作業だけ(事前準備、スナップショット取得、レポートの作成)で結構です。

Oracle Directでは頂いたStatspackとヒアリングシートを元に、パフォーマンスのボトルネックとなっているのはどこにあるのかその診断を行い、解決方法について無償でアドバイスいたします。

Oracle Direct パフォーマンス・クリニック・サービス

▲ ページTOPに戻る

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.