1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Understanding Latches in Oracle

Discussion in 'Other Development Tools' started by simply_dba, Mar 10, 2009.

  1. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    One of most imortant structures of Oracle Architectures is the Oracle Latching mechanism. from my experiance I have seen that a major portion of Oracle Performance tuning revolves round the latching mechanism of Oracle. In this article I will try explain this mechanism.
    Types of Locks
    Before we dwelve into to the deep of latches, first we need to see what are the types of locks in Oracle.

    Application Locks
    These are the locks which are used by the application developers.For example the statement
    Code (Text):
    Select name,address from emp for update;
     
    is such type of lock and when this statement is executed, any other user is unable to perform any dml/ddl on the table except query.

    Data Dictionary Locks
    These are the locks aquired when we execute some kind of DDL.For example, When we create a table,the data dictionary must be updated with the details.So the tables like tab$,col$ have to locked just like above.

    Memory Structure locks or Latches
    In a quite similer way as to above, we need to lock memory based structures from access by other processes while a process is reading or executing it. These not quite "Locks" , but as these are protecting the memory structures from accidental modification, these are known as "Latches". Unlike the Locks and Enqueues, they do not maintain any order while allocating to processes.

    Oracle Latch can be defined as protection structures which ensure serial execution of the related code thereby ensuring that Oracle cache is not corrupted resulting in the corruption of the data.

    Latches surround the particluer code and ensure that they are accessed by one and only one process, even if it is doing only read. For example, the latch that protects the library cache kernel code is called the library cache latch.Before the execution of the library cache kernel code , the process must acquire the library cache latch and keep it acquired until the execution of the code.

    The Latching Algorithm.
    The latches are requested by the processes in two ways. The "Immediate Mode" and the "Willing to Wait Mode"

    Immediate Mode
    In this mode, a process could ask for a latch and if it fails to acquire it, the control is returned to the process.

    Willing to Wait Mode.
    In this mode, a process could ask for a latch and if it fails to acquire it, it keeps on requesting it for a specified number of times (this is called spinning on the latch and the number of spins is given by the now hidden parameter _spin_count) and if it still fails acquire it, goes to a sleep, wakes up after certain interval and repeats the whole process (Spin and sleep).This process continues until the process acquires the latch or the user cancels the operation.

    The interesting thing to note here is the timing of this whole process. When a process is spinning on a latch, it is consuming CPU and while it is sleeping, it is idle.Accordingly, when the process is spinning, the timing is recorded as the CPU time and when it is sleeping , it is recorded as a wait event,i.e., the latch free event.So, whenever a latch free event occurs, we know that the process has gone to sleep and it has consumed some CPU time while spinning.It can be clearly seen from the this fact that increasing the value of the parameter _spin_count is actually bad, it makes the process consume more cpu time and thereby increasing the cpu bottleneck.

    Cheers
    :D
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Very good beginner's introduction to latches and locks. One point though, You wrote
    So do you mean that user is unable to perform any DML other than running a select query right?
     
  3. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Well, I was trying to sound simple.
    Actually you won't be able to modify only those rows which are locked by the for update query. The database won't stop you from modifying those rows which are not part of the select query with the for update clause

    HTH
    :)
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Exactly what i thought, locks are row-wise and not table-wise
     
  5. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Well you could get a table lock with the command
    Code (Text):

    lock table test_table in exclusive mode;
     
     
  6. nhernandez

    nhernandez Guest

    Excellent Article....