1. Query the V$FIXED_TABLE to determine the V$ views that provide latch information.
| a. Execute the following query:> select name from v$fixed_table where name like ‘V$LATCH%’; |
2. Remember that there are 14 levels of latches, numbered 0 – 13.
| a. Take a look at three common latches and their level by executing the following query:> select name, level# from v$latch where name in (‘cache buffers chain’,’library cache’,’redo allocation’); You may or may not see each of these latches listed depending upon the state and activity on your database. |
3. Each parent and child latch has one instance in X$KSLLT.
| a. Execute the following query to view a KSLLT structure for a latch:> select v$latch_parent.addr,v$latch_parent.level#,v$latch_parent.latch#, v$latch_parent.name from v$latch_parent,x$ksllt where v$latch_parent.addr = x$ksllt.addr; You should at least see information for the ‘latch wait list’ latch. However, your results will vary depending upon the state and activity of your database. |
4. Using the latch# from the previous query, you can query the X$KSLLD table to see the array of latch descriptors. One of these arrays is stored in every PGA.
| a. Execute the following query to view the array for the associated latch#:> select * from X$KSLLD where indx = <latch# from previous query>;
A given process can select from this table to view the latch array. |
Note: As you can see, output from the X$ tables is somewhat cryptic. However, knowledge of where to gather information regarding latches will help you to more effectively diagnose latch contention and communicate with Oracle support. The follow on lessons to this module will use the dynamic performance views and system tables for diagnosing and resolving latch contention.
5. Query the V$SYSTEM_PARAMETER to view the SPIN_COUNT and MAX_EXPONENTIAL_SLEEP parameters:
| a. Execute the following query:> select name from v$system_parameter where name like ‘%spin%’ or name like ‘%max%’;
Note: If you are running Oracle8i you will get no rows returned. That’s because each of these parameters are now hidden. b. Execute the following query using the X$KSPPI table which lists hidden parameters: > select ksppinm from x$ksppi where (ksppinm like ‘%spin%’ or ksppinm like ‘%max%’) and ksppinm like ‘\_%’ escape ‘\’ ; Also note the _MAX_SLEEPING_HOLDING_LATCH and _LATCH_WAIT_POSTING parameters. You can query the ksppidesc column to get a description of these parameters. |
6. Remember that some latches are held for long periods of time, while others are not.
| a. Execute the following query to view three latches that are held for long periods of time:> select name, level# from v$latch where name in (‘library cache’,’shared pool’,’library cache load lock’,’latch wait list’); Also notice the level numbers of these latches taking into account that most latches are in the range of 0 – 7. The latch wait list latch protects the linked list of waiters. |
7. Certain wait events are associated with latches. A session process may wait on a latch and the wait time will be updated in the V$SESSION_EVENT and
V$SYSTEM_EVENT dynamice performance views. V$SESSION_WAIT also provides event informaton.
| a. Execute the following query to view real time information on which sessions are currently waiting or have just waited for an event:> select sid, event, wait_time from v$session_wait order by wait_time, event; Latch Free will appear in the output if there were statistics for this event. b. Query V$SYSTEM_EVENT to view aggregate statistics for the latch free event: > select * from v$system_event where event = ‘latch free’; c. Query V$SESSION_EVENT to view statistics for the latch free event for each session: > select * from v$session_event where event = ‘latch free’; |
8. You can obtain information about specific latches by using both the V$SESSION_WAIT and V$LATCH views..
| a. Execute the following query to view three latches that are held for long periods of time:> select distinct l.name “Latch” from v$session_wait s, v$latch l where s.p2 = l.latch# and s.event = ‘latch free’; You may or may not get a result set from this query if there are no session latch free events. If so then execute the next query to get an idea of wait events: > select DISTINCT l.name “Latch”, p1, p2 Note: Remember that P1 is the latch address and P2 is the latch number. The DISTINCT operator is used because a process may hold more than one of the same latch. Run the same query without the DISTINCT operator and notice the result set will have multiple P1 values for the same latch. > select l.name “Latch”, p1, p2 |
9. Latch contention occurs when a latch is requested by several processes at the same time. The impact of latch contention can be assessed using the sleep histogram in V$LATCH.
| a. Execute the following query to derive information about latch sleeps:> select name, sleeps * sleeps / (misses – spin_gets ) impact from v$latch where sleeps > 0; b. The SLEEP columns in V$LATCH represent the sleep buckets in the histogram. Describe V$LATCH to view the sleep buckets: > desc v$latch Note: SPIN_GETS is also a sleep bucket. Only the first four buckets are used. |
10. You can also derive information about latches by creating a trace file.
| a. To create a trace file execute the following command:> alter session set events ‘immediate trace name latches level 10’
b. The trace file will be created in the directory that is specified by the parameter USER_DUMP_DEST. View this file and you will see information about sleeps and nowait times. c. If child latch#’s are listed in the trace file, you can query V$LATCH_CHILDREN to acquire additional information. |
