Oracle Performance Diagnostic Guide (Version 3.20)
Hang/Locking
9/11/2012
Welcome to the Oracle Performance Diagnostic Guide This guide is intended to help you resolve query tuning, hang/locking, and slow database
issues. The guide is not an automated tool but rather seeks to show methodologies, techniques, common causes, and solutions to performance
problems.
Most of the guide is finished but portions of the content under the Hang/Locking tab is still under development.
Your feedback is very valuable to us - please email your comments to:
Vickie.Carbonneau@oracle.com
Contents
Hang/Locking > Identify the Issue > Overview
Recognize a Hang or Locking Issue
Clarify the Issue
Verify the Issue
Next Step - Data Collection
Hang/Locking > Identify the Issue >Data Collection
Gather Operating System (OS) Performance Data
Gather Hanganalyze and SystemState
Gather V$ View Data
Next Step - Analyze
Hang/Locking > Identify the Issue > Analysis
Verify OS Resource Usage
Verify the Database is Hung
Next Step - Determine a Cause
Would You Like to Stop and Log a Service Request?
Hang/Locking > Determine a Cause >Overview
Hang/Locking > Determine a Cause >Data Collection
"True" Database Hang
"Stuck" or "Locked" Sessions
Next Step - Analyze
Hang/Locking > Determine a Cause >Analysis
Identify Blocker and Waiter Data
"True" Database Hang
"Stuck" or "Locked" Sessions
Open a Service Request with Oracle Support Services
Give Us Your Feedback
Feedback
We look forward to your feedback. Please email any comments, suggestion to help improve this guide, or any issues that you have encountered with
the tool usage to Vickie.Carbonneau@oracle.com, Technical Advisor, Center of Expertise (CoE).
Oracle Performance Diagnostic Guide (Version 3.20) - Hang/Locking 09/11/2012
Page 1 of 51
Hang/Locking > Identify the Issue > Overview
To properly identify the issue we want to resolve, we must do three things:
● Recognize a query hang or locking issue
● Clarify the details surrounding the issue
● Verify that the issue is indeed the problem.
Recognize a Hang or Locking Issue
What is a Hang or Locking issue?
A "true" database hang issue can manifest itself as:
● A database that is no longer allowing users to connect
● A database that is no longer performing work
● Select 1 from dual does not produce output
● Create table does not complete
A locking issue can manifest itself as:
● One or more sessions that have completely stopped functioning
You might have identified the hang or lock from:
● benchmarking/testing
● user complaints
● systemstate or hanganalyze showing stuck sessions
● a query appearing to hang
● session consuming a large amount of CPU
● ORA-60 errors appearing in the alert log
These problems might appear after:
● schema changes
● changes in database parameters
● changes in application
● database upgrades
Clarify the Issue
A clear problem statement is critical. You need to be clear on exactly what the problem is. It may be that in
subsequent phases of working through the issue, the real problem becomes clearer and you have to
revisit and re-clarify the issue.
To clarify the issue, you must know as much as possible of the following:
● The affected users
● The sequence of events leading up to the problem
● Where/how it was noticed
● The significance of the problem
● What IS working
● What is the expected or acceptable result
● What have you done to try to resolve the problem
As an example:
● Many sessions do not appear to be completing the requests that were made
● It was noticed by end users.
Notes
● ODM Reference: Identify the Issue
How-To
● How to Identify Resource Intensive
SQL for Tuning
Case Studies
● Resolving High CPU usage in Oracle
Servers
Oracle Performance Diagnostic Guide (Version 3.20) - Hang/Locking 09/11/2012
Page 2 of 51
of 51
免费下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论