v$latch_children

<pre name="code" class="sql">SQL> set linesize 200
SQL>  select addr,LATCH#,LEVEL#,CHILD#,NAME,gets  from v$latch_children where name='shared pool';

ADDR	     LATCH#	LEVEL#	   CHILD# NAME									 GETS
-------- ---------- ---------- ---------- ---------------------------------------------------------------- ----------
200A7B30	293	     7		7 shared pool								   10
200A7ACC	293	     7		6 shared pool								   10
200A7A68	293	     7		5 shared pool								   10
200A7A04	293	     7		4 shared pool								   10
200A79A0	293	     7		3 shared pool								   10
200A793C	293	     7		2 shared pool								   10
200A78D8	293	     7		1 shared pool								53967

已选择7行。

V$LATCH_CHILDREN

V$LATCH_CHILDREN displays statistics about child latches. This view includes all columns of V$LATCH plus the CHILD# column. 

Note that child latches have the same parent if their LATCH# columns match each other.




V$LATCH_CHILDREN  显示子latches的相关信息,这个视图包括了所有v$latch的列加上CHILD# 列

注意child latches 具有同样的父latch 如果 LATCH#列匹配的话

SQL>  select latch#,name from v$latch where name='shared pool';

    LATCH# NAME
---------- ----------------------------------------------------------------
       293 shared pool


------------------------------------------------------------------------------------------------------------------------------------



SQL>  select latch#,name from v$latch where name='buffer pool';

    LATCH# NAME
---------- ----------------------------------------------------------------
       146 buffer pool

SQL> select addr,LATCH#,LEVEL#,CHILD#,NAME from v$latch_children where name='buffer pool';

ADDR	     LATCH#	LEVEL#	   CHILD# NAME
-------- ---------- ---------- ---------- ----------------------------------------------------------------
2CD760A8	146	     1		8 buffer pool
2CD75F78	146	     1		7 buffer pool
2CD75E48	146	     1		6 buffer pool
2CD75D18	146	     1		5 buffer pool
2CD75BE8	146	     1		4 buffer pool
2CD75AB8	146	     1		3 buffer pool
2CD75988	146	     1		2 buffer pool
2CD75858	146	     1		1 buffer pool

已选择8行。



SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
  from x$ksppi a, x$ksppcv b
 where a.indx = b.indx and a.ksppinm like '_db_block_hash_latches';  2    3  

NAME	   VALUE      DESCRIPTION
---------- ---------- --------------------------------------------------
_db_block_ 1024       Number of database block hash latches hash_latches


Latch:cache buffers chains的数量受隐含参数_db_block_hash_latches的影响



SQL> col DESCRIPTION format a50
SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
  from x$ksppi a, x$ksppcv b
 where a.indx = b.indx and a.ksppinm like '_kghdsidx_count';  2    3  

NAME	   VALUE      DESCRIPTION
---------- ---------- --------------------------------------------------
_kghdsidx_ 1	      max kghdsidx countcount

SubPool的数量可以通过_kghdsidx_count参数来控制