【DB19c Upgrade!】エピソード03 パフォーマンスの安定性

パフォーマンスの安定性ヒント、コツ&アンダースコア Roy Swonger, Mike Dietrich & Daniel Overby Hansen
データベースのアップグレード、ユーティリティ、パッチ適用 UnsplashのLoic Lerayによるフォト

Vice President Database Upgrade, Utilities & Patching Roy F. Swonger
@RoyFSwonger Copyright © 2021, Oracle and/or its affiliates 2

Distinguished Product Manager Database Upgrade and Migrations Mike Dietrich https://MikeDietrichDE.com
@MikeDietrichDE mikedietrich Copyright © 2021, Oracle and/or its affiliates 3

Senior Principal Product Manager Cloud Migration Daniel Overby Hansen https://dohdatabase.com
@dohdatabase dohdatabase Copyright © 2021, Oracle and/or its affiliates 4

ステップ2 最新のRUのダウンロードとインストール MOSノート: 2118136.2 キー効果的なデータベース・アップグレードステップ1 Oracle 19cのダウンロー
ドとインストール eDelivery.oracle.com ステップ4 SPM、STA、RATによるパフォーマンス安定性 Copyright © 2021, Oracle and/or its affiliates 5 ステップ3 AutoUpgradeのダウンロードと利用 MOSノート: 2485457.1

Copyright © 2021, Oracle and/or its affiliates 6 一般的なパフォーマンスの
ベスト・プラクティス UnsplashのYannis Zauggによるフォト

パラメータ UnsplashのAlexander Andrewsによるフォト Copyright © 2021, Oracle and/or its affiliates
7

パラメータ | 一般的な推奨事項デフォルト Deprecated/desupported Underscores/events アプリケーションパラメータの数が少なくなるほど、パフォーマンスが向上 8 Copyright
© 2021, Oracle and/or its affiliates SQL> select name, value from v$parameter where isdefault='FALSE'; SQL> select name, value from v$parameter where isdefault='FALSE'; NAME VALUE _____________________________________________________________________________ _bug27355984_xt_preproc_timeout 1000 _cursor_obsolete_threshold 1024 _exclude_seed_cdb_view FALSE _optimizer_aggr_groupby_elim FALSE _use_single_log_writer TRUE audit_file_dest /u01/app/oracle/admin/CDB2/adump audit_trail NONE compatible 19.0.0 control_files /u02/fast_recovery_area/CDB2/control02.ctl

パラメータ | 一般的な推奨事項デフォルト Deprecated/desupported Underscores/events アプリケーション 9 Copyright ©
2021, Oracle and/or its affiliates プロ・チップ: アップグレード・ガイドには、非推奨およびサポートされないパラメータのリストが含まれています SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 1577055360 bytes Fixed Size 9135232 bytes Variable Size 385875968 bytes Database Buffers 1174405120 bytes Redo Buffers 7639040 bytes Database mounted. Database opened.

2021, Oracle and/or its affiliates 使用 • できるだけ少なくする • 必要ないものは設定しない計画を再作成して削除プロ・チップ: アップグレード時には、すべてのアンダースコアおよびイベントを削除することをお薦めします SQL> select name, value from v$parameter where substr(name, 0, 1) = '_' or name='event';

2021, Oracle and/or its affiliates アプリケーション固有の推奨事項に従う • E-Business Suite • Siebel • ...

コメントなしに変更を実装しないまたは、PFile内コメントの表示パラメータ | 変更のトラッキング Copyright © 2021, Oracle
and/or its affiliates 12 SQL> alter system set "_cursor_obsolete_threshold"=1024 comment='04-03-2021 Daniel: MOS 2431353.1, evaluate after upgrade' scope=both; SQL> select value, update_comment from v$parameter where name='_cursor_obsolete_threshold'; VALUE UPDATE_COMMENT ________ ___________________________________________________________ 1024 04-03-2021 Daniel: MOS 2431353.1, evaluate after upgrade *._cursor_obsolete_threshold=1024#04-03-2021 Daniel: MOS 2431353.1, evaluate after upgrade

COMPATIBLEとOPTIMIZER_FEATURES_ENABLE 互いに完全に独立 • COMPATIBLE • 機能の有効化 • Oracle 19cでは、常にデフォルト値19.0.0を使用 •
OPTIMIZER_FEATURES_ENABLE • 以前のリリースで使用したパラメータに戻すだけです • 可能な場合は使用しないようにします • これはスイス・アーミー・ナイフではありません。 • 多数の機能をオフにしますパラメータ Copyright © 2021, Oracle and/or its affiliates 13 通常、OPTIMIZER_FEATURES_ENABLEパラメータの変更は強くお薦めしません。Oracle Global Support の提案において短期間のメジャーとして使用してください。アップグレード後にOPTIMIZER_FEATURES_ENABLEパラメータを変更する場合は注意が必要です(ドキュメントID 1362332.1)

自動メモリー管理 | AMM Copyright © 2021, Oracle and/or its affiliates
14 Sounds like a good idea.... Oracle Databaseインスタンスでメモリーを自動的に管理およびチューニングできます。 " Database 19c、データベース管理者ガイド、第6章 Don't use it! パラメータMEMORY_TARGETおよびMEMORY_MAX_TARGETによる制御

AMM | ピットフォール Copyright © 2021, Oracle and/or its affiliates
15 1. LinuxではHugePagesはサポートされていません 2. PGAはSGAからメモリーをロバートできます 3. メモリー領域のサイズ変更にオーバーヘッドが発生 4. 問題 5. 4GB以上のメモリを搭載したAMMを使用できるのは、一部のプラットフォームのみです。 (MOSドキュメントID 2244817.1) I[DBT-11211] 物理メモリの合計が4GB以上の場合は、「自動メモリ管理」オプションは使用できません。 INS-35178 : 合計物理メモリーが4GBを超える場合、自動メモリー管理オプションは許可されません

AMM | もし本当に。 . Copyright © 2021, Oracle and/or its
affiliates 16 考えられるユース・ケース: 1. ASMインスタンス 2. 本番以外の小規模データベースプラットフォームのデータベース・インストール・ガイドを常に参照してください

Fix Control Persistence | DBMS_OPTIM_BUNDLE 概要チェック有効化出力結果
情報と問題 Fix Control Persistence 制御永続性の修正 • DBMS_OPTIM_BUNDLE • 12.1.0.2 2017年4月 (およびExadata以前)から存在 • アイデア: • オプティマイザの動作の修正を適宜有効化 • 修正はインストールされますが、デフォルトで無効になっています 17 Copyright © 2021, Oracle and/or its affiliates

情報と問題 18 Copyright © 2021, Oracle and/or its affiliates SQL> set serverout on SQL> exec dbms_optim_bundle.GetBugsForBundle; 19.10.0.0.210119DBRU: Bug: 29487407, fix_controls: 29487407 Bug: 30998035, fix_controls: 30998035 Bug: 30786641, fix_controls: 30786641 Bug: 31444353, fix_controls: 31444353 Bug: 30486896, fix_controls: 30486896 Bug: 28999046, fix_controls: 28999046 Bug: 30902655, fix_controls: 30902655 Bug: 30681521, fix_controls: 30681521 Bug: 29302565, fix_controls: 29302565 Bug: 30972817, fix_controls: 30972817 ...c

