Oracle Database 性能分析入門

Oracle Database 性能分析⼊⾨⽇本オラクル株式会社 2024年4⽉

1. Oracle Databaseにおける性能分析技術の進展 2. Oracle Databaseの性能分析ツール 3. 性能分析ツールの実践的活⽤ 4. 技術の進展による性能分析の運⽤の変化
5. まとめ Agenda Copyright © 2024, Oracle and/or its affiliates 2

20年以上にわたるOracle Databaseの進化 Oracle Databaseにおける性能分析技術の進展 ʻ92 ʻ97 ʻ99 ʻ01 ʻ03 ʻ07
ʻ09 ʻ13 Oracle7 パラレルクエリ分散DB 可⽤性と拡張性を両⽴するクラスタ技術と災害対策機能の実装システム統合を実現するグリッド技術によるオープン化促進⼤規模データ管理と標準技術の採⽤セキュリティ強化と運⽤最適・効率化によるデータ管理コストの最適化を実現様々なデータ活⽤ニーズに⾰新的な基盤ソリューションを提供マルチテナント・アーキテクチャを始めとする、データベース・クラウド実現に必要な機能強化エンタープライズシステム Oracle8 パーティション表 LOB型パラレルDML RMAN Oracle8i コンポジットパーティション FGA(セキュリティ) Java/XML対応 Oracle9i Database RAC Data Guard Oracle Database 10g EM Grid Control Clusterware ASM Oracle Database 11g DB Vault Audit Vault ⾃動チューニングテスト⽀援データ圧縮 Oracle Exadata Smart Scan コモディティ機器 InfiniBand 超並列データ処理列圧縮 HW暗号 Oracle Database 12c マルチテナント・データベース In-Memory Database Consolidation Database Replay Heat Map Automatic Data Optimization Data Redaction Oracle Database 19c 透過的 Application Continuity(18c) Partitioned Hybrid Tables リアルタイム統計 Automatic Indexing SQL JSON Enhancement Oracle Database 23c JSON リレーショナル・デュアリティ SQLドメインオペレーショナル・プロパティ・グラフマイクロサービス・サポートロックフリー列値予約⼤規模DB 対応インターネット対応ミッションクリティカル対応エンタープライズグリッド利便性・安全性強化⼤規模データ超⾼速処理統合基盤データベース・クラウド基盤安定性 ʻ19 ʻ23 12.2製品ファミリーのターミナル・リリース安定性 (18c より年次リリースモデル) あらゆる⽤途や規模のモダンアプリケーションおよびアナリティクスの開発、実⾏を容易に開発をシンプルに⽣産性の向上 Copyright © 2024, Oracle and/or its affiliates 4 Statspack 登場 AWR 登場リアルタイムSQL監視登場

Oracle Databaseの性能分析における変遷 Statspack導⼊前の運⽤ • OSツールを⽤いた情報の収集 OSレベルの分析もtopコマンドやvmstatコマンドを使ってリソース使⽤状況を把握していた。 • ⼿動で⾏われるDBパフォーマンス分析 bstat/estatを使⽤した統計情報の⽐較、性能に問
題があると思われるSQLを選んで、SQLトレースや TKPROFを使⽤し、パフォーマンスを分析していた。 • レポートの⼿動作成パフォーマンスの報告と分析は主に⼿作業で⾏われ、定期的なモニタリングやトレンド分析は困難だった。 Statspack導⼊後の運⽤ • ⼀部のOS情報の表⽰ OS統計も⼀部レポートに表⽰され、OSにおける情報収集への洞察を得られるようになった。 • パフォーマンスデータの収集機能取得したスナップショットを通じたデータベースパフォーマンスデータの収集が可能となり、分析が容易になった。 • レポート出⼒機能取得したスナップショットよりレポートの出⼒が可能となり、パフォーマンスの傾向分析、容量計画が容易になった。 AWR導⼊後の運⽤ • ⾃動化されたワークロードリポジトリレポートにOS統計セクションが含まれ、OSレベルのリソース状況の把握がより簡単になった。 • さらに⾼度なパフォーマンスデータ収集の⾃動化スナップショットは⾃動で取得され、Statspackよりも詳細なパフォーマンスデータを収集可能となったことから、データベース活動のより広範な視野を提供可能に。 • より詳細なレポート出⼒パフォーマンス分析とトラブルシューティングに必要な情報をより詳細に提供可能となり、ADDMによる診断及びアドバイスも参照可能となった。 Oracle Databaseにおける性能分析技術の進展 Copyright © 2024, Oracle and/or its affiliates 5 情報収集はすべて⼿動で、洞察には経験が必要。当時のDBAはかなり⼤変、、、データ収集、レポート出⼒機能により性能分析が効率的に︕ データ収集、レポート出⼒が⾃動化され、かつ情報量が増えた。より効率的な性能分析が可能に︕ 〜 Oracle8 Oracle8i 〜 Oracle Database 10g 〜

Copyright © 2024, Oracle and/or its affiliates 6 Oracle Databaseの性能分析ツール
Statspackの紹介

Statspackについて Oracle Databaseの性能分析ツール Copyright © 2024, Oracle and/or its affiliates
7 Statspack: Statistics packageが由来 • パフォーマンス・チューニングに役⽴つ情報をレポートという形で出⼒してくれるツール ‒ 別途インストールが必要 • Oracle8i R8.1.6から利⽤可能 • 無償で利⽤が可能であり、Oracle Databaseのエディション関係なくご利⽤可能 • 取得したスナップショットを基に結果レポートを出⼒ ‒ 結果レポートはテキスト形式のみ ‒ スナップショット間隔の設定及び取得はユーザ側で⼿動にて実施 ‒ 定期的なスナップショットを取得するにはjob、cron などのスケジュール機能を⽤いる必要がある • 出⼒されたレポートを参照し、ユーザはパフォーマンス・チューニングを⾏う SGA 統計情報負荷の⾼いSQL … PERFSTATスキーマ (Statspackのインストール先) スナップショットスナップショットスナップショットスナップショットユーザ、job、cron など Statspackレポート (テキスト形式) Database 管理者

Statspack取得の流れ Oracle Databaseの性能分析ツール Copyright © 2024, Oracle and/or its affiliates
8 (アプリケーションの実⾏) Ⓐ Ⓑ Ⓐ〜Ⓑ間の値を基に、DB内部の挙動を把握 Ⓐ時点の DB内部統計データ取得 Ⓑ時点の DB内部統計データ取得スナップショットの取得 ü ある2時点間で取得した内部統計データの差分を基に、その間のパフォーマンス統計データを結果レポートに出⼒ ‒ 2時点間のスナップショットはユーザ側で⼿動でそれぞれ取得 ‒ 定期的なスナップショットの取得を⾏う場合は、スケジューラ機能(job、cronなど)を⽤いる必要がある結果レポートの出⼒ ü 結果レポートはテキスト形式にて出⼒ ü 結果レポートに出⼒されたパフォーマンス統計データからボトルネックの特定、パフォーマンス・チューニングを実施出⼒レポート(⼀部抜粋)

Statspackで取得される情報 Oracle Databaseの性能分析ツール Copyright © 2024, Oracle and/or its affiliates
9 スナップショットレベル収集データ基本統計情報アドバイス情報 SQL統計情報 SQL詳細情報セグメント情報ラッチ詳細情報 Level 0 〇〇 Level 5 〇〇〇 Level 6 〇〇〇〇 Level 7 〇〇〇〇〇 Level 10 〇〇〇〇〇〇 Statspackで収集できる情報は以下の表のように、Level によって異なる。デフォルトではLevel 5だが、 statspack.snapプロシージャの i_snap_level パラメータを変更することで必要に応じたLevelの変更が可能。 i_snap_levelの違いデフォルト必要に応じて変更可能

Copyright © 2024, Oracle and/or its affiliates 10 Oracle Databaseの性能分析ツール
AWRの紹介

AWRの登場 Oracle Databaseの性能分析ツール Copyright © 2024, Oracle and/or its affiliates
11 ü スナップショットの取得は⼿動で⾏う必要がある ü データはスナップショット間隔の平均値しか取れない ü 出⼒されるレポートはテキスト形式のみ ü 問題の⾃動診断機能はなく、出⼒されたデータからボトルネックとなる問題点を⾃⼒で探す必要がある ü 出⼒されるデータは過去のスナップショットベースのため、リアルタイムでの監視ができるわけではないスナップショットの取得にある程度の⼯数が必要となる瞬間⾵速的な遅延や詳細のトレースは難しい確認したい項⽬を探すのに時間がかかるある程度の経験が必要なため、DBA頼りになってしまうリアルタイム監視ができない前提で運⽤を考える必要があった Oracle Database 10gからより簡単に性能分析ができるツールである AWRを含めたDiagnostics Packが登場 Statspackを運⽤する上での課題

AWR(Automatic Workload Repository)について Oracle Databaseの性能分析ツール Copyright © 2024, Oracle and/or
its affiliates 12 AWR: Automatic Workload Repositoryの略 • Oracleインスタンス全体の実⾏統計を取得してくれるツール • Oracle Database 10gから利⽤可能 • 利⽤するには、Enterprise Edition + Diagnostics Packが必要 • 取得したAWRスナップショットを基に結果レポートを出⼒ • 結果レポートはテキスト及びHTML形式 • スナップショットはバックグラウンドプロセスのMMONにより⾃動で取得される • ADDMがAWRのデータを定期的に分析し、⾃動でパフォーマンスの診断を⾏う • ASHによるリアルタイム監視も可能 AWRレポート Database 管理者 SGA 統計情報負荷の⾼いSQL … 60分毎 (デフォルト) MMON AWR (SYSAUX表領域) 8⽇間分 (デフォルト) スナップショットスナップショットスナップショットスナップショット

