SQL - Oracle

1
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
第95回夜な夜な! なにわオラクル塾
待ってましたシステム更改!
-優雅で安心のサーバー変更
プロジェクトへ
日本オラクル株式会社
テクノロジー製品事業統括本部
ソリューション本部支社ソリューション部
2012年12月19日
THIRD PARTY
COMPANY LOGO
2
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するも
のです。また、情報提供を唯一の目的とするものであり、いかなる契約にも
組み込むことはできません。以下の事項は、マテリアルやコード、機能を提
供することをコミットメント（確約）するものではないため、購買決定を行う際
の判断材料になさらないで下さい。オラクル製品に関して記載されている機
能の開発、リリースおよび時期については、弊社の裁量により決定されます。
Oracleは、米国オラクル・コーポレーション及びその子会社、関連会社の米国及びその他の国における登録
商標または商標です。その他の名称はそれぞれの会社の商標の可能性があります。
3
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Program Agenda
 システム更改における留意点
 テスト環境の準備
 テストデータの準備
 テストの実施支援
 システム統合に向けた支援
4
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
システム更改における留意点
5
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
システム変更・テストに関する声
OSバージョンアップの際に、製品の動作確認は
したが、本番公開したらパフォーマンスの低下
が発生してしまった。
パフォーマンス・シミュレーションテストを行った
にも関わらず、本番環境では十分なパフォーマ
ンスが得られなかった。
テストを完璧に行うことで、障害のリスクを低下
することができることはわかっているが、工数・
費用が確保できない。
テスト工数が取れないので、一度構築したシス
テムは、基本的に大幅な刷新までは一切変更し
たくない。
システムへの変更によって、障害発生のリスクが高くなるので、シ
ステムの設定の変更は極力行いたくない。
「ITインフラの硬直化」現象
運用管理コスト増大の原因
6
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
システム更改における課題
H/Wリプレースやサーバー統合などシステム更改における課題
7
要件
•
•
•
•
業務停止時間の最短化
プロジェクト期間の最短化
データベース移行におけるリスクの最小化
アプリケーションの変更の最小化
課題
•
•
•
•
最適なデータベース移行方法や手順の選択
業務停止可能時間内での移行
必要十分なテストの実行
テストの効率化
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
システム更改の極意
 「プロジェクト体制の整備」を怠れば、つまずく
 「テスト不足」が更改後のトラブルを招く
– 「テスト・ツールの活用」がキー
 Database最新機能、仕様変更のキャッチアップ
 「パフォーマンス問題」は事前の情報収集とツールの活用で防げる
8
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
「プロジェクト体制の整備」を怠れば、つまずく
 不明瞭な役割分担で進行することでのトラブル
 システム更改時にもユーザー部門の協力は不可欠
 ツール利用の検討
 課題管理表作り
9
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
「テスト不足」が更改後のトラブルを招く
 スケジュールの遅延で短縮されがちなテスト工程
 トラブルの多くは事前の適切なテストで回避できる
 万一に備え、「緊急時対応計画」を策定する
 「テスト・ツールの活用」がキー
– テスト期間の短縮や品質向上に威力を発揮するテスト・ツール
– 通常のテストでは発見が難しい「動作変更」を検出
10
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Database最新機能、仕様変更のキャッチアップ
 最新情報をキャッチアップすることが、システム更改の投資対効果を最大化
する
 コストベースオプティマイザとの上手な付き合い方
 必ずバージョンアップに伴う仕様変更/機能追加のキャッチアップを
11
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
「パフォーマンス問題」は事前の情報収集とツールの活
用で防げる
 新バージョンでパフォーマンスが向上するはずが・・・
 カットオーバー後も続くパフォーマンス問題への対応
 パフォーマンス・チューニングの自動化ツールの活用
12
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
そんなことは
わかっている！
でもできない
環境なんて簡単に用意できない
本番データなんてつかえない
本番と同等の負荷なんて実現できない
どこが課題かわからない
テストしてもやっぱり遅くなる
13
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
オラクルが提供する優雅で安心のサーバー変更プロジェクト
支援機能
プロジェクトステップ
テスト環境の迅速な準備
テストデータの準備
14
Oracleのプロジェクト支援機能
Oracle VM
テストに必要なサーバを仮想化で低コストで構築:
Oracle Data Guard
バックアップ環境をテスト環境として構築:
Oracle Database Vault
テスト担当者だけが安全にアクセスできる環境:
Oracle Database Lifecycle Management Pack
稼働中のデータベースを素早く複製:
機密データをマスキング:
Oracle Data Masking Pack
テスト計画・実施
本番環境下のデータベース負荷を再現:
アプリケーションのテスト自動化と管理:
Oracle Real Application Testing
Oracle Application Testing Suite
安定した運用監視
アプリケーションの運用管理/障害解析:
ユーザー視点の性能監視:
Oracle Enterprise Manager
Oracle Real User Experience Insight
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
テスト環境の準備
15
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
テスト環境の迅速な準備（１）
テスト環境に必要なサーバを仮想化で低コスト構築～Oracle VM～
 VMを複製もしくはテンプレート化により展開スピードの短縮
– Oracle VM ならばライセンスは無償
– Oracle VM ならばデータベースなどOracle製品の動作も保証
 運用管理もOracle Enterprise ManagerでWebブラウザから
