Oracle Database Technology Night #59 Autonomous Databaseのパフォーマンスのメリットと注意点

津島博士のパフォーマンス講座 Autonomous Databaseのパフォーマンスのメリットと注意点 Oracle Database Technology Night #59 津島浩樹
日本オラクル株式会社データベース・ソリューション部 2022年 9月29日

Copyright © 2022, Oracle and/or its affiliates 2 津島浩樹（津島博士）
マスタープリンシバルソリューションエンジニア日本オラクル株式会社長年に渡りデータベースの構築やパフォーマンスチューニングなどに従事し、最近では若手エンジニアの育成および大規模データベース案件などの支援に従事しております。今までの経験が少しでもお役に立てればと思い「津島博士のパフォーマンス講座」を2011年から連載しております。

Agenda 1. Autonomous Databaseとは 2. パフォーマンス・メリット 3. 手動チューニング 4. パフォーマンス・ツール
Copyright © 2022, Oracle and/or its affiliates 3

データセンターオぺレーションの自動化と機械学習インフラの完全な自動化データベースの完全な自動化 Oracle Cloud Oracle
Autonomous Database Autonomous Databaseとは機械学習で実現するフルマネージド・クラウド・データベース長年実績のあるインフラとデータベースを基盤とし、その管理やオペレーションを完全に自動化 RAC + EEオプション Copyright © 2022, Oracle and/or its affiliates 5

• Automatic Query Rewrite • Automatic Undo Management • Autonomous
Health Framework • Automatic Diagnostic Framework • Automatic Refresh of Clones • Automatic SQL Tuning • Automatic Workload Capture/Replay • Automatic SQL Plan Management • Automatic Capture of SQL Monitor • Automatic Data Optimization • Automatic In-Memory population • Automatic Columnar Flash Cache • Automatic Application Continuity 9i 10g 11g 12c 18c 19c • Automatic Indexes • SQL Quarantine • Real-Time Statistics • Automatic Memory Management • Automatic Segment Space Mgmt • Automatic Statistics Gathering • Automatic Storage Management • Automatic Workload Repository • Automatic Database Diagnostic Monitor 21c • Automatic Materialized Views • Automatic Zone Maps • Automatic SQL Tuning Set データベース技術の自動化データベースの革新とリーダーシップを推進 Copyright © 2022, Oracle and/or its affiliates 6

• Smart Scan • Infiniband Scale-Out • Database Aware Flash
Cache • Storage Indexes • Columnar Compression 2021 • IO Priorities • Data Mining Offload • Offload Decrypt on Scans • In-Memory Fault Tolerance • Direct-to-wire Protocol • JSON and XML offload • Instant failure detection • Network Resource Management • Multitenant Aware Resource Mgmt • Prioritized File Recovery • In-Memory Columnar in Flash • Smart Fusion Block Transfer • Exadata Cloud Service 2008 データベース・インフラの自動化と最適化 Exadataが提供する差別化されたプラットフォーム Copyright © 2022, Oracle and/or its affiliates 7

AUTONOMOUS DATABASE (ADB) Autonomous Data Warehouse (ADW) Autonomous Transaction Processing
(ATP) Shared Exadata Infrastructure Dedicated Exadata Infrastructure ワークロードデプロイメント ( /DWH) (OLTP/ ) ( Pluggable Database) ( OCI or C@C(*1)) Autonomous JSON Database (AJD) (*2) (JSON ) APEX Application Development (*2) (APEX ) *1: C@C: Oracle Cloud@Customer(お客様データセンターでのOCI提供サービス) *2: Dedicated では未提供ワークロードとデプロイメント方式共通のプラットフォームで特定のワークロードに最適化 Copyright © 2022, Oracle and/or its affiliates 8

リスクの低減 • サイバー攻撃の防止 • 常時稼働 (ダウンタイムなし) • 実績、移行の容易さ支出を減らす •
管理コストの削減 • ランタイム・コストの削減より革新的に • 人材の再配置 (管理者から) • 開発スピードの向上 ADBの主なメリット Copyright © 2022, Oracle and/or its affiliates 9

管理すべてのインフラとデータベースのメンテナンスを自動化全てのソフトウェアのオンラインパッチ適用、設定のチューニング、すべてのOSとSYSDBA操作の実行、エラーの診断安全外部・内部のあらゆる脅威からデータを守る
セキュリティのオンラインアップデート適用、DB Vaultで管理者の盗み見を防止、全データを暗号化プロビジョンミッションクリティカルなデータベースを迅速かつ容易に作成可能 Exadataクラウド・インフラ、 RACスケールアウト・データベース、 Active Data Guardスタンバイを構築 ADBの主なメリット何をどのように Copyright © 2022, Oracle and/or its affiliates 10

最適化人の指示なしにワークロードを最適に実行データフォーマット、インデックス、並列処理、プランをワークロードごとに自動で最適化スケールオンライン拡張で最高のパフォーマンスと低コストを実現コンピューティングとストレージ
の即時、自動、オンライン・スケーリングにより、真の従量課金が可能保護あらゆる障害からダウンタイムなしに回復するバックアップ、リストア、クラスタ内またはリモートスタンバイへのアプリケーション透過的フェイルオーバーの自動化 1 2 3 4 5 Copyright © 2022, Oracle and/or its affiliates 11 ADBの主なメリット何をどのように

Copyright © 2022, Oracle and/or its affiliates 12 Autonomous Databaseの
パフォーマンス・メリット

• Exadataによる性能向上 • ADWとATPに対して最適な事前設定 • 代表的なパフォーマンス問題の対応 • オプティマイア統計 • 正確にすると収集負荷が増える（正確に取集せずにヒントなどで調整している）
• リソース制御 • 同時実行での制御が難しい（シリアル実行している場合が多い） • SQLチューニング • SQLの書き換えや索引作成などによる最適な実行計画に簡単にできない（経験のある方でないと難しい） • SQLの数が多いと対応できない → 接続サービスごとに事前設定 → 自動的に収集されるように事前設定 → 自動チューニング ADBのパフォーマンス・メリット問題が発生しないように事前設定されているのが特徴 Copyright © 2022, Oracle and/or its affiliates 13