情報と問題 20 Copyright © 2021, Oracle and/or its affiliates 1) Current _fix_control setting for spfile: None 2) Final _fix_control setting for spfile considering current_setting_precedence is YES 29331066:1 28965084:1 28776811:1 28498976:1 28567417:1 28558645:1 29132869:1 29450812:1 29687220:1 29304314:1 29930457:1 27261477:1 31069997:1 31077481:1 28602253:1 29653132:0 29937655:1 30347410:1 30602828:1 30896685:0 29487407:1 30998035:1 30786641:1 31444353:0 30486896:1 28999046:1 30902655:1 30681521:1 29302565:1 30972817:1 30222669:1 31668694:1 31001490:1 30198239:7 30980115:1 30616738:0 31895670:0 19138896:1 31670824:0 9876287:1 30564898:1 32075777:0 30570982:1 3) Current _fix_control setting in memory: 29331066:0 28965084:0 28776811:0 28498976:0 28567417:0 28558645:0 29132869:0 29450812:0 29687220:0 29304314:0 29930457:0 27261477:0 31069997:0 31077481:0 28602253:0 29653132:0 29937655:0 30347410:0 30602828:0 30896685:0 29487407:0 30998035:0 30786641:0 31444353:0 30486896:0 28999046:0 30902655:0 30681521:0 29302565:0 30972817:0 30222669:0 31668694:0 31001490:0 30198239:0 30980115:0 30616738:0 31895670:0 19138896:0 31670824:0 9876287:0 30564898:0 32075777:0 30570982:0

情報と問題 21 Copyright © 2021, Oracle and/or its affiliates SPFILE内: • 有効にするために必要な再起動 *._fix_control='29331066:1','28965084:1','28776811:1','28498976:1','285 67417:1','28558645:1','29132869:1','29450812:1','29687220:1','29304314: 1','29930457:1','27261477:1','31069997:1','31077481:1','28602253:1','29 653132:0','29937655:1','30347410:1','30602828:1','30896685:0','29487407 :1','30998035:1','30786641:1','31444353:0','30486896:1','28999046:1','3 0902655:1','30681521:1','29302565:1','30972817:1','30222669:1','3166869 4:1','31001490:1','30198239:7','30980115:1','30616738:0','31895670:0',' 19138896:1','31670824:0','9876287:1','30564898:1','32075777:0','3057098 2:1'#added through dbms_optim_bundle package

情報と問題 22 Copyright © 2021, Oracle and/or its affiliates MOSノート: 2147007.1 - 自動修正管理の永続性ブログ投稿: DBMS_OPTIM_BUNDLEパッケージブログ投稿: 19.10.0で1回限りの作業が必要になる場合があります • Oracle 19.10.0 - パッチ31862593が必要です • それ以外の場合、19.10修正は有効にできませんブログ投稿: DBMS_OPTIM_BUNDLEがありません...again?! • パッケージがいくつかのRUで消えることがあります

パッチ | 重要な個別パッチ Copyright © 2021, Oracle and/or its affiliates
23 Oracle Database 19c重要推奨 One-off Patches (Doc ID 2720807.1)

統計 UnsplashのVeri Ivanovaによるフォト Copyright © 2021, Oracle and/or its affiliates
24

ディクショナリ統計 | 概要 Copyright © 2021, Oracle and/or its affiliates
25 SYSおよびその他のoracle保持スキーマに関する統計自動オプティマイザ統計収集によって実行される無効の場合、かわりにディクショナリ統計のみを機能させることを検討 SQL> exec dbms_stats.set_global_prefs('autostats_target','oracle');

26 Copyright © 2021, Oracle and/or its affiliates ディクショナリ統計 |
ギャザー手動でリフレッシュ: • アップグレードの前後 • (ソース)前および(ターゲット)論理移行後 • 主要なアプリケーションのアップグレード後手動で収集 SQL> BEGIN DBMS_STATS.GATHER_SCHEMA_STATS('SYS'); DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM'); END; / $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl ¥ -l /tmp ¥ -b gatherstats -- ¥ --x"begin dbms_stats.gather_schema_stats('SYS'); dbms_stats.gather_schema_stats('SYSTEM'); end;"

固定オブジェクトの統計 | 概要 Copyright © 2021, Oracle and/or its affiliates
27 アップグレード後、または他のデータベース構成の変更後、Oracleでは、Oracle Databaseで代表的なワークロードを実行した後で、固定オブジェクト統計を再収集することをお薦めします。 " アップグレード後すぐに実行しない Database 19cアップグレード・ガイド、第7章

28 Copyright © 2021, Oracle and/or its affiliates 固定オブジェクトの統計 |
定義何ですか? SQL> SELECT owner, table_name FROM dba_tab_statistics WHERE object_type = 'FIXED TABLE'; OWNER TABLE_NAME ________ _______________ SYS X$KQFTA SYS X$KQFVI SYS X$KQFVT SYS X$KQFDT SYS X$KQFCO SYS X$KQFOPT SYS X$KYWMPCTAB ... プロ・チップ: 動的統計(サンプリング)は、X$表には使用されません

アップグレード後ご自身に問いかけてください: これ覚えている? そうでない場合、レスキューへのDBMS_SCHEDULER

アップグレード後 1..sqlスクリプトの作成 BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => '"SYS"."GATHER_FIXED_OBJECTS_STATS_ONE_TIME"', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; END;', start_date => SYSDATE+7, auto_drop => TRUE, comments => 'Gather fixed objects stats after upgrade - one time' ); DBMS_SCHEDULER.ENABLE ( name => '"SYS"."GATHER_FIXED_OBJECTS_STATS_ONE_TIME"' ); END; /

アップグレード後 2..shスクリプトの作成 3.アップグレード後に.shスクリプトを実行 $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl ¥ -n 4 -e ¥ -C 'PDB$SEED' ¥ -b sched_gfos -d /home/oracle/sched_gfos/ sched_gfos.sql upg1.after_action=/home/oracle/sched_gfos/sched_gfos.sh

その他の状況次の後に、固定オブジェクトの統計も収集します: 1. 主要なアプリケーションのアップグレード 2. データベースでの新機能の使用 3. 大規模なデータベース構成変更システムのウォーム・アップ時に常に固定オブジェクト統計を収集 - 代表的なワークロード後 Oracle Database 19cを使用したオプティマイザ統計の収集のベスト・プラクティスを確認してくださいプロ・チップ: 自動統計収集では、完全に欠落している場合、固定オブジェクト統計のみが収集されます

統計 | チェック過去7日以内に統計が更新されていますか? SQL> select con_id, operation, target, end_time
from cdb_optstat_operations where ( (operation = 'gather_fixed_objects_stats') or (operation = 'gather_dictionary_stats' and (target is null or target in ('SYS', 'SYSTEM'))) or (operation = 'gather_schema_stats' and target in ('SYS', 'SYSTEM')) ) and end_time > sysdate - 7 order by con_id, end_time; CON_ID OPERATION TARGET END_TIME _________ _____________________________ _________ _________________________________________ 1 gather_schema_stats SYS 26-FEB-21 07.00.19.182084000 AM +01:00 1 gather_schema_stats SYSTEM 26-FEB-21 07.00.22.351981000 AM +01:00 1 gather_dictionary_stats 26-FEB-21 07.05.17.931954000 AM +01:00 1 gather_fixed_objects_stats 26-FEB-21 07.14.55.088707000 AM +01:00 2 gather_schema_stats SYS 26-FEB-21 07.02.40.485494000 AM +01:00 2 gather_schema_stats SYSTEM 26-FEB-21 07.02.46.151578000 AM +01:00 3 gather_schema_stats SYS 26-FEB-21 07.02.46.171862000 AM +01:00 3 gather_schema_stats SYSTEM 26-FEB-21 07.02.49.725878000 AM +01:00 Copyright © 2021, Oracle and/or its affiliates 33

