暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 使用AHF诊断Oracle数据库性能

使用AHF诊断Oracle数据库性能

原创 majunyang 2025-05-26
89

安装AHF 24.10

如果已经安装AHF，则在安装过程会提示进行升级。

  • 解压AHF-LINUX_v24.10.0.zip
[root@rac1 ~]# unzip AHF-LINUX_v24.10.0.zip
Archive:  AHF-LINUX_v24.10.0.zip
  inflating: ahf_setup
 extracting: ahf_setup.dat
  inflating: README.txt
  inflating: oracle-tfa.pub
  • 安装AHF
[root@rac1 ~]# ./ahf_setup

AHF Installer for Platform Linux Architecture x86_64

AHF Installation Log : /tmp/ahf_install_2410000_15005_2024_12_06-09_46_32.log

Starting Autonomous Health Framework (AHF) Installation

AHF Version: 24.10.0 Build Date: 202411061500

AHF is already installed at /opt/oracle.ahf

Installed AHF Version: 24.4.0 Build Date: 202405030509
#选择y进行更新
Do you want to upgrade AHF ? [Y]|N : y

AHF will also be installed/upgraded on these Cluster Nodes :

1. rac2

The AHF Location and AHF Data Directory must exist on the above nodes
AHF Location : /opt/oracle.ahf
AHF Data Directory : /u01/app/grid/oracle.ahf/data
#选择y更新另一节点AHF
Do you want to -upgrade AHF on Cluster Nodes ? [Y]|N : y

Upgrading /opt/oracle.ahf

Shutting down AHF Services

Upgrading AHF Services

Started retype of index schema

Starting AHF Services
No new directories were added to TFA
Directory /u01/app/grid/crsdata/rac1/trace/chad was already added to TFA Directo                                                                  ries.

AHF upgrade completed on rac1

Upgrading AHF on Remote Nodes :

AHF will be installed on rac2, Please wait.

Upgrading AHF on rac2 :

[rac2] Copying AHF Installer

[rac2] Running AHF Installer

Do you want AHF to store your My Oracle Support Credentials for Automatic Upload ? Y|[N] : n

.-------------------------------------------------------------.
| Host | TFA Version | TFA Build ID          | Upgrade Status |
+------+-------------+-----------------------+----------------+
| rac1 | 24.10.0.0.0 | 241000020241106150005 | UPGRADED       |
| rac2 | 24.10.0.0.0 | 241000020241106150005 | UPGRADED       |
'------+-------------+-----------------------+----------------'

Setting up AHF CLI and SDK

AHF is successfully upgraded to latest version

Moving /tmp/ahf_install_2410000_15005_2024_12_06-09_46_32.log to /u01/app/grid/oracle.ahf/data/rac1/diag/ahf/

AHF默认安装在 /opt/oracle.ahf 目录

  • 查看ahf,ahtctl,tfactl工具路径
[root@rac1 bin]# cd /opt/oracle.ahf/bin
[root@rac1 bin]# ls -l
total 40
-rwxr-xr-x 1 root root 4631 Dec  6 09:56 ahf
-rwxr-xr-x 1 root root 7771 Dec  6 09:56 ahfctl
-rwxr-xr-x 1 root root 4701 Dec  6 09:56 balance
-rwxr-xr-x 1 root root 4701 Dec  6 09:56 iwareport
lrwxrwxrwx 1 root root   34 Aug  8 14:24 oerr -> /opt/oracle.ahf/orachk/lib/oerr.sh
lrwxrwxrwx 1 root root   29 Aug  8 14:24 orachk -> /opt/oracle.ahf/orachk/orachk
-rwxr-xr-x 1 root root 7771 Dec  6 09:56 tfactl
  • 查看ahf版本与命令帮助
[root@rac1 bin]# ahf -v
AHF version: 24.10.0
Build Timestamp: 20241106150005
TFA version: 24.10.0
Compliance version: 24.10.0
Compliance metadata version: 20241106

[root@rac1 bin]# ahf -h
usage: ahf category action [options]