テンプレートからサーバのコピーも可能
Oracle VM
管理ツール
（Oracle Enterprise Manager 12c)
1台の物理サーバ上で複数のシステムを稼働
16
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
コストをかけずに、もう1台の調達が可能
テスト環境の迅速な準備（２）
素早く稼働中のデータベースを複製～Oracle Database Lifecycle
Management Pack～
• 現在稼働中のデータベースを容易に複製可能
• 複製（クローニング）作業は Oracle Enterprise Managerに統合
• 複製（クローニング）中に、データをマスキング可能
別のマシンにデータベースの
クローンを作成
クローニング作業は
Webブラウザで実施
バックアップデータからも
クローニングが可能
17
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
同じマシンにデータベースの
クローンを作成
データベースの複製
バックアップを使った複製DB作成
 事前準備
db_file_name_convert
– 複製DB用の初期化パラメータファイルの用意
log_file_name_convert
– 複製DBにて利用するディレクトリの用意
を設定
 Oracle Database（複製DB）の起動
% export ORACLE_SID=dup
% sqlplus / as sysdba
SQL> startup nomount
SQL> exit
 RMANのduplicateコマンドの実行
% export ORACLE_SID=dup
バックアップ格納先ディレクトリ
% rman auxiliary /
RMAN> DUPLICATE DATABASE TO dup
2>
BACKUP LOCATION '/home/oracle/orcl_bkup';
18
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Oracle Enterprise Manager
データベースの複製（クローニング）
 データベースのクローニングの選択
19
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Oracle Enterprise Manager
データベースの複製（ソース・タイプの選択）
 ソースタイプ
 実行中のデータベース
 既存のバックアップ
 ソースへのログイン情報
20
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Oracle Enterprise Manager
データベースの複製（バックアップ使用の例）
• 複製に使用するバックアップを選択
21
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Oracle Enterprise Manager
データベースの複製（実行中のデータベース使用の例）
 アーカイブログ運用DBの複製が可能
22
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
テスト環境の迅速な準備（３）
バックアップ環境をテスト環境として活用!!
～Oracle Data Guard / Snapshot Standby～
• Oracle Data Guard によって本番のバックアップ環境を準備
• バックアップ環境をそのままテスト環境として利用可能
• バックアップ環境とテスト環境の両立が可能
一時的に更新可能なテス
ト環境としてオープン
本番サイト障害時には、
本番と同一の環境に
切り替え
Oracle Data Guard
本番環境
23
プライマリ・
データベース
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
データベースへの更新を同期
物理スタンバイ・
データベース
テスト環境
Snapshot Standbyの使用方法
 フィジカル・スタンバイを一時的に切り替える
 保証付きリストア・ポイントを内部的に作成後、切り替える
設定方法 ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
（変更後テスト・データベースとして再起動）
 テスト完了後フィジカル・スタンバイに戻す
 Flashback Databaseを内部的に実行
 保証付きリストア・ポイントまでフラッシュバック
設定方法 ALTER DATABASE CONVERT TO PHYSICAL STNANDBY;
（変更後フィジカル・スタンバイとして再起動し、REDO適用を再開）
Snapshot Standbyの使用には Flashback Database の設定が必須
24
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Snapshot Standbyの使用方法
フィジカル・スタンバイの切り替え手順
1. スタンバイ・データベースのREDO適用を停止
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL；
2. スタンバイ・データベースをスナップショット・スタンバイに移行
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY；
SQL> STARTUP MOUNT FORCE
3. データベース・ロールを確認
SQL> SELECT DATABASE_ROLE FROM V$DATABSE;
DATABASE_ROLE
---------------SNAPSHOT STANDBY
4. スナップショット・スタンバイをオープン
SQL> ALTER DATABASE OPEN;
25
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Snapshot Standbyの動作
Snapshot Standby
Snapshot Standbyを使用したテスト
テスト用
クライアント
Read/Write
Open状態
プライマリ・データベース
26
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
受信したREDOは
適用せずに保持
フラッシュバック・データベ
ース・ログを取得
Snapshot Standbyの動作
テスト終了後、フィジカル・スタンバイへ切り替え
Snapshot Standby
切り替え時点まで
フラッシュバック
プライマリ・データベース
27
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
受信したREDOは
適用せずに保持
テスト環境の迅速な準備（４）
安全にテスト担当者だけがアクセスできる環境～Oracle Database Vault～
• データベースに職務分掌機能を追加
• テスト対象（データ、システム、時間、クライアント）だけに限定
• SI企業が顧客システムに安全にアクセスできる環境
テスト対象だけにアクセス
テスト時間だけにアクセス
テストクライアントだけからアクセス
×
×
×
対象外システム/データ
対象外時間
Oracle Database Vault
28
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
テスト環境
対象外クライアント
テストデータの準備
29
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
データベースのテストには本番に近いデータを使用
 正確な負荷テストを行うためには、本番環境に近いデータを使用するのが
理想
– 本番と同様のデータ量や質、構成（パラメータ、索引など）
 本番データに近いテストデータを用意する
– 本番データの活用（列データの要素数、分布、件数などが本番データと同等で
あること
30
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
テストデータの準備（1）
データベース間のデータ連携技術の活用
 データベース間のデータ連携技術を使い常に本番データベースと同等のテ