統計 | アップグレード前に統計を収集 0 20 40 60 80 100 0:00:00
0:06:10 0:12:20 0:18:30 0:24:40 0:30:50 0:37:00 0:43:10 0:49:20 0:55:30 1:01:40 1:07:50 1:14:00 1:20:10 1:26:20 1:32:30 1:38:40 1:44:50 1:51:00 1:57:10 2:03:20 2:09:30 2:15:40 2:21:50 2:28:00 2:34:10 2:40:20 2:46:30 2:52:40 2:58:50 3:05:00 3:11:10 3:17:20 3:23:30 3:29:40 3:35:50 3:42:00 3:48:10 3:54:20 4:00:30 4:06:40 0 20 40 60 80 100 0:00:10 0:06:10 0:12:10 0:18:10 0:24:10 0:30:10 0:36:10 0:42:10 0:48:10 0:54:10 事前に統計を収集して12分節約ディクショナリおよび固定オブジェクト Copyright © 2021, Oracle and/or its affiliates 34

統計 | アップグレード時の良好な状態ディクショナリが大きいほど効果が大きくなりますこの例は、小さいハンズオン・ラボ・データベースの1つで行われています DURATION 削減ディクショナリおよび固定オブジェクト統計なし 15分55秒ディクショナリおよび固定オブジェクト統計の収集
14分10秒 11 % スキーマおよびクラスタ索引の統計を収集 13分41秒 3.4 前へ% 節約されたダウンタイムの合計 2分14秒全体で14% Copyright © 2021, Oracle and/or its affiliates 35

統計 | アップグレード時の良好な状態 Oracle E-Business Suiteのアップグレード期間 DURATION 削減ディクショナリおよび固定オブジェクト統計なし 10時間56分52秒
ディクショナリおよび固定オブジェクト統計の収集 52分42秒 93 % スキーマおよびクラスタ索引の統計を収集 52分25秒 0.5 前へ% 節約されたダウンタイムの合計 10時間4分14秒 93.5 %全体 Copyright © 2021, Oracle and/or its affiliates 36

失効 / 統計なし良好な統計統計 | アップグレード時の良好な状態 Copyright © 2021,
Oracle and/or its affiliates 37 ID OPERATION OPTIONS OBJECT_NAME ----- -------------------- -------------------- ----------- 0 UPDATE STATEMENT 1 UPDATE DEPENDENCY$ 2 FILTER 3 TABLE ACCESS FULL DEPENDENCY$ 4 INDEX FULL SCAN I_OBJ2 5 INDEX FULL SCAN I_OBJ2 6 TABLE ACCESS BY INDEX ROWID BATCHED OBJ$ 7 INDEX RANGE SCAN I_OBJ1 8 TABLE ACCESS BY INDEX ROWID BATCH OBJ$ 9 INDEX RANGE SCAN I_OBJ1 ID OPERATION OPTIONS OBJECT_NAME ----- -------------------- -------------------- ----------- 0 UPDATE STATEMENT 1 UPDATE DEPENDENCY$ 2 FILTER 3 TABLE ACCESS FULL DEPENDENCY$ 4 INDEX RANGE SCAN I_OBJ1 5 INDEX RANGE SCAN I_OBJ1 6 TABLE ACCESS BY INDEX ROWID BATCHED OBJ$ 7 INDEX RANGE SCAN I_OBJ1 8 TABLE ACCESS BY INDEX ROWID BATCH OBJ$ 9 INDEX RANGE SCAN I_OBJ1 9h 59m 23s 87ms 2s 33ms

システム統計 | 概要 Copyright © 2021, Oracle and/or its affiliates
38 システム統計は、I/OやCPUのパフォーマンスや使用率などのハードウェア特性を記述します。システム統計を使用すると、問合せオプティマイザが実行計画の選択時にI/OコストおよびCPUコストをより正確に見積もることができます。 " Database 19c SQLチューニング・ガイド、第10章 That sounds like a good idea

システム統計 | 推奨 Copyright © 2021, Oracle and/or its affiliates
39 ...ほとんどの場合、デフォルトを使用し、システム統計を収集しません。 "Oracle Exadata Database Machineで純粋なデータ・ウェアハウス・ワークロードをサポートするデータベースは、EXADATAオプションを使用して収集されたシステム統計を利用できます ...ワークロードが混在しているか、EXADATAシステム統計の使用による影響をテストする位置がない場合は、このプラットフォームでもデフォルトに戻ります。 Nigel Bayliss氏、オプティマイザ・ブログ

40 Copyright © 2021, Oracle and/or its affiliates システム統計 |
リファレンスシステム統計を削除するには(およびデフォルトに戻す) 参照: • オプティマイザ・ブログ、システム統計を収集しますか? • SQLチューニング・ガイド、システム統計 • SQLチューニング・ガイド、オプティマイザ統計の手動収集のガイドライン • データベース・パフォーマンス・チューニング・ガイド、セッションおよびシステム統計 SQL> EXEC DBMS_STATS.DELETE_SYSTEM_STATS

Oracle Database 12.2の新機能試してみてください、しかし... 注意 - SYSAUX表領域を食い潰す可能性があります統計アドバイザ | 概要
Copyright © 2021, Oracle and/or its affiliates 41

どのくらいの領域を使用していますか。統計アドバイザ | チェック Copyright © 2021, Oracle and/or its
affiliates 42 SQL> select occupant_name,space_usage_kbytes from v$sysaux_occupants; OCCUPANT_NAME SPACE_USAGE_KBYTES ------------------------------ ------------------ SM/ADVISOR 5901376 ... SQL> select * from ( select segment_name, owner, tablespace_name, bytes/1024/1024 "size(mb)", segment_type from dba_segments where tablespace_name='SYSAUX' order by bytes desc) where rownum <= 10; SEGMENT_NAME OWNER TABLESPACE SIZE(MB) SEGMENT_TYPE ------------------------------ ---------- ---------- ---------- --------------- WRI$_ADV_OBJECTS SYS SYSAUX 3600 TABLE WRI$_ADV_OBJECTS_IDX_01 SYS SYSAUX 1400 INDEX

自動統計アドバイザ・ジョブを無効にする場合 1. 21cで自動タスクを無効にします 2. 19c、バグ26749785のバック・ポートをリクエストし、無効にします 3. または、回避策で無効にします統計アドバイザ | 無効化
Copyright © 2021, Oracle and/or its affiliates 43 プロ・チップ: 自動統計アドバイザ・ジョブを無効にしても、手動実行は可能です SQL> begin dbms_advisor.set_task_parameter('AUTO_STATS_ADVISOR_TASK','_AUTO_MMON_INTERVAL',2147483647); dbms_advisor.set_task_parameter('AUTO_STATS_ADVISOR_TASK','_AUTO_STATS_INTERVAL',2147483647); end; / SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');

