文档内容
适用于:Oracle Database - Enterprise Edition - 版本 10.2.0.1 和更高版本本文档所含信息适用于所有平台 目标本文旨在提供如何解释跟数据库性能问题息息相关的AWR信息。 需要注意的是生成 AWR Report 或访问 AWR 相关的视图,以及使用任何 AWR 相关的诊断信息,都需要额外的 Diagnostic Pack License。这包括生成 AWR/ADDM/ASH report,也包括当技术支持要求的生成上述报表时。 注意: Oracle Diagnostics Pack (以及 Oracle Tuning Pack) 只在企业版中提供。 Oracle® Database Licensing Information 最佳实践如何主动避免问题发生及做好诊断信息的收集有些问题是无法预见的,但大部分其它的问题如果及早发现一些征兆其实是可以避免的。同时,如果问题确实发生了,那么收集问题发生时的信息就非常重要。有关于如何主动避免问题及诊断信息的收集,请参见:
Document 1482811.1 Best Practices: Proactively Avoiding Database and Query Performance Issues
Document 1477599.1 Best Practices Around Data Collection For Performance Issues 提出问题、获取帮助并分享您的经验 您想要与其他 Oracle 客户、Oracle 员工及业内专家深入探讨吗? 解决方案对于数据库整体的性能问题,AWR的报告是一个非常有用的诊断工具。
Document 1363422.1 Automatic Workload Repository (AWR) Reports - Start Point
注:最好一开始我们从ADDM报告入手,因为对应时间段的ADDM报告往往已经指出了问题所在。
参见: Use of ADDM Reports alongside AWR
Interpretation在处理性能问题时,我们最关注的是数据库正在等待什么。
值得注意的wait events
诊断其他问题关于其他性能问题,请参照文档:
Document 1377446.1 Troubleshooting Performance Issues
使用ADDM的报告当分析性能问题时,除了AWR报告,我们还可以同时参照ADDM报告,对于潜在的性能问题,它同时提供了具体的解决方案建议。下面是从如下文档拿到的一个ADDM报告示例:
Note:250655.1How to use the Automatic Database Diagnostic Monitor:
DETAILED ADDM REPORT FOR TASK 'SCOTT_ADDM' WITH ID 5
---------------------------------------------------- Analysis Period: 17-NOV-2003 from 09:50:21 to 10:35:47 Database ID/Instance: 494687018/1 Snapshot Range: from 1 to 3 Database Time: 4215 seconds Average Database Load: 1.5 active sessions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FINDING 1: 65% impact (2734 seconds) ------------------------------------ PL/SQL execution consumed significant database time. RECOMMENDATION 1: SQL Tuning, 65% benefit (2734 seconds) ACTION: Tune the PL/SQL block with SQL_ID fjxa1vp3yhtmr. Refer to the "Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and Reference" RELEVANT OBJECT: SQL statement with SQL_ID fjxa1vp3yhtmr BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END; FINDING 2: 35% impact (1456 seconds) ------------------------------------ SQL statements consuming significant database time were found. RECOMMENDATION 1: SQL Tuning, 35% benefit (1456 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID gt9ahqgd5fmm2. RELEVANT OBJECT: SQL statement with SQL_ID gt9ahqgd5fmm2 and PLAN_HASH 547793521 UPDATE bigemp SET empno = ROWNUM FINDING 3: 20% impact (836 seconds) ----------------------------------- The throughput of the I/O subsystem was significantly lower than expected. RECOMMENDATION 1: Host Configuration, 20% benefit (836 seconds) ACTION: Consider increasing the throughput of the I/O subsystem. Oracle's recommended solution is to stripe all data file using the SAME methodology. You might also need to increase the number of disks for better performance. RECOMMENDATION 2: Host Configuration, 14% benefit (584 seconds) ACTION: The performance of file D:\ORACLE\ORADATA\V1010\UNDOTBS01.DBF was significantly worse than other files. If striping all files using the SAME methodology is not possible, consider striping this file over multiple disks. RELEVANT OBJECT: database file "D:\ORACLE\ORADATA\V1010\UNDOTBS01.DBF" SYMPTOMS THAT LED TO THE FINDING: Wait class "User I/O" was consuming significant database time. (34% impact [1450 seconds]) FINDING 4: 11% impact (447 seconds) ----------------------------------- Undo I/O was a significant portion (33%) of the total database I/O. NO RECOMMENDATIONS AVAILABLE SYMPTOMS THAT LED TO THE FINDING: The throughput of the I/O subsystem was significantly lower than expected. (20% impact [836 seconds]) Wait class "User I/O" was consuming significant database time. (34% impact [1450 seconds]) FINDING 5: 9.9% impact (416 seconds) ------------------------------------ Buffer cache writes due to small log files were consuming significant database time. RECOMMENDATION 1: DB Configuration, 9.9% benefit (416 seconds) ACTION: Increase the size of the log files to 796 M to hold at least 20 minutes of redo information.
其他的AWR参考文章当阅读AWR报告的其他部分时,可以参照下面的一些文档:
Document 786554.1 How to Read PGA Memory Advisory Section in AWR and Statspack Reports
Document 754639.1 How to Read Buffer Cache Advisory Section in AWR and Statspack Reports Document 1301503.1 Troubleshooting: AWR Snapshot Collection issues Document 1363422.1 Automatic Workload Repository (AWR) Reports - Start Point StatspackAWR报告取代了旧有的staspack及bstat/estat报告,下面的这些文档概述了如何阅读statspack报告: Additional information can be found in the following articles:
Document 94224.1 FAQ- Statspack Complete Reference
Document 394937.1 Statistics Package (STATSPACK) Guide Document 149113.1 Installing and Configuring StatsPack Package Document 149121.1 Gathering a StatsPack snapshot Document 228913.1 Systemwide Tuning using STATSPACK Reports
参考NOTE:1349387.1 - Troubleshooting 'cursor: pin S wait on X' waits.NOTE:6904068.8 - Bug 6904068 - High CPU usage when there are "cursor: pin S" waits NOTE:262687.1 - Using the DBMS_SQLTUNE package to Run the Sql Tuning Advisor NOTE:271196.1 - Automatic SQL Tuning and SQL Profiles NOTE:276103.1 - Performance Tuning Using Advisors and Manageability Features: AWR, ASH, ADDM and Sql Tuning Advisor NOTE:34405.1 - WAITEVENT: "buffer busy waits" Reference Note NOTE:250655.1 - How to use the Automatic Database Diagnostic Monitor NOTE:223117.1 - Troubleshooting I/O Related Waits NOTE:1482811.1 - Best Practices: Proactively Avoiding Database and Query Performance Issues NOTE:1477599.1 - Best Practices: Proactive Data Collection for Performance Issues NOTE:34592.1 - WAITEVENT: "log file sync" Reference Note NOTE:413942.1 - How to Identify Which Latch is Associated with a "latch free" wait NOTE:433472.1 - OS Watcher For Windows (OSWFW) User Guide NOTE:1356828.1 - FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events NOTE:1363422.1 - Automatic Workload Repository (AWR) Reports - Main Information Sources NOTE:1377446.1 - * Troubleshooting Performance Issues NOTE:1376916.1 - Troubleshooting: 'Log file sync' Waits NOTE:155971.1 - Resolving Intense and "Random" Buffer Busy Wait Performance Problems NOTE:164768.1 - Troubleshooting: High CPU Utilization NOTE:228913.1 - Systemwide Tuning using STATSPACK Reports NOTE:754639.1 - How to Read Buffer Cache Advisory Section in AWR and Statspack Reports. NOTE:786554.1 - How to Read PGA Memory Advisory Section in AWR and Statspack Reports to Tune PGA_AGGREGATE_TARGET BUG:6904068 - HIGH CPU UTILIZATION DURING MUTEX WAIT EVENTS NOTE:1301503.1 - Troubleshooting: AWR Snapshot Collection Issues |
文档内容
适用于:Oracle Database - Enterprise Edition - 版本 10.2.0.1 和更高版本本文档所含信息适用于所有平台 目标本文旨在提供如何解释跟数据库性能问题息息相关的AWR信息。 需要注意的是生成 AWR Report 或访问 AWR 相关的视图,以及使用任何 AWR 相关的诊断信息,都需要额外的 Diagnostic Pack License。这包括生成 AWR/ADDM/ASH report,也包括当技术支持要求的生成上述报表时。 注意: Oracle Diagnostics Pack (以及 Oracle Tuning Pack) 只在企业版中提供。 Oracle® Database Licensing Information 最佳实践如何主动避免问题发生及做好诊断信息的收集有些问题是无法预见的,但大部分其它的问题如果及早发现一些征兆其实是可以避免的。同时,如果问题确实发生了,那么收集问题发生时的信息就非常重要。有关于如何主动避免问题及诊断信息的收集,请参见:
Document 1482811.1 Best Practices: Proactively Avoiding Database and Query Performance Issues
Document 1477599.1 Best Practices Around Data Collection For Performance Issues 提出问题、获取帮助并分享您的经验 您想要与其他 Oracle 客户、Oracle 员工及业内专家深入探讨吗? 解决方案对于数据库整体的性能问题,AWR的报告是一个非常有用的诊断工具。
Document 1363422.1 Automatic Workload Repository (AWR) Reports - Start Point
注:最好一开始我们从ADDM报告入手,因为对应时间段的ADDM报告往往已经指出了问题所在。
参见: Use of ADDM Reports alongside AWR
Interpretation在处理性能问题时,我们最关注的是数据库正在等待什么。
值得注意的wait events
诊断其他问题关于其他性能问题,请参照文档:
Document 1377446.1 Troubleshooting Performance Issues
使用ADDM的报告当分析性能问题时,除了AWR报告,我们还可以同时参照ADDM报告,对于潜在的性能问题,它同时提供了具体的解决方案建议。下面是从如下文档拿到的一个ADDM报告示例:
Note:250655.1How to use the Automatic Database Diagnostic Monitor:
DETAILED ADDM REPORT FOR TASK 'SCOTT_ADDM' WITH ID 5
---------------------------------------------------- Analysis Period: 17-NOV-2003 from 09:50:21 to 10:35:47 Database ID/Instance: 494687018/1 Snapshot Range: from 1 to 3 Database Time: 4215 seconds Average Database Load: 1.5 active sessions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FINDING 1: 65% impact (2734 seconds) ------------------------------------ PL/SQL execution consumed significant database time. RECOMMENDATION 1: SQL Tuning, 65% benefit (2734 seconds) ACTION: Tune the PL/SQL block with SQL_ID fjxa1vp3yhtmr. Refer to the "Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and Reference" RELEVANT OBJECT: SQL statement with SQL_ID fjxa1vp3yhtmr BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END; FINDING 2: 35% impact (1456 seconds) ------------------------------------ SQL statements consuming significant database time were found. RECOMMENDATION 1: SQL Tuning, 35% benefit (1456 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID gt9ahqgd5fmm2. RELEVANT OBJECT: SQL statement with SQL_ID gt9ahqgd5fmm2 and PLAN_HASH 547793521 UPDATE bigemp SET empno = ROWNUM FINDING 3: 20% impact (836 seconds) ----------------------------------- The throughput of the I/O subsystem was significantly lower than expected. RECOMMENDATION 1: Host Configuration, 20% benefit (836 seconds) ACTION: Consider increasing the throughput of the I/O subsystem. Oracle's recommended solution is to stripe all data file using the SAME methodology. You might also need to increase the number of disks for better performance. RECOMMENDATION 2: Host Configuration, 14% benefit (584 seconds) ACTION: The performance of file D:\ORACLE\ORADATA\V1010\UNDOTBS01.DBF was significantly worse than other files. If striping all files using the SAME methodology is not possible, consider striping this file over multiple disks. RELEVANT OBJECT: database file "D:\ORACLE\ORADATA\V1010\UNDOTBS01.DBF" SYMPTOMS THAT LED TO THE FINDING: Wait class "User I/O" was consuming significant database time. (34% impact [1450 seconds]) FINDING 4: 11% impact (447 seconds) ----------------------------------- Undo I/O was a significant portion (33%) of the total database I/O. NO RECOMMENDATIONS AVAILABLE SYMPTOMS THAT LED TO THE FINDING: The throughput of the I/O subsystem was significantly lower than expected. (20% impact [836 seconds]) Wait class "User I/O" was consuming significant database time. (34% impact [1450 seconds]) FINDING 5: 9.9% impact (416 seconds) ------------------------------------ Buffer cache writes due to small log files were consuming significant database time. RECOMMENDATION 1: DB Configuration, 9.9% benefit (416 seconds) ACTION: Increase the size of the log files to 796 M to hold at least 20 minutes of redo information.
其他的AWR参考文章当阅读AWR报告的其他部分时,可以参照下面的一些文档:
Document 786554.1 How to Read PGA Memory Advisory Section in AWR and Statspack Reports
Document 754639.1 How to Read Buffer Cache Advisory Section in AWR and Statspack Reports Document 1301503.1 Troubleshooting: AWR Snapshot Collection issues Document 1363422.1 Automatic Workload Repository (AWR) Reports - Start Point StatspackAWR报告取代了旧有的staspack及bstat/estat报告,下面的这些文档概述了如何阅读statspack报告: Additional information can be found in the following articles:
Document 94224.1 FAQ- Statspack Complete Reference
Document 394937.1 Statistics Package (STATSPACK) Guide Document 149113.1 Installing and Configuring StatsPack Package Document 149121.1 Gathering a StatsPack snapshot Document 228913.1 Systemwide Tuning using STATSPACK Reports
参考NOTE:1349387.1 - Troubleshooting 'cursor: pin S wait on X' waits.NOTE:6904068.8 - Bug 6904068 - High CPU usage when there are "cursor: pin S" waits NOTE:262687.1 - Using the DBMS_SQLTUNE package to Run the Sql Tuning Advisor NOTE:271196.1 - Automatic SQL Tuning and SQL Profiles NOTE:276103.1 - Performance Tuning Using Advisors and Manageability Features: AWR, ASH, ADDM and Sql Tuning Advisor NOTE:34405.1 - WAITEVENT: "buffer busy waits" Reference Note NOTE:250655.1 - How to use the Automatic Database Diagnostic Monitor NOTE:223117.1 - Troubleshooting I/O Related Waits NOTE:1482811.1 - Best Practices: Proactively Avoiding Database and Query Performance Issues NOTE:1477599.1 - Best Practices: Proactive Data Collection for Performance Issues NOTE:34592.1 - WAITEVENT: "log file sync" Reference Note NOTE:413942.1 - How to Identify Which Latch is Associated with a "latch free" wait NOTE:433472.1 - OS Watcher For Windows (OSWFW) User Guide NOTE:1356828.1 - FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events NOTE:1363422.1 - Automatic Workload Repository (AWR) Reports - Main Information Sources NOTE:1377446.1 - * Troubleshooting Performance Issues NOTE:1376916.1 - Troubleshooting: 'Log file sync' Waits NOTE:155971.1 - Resolving Intense and "Random" Buffer Busy Wait Performance Problems NOTE:164768.1 - Troubleshooting: High CPU Utilization NOTE:228913.1 - Systemwide Tuning using STATSPACK Reports NOTE:754639.1 - How to Read Buffer Cache Advisory Section in AWR and Statspack Reports. NOTE:786554.1 - How to Read PGA Memory Advisory Section in AWR and Statspack Reports to Tune PGA_AGGREGATE_TARGET BUG:6904068 - HIGH CPU UTILIZATION DURING MUTEX WAIT EVENTS NOTE:1301503.1 - Troubleshooting: AWR Snapshot Collection Issues |