AWR取得の流れ Oracle Databaseの性能分析ツール Copyright © 2024, Oracle and/or its affiliates
13 (アプリケーションの実⾏) Ⓐ Ⓑ Ⓐ〜Ⓑ間の値を基に、DB内部の挙動を把握 Ⓐ時点の DB内部統計データ取得 Ⓑ時点の DB内部統計データ取得スナップショットの取得 ü Statspackと同様、ある2時点間で取得した内部統計データの差分を基に、その間のパフォーマンス統計データを結果レポートに出⼒ ‒ スナップショットの取得はMMONにより2時点とも⾃動で実施 (AWRの保存期間はデフォルトで8⽇間) ‒ デフォルトは60分間隔で取得(最⼩10分〜最⼤1年で設定可能) 結果レポートの出⼒ ü 結果レポートはテキスト及びHTML形式にて出⼒ ‒ 異なるセクションや情報間の相互リンクが提供され、迅速なアクセスが可能 ü 出⼒された結果レポートに加え、パフォーマンス診断は ADDMが⾃動で実施出⼒レポート(⼀部抜粋)

AWRで取得される情報について Oracle Databaseの性能分析ツール Copyright © 2024, Oracle and/or its affiliates
14 AWRで収集できる情報はデフォルトでStatspackスナップショットLevel7相当となる。スナップショットレベル収集データ基本統計情報アドバイス情報 SQL統計情報 SQL詳細情報セグメント情報ラッチ詳細情報 Level 0 〇〇 Level 5 〇〇〇 Level 6 〇〇〇〇 Level 7 〇〇〇〇〇 Level 10 〇〇〇〇〇〇 Statspack (デフォルト) AWR (デフォルト)

ADDMについて Oracle Databaseの性能分析ツール Copyright © 2024, Oracle and/or its affiliates
15 スナップショットスナップショットスナップショットスナップショット AWRのデータを定期的に分析し、データベースのパフォーマンスを監視および診断 • AWRに収集されたデータを定期的に分析し、データベースのパフォーマンスを監視 / 診断 • 問題(DB Timeへの影響が⼤きいもの)を発⾒すると、解決⽅法とともに画⾯に表⽰ SGA 統計情報負荷の⾼いSQL … MMON AWR ADDM 診断結果/アドバイス起動スナップショットの差分を診断 SQLチューニング・アドバイザの実⾏を推奨 Enterprise Managerの画⾯例

ASH(アクティブセッション履歴)について Oracle Databaseの性能分析ツール Copyright © 2024, Oracle and/or its affiliates
16 Enterprise Managerの画⾯例) 時間 1 2 3 4 アクティブ・セッション数経過時間 t0 t1 ユーザ1 ユーザ2 ユーザ3 ユーザn 最も負荷が多い短期間のパフォーマンス問題の分析 • アクティブ・セッションの情報を1秒ごとにメモリー(SGA)に格納 • SGAから10秒ごとの情報としてSYSAUX領域に格納 • 期間を指定し、ASHレポートを⽣成可能

AWR、ADDM、ASHの関係性 Oracle Databaseの性能分析ツール Copyright © 2024, Oracle and/or its affiliates
17 スナップショットスナップショットスナップショットスナップショット AWR ADDM 診断結果/ アドバイス起動スナップショットの差分を診断 SGA ASHデータ統計情報負荷の⾼いSQL … MMON AWRスナップショット (デフォルト60分間隔) ASHデータの転送 (SGA内のバッファ満杯時) MMNL SYSAUX領域 ASH 統計診断結果は SYSAUX領域へ保存 AWR • Oracle Databaseのパフォーマンス監視とチューニングに使⽤される情報を収集し、保管するリポジトリ • AWRスナップショットはMMONプロセスにより取得され、 AWRへ保管される ADDM • パフォーマンス分析及び診断を⾃動で実施するツール • AWRスナップショットがAWRに保管された際にMMONにより起動され、AWR内の2つのスナップショットの差分データを分析する • 診断結果はSYSAUX領域へと保存される ASH • 動的パフォーマンスビューに格納される、アクティブなセッションがデータベース内で何をしているかを表すデータ • MMNLによって1秒間隔のアクティブなセッション状態を SGA内のASHバッファ上に保持 • ASHバッファが満杯になれば、MMNLによりSYSAUXへ転送される

StatspackとAWRの⽐較 Oracle Databaseの性能分析ツール Copyright © 2024, Oracle and/or its affiliates
18 Statspack • Oracle8i R8.1.6〜 • エディション制限なし、オプションライセンス不要 • インストールスクリプトによるインストールが必要 • 定期的にスナップショットを取得する場合job、 cronなどを利⽤ • レポート形式はテキストのみ AWR Oracle Database 10g〜 • Enterprise Edition＋レポート出⼒はDiagnostics Pack が必要 • インストール不要 • デフォルト60分間隔での⾃動取得、8⽇間の保存 • 取得⽅法、レポートの改良（性能、出⼒形式、種類） • Diagnostics Packのほかの機能との連携やEMとの連携 StatspackはOracle8i R8.1.6以降のOracle Databaseであれば利⽤可能、 AWRはStatspackの進化版で使いやすさ、機能⾯も充実しているがライセンスが必要＝ライセンスによって使い分け進化

想定されるケースと出⼒レポートの活⽤⽅法性能分析ツールの実践的活⽤ Copyright © 2024, Oracle and/or its affiliates 20
• 対象DBにアクセスしているすべての処理で通常よりも遅延が発⽣ ⇒ 正常時/問題発⽣時の出⼒レポートの⽐較分析 • プロアクティブな性能問題の兆候分析によるパフォーマンストラブルの未然防⽌ ⇒ 定期的なレポートの抽出と⽐較分析 • 上記による分析対象SQL特定時での⼀定期間におけるSQL実⾏状況の把握や改善のための分析 ⇒ 特定SQLの対象期間におけるStatspack SQLレポートもしくはAWR SQLレポートの分析

参考:Statspack SQLレポートもしくはAWR SQLレポートの出⼒⽅法性能分析ツールの実践的活⽤ Copyright © 2024, Oracle and/or its
affiliates 21 StatspackもAWRも、個別のSQLIDを指定してSQL実⾏計画や実⾏統計を出⼒することが可能です。《取得⽅法》 n Statspack(Level6以上でスナップショットを取得する必要あり) $ORACLE_HOME/rdbms/admin/sprepsql.sql n AWR $ORACLE_HOME/rdbms/admin/awrsqrpt.sql 本資料では上記のコマンドで出⼒できるStatspackレポートまたはAWRレポートを Statspack SQLレポートまたはAWR SQLレポートと表現させていただきます。 StatspackおよびAWRともに実施可能

正常時/問題発⽣時の出⼒レポートの⽐較分析の流れ性能分析ツールの実践的活⽤ Copyright © 2024, Oracle and/or its affiliates 22
1. DBインスタンスの稼働状況を把握(負荷傾向の確認) 2. 待機イベントから問題発⽣時に発⽣しているボトルネックを特定(問題特定) 3. ボトルネックとなっている待機イベントの増加原因の分析と対策の策定(問題の調査・対策検討)

Load Profile Per Second Per Transaction ~~~~~~~~~~~~ --------------- --------------- DB Time(s): 0.0 0.1 DB CPU(s): 0.0 0.0 Redo size: 4,034,803.8 2,499.9 Logical reads: 215,587.1 164.6 Block changes: 6,833.0 9.5 Physical reads: 21.1 0.4 Physical writes: 899.5 0.4 User calls: 9,897.6 6.8 Parses: 26.3 6.8 Hard parses: 0.2 0.2 W/A MB processed: 75.2 0.6 Logons: 0.3 1.0 Executes: 3,011.2 1.7 Rollbacks: 805.0 0.0 Transactions: 2,035.3 正常時 Load Profile Per Second Per Transaction ~~~~~~~~~~~~ --------------- --------------- DB Time(s): 24.5 0.0 DB CPU(s): 6.8 0.0 Redo size: 16,605,841.7 2,578.4 Logical reads: 1,181,640.3 183.5 Block changes: 53,613.6 8.3 Physical reads: 109.4 0.0 Physical writes: 3,002.2 0.5 User calls: 28,759.9 4.5 Parses: 77.2 0.0 Hard parses: 0.3 0.0 W/A MB processed: 125.0 0.0 Logons: 0.3 0.0 Executes: 10,313.1 1.6 Rollbacks: 3,785.0 0.6 Transactions: 6,440.4 問題発⽣時正常時と⽐較して、インスタンスの処理の状況がどのように変化しているかを確認する 1．DBインスタンスの稼働状況を把握(負荷傾向の確認) StatspackおよびAWRともに実施可能