ストデータを維持
 Oracle GoldenGate
業務アプリ
ケーション
テスト
– 特徴
 高速・軽量・柔軟
 高い信頼性
– DB間をつなぐあらゆる用途で使える
GoldenGate
 テスト用DBを構築
 テストに必要なデータ（表）のみを
GoldenGateで連携
31
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
本番
データベース
テスト
データベース
Oracle GoldenGate 動作の仕組み
トランザクションログから
データを抽出し、中間ファイ
ルに永続化
Capture
トランザクション単位
でネットワーク転送
Trail
Files
データベースＡ
32
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
トランザクション情報を
SQLに変換して適用
Trail
Files
Pump
Delivery
LAN / WAN / Internet
Over TCP/IP
データベースB
本番データをテストデータとして使用する課題
 テストデータから情報漏えいが発生する可能性
 本番環境に近いデータでないと、正確なテストはできない
データベースのテストからの視点
 テストデータの作成には手間と時間がかかる


できるだけ本番に近いデータでテストしたい
テスト環境はクローズドなネットワークだから問題ない
かも？
機密情報保護の視点
 テストのために、本番環境に近いデータが必要なことは
理解できる。

機密情報が含まれる可能性が高いのでは？

本番用データを扱う開発者の監査は？

委託業者との機密保持契約の観点は？
両方の要件を満たすには
33

効率的な開発やテストには、本番環境に近いテストデータが必要

機密情報を含んだデータをテストに使用するリスクへの対策が必要
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
テストデータの準備（２）
データベース中のデータをマスキング～Oracle Database Masking Pack～
• 元データが分からないようにデータを完全にマスキング
• テーブル（表）構造とルールに基づき大量テストデータを自動作成
• 複製（クローニング）と同時に、データをマスキング可能
ID
NAME
PHONE
同じマシン上でも
別のマシン上（クローン）でも
マスキング可能
佐藤浩
03-5412-2000
[email protected]
2
鈴木浩一
090-8879-7601
[email protected]
3
高橋誠
042-2346-1702
[email protected]
4
田中浩二
011-5813-7302
[email protected]
5
渡邉隆
090-9280-1403
[email protected]
ID
NAME
PHONE
：
：
：
1
太田智子
090-0000-6854
[email protected]
2
松尾裕子
000-1111-6555
[email protected]
3
原誠
011-2222-6257
[email protected]
4
金子真由美
090-3333-5958
[email protected]
5
安藤博
080-4444-2747
[email protected]
：
：
：
操作はすべてWebブラウザによる
Oracle Enterprise Managerで実現
34
EMAIL
1
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
：
EMAIL
：
カーディナリティを維持したマスキング
マスク後
マスク前
都道府県
東京都
東京都
都道府県
全ての行を単純に置き換えると、要素の数や
AAAAAA
各要素の割合が変化し、where句、group by
AAAAAA
句などを含むクエリの結果が変化してしまう
where 都道府県=‘東京都’
3件
AAAAAA
AAAAAA
都道府県
大阪府
AAAAAA
大分県
AAAAAA
AAAAAA
ク先も同一になる(異なる)ようにマスクを行う
35
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
5件
AAAAAA
東京都
元のデータが同一である(異なる)場合、マス
where 都道府県=‘AAAAAA’
BBBBBB
CCCCCC
where 都道府県=‘AAAAAA’
3件
Databaseに最適化されたマスキング
主キー / 一意 / 参照整合性制約を自動検知
参照整合性を維持したマスキング(ランダム文字列＋ランダム数値)
ID
NAME
CID
CID
1
SMITH
1
1
2
ALLEN
4
3
JONES
4
COUNTRY_NAME
ID
NAME
CID
CID
UNITED_KINGDOM
1
Aaafeh
83
83
UNITED_KINGDOM
2
UNITED_STATES
2
Aafhed
65
39
UNITED_STATES
1
3
AUSTRALIA
3
Aaaafhe
83
9
AUSTRALIA
CLARK
2
4
IRELAND
4
Bodofa
39
65
IRELAND
5
ADAMS
3
5
CANADA
5
9
7
CANADA
：
：
：
：
aaahfie
d
：
：
：
：
：
COUNTRY_NAME
：
外部キー制約を自動的に
検知し、整合性を維持
36
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
データベースに最適化された設定
 高速なデータマスキングの実現
– マスク処理を並列化に実行
– REDOログ生成を無効化、オーバーヘッドの削減
 マスキング後のパフォーマンスへの考慮
– 統計情報のリフレッシュ、マスク処理によるデータ量の変化に対応
– 一時表の削除により、不要なリソース占拠を回避
 テスト環境へクローニング＋マスキングの自動化
37
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Oracle Database Masking Pack利用手順
エクスポート
フォーマットライブラリ
作成
他にインポート
クローニング
マスキングジョブの実行
一度作った定義をライブラリに
登録しておくと、簡単に再利用
可能
38
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
マスク定義を元に、実際に実
行されるPL/SQLスクリプトを
画面からクリック一つで生成
マスキング完了
暗号化とマスキングの違い
 暗号化 →
- データの漏えい、盗難への対策
- 鍵によって復号化できる
- 暗号化によって情報の特性は無くなる
 マスキング → - 開発現場からの事件、事故対策