ADW • I/Oの高速化 • RoCE, ASM, Exadata Smart Flash Cache
• I/O&CPU使用率の削減 • Exadata Smart Scan (一部をストレージにオフロード) • Storage Index (クエリ対象外のブロックを読み飛ばし) • Hybrid Columnar Compression (高圧縮率を実現) • In-Memory Columnar in Flashにより更に向上 ATP • ランダムI/Oの高速化、データスキャン帯域幅の向上 • Persistent Memory Data Accelerator • Exadata Smart Flash Cache • Redoログ書込みの高速化 • Persistent Memory Commit Accelerator • Exadata Smart Flash Log RDMA(Remote Direct Memory Access) による通信オーバーヘッドの解消 • 100 Gb/sec の RoCE (RDMA over Converged Ethernet) Persistent MemoryによるI/Oレイテンシーの削減 • トランザクション処理のレイテンシーを10倍改善 • 2.5倍のトランザクション処理IOを実現 SQLをストレージにオフロードすることにより、ストレージのボトルネックを解消 • DBサーバーへの転送量を削減 Intel® Optane™ DC Persistent Memory Compute Server Storage Server Hot Warm RDMA Cold RoCE PMEM FLASH Exadataによる性能向上世界最速のデータベース・プラットフォーム Copyright © 2022, Oracle and/or its affiliates 14

処理内容 • 汎用検索1 • 7つの表を結合し1件の結果を戻す検索処理効果 • Exadataに移行するだけでI/Oが高速化し、1.41倍の性能向上 • RoCE,
ASM, Exadata Smart Flash CacheによるI/O処理の高速化 • 索引削除によりSmart Scanが有効化しI/Oが最適化され、12.8倍まで性能向上 • I/O量は4.44GBから928MBに減少 • HCC圧縮によるI/O量減少で、さらに32.6倍まで性能向上 Exadataによる性能向上参考）Exadataが実現する脅威的な性能改善効果（検証結果のご紹介） Copyright © 2022, Oracle and/or its affiliates 15 ADWならば、ここからスタート可能

行型フォーマット列型フォーマットインデックス作成データ集計を作成 I/O削減のためのデータキャッシュ（SGA）メモリでの結合、集計（PGA）オプティマイザ統計をリアルタイム/高頻度で更新し、実行計画の劣化を防止 Autonomous Transaction Processing
(ATP) Autonomous Data Warehouse (ADW) OLTPおよび混合ワークロードに最適化トランザクション、バッチ、レポーティング、 IoT、アプリケーション開発、機械学習すべてのデータ分析処理に最適化データウェアハウス、データマート、データレイク、機械学習ワークロードに応じた最適化 ADWとATPに対して最適な事前設定 Copyright © 2022, Oracle and/or its affiliates 16

ADW ATP メモリ設定集計・ソート処理を優先する設定（SGA < PGA） INMEMORY_SIZE=1G データのキャッシュを優先する設定（SGA > PGA）
INMEMORY_SIZE=0 圧縮分析処理に特化しているため有効（表領域レベルでHCC、表毎に無効化可能）無効（表毎に圧縮設定可能） Result Cache これも分析系向け機能のため全てのSQLに有効（NO_RESULT_CACHEヒントで無効化可能）デフォルトのまま（無効）ヒント無効（Smart Scanをより活かしやすいように） NO_RESULT_CACHE、APPEND ヒントを除くデフォルトのまま（有効）パラレル処理接続サービス毎に選択可能・High : 自動的にパラレル処理・Medium : 自動的にパラレル処理・Low : 無効接続サービス毎に選択可能・TPURGENT : 手動で設定・TP : シリアル処理・High : 自動的にパラレル処理・Medium : 自動的にパラレル処理・Low : 無効自動オプティマイザ統計収集無効高頻度自動オプティマイザ統計収集は有効有効 ADWとATPに対して最適な事前設定 ADWとATPの主な違い Copyright © 2022, Oracle and/or its affiliates 17

代表的なパフォーマンス問題の対応オンライン統計収集 • バルク・ロードのオンライン統計収集 (ダイレクト・ロード操作に対する統計情報の自動収集) • CREATE TABLE …
AS SELECT …; INSERT /*+ append */ INTO … SELECT …; • Data PumpのImportロード • DBMS_CLOUDのロード • 非ADBとは異なり、以下も動作する • 表が空でなくても可能 • ヒストグラムの収集リアルタイム統計 • 従来型DMLのオンライン統計収集 • オプティマイザ統計のサブセットを収集 • 行数、列の最大値、最小値など • 21c(RU19.10)からデフォルトFALSE • optimizer_real_time_statisticsが追加 • 自動索引機能など自動オプティマイザ統計収集 • ADWは無効 • DWHで従来型DMLはあまり使用しない • ETLなどで使用する場合は一部として収集を行う • 高頻度自動オプティマイザ統計収集 • 非ADBと異なりデフォルト有効 • 統計情報が古い場合、15分ごとに完全なオプティマイザ統計を収集するオプティマイザ統計（基本は手動取集は不要） Copyright © 2022, Oracle and/or its affiliates 18

代表的なパフォーマンス問題の対応リソース管理機能群 (インスタンス、プロセス、I/O) を利用しやすい形で提供 • ミッションクリティカルなシステムに求められる、同時実行数制御や、 CPU・ネットワーク・ストレージ帯域管理、レイヤー毎の自動キャッシュ • DWH系システムに求められる、SQL実行の自動パラレル処理、
自動キューイングなど • ユーザーは接続サービスを選択するだけ CPUのオンライン自動スケーリング煩雑な作業なしに、負荷状況に応じてCPUリソースを柔軟に利用可能 • CPUを動的に割り当てることで性能を担保 • アプリケーションの停止は不要 • 1 OCPU単位での柔軟なリソース配分により大幅なコストダウンが可能 • 負荷のピークに合わせたサイジングは不要 • ユーザーはOCPU数と自動スケーリングの利用有無を選択するだけリソース管理制御機能の一例リソース制御負荷状況を判断し、自動的にリソースの拡張・縮小を無停止で実施する 1 CPU単位で拡張・縮小を行い、秒単位で課金されまる 0 4 8 CPU 6時 12時 18時 24時翌6時日中時間帯のスパイクに対応夜間バッチの高負荷に対応 16 実際に利用しているリソース Autonomous Database ～～ 0 4 8 6時 12時 18時 24時翌6時 16 実際に利用しているリソース課金対象のリソース他社データベース・サービス柔軟性が欠如しているため、ピークに合わせたサイジングが必要～～シェイプ単位での拡張のみシステム再起動が必要 ※1 OCPU (Oracle Compute Unit) = 1物理コア = 2vCPU Copyright © 2022, Oracle and/or its affiliates 19

接続サービス platform 概要パラレル制御 (DOP) 同時実行セッション数リソース割り当て (SHARES*2) TPURGENT
ATP only 最も優先度の高い処理向け手動設定 300 x OCPUs 12 TP ATP only 汎用的な処理向けシリアル 300 x OCPUs 8 HIGH ADW/ATP 大量データを扱う処理向け自動(最大はOCPUs) 3 4 MEDIUM ADW/ATP 大量データを扱いつつも、同時実行数も多い処理向け自動(*3) 1.26 x OCPUs (*4) 2 LOW ADW/ATP 優先度が低い処理向けシリアル 300 x OCPUs 1 代表的なパフォーマンス問題の対応インスタンス作成時点で、接続サービスが定義されている • アプリケーションは、事前定義された5つのデータベースサービスのいずれかに接続 • リソース・マネージャのコンシューマ・グループにマッピングされ、優先度、同時実行性、並列処理を制御インスタンス接続時に、接続サービスを選択するだけでリソース制御が可能(*1) *1 : 5分以上アイドルのセッションはリソース不足時の削除対象になる *2 : サービス間でのCPU配分の相対的な優先度を示す。デフォルト値からの変更は可能 *3 : OCPU<4の場合はOCPU数、OCPU≧4の場合は4が最大 *4 : OCPU<4の場合は5、OCPU≧4の場合は1.26 x OCPU数 MEDIUMのみ同時実行セッション数の上限を変更可能（後述）リソース制御（接続サービスに対して事前設定） Copyright © 2022, Oracle and/or its affiliates 20

代表的なパフォーマンス問題の対応 OLTPの特徴 • 少量の行にアクセスし、大量のユーザを同時実行する • 一般的にはミリ秒レベル • スループット (TPS) を重視し、単体処理のリソース利用
の極小化を目指す • TPの利用を推奨 • 単一のCPUコアで処理させるため • 最優先したい特別な処理(もしくは手動でパラレル度を制御したい)の場合はTPURGENTを使用バッチ・DWHの特徴 • ユーザ数は少なく、大量の行にアクセスし一括処理する • 一般的には秒～分レベル • 単体SQLのレスポンスタイムを重視し、単体処理でCPU、 IOリソースを100%割り当てることを目指す • MEDIUMの利用を推奨 • 複数のCPUコアで処理させるため (動的なパラレル処理以外にキューイングも実装されていて効率よく処理可能) • 同時実行数が 3よりも少ない場合はHIGHを推奨リソース制御（接続サービスの選択） 100% CPU/IO CPU/IO リソースを100% 使い切ることで、全体の処理を早く終えるように！少ないリソースで処理することで、多くの処理をこなせるように！ CPU/IO CPU/IO Copyright © 2022, Oracle and/or its affiliates 21

------------------------------------------------------- | Id | Operation | Name | ------------------------------------------------------- |
0 | INSERT STATEMENT | | | 1 | LOAD AS SELECT | TAB1_2 | | 2 | PX COORDINATOR | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | | 4 | OPTIMIZER STATISTICS GATHERING | | | 5 | PX BLOCK ITERATOR | | | 6 | TABLE ACCESS STORAGE FULL | TAB1 | ------------------------------------------------------- - automatic DOP: Computed Degree of Parallelism is 8 --------------------------------------------- | Id | Operation | Nam | --------------------------------------------- | 0 | INSERT STATEMENT | | | 1 | LOAD TABLE CONVENTIONAL | TAB1_2 | | 2 | TABLE ACCESS STORAGE FULL| TAB1 | --------------------------------------------- - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation - PDML disabled because object is not decorated with parallel clause - Direct Load disabled because no append hint given and not executing in parallel LOW HIGH MEDIUM リソース制御（ダイレクト・パスとコンシューマグループ）代表的なパフォーマンス問題の対応ダイレクト・パスINSERTが使用される場合 • パラレルDMLのとき • ヒント/*+ APPEND */を付加したとき • ADWもAPPEND ヒントはデフォルトで有効 • シリアル(LOW)はヒントがないと従来型INSERT パラレルDML(PDML)はデフォルトで有効 • CPUコア数＞1、かつコンシューマ・グループがMEDIUM またはHIGHの場合のみ Copyright © 2022, Oracle and/or its affiliates 22

代表的なパフォーマンス問題の対応自動チューニング • SQL自動変換 • オプティマイザにより可能な限り自動的に最適なSQLに書き換えるQuery Transformation • 様々なチューニング項目（索引、パーティション、MVIEW）の自動化 •
OLTPの高速化のための自動索引 • 索引はADWでも使用できる • 分析系処理の高速化のための自動パーティション、自動MVIEW • 実行計画管理の自動化 • SQLの性能劣化を検出し修復する過程をすべて自動化 • 自動SQLチューニングセット、自動SPM(SQL計画管理)、自動/高頻度自動SPM展開アドバイザ Query Transformation、自動SQLチューニングセット、自動SPM展開アドバイザ以外はADBとExadataのみで使用可能 SQLチューニング Copyright © 2022, Oracle and/or its affiliates 23

