Conquering Oracle Latch Contention

(Identifying, measuring, and resolving harmful latch contention)

Latest version available at http://www.orapub.com

2 Craig A. Shallahamer

©OraPub, Inc.

This page has been intentionally left blank.

Conquering Oracle Latch Contention 3

©OraPub, Inc.

Table of Contents

TABLE OF CONTENTS ............................................................................................................... 3

INTRODUCTION......................................................................................................................... 4

HOW TO LEAN ABOUT ORACLE LATCHING................................................................................... 4

THE PROCESS EXPLAINED...................................................................................................... 5

UNDERSTANDING THE GENERAL LATCHING ALGORITHM........................................ 6

TYPES OF ORACLE LOCKS ............................................................................................................. 6

THE ORACLE LATCH ..................................................................................................................... 7

THE GENERAL ORACLE LATCHING ALGORITHM........................................................................... 7

HOW MULTIPLE LATCHES ARE IMPLEMENTED ............................................................................. 8

HOW TIME IS RECORDED............................................................................................................... 9

HOW TO DETECT HARMFUL LATCH CONTENTION..................................................... 10

RESOLVING HARMFUL LATCH CONTENTION ............................................................... 12

CONCLUSION............................................................................................................................ 13

ABOUT THE AUTHOR.............................................................................................................. 13

REFERENCE ............................................................................................................................... 14

4 Craig A. Shallahamer

©OraPub, Inc.

Conquering Oracle Latch Contention

Craig A. Shallahamer (craig@orapub.com)

Original June 7, 2004

Version 1d June 23, 2004

Abstract

As Oracle server complexity, transaction throughput, and simultaneous usage all continue to

increase, latch contention can plague even the most experienced Oracle performance specialist.

This paper describes why latches exist, how they work, how to detect latching contention, and

how to resolve the contention. To demonstrate this process, the library cache latch will be used

throughout the paper as well as publicly available latching scripts.

Introduction

It’s really true. Even the most seasoned Oracle performance specialists shudder at the thought of

dealing with Oracle latch contention. I have heard some of the most respected Oracle performance

specialists say that once you get latch contention, there really isn’t a whole lot you can do. This is

absolutely wrong! Why do even the best performance specialist’s knees buckle in the face of

latch contention? Because it means understanding a great deal about not only latching, specific

Oracle architecture internals, and some queuing theory [PPM], but it also means taking risks like

suggesting bold application changes or implementing hidden instance parameters that few have

actually tried in a real production environment.

Over my years as a consultant and a teacher, I have found the best way to not only resolve but to

teach others how to resolve latch contention is to first understand Oracle’s general latching

algorithm and then understand the specific Oracle architecture component that the latch is related

to. So it’s a two-step educational process that breaks out into a seven-step contention resolution

strategy. In this paper, I’ll discuss the general latching algorithm and a supporting seven-step

process to identify, measure, and resolve latch contention. However, I will not discuss the Oracle

architecture internals related to each latch. There are many resources available, including my

Advanced Reactive Performance Management [RPM] class.

Unless specifically mentioned, all the tools and scripts mentioned and used in this paper are

available for free on OraPub’s web site, www.orapub.com. The tools are combined into a single

toolkit called the OraPub System Monitor or OSM for short. [OSM]

How To Lean About Oracle Latching

As I mentioned in the Introduction, I have found the best way to not only resolve but to teach

others how to resolve latch contention is to first learn about Oracle’s general latching algorithm

and then learn about the specific Oracle architecture area that is related to the latch. It’s kind of

Conquering Oracle Latch Contention 5

©OraPub, Inc.

like, once you learn the principle then you can apply it to the specific problem. Like honesty, once

you learn to live honestly, actually being honest in various situations just naturally occurs and

makes sense. Latching, in some ways, is just like this.

Latches protect Oracle memory structures. And while there are many Oracle memory structures,

and therefore many Oracle latches, they all operate under the same basic algorithm. So the first