Top 10 Foreground Events by Total Wait Time ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tota Wait % DB Event Waits Time Avg(ms) time Wait Class ------------------------------ ------------ ------ ------- ------ ----------- DB CPU 3483.6 90.5 log file sync 164,157 164.2 1 4.3 Commit db file sequential read 78,151 156.3 2 4.1 User I/O gc cr block 2-way 25,156 25.2 1 .6 Cluster gc current block 2-way 20,982 20.1 1 .5 Cluster library cache pin 60 .5 0 .0 Concurrency control file sequential read 3,796 .5 1 .0 System I/O SQL*Net more data to client 202,116 .4 0 .0 Network IPC send completion sync 551 .2 0 .0 Other enq: FB - contention 1,746 .0 0 .0 Concurrency Top 10 Foreground Events by Total Wait Time ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tota Wait % DB Event Waits Time Avg(ms) time Wait Class ------------------------------ ------------ ------ ------- ------ ----------- log file sync 100,157 2203.5 22 42.4 Commit DB CPU 2067.6 39.8 db file sequential read 48,151 866.7 18 16.6 User I/O gc cr block 2-way 25,156 25.2 1 .4 Cluster gc current block 2-way 20,982 20.1 1 .4 Cluster library cache pin 42 .4 0 .0 Concurrency control file sequential read 1,717 .3 1 .0 System I/O SQL*Net more data to client 104,116 .3 0 .0 Network IPC send completion sync 451 .2 0 .0 Other enq: FB - contention 1,625 .0 0 .0 Concurrency 正常時問題発⽣時正常時と⽐較して処理量の増加分等を考慮しても待機時間が⼤幅に増加している待機イベント、 1待機あたりの平均待機時間が増加している待機イベントを特定 2. 待機イベントから問題発⽣時に発⽣しているボトルネックを特定(問題特定) StatspackおよびAWRともに実施可能

待機時間の発⽣起因の洗い出し • 待機イベントはどのような処理で発⽣するか︖ • 待機イベントの発⽣に関わるリソースや設定は何か︖ データベース設定 CPU ディスクI/O N/W ・・・原因に応じた対応を実施例) 問題の待機イベントの特定 3. ボトルネックとなっている待機イベントの増加原因の分析と対策の策定(問題の調査・対策検討) StatspackおよびAWRともに実施可能

定期的なレポートの抽出と⽐較分析の流れ性能分析ツールの実践的活⽤ Copyright © 2024, Oracle and/or its affiliates 26
1. ⼀定期間の稼働状況のスナップショット間の差分情報取得及び出⼒(情報採取) 2. 取得したデータのグラフ化と傾向分析(問題及び予兆のチェック) StatspackおよびAWRともに実施可能

select '"' || es.DBID || '","' || es.INSTANCE_NUMBER || '","' || bs.SNAP_ID || '","' || to_char(bs.END_INTERVAL_TIME, 'yyyy') || '","' || to_char(bs.END_INTERVAL_TIME, 'mm') || '","' || to_char(bs.END_INTERVAL_TIME, 'dd') || '","' || to_char(bs.END_INTERVAL_TIME, 'hh24') || '","' || to_char(bs.END_INTERVAL_TIME, 'mi') || '","' || to_char(bs.END_INTERVAL_TIME, 'ss') || '","' || es.SNAP_ID || '","' || … from DBA_HIST_SNAPSHOT bs, DBA_HIST_SNAPSHOT es, … CSVで出⼒できるようにSQLを左記のような形で作成 ※列を || ‘“,“’ || で区切らない場合、CSVで出⼒される際に固定⻑となり、列間に⼤量のスペースが⼊るので注意 DBA_HISTビュー(AWRの場合) AWRによって取得された情報を確認するためのビュー AWRが取得したスナップショットを確認できるDBA_HIST_SNAPSHOTビューを使⽤ ※Statspackの場合はSTATSPACK.SNAPやSTATSPACK.STATS$を参照例) CSVファイルで出⼒する場合 1. ⼀定期間の稼働状況のスナップショット間の差分情報取得及び出⼒(情報採取) StatspackおよびAWRともに実施可能

取得したCSVファイル 0 1 2 3 4 5 6 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 1 11 db file sequential read - orcl1 db file sequential read - orcl2 0 1 2 3 4 5 6 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 15 11 db file sequential read - orcl1 db file sequential read - orcl2 スプレッドシート機能を使⽤してグラフ化例) 待機イベント db file sequential read の1待機あたりの待機時間の推移 11/1はブロック平均物理読み込みにおける待機時間は平均1ミリ秒〜2ミリ秒で推移 14⽇後には1ブロックの平均物理読み込みにおける待機時間は平均2〜4ミリ秒で推移 ➡劣化傾向の確認 11/1 11/15 2. 取得したデータのグラフ化と傾向分析(問題及び予兆のチェック) StatspackおよびAWRともに実施可能

0 2,000 4,000 6,000 8,000 10,000 12,000 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 1 11 read by other session rdbms ipc reply enq: TX - index contention direct path read SQL*Net message to client db file sequential read log file sync DB CPU 0 2,000 4,000 6,000 8,000 10,000 12,000 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 15 11 read by other session rdbms ipc reply enq: TX - index contention direct path read SQL*Net message to client enq: TX - row lock contention db file sequential read log file sync DB CPU 例) フォアグラウンドプロセスにて待機時間の多い上位待機イベントの待機時間の推移 11/1には発⽣していなかった待機イベント(ここではTX⾏ロック競合)が待機時間の多い上位待機イベントとして発⽣ ➡発⽣原因を調査 11/1 11/15 2. 取得したデータのグラフ化と傾向分析(問題及び予兆のチェック) StatspackおよびAWRともに実施可能

