Oracle 19c移行の性能検討ポイント

Nov. 27, 2021Oracle Groudbrakers APAC Virtual Tour 2021 講演資料

Transcript

1. © 2021 NTT DATA Corporation Oracle 19c移行の性能検討ポイント a few consideration points of performance on Oracle 19c migration Nov. 27, 2021 Oracle Groudbrakers APAC Virtual Tour 2021 Kazuhiro Takahashi
2. © 2021 NTT DATA Corporation 2 自己紹介 • DBスペシャリスト • 様々な高難易度のSIプロジェクトでDBの設計・構築、移行、トラシュー等を対応 • 技術領域はOracle、Exadata、最近はOCI • コミュニティ活動（JPOUG、MOSC、Blog、Oracle ACE） https://community.oracle.com/mosc/profile/discussions/Kazuhiro
3. © 2021 NTT DATA Corporation 3 アジェンダ はじめに 1. オプティマイザの移行 2. スマートスキャンの活用 3. 保留統計の活用 4. テスト効率化 まとめ
4. © 2021 NTT DATA Corporation 4 はじめに • 大規模・ミッションクリティカルシステムの基盤更改（2017～2021） • 11g/9iを19c（ExadataX7-2）へ集約 • 2019年に19cにアップグレードを経験 • 上記経験から、性能検討ポイントを４点紹介 1. オプティマイザの移行 2. 保留統計の活用 3. スマートスキャンの活用 4. テスト効率化
5. © 2021 NTT DATA Corporation １．オプティマイザの移行
6. © 2021 NTT DATA Corporation 6 １．オプティマイザの移行 • オプティマイザの動作について詳細設計～パラメータ設計工程で検討 • 想定外挙動については性能試験の中で個別にチューニングを実施 ①移行基本 方針策定 ②初期化パラメータ設計 ④性能試験 (チューニング) ③統計情報運用 設計 オプティマイザの動作に ついての基本方針を 決める オプティマイザの動作に ついての基本方針を 決める オプティマイザの動作に ついての基本方針を 決める • 新機能調査（トップダウン）から新機能 や未サポートのパラメータを精査 • 現行パラメータ調査（ボトムアップ）から 引き継ぐべきパラメータを精査 オプティマイザの動作 についての基本方針 を決める バージョン変更に伴う変更点 を踏まえ統計情報（システム、 ディクショナリ、オブジェクト）の 運用方法を決める 性能試験で顕在化し た問題を個別にチュー ニング 構築・試験
7. © 2021 NTT DATA Corporation 7 １．オプティマイザの移行 ～①移行基本方針策定 • 更改における暗黙の要件 • 既存の実行計画を現行踏襲してそのまま活かしたい • 運用中の実行計画変動は極力抑えたい • オプティマイザの動きに影響を与える検討ポイント毎に方針を決める • 現行を踏襲するもの 例）統計ロック、バインド変数の利用、SPMは使わない、等 • 見直すもの 例）手動統計取得→自動統計取得 • 新機能を採用 例）保留統計の採用、適応問い合わせ最適化は使わない、等 表 更新 AP 統計情報 統計情報取得 ライブラリキャッシュ ハードパース 実行計画 クエリ • 統計取得方法 • 統計取得対象・タイミング • 適応統計 • 統計ロック・固定統計 • 保留統計 • バインド変数の利用 • 共有プールメモリサイズ • SQLのカーソルPIN・暖気 • ヒント • SPM • 適応問合せ最適化
8. © 2021 NTT DATA Corporation 8 使用しない 使用しない 【参考】適応問い合わせ最適化の利用方針 • 適応計画と自動再最適化の機能それぞれについて、システム特性を考慮し、利用方針を決定 適応計画 適応統計 Nested Loop結合をHash結合 に変更 SQLはチューニング済み、 HINTで固定 結合方法 パラレル分散方法 ビットマップ索引プルーニ ング ビットマップ索引未使用 PX SEND RANDOM を PX SEND HASH に変更 非効率なビットマップ索引のプルーニ ング 動的統計 (ダイナミックサンプリング) 自 動 再 最 適 化 統計フィードバック (カーディナリティフィード バック) パラレルクエリの限定的な使用 性能フィードバック SQL計画ディレクティブ・ 自動拡張統計 オプティマイザの機能 説明 システム特性 統計情報が不足していると、動的に 統計を取得 クエリ実行時に返却される行数を監 視し、乖離があれば実際の行数を もとにハードパース パラレル分散方法やパラレル化をや める 追加の統計情報を取得 統計情報は計画的に取得 （動的に取得する必要なし） SQLはチューニング済み、 HINTで固定 動的にパラレル度は変更しない (リソースを考慮しPARALLELヒ ントを明示的に使う) SQLはチューニング済み、 HINTで固定 optimizer_adaptive_ plans optimizer_adaptive_ plans optimizer_adaptive_ statistics optimizer_adaptive_ statistics 利用方針
9. © 2021 NTT DATA Corporation 9 １．オプティマイザの移行 ～②初期化パラメータ設計 • 新機能調査（トップダウン）から新機能や未サポートのパラメータを精査 • 11gからの移行では、特に適応問い合わせ最適化のパラメータを確実に設定 • ダイレクト・パス・ロードのオンライン統計収集も注意が必要 • 現行パラメータ調査（ボトムアップ）から引き継ぐべきパラメータを精査 • 10053トレースを取得し、パラメータを突合し、設定値の差について取り込むべきかを精査する • 以下を新・旧システムで実行し、トレース内のオプティマイザ関連パラメータ一覧を確認する • 値が一致していないものについて、特に旧システムで明示的に変更しているものは、理由を確認し、取り 込むべきか精査する • 隠しパラメータは基本的に変更しない方が良い（不具合回避目的でサポート指示のもの変更するもの） -- to enable the 10053 trace alter session set events ‘10053 trace name context forever’; <SQL実行> -- stop tracing alter session set events ‘10053 trace name context off’; -- to enable the 10053 trace alter session set events ‘10053 trace name context forever’; <SQL実行> -- stop tracing alter session set events ‘10053 trace name context off’;
10. © 2021 NTT DATA Corporation 10 【参考】10053トレース オプティマイザの動きに影響するパラメータ一覧 *************************************** PARAMETERS USED BY THE OPTIMIZER ******************************** ************************************* PARAMETERS WITH ALTERED VALUES ****************************** Compilation Environment Dump _fix_control_key = 1315652421 Bug Fix Control Environment ************************************* PARAMETERS WITH DEFAULT VALUES ****************************** Compilation Environment Dump optimizer_mode_hinted = false optimizer_features_hinted = 0.0.0 parallel_execution_enabled = true parallel_query_forced_dop = 0 parallel_dml_forced_dop = 0 parallel_ddl_forced_degree = 0 parallel_ddl_forced_instances = 0 _query_rewrite_fudge = 90 optimizer_features_enable = 19.1.0 _optimizer_search_limit = 5 cpu_count = 1 active_instance_count = 1 parallel_threads_per_cpu = 1 hash_area_size = 131072 bitmap_merge_area_size = 1048576 sort_area_size = 65536 ・・・ デフォルトから変更したもの デフォルト値のもの
11. © 2021 NTT DATA Corporation 11 【参考】オプティマイザ系初期化パラメータ 説明 設定値 デフォルト パラメータ No 動的な実行計画の変動を抑止するため、適応計画・適応統 計、動的統計は使用しない FALSE TRUE optimizer_adaptive_plans 1 FALSE FALSE optimizer_adaptive_statistics 2 TRUE FALSE optimizer_adaptive_reporting_only 3 0 2 optimizer_sampling 4 バインドピークは使用しない（現行踏襲） FALSE TRUE _optim_peek_user_binds 5 カーディナリティフィードバックは使用しない（現行踏襲） FALSE TRUE optimizer_use_feedback 6 SPMは使用しない（現行踏襲） FALSE TRUE optimizer_use_sql_plan_baselines 7 パラレル実行ノードを固定（現行踏襲） TRUE FALSE parallel_force_local 8 動的にパラレル度を変更しない（現行踏襲） FALSE FALSE parallel_adaptive_multi_user 9 動的にパラレル度を変更しない MANUAL MANUAL parallel_degree_policy 10 ダイレクト・パス・ロードのオンライン統計収集は使用しない FALSE TRUE _optimizer_gather_stats_on_load 11 • オプティマイザ系初期化パラメータの検討例 ※赤字はデフォルトからの設定変更
12. © 2021 NTT DATA Corporation 12 １．オプティマイザの移行 ～③統計情報運用設計 基本方針に従いCDB、PDB毎に各種統計情報の運用方法を設計する 例） 手動統計取得 → 自動統計取得 ※メンテナンスウィンドウは従来の運用に合わせる 統計情報は即時反映 → 保留統計の採用 ヒストグラムのバケットサイズはデフォルト（254） → 254固定（METHOD_OPT SIZE 254） ※従来の頻度ヒストグラム、高さ調整済みヒストグラムに加え、19c(12c)では上位頻度ヒストグラム、ハイブリッドヒストグラムが追加 PDB CDB 統計情報 基本的に自動統計取得に 任せる ・業務スキーマ毎にプリファ レンス ・ヒストグラムのバケットサ イズ ・オブジェクト毎に保留統計 や統計ロックを設定 AWRなど業務外のオブジェ クトの統計情報は基本的 に自動統計情報取得に任 せる ・ワークロードかノーワーク ロードか ・Exadataモードか ・運用方法（同じ統計情 報を全環境で利用、更新 しない等） ・自動統計の有効・無効 ・メンテナンスウィンドウ ※性能試験で統計固定したい場 合は取得対象をoracleに変更 自動統計 ・自動統計の有効・無効 ・メンテナンスウィンドウ ディクショナリ・固定統計 ディクショナリ(dba...)や固 定オブジェクト(x$...)などの 業務外のオブジェクトの統 計情報 オブジェクト統計 スキーマのテーブル・索引等 の統計情報 システム統計 IOやCPU性能に関する統 計情報。Exadata特有の モードがある メンテナンスウィンドウで統計情報 を自動取得する機能 取得対象はディクショナリのみ (auto)か、ディクショナリ＋オブジェ クト統計か(oracle)を設定できる
13. © 2021 NTT DATA Corporation 13 １．オプティマイザの移行 ～④性能試験 • 性能試験で顕在化した遅延や異常終了に対し、個別に原因分析・対処を実施 • 実行計画の解析には10053トレースが有効（SQLチューニングの有識者が必要） • 遅延解析には待機イベントやExadata(スマートスキャン)など総合的な知識が必要 対処 解析 直接原因 事象 No _smm_min_sizeパラメータを旧 SORT_AREA_SIZE相当に変更 SORT_AREA_SIZEから PGA_AGGREGATE_TARGETに見 直した結果、1セッションあたりの最小 ソートサイズが小さく（1MB）なってし まい、コスト計算が現行と乖離した 実行計画（ソートが索引フ ルスキャンになっている） バッチ処理遅延 1 _optimizer_cost_based_transformatio nをlinierからoffに変更 _optimizer_cost_based_transf ormationが現行と異なっていた ※旧システム(11g)では不具合回避 のためoffとしていた経緯があった 実行計画（結合順が変化 し、絞り込みが甘い状態で 大きな表へのアクセス） ORA-1652 （TEMP溢れ）で 異常終了 2 バッファキャッシュのチューニング ジョブの並走による多重度増加の結果、 バッファキャッシュが不足し、遅延ブロッ ククリーンアウトが発生 フルスキャンでシングルブロッ クリード多発（リランで再発 しない） バッチ処理遅延 ３
14. © 2021 NTT DATA Corporation 14 【参考】10053トレース 結合順序DEPT→EMPのコスト計算（ハッシュ結合がベスト） OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. Permutations for Starting Table :0 Join order[1]: DEPT[T2]#0 EMP[T1]#1 *************** Now joining: EMP[T1]#1 *************** NL Join Outer table: Card: 100000.000000 Cost: 170.791355 Resp: 170.791355 Degree: 1 Bytes: … Best NL cost: 247202304.349216 resc: 247202304.349216 resc_io: 245835588.000000 resc_cpu: 40464154517536 resp: 247202304.349216 resp_io: 245835588.000000 resc_cpu: 40464154517536 … SM Join SM cost: 16808.246564 resc: 16808.246564 resc_io: 16753.000000 resc_cpu: 1635676266 resp: 16808.246564 resp_io: 16753.000000 resp_cpu: 1635676266 … HA Join HA cost: 5988.246099 resc: 5988.246099 resc_io: 5967.000000 resc_cpu: 629029877 resp: 5988.246099 resp_io: 5967.000000 resp_cpu: 629029877 Best:: JoinMethod: Hash Cost: 5988.246099 Degree: 1 Resp: 5988.246099 Card: 991827.342696 Bytes: *********************** Best so far: Table#: 0 cost: 170.791355 card: 100000.000000 bytes: 3400000.000000 Table#: 1 cost: 5988.246099 card: 991827.342696 bytes: 87280776.000000 DEPT→EMPの結合順序 Nested Loop結合のコスト計算 ソートマージ結合のコスト計算 ハッシュ結合のコスト計算 DEPT→EMPでベストな結合方法は ハッシュ結合
15. © 2021 NTT DATA Corporation 15 【参考】10053トレース SQLと最終的な実行計画（DEPT→EMPのハッシュ結合） user_id=0 user_name=SYS module=sqlplus@localhost.localdomain (TNS V1-V3) action= sql_id=900b5j3m4apqt plan_hash_value=615168685 problem_type=3 command_type=3 ----- Current SQL Statement for this session (sql_id=900b5j3m4apqt) ----- select * from scott.emp t1, scott.dept t2 where t1.deptno=t2.deptno sql_text_length=68 sql=select * from scott.emp t1, scott.dept t2 where t1.deptno=t2.deptno ----- Explain Plan Dump ----- ----- Plan Table ----- ============ Plan Table ============ --------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 5988 | | | 1 | HASH JOIN | | 969K | 83M | 5988 | 00:01:12 | | 2 | TABLE ACCESS FULL | DEPT | 98K | 3320K | 171 | 00:00:03 | | 3 | TABLE ACCESS FULL | EMP | 977K | 51M | 2472 | 00:00:30 | --------------------------------------+-----------------------------------+ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------ 1 - SEL$1 2 - SEL$1 / "T2"@"SEL$1" 3 - SEL$1 / "T1"@"SEL$1" ------------------------------------------------------------ Predicate Information: ---------------------- 1 - access("T1"."DEPTNO"="T2"."DEPTNO") 実行計画 セッション情報とSQL
16. © 2021 NTT DATA Corporation 16 【参考】遅延ブロッククリーンアウトとは • バッファキャッシュに収まりきらない大きなトランザクションでは、未コミットのブロックがバッファキャッ シュからストレージに未コミット状態で書き込まれる • Oracleはコミット時にこの状態は変更せず、次回SELECT時にUNDOを参照しコミット済みに 変更する。この処理を遅延ブロッククリーンアウトと呼ぶ • これが発生するとSELECT時にUNDOへのシングルブロックリードが多発し遅延の原因になる。2 回目の実行ではクリーンアウト済みのため、遅延が発生しない 業務表領域 業務表領域 UNDO ストレージサーバ ストレージサーバ 表 ①TRUNCATE ②INSERT/COMMIT ③DBWR ④SELECT （スマートスキャン） 表 ⑤未コミットブロックのため シングルブロックを返却 ⑥コミット済みチェック のためUNDOを シングルブロックリード ⑧DBWRが コミット済み ブロックを反映 REDO バッファキャッシュの利 用率が上がると、未コ ミットの状態でブロック がバッファキャッシュ上 からフラッシュされる コンベンショナルINSERTは バッファキャッシュ上に書き込 み、DBWRがディスクに非同 期反映する 未コミット状態のブロッ クが多いとUNDOへの アクセス量が増え遅 延につながる 未コミットブロックだとス マートスキャンは不可 ⑦コミット済み ブロックに変更し REDO生成
17. © 2021 NTT DATA Corporation ２．保留統計の活用
18. © 2021 NTT DATA Corporation 18 従来の統計情報管理 保留統計を利用した統計情報管理 ２．保留統計の活用 統計情報(保留) 保留統計を有効化 表 統計情報取得 （メンテナンスウィンドウ） 保留統計公開 （パブリッシュ） AP 統計情報 業務APの実行計 画は公開しない限り 統計情報取得の影 響を受けない 表 AP 統計情報 業務APの実行計 画は統計情報取得 の影響を受ける 統計情報取得 （メンテナンスウィンドウ） • 保留統計とは、統計情報は取得したタイミングで即時反映するのではなく、一時的に保留状態 にしておき、任意のタイミングで反映（公開）することができる機能（12c～） • 保留統計を参照するようにセッションレベルで統計を切り替えることができるため、explain plan for等で実行計画を反映前に事前に確認することができる • 統計情報取得によって突然実行計画が変わり性能劣化を招くような事態を未然に防ぐことがで きる DML DML AP AP
19. © 2021 NTT DATA Corporation 19 ２．保留統計の活用 • 旧システムでは運用として実行計画の監視を行っていた • 統計情報は日次で取得しており、検知するとかけつけ等対応が必要となるため運用負荷が高 かった • 更改後は保留統計を活用し、実行計画を即時反映せず、精査してからパブリッシュする運用に 改善 統計情報取得 （日次） 実行計画チェック （日次） メール通知 登録SQL・ 実行計画 NG 統計情報取得 （メンテナンスウィンドウ） 実行計画チェック （隔週） メール通知 登録SQL・ 実行計画 NG 保留統計 公開 OK 保留統計 当日実行計画（保留統計） 登録実行計画 （過去実績） 当日実行計画 登録実行計画 （過去実績） 旧システム 更改後（保留統計を利用） SE判断 （かけつけ） SE判断 （翌営） 主要なSQLと 実行計画を登 録する 当日の実行 計画と登録さ れた実行計画 を比較 新規実行計 画の場合は必 要に応じてか けつけ対応 保留統計のた め即時反映さ れない 保留統計での 実行計画と登 録された実行 計画を比較 問題なければ保留統計を公 開。新規実行計画の場合は 即時対応不要（精査してか ら手動公開） 統計 比較 比較
20. © 2021 NTT DATA Corporation 20 ２．保留統計の活用 ～19cで発生した問題① 保留統計をため込むと、統計情報取得処理が遅延する 問題① 保留統計をため込むと、統計取得に伴う過去保留統計(ヒストグラム)の削除処理に時間がかかり、統計取得が遅延することがある。 事象 保留統計を多くの表・パーティションに対してヒストグラム込みで取得する（公開はしない）。この状態で、特定の表の統計情報を取得する 発生条件 製品仕様。統計取得時に、以下DocIDに下記ヒストグラム履歴削除のDELETE文が遅延するため、保留統計はためこまずに公開するよう 記載されている。 ※実際はこのDELETE文の絞り込みに利用している索引（I_WRI$_OPTSTAT_H_ST）は非効率であり、別の索引 （I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST）を使った方が保留統計の状態に依存せず絞り込みが可能となる 原因 以下のようなSQLパッチで改善することを確認（19.3）※バージョンや環境によりSQLIDや実行計画は変わるため確認は必要 対処方法 • When Number of Pending Statistics Increases, It Takes Time to Collect Statistical Information (ドキュメントID 2642768.1) 参考 declare patch_name varchar2(20); begin patch_name := dbms_sqldiag.create_sql_patch( sql_id=>'d8yp8608d866z', hint_text=>'index(@DEL$1 WRI$_OPTSTAT_HISTGRM_HISTORY@DEL$1 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST)', name=>'pend_del_patch'); end; / declare patch_name varchar2(20); begin patch_name := dbms_sqldiag.create_sql_patch( sql_id=>'d8yp8608d866z', hint_text=>'index(@DEL$1 WRI$_OPTSTAT_HISTGRM_HISTORY@DEL$1 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST)', name=>'pend_del_patch'); end; / SQL_ID d8yp8608d866z, child number 0 ------------------------------------- delete /* QOSH:PURGE_OLD_STS *//*+ dynamic_sampling(4) */ from sys.wri$_optstat_histgrm_history where :1 = savtime and obj# = :2 and intcol# = nvl(:3, intcol#) SQL_ID d8yp8608d866z, child number 0 ------------------------------------- delete /* QOSH:PURGE_OLD_STS *//*+ dynamic_sampling(4) */ from sys.wri$_optstat_histgrm_history where :1 = savtime and obj# = :2 and intcol# = nvl(:3, intcol#)
21. © 2021 NTT DATA Corporation 21 ２．保留統計の活用 ～19cで発生した問題② 保留統計でgather_table_statsで索引の統計情報を取得すると、索引の統計がnum_rows=0となってしま うことがある 問題② 保留統計を使っている表に対してgather_table_statsで表と一緒に索引の統計情報を取得するとき（カスケードオプション有効）、ある 条件を満たすと索引の統計が適切に取得できない(表の件数が有件なのに、索引の統計（num_rows）が0になってしまう) 事象 表の（パブリッシュされた）統計（num_rows）が0で、保留統計を使っており、gather_table_statsでカスケードオプション有効にした 場合に発生する 発生条件 製品不具合 ※新規不具合のため、パッチは確認できていないが、少なくとも19.11.0で再現することを確認している 原因 そもそも表が0件統計となる状況自体はレアケースなため、実運用上は顕在化する可能性は低い。新規表をリリースする場合等、0件が発 生するケースにおいては注意が必要。 運用対処として、保留統計の公開前に以下のチェックSQLで、索引統計のnum_rowsが0件となっている索引を抽出しチェックする。 問題となる索引については、手動で索引の統計情報をdbms_stats.gather_index_statsで手動で取得すればよい。 対処方法 再現テストケース等 gather_table_stats doesn't gather related index stats properly when pending stats are enabled https://community.oracle.com/mosc/discussion/4497632 参考 -- check sql of index stats select t.owner, t.table_name, dt.num_rows nrows, t.num_rows p_nrows, i.index_name, i.num_rows p_nrows from dba_tab_pending_stats t, dba_ind_pending_stats i, dba_tab_statistics dt where t.owner=i.owner and t.table_name=i.table_name and dt.owner=t.owner and dt.table_name=t.table_name and t.num_rows>0 and i.num_rows =0 and dt.num_rows = 0 order by t.owner, t.table_name,i.index_name; -- check sql of index stats select t.owner, t.table_name, dt.num_rows nrows, t.num_rows p_nrows, i.index_name, i.num_rows p_nrows from dba_tab_pending_stats t, dba_ind_pending_stats i, dba_tab_statistics dt where t.owner=i.owner and t.table_name=i.table_name and dt.owner=t.owner and dt.table_name=t.table_name and t.num_rows>0 and i.num_rows =0 and dt.num_rows = 0 order by t.owner, t.table_name,i.index_name;
22. © 2021 NTT DATA Corporation ３．スマートスキャンの活用
23. © 2021 NTT DATA Corporation 23 ３．スマートスキャンの活用 • 大量データロード・変換を伴う移行APで積極的に活用 • スマートスキャンとは、クエリの一部処理をストレージにオフロードすることで高速化するExadata の機能。アプリケーションから透過的に実行されるため通常は意識する必要なし • スマートスキャンを制御には、発生条件と阻害要因の両面の理解が必要 Storage Indexes Predicate Filtering Column Projection DBサーバへ転送される ブロック数を限定 Exadata 返却さ れる データ 返却さ れる データ 大量ブロック転送 ストレージ層 DB層 filter 返却さ れる データ Oracle
24. © 2021 NTT DATA Corporation 24 ３．スマートスキャンの活用 ～発生条件 • スマートスキャンの３つの発生条件 • Exadataであること • FULLスキャンであること • ダイレクトパスリードであること • ダイレクトパスリードの判断ロジックは非公開かつバージョンによって異なるため、わかりにくい • ダイレクトパスロードの発生条件のポイント • バッファキャッシュサイズに対するセグメントサイズ（統計のブロックサイズ）の割合で制御される • ３つの閾値（STT、MTT、VLOT） • シリアルとパラレルでダイレクトパスリードに切り替わるポイントが異なる • 実質的にクエリをパラレル（HINTでパラレル度を指定）にすることで、ダイレクトパスリードに誘導が可能 • NSMTIOトレースで条件を確認することができる
25. © 2021 NTT DATA Corporation 25 ３．スマートスキャンの活用 ～NSMTIOトレース • NSMTIOトレースでダイレクトパス発生条件を確認できる • qertbFetch関数のトレースにダイレクトパスの判断のトレースが出力される • STT、MTT、VLOTとオブジェクトサイズの大小関係 • キャッシュ（ローカル・リモート）読み取りのコスト • ストレージ削減ファクター（OLTP/EHCC圧縮） alter session set events 'trace[nsmtio]'; ＜任意のSQL文＞ alter session set events 'trace[nsmtio] off'; alter session set events 'trace[nsmtio]'; ＜任意のSQL文＞ alter session set events 'trace[nsmtio] off'; NSMTIOトレース取得例 セグメントサイズ1573ブロックで、ダイレクトパスリードが発生している例 [oracle@localhost trace]$ cat -n orclcdb_ora_3715.trc | grep NSMTIO | more ... 145 NSMTIO: kcbimd: nblks 1573 kcbstt 1161 kcbnbh 5807 kcbisdbfc 3 is_medium 0 146 NSMTIO: kcbcmt1: scann age_diff adjts last_ts nbuf nblk has_val kcbisdbfc 0 15374 0 58072 1573 0 0 147 NSMTIO: kcbivlo: nblks 1573 vlot 500 pnb 58072 kcbisdbfc 0 is_large 0 148 NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp) 149 NSMTIO: kcbdpc:DirectRead: tsn: 5, objd: 81221, objn: 81221 … NSMTIOトレース取得例 セグメントサイズ1573ブロックで、ダイレクトパスリードが発生している例 [oracle@localhost trace]$ cat -n orclcdb_ora_3715.trc | grep NSMTIO | more ... 145 NSMTIO: kcbimd: nblks 1573 kcbstt 1161 kcbnbh 5807 kcbisdbfc 3 is_medium 0 146 NSMTIO: kcbcmt1: scann age_diff adjts last_ts nbuf nblk has_val kcbisdbfc 0 15374 0 58072 1573 0 0 147 NSMTIO: kcbivlo: nblks 1573 vlot 500 pnb 58072 kcbisdbfc 0 is_large 0 148 NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp) 149 NSMTIO: kcbdpc:DirectRead: tsn: 5, objd: 81221, objn: 81221 … 説明 パラメータ セグメントブロック数 OBJECT_SIZE (nblks) バッファキャッシュの2% STT (kcbstt) バッファキャッシュの10% MTT (kcbnbh) バッファキャッシュの5倍 VLOT (vlot)
26. © 2021 NTT DATA Corporation 26 【参考】DPR判断とNSMTIOトレース（19c） Buffer Cache STT (Small Table Threshold) = _small_table_threshold =~2% of buffer cache size (b) direct path read Serial Parallel MTT (Medium Table Threshold) =_small_table_threshold x 5 =~10% of buffer cache size (c)direct path read (buffer cache considered)(*1)(*2) VLOT(Very Larget Object Threshold) = _small_table_threshold x 250 =~buffer cache size x _very_large_object_threshold (500%) Direct Path read decision and NSMTIO trace (19c) (d) multiblock read(*2) (b) direct path read(*1) NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp) NSMTIO: kcbdpc:DirectRead: … or NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: XXX (blocks), Threshold: MTT(XXX blocks), NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: XXX (blocks), Threshold: MTT(XXX blocks), NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp) NSMTIO: kcbdpc:DirectRead: … NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp) NSMTIO: kcbdpc:DirectRead: … (*1)can be multiblock read by setting "_serial_direct_read"=never; (*2)can be direct path read by setting "_serial_direct_read"=always; (a) direct path read (*1) (a) direct path read (*1) NSMTIO: qertbFetch:DirectRead:[OBJECT_SIZE>VLOT] NSMTIO: qertbFetch:DirectRead:[OBJECT_SIZE>VLOT] Dec. 2020 Kazuhiro Takahashi NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp) NSMTIO: kcbdpc:DirectRead: … or NSMTIO: kcbdpc:NoDirectRead:[CACHE_READ]: … (c)direct path read (buffer cache considered) • シリアルは2%でダイレクトパスリードになるが、バッファキャッシュに乗っ ている場合はそちらが利用される。10%を超えると常にダイレクトパス リードとなる • パラレルでは2%以下でもダイレクトパスリードとなる（バッファキャッ シュは考慮される）。2%を超えると常にダイレクトパスリードとなる
27. © 2021 NTT DATA Corporation 27 ３．スマートスキャンの活用 ～阻害要因 • スマートスキャンの阻害要因として知っておくべきポイント • 遅延ブロッククリーンアウト • 断片化（行連鎖、行移行） • スマートスキャンのはずが、シングルブロックリード多発で遅延する • AWR上にcell single block physical readが多発する • 読み取り一貫性、遅延ブロッククリーンアウトはUNDOブロックへのシングルブロックリード多発 • 断片化の場合は対象としている表へ（フルスキャンなのに）シングルブロックリード多発 • いずれもストレージサーバ単体で処理が完結することができないケース • 遅延ブロッククリーンアウトはバッファキャッシュでコミット済みに変更 • 複数ブロック（ストレージサーバ）にまたがっている断片化ブロックはバッファキャッシュ上で処理 • 対策はそれぞれの阻害要因を取り除くこと
28. © 2021 NTT DATA Corporation 28 ３．スマートスキャンの活用 ～阻害要因 • スマートスキャンが期待通りに働かない阻害要因に対し、実現可能な対処策を実施し効果を確 認する • 遅延ブロッククリーンアウトや断片化による遅延対策は、クエリ実行前の表の状態をスマートスキャ ン実行に適した状態にする点がポイント • 255カラム超え表は特に断片化に注意が必要。ダイレクトパスINSERTを活用する 備考 対処策 解析 症状 阻害要因 No 対処はSELECT処理では なく、そのデータを作成・更 新する処理に着目する • バッファキャッシュ拡張 • 該当表の更新時のAP 多重度を下げる • 更新時のコミット間隔を 増やす • ロード時にダイレクトパス INSERTの利用（バッ ファキャッシュの回避） • AWRでシングルブロックリード、 遅延ブロッククリーンアウト関連 の統計値(cleanout)の顕著 な増加 • ASHでアクセス対象がUNDO • リランで再現しない UNDOブロックへのシング ルブロックリード多発 遅延ブロッククリーン アウト １ ※255カラム以上の表では、 ダイレクトパスINSERTの利 用で行連鎖を回避可能 • 再編成（table move, index rebuild等） • ロード時にダイレクトパス INSERTの利用（※） • analyzeによる断片化状態の 確認 対象表へのシングルブロッ クリード多発 断片化（行連鎖・ 行移行） ２
29. © 2021 NTT DATA Corporation 29 【参考】ExadataのIO統計まとめ consistent gets Buffer cache Buffer cache cache fusion gc cr blocks received cell single block physical read cell multi block physical read cell physical IO Interconnect bytes physical read total IO request physical read total bytes Smart scan Flash cache cell flash cache read hits physical read total bytes optimized physical read requests optimized cell physical IO bytes saved by storage index Physical disks cell blocks helped by commit cache KTR (kernel transaction layer) KCB (kernel cache buffer mgmt) cell blocks processed by txn layer cell blocks processed by cache layer cell blocks processed by data layer cell blocks processed by index layer smart scaned blocks row blocks Storage index cell physical IO interconnect bytes returned by smart scan Storage Server Database Server KDS (kernel data scan) KIS (kernel index scan) Smart scan cell physical IO interconnect Bytes returned by smart scan cell physical IO bytes saved by storage index cell physical IO bytes eligible for predicate offload segment 読み取り一貫性や 断片化でシングルブ ロックリードで返却さ れる動き 遅延ブロッククリーン アウトでシングルブ ロックリードで返却さ れる動き 参考）Expert Oracle Exadata, Martin Bach他
30. © 2021 NTT DATA Corporation ４．テスト効率化
31. © 2021 NTT DATA Corporation 31 ４．テスト効率化 • 試験環境のテスト効率化には、DBオブジェクトとデータのライフサイクルに着目し、試験環境の構 築・リストア戦略を立てることが有効 • Oracleのバックアップ・リカバリ機能やマルチテナントの機能を適材適所に活用することでアジリティ の高い試験環境が実現できる 環境戻し 環境変更 リリース データ戻し データ準備 面追加 初期構築 DBオブジェクト RMAN － データパッチ RMAN/ フラッシュバック DB exp/imp (dataonly) PDBクローン imp データ － (imp) － imp 統計情報 － DDL － imp 表・索引・ビュー・PT PKG・関数 シノニム シーケンス トリガー － DDL リフレッシュ リフレッシュ リフレッシュ DDL リフレッシュ MVIEW・MLOG 環境変更 － － 環境構築 環境構築 DBLINK ディレクトリ － － PDBクローン 権限・スキーマ 表領域 初期化パラメータ サービス PDB － － － － CDB ファイル － － － 環境構築 接続識別子 PDBクローンでデー タ込みのDBを効率 的に量産できる フラッシュバックを利 用することで、戻し 時間短縮とテスト自 動化を可能に
32. © 2021 NTT DATA Corporation 32 ４．テスト効率化 • 機能試験（現新比較）やシステムテスト（負荷性能試験）では、DB断面戻しの効率化が試 験効率化（＝～AP品質）に直結する • 環境に応じた最適なDBリストア方式を選定する 例）大規模データ戻しには時間短縮のためフラッシュバックDBを利用 • テスト自動化の仕組みと連携するにはバックアップ・リカバリシェルを準備するなどの工夫が必要 検証（大規模）・本番 検証（小規模） 開発環境 • アーカイブログ運用 • DBサイズ大規模 • マルチテナント未使用 • アーカイブログ運用 • DBサイズ小規模 • マルチテナント（PDB）で 面分割 • ノーアーカイブログ運用 • DBサイズ小規模 • マルチテナント未使用 環境概要 • オンラインバックアップ（PDB）からの PDBフルリストア • フラッシュバックDBによるPDBリカ バリ オンラインバックアップ（PDB）からの PDBフルリストア コールドバックアップ（CDB）から のDBフルリストア バックアップ・ リカバリ方式 RMAN:数時間 FBDB:リストアポイントからの更新量に 依存（30分程度) RMAN:～10分程度 RMAN:～5分程度 リストア時間
33. © 2021 NTT DATA Corporation 33 ４．テスト効率化 ～フラッシュバックDBとRMANバックアップとの比較 • フラッシュバックDBはDBリストアが不要なためリカバリが高速、かつ、リストアポイント取得は瞬時 で完了するため、何度の同じ断面に戻したい場合に適している • 副作用として、更新時にFBログのIOが発生すること、および領域管理が必要 • フラッシュバックDBのリカバリ時間は更新量に依存 • RMANバックアップの代替にはならない（フラシュバックDBに失敗した場合など） RMAN フラッシュバックDB DB BK DB BK ログ ログ バックアップ リストア リカバリ DB DB ログ ログ リカバリ リストア ポイント設定 FBログ FBログ フラッシュ バックDB 追加のI/O。 領域必要 DBフルリストアのため 時間がかかる 更新前イメージで DBをリストアポイ ント近辺まで巻き 戻す リストアポイン トまでロール フォワード 瞬時に完了
34. © 2021 NTT DATA Corporation 34 【参考】フラッシュバックDBの仕組み • ブロック変更時にFBログに更新前ブロックを出力する ※すべての更新前ブロックイメージがFBログに出力される訳ではない • 離散的な古いDBの履歴ブロックの集合がFBログに蓄積される • リカバリ時、FBログでDBの特定の過去時点に近い状態に戻し、REDOでリカバリポイントまで ロールフォワードし、DB全体の整合性を取る 10 9 8 7 6 5 4 3 2 1 2 4 6 9 ブロックの状態 FBログ FBログ1 FBログ2 SCN=150 ①FBログをさかのぼり、リストアポ イント前のブロックイメージを探す ②SCN<=150の ブロックをリストア ③SCN=150まで ロールフォワード 時間 SCN=140 SCN=200 参考）Oracle Core Essential Internals for DBAs and Developers, Jonathan Lewis
35. © 2021 NTT DATA Corporation 35 ４．テスト効率化 ～FBDB活用イメージ • データ仕込み済みの初期断面でRMANバックアップを取得（FBDBリカバリ失敗時への備え） • リストアポイント取得→テスト実行→フラッシュバックDB を繰り返す、が基本 • データを育てながら、テスト実行を繰り返す • テスト工程の切り替えで、データは作り直す（性能試験→運用試験、など） • ポイントは、インカネーション跨ぎのリカバリが発生しないよう、テスト計画を立てること • リカバリ時の難易度が高い • PDBでは不可能（19c) DB BK1 リストア ポイント設定 テスト1 フラッシュ バックDB （PDB) リストア ポイント設定 テスト２ フラッシュ バックDB （PDB) ・・・ データ 仕込み データ 仕込み BK2 Inc:1 Inc:2 Inc:3 DB DB インカネーション 跨ぎ BK RMAN バックアップ 性能試験 運用試験 性能試験 運用試験
36. © 2021 NTT DATA Corporation 36 ４．テスト効率化 ～PDBのリカバリについて • 21cではPDBでreset incarnationを実行できるようになり、孤立したPDBブランチのある時点 にまでフラッシュバックやPITRできるようになった https://docs.oracle.com/cd/F39414_01/nfcon/details-pdb-point-in-time-recovery-or-flashback-to-any-time-in-the-recent-past-282450811.html 過去のある時点へのPDBポイント・イン・タイム・リカバリまたはフラッシュバック
37. © 2021 NTT DATA Corporation 37 まとめ • 19c移行の性能検討ポイントとして、オプティマイザの移行、保留統計の活用、スマートスキャン の活用、テスト効率化について、考慮すべきポイントを述べた • 総じて、設計工程できちんと検討すれば、大きな問題は未然に防ぐことができる • 残りの問題は試験工程で品質確保するが、ここで発生する未知の問題の解析は難しいものが 含まれる。蓄積されたノウハウ活用や有識者の確保が重要 • 設計工程で現行の運用課題に対し、新機能がどう活用できるかを盛り込む検討をするとよい。 現行の運用を変えるのはかなりパワーが必要だが、強い思いと情熱があれば必ず実現できる
38. © 2021 NTT DATA Corporation