- マスキングしたデータは元に戻せない
- 情報の特性は維持できる
平文
39
暗号化
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
暗号化したデータ
×
マスキングしたデータ
平文
マスキング
テスト環境の準備・番外編
統計情報の移行
 本番環境の統計情報をExportしておき、テスト環境にImportすることにより、ほぼ同
じプランを選択させることが可能
 Step1.統計情報を格納する表の作成
DBMS_STATS.CREATE_STAT_TABLE(
ownname => ‘SCOTT',
stattab => ‘STAT_TAB‘
)
Step3. 統計情報のImport
DBMS_STATS.IMPORT_TABLE_STATS(
ownname => ‘SCOTT’,
tabname => ‘EMP’,
stattab => ‘STAT_TAB’
)
 Step2.統計情報のExport
DBMS_STATS.EXPORT_TABLE_STATS(
ownname => ‘SCOTT’,
tabname => ‘EMP’,
stattab => ‘STAT_TAB’
)
40
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Export/Importの使いどころ
•開発環境と同様な実行プランを生成させ
たい場合
•統計保存期間より前の実行プランを選択
させたい場合
テストの実施支援
41
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
データベースをテストして「最上級品質」を実現
現状：
–テストの重要性は理解しているが工数がかかるしツールも高価
–テスト不足で問題が発生すると修正に時間がかかる
機
能
テ
ス
ト
目的
これまで
変更や追加機能が
不十分な工数と
テスト漏れの発生
「正常動作するか」
の確認
データベースが
「どのくらいまで
処理が可能か」
環境構築
非常に高価な
ツール
の確認
手作業
42
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
現実的で効果的な
機能テスト・ツール
Real Application Testing
手作業
負
荷
テ
ス
ト
これから
環境構築
DBに最適化された負
荷テスト・ツール
Diagnostic Pack/
Tuning Packで
問題の分析と解決
テスト計画の作成とテストの自動化（１）
データベースアプリケーションのテスト自動化～Oracle Real Application
Testing～
• 本番環境でのアプリケーションをキャプチャ（記録）し再生
• 疑似クライアントで高負荷状態も再現可能、テスト履歴も管理
• Oracle9i,10g,11gで利用可能、アップグレードテストにも最適
アプリケーションがなくても
データベースへの処理を
キャプチャし再生して再現
キャプチャ
Replay
疑似クライアント
本番環境
43
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
複数の疑似クライアントで
高負荷状態のテストも可能
テスト環境
Oracle Real Application Testing
• Database Replay：
End-to-end testing with real workloads
テスト環境の
構築
ワークロード
のキャプチャ
本番環境と同じ負荷を利用したテストの実現
• システム・テスト向き
• 本番環境で実行されたトランザクションを時系列に記録
• テスト環境で本番環境の負荷を忠実に再現
• 本番環境とテスト環境で自動的に取得された統計情報を元に
パフォーマンス比較レポートを作成
• SQL Performance Analyzer (SPA)：
ワークロードの
リプレイ
リプレイ・クライアント
の展開
SQLのレスポンス・タイムについてのテスト
• SQL単体テスト向き
• 本番環境で実行された問合せと、その実行計画を記録
• テスト環境で問合せを再実行し、パフォーマンスと実行計画の
比較レポートを作成
Database Replay と SQL Performance Analyzer は
それぞれが補完しあう機能です。
44
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Oracle Real Application Testing
Database Replayの必要性
 現状のシステムテストの問題点
– 広範囲に及ぶテストや検証  時間・コストがかかる
– 高コストで低い成功
 多数の問題が検知されない
 システムの可用性・パフォーマンスにマイナスの影響
– 成功率が低い原因
 現行のテストツールでは十分ではない
–
実際の本番ワークロードを再生するかわりに、合成のワークロードをシミュレート
–
ワークフローの一部分が対象であるため、全体での影響度が不明
Database Replayにより実際のテストが可能に
45
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Database Replay
概要
本番環境
クライアント
テスト環境
リプレイ・クライアント
• 本番データベースのワークロード
を利用したデータベースの
パフォーマンス・テストを実施
• 本番環境特有のタイミングやトラン
ザクションの依存性、思考時間など
を維持
• トランザクションのスループット改
善をテスト及び計測
• Oracle Database 9i R2や10g R2
からのアップグレードをサポート
46
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
ストレージ
ストレージ
キャプチャ
前処理
リプレイ
分析＆
レポート
Database Replay
キャプチャされる内容
 Client→RDBMS
– ログイン/ログオフ/Session switch
– 全 SQL 文と PL/SQL テキスト
– バインド変数
– 実行/フェッチ
 RDBMS→Client
– 返された行数
プロトコルレイヤ
– エラーコード
 System Data
– SCN
47
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
RDBMS
キャプチャ用探針
Database Replay
分析とレポーティング
 キャプチャ時とリプレイ時の相違をレポート
– パフォーマンスの相違
– エラーの相違
 リプレイ時に発生した新規エラー
 キャプチャ時に発生していたがリプレイ時に発生しなかったエラー
 キャプチャ時に発生していたがリプレイ中に変異したエラー
– データの相違
 キャプチャ時と異なる行数が変更されたDML
 キャプチャ時と異なる件数が返されたSELECT
 リプレイの実行後、比較レポートを生成
– ワークロード・リプレイレポート/AWR比較レポート/ASHレポート
48
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Database Replay
Database Controlによる使用方法
Database Controlのウィザードに従って取得／再実行
このガイドに従って実
行
49
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Database Replay
テスト対象となる変更
対象とならない変更
中間層
対象となる変更
•
•
•
•
•
データベースのアップグレード、パッチ
スキーマ、パラメータ
RACノード、インターコネクト
OSバージョンアップ、パッチ
CPU、メモリ、ストレージ
• その他
50
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Replay
Oracle
Instance
Capture
SQL Performance Analyzer（SPA）
概要
SQLの実行計画と実行統計
変更前の実行
SQL Tuning Set
（STS）
SQLパフォーマンスの比較
SQLの実行計画と実行統計
変更後の実行
•
•
•
•
51
分析レポート
システム変更がクエリー・パフォーマンスに与える影響の予測とテスト
パフォーマンス改善と劣化の分析
キャプチャされたSQLの実行と分析を完全に自動化
STS,SQL Plan BaselineやSQL Tuning Advisorによる統合されたソリューション
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
SQL Performance Analyzer（SPA）
分析とレポーティング
 下記項目を比較
– 実行時間
– CPU時間
– オプティマイザ・コスト
比較分析
– バッファ読取りブロック数
– ディスク読み取りブロック数
– ディスク書き込みブロック数
– 問い合わせ件数
 変更適用により影響のあったSQLを表示
– 性能が改善したSQL
– 性能が低下したSQL
– 性能が変わらなかったSQL
52
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
レポーティング
SQL Performance Analyzer
分析後の対処
SQL Tuning AdvisorやSQL Plan Baselinesを利用
して性能の悪くなったSQLをチューニング
Oracle Real Application Testing 用途と特徴
Database Replay
SQL Performance Analyzer
どんな場面で使い分ける?
データベースサーバー本番環境での負荷を
用いサブシステムも含めた包括的なテストを
実施する場合
特定の重要なSQLに関してシステム変更によるパ
フォーマンス影響の有無を確認するSQLの単体テ
スト
何ができる機能?
テストシステム上においてDatabase本番環
境の負荷を再現
システム変更に伴うSQL 応答時間の変化が利用者
が影響をこうむる前に確認する
機能の目的は?
システムにおけるスループットに対する影響
度を評価
SQL の応答時間に対する影響度を評価
仕組みは?
本番環境にて収集された負荷を同時実行性
やタイミングおよびトランザクション間の依存
性も含め再現を行う
SQL Tuning Set に格納された個別の SQL 文を実
行しシステム変更前後における実行計画や実行時
の統計値の比較を行う
※Database Replay と SQL Performance Analyzer はそれぞれ補完しあう機能
53
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
テスト計画の作成とテストの自動化（２）
Webアプリケーションのテストの管理・自動化～Oracle Application Testing
Suite～
• Webアプリケーションに対するシステム単体テスト、負荷テスト
• テストの工程管理から機能テスト、負荷テストまでを統合
• テスト中のDB/ASなどサーバ負荷状況監視も可能
機能/回帰テスト
Oracle Functional Testing
テスト工程管理
Oracle Test Manager
負荷テスト
Oracle Load Testing
54
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Oracle Application Testing Suiteの使用場面
アプリケーション開発のライフ・サイクルを通して品質向上を支援
Oracle Functional Testing
機能テスト
ユーザー・インタフェース機能&回帰テスト/ データ投入の自動化
Oracle Load Testing
負荷テスト
コンポーネント
レベル
システム
レベル
チューニング
開発フェーズ
要件
定義
設計
開発
単体
テスト
結合
テスト
システム
テスト
運用
Oracle Test Manager
テスト工程管理
55
テスト計画
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
テスト管理
不具合管理
リリース管理
Oracle Application Testing Suite用途と特徴
課題
Webアプリケーションの品質課題を解決
応答時間が遅くユーザからのクレームが多い
キャンペーンの告知をしたらサイトがダウンした
原因
Oracleのソリューション
性能に関する非機能要件での取り決めが甘く(あ
るいは実施していない)、多数のアクセスにシステ
ムが耐えられるかテストしていない
Oracle Load Testing
高負荷時に他人のデータが表示されてしまった
HTTP通信レベルのテストのみでコンテンツまで確
認していない
性能検証を行いたいが方法が分からない
性能に関するテストの経験がない
入力値が違うだけのテストが多く時間がかかる
パッケージアプリなので環境ごとのテストが必要
機能変更に対してテスト漏れはないだろうか
不具合を修正したがデグレートしてしまった
毎週の進捗会議の資料を作るのが大変
不具合の修正状況が分からない
開発拠点が分散していて連絡が大変
56
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
入力値やプラットフォームが違うだけのテストを人
手で実施してては効率が悪く、テスト漏れも誘発
要件に対するテストの適用範囲や他の機能に対
する影響度合いが可視化されていないため、単一
機能についてしかテストされていない
Excelなどオフラインでのテスト管理となっているた
め、データの集計に時間を要してしまい、正確性
にかけてしまう時もある
メールだけでは対応が遅れ、オフショア開発では
時差の影響も受ける
WebアプリケーションやWebサービス
/SOAベース･アプリケーションの性能と拡
張性を、性格かつ容易にテストできる負荷
テストツールです。
Oracle Functional Testing
機能および回帰テストを、迅速かつ正確に
実行する自動テストツールです。
Oracle Test Manager
テスト工程全体を構築・体系化する、柔軟
で操作が容易なテスト工程管理ソリュー
ションです。
品質に関する情報を一元管理することでテ
スト資産を活用し、品質状況の可視化と組
織間の効果的な情報共有を実現します。
※C/S, 組み込み等、Webアプリケーション
以外でも適用可能です。
システム統合に向けた支援
57
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
段階的なシステム統合のために必要な事項
 システム/アプリケーションの安定運用
– アプリケーションの運用管理、障害解析
– リアルなエンドユーザ視点のアプリケーション監視
– SQLの実行性能を低下させない/改善させる
 統合環境のリソース管理
– リソース利用の制御
– リソース面からの統合の可否判断
 システム並行稼働の支援
58
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
システム/アプリケーションの安定運用（１）
アプリケーションの運用管理、障害解析～Oracle Enterprise Manager ～
アプリケーション
HTTPサーバー
SOA
ESB
APサーバー
データベース
認証
ネットワーク
ファイルシステム
OS
サーバー
ストレージ
59
•サービスレベルの管理
•アプリケーション管理
•セキュリティ管理
•障害の発見、問題分析と対処
•AS/DBの原因切り分け
•アップグレード
•データベース管理／チューニング
•パッチ・マネジメント
•キャパシティ・プランニング管理者へ
•ハードウェア・サイジング
通知
•パフォーマンス管理
•構成管理
•ライフサイクル管理
•ホスト（サーバ）の管理
•OSの管理
•ストレージの管理
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
警告
EBS
Oracle Enterprise Managerの活用
 Enterprise Manager
パフォーマンス関連機能
– 負荷状態をグラフィカルに
時系列で把握できる
 リアルタイムSQL監視
– 実行中SQLの実行状況をリアルタイムに確認できる
60
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
ADDM 自動データベース診断モニター
(Automatic Database Diagnostics Monitor)
 AWRに収集された統計情報をもとに、定期的なデータベースのパフォーマンス
監視 / 診断をDB管理者（DBA）向けに行ってくれる機能
61
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
リアルタイムSQL監視による実行計画の把握
実行中のデータ参照(「今ここ！」マーク)
現在実行中である
ことを示すマーク
進行状況がわかるため、
「あとどれくらいで(バッチな
どの)処理が終了するか」、
見当をつけられる
「今ここ！」
62
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
システム/アプリケーションの安定運用（２）
リアルなエンドユーザ視点のアプリケーション監視～Oracle Real User
Experience Insight ～
• 真のエンドユーザー体験を洞察し、顧客満足度を向上
• ユーザーの指摘より先に性能問題を認知
• 障害発生時の内容を迅速に確認
KPI/SLAモニタリング
表示エラーページの分析
ボトルネックページの分析
ドメインごとの負荷分析
アクセス動向分析レポート
KPIアラート通知(SNMP)
BIツールとの連携
トレンド分析
影響のあったユーザの特定
監視サイトA
監視サイトB
Switch/TAP
実ユーザ
63
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
システム/アプリケーションの安定運用（３）
SQLの実行性能を低下させない/改善させるSQL計画管理（SQL Plan Management/SPM）
 CBO自身が実行計画を時間経過とともに履歴として記録し、評価する管理メカニズム
 複数の実行計画からSQL計画ベースラインを構築し、その中から最適な実行計画を選択可能
 Proactive な、未然防止メカニズム
 SPMの3つのフェーズ
1. 取得
取得


選択
2. 選択
改良
3. 改良


64
実行計画を取得し、SQL計画履歴として記録する
既存の実行計画からのSQL計画ベースラインを作成する
格納されたSQL計画履歴に基づいて、パフォーマンスが低下する可能性を回
避する実行計画を選択する
新しい実行計画のパフォーマンスを評価し、より優れたパフォーマンスの実行
計画をSQL計画ベースラインに組み込む
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
SPM 利用の背景
 重要なSQL文の性能低下による遅延は避けたい
 Oracle Database のバージョンが変わるため、同じ SQL 文でも実行計画
は変化する可能性がある。（多くの SQL は性能が改善すると想定されるが、
ごく一部の SQL で性能が低下する可能性がある）
 新システムの I/O 性能は現行システムと比較して改善することが想定され
