【MOS】Troubleshooting Performance Issues (文档 ID 1377446.1)

2023-11-11 22:30

本文主要是介绍【MOS】Troubleshooting Performance Issues (文档 ID 1377446.1),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

【MOS】Troubleshooting Performance Issues (文档 ID 1377446.1)
In this Document
Purpose
  Best Practices
  Pro-Active Problem Avoidance and Diagnostic Collection
  Performance Service Request Diagnostic Collection (SRDC) documents
Troubleshooting Steps
  General Performance
  AWR Generation
  AWR Interpretation
  Buffer Busy  Contention
  Cache Buffers Chains Latch Contention
  Configuration Parameters
  Connection Timeouts: Errors ORA-3135/ORA-3136
  Deadlocks: Error ORA-00060
  Disk I/O
  Enqueue/Locks
  High Numbers of Child Cursors/High Cursor Version Counts
  Hung database
  Latches
  Library Cache
  Log File Sync
  Mutex Contention
  Operating System (OS)
  Paging/Memory
  Slow Database
  SQL Issue Diagnosis Using DBMS_SQLDIAG
  SQL Tuning
  SYSAUX Tablespace Issues
  TKProf Runtime Errors
  Upgrade
  Virtual Circuit Waits
  'WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!'
  Community Discussions
References


APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database - Standard Edition - Version 10.1.0.2 and later
Oracle Database - Personal Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.

PURPOSE

This article provides a central point for Performance Troubleshooting advice.

You can access categorised Troubleshooting information via the following:

Document 1543445.2 Troubleshooting Assistant: Troubleshooting Performance Issues
Document 1542678.2 Troubleshooting Assistant: SQL Performance Issues

This articles supersedes :

Document 106285.1 TROUBLESHOOTING GUIDE: Common Performance Tuning Issues (ARCHIVED)

Best Practices

Pro-Active Problem Avoidance and Diagnostic Collection

Although some problems may be unforeseen, in many cases problems may be avoidable if signs are detected early enough. Additionally, if an issue does occur, it is no use collecting information about that issue after the event. For information on suggested preparations to avoid issues and in case diagnostics are required, see:

Document 1482811.1 Best Practices: Proactively Avoiding Database and Query Performance Issues
Document 1477599.1 Best Practices Around Data Collection For Performance Issues

Performance Service Request Diagnostic Collection (SRDC) documents

Service Request Data Collection (SRDC) documents have been specially designed to provide the reader with the necessary instructions to provide a step by step guide to collecting information for a various common Database Performance Issues.

Document 1938786.1 List of Available Database Performance Related SRDC Documents

TROUBLESHOOTING STEPS

General Performance

The following articles can help with the interpretation of diagnostics to troubleshoot various issues:

Document 1362329.1 How To Investigate Slow Database Performance Issues
Document 390374.1 Oracle Performance Diagnostic Guide (OPDG)

AWR Generation

To troubleshoot the failure to collect AWR snapshots or reports, See:

Document 1301503.1 Troubleshooting: AWR Snapshot Collection issues 
Document 1363422.1 Automatic Workload Repository (AWR) Reports - Start Point

AWR Interpretation

How to troubleshoot performance issues using AWR snapshots or reports, See:

Document 1359094.1 FAQ: How to Use AWR reports to Diagnose Database Performance Issues 

Buffer Busy  Contention

To troubleshoot issues where Buffers have become Busy due to concurrent access, see:

Document 155971.1 Resolving Intense and "Random" Buffer Busy Wait Performance Problems:

Cache Buffers Chains Latch Contention

To troubleshoot issues caused by contention due to multiple sessions waiting to read the same block, see:

Document 1342917.1 Troubleshooting 'latch: cache buffers chains' Wait Contention

Configuration Parameters

Database parameters are designed so that the default is appropriate for the majority of cases. The following article discusses Optimizer Specific Parameters:

Document 1507909.1 What does it Mean if Disabling an Optimizer Specific Parameter Avoids a Performance Problem Detected After an Upgrade?

Connection Timeouts: Errors ORA-3135/ORA-3136

To troubleshoot issues caused by Connection Timeouts when errors such as ORA-3135 or ORA-3136 are seen, see:

Document 730066.1 Troubleshooting ORA-3135/ORA-3136 Connection Timeouts Errors - Database Diagnostics

Deadlocks: Error ORA-00060

For issues related to Deadlocks,refer to :

Document 60.1 Troubleshooting Assistant: Oracle Database ORA-00060 Errors on Single Instance (Non-RAC) Diagnosing Using Deadlock Graphs in ORA-00060 Trace Files
Document 1509919.1 Master Note for Database Error ORA-00060

Disk I/O

For issues related to Disk Input/Ouput (I/O),refer to :

Document 223117.1 Troubleshooting I/O-related waits 
Document 1275596.1 How to Tell if the IO of the Database is Slow

