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


#下記のように実行されればOK。しばらく流し続け、トランザクション(TPM)が頭打ちになったら、その値が現在のTPMの上限値として覚えておく。この環境だとTPM1400前後
Time      Users       TPM      TPS
13:30:26  [10/10]     1427     19
13:30:27  [10/10]     1414     16
13:30:28  [10/10]     1412     26
13:30:29  [10/10]     1401     19

• 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倍くらいは上昇するはず

TPM 1400 -> TPM 3800までUP
Time      Users       TPM      TPS
13:55:40  [10/10]     3814     51
13:55:41  [10/10]     3837     67
13:55:42  [10/10]     3852     59

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

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

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

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

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

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

• STSの場合と同様に推奨事項の実装を適用する

• swingbenchのTPMの推移を見ると最大40倍くらいは増加するはず。

TPM 3800 -> TPM 120000までUP
Time      Users       TPM      TPS
14:20:14  [10/10]     120092   1439
14:20:15  [10/10]     119711   1408
14:20:16  [10/10]     119293   1456

• パフォーマンス・ハブを見直すと、ASH分析での見え方が変わっている。SELECTのボトルネックが解消したので大量のSELECTを処理できるようになった反動で、今度はUPDATEのenq row lockが頻繁に発生するようになった。その結果TPMは安定しない。(※DBの環境によるが)
  TPMが1300 -> 最大120000までSQLパフォーマンス改善することは分かったので、今後はどのあたりがDB性能限界なのか、安定してSQLを処理できるTPMはどれくらいなのかを調査するには、DBの設定、Swingbenchの実行パラメータの設定などを調整しながら探っていく。
  

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