代表的なパフォーマンス問題の対応最適なSQLにするのが難しい • 高度なスキルや経験・ノウハウが必要オプティマイザにより可能な限り自動的に書き換え、開発者はアプリケーションの実装に専念できるようにする • 効率の良いSQLへ自動で変換、より高速に処理 • ユーザーは特に意識する必要はない
• OR拡張 • ビュー・マージ • 述語のプッシュ • 副問合せのネスト解除 • MVIEWへのクエリー・リライト(第70回) • スター型変換(第54回) (索引がないと動作しない) • インメモリ集計(第54回) (VECTOR GROUP BY) • cursor-duration一時表(第60 回) • 表拡張 • 結合の因数分解(第34回) 等自動チューニング（SQL自動変換）無駄な処理の排除 • 結合（外部キー）、ORDER BY（インライン・ビュー内）、 DISTINCT（主キー）<=第34回 • GROUP BY（結合をしないインライン・ビュー内）<=12cからビュー（インライン・ビュー）の最適化（後で説明）副問合せのネスト解除（セミ結合、アンチ結合） • INの方が制約が多いのでEXISTSを使用する（第29回） • OR条件で変換されないときがある（第44回、第52回） OR拡張、表拡張（後で説明） • UNION ALLに変換 Copyright © 2022, Oracle and/or its affiliates 24

25 代表的なパフォーマンス問題の対応 OR拡張（第9回） • OR条件は索引を使用できない • 索引を使用したいときはUNION ALLに変換 • パラレル実行時のOR拡張は一部シリアル処理に
(第52回) SQL自動変換（OR拡張、表拡張） SELECT * FROM employees e, departments d WHERE (e.email='SSTILES' OR d.department_name='Treasury') AND e.department_id = d.department_id; SELECT * FROM employees e, departments d WHERE e.email = 'SSTILES'AND e.department_id = d.department_id UNION ALL SELECT * FROM employees e, departments d WHERE d.department_name = 'Treasury’ AND e.department_id = d.department_id; 表拡張（第34回） • 索引が使用可と使用不可のパーティションのときUNION ALL に変換（問合せで同一表に複数のアクセス方法ができない） SQL> ALTER INDEX tab1_ix1 MODIFY PARTITION p04 UNUSABLE; SQL> SELECT * FROM tab1 WHERE c1 > 200 AND c2 = 1; ------------------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | VIEW | VW_TE_2 | | | | 2 | UNION-ALL | | | | | 3 | PARTITION RANGE SINGLE | | 3 | 3 | |* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB1 | 3 | 3 | |* 5 | INDEX RANGE SCAN | TAB1_IX1 | 3 | 3 | | 6 | PARTITION RANGE SINGLE | | 4 | 4 | |* 7 | TABLE ACCESS FULL | TAB1 | 4 | 4 | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("TAB1"."C1">200) 5 - access("C2"=1) 7 - filter("C2"=1 AND "TAB1"."C1" IS NOT NULL) Copyright © 2022, Oracle and/or its affiliates

26 代表的なパフォーマンス問題の対応ビューとは • アクセス制御などで使用（条件は外で指定）特性（メリット／デメリット） • 結合順が調整できない <= •
例のtab1は最後の結合に • ビュー実行後は索引が使用できない <= • 例のB.c3<100の索引を使用しない • 結合前に行数を削減できる（DISTINCT、 Group Byなど） <= • 例のインライン・ビューにGroup byがなくても結果は同じ（主問合せで同じSUM集計を行っているから） SQL自動変換（ビュー、インライン・ビュー）ビューのQuery Transformation • ビューを使用しない • 結合順を変えたい、索引を使用したい • View Merging（ビュー・マージ）<=第29回 • ビューが効果的なとき（ビューを作成） • Group by Placement（Group byの配置）<=第42回 • ビューをマージできない／しない • Group by後にネステッド・ループ結合するなど • Predicate Pushing（述語のプッシュ）<=第29回 SQL> SELECT A.c2, SUM(b2), SUM(b3) FROM tab1 A , 2 (SELECT C.c1,C.c3,SUM(C.c2) b2,SUM(D.c2) b3 3 FROM tab2 C,tab3 D 4 WHERE C.c1 = D.c1 GROUP BY C.c1,C.c3) B 5 WHERE A.c1 = B.c1 AND B.c3 < 100 6 GROUP BY A.c2 ; Copyright © 2022, Oracle and/or its affiliates

代表的なパフォーマンス問題の対応高スキルなエンジニアによる探索的なチューニング • エンジニアのノウハウに依存し、コストをかけて実施熟練のエンジニアによる一連のプロセスと同等のことを機械学習を活用し自動化 • 様々なチューニング項目を順次自動化 • OLTPの高速化のための自動索引
(デフォルト無効) • 分析系処理の高速化のための自動パーティション (デフォルト無効)、自動MVIEW (21cから) • 最新の統計情報に基づき、最適な設定を断続的に探索、自動選択することで性能改善を図る • ユーザーは利用するか否かを選択するだけ自動パーティション • 熟練したパフォーマンス・エンジニアが行うようなパーティショニング方法を実装する • パーティショニング手法の候補を特定し、検証を行う • プロセス全体を完全に自動化することが可能 • 透明性は高度な自動化と同様に重要 • すべての推奨事項はレポートにより監査可能自動チューニング（自動索引、自動パーティション、自動MView） Capture Identify Verify Decide Monitor Copyright © 2022, Oracle and/or its affiliates 27

