Stop arnaque

Arnaque aux dentistes mais pas que!

Cette recette c'est pour toi mon BB d'amour

Les Beignets de Carnaval !

Tuning Latch Contention: Cache-Buffers-Chain Latches

Researching Further

Re-querying v$session_wait, I see that a couple of latches are hot. I will consider one latch children with latch address 19875043200 as an example, and drill down further.

 

Hang Those Buffers!

Next, I needed to find buffers protected by these latch children, and then find the buffers causing latch contention. Many such hash buckets (and so, numerous buffers) are protected by a latch children. 

I don’t favour the above script, since its performance is not optimal. It is much easier to dump the blocks and convert them to object ids. Let’s dump these three blocks.

Now I can query dba_objects to find the object_names.

https://blog.pythian.com/tuning-latch-contention-cache-buffers-chain-latches/

Identifying locked objects and killing the session in DB

  SELECT oracle_username || ' (' || s.osuser || ')' username,
         s.sid || ',' || s.serial# sess_id,
         owner || '.' || object_name object,
         object_type,
         DECODE (l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS,
         DECODE (v.locked_mode,
                 0, 'None',
                 1, 'Null',
                 2, 'Row-S (SS)',
                 3, 'Row-X (SX)',
                 4, 'Share',
                 5, 'S/Row-X (SSX)',
                 6, 'Exclusive',
                 TO_CHAR (lmode))
            mode_held
    FROM v$locked_object v,
         dba_objects d,
         v$lock l,
         v$session s
   WHERE     v.object_id = d.object_id
         AND v.object_id = l.id1
         AND v.session_id = s.sid
ORDER BY oracle_username, session_id;

https://emarcel.com/identifyandkill/