Database ManagementによるOracle Databaseのパフォーマンス監視①

前回の記事では、Database ManagementとEnterprise Managerの機能比較を行いました。実際にDatabase Managementを使用したデータベースのパフォーマンス監視の方法について何回かに分けて紹介していきます。

Database Managementは、パフォーマンス・チューニングと運用監視系の機能にざっくり分けることができます。特にパフォーマンス・チューニングは、STS、SQLチューニング・アドバイザー、ADDMといったデータベースの機能と密接に関わっており、Oracle Databaseに馴染みのない方だとそもそも用語がよく分からないこと多いかと思いますので、実際にSQLチューニングをしながら動作確認できる手順を紹介します。

Database Managementサービスの詳細資料はこちら

Database Managementの準備

Database Managementの有効化は、以下の記事を参考にして有効化
OCI Database ManagementでBaseDB/ExaDB-Dのパフォーマンス監視をする
OCI Database ManagementでオンプレミスのOracle Databaseのパフォーマンス監視をする
Base Database ServiceでDatabase Managementを有効化する

Swingbenchの準備

データベースへの負荷掛けには、お馴染み?のSwingbenchを使用する。本来は、別のVMからGUIで実行したほうが見やすいが、ここでは単にSQLチューニングできるデータベース環境を作りたいだけなので、最小で直接DBサーバー上に実行させる。

#swingbenchをダウンロード (実行にはJDK11以上が必要。DBのJDKを使う)
wget https://www.dominicgiles.com/site_downloads/swingbench04112023_jdk11.zip

##SOEスキーマの作成
cd swingbench/bin/
./oewizard -cs //ホスト名:ポート/サービス名 -dba "sys as sysdb
a" -dbap sysdbaのパスワード -u soe -p soeスキーマのパスワード -ts 表領域 -async_off -scale 4 -create -cl

##SOEスキーマで接続
sqlplus soe/パスワード@ホスト名:ポート/サービス名

##パフォーマンス劣化させるために意図的にIndexを削除
DROP INDEX ADDRESS_CUST_IX;
DROP INDEX CARDDETAILS_CUST_IX;

##Swingbenchの実行。ucはユーザーセッション、min/maxはトランザクション実行のシンクタイム。DBのH/Wリソースに合わせて増減させて適切な負荷に調整
./charbench -c ../configs/SOE_Server_Side_V2.xml -cs //ホスト名:ポート/サービス名 -u soe -p パスワード -uc 10 -min 0 - max 100 -v users,tpm,tps,cpu


#下記のように実行されればOK。しばらく流し続け、トランザクション(TPM)が頭打ちになったら、その値が現在のTPMの上限値として覚えておく。
Time      Users       TPM      TPS     User  SystemWait  Idle
03:28:30  [0/10]      0        0       0     0     0     0
03:28:31  [10/10]     10       10      0     0     0     0
03:28:32  [10/10]     28       18      0     0     0     0
03:28:33  [10/10]     56       28      0     0     0     0
03:28:34  [10/10]     86       30      0     0     0     0
03:28:35  [10/10]     106      20      0     0     0     0

• Database Managementのパフォーマンス・ハブのASH分析やSQLモニタリングのタブから、データベースに負荷がかかっていることを確認する
  

---

SQLチューニング・アドバイザを使ってSQLチューニングをするにはSTSを指定して実行する方法と、SQLをピンポイントで指定して実行させる方法があります。まず、オーソドックスなSTS(SQLチューニング・セット)というSQLと実行統計のセットを準備して実行する方法を紹介します。

STSを使用したSQLチューニング・アドバイザの実行

まず初めに、STS(SQLチューニング・セット)を準備する。

• STSの作成
  

• STSに追加するSQLは、現在実行中のSQLをカーソルキャッシュから収集。収集間隔は60秒。実行にはADMINISTER SQL TUNING SET権限が必要なので、資格証明にはSYSを指定して名前をつけて保存し、作成をクリック。
  

• 作成したSTSの詳細。SOEスキーマのSQLが収集できていればOK。
  

作成したSTSに対して、SQLチューニング・アドバイザを実行する。

• SQLチューニング・アドバイザの作成
  

• 作成したSTSを選択し、実行時間は5分以内、STSで保存した資格情報を選択して実行
  

• SQLチューニング・アドバイザの実行結果のサマリー。2つのSELECT文に対して索引作成の推奨が検出されている。
  
  

• 上記の画面の実行計画の比較をクリックすると、実行計画の差異を比較できる。上は元の実行計画でTABLE ACCESS、下はINDEXになっておりコストが大幅に減少しているのが分かる
  
  

上記手順の流れのようにSTSの作成 -> チューニング・アドバイザの実行でSTS内のSQLパフォーマンス改善案を提示してくれる。STSはカーソルキャッシュだけでなく、定期実行され蓄積されているAWRスナップショットの結果を基に作成することもできる。
では、実際にSQLチューニング・アドバイザが推奨した提案を受け入れて索引を作成する。

• 後で検索に使用するのでSQL ID(g81cbrq5yamf5)をメモしておき、推奨事項を実施をクリック
  以下のように、索引を作成する表領域を指定、STSで保存した資格情報を選択、ログを出力するオブジェクトストレージのバケットを指定して実装をクリック
  

• swingbenchのTPMの推移を見ておく。索引の作成が完了すると、TPMがおよそ2倍まで上昇するはず

• SQL検索からSQL IDを指定して検索。作成された索引を使用した実行計画に変更されていることが分かる
  
  

単一SQLからSQLチューニング・アドバイザの実行

• パフォーマンス・ハブのASH分析からCPU負荷が高くなっているSQL ID(7ws837zynp1zv)を選択
  

• 実行計画でTABLE ACCESSになっていることを確認し、SQLチューニングをクリック
  

• SQLのソースが選択したSQLのみのピンポイントになっている点に注目。前回同様の設定で実行
  

• 指定したSQLのみのSQLチューニング・アドバイザの結果。同様に索引作成の推奨がされている
  
  

• STSの場合と同様に推奨事項の実装を適用。swingbenchのTPMの推移を見ると4-5倍くらいに増加しているはず。

• パフォーマンス・ハブを見直すと、ASH分析での待機イベントの見え方が変わっている。DB環境によって異なるが、SQLボトルネックは解消しているので、待機イベントから類推しながらさらなるDB性能限界を探っていくことも可能。また、先ほどのSQLの実行計画がTABLE ACCESSがINDEX SCANになっていることも確認
  

以上がDatabase Managementでの基本的なSQLチューニングの実行手順です。
Oracle DatabaseのSQLチューニングではAWRレポートがよく使われるかと思いますが、Database ManagementではAWRエクスプローラーから過去の様々なパフォーマンス情報をオンデマンドでアクセスしながら、GUIで分析しSQLチューニングに繋げていけるのでより直感的な操作が可能になります。次回は、ADDM、ADDMスポットライトについて紹介します。