Enqueue/Locks

For locking issues refer to :

Document 1392319.1 Master Note: Locks, Enqueues and Deadlocks

Document 15476.1 FAQ about Detecting and Resolving Locking Conflicts

 

High Numbers of Child Cursors/High Cursor Version Counts

When a SQL statement is executed, Oracle will try to match that statement up with an existing identical statement that has already been executed and attempt to reuse the stored parsed representation of it (which is stored within a cursor structure in the library cache). If the existing information in the cursor for that statement cannot be used, a new version of that statement will be created and used instead. If lots of versions of a particular statement are created, Oracle has to search through the versions to determine which (if any) can be shared. In extreme circumstances, this can cause mutex contention and other issue that degrade the database performance. In order to troubleshoot these issues and determine the cause, see:

Document 296377.1 Troubleshooting: High Version Count Issues

Hung database

To troubleshoot cases where the database appears to have hung, refer to :

Document 1378583.1 Troubleshooting Database Hang Issues 
Document 452358.1 How to Collect Diagnostics for Database Hanging Issues


Latches

To troubleshoot issues where there is contention on 'cache buffers chains' latches see:

Document 1342917.1 Troubleshooting 'latch: cache buffers chains' Wait Contention

Library Cache

For issues related to contention in the Library Cache

Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention

Log File Sync

To troubleshoot issues where 'log file sync' is a primary cause of session wait activity, see:

Document 1376916.1 Troubleshooting: "Log File Sync" Waits 


Mutex Contention

For issues related to Mutex contention, refer to :

Document 1377998.1 Troubleshooting: Waits for Mutex Type Events

Document 1349387.1 Troubleshooting 'cursor: pin S wait on X' waits
Document 1357946.1 Troubleshooting 'library cache: mutex X' waits.

Document 1356828.1 FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events

Operating System (OS)

To troubleshoot Operating System (OS) related issues see:

Document 352648.1 How to Diagnose High CPU Usage Problems to the Module Level
Document 148176.1 Diagnosing hardware configuration induced performance problems

Paging/Memory

If heavy paging activity is seen and the database appears to be running more slowly than normal, refer to :

Document 1195174.1 Heavy Paging Causes Slow Database Performance

Slow Database

To troubleshoot occasions where the database appears to be running more slowly than normal, refer to :

Document 1362329.1 How To Investigate Slow Database Performance Issues

SQL Issue Diagnosis Using DBMS_SQLDIAG

An increasing number of issues can be detected, diagnosed and avoided using the DBMS_SQLDIAG package. For details see:

Document 1509192.1 How To Use DBMS_SQLDIAG To Diagnose Various Query Issues

SQL Tuning

For issues with individual queries, refer to the following troubleshooting documents:

Document 742112.1 * Query Performance Degradation - Recommended Actions 
Document 745216.1 * Query Performance Degradation - Upgrade Related - Recommended Actions

Document 179668.1 * TROUBLESHOOTING: Tuning Slow Running Queries 

Document 33089.1 * TROUBLESHOOTING: Possible Causes of Poor SQL Performance 
Document 372431.1 * TROUBLESHOOTING: Tuning a New Query 
Document 122812.1 * TROUBLESHOOTING: Tuning Queries That Cannot be Modified 
Document 163563.1 * TROUBLESHOOTING: Advanced Query Tuning

Document 1386036.1 How To Diagnose Issues Where Plans Are Different From Different Clients

SYSAUX Tablespace Issues

Document 1399365.1 Troubleshooting Issues with the SYSAUX Tablespace

TKProf Runtime Errors

Document 1383275.1 Troubleshooting TKProf Runtime Errors

Upgrade

To troubleshoot issues encountered after upgrading a database see:

Document 1528847.1 Troubleshooting: Avoiding and Resolving Database Performance Related Issues After Upgrade
Document 160089.1 TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance 


For advice on how to avoid such issues before upgrading, see:

Document 1482811.1 Best Practices: Proactively Avoiding Database and Query Performance Issues
Document 785351.1 Oracle 11gR2 Upgrade Companion

Document 1645862.1 Things to Consider Before Upgrading to 11.2.0.4 to Avoid Poor Performance or Wrong Results 
Document 1392633.1 Things to Consider Before Upgrading to 11.2.0.3 to Avoid Poor Performance or Wrong Results 
Document 1320966.1 Things to Consider Before Upgrading to 11.2.0.2 to Avoid Poor Performance or Wrong Results

Virtual Circuit Waits

Document 1415999.1 Troubleshooting: Virtual Circuit Waits

'WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!'

To troubleshoot issues when the database detects that a waiter has waited for a resource for longer than a particular threshold and he message "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" appears in the alert log, See:

Document 278316.1 Troubleshooting: "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!"


Community Discussions

Still have questions? Use the communities window below to search for similar discussions or start a new discussion on this subject. (Window is the live community not a screenshot)

