Newsgroup: it.comp.software.database
Titolo: ORACLE LRU LATCH??

Qualcuno mi sa spiegare cosa sono questi latch che trovo spesso nella
documentazione al capitolo tuning???
ringrazio anticipatamente
ciao
--------------------------------
Inviato via 
http://usenet.libero.it

Ciao,
per quello che so te lo racconto, se erro correggettemi, allora.....
LRU list recently used ovvero un array contente gli oggetti (o altro, di 
latch ce ne sono molti) più recentemente usati. Ad esempio, uno di 
questi array viene riempito per tutte le query più usate o più 
recentemente usate.
Al susseguirsi delle query questo array viene riempito e svuotato man 
mano che si riempie con le qyery più usate. Se una query è simile ad una 
  presente nell'array viene riusata.
Un contesa su questo array può causare rallentamenti nel database.
Spero di essere stato chiaro e preciso,
Simone
                       This bulletin focuses on latches. It attempts to give
                       a clear understanding of how latches are implemented
                       in the Oracle RDBMS and what causes latch contention.
                       The information provided can be used in tuning the
                       various kinds of latches discussed.
Contents:
1.  What is a Latch?
2. Latches vs Enqueues
3. When do we need to obtain a latch?
4. Latches request modes?
5. What causes latch contention?
6. How to identify contention for internal latches?
7. Useful SQL scripts to get latch information
8. List of all the latches
9. List of latches that are of most concern to a DBA
10. How can we reduce contention for internal latches?
11. Tuning _SPIN_COUNT (_LATCH_SPIN_COUNT in Oracle7)
         WHAT ARE LATCHES AND WHAT CAUSES LATCH  CONTENTION
The Oracle RDBMS makes use of different types of locking mechanisms.
They are mainly latches, enqueues, distributed locks and global locks
(used in parallel instance implementations).
This bulletin focuses on latches. It attempts to give a clear understanding
of how latches are implemented in the Oracle RDBMS and what causes latch
contention. The information provided can be used in tuning the various
kinds of latches discussed.
1. What is a latch?
     Latches are low level serialization mechanisms used to protect shared
     data structures in the SGA. The implementation of latches is operating
     system dependent, particularly in regard to whether a process will wait
     for a latch and for how long.
     A latch is a type  of a lock that can be very quickly acquired and 
freed.
     Latches are typically used to prevent more than one process from
     executing the same  piece of  code at  a given time. Associated 
with each
     latch is a cleanup procedure that will be called if a process  dies 
while
     holding  the latch.  Latches  have an  associated level  that  is 
used to
     prevent deadlocks.  Once a process acquires a latch at a certain 
level it
     cannot subsequently acquire a latch at a  level that is equal to 
or less
     than that level (unless it acquires it nowait).
2. Latches vs Enqueues
     Enqueues are another type of locking mechanism used in Oracle.
     An enqueue is a more sophisticated mechanism which permits several 
concurrent
     processes to have varying degree of sharing of "known" resources. 
Any object
     which can be concurrently used, can be protected with enqueues. A 
good example
     is of locks on tables. We allow varying levels of sharing on tables 
e.g.
     two processes can lock a table in share mode or in share update 
mode etc.
     One difference is that the enqueue is obtained using an OS specific
     locking mechanism. An enqueue allows the user to store a value in 
the lock,
     i.e the mode in which we are requesting it. The OS lock manager 
keeps track
     of the resources locked. If a process cannot be granted the lock 
because it
     is incompatible with the mode requested and the lock is requested 
with wait,
     the OS puts the requesting process on a wait queue which is 
serviced in FIFO.
     Another difference between latches and enqueues is that
     in latches there is no ordered queue of waiters like in enqueues. Latch
     waiters may either use timers to wakeup and retry or spin (only in
     multiprocessors). Since all waiters are concurrently retrying 
(depending on
     the scheduler), anyone might get the latch and conceivably the 
first one to
     try might be the last one to get.
3. When do we need to obtain a latch?
     A process acquires a latch when working with a structure in the SGA
     (System Global Area). It continues to hold the latch for the period
     of time it works with the structure. The latch is dropped when the
     process is finished with the structure. Each latch protects a different
     set of data, identified by the name of the latch.
     Oracle uses atomic instructions like "test and set" for operating 
on latches.
     Processes waiting to execute a part of code for which a latch has
     already been obtained by some other process will wait until the
     latch is released. Examples are redo allocation latches, copy
     latches, archive control latch etc. The basic idea is to block 
concurrent
     access to shared data structures. Since the instructions to
     set and free latches are atomic, the OS guarantees that only one 