次の2つのMOSノートを参照してください: 1. 統計アドバイザのためにデータベースを12.2.0.1以上にアップグレードすると、SYSAUX表領域が急速に拡大する(ドキュメントID 2305512.1) 2. 12.2以降のオプティマイザ統計アドバイザの古いレコードをパージする方法(ドキュメントID 2660128.1) 統計アドバイザ |
パージ Copyright © 2021, Oracle and/or its affiliates 44

統計 | アップグレード後すぐに行うこと統計履歴保持期間の構成 • 領域使用率の確認: • 保持の確認 • デフォルト:
31日間 • 設定の調整 • 例: 10日間 SQL> select space_usage_kbytes/1024 mb from v$sysaux_occupants where occupant_name='SM/OPTSTAT'; SQL> select dbms_stats.get_stats_history_retention from dual; SQL> exec dbms_stats.alter_stats_history_retention(10); Copyright © 2021, Oracle and/or its affiliates 46

Copyright © 2021, Oracle and/or its affiliates 48 テストベスト・プラクティス
UnsplashのBattlecreek Coffee Roastersによるフォト

In 95% ヘルプ - アップグレードに問題があります … Unsplashのnikko macaspacによるフォト “ ”
アップグレード後のパフォーマンスの問題は、すべてのケースで「アップグレードの問題」が発生しています。または、データベース関連ではありません。リスクを軽減する方法は1つしかありません。テスト Copyright © 2021, Oracle and/or its affiliates 49

テスト | 代表的なミス実際のデータの使用量はわずか10% 人工データ・セット古いデータラップトップで実行されるテスト使用されるテスト・ツールはありません古い統計がリフレッシュされていませんテストとは。なんて時間の無駄なんだろう。
• 実質的な専門家が稼働開始後に修正 … UnsplashのElijah O'Donnellによるフォト " Copyright © 2021, Oracle and/or its affiliates 50

テスト | 完全性とコストコスト完全性低い高い 0% 100% 80%
- 90% Copyright © 2021, Oracle and/or its affiliates 51

テスト適切なテスト環境の設定 UnsplashのAlex Motocによるフォト Copyright © 2021, Oracle and/or its
affiliates 52

テスト環境 | アイデア Copyright © 2021, Oracle and/or its affiliates
53 スナップショット・スタンバイ・データベース • 既存のスタンバイ・データベースを活用 • RTOの短縮 - 無料のテスト環境を実現

54 Oracle Cloud InfrastructureのハイブリッドData Guard • 好きな数の作成 • Pay-as-you-go

55 CloneDB • Copy-on-write • NFSに格納されているデータ・ファイルのイメージ・コピーを使用します。デルタはローカルに書き込まれます

56 スナップショット・コピーPDB • 互換性のあるストレージ・システムが必要 • または、CloneDB機能を使用します(ソースPDBが読取り専用である必要があります)

57 ミラー・クローンPDBの分割 • ASMおよびOracle Database 18cが必要です • PDBのポイント・イン・タイム・バージョン

58 Exadataスパース・スナップショット • 省スペース - 迅速なプロビジョニング • クローンは引き続きExadataストレージ機能にアクセスできます

テスト統計に注意 UnsplashのCarlos Muzaによるフォト Copyright © 2021, Oracle and/or its
affiliates 59

60 Copyright © 2021, Oracle and/or its affiliates 統計 |
リフレッシュ? Oracle 19cへのアップグレード時にオブジェクト統計をリフレッシュする必要がありますか。 • 不要 • ただし、特に11.2からアップグレードすると、ヒストグラムが変更される可能性があります • 統計が失効したときにプランを段階的に変更しない • 可能な限り早くオブジェクト統計を取り戻す

61 Copyright © 2021, Oracle and/or its affiliates 統計 |
リフレッシュ? 統計を迅速に収集しますか? より高速に(CPUを使用できる場合) 高速(CPUを大量に使用可能かどうか) または、一致するテスト・システムから新しい統計をインポート SQL> exec dbms_stats.set_global_prefs('CONCURRENT','AUTOMATIC'); SQL> exec dbms_stats.set_global_prefs('CONCURRENT','AUTOMATIC'); SQL> exec dbms_stats.set_global_prefs('DEGREE', DBMS_STATS.AUTO_DEGREE); SQL> exec dbms_stats.set_global_prefs('DEGREE', DBMS_STATS.AUTO_DEGREE); プロ・チップ: Nigel BaylissのブログonHowを参照して、オプティマイザ統計を迅速に収集します。

62 Copyright © 2021, Oracle and/or its affiliates 統計の転送 |
概要プロ・チップ: 統計の転送の詳細は、『SQLチューニング・ガイド』を参照してくださいデータベース間でオプティマイザ統計を転送する場合、DBMS_STATSを使用してステージング表との間で統計をコピーし、表のコンテンツを宛先データベースからアクセス可能にするにはツールを使用する必要があります。 " Database 19c SQLチューニング・ガイド、第17章次の統計を転送できます • スキーマ • 表 • データベース(まれ) • ディクショナリおよび固定オブジェクト(まれ)

ユースケーステスト環境での本番統計の使用 • データなし • データのサブセット

ユースケース • 問合せチューニングに本番統計を使用

ユースケース再収集することなくアップグレード後に新しい統計を取得 • オプティマイザ変更によるメリット • 時間によって統計の完全な再収集が許可されない場合

ユースケース再収集せずに移行後に新しい統計を取得 • 文字セットの移行には新しい統計が必要です • 論理移行(トランスポータブル表領域、インポート)

ユースケースデータ・ポンプを使用した統計の転送より高速 • ネイティブ・データ・ポンプ統計エクスポートをDBMS_STATSに置き換えます • 優れたパフォーマンス

ワークフローソース・データベースユーザー・データ(SALESAPP) データ・ディクショナリ(SYS) オプティマイザ統計が配置される場所

ワークフローステージング表の作成統計をステージング表にエクスポートデータ・ポンプを使用したステージング表のエクスポート SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE ( ownname => 'SALESAPP', stattab => 'OPT_STATS_STG'); SQL> EXEC DBMS_STATS.EXPORT_SCHEMA_STATS ( ownname => 'SALESAPP', stattab => 'OPT_STATS_STG'); $ expdp SALESAPP ¥ DIRECTORY=mydirectory ¥ DUMPFILE=opt_stats_stg.dmp ¥ TABLES=OPT_STATS_STG

ワークフローターゲット・データベース転送ダンプ・ファイルデータ・ポンプを使用したステージング表のインポートステージング表から統計をインポートプロ・チップ: データベース・リンクを使用して、ソース・データベースから直接インポートすることもできます $ impdp SALESAPP ¥ DIRECTORY=mydirectory ¥ DUMPFILE=opt_stats_stg.dmp ¥ TABLES=OPT_STATS_STG SQL> EXEC DBMS_STATS.IMPORT_SCHEMA_STATS ( ownname => 'SALESAPP', stattab => 'OPT_STATS_STG');

デモ YouTubeで見る