step is learning about this algorithm. The second step is to learn about the memory structures.

Once you understand both the latching algorithm and the memory structure architecture, then the

solutions naturally come forth in those, all to infrequent, “ah ha” moments.

The Process Explained

There are seven steps to detecting, measuring, and resolving latch contention. The steps are

summarized below. Throughout the paper, I will explain each of the steps in more detail complete

with actual examples.

1. Understand the general latching algorithm. I’ve already mentioned this above and I’ll

detail the algorithm in the next section.

2. Detect latch contention. While it may be obvious to you, before you attempt to resolve latch

contention, make sure resolving latch contention is worth your time and will significantly

improve performance. The best way to detect latch contention is using a response time based

approach with its core based upon Oracle’s wait event interface. [RTA, SWA, RPM] For

example, if response time is unacceptable and an Oracle process or processes are waiting 90%

of the time for a latch, then it makes sense to spend your time resolving the latch contention.

But if that percentage is only 15%, then you would had better focus elsewhere. While you are

celebrating your 15% response time improvement, the users will be planning your demotion

because of the other 85%. I will discuss this in more detail later in the paper. (Not your

demotion, but detecting latch contention.)

3. Determine the latch. Once you know there is significant and harmful latch contention, you

will need to determine which specific latch. This may seem obvious, but depending on your

version of Oracle and if you are looking at performance interactively (i.e., in real time) or

historically, you may only know there is latch contention, but not the specific latch.

4. Understand the related kernel code. This is when it becomes important to understand what

the latch is actually protecting and why. For example, if you have library cache latch

contention, then you will need to know what the library cache is, how it works, and what you

can do to affect how it works. Understanding latching in general is good, but if you don’t

understand the underlying architecture, your attempts to resolve the contention will be nothing

more than a good guess. As a side note, you will also be able to quickly understand why

others’ recommendations could never solve the latch contention.

5. Understand the nature of the latch contention. Ask yourself two questions and how you

could affect the answer to the questions. The first question is, “Why is the latch held so

long?” And the second question is, “Why is the latch being requested so often?” These are

two very different questions that address two distinct yet closely related operations or the

nature of how the CPU subsystem and Oracle’s latches are working together.

6 Craig A. Shallahamer

©OraPub, Inc.

6. Devise multiple resolution strategies. Because of uptime requirements, response time

requirements, politics, and the list goes on and on, you will need to come up with multiple

ways to possibly resolve the latch contention. Hopefully one of your ideas will be able to be

implemented. Many times, latching contention solutions require unusual changes in the

system that can not or will not be allowed in your IS group. So you want to have as many

options and fallback plans as possible.

7. Take appropriate action to resolve. Finally…you have methodically worked through this

process and are ready to actually implement a change that will hopefully reduce the latch

contention. If you have followed these seven steps, you stand a very, very good chance of

improving response time.

Understanding The General Latching Algorithm

The first step to ultimately resolve latch contention is to understand Oracle’s general latching

algorithm. But even before that, it’s important to understand how Oracle uses locks and why it

uses locks, and then understanding what an Oracle latch is and why it is used. Then finally, we

are in a position to understand Oracle’s latching algorithm and more advanced topics like how

multiple latches are implemented. This section very quickly discusses each of these topics.

Types of Oracle Locks

While you never read or hear this in official Oracle Corporation presentations, I feel that Oracle

has three basic ways of protecting things. There are application locks, data dictionary locks, and

memory structure control.

Application Locks are under the control of an application developer. For example, if I type,

“lock table employee exclusive” and you type, “update employee set salary = salary * 0.75”

(which is not a nice thing to do by the way), then thankfully your operation will be

blocked…locked. Any DBA can observe this by looking at the v$lock view and also the wait

event views with an event name of “enqueue wait” of type TX (row or block related) or TM (table

related).

Data Dictionary Locks

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

转载于:http://blog.itpub.net/9599/viewspace-472888/