process gets
     it. Since it is only one instruction, it is quite fast. Latches are 
held
     for short periods of time and provide a mechanism for cleanup in case
     a holder dies abnormally while holding it. This cleaning is done using
     the services of PMON.
4. Latches request modes?
     Latches request can be made in two modes: "willing-to-wait" or "no 
wait". Normally,
     latches will be requested in "willing-to-wait" mode. A request in 
"willing-to-wait"  mode
     will loop, wait, and request again until the latch is obtained.  In 
"no wait" mode the process
     request the latch. If one is not available, instead of waiting, 
another one is requested. Only
     when all fail does the server process have to wait.
     Examples of "willing-to-wait" latches are: shared pool and library 
cache latches
     A example of "no wait" latches is the redo copy latch.
5. What causes latch contention?
     If a required latch is busy, the process requesting it spins, tries 
again
     and if still not available, spins again. The loop is repeated up to 
a maximum
     number of times determined by the initialization parameter _SPIN_COUNT.
     If after this entire loop, the latch is still not available, the 
process must yield
     the CPU and go to sleep. Initially is sleeps for one centisecond. 
This time is
     doubled in every subsequent sleep.
     This causes a slowdown to occur and results in additional CPU usage,
     until a latch is available. The CPU usage is a consequence of the
     "spinning" of the process. "Spinning" means that the process 
continues to
     look for the availability of the latch after certain intervals of time,
     during which it sleeps.
6. How to identify contention for internal latches?
         Relevant data dictionary views to query
         ---------------------------------------------
     V$LATCH
     V$LATCHHOLDER
     V$LATCHNAME
     Each row in the V$LATCH table contains statistics for a different type
     of latch. The columns of the table reflect activity for different types
     of latch requests. The distinction between these types of requests is
     whether the requesting process continues to request a latch if it
     is unavailable:
     willing-to-wait        If the latch requested with a willing-to-wait
                                     request is not available, the 
requesting process
                                     waits a short time and requests the 
latch again.
                                     The process continues waiting and 
requesting until
                                     the latch is available.
     no wait                    If the latch requested with an immediate 
request is
                                     not available, the requesting 
process does not
                                     wait, but continues processing.
     V$LATCHNAME key information:
     ---------------------------------------
     GETS                      Number of successful willing-to-wait 
requests for
                                     a latch.
     MISSES                  Number of times an initial willing-to-wait 
request
                                     was unsuccessful.
     SLEEPS                  Number of times a process waited a 
requested a latch
                                     after an initial wiling-to-wait 
request.
     IMMEDIATE_GETS              Number of successful immediate requests 
for each latch.
     IMMEDIATE_MISSES          Number of unsuccessful immediate requests 
for each latch.
     Calculating latch hit ratio
     ----------------------------
     To get the Hit ratio for  latches apply the following formula:
     "willing-to-wait" Hit Ratio=(GETS-MISSES)/GETS
     "no wait" Hit Ratio=(IMMEDIATE_GETS-IMMEDIATE_MISSES)/IMMEDIATE_GETS
     This number should be close to 1. If not, tune according to the 
latch name
7. Useful SQL scripts to get latch information
     /*
     ** Display System-wide latch statistics.
     */
     column name format A32 truncate heading "LATCH NAME"
     column pid heading "HOLDER PID"
     select 
c.name
,a.addr,a.gets,a.misses,a.sleeps,
     a.immediate_gets,a.immediate_misses,b.pid
     from v$latch a, v$latchholder b, v$latchname c
     where a.addr = b.laddr(+)
     and a.latch# = c.latch#
     order by a.latch#;
     /*
     ** Given a latch address, find out the latch name.
     */
     column name format a64 heading 'Name'
     select name from v$latchname a, v$latch b
     where b.addr = '&addr'
     and b.latch#=a.latch#;
     /*
     ** Display latch statistics by latch name.
     */
     column name format a32 heading 'LATCH NAME'
     column pid heading 'HOLDER PID'
     select 
c.name
,a.addr,a.gets,a.misses,a.sleeps,
     a.immediate_gets,a.immediate_misses,b.pid
     from v$latch a, v$latchholder b, v$latchname c
     where a.addr   = b.laddr(+) and a.latch# = c.latch#
     and 
c.name
 like '&latch_name%' order by a.latch#;
8. List of all the latches
     Oracle versions might differ in the latch# assigned to the existing 
latches.
     The following query will help you to identify all latches and the 