わかりやすく • オプティマイザは、ユーザーが所有する表に格納されている統計を使用しません - 辞書からのみ • 統計をインポートすると、統計が最新になります(例:。失効していない) • 上位バージョンに転送できます - 統計表をアップグレードする必要がある可能性があります • 増分統計: オプションで同期をエクスポート SQL> EXEC DBMS_STATS.IMPORT_SCHEMA_STATS ( ... ORA-20002: 統計表"SALESAPP"."OPT_STATS_STG"のバージョンは古すぎます SQL> EXEC DBMS_STATS.UPGRADE_STAT_TABLE ('SALESAPP', 'OPT_STATS_STG'); SQL> EXEC DBMS_STATS.EXPORT_SCHEMA_STATS ( ... stat_category => 'OBJECT_STATS, REALTIME_STATS, SYNOPSES');

73 Copyright © 2021, Oracle and/or its affiliates ロック統計 |
概要 emをロックする時間です。ついに君はそれをやった。最終的な統計を作成しました。 emをロックする時間です。

ユースケース統計をロックして、統計の変更を回避できます。 " Database 19c SQLチューニング・ガイド、第15章 • 特定の静的環境 • 揮発性の高い表 • 動的統計の使用の有効化 • ...すべての例外

表示表統計のロック次の場所にロックすることもできます: • Schema-level • Partition-level 統計のロックを解除することもできます SQL> EXEC DBMS_STATS.LOCK_TABLE_STATS(ownname=>'MYAPP', tabname=>'MY_VOLATILE_TAB1'); プロ・チップ: 表統計のロックにより、索引およびパーティション統計もロックされます

言及する価値 • 統計をロックおよびロック解除すると、カーソルの無効化が発生 • 計画の安定性を実現するには、SQL計画管理を検討 • 統計アドバイザは、ロックされた統計について警告 • ロック情報はエクスポートされません

増分統計 | 概要 Oracle 11gで導入 Oracle 12.2.0.1により改善コンセプト • 低オーバーヘッドでパーティション・オブジェクトのグローバル統計を迅速に更新
• リストおよび間隔のパーティション化既知のPitfall • ディスク上の元の同期では、数百GBを簡単に消費できます • デフォルトでは、DBMS_STATSを使用した統計情報の転送には同期は含まれません新たな効率的な同期アルゴリズムにより、領域消費を大幅に削減できます 77 Copyright © 2021, Oracle and/or its affiliates

増分統計 | コンセプト ORDERS表 2020年5月22日 2020年5月23日 2020年5月18日 2020年5月19日 2020年5月20日 2020年5月21日
グローバル統計  元のパーティションの概要を新しいものに集約して生成されるグローバル統計新しいパーティションが表&データに追加されます   パーティション・レベルの統計と概要を集計して生成されるグローバル統計  SYSAUXから他のパーティションごとに概要を取得 新しいパーティションのパーティション統計を収集パーティション・レベル統計が収集される&概要  S1 S2 S3 S4 S5 S6 SYSAUX表領域 78 Copyright © 2021, Oracle and/or its affiliates S7 2020年5月24日

増分統計 | 重要な構成ステップ表単位で増分統計を有効化 Must-Do • 変更されたパーティションは新しい統計生成の対象にはなりませんオプション • 失効率の調整
- default: 10% SQL> EXEC dbms_stats.set_table_prefs(null,'ORDERS','INCREMENTAL','TRUE'); SQL> exec dbms_stats.set_database_prefs('INCREMENTAL_STALENESS','USE_STALE_PERCENT'); SQL> exec dbms_stats.set_database_prefs('STALE_PERCENT','15'); 79 Copyright © 2021, Oracle and/or its affiliates

80 Copyright © 2021, Oracle and/or its affiliates 増分統計 |
Oracle 19cへのアップグレード新しい非常に効率的なHyperLogLogアルゴリズムを使用 • 10倍のファクタにより、同期が大幅に増加 - 25x • Oracle 12.2.0.1以降に使用可能 2つのオプション • 古い同期と新しい同期の共存(デフォルト) • シンソプスの採集推奨 • 共存のため調整を使用して問題が発生

81 Copyright © 2021, Oracle and/or its affiliates 増分統計 |
Oracle 19cへのアップグレード古いものを新しいものに置き換える • 古い同期の削除 • 新規HLLアルゴリズムの選択 • 共存の禁止と強制的な収集関連項目: オプティマイザ・ブログ ORDERS SYSAUX表領域 P1には、12.2より前の形式の概要があります HLL形式のP1の新機能パーティション化表 P1 SQL> exec dbms_stats.set_table_prefs('XY','ORDERS','APPROXIMATE_NDV_ALGORITHM','HYPERLOGLOG'); SQL> exec dbms_stats.set_table_prefs('XY','ORDERS','INCREMENTAL_STALENESS','NULL'; SQL> exex dbms_stats.delete_table_stats(…, cascade_indexes=>FALSE, stat_category=>'SYNOPSES');

Copyright © 2021, Oracle and/or its affiliates 82 1. 収集
2.比較 3.分析 4. Tune 5. Manage 6. Test パフォーマンス安定性規定

SQLチューニング・セット(STS)を使用すると、SQL文と関連するメタデータを単一のデータベース・オブジェクトにグループ化できます。このメタデータは、チューニング目標の達成に使用できます。具体的には、SQLチューニング・セットは次の目的を実現します: • パフォーマンス・チューニング・アドバイザへの入力の提供 • データベース間のSQL転送 SQLチューニング・セット | 定義
Copyright © 2021, Oracle and/or its affiliates 83 Database 19c SQLチューニング・ガイド、第23章 "

SQLチューニング・セット | 定義 Copyright © 2021, Oracle and/or its affiliates
84 SQL文コンテキスト統計プラン

SQLチューニング・セット | 作成 Copyright © 2021, Oracle and/or its affiliates
85 S T S まず、SQLチューニング・セットを作成プロ・チップ: DBMS_SQLTUNEを使用してSQLチューニング・セットを作成することもできます SQL> BEGIN DBMS_SQLSET.CREATE_SQLSET ( sqlset_name => 'UPG_STS_1', description => 'For upgrade - from source' ); END; /

SQLチューニング・セット | 取得 Copyright © 2021, Oracle and/or its affiliates
86 次に、AWRからの文を取得プロ・チップ: DBSNMP、ORACLE_OCM、 LBACSYS、WMSYS、XDB、SYSTEMなどの他の内部スキーマを除外することを検討してください S T S SQL> DECLARE begin_id number; end_id number; cur sys_refcursor; BEGIN SELECT min(snap_id), max(snap_id) INTO begin_id, end_id FROM dba_hist_snapshot; open cur for select value(p) from table(dbms_sqltune.select_workload_repository( begin_snap => begin_id, end_snap => end_id, basic_filter => 'parsing_schema_name not in (''SYS'')', ranking_measure1 => 'elapsed_time', result_limit => 5000, attribute_list => 'ALL')) p; dbms_sqltune.load_sqlset('UPG_STS_1', cur); close cur; END; /

SQLチューニング・セット | 取得 Copyright © 2021, Oracle and/or its affiliates
87 カーソル・キャッシュから文を取得(オプション) 慎重 - システムにロードプロ・チップ: SQLチューニング・ガイドでは、特定のスキーマからすべての文をロードする方法を示します S T S SQL> BEGIN DBMS_SQLSET.CAPTURE_CURSOR_CACHE_SQLSET( sqlset_name => 'UPG_STS_1', time_limit => 900, repeat_interval => 60, capture_option => 'MERGE', capture_mode => DBMS_SQLTUNE.MODE_ACCUMULATE_STATS, basic_filter => 'parsing_schema_name not in (''SYS'')', sqlset_owner => NULL, recursive_sql => 'HAS_RECURSIVE_SQL'); END; /

SQLチューニング・セット | 輸送 Copyright © 2021, Oracle and/or its affiliates
88 S T S ソース・データベースのステージング表への梱包データ・ポンプによるエクスポートオプションで、DBMS_SQLTUNE.REMAP_STGTAB_SQLを使用して CON_DBID間の再マップを行います SQL> BEGIN DBMS_SQLTUNE.CREATE_STGTAB_SQLSET ( table_name => 'UPG_STGTAB_1'); DBMS_SQLTUNE.PACK_STGTAB_SQLSET ( sqlset_name => 'UPG_STS_1', staging_table_name => 'UPG_STGTAB_1'); END; $ expdp user ¥ directory=mydirectory dumpfile=upg_stgtab_1.dmp tables=UPG_STGTAB_1

SQLチューニング・セット | 輸送 Copyright © 2021, Oracle and/or its affiliates
89 S T S ターゲット・データベースへのデータ・ポンプによるインポートステージング表の解凍 $ impdp user ¥ directory=mydirectory dumpfile=upg_stgtab_1.dmp tables=UPG_STGTAB_1 SQL> BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET ( sqlset_name => '%' replace => true staging_table_name => 'UPG_STGTAB_1' ); END; /

SQLチューニング・セット | ライセンス SQLチューニング・セットには、データベース・サーバーAPIやコマンドライン・インタフェースを使用してアクセスすることもできます。SQLチューニング・セットを管理するためのDBMS_SQLSETパッケージのすべてのサブプログラムの使用は、EEおよびEE-ESオファリングの一部です。 " Database 19cデータベース・ライセンス情報ユーザー・マニュアル SQLチューニング・セットには、データベース・サーバーAPIやコマンドライン・インタフェースを使用してア
クセスすることもできます。SQLチューニング・セットを管理するためのDBMS_SQLSETパッケージのすべてのサブプログラムの使用は、EEおよびEE-ESオファリングの一部です。また、DBMS_SQLTUNEパッケージの一部である次のサブプログラムは、SQLチューニング・セットを管理するための古いインタフェースを提供し、EEおよびEE-ESオファリングの一部でもあります: ADD_SQLSET_REFERENCE CAPTURE_CURSOR_CACHE_SQLSET CREATE_SQLSET CREATE_STGTAB_SQLSET DELETE_SQLSET DROP_SQLSET LOAD_SQLSET PACK_STGTAB_SQLSET REMOVE_SQLSET_REFERENCE SELECT_CURSOR_CACHE SELECT_SQLSET SELECT_WORKLOAD_REPOSITORY UNPACK_STGTAB_SQLSET UPDATE_SQLSET Copyright © 2021, Oracle and/or its affiliates 90

Copyright © 2021, Oracle and/or its affiliates 91 1. Collect
2. Compare 3. Analyze 4. Tune 5. Manage 6. Test パフォーマンス安定性規定

AWR | 差異レポート Copyright © 2021, Oracle and/or its affiliates
92 2つの異なる期間からのAWRレポートの比較 1. AWRスナップショット 2. ワークロードの実行 3. AWRスナップショット 4. アップグレード 5. AWRスナップショット 6. ワークロードの実行 7. AWRスナップショット 8. 比較

93 スクリプトawrddrpt.sqlの使用

94 スクリプトawrddrpt.sqlの使用 Enterprise Edition +診断パックが必要ですプロ・チップ: 移行の場合、awrext.sqlおよび awrload.sqlを使用したAWRデータのエクスポートとインポート

SQLパフォーマンス・アナライザ | SPA " Oracle Database Real Application Testingデータ・シート SPAは、変更前および変更後の環境でSQL文を分離してシリアルに実行することで、SQL実行計
画および統計に対する環境変更を詳細に評価します。 SPA機能は、既存のSQLチューニング・セット(STS)、SQLチューニング・アドバイザおよびSQL計画管理機能と適切に統合されています。 Enterprise Edition + Real Application Testingが必要です Copyright © 2021, Oracle and/or its affiliates 96

SPA | コンセプト 11.2.0.4 COMPARE COMPARE SQLチューニングSetProductionワークロードアップグレード 19c SQL、
スキーマ、バインド、 ... SPA テストの実行アップグレード後アップグレード前プラン経過時間、CPU時間、バッファ読取り、ディスク読取り... プロ・チップ: 移行の場合は、ターゲット・データベースにSTSをインポート Copyright © 2021, Oracle and/or its affiliates 97

SPA | 劣化したレポート生産からワークロード testexecutionから Copyright © 2021, Oracle
and/or its affiliates 98

SPA | 劣化したレポート Copyright © 2021, Oracle and/or its affiliates
99

100

101

SPA | 継続的な改善 19c SPA テストの実行 Implement change COMPARE COMPARE
Copyright © 2021, Oracle and/or its affiliates 102

103

SQLチューニング・アドバイザ Copyright © 2021, Oracle and/or its affiliates 105 "
Database 19c SQLチューニング・ガイド、第24章 SQLチューニング・アドバイザは、Oracle Database Tuning PackのSQL診断ソフトウェアです。 ... SQLチューニング・アドバイザは、パフォーマンスが最適でないSQL文に関連する問題を解決するためのメカニズムです。

結果のタイプ: 1. オブジェクト統計の収集 2. 索引の作成 3. SQL文のリライト 4. SQLプロファイルの作成 ....など
SQLチューニング・アドバイザ | 結果 Copyright © 2021, Oracle and/or its affiliates 106 プロ・チップ: SQL Developerには、SQL チューニング・アドバイザの優れたインタフェースがあります

SQLプロファイル Copyright © 2021, Oracle and/or its affiliates 107 "
Database 19c SQLチューニング・ガイド、第26章 SQLプロファイルは、SQL文固有の補助統計を含むデータベース・オブジェクトです。 ... SQLプロファイルの修正された統計は、オプティマイザのカーディナリティの見積りを改善でき、これによりオプティマイザはより優れた計画を選択できます。

1. Tuning Packの一部 • 一部のクラウド製品に含まれる 2. オプティマイザによる計画の選択の原因となる一連のヒントを格納 3. 1つの文のみに影響 SQLプロファイル
| ファクト Copyright © 2021, Oracle and/or its affiliates 108

4. プロファイルを有効または無効にできます 5. アプリケーションに対して透過的 • アプリケーションの変更は必要ありません 6. 永続的でトランスポータブル • ドキュメンテーション
7. FORCE_MATCH=TRUEを使用したリテラルで役立ちます SQLプロファイル | ファクト Copyright © 2021, Oracle and/or its affiliates 109

SQLプロファイル | テスト Copyright © 2021, Oracle and/or its affiliates
110 1.選択した環境に対してのみプロファイルを有効にします 2.プロファイルの検証 - ライブ環境ではオプティマイザによって使用されない 3.すべてのセッション('DEFAULT')を受け入れて可視にします SQL> exec :p_name := dbms_sqltune.accept_sql_profile( task_name=>'TASK_21944', name=>'XT_PROFILE', category=>'TEST_ENV'); SQL> alter session set sqltune_category='TEST_ENV'; SQL> exec dbms_sqltune.alter_sql_profile( name=>'XT_PROFILE', attribute_name=>'CATEGORY', value=>'DEFAULT');

Real Worldの例 SQLチューニング・アドバイザの動作 Copyright © 2021, Oracle and/or its affiliates
111 UnsplashのAugust Phliegerによるフォト

© 2020 Oracle Corporation 6つのシンプルなステップ SQL TUNING ADVISOR 大幅な違いができる 1.問題の識別
Copyright © 2021, Oracle and/or its affiliates 112 2.候補文の選択 3.取引明細書詳細の取得 4.チューニング・タスクの実行 5.レポート結果の表示 6.推奨の適用 Copyright © 2021, Oracle and/or its affiliates 112

Real Worldの例 | SQLチューニング・アドバイザの動作 1. 解決する問題の特定 Copyright © 2021, Oracle
and/or its affiliates 113 私たちは、統計情報をstatsテーブルにエクスポートするのに1.5時間で済んだのに、インポートするのに48時間かかったのか理由を理解しようとしています。一方、SRエンジニアはなにがしか回避策を作ろうとしているように見えます。なぜこんなに時間がかかっているのか、statsテーブルのインポートのプロセスを追跡したいと考えています。作成された statsテーブルには2.8GBのデータしか含まれていないので、分単位で読み込むことができるはずです。この問題を解決するために、皆様のご協力をお願いいたします。

Real Worldの例 | SQLチューニング・アドバイザの動作 4.SQLチューニング・タスクの作成と実行 Copyright © 2021, Oracle and/or
its affiliates 116 VARIABLE stmt_task VARCHAR2(64); EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'f4k19gvr3nu38'); EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task); SET LONG 10000 SET LONGCHUNKSIZE 10000 SET LINESIZE 100 SPOOL sql_tune_f4k19gvr3nu38.txt SELECT dbms_sqltune.report_tuning_task( :stmt_task )FROM DUAL; SPOOL OFF;