さらに、①は以下にも活用 • 自動索引 [19c] • 自動パーティション[19c] • 自動ゾーンマップ [21c] •
自動Materialized Views [21c] 実行計画管理の全てのステップを自動化（デフォルト有効） • 最新の統計情報、データ構成を機械的に都度収集し、断続的に適用テストを行い実行計画の安定化を実現 • データ量の変動やパッチ適用、バージョンアップに伴うアプリケーションの性能劣化を抑制 ⑤ベースラインに存在する実行計画を優先利用 (オプティマイザ) PLAN （採用） ①SQLの実行情報を高頻度で自動収集 (自動SQLチューニングセット) ④現行のベースラインと代替プランを比較し、良い実行計画であればベースラインに自動登録 (自動SPM展開アドバイザ/高頻度自動SPM展開アドバイザ) ②リソースを大量消費する上位SQLを自動検出 (自動SPM) ③代替プランを自動検索 (自動SPM) 実行計画 Baseline PLAN （承認済） PLAN （承認済）実行計画履歴 PLAN （未承認） PLAN （未承認） PLAN （未承認） Cursor Cache AWR SQL Tuning Set ②〜④は SPM展開アドバイザ・タスクで行われる代表的なパフォーマンス問題の対応自動チューニング（実行計画管理の自動化） Copyright © 2022, Oracle and/or its affiliates 28

SQLの実行情報の格納先 • AWR • リソースの消費が多いSQL • カーソル・キャッシュ • 全てのSQLが対象であるがメモリ上のみ •
自動SQLチューニング・セット (SYS_AUTO _STS) • 実行されたほぼ全てのSQL (過去の最適な実行計画) SQL展開アドバイザ・タスク • リソースを大量消費する上位SQLを自動検出 (AWR, SYS_AUTO_STS) • 代替プランを自動検索 (SYS_AUTO_STS) • 現行のベースライン (現行プラン) と代替プランを比較 • 「CPU time」と「buffer gets」などを検証する • デフォルトは1.5倍の性能向上自動SPMがOFFの場合 • 実行計画の取得 • 自動取得 (デフォルトOFF)<-プロアクティブ • 繰り返し実行されたSQLの最初の実行計画がベースラインとして自動取得する（パース時） • 12.2からSQLのフィルターも可能に • 手動取得<-リアクティブ • DBMS_SPMパケージを使用してベースラインにロードする • ベースラインが設定されたSQL • 異なる実行計画をすべて計画履歴 (代替プラン) に登録（ハードパース時） • SQL展開アドバイザで比較する • 現行のベースラインと計画履歴を比較代表的なパフォーマンス問題の対応自動チューニング（実行計画管理の自動化） Copyright © 2022, Oracle and/or its affiliates 29

手動チューニング

ADBの手動チューニング • できること/できないこと • よくあるパフォーマンス・トラブル Copyright © 2022, Oracle and/or
its affiliates 31

ADBの手動チューニングできないこと • インメモリ列ストアの使用 • ADWはFlash上で使用 • DBブロック・サイズ (8K) の変更
• メモリ・チューニングできること • CPUコア数のスケールアップ • 別のサービスを利用する • 表の圧縮/非圧縮の変更 • 制約の設定/変更 • 例: 外部キー制約を使用して結合の排除 (クエリ変換) • オプティマイザ統計の再収集 • ヒントの追加 • ADWはデフォルト無視 • インデックスの作成 • パーティション表の作成 • マテリアライズド・ビューの作成できること/できないこと • MEDIUMの同時実行数も変更可 • 自動チューニングの限界 Copyright © 2022, Oracle and/or its affiliates 32 1SESSIONレベルのみ 2SYSTEMレベルのみ変更可能な初期化パラメータ • APPROX_FOR_AGGREGATION • APPROX_FOR_COUNT_DISTINCT • APPROX_FOR_PERCENTILE • AWR_PDB_AUTOFLUSH_ENABLED2 • CONTAINER_DATA • CURRENT_SCHEMA1 • CURSOR_SHARING • DDL_LOCK_TIMEOUT • FIXED_DATE • IGNORE_SESSION_SET_PARAM_ERRORS • LDAP_DIRECTORY_ACCESS • MAX_IDLE_TIME • MAX_STRING_SIZE • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES1 • OPTIMIZER_IGNORE_HINTS • OPTIMIZER_IGNORE_PARALLEL_HINTS • SESSION_EXIT_ON_PACKAGE_STATE_ERROR • OPTIMIZER _MODE • PLSCOPE_SETTINGS • PLSQL_CCFLAGS • PLSQL_DEBUG • PLSQL_OPTIMIZE_LEVEL • PLSQL_WARNINGS • QUERY_REWRITE_INTEGRITY • RESULT_CACHE_MODE • ROUTE_OUTBOUND_CONNECTIONS • SQL_TRACE1 • STATISTICS_LEVEL1 • SYSDATE_AT_DBTIMEZONE1 • NLS_*

ADBの手動チューニング MEDIUMサービスの同時実行セッション数の上限 (CONCURRENCY_LIMIT) • アプリケーションの特性に合わせた調整が可能 • パラレル処理しながら、より多くの処理を実行したい (スループット優先) • 同時実行数は最小に、HIGHよりも多いDOPにしたい
(レスポンス優先) • OCPU数よりDOPを増やしたいなど • 特記事項 • MEDIUMサービスでのみ変更可能 • OCPUが2以上の場合に変更可能 • 同時実行制限の変更に伴い、DOPも変更されるデフォルト値変更範囲同時実行数上限ベースOCPUに紐づく • OCPUが4未満の場合：5 • OCPUが4以上の場合：1.26 x OCPU数 1 から 3 x OCPU数の範囲で指定可能 DOP (パラレル度) ベースOCPUに紐づく • OCPUが4未満の場合：OCPU数 • OCPUが4以上の場合：4 同時実行数上限をデフォルトから変更した場合、 DOPは同時実行数上限値に反比例する形で変動する。またこの場合、AutoScalingの有無によってDOPの上限が変わる • Auto Scaling 無効時は、2から2xOCPU数の間でDOPが変動 • Auto Scaling 有効時は、2から6xOCPU数の間でDOPが変動 MEDIUMサービスの同時実行セッション数の設定 Copyright © 2022, Oracle and/or its affiliates 33