category:
  {analysis,configuration,data,observer,security,software}
    analysis            Analysis category actions
    configuration       Configuration category actions
    data                Data category actions
    observer            Observer category actions
    security            Security category actions
    software            Software category actions

options:
  -h, --help            show this help message and exit
  --version, -v         Get version
  --debug, -d           Show additional debug information in log file

Specify --debug or -d to enable debug logging

Usage Example:

ahf --debug software get-version

ahf -d software get-version
  • ahfctl 命令帮助
[root@rac1 bin]# ahfctl -h
Usage : ahfctl <command> [options]
        | applyupdate
        | celldiagcollect
        | checkpassword
        | checksmtp
        | checkupload
        | compliance
        | deleteupdatebackup
        | get
        | getosgroup
        | getresourcelimit
        | getsecretvalue
        | getsmtp
        | getupdate
        | getupgrade
        | getupload
        | import
        | loadpolicy
        | moveahf
        | moveahfloc
        | movedatadir
        | printresourcestats
        | queryupdate
        | redact
        | restartahf
        | rmap
        | rollbackupdate
        | set
        | setosgroup
        | setpassword
        | setresourcelimit
        | setsmtp
        | setupdate
        | setupgrade
        | setupload
        | showrepo
        | startahf
        | statusahf
        | stopahf
        | switch
        | uninstall
        | unloadpolicy
        | unset
        | unsetpassword
        | unsetresourcelimit
        | unsetsmtp
        | unsetupdate
        | unsetupgrade
        | unsetupload
        | update
        | upgrade
        | upgradehistory
        | upload
        | version


For detailed help on each command use: ahfctl <command> -h

收集DB性能报告

通过以下命令捕获在 dbperf SRDC 中进行性能优化所需的诊断数据。

oracle@rac1:/home/oracle> tfactl diagcollect -srdc dbperf -database orcl
Is the issue related to a specific Plugable Database? [Y|N]  [Required for this SRDC]: y #CDB数据库
Enter the PDB Name  [Required for this SRDC] : pdb1 #指定要分析收集PDB
Do you have a performance issue now [Y|y|N|n] [Y]: y #
Enter duration of the issue in hours [<RETURN>=1h] : #默认1小时
As you have indicated that the performance issue is currently happening,
Performance Reports will be collected for the following periods:
Start time when the performance was bad:  2024-12-06 09:50:18 
Stop time when the performance was bad:   2024-12-06 10:50:18
For comparison, it is useful to gather data from another period with similar load where problems are not seen. Typically this is likely to be the same time period on a previous day. To compare to the same time period on a previous day enter the number of days ago you wish to use. [<RETURN> to provide other time range] : 1 #指定基线对比，几天前
Start time when the performance was good 2024-12-05 09:50:18 
Stop time when the performance was good 2024-12-05 10:50:18
Has any SQL been identified to contribute to the performance issue?[Y|N]  [Required for this SRDC]: n #SQL语句收集，不收集
Do you wish to take an AWR Dump as part of this collection? 
 NOTE: AWR Dump Can add signification size to the collection and increase the time necessary for the collection to complete. [Y|N] [Required for this SRDC]: y #导出收集awr dmp文件
Ending AWR snapshot successfully created.
Found 3 snapshot(s) in Bad Performance time range for database orcl
Found 3 snapshot(s) in Baseline Performance time range for orcl
"Automatic Workload Repository (AWR) is a licensed feature. Refer to My Oracle Support Document ID 1490798.1 for more information"

Components included in this collection: DATABASE CHMOS CHA OS INSIGHTS

Preparing to execute support diagnostic scripts.
 Executing DB Script srdc_db_lfsdiag.sql on orcl with timeout of 120 seconds...
 Executing DB Script srdc_real_time_addm.sql on orcl with timeout of 120 seconds...
 Executing DB Script srdc_statsadvisor_report.sql on orcl with timeout of 300 seconds...
 Executing DB Script collect_logon_logoff_triggers.sql on orcl with timeout of 300 seconds...
 Executing OS Script get_perfhub_report with timeout of 600 seconds...