るため、同じ実行計画であれば性能改善が見込める。
全SQL文
実行計画が
変化するSQL
一部のSQLで性能が低下した影響により、月次バッ
チが遅延することを避けなければならない
65
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
性能低下
SQL
性能改善
SQL
実行計画が
同じSQL
SQL の本数比率を図で表現しているわけで
はありません。
SPM ベースラインの編集
KROWN#134329 [11g新機能]ヒントを指定した際の実行計画を SQL計画の管理にロードする方法
作業内容
元のプランの登録（ベース登録）
ヒントを利用してプランを編集
SQL実行
select * from tab2 where c2 > 999;
select /*+ FULL (TAB2) */ * from tab2 where c2 > 999;
カーソルのSQL_ID, PHV
を確認(V$SQL)
SQL_ID
PLAN_HASH_VALUE
------------- --------------3m7d74pkw543z
2200541503
SQL_ID
PLAN_HASH_VALUE
------------- --------------1j82gtnc0n4ps
2156729920
カーソルのプランを確認
(dbms_xplan.display_curs
or)
Plan hash value: 2200541503
-------------------------------------------| Id | Operation
| Name |
-------------------------------------------|
0 | SELECT STATEMENT
|
|
|
1 | TABLE ACCESS BY INDEX ROWID| TAB2 |
|* 2 |
INDEX RANGE SCAN
| ND2 |
--------------------------------------------
Plan hash value: 2156729920
---------------------------------| Id | Operation
| Name |
---------------------------------|
0 | SELECT STATEMENT |
|
|* 1 | TABLE ACCESS FULL| TAB2 |
----------------------------------
カーソルから SPM 登録
var res number
exec :res := dbms_spm.load_plans_from_cursor_cache( sql_id => '3m7d74pkw543z', plan_hash_value => 2200541503);
var res number
exec :res := dbms_spm.load_plans_from_cursor_cache( sql_id => '1j82gtnc0n4ps', plan_hash_value => 2156729920, sql_handle => ‘SYS_SQL_2506061412f95de3');
SPM 登録
SQL_HANDLE
-----------------------------SYS_SQL_2506061412f95de3
SYS_SQL_2506061412f95de3
66
元の SQL_HANDLE に、ヒントで編集したプランを結び付けることで、元
の SQL のプランを変えることが可能
（元のプランを今後使わないように、無効化 or 削除を行う）
PLAN_NAME
-----------------------------SYS_SQL_PLAN_12f95de371befc37
SYS_SQL_PLAN_12f95de3b157e6ce
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
ORIGIN
----------MANUAL-LOAD
MANUAL-LOAD
ENA
--YES
YES
ACC
--YES
YES
FIX
--NO
NO
AUT
--YES
YES
CREATED
----------------09/06/03 08:35:32
09/06/03 08:39:06
SPM 登録
SPMでヒントで指定した実行計画をヒント句無しのSQLに適用
INDEXスキャンをFULLスキャンに変更する例(1/8)
 以下のSQLの実行計画をSPMを使ってINDEXスキャンからFULLスキャンに変更する
select * from tab2 where c2 > 999;
実行計画
---------------------------------------------------------Plan hash value: 2200541503
--------------------------------------------------| Id | Operation
| Name | Rows
--------------------------------------------------|
0 | SELECT STATEMENT
|
|
10
|
1 | TABLE ACCESS BY INDEX ROWID| TAB2 |
10
|* 2 |
INDEX RANGE SCAN
| IND2 |
10
---------------------------------------------------
INDEX RANGE SCANの実行計画
この実行計画をSQLを変更せずにFULLSCANに変更
67
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
SPMでヒントで指定した実行計画をヒント句無しのSQLに適用
INDEXスキャンをFULLスキャンに変更する例(2/8)
 SPMを使うため、前ページのSQL情報をSQL管理ベースラインに登録
※カーゾルキャッシュに残っている情報から登録する方法の他に、optimizer_capture_sql_plan_baselines=trueにして2回実行
する方法やSTSから指定する方法があります
SQLIDを指定
（ライトのマークをクリックするとSQLテキストの一部分からSQLIDを選択）
プロシージャdbms_spm.load_plans_from_cursor_cacheを使用しても可能
68
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
SPMでヒントで指定した実行計画をヒント句無しのSQLに適用
INDEXスキャンをFULLスキャンに変更する例(3/8)
 登録されたベースラインの状態やSQL文、実行計画の内容などを確認
 ヒント指定前のSQLのSQL handleは後で使用
SQL handleをメモ
先ほどのSQL textとINDEX
RANGE SCANの実行計画が
登録されていることを確認
69
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
SPMでヒントで指定した実行計画をヒント句無しのSQLに適用
INDEXスキャンをFULLスキャンに変更する例(4/8)
 ヒントを使用してSQLの実行計画をFULLスキャン(TABLE ACCESS FULL)に変更
select /*+ FULL (TAB2) */ * from tab2 where c2 > 999;
実行計画
-------------------------------------------------Plan hash value: 2156729920
-------------------------------------------------| Id | Operation
| Name | Rows | Bytes |
-------------------------------------------------|
0 | SELECT STATEMENT |
|
18 | 4104 |
|* 1 | TABLE ACCESS FULL| TAB2 |
18 | 4104 |
--------------------------------------------------
 SQLIDとPLAN_HASH_VALUEを取得
select sql_id, plan_hash_value from v$sql where sql_text like
'select /*+ FULL (TAB2) */ * from tab2%';
SQL_ID
PLAN_HASH_VALUE
SQLIDをメモ
----------------------------------------------------- PLAN_HASH_VALUEをメモ
1j82gtnc0n4ps
2156729920
70
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
SPMでヒントで指定した実行計画をヒント句無しのSQLに適用
INDEXスキャンをFULLスキャンに変更する例(5/8)
 dbms_spm.load_plans_from_cursor_cacheに、SQL管理ベースラインに今までメモした内容
（ヒント指定後のSQL_ID 、ヒント指定後のPLAN_HASH_VALUE、ヒント指定前の
SQL_HANDLE）を入力してSQL管理ベースラインにFULL SCANの実行計画をロード
var res number
exec :res := dbms_spm.load_plans_from_cursor_cache( sql_id => '&hinted_SQL_ID', plan_hash_value => &hinted_plan_hash_value, sql_handle => '&sql_handle_for_original');
hinted_sql_idに値を入力してください: 1j82gtnc0n4ps
<--- ★ ヒント指定後の SQL_ID
hinted_plan_hash_valueに値を入力してください: 2156729920
<--- ★ ヒント指定後の PLAN_HASH_VALUE
sql_handle_for_originalに値を入力してください: SQL_2506061412f95de3
<--- ★ ヒント指定前の SQL_HANDLE
ヒント指定前のSQL_HANDLEを指定す
るところがポイント！！
71
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
SPMでヒントで指定した実行計画をヒント句無しのSQLに適用
INDEXスキャンをFULLスキャンに変更する例(6/8)
 ヒント句無しのSQLテキストに対して新しく２つ目のベースラインが表示される
72
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
SPMでヒントで指定した実行計画をヒント句無しのSQLに適用
INDEXスキャンをFULLスキャンに変更する例(7/8)
 新しく作成されたベースラインの詳細を確認
– ヒント句無しのSQLテキストに対してTABLE ACCESS FULLの実行計画が確認
ヒント句無しのSQLテキスト
TABLE ACCESS FULLの実行計画
73
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
SPMでヒントで指定した実行計画をヒント句無しのSQLに適用
INDEXスキャンをFULLスキャンに変更する例(8/8)
 FULL SCANのベースラインのみを固定済み YESに指定することで実行計画をFULL SCAN
に固定
– SQLテキストを変更なしにヒントで指定した実行計画に使用することが可能
– 一度登録すれば、使用する実行計画を簡単に切り替えることができるので、性能評価の際にも有効
「固定済＝YES」とすることで使用する実行計画を固定
※「固定=yes」は固定はするが履歴管理はしない設定
74
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
統合環境のリソース管理（１）
リソース利用の制御(リソース・マネージャ)
 ハードウェア・リソースの割当て方法を制御する機能
•並列度制限
•CPU
•アイドル時間制限など
•実行時間制限
 リソースの割り当てを要件に応じて定義し、制御
DAY_PLAN
昼間: 高いプライオリティ
OLTP ユーザー夜間：低いプライオリティ
DSS ユーザー
昼間: 低いプライオリティ
夜間：高いプライオリティ
BATCH ユーザー
75
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
OLTP
75%
NIGHT_PLAN
OLTP
25%
BATCH
75%
DSS
25%
時間帯で切り替え
統合環境のリソース管理（２）
リソース面からの統合の可否判断
 統合対象のサーバーの負荷状況から、統合後の負荷状況を見積もる
Consolidation Planner
 ビジネス要件/技術要件に基づいた統合のアドバイス
– CPUのアーキテクチャ、OSなど
– 所有部門、場所、サーバーのライフサイクル
 広範な統合の選択肢
– Physical to Physical （P2P）
– Physical to Virtual （P2V）
– Database Consolidation on Exadata
76
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
統合環境のサイジング
Consolidation Plannerの必要性
 サーバー、アプリケーションの負荷を加味した統合環境サイジング
– CPU、メモリ、ディスク、ネットワークそれぞれに統合後の負荷を見積る
77
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
+
＝
×
+
＝
○
Consolidation Plannerのアーキテクチャ
Consolidation Planner
統合の最適化
What-If 分析
制約の設定
サーバー情報
• 場所 / 部門 / 構成
アップロード
• DB / WLS / Apps
リソース使用率の分析
レポート
ターゲットの計画
EM リポジトリ
ターゲット構成情報
統合シナリオ
メトリック
保存
• OS
• CPU / メモリ/ ディスク / NIC
統合先サーバー
（ターゲットサーバー）
統合対象サーバー
（ソースサーバー）
統合
78
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
システム並行稼働の支援
ノンストップ移行・統合/Oracle GoldenGate
効果
移行日作業のコスト・イメージ
通常の移行
GoldenGateを活用
データ移行
アップ
グレード
作業
移行時間が長い
移行日前に
終了
整合性確認
ノンストップ移行が可能
整合性確認
新環境稼働
複雑な移行計画
移行/アップグレード当日の作業時間を大幅に削減
することで、リスクとコストも大幅削減
79
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
シンプルな統合・移行が可能
まとめ
80
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
まとめ
システム更改において、移行時間および
テストの効率化は重要なポイント
システム更改において、業務停止時間を
最小化しアプリケーションへの影響を検証
するテスト工数の効率化機能を活用する
ことで優雅で安心のサーバー変更プロ
ジェクトを実現します
81
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
オラクルエンジニア通信
オラクル製品に関わるエンジニアの方のための技術情報サイト
技術資料
インストールガイド・設定チュートリアル
etc. 欲しい資料への最短ルート
特集テーマ
Pick UP
性能管理やチューニングなど月間
テーマを掘り下げて詳細にご説明
アクセス
ランキング
他のエンジニアは何を見ているのか？
人気資料のランキングは毎月更新
技術コラム
SQLスクリプト、索引メンテナンスetc.
当たり前の運用/機能が見違える!?
http://blogs.oracle.com/oracle4engineer/
https://blogs.oracle.com/oracle4engineer/entry/cat_rac
82
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
オラクルエンジニア通信
83
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
84
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.