ADBの手動チューニングオプティマイザ統計をすべて最適に収集するわけではない • 拡張統計（列グループ、式）は自動作成されない • ヒストグラムのバケット数は最大254 • 適応統計は動作しない • 単一表カーディナリティだけ
• パラレル実行時にダイナミック統計は動作する自動SPMは過去に存在した実行計画に改善するだけ • オプティマイザ統計が正確でないと最適な実行計画が生成できない場合も • バージョンアップなどによる性能劣化の場合に使用するもの対応策として以下が必要 • ヒントで最適な実行計画を生成させる • 拡張統計の作成やヒストグラムの最大値を変更する • 最適なパフォーマンスではないが遅くなければ問題ないという考え方もあり • どうしても遅いSQLにはCPUのスケールアップで対応する自動チューニングの限界（オプティマイザ統計） Copyright © 2022, Oracle and/or its affiliates 34

ADBの手動チューニング • Smart Scanにならない • TRUNCATE/DROP TABLEが遅い • INSERT文が遅い •
パラレル実行されない • Temp領域の使用 • DELETE/UPDATE/MERGE文が遅い • 実行計画が変化して遅くなった SQLの実行の仕方によっては効果的に動作しない場合もよくあるパフォーマンス・トラブル Copyright © 2022, Oracle and/or its affiliates 35

よくあるパフォーマンス・トラブル Smart Scanの待機イベン • AWRやSQLモニターで確認 • cell smart table scan、cell
smart index scan direct path read待機イベントになっている • Smart Scanの条件を確認する（第69回） • ヒープ表 (通常の表) でない（クラスタ表／索引構成表である） • HCC以外で255 を超える列を参照 • 非インラインLOB (4000バイト超過) が参照されている • 行連鎖しているなど Cell single block physical read待機イベントになっている • 索引スキャンのためオプティマイザ統計を確認する • パラレル実行してみる • 索引スキャンがなくなる場合がある • 索引をINVISIBLEにしてみる • アクセス件数が多い場合は効果がある場合も • ヒントを無効にしてみる（ADWのデフォルトは無視） • INDEXヒントで強制的に索引スキャンにしている場合も Smart Scanにならない Copyright © 2022, Oracle and/or its affiliates 36

cell multiblock physical read待機イベントになっている • 表サイズ (アクセス・パーティション合計サイズ) の確認 • アクセス・サイズがバッファ・キャッシュの2%以下だと非ダイレクト
• オプティマイザ統計の確認も (11.2からセグメント・サイズはオプティマイザ統計を使用する) • In-Memory Parallel Execution (バッファ・キャッシュ上のパラレル実行) が動作している • バッファ・キャッシュの80%以下のサイズのオブジェクトが動作する • パラレル実行（MEDIUMまたはHIGH）にしてみる • パラレル実行は Direct Path Read (Smart Scan) になりやすい • シリアル実行はバッファ・キャッシュの状態による（大量更新のバッチ処理後には注意） • 同時実行している場合が多く、ミニ・チェックポイントが発生するため • ALTER SYSTEM FLUSH BUFFER_CACHE を実行してみる（ADBでは実行できない） • 一度パラレル実行を行う Smart Scanにならないよくあるパフォーマンス・トラブル Copyright © 2022, Oracle and/or its affiliates 37 表サイズパラレル実行パラレル実行 (自動DOP) シリアル実行 ≦2%(Small) 非ダイレクト非ダイレクト非ダイレクト ≦80% ダイレクト非ダイレクト (IM PX) バッファ・キャッシュの状態による < 500% ダイレクトダイレクト ≧500%(Very Large) ダイレクトダイレクトダイレクト

TRUNCATE/DROP TABLE(PARTITION)が遅いよくあるパフォーマンス・トラブル大量ブロックのミニ・チェックポイントが動作した • enq: RO - fast object
reuse 待機イベント • enq: KO - fast object checkpoint はダイレクト・パス・リードのとき • ADB (Exadata HC Storage) はディスクに強制書込み • 大量更新後の実行に注意 • 通常の運用ではあまり行わないと思う（テストなどではよく発生する） Copyright © 2022, Oracle and/or its affiliates 38

シリアル (LOW) だとAPPENDヒントが必要 (パラレルは必要ない) 制約を考慮する必要がある • 外部キー制約が定義されている場合、 PDML/ダイレクト・パスは無効となる • 従来型ロードが使用される
• 推奨 • 信頼性の高い制約 (RELY制約) の定義 • 制約をTRUE状態とする ALTER TABLE tab1_2 ADD FOREIGN KEY (country_id) REFERENCES countries RELY DISABLE NOVALIDATE INSERT /*+ APPEND */ * INTO tab1_2 SELECT … ; ------------------------------------------------- | Id | Operation | Name | ------------------------------------------------- | 0 | INSERT STATEMENT | | | 1 | LOAD TABLE CONVENTIONAL | TAB1_2 | | 2 | PX COORDINATOR | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | | 4 | PX BLOCK ITERATOR | | | 5 | TABLE ACCESS STORAGE FULL| TAB1 | ------------------------------------------------- Note ----- - automatic DOP: Computed Degree of Parallelism is 8 - PDML disabled because parent referential constraints are present INSERT文が遅い（Direct Path Insertにならない）よくあるパフォーマンス・トラブル Copyright © 2022, Oracle and/or its affiliates 39

