My Oracle Support Banner

Best Practices: Proactive Data Collection for Performance Issues (Doc ID 1477599.1)

Last updated on MARCH 25, 2021

Applies to:

Oracle Database - Standard Edition - Version 10.1.0.2 and later
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database - Personal Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.

Purpose

This document describes a best practice methodology to ensure that sufficient performance data is pro-actively gathered enough at first occurrence of the problem to enable effective root cause determination. It can be used alongside the following document to both attempt to avoid problems and then if that is unavoidable, collect the information required for a quick diagnosis:

 

<Document 1482811.1> Best Practices: Proactively Avoiding Database and Query Performance issues
NOTE: These recommendations are best practices that apply to the MAJORITY of scenarios encountered by support. Each issue is different and specific additional diagnostic may be required in some circumstances to completely achieve a root cause diagnosis. It is not necessarily feasible to collect this targeted information up front for every issue since specific diagnostics required to resolve one particular issue may not be relevant in all cases. The goal is to provide a solid starting point that will collect enough for most issues to be resolved or to enable prompt recommendations for further tracing.

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Tuning.

Scope

This document is intended to advise database users on proactive best practices for the collection of diagnostic performance data 

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
 Ask Questions, Get Help, And Share Your Experiences With This Article
Scope
Details
 Executive Summary
 Methodology
 Top-Down approach:
 Data collection at Operating System (O/S) level.
 OSWatcher
 Data collection at Database level
 AWR
 ASH
 Establishing multiple baselines:
 Be Prepared!: Having the right tools already installed and running before a problem occurs
 HangFG
 SQLHC
 SQLTXPLAIN (SQLT)
 Deploying specialized tools for unstable environments:
 The LTOM Utility has been obsoleted and is no longer for use so the Document: 352363.1 now automatically redirects here as much of the functionality is now available within the Active Session History (ASH).
LTOM
 Procwatcher
 What to collect prior to an upgrade
 AWR baselines
 SQL Plan management Baselines
 Checklist of Proactive Best Practices
 Logging A Service Request
 What to do if no proactive collection has occurred?
 Performance Service Request Diagnostic Collection (SRDC) documents
 Discuss Proactive Avoidance!
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.