昨天有篇“db file sequential read”的介绍,还有一篇类似的:Resolving Issues Where Application Queries are Waiting Too Frequently for 'db file sequential read' Operations (文档 ID 1475825.1)
诊断“db file sequential read”的步骤:
简述:
低效的SQL会引起不同节点间非常多的块读。
问题确认:
花费在本地数据库的active时间非常明显。
仅一些session,查询或job变慢(不是整个数据库)。
“db file sequential read”等待是整个DB time中占比最大的组件。
“db file sequential read”等待操作不慢(IO的平均时间没超过标准IO性能(例如少于20毫秒))。
等待“db file sequential read”操作太频繁的应用查询
等待“db file sequential read”事件指的是一个session正在等待一次从磁盘读到内存的单块读以满足查询的要求。
假设完成一次IO操作的平均时间是正常的(例如单次IO花费时间少于20毫秒),那么相比于单次IO操作的时间,花费在“db file sequential read”的总时间必须降到与IO次数相匹配。
如果“db file sequential read”等待次数太多,单条SQL又极消耗资源,那么这些查询的确可能需要调优以选择一种更能接受的执行路径,减少资源消耗。
为了确定哪些查询等待“db file sequential read”最多,需要采集与AWR报告相同时间段的ASH报告。在报告中,查找等待次数最多的查询。可以与AWR报告关联起来,根据CPU,IO和SQL统计节中的buffer gets的标准测量,判断查询的总体性能。
一旦有问题的语句已经确认,可以参考Document 223117.1 Troubleshooting I/O-related waits的“Reduce the I/O requirements of the database by tuning SQL”节中的方法,使用其中的方法提高这些语句的性能。
如果相比于太多这种等待事件,IO确实存在问题,那么可以参考下面的细节:
Document 1476092.1 Troubleshooting IO Performance Problems Impacting Scattered Reads
Document 262687.1 How to use the Sql Tuning Advisor
Document 1195363.1 Database Performance and SQL
衡量正确性:
一旦已经尝试如上方法,对比最新的AWR与标准AWR(AWR是基线)。查找这种等待事件总体时间减少的百分比。如果仍有问题,需要重新分析这些问题,根据他们具体的现象定位具体的问题。
分享到:
相关推荐
Resolving Ethical Dilemmas: A Guide for Clinicians, Fifth Edition Now in its Fifth Edition, this respected reference helps readers tackle the common and often challenging ethical issues that affect ...
The Android Marketplace: Where We Are Now 22 Android Platform Differences 23 Android: A Next-Generation Platform 23 Free and Open Source 25 Familiar and Inexpensive Development Tools 25xii Contents ...
[BULK-OP-DB] – Bulk Operation Lock for Database [BULK-OP-LOG] – Bulk Operation Lock for Log Table Lock Sub-Resources: Full Table Lock (default) [UPD-STATS] – Update statistics Lock [COMPILE] – ...
involved in implementing a user story or resolving an issue. In this paper, we offer for the first time a comprehensive dataset for story points-based estimation that contains 23,313 issues from 16 ...
Resolving occlusion between virtual and real scenes for augmented reality applications
RELEASE NOTES FOR MICROSOFT(R) TCP/IP-32 FOR WINDOWS(TM) FOR WORKGROUPS 3.11 PLEASE READ THIS ENTIRE DOCUMENT General ------- This product is compatible with, and supported exclusively on, the ...
Best_Practice: Scale-Resolving Simulations in ANSYS CFD.pdf
As companies keep their existing hardware and operating systems for more years than ever before, the need to diagnose and repair problems is becoming ever more important for IT Pros and system ...
In the third and forth clusters the applications found in the Literature for the indoor thermal parameters investigation and outdoor thermal conditions calculation are described, while in the last one...
Normally, only the columns in the WHERE clause are helpful in determining useful indexes, but for a covering index, all columns must be included. If all columns needed for the query are in the index,...
Performance tuning is a challenging topic that focuses on resolving tough performance issues. In this book, you will explore the art of performance tuning from all perspectives using a variety of ...
Troubleshooting Oracle Performance, 2nd Edition is your systematic guide to diagnosing and resolving performance problems in database-backed applications involving Oracle's database engine. Christian ...
If you are working with Java or Java EE projects and you want to take advantage of Maven dependency management, then this book is ideal for you. This book is also particularly useful if you are a ...
Debugging: Designing for introspection and resolving issues Node in production: Deploying applications safely PART 3 WRITING MODULES Writing modules: Mastering what Node is all about
information needed for resolving bug: * Description * jclazz package version * JRE version used to run utils * Compiled Java class * Output files produced by utility (optional) * Expected ...
Assembly Versioning: By default, assembly version numbers are ignored when resolving type and member references. You can enable side-by-side versioning in the options dialog but it is suggested to ...
Spring MVC is a modern web application framework built upon the Spring Framework, and Spring Web Flow is a project that complements Spring MVC for building reusable web controller modules that ...
application of solutions to problems by individuals or organizations. There is a continuing need to provide a systematic means for compiling this information and making it available to the entire ...
Minimum displacements for cloth-obstacle penetration resolving
Case Study:Resolving High CPU Usage on Oracle Servers