everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

Performance

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15
<< Previous

Chapter # 09

Next >>


 

Diagnosing Contention for Latches

 

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.

 

Questions:

Q: Describe a latch in the SGA memory?

Q: What does a latch protect?

Q: How do you diagnose contention for latches?

Q: How do you view the shared pool memory size?

Q: How do you view the library cache request latches hit ratio value?

Q: How do you view the redo allocation latch ratio value?

Q: How do you view the redo copy latch wait ratio value?

Q: How many types of latch request does Oracle have?

Q: Describe the V$LATCH dictionary view?

Q: Describe the V$LATCHEHOLDER view?

Q: Describe the V$LATCHNAME view?

Q: Calculate the shared pool latch ratio value?

Q: Calculate the copy wait ratio value?

Q: What does a process do when a latch is willing to wait for a request and does not get a latch?

Q: What does a process do when a latch is not willing to wait for a request and does not get a latch?

Q: What does the following SQL statement?

SQL> SELECT name, (1-(misses/gets))*100

AS "Ratio", sleeps
FROM v$latch
WHERE name in ('library cache', 'shared pool')
/

Q: What is an acceptable shared pool latch hit ratio?

Q: What action do you need to perform if the value of the following SQL statement is more than 1.

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')
/

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.