|
“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:
PRF009(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')
/
|