Performance

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:

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.

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, 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?

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