iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Performance

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 |

 

Lesson 09

"I can't give you a sure-fire formula for success, but I can give you a formula for failure: try to please everybody all the time." Herbert Bayard Swope (1882 - 1958)

 

Read first then play the video:

   PRF-VIDEO -Diagnosing Contention for Latches

   

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.

 

"Dreaming permits each and every one of us to be quietly and safely insane every night of our lives." William Dement

 

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