Forum Stats

  • 3,826,800 Users
  • 2,260,708 Discussions
  • 7,897,074 Comments

Discussions

latches and locks - an example?

OracleGuy777
OracleGuy777 Member Posts: 623
edited Sep 2, 2008 3:22PM in General Database Discussions
hi guys,

I keep coming across these two terms, and I am trying to conceptualise them. I understand why locks are needed on resources (this to me means tables, rows, indexes, sequences etc - maybe something else??). But I am not really sure how latches and locks fit in together. I have gathered the following from googling around.

LATCHES:

Provide only exclusive access to protected data structures
Request are not queued, if a request fails, process may try later
Simple data structure
Protect resources that are briefly needed (LRU list)
Very efficient

LOCKS:

Allow serialized access to some resources
Requests for locks are queued and serviced in order
Complex data structure that is further protected by latch
Protect resources needed for a longer time (e.g. tables)
Less efficient


If someone could give me an easy example for say a very simple table and show how the lock and latch will interact for a query on that table, it would help me a lot.

Thanks.

Best Answer

Answers

  • 587671
    587671 Member Posts: 768
    This might be of use

    What is Latch ?

    A mechanism to protect shared data structures in the System Global Area.
    For Example: latches protect the list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache.

    A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures.


    During DB performance we will see LATCH event ...so what is latch event and how many types of latch events ?

    A latch is a low-level internal lock used by Oracle to protect memory structures.

    The latch free event is updated when a server process attempts to get a latch, and the latch is unavailable on the first attempt.

    Most Popular latch wait event are ...



    1. Latch: library cache or Latch: shared pool

    Below is Possible causes for above both latch events.

    1. Lack of statement reuse
    2. Statements not using bind variables
    3. Insufficient size of application cursor cache
    4. Cursors closed explicitly after each execution
    5. Frequent logon/logoffs
    6. Underlying object structure being modified (for example truncate)
    7. Shared pool too small

    Below is Possible suggestion for aviod above both latch events.

    1. Increase SHARED_POOL_SIZE parameter value.
    2. Modify Frontend application to use BIND VARIABLE
    3. Use CURSOR_SHARING='force' (for temporary basis)


    2. Latch: cache buffers lru chain

    Possible Causes

    1. Inefficient SQL that accesses incorrect indexes iteratively (large index range scans) or many full table scans.
    2. DBWR not keeping up with the dirty workload; hence, foreground process spends longer holding the latch looking for a free buffer
    3. Cache may be too small

    Possible Suggestion

    1. Look for: Statements with very high logical I/O or physical I/O, using unselective indexes
    2. Increase DB_CACHE_SIZE parameter value.
    3. The cache buffers lru chain latches protect the lists of buffers in the cache. When adding, moving, or removing a buffer from a list, a latch must be obtained.

    For symmetric multiprocessor (SMP) systems, Oracle automatically sets the number of LRU latches to a value equal to one half the number of CPUs on the system. For non-SMP systems, one LRU latch is sufficient.

    Contention for the LRU latch can impede performance on SMP machines with a large number of CPUs. LRU latch contention is detected by querying V$LATCH, V$SESSION_EVENT, and V$SYSTEM_EVENT. To avoid contention, consider tuning the application, bypassing the buffer cache for DSS jobs, or redesigning the application.


    Latch: cache buffers chains

    Possible Causes

    1. Repeated access to a block (or small number of blocks), known as a hot block
    2. From AskTom:

    Contention for these latches can be caused by:

    - Very long buffer chains.
    - very very heavy access to the same blocks.

    Possible Suggestion

    1. From AskTom:
    When I see this, I try to see what SQL the waiters are trying to execute. Many times,
    what I find, is they are all running the same query for the same data (hot blocks). If
    you find such a query -- typically it indicates a query that might need to be tuned (to
    access less blocks hence avoiding the collisions).

    If it is long buffer chains, you can use multiple buffer pools to spread things out. You
    can use DB_BLOCK_LRU_LATCHES to increase the number of latches. You can use both
    together.

    The cache buffers chains latches are used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache. Contention on this latch usually means that there is a block that is greatly contended for (known as a hot block).

    To identify the heavily accessed buffer chain, and hence the contended for block, look at latch statistics for the cache buffers chains latches using the view V$LATCH_CHILDREN. If there is a specific cache buffers chains child latch that has many more GETS, MISSES, and SLEEPS when compared with the other child latches, then this is the contended for child latch.

    This latch has a memory address, identified by the ADDR column. Use the value in the ADDR column joined with the X$BH table to identify the blocks protected by this latch. For example, given the address (V$LATCH_CHILDREN.ADDR) of a heavily contended latch, this queries the file and block numbers:

    SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCH
    FROM X$BH
    WHERE HLADDR = 'address of latch'
    ORDER BY TCH;

    X$BH.TCH is a touch count for the buffer. A high value for X$BH.TCH indicates a hot block.

    Many blocks are protected by each latch. One of these buffers will probably be the hot block. Any block with a high TCH value is a potential hot block. Perform this query a number of times, and identify the block that consistently appears in the output. After you have identified the hot block, query DBA_EXTENTS using the file number and block number, to identify the segment.

    After you have identified the hot block, you can identify the segment it belongs to with the following query:

    SELECT OBJECT_NAME, SUBOBJECT_NAME
    FROM DBA_OBJECTS
    WHERE DATA_OBJECT_ID = &obj;

    In the query, &obj is the value of the OBJ column in the previous query on X$BH.


    5. Latch: row cache objects
  • 247514
    247514 Member Posts: 10,875 Bronze Trophy
    I suggest you read metalink doc
    What are Latches and What Causes Latch Contention
    Doc ID: Note:22908.1

    It has very good explanation of latch and cause of latch contention.
    247514
  • OracleGuy777
    OracleGuy777 Member Posts: 623
    That is great.

    I looked at the document, and it has a date of 18 May 2004. It does not say which version of Oracle it relates to. Is that normal for metalink documents?

    Also, is that stuff copyrighted? If I was to copy and paste say a large chunk of it, would I be breaking the law?

    thanks
  • Fco Munoz Alvarez
    Fco Munoz Alvarez Member Posts: 926
    edited Sep 2, 2008 5:29AM
    Hi OracleGuy777,

    Regarding Copyright, yes. Like Nicolas explain me 2 times, is ilegal to cut/past part or a full document from metalink, you can lose your support from Oracle. For more information refer to the bottom of Metalink page link "Legal Notices and Terms of Use".

    The Note 22908.1 What are Latches and What Causes Latch Contention, is valid for all Oracle Versions.

    Also take a look on the following metalink notes:

    - How To Identify a Hot Block Within The Database Buffer Cache, Note 163424.1
    - FAQ about Detecting and Resolving Locking Conflicts, Note 15476.1
    - Diagnosing Database Hanging Issues, Note 61552.1
    - LIBRARY CACHE LOCK, PIN AND LOAD LOCK, Note 444561.1
    - RDBPROD: How to Determine What Process is Causing Another to Stall, Note 76633.1

    Best Regards and I hope this will help you :)

    Francisco Munoz Alvarez
    www.oraclenz.com
    Fco Munoz Alvarez
  • Hi,

    Your Welcome.

    Here I found two excellent links:

    - [Data Concurrency and Consistency|http://www.acs.ilstu.edu/docs/Oracle/server.101/b10743/consist.htm]

    - http://tonguc.wordpress.com/2007/01/02/oracle-concepts-and-architecture-part-1/

    Regards,

    Francisco Munoz Alvarez
    www.oraclenz.com
    Fco Munoz Alvarez
  • OracleGuy777
    OracleGuy777 Member Posts: 623
    >
    - http://tonguc.wordpress.com/2007/01/02/oracle-concepts-and-architecture-part-1/
    wow, that is a GREAT link. It beautifully compares the two near the bottom!

    feel free to post again and I will mark your answer as Correct (I accidentally clicked on Helpful)

    many thanks for this great link
  • Fco Munoz Alvarez
    Fco Munoz Alvarez Member Posts: 926
    Answer ✓
    Dear Oracleguy777,

    Your Welcome any time, and yes this article from H.Tonguç YIlmaz is excelent.

    Cheers :)

    Francisco Munoz Alvarez
    www.oraclenz.com
  • 426850
    426850 Member Posts: 1,242
    Oracleguy777, Munoz thanks for your kind words.

    But let me re-mention that all credits here goes to Mr. Richmond Shee, Mr. Kirtikumar Deshpande and Mr. K. Gopalakrishnan, the authors of the great book I referenced at my post.

    Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning
    http://tinyurl.com/6ypufh

    Best regards.
    426850
  • OracleGuy777
    OracleGuy777 Member Posts: 623
    But let me re-mention that all credits here goes to Mr. Richmond Shee, Mr. Kirtikumar Deshpande and Mr. K. Gopalakrishnan, the authors of the great book I referenced at my post.
    Modesty! :)
This discussion has been closed.