its affiliates 118

its affiliates 119

its affiliates 120

its affiliates 121

124 Copyright © 2021, Oracle and/or its affiliates SQLパッチ|概要 SQL文の修復
• ヒントの追加 • 透明 • 永続 • Oracle 11gで導入 • Oracle 11gおよび12.1: DBMS_SQLDIAG_INTERNAL • Oracle 12.2以上: DBMS_SQLDIAG • EEおよびSE2で使用可能 • ドキュメンテーション : • https://docs.oracle.com/en/database/oracle/oracle- database/19/arpls/DBMS_SQLDIAG.html#GUID-0F29CD05-6BF3-4EEB-90F5-E2465865C255 • 便利なスクリプト create_sql_patch.sql: • http://kerryosborne.oracle-guy.com/2013/06/06/sql-gone-bad-but-plan-not-changed/

Oracle 11.2および12.1 • DBMS_SQLDIAG_INTERNAL (undocumented) Oracle 12.2以上 • DBMS_SQLDIAG SQLパッチ|バージョン差異
Copyright © 2021, Oracle and/or its affiliates 125 プロ・チップ: SQLの完全なテキストのかわりにSQL IDを使用できます BEGIN SYS.DBMS_SQLDIAG_INTERNAL.i_create_patch( sql_text => 'select * big_table', hint_text => 'PARALLEL(big_table,10)', name => 'big_table_sql_patch'); END; / DECLARE l_patch_name VARCHAR2(4000); BEGIN l_patch_name := SYS.DBMS_SQLDIAG.create_sql_patch( sql_text => 'select * from big_table', hint_text => 'PARALLEL(big_table,10)', name => 'big_table_sql_patch'); END; /

