Hands-On 09 (Diagnosing Contention for Latches)

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 09 (Diagnosing Contention for Latches)
-- Diagnosing Contention for latches
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
COL name FORMAT a40
col parameter format a40
col username format a10
pause

--Start

CLEAR SCR
-- 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.

-- We will learn about different types of latch requests.

-- Connect to SQLPlus as the SYSTEM/MANAGER user.

pause

CONNECT system/manager AS SYSDBA

pause

CLEAR SCR

-- Important notes to remember:

-- 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.

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

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

pause
pause

CLEAR SCR
-- Query the Library Cache and Shared Pool request latches
-- Hit Ratio from the V$LATCH dictionary view.

pause

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. 

pause

CLEAR SCR
-- Query the Redo Allocation Latch and the Redo Copy Latch wait ratios.

pause

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.

pause


CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause