Diagnosing Contention for Latches

Leave a Comment:

For video click this link:

http://www.is123.org/iselfschooling-swf/prf/PRF009.htm

**************************************************************************************************************

Introduction

As a DBA, you are responsible for diagnosing any latch contentions in the Shared Pool area in case of performance problems. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

• Diagnosing contention for latches
• Viewing the Shared Pool memory size
• Viewing the Library Cache Hit Ratio
• Viewing the Redo Allocation Latch ratio
• Viewing the Redo Copy Latch wait ratio
• Types of latch requests
• Willing to wait request
• Immediate Request
• Using the V$LATCH dictionary view
• Using the V$LATCHHOLDER view
• Using the V$LATCH view
• Using the V$LATCHNAME view
• Calculating the Shared Pool Latch Hit Ratio
• Calculating the Redo Allocation Latch ratio
• Calculating the Copy Wait Ratio

Hands-on
In this exercise you will learn how to: diagnose contention for latches, query the Shared Pool and Library Cache Hit Ratio, and query the Redo Allocation Latch and the Redo Copy Latch wait ratios. You will learn about different types of latch requests.

Connect to SQLPlus as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA

You use the V$LATCH dictionary view to query all active latches that your database is currently using, the V$LATCHNAME view to show a latch name with its associated latch number, and the V$LATCHHOLDER view to query the latches that are waiting.

Latches:
You should think of a latch as a permission that can be given to one server process at a time. Latches protect shared memory allocation, and also protect shared data structures in the SGA.

Oracle has two different types of latch requests: willing to wait or immediate.

Willing to wait request

In the willing to wait request, the process waits briefly and then goes to sleep. And then, it requests the latch again.

Immediate request

In the immediate request, if the process cannot obtain the latch requested in the immediate mode, it does not wait and does other jobs when it is finished, then it attempts to obtain the latches again.

Latches Hit Ratio

Query the Library Cache and Shared Pool request latches Hit Ratio from the V$LATCH dictionary view.
SQL> SELECT name, (1-(misses/gets))*100

AS “Ratio”, sleeps
FROM v$latch
WHERE name in (‘library cache’, ’shared pool’)
/
The ratio must be above 99 percent. For example, if the Shared Pool latch Hit Ratio is less than 99 percent, it means that you have contention for the Shared Pool latch, and indicates that you may need to tune the application. Or the application cursor cache may be too small, or the cursors may have been closed too soon explicitly.

If there is a problem in the Library Cache Latch then it means that unshared SQL, reparsed sharable SQL, and an undersized Library Cache contributed to the Library Cache Latch contention. Consider using bind variables in the application, or increase the Shared Pool size.

Query the Redo Allocation Latch and the Redo Copy Latch wait ratios.
SQL> SELECT h.pid, n.name, (l.misses/l.gets)*100 wait_ratio
FROM v$latchholder h, v$latchname n, v$latch l
WHERE h.laddr = l.addr
AND l.latch# = n.latch#
AND n.name in (‘redo allocation’, ‘redo copy’)
/
Notice that if there was an output and the wait ratio was more than 1, there is a problem. Then, you will need to increase the Redo Log Buffer size.

**************************************************************************************************************