SQL計画管理 | SPM Copyright © 2021, Oracle and/or its affiliates
129 " Database 19c SQLチューニング・ガイド、第27章 SQL計画管理では、SQL計画ベースラインと呼ばれるメカニズムが使用されます。これは、オプティマイザがSQL文に使用できる一連の受け入れられた計画です。 SQL計画管理では、SQL計画ベースラインと呼ばれるメカニズムが使用されます。これは、オプティマイザがSQL文に使用できる一連の受け入れられた計画です。 ... SQL計画管理では、計画変更によるパフォーマンスの低下を防ぎます。

SPM | コンセプト Copyright © 2021, Oracle and/or its affiliates
130 計画ベースライン反復可能なSQL OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE フィルタフィルタ: • スキーマ • SQLテキスト • モジュール • 処理計画A 計画A 受入済計画なしベースライン内次の実行プランB 何かが変更されました • 新しい統計 • 新規パラメータ • アップグレードプラン履歴プラン履歴 - SQL計画ベースラインの専用部分プランB 未受入 OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE プランAは使用されています

SQL計画ベースラインの計画は、次のとおりです: • 有効 • 受入済 • 固定ステータスを変更するには、DBMS_SPM.ALTER_SQL_PLAN_BASELINEを使用自動パージ・プロパティを設定することで、プランがパージされないようにすることもできます。 SPM
| プラン Copyright © 2021, Oracle and/or its affiliates 131 プロ・チップ: Accept属性は、テスト実行によってのみ設定できます

SPM | 発展 Copyright © 2021, Oracle and/or its affiliates
133 進化はメンテナンス・タスクSYS_AUTO_SPM_EVOLVE_TASKで発生 • 自動SQLチューニング・タスクの一部推奨事項を自動的に実装するかどうかを決定プランを手動で展開できます SQL> BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( parameter => 'accept_plans', value => 'true'); END; /

設定を確認してください保持の変更領域使用量の変更 SPM | 管理ベース Copyright © 2021, Oracle
and/or its affiliates 135 SQL> select parameter_name, parameter_value from dba_sql_management_config; PARAMETER_NAME PARAMETER_VALUE ___________________________________ __________________ AUTO_CAPTURE_ACTION AUTO_CAPTURE_MODULE AUTO_CAPTURE_PARSING_SCHEMA_NAME AUTO_CAPTURE_SQL_TEXT AUTO_SPM_EVOLVE_TASK OFF AUTO_SPM_EVOLVE_TASK_INTERVAL 3600 AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800 PLAN_RETENTION_WEEKS 53 SPACE_BUDGET_PERCENT 10 SQL> select parameter_name, parameter_value from dba_sql_management_config; SQL> exec DBMS_SPM.CONFIGURE('plan_retention_weeks', 5); SQL> exec DBMS_SPM.CONFIGURE('space_budget_percent', 5);

SPM | STSからロード Copyright © 2021, Oracle and/or its affiliates
136 計画A 受入済プラン履歴プランB 未受入プランC SQL> DECLARE cnt number; BEGIN cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET('UPG_STS_1'); END; / プランC 受入済 SQLチューニング・セット自動受入

計画ベースライン SPM | ユース・ケース Copyright © 2021, Oracle and/or its
affiliates 139 プロ・チップ: 関数 LOAD_PLANS_FROM_SQLSETは、計画を修正することもできます SQL> DECLARE plans_loaded NUMBER; filter VARCHAR2(255); BEGIN filter := 'sql_id=''czzzubf8fjz96'' AND plan_hash_value=''1165613724'''; plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_SQLSET ( sqlset_name => 'UPG_STS_1', basic_filter => filter ); END; / SQL> DECLARE plans_loaded NUMBER; filter VARCHAR2(255); BEGIN filter := 'sql_id=''czzzubf8fjz96'' AND plan_hash_value=''1165613724''';

SQL計画管理 SQLプロファイル SQLパッチエディション EE (SE2のサブセット) EE +チューニングすべてメソッド
プラン使用の制限カーディナリティの見積りを改善ヒントを適用ストア計画全体統計 / ヒントヒントトランスポータブルはいはいはい計画保証はい - - メンテナンス自動進化手動検査手動検査異なる PLAN STABILITY オプション Copyright © 2021, Oracle and/or its affiliates 140

データベース・リプレイ | 概要 Copyright © 2021, Oracle and/or its affiliates
142 " Database 19cテスト・ガイド、第9章データベース・リプレイを使用すると、本番システムのワークロードを取得し、元のワークロードの正確なタイミング、同時実行性およびトランザクション特性でテスト・システムでリプレイできます。これにより、本番システムに影響を与えずにシステム変更の影響をテストできます。 Enterprise Edition + Real Application Testingが必要です

143 11.2.0.4 19c ファイルの取得ワークロード把握テストシステム前処理リプレイ・ファイルリプレイ分析

144 Ulrike Schwinn on blogs.oracle.com

145 Ulrike Schwinn on blogs.oracle.com

1. ワークロード取得の制限 2. 外部の依存関係に適合しません • データベース・リンク、外部表、UTL_HTTP 3. 取得前にデータベースを再起動することをお薦めします • 制限モードで起動し、取得では制限なしモードが自動的に設定されます
4. 専用SCNから最適な作業 • データ・ポンプFLASHBACK_SCNまたは特定のSCNにリストアデータベース・リプレイ | 考慮 Copyright © 2021, Oracle and/or its affiliates 147

DISABLED 150 Copyright © 2021, Oracle and/or its affiliates 驚き
| 自動SQL計画管理 Oracle 19.3.0でデフォルトで有効 Oracle 19.4.0以降、デフォルトでは無効になっていますが、Exadata以外のシステムでのみ有効です • AWRのスキャン • DBAの介入なしでSQL計画ベースラインを検証および有効化 BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', parameter => 'ALTERNATE_PLAN_BASELINE', value => 'EXISTING' ); END; / ENABLED BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', parameter => 'ALTERNATE_PLAN_BASELINE', value => 'AUTO' ); END; /

驚き | 自動SQLチューニング・セット Oracle 19.7.0では、自動SQLチューニング・セットが移入されます • SYSAUXで高成長と消費が報告されている顧客もあります • 19.8.0以降無効 •
ブログ投稿: Do you love unexpected surprises? • 手動で無効にします: exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'Auto STS Capture Task', operation=>NULL, window_name=>NULL); select to_char(max(last_schedule_time),'DD-MON-YY hh24:mi') LATEST, task_name, enabled from dba_autotask_schedule_control group by task_name, status, enabled LATEST TASK_NAME ENABLED ------------------ ------------------------------ ------- 15-JUL-20 09:56 Auto STS Capture Task TRUE 15-APR-20 00:16 Auto SPM Task FALSE Copyright © 2021, Oracle and/or its affiliates 152

153 Copyright © 2021, Oracle and/or its affiliates アンダースコア |
optimizer_adaptive_* パラメータ optimizer_adaptive_plans • デフォルト: TRUE • 解析後に実行時における結合メソッド、ビットマップ・プルーニングおよびパラレル配分メソッドの調整パラメータ optimizer_adaptive_statistics • デフォルト: FALSE • 動的統計、SQL計画ディレクティブの作成、自動再最適化の実行推奨 • デフォルトのままにします • Oracle 12.2.0.1以降

_sql_plan_directive_mgmt_control パラメータ _sql_plan_directive_mgmt_control • SQL計画ディレクティブはバックグラウンドで収集されます • optimizer_adaptive_statistics=falseの場合でも(デフォルト) • ただし、SPDは使用されません • MOSノート: 2209560.1 - SQL計画ディレクティブ(SPD)を無効にする方法推奨 • _sql_plan_directive_mgmt_control=0 をどこでも設定 • Oracle 12.2.0.1以降

_cursor_obsolete_threshold パラメータ _cursor_obsolete_threshold • 親カーソルが廃止されていません • したがって、親の下の子カーソルは1024を超える拡張されます(デフォルトでは12.1) • カーソルmutexによる大規模な同時実行性の問題 • MOSノート: 2431353.1 • SQLの高バージョン数(1024以上) 12.2以上のアップグレード後、パフォーマンスが低下推奨 • _cursor_obsolete_threshold=1024を常にどこでも • Oracle 12.2.0.1以降

optimizer_real_time_statistics パラメータ optimizer_real_time_statistics • DML操作のリアルタイムおよび高頻度の統計収集 • Exadataのみの機能 • デフォルトで19.9.0までON • 19.10.0以降、デフォルトでOFF • ドキュメント、オプティマイザ・ブログおよびアップグレード・ブログ推奨 • 19.9.0まで • _optimizer_gather_stats_on_conventional_dml=FALSE • _optimizer_use_stats_on_conventional_dml=FALSE • 19.10.0から: optimizer_real_time_statistics=FALSE

deferred_segment_creation パラメータ deferred_segment_creation • 表/索引が作成されましたが、行は挿入されませんでしたか。使用済領域がありません • ただし、最初の行が挿入されたときのパフォーマンスは低下 • オブジェクトを一括して作成および削除する環境でのみ有用 • いくつかの破損バグ、競合の問題など • 参照: MOSノート1216282.1 - パラメータ"DEFERRED_SEGMENT_CREATION" 推奨 • 本当にこの機能が必要でないかぎり、deferred_segment_creation=falseを設定 • Oracle 11.2以降

160 Copyright © 2021, Oracle and/or its affiliates SE2 |
様々なチューニング・ツールの選択 SE2を使用すると、多くの作業がうまく行えます • Björn Rostによる無料チューニング・ツールに関するプレゼンテーション(2016年) 非常に役立つ例を次に示します: • MOATS (最新バージョン: : 2020) • Snapper (2020) • SQLdb360 (2020) • TUNAs360 (2016) • Pathfinder (2016) • その他次のことを忘れないでください: • イベント10046 - MOSノート: 376442.1

161 Copyright © 2021, Oracle and/or its affiliates SE2 |
SQL計画管理 SQL計画管理の一部はDiag/Tuning Packなしで使用できます • Oracle 19c License Guide • SQL文ごとに1つのSQL計画ベースラインのみが許可され、SQL計画の展開は無効になります • Oracle 19c License Guideからの抜粋 - 詳細と現在の詳細を確認してください

レコード済Webセミナー https://mikedietrichde.com/videos/ YouTube Channel: https://www.youtube.com/c/Oracle DatabaseUpgradesandMigrations Copyright © 2021, Oracle
and/or its affiliates 164 UnsplashのJonathan Velasquezによるフォト

【DB19c Upgrade!】エピソード03 パフォーマンスの安定性

【DB19c Upgrade!】エピソード03 パフォーマンスの安定性

More Decks by oracle4engineer

Other Decks in Technology

Featured

Transcript