Collecting data for all node(s).

TFA is using system timezone for collection, All times shown in CST.
Scanning files from 2024-12-06 09:50:18 CST to 2024-12-06 10:50:18 CST

Collection Id : 20241206105111rac1

Detailed Logging at : /u01/app/grid/tfa/repository/srdc_dbperf_collection_Fri_Dec_06_10_51_15_CST_2024_node_all/diagcollect_20241206105111_rac1.log

Waiting up to 60 seconds for collection to start
2024/12/06 10:51:21 CST : NOTE : Any file or directory name containing the string .com will be renamed to replace .com with dotcom
2024/12/06 10:51:21 CST : Collection Name : tfa_srdc_dbperf_Fri_Dec_06_10_51_13_CST_2024.zip
2024/12/06 10:51:21 CST : Collecting diagnostics from hosts : [rac1, rac2]
2024/12/06 10:51:23 CST : Getting list of files satisfying time range [12/06/2024 09:50:18, 12/06/2024 10:50:18]
2024/12/06 10:51:23 CST : Collecting Additional Diagnostic Information...
2024/12/06 10:51:57 CST : Completed Collection of Additional Diagnostic Information for Insights...
2024/12/06 10:52:13 CST : Executing DB Script awr_reports on orcl with timeout of 3600 seconds...
2024/12/06 10:52:14 CST : Collecting ADR incident files...
2024/12/06 10:54:06 CST : Executing DB Script awrdump on orcl with timeout of 3600 seconds...
2024/12/06 10:57:30 CST : Executing Applicable ORAchk Validations with timeout of 600 seconds...
2024/12/06 10:59:46 CST : Executing IPS Incident Package Collection(s)...
2024/12/06 10:59:52 CST : No ADR Incidents for orcl covering period "2024-12-06 09:50:18" to "2024-12-06 10:50:18" were generated, IPS Pack will not be collected.
2024/12/06 10:59:52 CST : Executing SQL Script db_feature_usage.sql on orcl with timeout of 600 seconds...
2024/12/06 10:59:52 CST : Executing Collection for OS with timeout of 1800 seconds...
2024/12/06 11:00:00 CST : Completed Collection of Additional Diagnostic Information...
2024/12/06 11:00:09 CST : Completed Local Collection
2024/12/06 11:00:09 CST : Not Redacting this Collection...
2024/12/06 11:00:09 CST : Remote Collection in Progress...
2024/12/06 11:00:10 CST : Collection completed on host: rac2
2024/12/06 11:00:13 CST : Executing Creation of insights zip with timeout of 900 seconds...
2024/12/06 11:00:45 CST : Finished creation of insights zip with status 0
2024/12/06 11:00:45 CST : Report rac1_insights_2024_12_06_11_00_14.zip added to rac1.tfa_srdc_dbperf_Fri_Dec_06_10_51_13_CST_2024.zip
2024/12/06 11:00:47 CST : Collection completed on host: rac1
2024/12/06 11:00:46 CST : Completed collection of zip files.

.--------------------------------.
|       Collection Summary       |
+------+-----------+------+------+
| Host | Status    | Size | Time |
+------+-----------+------+------+
| rac2 | Completed | 13MB | 290s |
| rac1 | Completed | 17MB | 528s |
'------+-----------+------+------'

Logs are being collected to: /u01/app/grid/tfa/repository/srdc_dbperf_collection_Fri_Dec_06_10_51_15_CST_2024_node_all
#收集文件存放路径
/u01/app/grid/tfa/repository/srdc_dbperf_collection_Fri_Dec_06_10_51_15_CST_2024_node_all/rac2.tfa_srdc_dbperf_Fri_Dec_06_10_51_13_CST_2024.zip
/u01/app/grid/tfa/repository/srdc_dbperf_collection_Fri_Dec_06_10_51_15_CST_2024_node_all/rac1.tfa_srdc_dbperf_Fri_Dec_06_10_51_13_CST_2024.zip