例) SGA内の各コンポーネントサイズの推移 4,000,000,000 6,000,000,000 8,000,000,000 10,000,000,000 12,000,000,000 14,000,000,000 16,000,000,000 18,000,000,000 0 1 2 3 4 5 6 7 8 9 1011121314151617181920212223 0 1 2 3 4 5 6 7 8 9 1011121314151617181920212223 0 1 2 3 4 5 6 7 8 9 1011121314151617181920212223 1 2 3 11 java pool streams pool large pool shared pool DEFAULT buffer cache 11/1から11/3にかけて時間の経過とともに特定のコンポーネントの増加、減少傾向 (ここでは共有プールが増加し、デフォルトバッファキャッシュが減少している)を確認 ➡要因原因を調査 2. 取得したデータのグラフ化と傾向分析(問題及び予兆のチェック) StatspackおよびAWRともに実施可能

定期的なレポートの抽出と⽐較分析(AWRの場合) 性能分析ツールの実践的活⽤ Copyright © 2024, Oracle and/or its affiliates 31
インスタンス負荷傾向把握のための取得項⽬の例と使⽤するDBA_HISTビュー分析項⽬ DBA_HISTビュー使⽤する列 SQL実⾏回数 DBA_HIST_SYSSTAT (STAT_NAME = execute count) ①STAT_NAME ②VALUE REDO⽣成量 DBA_HIST_SYSSTAT (STAT_NAME = redo size) 論理読み込みブロック数 DBA_HIST_SYSSTAT (STAT_NAME in db block gets, consistent gets) 物理読み込みブロック数 DBA_HIST_SYSSTAT (STAT_NAME = physical reads) 分析項⽬ DBA_HISTビュー使⽤する列待機イベント・CPU時間 DBA_HIST_SYSTEM_EVENT ①EVENT ②TIME_WAITED_MICRO_FG DBA_HIST_SYS_TIME_MODEL (STST_NAME = DB CPU) ①STAT_NAME ②VALUE インスタンスボトルネック傾向把握のための取得項⽬の例と使⽤するDBA_HISTビュー差分を利⽤して⽐較分析 Enterprise Edition + Diagnostics pack にて実施可能

インスタンスの性能傾向把握のための取得項⽬の例と使⽤するDBA_HISTビュー分析項⽬ DBA_HISTビュー使⽤する列読み込みI/O性能 DBA_HIST_SYSTEM_EVENT (EVENT_NAME = db file sequential read) ①EVENT_NAME ②TIME_WAITED_MICRO_FG ③TOTAL_WAITS_FG 1待機あたりの平均待機時間＝②/③ 書き込みI/O性能 DBA_HIST_SYSTEM_EVENT (EVENT_NAME = log file parallel write) コミット性能 DBA_HIST_SYSTEM_EVENT (EVENT_NAME = log file sync) キャッシュフュージョン性能 (Currentブロック) DBA_HIST_SYSSTAT (STAT_NAME in (gc current blocks received gc current block receive time)) ①STAT_NAME ②VALUE(〜 receive time) ③VALUE(〜 received) 1転送あたりの平均時間 =②/③ キャッシュフュージョン性能 (CRブロック) DBA_HIST_SYSSTAT (STAT_NAME in (gc cr blocks received gc cr block receive time)) 差分を利⽤して⽐較分析 Enterprise Edition + Diagnostics pack にて実施可能

分析項⽬ DBA_HISTビュー使⽤する列 SGAサイズ DBA_HIST_MEM_DYNAMIC_COMP (COMPONENT in (DEFAULT buffer cache, java pool, large pool, shared pool, streams pool)) ①COMPONENT ②SURRENT_SIZE PGAサイズ (セッションあたりの確保可能サイズ) DBA_HIST_PGASTAT (NAME = global memory bound) ①NAME ②VALUE PGAサイズ (スナップ取得時の確保サイズ) DBA_HIST_PGASTAT (NAME = total PGA allocated) インスタンスのメモリ傾向把握のための取得項⽬の例と使⽤するDBA_HISTビュー ※複数バッファ・プールや⾮標準ブロック・サイズを使⽤している場合は、SGAサイズの確認対象コンポーネントを適宜追加してください。 Enterprise Edition + Diagnostics pack にて実施可能