よくあるパフォーマンス・トラブルパーティション表のパラレル・インサート • ExadataのHCCはHigh Water Mark(HWM) Loadingになる (第50回) • セグメントに対して単一PXプロセスで処理する
(一つだけのパーティション・ロードはシリアル処理になる) • 改善するにはPQ_DISTRIBUTEヒントか Exchange Partitionを使用する • HCC以外はHigh Water Mark Brokering(HWMB) Loadingになる • パーティションごとにHVエンキュー (HWMの更新のため) を使用してパラレル・ロードを行う表がNologgingモードでない • Redoログにブロック・イメージが出力される • 表圧縮を検討する（Redoログも削減される）パーティション1 パーティション2 PXプロセス PXプロセス INSERT文が遅い（Direct Path Insertが動作） ※Exchange Partitionを使用したロードダミー表にロードしてからパーティションと交換する (データの移動はない) Copyright © 2022, Oracle and/or its affiliates 40

ダイレクト・パス・ロード後のインデックス・メンテナンス • 非常に時間がかかる、パラレル化できない • 回避策：RELY DISABLE NOVALIDATEで主キーを作成する • 終了後にパラレルで作成する INSERT文が遅い（Direct
Path Insertが動作）よくあるパフォーマンス・トラブル Copyright © 2022, Oracle and/or its affiliates 41

MEDIUMまたはHIGHでもパラレル・クエリが動作しない • 実行計画を確認 (PX xxxxx操作が存在しない) • 索引スキャンを行っている • 索引をパーティション化する •
ソート処理 (Order By) を行っている • レンジ分割のためデータがあまり分散しない (できれば止める) パラレル実行にならないよくあるパフォーマンス・トラブル Copyright © 2022, Oracle and/or its affiliates 42

Temp I/Oの待機イベントが多い (第68回) • direct path read temp、direct path write
temp • Exadata 12.2からFlash Cacheを使用する使用するPGAの増加 • パラレル度を増やす/スケールアップする（同時実行のために単一プロセスの最大サイズがある） • 同時実行が多いとPGA_AGGREGATE_TARGETを超える (そのためPGA_AGGREGATE_LIMITがある) 使用するPGAの削減 • SQLから必要ない列を削除する • パーティション・ワイズ処理を検討 (パーティション単位に処理が可能) <=第20回, 第45回, 第60回 • ハッシュ結合, Group By, DISTINCT, 分析ファンクション(18cから), Order By(レンジでシリアル処理だけ) • SQL文を変更する Temp領域を使用している場合 PGA_AGGREGATE_TARGET < 1Gバイト MIN (PGA_AGGREGATE_TARGETの20%, 100Mバイト) PGA_AGGREGATE_TARGET ≧ 1Gバイト MIN (PGA_AGGREGATE_TARGETの10%, 1Gバイト) パラレル実行 MIN (PGA_AGGREGATE_TARGETの50%÷パラレル度, 単一プロセスの最大サイズ) よくあるパフォーマンストラブル

HCC表に対して行なっている • 大量の更新は避ける • 対象データはOLTP圧縮に変換される • 12cから行ロックをサポートパラレル実行がスケールしない (Redoログ・ネックなど) •
INSERT文に書き換える SQL> DELETE FROM tab000 2 WHERE 日付 < TO_DATE(‘20101001’,’YYYYMMDD’) ; SQL> CREATE TABLE tab001 NOLOGGING PARALLEL AS 2 SELECT * FROM tab000 3 WHERE 日付 >= TO_DATE(‘20101001’,’YYYYMMDD’) ; SQL> DROP TABLE tab000 ; SQL> RENAME tab001 TO tab000 ; DELETE/UPDATE/MERGE文が遅いよくあるパフォーマンス・トラブル

よくあるパフォーマンス・トラブルコストベースのクエリ変換が動作／単純に最適でない実行計画になった • オプティマイザ統計が正確でないため誤動作 • 高頻度自動オプティマイザ統計収集の動作により統計が古いということはない • オプティマイザ統計を正確にする • 拡張統計などを作成する
• SQLの書き換えやヒントを追加する • 何かが変化したタイミングで発生するので、基本は自動SPMが動作するはず • 次のようなとき正しくない見積り行数により誤動作する • データの増減（最適でない実行計画になった、これまで動作していなかったクエリ変換が誤動作する場合も） • バージョンアップ（新しく追加されたクエリ変換が誤動作する）実行計画が変化して遅くなった

パフォーマンス・ツール

パフォーマンス・ツール AWR（Automatic Workload Repository） • 出力されるのはPDBレベル（AWR_PDB_AUTOFLUSH_ENABLEDのデフォルトTRUE） • デフォルトの自動取得設定は間隔が60分、保持期間は30日（非ADBは8日間） • パフォーマンス・ハブ
• DBMS_WORKLOAD_REPOSITORYパッケージ • ADBはawrrpt.sqlスクリプトにアクセスできない • SQL Developer SQLモニター • サービス・コンソール • パフォーマンス・ハブ • SQL Developer Copyright © 2022, Oracle and/or its affiliates 47

パフォーマンス・ツール AWR（Automatic Workload Repository） • 出力されるのはPDBレベル（AWR_PDB_AUTOFLUSH_ENABLEDのデフォルトTRUE） • デフォルトの自動取得設定は間隔が60分、保持期間は30日（非ADBは8日間） • パフォーマンス・ハブ
• DBMS_WORKLOAD_REPOSITORYパッケージ • ADBはawrrpt.sqlスクリプトにアクセスできない • SQL Developer SQLモニター • サービス・コンソール • パフォーマンス・ハブ • SQL Developer Copyright © 2022, Oracle and/or its affiliates 48

パフォーマンス・ツール Overview • ストレージ使用状況 • CPU使用率 • 割り当てOCPU数 • SQL実行数
• SQLの平均レスポンスタイム Activity（モニター） • 接続サービス毎の負荷状況やキューイング状況 • DBの待機イベント Activity（モニターSQL対象） • Show details • リアルタイムSQLモニターレポートの表示 • Download report • リアルタイムSQLモニターレポートのダウンロードデータベース稼働状態（サービス・コンソール） Copyright © 2022, Oracle and/or its affiliates 49