number assigned.
     column name format a40 heading 'LATCH NAME'
     select latch#, name from v$latchname;
     The following output is for Oracle Release 8.1.7:
     LATCH#     NAME
     ---------- ------------------
              0 latch wait list
             1 process allocation
             2 session allocation
             3 session switching
             4 process group creation
             5 session idle bit
             6 longop free list
             7 cached attr list
             8 GDS latch
             9 shared java pool
            10 event group latch
            11 messages
            12 enqueues
            13 enqueue hash chains
            14 trace latch
            15 KSFQ
            16 X$KSFQP
            17 i/o slave adaptor
            18 ksfv messages
            19 msg queue latch
            20 done queue latch
            21 session queue latch
            22 direct msg latch
            23 vecio buf des
            24 ksfv subheap
            25 resmgr group change latch
            26 channel handle pool latch
            27 channel operations parent latch
            28 message pool operations parent latch
            29 first spare latch
            30 second spare latch
            31 dlm process table freelist
            32 process parent latch
            33 dlm process hash list
            34 dlm resource table freelist
            35 dlm resource hash list
            36 dlm resource scan list
            37 dlm lock table freelist
            38 dlm cr info freelist latch
            39 dlm timeout list
            40 dlm deadlock list
            41 dlm statistic table latch
            42 dlm synchronous data latch
            43 dlm shared communication latch
            44 dlm recovery domain table latch
            45 dlm recovery domain record latch
            46 dlm domain table latch
            47 dlm domain lock latch
            48 dlm domain lock table latch
            49 dlm group table freelist
            50 dlm group lock latch
            51 dlm group lock table latch
            52 name-service entry
            53 name-service request queue
            54 name-service pending queue
            55 name-service namespace bucket
            56 name-service memory objects
            57 name-service request
            58 dlm cr request queue latch
            59 file number translation table
            60 mostly latch-free SCN
            61 batching SCNs
            62 cache buffers lru chain
            63 multiple dbwriter suspend
            64 active checkpoint queue latch
            65 checkpoint queue latch
            66 cache buffers chains
            67 cache buffer handles
            68 multiblock read objects
            69 cache protection latch
            70 large memory latch
            71 simulator lru latch
            72 simulator hash latch
            73 archive control
            74 archive process latch
            75 redo allocation
            76 redo copy
            77 redo writing
            78 KCL instance latch
            79 KCL lock element parent latch
            80 KCL name table latch
            81 KCL freelist latch
            82 loader state object freelist
            83 begin backup scn array
            84 dml lock allocation
            85 list of block allocation
            86 transaction allocation
            87 transaction branch allocation
            88 sort extent pool
            89 undo global data
            90 ktm global data
            91 parallel txn reco latch
            92 intra txn parallel recovery
            93 sequence cache
            94 temp lob duration state obj allocation
            95 row cache objects
            96 dictionary lookup
            97 cost function
            98 user lock
            99 comparison bit cache
           100 instance information
           101 policy information
           102 global tx free list
           103 global transaction
           104 global tx hash mapping
           105 shared pool
           106 library cache
           107 library cache load lock
           108 Token Manager
           109 Direct I/O Adaptor
           110 cas latch
           111 resmgr:runnable lists
           112 resmgr:actses change state
           113 resmgr:actses change group
           114 resmgr:session queuing
           115 resmgr:actses active list
           116 resmgr:schema config
           117 resmgr:gang list
           118 resmgr:queued list
           119 resmgr:running actses count
           120 resmgr:method mem alloc latch
           121 resmgr:plan CPU method
           122 resmgr:resource group CPU method
           123 dispatcher configuration
           124 session timer
           125 parameter list
           126 presentation list
           127 address list
           128 end-point list
           129 virtual circuit buffers
           130 virtual circuit queues
           131 virtual circuits
           132 ncodef allocation latch
           133 NLS data objects
           134 JOX SGA heap latch
           135 job_queue_processes parameter latch
           136 query server process
           137 query server freelists
           138 error message lists
           139 process queue
           140 process queue reference
           141 parallel query stats
           142 parallel query alloc buffer
           143 temp table ageout allocation latch
           144 hash table modification latch
           145 constraint object allocation
           146 device information
           147 temporary table state object allocation
           148 kwqit: protect wakeup time
           149 AQ Propagation Scheduling Proc Table
           150 AQ Propagation Scheduling System Load
           151 fixed table rows for x$hs_session
9. List of latches that are of most concern to a DBA
     BUFFER CACHE LATCHES
     =====================
     Cache buffers chains latch:
     ...........................
             This latch is needed when user processes try to scan
             the SGA for database cache buffers.
     Cache buffers LRU chain latch:
     ..............................
             This latch is needed when user processes try to scan
             the LRU (least recently used) chain containing all the 