Enterprise ManagerによるAWRの期間⽐較レポート性能分析ツールの実践的活⽤ Copyright © 2024, Oracle and/or its affiliates
34 ベースライン機能 • Enterprise Managerより提供される機能 - 特定期間を⽰すスナップショットのペアをベースラインとして設定 - データベースの「正常な」パフォーマンス状態を⽰す基準点として機能させることが可能 • パフォーマンスの⽐較及び根本原因の分析 - AWRによって取得、格納されたレポートを定期的に参照 - 設定していたベースラインと他の期間で取得したベースラインを⽐較 - 設定していたベースラインよりも低いパフォーマンスの原因分析 - データベース、SQLのパフォーマンス分析を実施 • 異常検出及びアラート - 設定したしきい値の超過によるアラートの設定が可能スナップショットスナップショットスナップショットスナップショット AWR SGA 統計情報負荷の⾼いSQL … スナップショット取得 ①ベースラインの設定過去のベースラインベースラインX 監視パフォーマンス X ③監視及び分析 Oracle Enterprise Manager ベースラインを基準としてパフォーマンス監視 A B スナップショットスナップショットベースライン ②ベースラインを基準として参照 Enterprise Edition + Diagnostics pack にて実施可能

affiliates 36 Stat Name Statement Per Execution % Snap ---------------------------------------- ---------- -------------- ------- Elapsed Time (ms) 2,183,155 42.4 12.0 CPU Time (ms) 1,025,266 19.9 11.1 Executions 51,526 N/A N/A Buffer Gets 9,971,515 193.5 13.2 Disk Reads 8,781,526 170.4 53.5 Parse Calls 12 0.5 0.0 Rows 51,526 1 N/A User I/O Wait Time (ms) 1,154,366 22.4 N/A Cluster Wait Time (ms) 2,382 0.0 N/A Application Wait Time (ms) 1,141 0.0 N/A Concurrency Wait Time (ms) 0 N/A N/A Invalidations 0 N/A N/A Version Count 1 N/A N/A Sharable Mem(KB) 3,167 N/A N/A ------------------------------------------------------------- Statspack SQLレポートもしくはAWR SQLレポートのSQL実⾏統計から改善できるポイントを確認どの部分に時間を要しているか等を確認するバッファ読み込み数や物理ブロック読み込み数の状況を確認する 1．SQL実⾏統計の分析(SQLの改善点の確認) StatspackおよびAWRともに実施可能

affiliates 37 Execution Plan ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (0)| | | 1 | NESTED LOOPS | | 1 | 12 | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | | 1 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 1 | 1 | 1 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID | IDX1_DEPT | 1 | 1 | 1 (0)| 00:00:01 | | 5 | INDEX RANGE SCAN | DEPT | 1 | 1 | 1 (0)| 00:00:01 | | 6 | INDEX RANGE SCAN | IDX1_EMP | 1 | 1 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------ 実⾏計画から実⾏統計で確認したポイントの改善余地有無を確認する改善の余地があった場合は、SQLチューニングを実施 2. SQL実⾏計画の分析(改善のための実⾏計画の検討) StatspackおよびAWRともに実施可能

リアルタイムSQL監視について性能分析ツールの実践的活⽤ Copyright © 2024, Oracle and/or its affiliates 39
リアルタイムSQL監視 • 実⾏中のSQLを⾃動で監視し、詳細な統計を取得、分析するプロセス - Oracle Database 11gから利⽤可能 - Enterprise Managerによるグラフィカルなレポート画⾯からの分析が可能 - 利⽤するにはEnterprise Edition + Diagnostics Pack + Tuning Packが必要 • 主な特徴 - 再現待ちや特別な設定をせず、すぐ分析を始められる - HTML形式でのレポート出⼒が可能 • 期待される導⼊効果 - 開発・テスト時からのチューニング制度の向上 - カットオーバー後に発⽣した性能問題の迅速な解決 - ⻑時間かかるSQL(バッチなど)への正確な対処 Enterprise Managerの画⾯例) 従来のチューニングリアルタイムSQL監視を使⽤したチューニングリアルタイムSQL監視結果の分析 V$表やStatspack による切り分け SQLトレース設定 OSスクリプト設定再現待ち集めた情報の整理、分析時間チューニング着⼿までの経過時間イメージ Enterprise Edition + Diagnostics Pack + Tuning Packにて実施可能

関連する動的パフォーマンスビューを参照 Oracle Database Oracle Enterprise Manager リアルタイムSQL監視について性能分析ツールの実践的活⽤
Copyright © 2024, Oracle and/or its affiliates 40 SGA V$SQL_MONITOR V$SQL_PLAN_MONITOR V$ACTIVE_SESSION_HISTORY V$SESSION_LONGOPS … SQL実⾏サーバープロセス SGA内の動的パフォーマンスビュー (V$表)にて実⾏中及び監視中の SQL、OS情報を提⽰監視は特定のいずれかの条件下にて⾃動で実⾏ • 5秒以上のCPU時間またはI/O時間を消費しているSQL • パラレル実⾏されているSQL • /*+ MONITOR */ヒントを指定しているSQL グラフィカルなレポート画⾯として出⼒ HTMLファイルとしても出⼒可能 HTMLファイル(test.html) 例) Enterprise Managerを利⽤する場合 ※Enterprise Managerを利⽤しない場合でも、SQL*Plusなどで以下の SQL⽂を実⾏するとHTMLファイルの出⼒が可能 set heading off set echo off set linesize 1000 set long 2000000 set longchunksize 2000000 set trims on spool test.html select dbms_sqltune.report_sql_mo nitor( sql_id => '<SQLID>', type => 'active' ) from dual; デフォルトは‘TEXT‘だが、 ’ACTIVE’を設定すると表現豊かなEMと同等の表⽰となる Enterprise Edition + Diagnostics Pack + Tuning Packにて実施可能

監視対象となる主な動的パフォーマンスビュー(V$表) • V$SQL_MONITOR - 実⾏がOracle Databaseによって監視されたSQL⽂のパフォーマンス統計と監視情報を格納する。エントリは SQL⽂の1回の実⾏のみに対応し、1秒ごとにリアルタイムで更新される。実⾏終了後も最低1分間は情報が保持される。 • V$SQL_PLAN_MONITOR - V$SQL_MONITORにて記載されているSQL⽂の実⾏計画における各ステップに対するパフォーマンス統計と監視情報を格納する。V$SQL_MONITORと同様、1秒ごとにリアルタイムで更新される。 • V$ACTIVE_SESSION_HISTORY - システム内のアクティブなセッションの履歴情報をリアルタイムに近い形で格納する。1秒ごとにサンプリングされ、セッションの待機イベントやリソースの使⽤状況、アクティビティの詳細(実⾏中のSQL⽂や実⾏計画のステップ) などの情報が参照可能。 • V$SESSION_LONGOPS - 実⾏中の⻑時間(6秒以上)操作に関する情報を格納する。⻑時間操作の進⾏状況や完了⾒込み時間などが参照可能。 Enterprise Edition + Diagnostics Pack + Tuning Packにて実施可能

Enterprise Edition + Diagnostics Pack + Tuning Packにて実施可能

V$SQL_MONITOR Oracle Databaseによって監視されるSQL⽂の詳細情報を表⽰ V$SQL_PLAN_MONITOR V$SQL_MONITORによって監視されるSQL⽂の実⾏計画の詳細情報を表⽰ V$SESSION_LONGOPS ⻑時間の操作における経過時間や終了予測時間を表⽰ Enterprise Edition + Diagnostics Pack + Tuning Packにて実施可能

V$ACTIVE_SESSION_HISTORY アクティブなセッション履歴、待機イベントやリソース使⽤状況などを表⽰ Enterprise Edition + Diagnostics Pack + Tuning Packにて実施可能

Oracle Autonomous Databaseによる性能改善の⾃動化技術の進展による性能分析の運⽤の変化 Copyright © 2024, Oracle and/or its
affiliates 46 AI/機械学習により常に監視＆対策を⾃動で実施 • リソースの最適化 - 負荷状況を⾃動的に判断し、CPUの⾃動拡張/縮⼩を⾏う - ストレージの利⽤状況を⾃動的に判断し、割り当て容量の拡⼤を⾃動で⾏う • 実⾏計画管理の⾃動化 - 最新の統計情報、データ構成を機械的に都度収集し、断続的に適⽤テストを⾏い実⾏計画の安定化を実現 - データ量の変動やパッチ適⽤、バージョンアップに伴うアプリケーションの性能劣化を抑制 • ⾃動索引作成 - 実⾏中のSQLクエリの実⾏計画を分析して、それに基づいて新たなインデックスの候補を⽣成 - これまでのSQLチューニングと同様のアプローチをDBAの介在なしで実施 Oracle Autonomous DatabaseはStatspack、AWRを⽤いて実施していた性能分析及びチューニングを⾃動化

Copyright © 2024, Oracle and/or its affiliates 47 まとめ Oracle
Databaseにおける性能分析の進化性能分析ツールの実践的活⽤技術の進展による性能分析の運⽤の変化 • Statspack導⼊前では情報収集はすべて⼿動で⾏い、かつ洞察には経験が必要であったことから当時⾏う性能分析は負荷の⾼いものであった。 • Statspackのデータ収集、レポート出⼒機能により性能分析に対する負荷が軽減。 • AWR、ADDM、ASHなどの機能の組み合わせにより性能分析の負荷がより軽減され、かつ効率化された。 • Oracle Databaseの性能分析ツールから出⼒されるレポートの差分などの分析により、迅速な問題の把握、対処が可能。 • Oracle Enterprise Managerを利⽤することでグラフィカルで直観的な情報の識別が可能。 • Oracle Tuning Packを利⽤したリアルタイムSQL監視の実施により、チューニング制度の向上やより迅速な性能問題の解決が可能。 • 性能分析及びチューニングの⾃動化されたOracle Autonomous Databaseが登場。 - AI/機械学習により常に監視、⾃動判断によりチューニングを実施。 - リソース管理、実⾏計画管理、索引作成などがDBAの介在なしで実現可能。

Oracle Database 性能分析入門

Oracle Database 性能分析入門

Video

More Decks by oracle4engineer

Featured

Transcript