最後に • ADBは、Exadataベースで最適に事前設定されているが、通常のOracle Databaseと同様のパフォーマンス問題は発生するので、どんなことが発生するかは知っておいてください • 過度のチューニングはADBの良さがなくなるので最低限のチューニングにとどめる。そうでないと使いづらいOracle Databaseとなってしまう
• それ以外のパフォーマンス向上はスケールアップで対応する Copyright © 2022, Oracle and/or its affiliates 51

Copyright © 2020, Oracle and/or its affiliates. 53 津島博士のQ&Aコーナー（ask 津島博士）
サンプル質問 1. ADWとATPのワークロードがありますが、どのようにして使い分けるのが良いのでしょうか。単純にトランザクション系とDWH系で使い分けるのでしょうか。 ✓ Autonomous Databaseはインスタンス・チューニングできないので、使い方に合ったワークロードを選ぶことになります。 ATPはSGA が大きくADWはPGAが大きいので、ATPは索引を主に利用する場合、ADWは索引を主に利用しない（HCCとSmart Scanを主に利用する）場合という考え方で良いかと思います。

サンプル質問 2. 接続サービスの使い分けはどのよう考えれば良いのでしょうか（LOWはどのような場合に使用するのでしょうか）。 ✓ HIGH、MEDIUMはパラレル実行する時に使用しますが、LOWは同時実行数が多いのでパラレル実行せずに、フルスキャンたまに索引スキャンを行うような場合に使用します。常に索引スキャンや更新を頻繁に行う場合には、ATPのTPを使用してください。

サンプル質問 3. 接続サービスMEDIUMで同時実行数や最大パラレル度を増やすのはどのような場合でしょうか。 ✓ TEMP I/Oを回避したい時にパラレル度を増やすことで回避できる可能性があります。これはPGAに単一プロセスの最大サイズがあるためです（43ページ参照）。同時実行数は、CPUに余裕があれば行うのも良いかと思いますが、できればOCPUを増やすのが良いかと思います。

サンプル質問 4. ADWでの索引アクセスは効果的なのでしょうか。索引アクセスさせない方が良いのでしょうか。 ✓ ADWのデフォルトはHCCなので、HCCでは索引アクセスの効率が悪かったのですが、以下がサポートされたので、更新と索引アクセスが少なければHCCでも問題ないかと思います（第69回）。多い場合にはHCC以外またはATPにしてください。・12cからCUレベルから行レベル・ロック・12cから索引範囲スキャンがSORT CLUSTER BY ROWD ・12.2から配列挿入 (NOAPPENDヒント) も圧縮可能に

サンプル質問 5. Autonomous Databaseは、オプティマイザ統計収集は何も考えなくても良いのでしょうか。 ✓ 拡張統計は自動作成されないので、SQLによっては最適な実行計画にならない場合があります。そのため、式や列グループを使用するクエリで性能的に問題がある場合は拡張統計の作成（またはヒント）を検討する必要があります（運用していて性能劣化する場合は自動SPM で対処されると思います）。パーティション表を使用する場合は増分統計の使用を検討する必要があります。

サンプル質問 6. ADWはResult Cacheが有効になっていますが、これはどこまで効果があるのでしょうか。 ✓ ADWのRESULT_CACHE_MAX_RESULT（各結果で使用できる割合）のデフォルトは1（RESULT_CACHE_MAX_SIZEの1%）になっているので、あまり大きな結果では利用できませんが、同じSQLを頻繁に実行する場合には有効かと思います。RESULT_CACHE_MAX_SIZEは、 1OCPUあたり10Mバイトになっています。これを含めてCPU数を検討すると良いかと思います。

サンプル質問 7. Autonomous DatabaseのAWRはPDBレベルのみとあるのですが、これでどこまでチューニングできるのでしょうか。 ✓ できるのはSQLチューニング・レベルだけになるかと思います。インスタンス・チューニング・レベルの初期化パラメータは変更できないので、そのような場合にはCPU数を増やすことになります。

サンプル質問 8. Autonomous Databaseの限界（このような使い方には向かない）とかはあるのでしょうか。 ✓ 特にないと思いますが、強いて言えば、できるだけ最速に（バリバリにチューニング）したいという方は避けた方が良いかと思います。今回のセミナーで紹介したレベルのチューニングにとどめてください。

当日の質問 1. 発表資料は後でダウンロード可能でしょうか？ ✓ 本日のセッション資料は公開次第、下記URL（connpass イベントページ）にも掲載させていただきます。公開までお待ちください。 https://oracle-code-tokyo-dev.connpass.com/event/257960/

当日の質問 2. ADWではヒント句が無効とのことですが、ダイレクトロードインサート（APPEND）が使えていたように思ったのですが勘違いでしょうか… ✓ ADWはヒントを無視する (optimizer_ignore_hints=TRUE) になっていますが、APPENDヒントは無視されません（スライドも修正しました）。 optimizer_ignore_hints=TRUEで、すべてのヒントが無視されるわけではないようです（以下のヒントは無視されないようです）。・APPEND/NOAPPEND ・PARALLEL/NO_PARALLEL(optimizer_ignore_parallel_hintsで無視) ・RESULT_CACHE/NO_RESULT_CACHE

当日の質問 3. statspackはADBでは使用不可であっていますか？ ✓ ADBはスクリプトにアクセスできませんので、statspackは使用できません。AWRを使用してください。

当日の質問 4. 最後当たりのOverviewなどの見える画面はどこからアクセスできるのでしょうか？ ✓ サービスコンソールは、「データベース・アクション」に統合されましたので、「データベース・アクション」の「DATABASE DASHBOARD」からアクセスできます。

Oracle Database Technology Night #59 Autonomous...

Oracle Database Technology Night #59 Autonomous Databaseのパフォーマンスのメリットと注意点

More Decks by oracle4engineer

Other Decks in Technology

Featured

Transcript