一篇分析诊断被"hang"住数据库的资料（Oracle Performance Diagnostic Gui...

Oracle Performance Diagnostic Guide

Hang/Locking

Version 3.1.0 January 13, 2009

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.

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

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

with the tool usage to Vickie.Carbonneau@oracle.com, Technical Advisor, Center of Expertise (CoE).

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.

In this step, we will collect data to verify the database hang is due to the database and not external to it. Note: Collect data when the database is

regardless of the instance that issued the command.

HANGANALYZE syntax:

3 Syntax Examples:

ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level <level>';

ORADEBUG hanganalyze <level>

ORADEBUG -g def hanganalyze <level>

10 Dump all processes (IGN state)

5 Level 4 + Dump all processes involved in wait chains (NLEAF state)

4 Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)

3 Level 2 + Dump only processes thought to be in a hang (IN_HANG state)

It is advisable not to use levels higher than 3 due to the potentially large number of trace files that

may be produced (and could overwhelm the I/O subsystem). Since HANGANALYZE will be mostly

used to diagnose "true" hangs, a level 3 will dump the processes that are involved in the hang

hung. To verify that hanganalyze is working on clustered environments, monitor

the size of the "diag" trace file in the background_dump_destination.

A system state is a set of process states for all processes on the instance when the dump is taken.

A system state dump is useful in determining the interaction between processes. A systemstate

ALTER SESSION SET EVENTS 'immediate trace name SYSTEMSTATE level <level>';

ORADEBUG dump systemstate <level>

ORADEBUG -g all dump systemstate <level>

The <level> sets the amount of additional information that will be extracted from the processes

found by SYSTEMSTATE dump based on the "STATE" of the node.

The levels are defined as follows: