#Oracle 今時はSPA+Data MaskingとOracleCloudを用いてアップグレードテストする！ Oracle DBA & Developer Day 2015セッションレポート

クラウド時代でのDB開発や、アップグレードのノウハウなどのセッションがあるOracle DBA & Developer Day 2015に参加してきました。

そのうち、「失敗しない移行・アップグレードプロジェクトのために 〜コスト削減とリスク低減を両立するクラウドの使い方とは？〜」のセッションだけでれたのでそのメモになります。

失敗しない移行・アップグレードプロジェクトのために 〜コスト削減とリスク低減を両立するクラウドの使い方とは？〜

SQL Performance Analayzerと、Data Masking、OracleCloudを用いてアップグレードテストをするというお話でした

テスト工数が非常にかかる

• SQLの網羅性を100%にするのは大変。
• ある程度品質をあげていくと、ある点を堺にユーザ満足度は上がらなくなるので、低コストでどこまで高められるかにかかっている
• コスト優先なら、本番環境で実行されたSQLを取得する方法を検討するのは効率的な選択肢の一つ
• 本番環境からsqlをとるにはv$sql, v$sqlareaから取ると思うが、業務の周期を数周期まわして、SQL数が収束曲線を描くようになることを観察する
  • これをやった結果、SQL数がどんどん増える場合は、リテラルや動的SQLが多用されている可能性が考えられるので、網羅的なテストは不可能になる。
  • その場合はそもそそもリソースマネージャを使ってどうにかするなど別のアプローチを取る必要がある

テスト用のSQLリポジトリを作る

• v$sqlstatsは、v$sqlとほぼ同じデータが入っているがより少ないオーバーヘッドで取得ができるのでそこからSQLリポジトリを作っておく
  • SQLリポジトリを整備しておくとチューニングやパラメータ変更など、テスト時や、障害対応にも使える
• SQLリポジトリを作る機能としてSQL Tuning Setという機能がある
  • カーソルキャッシュやAWRなどからSQLや実行計画や実行統計などをディスクに格納したりすることができる
  • Tuning packか、Real Application Testingのオプションが必要になります
  • SQLをとった後は、dba_sqlset_statementsからSQLをみれます

集まったSQLをどうテストするか

• アップグレード後の環境で実行計画が変化をすることがある
• 負荷テストをやって悪くなったSQLを治していくという手法がよく取られるが、そのSQLが加害者なのか、被害者なのかがわかりづらい
  • 別のSQLが引っ張って遅くなっている可能性がある
  • 実行計画の変化の有無をみてSQLを治していくアプローチのほうが効率的
• EXPLAIN PLANは実際に使用される実行計画を表してない場合がある。
  • 例えばバインド値があるSQLは実際の適応計画を加味して作成される
  • バインド値を用いてSQLを実行してhashvalueから調べたほうがいい
  • 他にもadaptive planというのが12cからの機能で、実行計画を作った段階ではテーブルをどうジョインするかを決めずに、実際にやってみてジョイン方法を変えていくといった場合がある

バインド値を取得する

• どういう値が入るかを調べる方法としてはDBMS_XPLAN.DISPLAY_CURSORもしくはV$SQL_BIND_CAPTUREを用いる方法がある
  • V$SQL_BIND_CAPTURE：実際の実行計画のバインド値がわかるが、SQL_IDの数だけループを回さないといけないので仕込みが大変。
  • V$SQL_BIND_CAPTURE：300秒に1回リフレッシュされるが、日々入ってくるバインド値が入ってくるので、観察しているとどういう値で実行しているのかを拾うことができる
• SQLの収集作業〜テスト実施まで、RealApplicationTestingを使えば一挙に解決できます

SQL Performance Analyzer

• RealApplicationTestingの機能の一部でシステム変更前後でSQLの実行計画やパフォーマンスの比較レポートが見れる
• SPAではSQL文だけでなく、バインド値の取得もできるので、本番環境で使われていたSQLでテストが出来ます

Oracle Database Cloud Service

• 12cをすぐに使うことが出来るので最新バージョンのテストがすぐに出来ます
• テスト環境を立ち上げる工数を削減できます
• OracleCloudのエディションについて、SEとEEはオンプレで提供しているのと同じだが、HighPerformanceとExtreamPerformanceはいろんなオプションをまるっとはいったエディションになる

RAT in Cloud