To check the list of collected files, run the command "tfactl collection list-contents -collectionzip <zipfile>"

使用AHF Insights

性能报告

将收集的rac1.tfa_srdc_dbperf_Fri_Dec_06_10_51_13_CST_2024.zip 文件下载到本地并解压，

image-20241206132755683

再解压rac1_insights_2024_12_06_11_00_14.zip文件。打开rac1_insights_2024_12_06_11_00_14\web文件夹的index.html

image-20241206161205619

AHF Insights 报告分为2个部分

System Topology

  • Cluster: Provides a summary of cluster and cluster resources, and ASM details.

  • Databases: Provides basic and detailed information about Oracle Databases running on the system.

  • Database Servers: Provides basic information about database servers.

Insights

  • Timeline: Provides Timeline visualization in a graph and provides a table with specific information about each timestamp.
  • Operating System Issues: Provides details about the metrics collected on the system and a detailed report on operating system anomalies.
  • Best Practice Issues: Provides the results of Best Practices Compliance checks run on the system, paginated.
  • System Change: Provides details on the changes applied to the system, paginated.
  • Recommended Software: Lists recommended software and links to supported versions.
  • Database Server: Provides details about the Management Server metrics and the alerts recorded in the Management Server.
  • RPM List: Lists RPMs and the differences between them across nodes, paginated.
  • Database Parameters: Lists normal and hidden Oracle Database parameters, paginated.
  • Kernel Parameters: Lists the kernel parameters, paginated.
  • Patch Information:
  • Space Analysis: Renders Disk Utilization and Diagnostice Space Usage data in visual and tabular format.
  • Performance Reports: Provides reports to monitor and analyze database performance

​

image-20241206142610892

AHF Insights 的 Performance Reports 部分，其中包含：

  • AWR 报告
  • AWR 比较报告
  • PerfHub 报告

image-20241206142752433

这些报告通过以下方式帮助 DBA 维护 Oracle 数据库的健康和效率：

  • 诊断性能瓶颈
  • 识别耗费资源的 SQL 查询
  • 监控系统工作负载和使用趋势
  • 协助数据库性能调整和优化

1.AWR 报告（自动工作负载存储库）

AWR 是 Oracle 的内置功能，用于收集、处理和存储数据库性能统计数据。这些统计数据用于生成指定时间范围内数据库性能的详细报告。

报告内容如下：

  • CPU、内存和 I/O 使用情况统计信息
  • 数据库等待事件（系统花费的时间）
  • 消耗最多资源的 SQL 查询
  • 实例活动和总体工作负载数据
  • 对象级统计信息，例如索引使用情况和表扫描

AWR 报告有助于诊断性能瓶颈、资源密集型 SQL 查询以及确定需要优化的领域。它们对于调整数据库并确保其高效运行非常有用。

2.AWR 比较报告**

AWR 比较报告允许用户比较两个特定时间段之间的数据库性能，从而了解性能指标随时间如何变化。

报告内容如下：

  • 等待事件、资源使用情况（CPU、内存和 I/O）和 SQL 性能等关键指标的比较
  • 突出显示两个快照之间的系统性能差异

这些报告对于识别系统事件（例如升级或配置更改）的影响以及比较不同工作负载下的性能特别有用。

3.PerfHub 报告

PerfHub 是 Oracle Cloud（自治数据库）的一部分，提供用于实时性能诊断的交互式图形界面。它允许用户通过仪表板可视化性能数据，从而提供一种现代化的性能报告方法。

打开PerfHub 报告

image-20241206162441321

报告内容如下：

  • CPU 和内存使用趋势
  • SQL 性能数据
  • I/O 指标
  • 最主要的等待和系统瓶颈

图标显示平均活动会话，ASH分析，SQL监视，ADDM，工作负载信息。可以分类进行查看。

image-20241206162852894

参考文档：

Autonomous Health Framework (AHF) - Including Trace File Analyzer and Orachk/Exachk (文档 ID 2550798.1)

Oracle Autonomous Health Framework Users Guide

墨力计划oracle ahf
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论