dirty blocks
             in the buffer cache.
     REDOLOG BUFFER LATCHES
     ========================
     Redo allocation latch:
     ......................
             This latch controls the allocation of space for redo
             entries in the redo log buffer. There is one redo allocation
             latch per instance.
     Redo copy latch:
     ................
             This latch is used to write redo records into the redolog 
buffer.
     SHARED POOL LATCHES
     ====================
     Row cache objects latch:
     ........................
             This latch comes into play when user processes are 
attempting to
             access the cached data dictionary values.
     Library cache latch
     ..............................
              It serialize access to the objects in the library cache. 
Every time a SQL statements, PL/SQL
              blocks or a stored objects (Procedures, packages, 
functions, triggers) is executed this latch
              is acquired.
     Shared pool latch
     ............................
              This latch protects the allocation of memory in the 
library cache. There is just one latch to
               the entire database.
10. How can we reduce contention for internal latches?
     We can reduce contention for these latches and tune them by adjusting
     certain init.ora parameters.
     Cache buffers chain latch:
     ..........................
             Contention in this latch might be related with the Buffer 
cache size, but it might be present
             due to a "hot block" (meaning a block highly accessed). 
Before of incrementing the parameter
             DB_BLOCK_BUFFER check that specific blocks are not causing 
the contention avoiding
             memory wasting.
     Cache buffers LRU chain latch:
     ..............................
             Multiple Buffer pools and adjusting the parameter 
DB_BLOCK_LRU_LATCHES to have multiple
             LRU latches will help on reducing latch contention.
     Redo Allocation Latch:
     ......................
             Contention for this latch in Oracle7 can be reduced by 
decreasing the value of
             LOG_SMALL_ENTRY_MAX_SIZE on multi-cpu systems to force the 
use of the
             redo copy latch. In Oracle8i this parameter is obsolete, so 
you need to consider
             to increase the size of the LOG_BUFFER or reduce the load 
of the log buffer using
             NOLOGGING features when possible.
     Redo copy latch:
     ................
             This latch is waited for on both single and multi-cpu systems.
             On multi-cpu systems, contention can be reduced by 
increasing the
             value of LOG_SIMULTANEOUS_COPIES (Hidden in Oracle8i) 
and/or increasing
             LOG_ENTRY_PREBUILD_THRESHOLD (undocumented in Oracle7).
     Row cache objects latch:
     ........................
             In order to reduce contention for this latch, we need to
             tune the data dictionary cache. In Oracle7 this basically means
             increasing the size of the shared pool (SHARED_POOL_SIZE)
             as the dictionary cache is a part of the shared pool.
     Library cache and Shared pool latches
     ..............................
              The first resource to reduce contention on this latch is 
to ensure that the application
              is reusing as much as possible SQL statement 
representation. If the application is already
              tuned the SHARED_POOL_SIZE can be increased. Be aware that 
if the application is not
              using appropriately the library cache the contention might 
be worst with a larger structure
              to be handled.
11. Tuning _SPIN_COUNT (_LATCH_SPIN_COUNT in Oracle7)
         SPIN_COUNT controls how many times the process will re-try to 
obtain
         the latch before backing off and going to sleep. This basically
         means the process is in a tight CPU loop continually trying to get
         the latch for SPIN_COUNT attempts.
         On a single CPU system if an Oracle process tries to acquire a 
latch
         but it is held by someone else the process will release the CPU 
and go
         to sleep for a short period before trying again.
         However, on a multi processor system (SMP) it is possible that the
         process holding the latch is running on one of the other CPUs
         and so will potentially release the latch in the next few 
instructions
         (latches are usually held for only very short periods of time).
         Performance can be adjusted by changing the value of SPIN_COUNT.
         If a high value is used, the latch will be attained sooner than if
         you use a low value. However, you may use more CPU time spinning to
         get the latch if you use a high value for SPIN_COUNT.
NOTE:
=====
It is important to understand that it is neither practical nor possible
to provide specific values for the init.ora parameters discussed in this
bulletin. The values for these parameters vary from database to database
and from platform to platform. Moreover, for the same database and 
platforms,
they may vary from application to application.
Before using any of the underscore (undocumented) parameters, please 
call your
local support office.
Related Documents:
==================
<Note:62161.1> - Systemwide Tuning using UTLESTAT Reports in Oracle7/8
   .

 11. Tuning _SPIN_COUNT (_LATCH_SPIN_COUNT in Oracle7)
NINJA!!!
accendere la stampante, subito!
-- 
punkarruso

 Simone
sei stato chirissimo e precisissimo..ti ringrazio


 questi array viene riempito per tutte le query piů usate o piů
le LRU latches riguardano i blocchi letti nella buffer cache, non le query
nella shared-pool..