• オンプレミスで作ったSTSと、データをクラウドにもっていくことで同じようにDBのテストができます
• OracleCloudは12cのみの提供になるので10gの性能は測れないが、現在の性能についてはSQLTuningSetに入っています
• SPAでは実際にテストを実行する、実行計画だけ生成して記録するといったことができる
  • explain planでもバインド値を使うのでリアルな実行計画になる
• SPAのConverSQLSETのテストモードを使うことで、オンプレミスの性能結果をそのままテスト結果として使うことも出来ます

テスト環境のデータについて

• できれば本番同等のデータをリストアできるのがいいが、データによってはカーディナリティなども保持したままマスキングなどをする必要もある。その時はDataMasking とSubsetting Packというオプションを使うと解決できる
  • カーディナリティを維持したままマスキングができるので有用なデータを安全に作れる
• 自分でデータをマスキングすると、リアルなバインド値を検索した時にひっかからないことがあるので、バインド値も同じようにマスキングしてあげないと正確なテストにならない
  • SPAとDataMaskingPackを連携させることでSTSのバインド値も同じようにマスキングしてくれます
• マスキングの方法によってはマスキングによって実行計画が変化が発生する可能性もあるので、マスキングの前後での差も見てくれる機能もあります

テスト結果評価

• アップグレードと前と後でテストが可能な場合
  • バッファ読み取り量で比較する
    • レスポンスタイムで比較してしまうと、タイミングによって異なるし、本番環境の沢山のトランザクションがあるなかでみたりすると純粋な単体テストにならない
  • 実行計画の良し悪しを定量的に評価可能
• アップグレード後のテスト環境しかない場合
  • 本番環境はUNDOの量も増えている可能性もあるので、経過時間である程度比較してサイジング値として評価する
  • オプティマイザコストを用いることで実行計画の良し悪しを評価できるが、実性能とは異なる場合があるので、変わったものに関しては実際になげてみて評価していくのがいい
• テスト環境にデータを準備がない場合
  • オプティマイザ統計情報をインポートしてオプティマイザコストで比較するしかない

RAT in Cloudのユースケース

• 下記の3つのいずれの方法でも、オプティマイザコストで比較した場合、ReportSummaryに表示されるコスト値は実際よりも小さい値になってしまうため、SQLごとの詳細画面で確認する必要があることに注意

Basic

• クラウドに本番データももっていけて、テスト条件もオンプレミスと同等にできる場合はSPAで性能を比較できる
• 経過時間もしくはコストで比較することが望ましい
  • 経過時間での比較はHWの性能差を反映した参考値として扱う
  • コストでの比較はHWの性能差によらない実行計画の改善・劣化を評価する
• バッファ読み取り比較で、ConvertSQLSETを用いる場合は結果にブレが生じるので評価が難しい

No Data to Cloud

• データにクラウドにもっていけない場合はクラウド上にスキーマを作成して統計情報をインポートして実行計画を生成し、実行計画とコストで比較する
• 自分でプランを比較する場合はバインド値を入れることを考慮しましょう

Masked Data to Cloud

• マスキングデータをクラウドに持っていける場合はMaskingPackとRAT連携を使ってクラウドへデータを持って行って比較する
  • Basicと同様に経過時間か、コストで評価する
• マスク方法によってはコスト変化が起こる可能性があることに注意

まとめ

• oracleのアップグレードリリースごとにアップグレード戦略を立てる
• 本番環境の情報をうまく活用する
• テストするときは実行計画の変化を正確に把握して下さい
• データや環境の制約に合わせて適切に評価をしましょう
• Oracle Cloud + SPA + Data Maskingで迅速に安全に、網羅度高くテストする

@mogmetの所感

12cから搭載されたData MaskingがSPAと連携できるのは今回はじめて知りました。

Maskingしたデータをテストするときにバインド値も考慮するとなると非常に工数がかかって大変だと思うので、この連携機能は非常に使えそうな機能と感じました。

ついでにアップグレード試験にも出そうな気がするので時間があったらどこかで検証してみようかと思っています。

～ここから余談～

別セッションの余談になりますが、「シバタツ流！ 無料のオラクル純正開発ツールで、データベース・システムの開発効率を改善しよう！」のセッションに最後だけ一瞬出れたのですが、そこで紹介されていたSQLclがまじで、すごい便利そうでした

cf: “Oracle SQLcl”を使ってみる

Linuxの人からみたら、とうとう履歴が使えるようになったのはマジで大きいです！

ぜひSqlplusの代替品として使ってみようかと思いました。