Click here to open in main browser window

..........................................................................................................................................................................................................................................................................................................

● 本文来自于MOS转载文章,(文档 ID  1377446.1)

● 本文在itpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● QQ群:230161599  微信群:私聊

● 小麦苗分享的其它资料:http://blog.itpub.net/26736162/viewspace-1624453/

● 小麦苗云盘地址http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群: 230161599   微信群:私聊

● 联系我请加QQ好友(642808185),注明添加缘由

版权所有,欢迎分享本文,转载请保留出处

..........................................................................................................................................................................................................................................................................................................

手机长按下图识别二维码或微信客户端扫描下边的二维码来关注小麦苗的微信公众号:xiaomaimiaolhr,免费学习最实用的数据库技术。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2129427/,如需转载,请注明出处,否则将追究法律责任。

这篇关于【MOS】Troubleshooting Performance Issues (文档 ID 1377446.1)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/393252

相关文章

使用Python从PPT文档中提取图片和图片信息(如坐标、宽度和高度等)

《使用Python从PPT文档中提取图片和图片信息(如坐标、宽度和高度等)》PPT是一种高效的信息展示工具,广泛应用于教育、商务和设计等多个领域,PPT文档中常常包含丰富的图片内容,这些图片不仅提升了... 目录一、引言二、环境与工具三、python 提取PPT背景图片3.1 提取幻灯片背景图片3.2 提取

Android实现在线预览office文档的示例详解

《Android实现在线预览office文档的示例详解》在移动端展示在线Office文档(如Word、Excel、PPT)是一项常见需求,这篇文章为大家重点介绍了两种方案的实现方法,希望对大家有一定的... 目录一、项目概述二、相关技术知识三、实现思路3.1 方案一:WebView + Office Onl

Python实现word文档内容智能提取以及合成

《Python实现word文档内容智能提取以及合成》这篇文章主要为大家详细介绍了如何使用Python实现从10个左右的docx文档中抽取内容,再调整语言风格后生成新的文档,感兴趣的小伙伴可以了解一下... 目录核心思路技术路径实现步骤阶段一:准备工作阶段二:内容提取 (python 脚本)阶段三:语言风格调

使用Java将DOCX文档解析为Markdown文档的代码实现

《使用Java将DOCX文档解析为Markdown文档的代码实现》在现代文档处理中,Markdown(MD)因其简洁的语法和良好的可读性,逐渐成为开发者、技术写作者和内容创作者的首选格式,然而,许多文... 目录引言1. 工具和库介绍2. 安装依赖库3. 使用Apache POI解析DOCX文档4. 将解析

Java利用docx4j+Freemarker生成word文档

《Java利用docx4j+Freemarker生成word文档》这篇文章主要为大家详细介绍了Java如何利用docx4j+Freemarker生成word文档,文中的示例代码讲解详细,感兴趣的小伙伴... 目录技术方案maven依赖创建模板文件实现代码技术方案Java 1.8 + docx4j + Fr

使用C#代码在PDF文档中添加、删除和替换图片

《使用C#代码在PDF文档中添加、删除和替换图片》在当今数字化文档处理场景中,动态操作PDF文档中的图像已成为企业级应用开发的核心需求之一,本文将介绍如何在.NET平台使用C#代码在PDF文档中添加、... 目录引言用C#添加图片到PDF文档用C#删除PDF文档中的图片用C#替换PDF文档中的图片引言在当

详解C#如何提取PDF文档中的图片

《详解C#如何提取PDF文档中的图片》提取图片可以将这些图像资源进行单独保存,方便后续在不同的项目中使用,下面我们就来看看如何使用C#通过代码从PDF文档中提取图片吧... 当 PDF 文件中包含有价值的图片,如艺术画作、设计素材、报告图表等,提取图片可以将这些图像资源进行单独保存,方便后续在不同的项目中使

mysql数据库重置表主键id的实现

《mysql数据库重置表主键id的实现》在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,本文主要介绍了mysql数据库重置表主键id的实现,具有一定的参考价值,感兴趣的可以了... 目录关键语法演示案例在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,当我们

Python实现合并与拆分多个PDF文档中的指定页

《Python实现合并与拆分多个PDF文档中的指定页》这篇文章主要为大家详细介绍了如何使用Python实现将多个PDF文档中的指定页合并生成新的PDF以及拆分PDF,感兴趣的小伙伴可以参考一下... 安装所需要的库pip install PyPDF2 -i https://pypi.tuna.tsingh

Python批量调整Word文档中的字体、段落间距及格式

《Python批量调整Word文档中的字体、段落间距及格式》这篇文章主要为大家详细介绍了如何使用Python的docx库来批量处理Word文档,包括设置首行缩进、字体、字号、行间距、段落对齐方式等,需... 目录关键代码一级标题设置  正文设置完整代码运行结果最近关于批处理格式的问题我查了很多资料,但是都没