v$latch summarizes v$latch_children

SQL*Plus: RELEASE 12.1.0.2.0 Production ON Sat Oct 17 14:42:21 2015

Copyright (c) 1982, 2014, Oracle.  ALL rights reserved.


Connected TO:
Oracle DATABASE 12c Enterprise Edition RELEASE 12.1.0.2.0 - 64bit Production
WITH the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
AND REAL Application Testing options

SQL>
SQL> SELECT l.latch#, l.hash
  2  FROM v$latch l
  3  WHERE l.name = 'cache buffers chains'
  4  ;

    LATCH#       HASH
---------- ----------
       228 3563305585

SQL>
SQL> SELECT
  2  lc.latch#,
  3  lc.hash,
  4  COUNT( DISTINCT lc.addr ),
  5  COUNT(*),
  6  MAX(lc.child#),
  7  lc.con_id
  8  FROM v$latch_children lc
  9  WHERE lc.name = 'cache buffers chains'
 10  GROUP BY lc.latch#, lc.hash, lc.con_id
 11  ;

    LATCH#       HASH COUNT(DISTINCTLC.ADDR)   COUNT(*) MAX(LC.CHILD#)     CON_ID
---------- ---------- ---------------------- ---------- -------------- ----------
       228 3563305585                  65536      65536          65536          0

SQL>
SQL> SELECT l.gets, l.misses, l.sleeps, l.immediate_gets, l.immediate_misses, l.spin_gets, l.wait_time
  2  FROM v$latch l
  3  WHERE l.name = 'cache buffers chains'
  4  ;

      GETS     MISSES     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES  SPIN_GETS  WAIT_TIME
---------- ---------- ---------- -------------- ---------------- ---------- ----------
 687328579          7          6        1124332                2          1       2998

SQL>
SQL> SELECT
  2  SUM( lc.gets),
  3  SUM( lc.misses),
  4  SUM( lc.sleeps),
  5  SUM( lc.immediate_gets),
  6  SUM( lc.immediate_misses),
  7  SUM( lc.spin_gets),
  8  SUM( lc.wait_time)
  9  FROM v$latch_children lc
 10  WHERE lc.name = 'cache buffers chains'
 11  GROUP BY lc.latch#, lc.hash, lc.con_id
 12  ;

SUM(LC.GETS) SUM(LC.MISSES) SUM(LC.SLEEPS) SUM(LC.IMMEDIATE_GETS) SUM(LC.IMMEDIATE_MISSES) SUM(LC.SPIN_GETS) SUM(LC.WAIT_TIME)
------------ -------------- -------------- ---------------------- ------------------------ ----------------- -----------------
   687328331              7              6                1124332                        2                 1              2998