怎样将V$LATCH_CHILDREN、X$BH、DBA_EXTENTS三张表关联起来

from:http://www.itpub.net/thread-805849-1-1.html

 

 

数据库中有些类别的latches拥有多个。V$LATCH中提供了每个类别的总计信息。如果想看到单个latch，你可以通过查询V$LATCH_CHILDREN。

 

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

1 从 v$latch_children 中找出latch的地址addr
2 SELECT OBJ data_object_id, FILE#, dbarfil , DBABLK,CLASS, STATE, TCH
  FROM X$BH
WHERE HLADDR = 'address of latch'
  ORDER BY TCH;

X$BH.TCH is a touch count for the buffer. A high value for X$BH.TCH indicates a hot block
3  SELECT OBJECT_NAME, SUBOBJECT_NAME
  FROM DBA_OBJECTS
WHERE DATA_OBJECT_ID = &obj;

以上可以定位到hot block是在哪个对象上
4 也可以关联dba_extents找到segment_name

在第2步中找到了dbarfi和dbablk可以和DBA_EXTENTS中的relative_fno,block_id , blocks相关联

 

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

v$latch 　反映的是某latch 整体情况．
１０g　　latch的种类很多　
举个例子：
cache buffers chains       　这个latch .　产生这个 latch 对应的数据块对象可能有很多．可能是表也可能是索引．每一个块对应的buffer 就会产生一个子latch .但是所有的这些latch 都是属于父latch的同一类型latch.

为什么要引入子latch 的概念．
说白了就是通过children addr可以查到具体的 产生cache  的地址．

出差住某宾馆谁都知道．但是主哪个房子．那个铺位．就不知道了．子地址就是告诉你这些．

用途：大师已经说的很明白了．
狗尾逐貂一下．
把大师的这个语句解析一下:
2 SELECT OBJ data_object_id, FILE#, dbarfil , DBABLK,CLASS, STATE, TCH
  FROM X$BH
WHERE HLADDR = 'address of latch'
  ORDER BY TCH;
HLADDR:这个 buffer cache lru 地址
OBJ:   对象id 
通过dba_objects 中OBJECT_ID  就可以打通 v$latch_children 与dba_objects的桥梁.

结论:要想记住东西.先理解他.
要消灭敌人.想了解他.《孙子兵法.谋攻第三》:知己知彼，百战不贻

 

 

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

 

 

QUOTE:

---

原帖由   tanfufa  于 2008-11-14 15:00 发表  
每一个块对应的buffer 就会产生一个子latch .但是所有的这些latch 都是属于父latch的同一类型latch.

---

同意LS的大部分意见，
但对这句话表示怀疑。
在 Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production 做个测试：

TYPE TYPE                                     PARENT   CHILD
NUMBER NAME                                      LATCH LATCHES
98 cache buffers chains                          1    2048

可以查询到cache buffers chains                共有2048个子latch，
而
SQL> select count(*) from x$bh;

  COUNT(*)
----------
    148875
应该是一个  cache buffers chains          管理多个hash bucket, 而hash bucket中的block是根据DBA  hash后的分布。

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

 

Jonathan Lewis’ post discussing the join between v$bh and dba_objects

from: http://jonathanlewis.wordpress.com/2006/11/02/but-its-in-the-manual/

I’ve just seen a note on the news group comp.databases.server.oracle advising someone to check the online manual for a piece of code to report which objects are using how much space in the buffer cache. This is the reference and this is the code:

SELECT o.object_name, COUNT(1) number_of_blocks
  FROM DBA_OBJECTS o, V$BH bh
 WHERE o.object_id  = bh.objd
   AND o.owner     != 'SYS'
 GROUP BY o.object_name
 ORDER BY count(1);

There are two flaws with this code – it gets the wrong results, and it’s inefficient.

The column objd in v$bh is the data_object_id from dba_objects, not the object_id.  The data object id caters for rebuilding indexes, moving tables, clusters, and partitioning (amongst others).  Look at the sample below where I’ve created a partitioned table with a primary key index, then done various bits of DDL, including a partition exchange and one index partition rebuild.

SQL> l
  1  select
  2  	object_id, data_object_id,
  3  	object_name, subobject_name
  4  from
  5* 	user_objects
SQL> /              

 OBJECT_ID DATA_OBJECT_ID OBJECT_NAME          SUBOBJECT_NAME
---------- -------------- -------------------- --------------------
     44797          44802 PT_PK                P400
     44801          44791 PT_PK                P600
     44795                PT_PK
     44794          44794 PT_RANGE             P400
     44800          44790 PT_RANGE             P600
     44792                PT_RANGE
     44790          44800 T1
     44791          44801 T1_PK
     44798                V_PT
SQL> spool off              

Note particularly how the partitioned table has a ‘logical’ object id, but it is only the physical data segments of the underlying partitions that have a ‘physical’ data object id.

Note also that the ‘logical’ object id of the table t1 matches the ‘physical’  data object id of partition p600 and vice versa (with a matching cross-over on the primary keys). This is because I exchanged that table with that partition.  And while we’re talking about partitioning, shouldn’t the code also aggregate by subobject_name to separate the partitions out ? So this code is going to get funny results, and miss objects, for all sorts of reasons.

Efficiency

A couple of standard guidelines for writing SQL – eliminate early, aggregate before extending.  So let’s apply them here.

The join to dba_objects is not going to work for rollback segments or global temporary tables, so eliminate them – the predicate “objd < power(2,22)” will do this. (It’s an interesting point that we can now create partitioned tables with millions of partitions – but once you get above roughly 4,000,000 the data_object_id is going to collide with the objd values used for temporary table segments).

Then we might want to eliminate the buffers which are free – they may still have the object information in them but (after a truncate, for example) the information is no longer valid. For v$bh an appropriate predicate would be status != ‘free’.

We then see that the code joins to dba_objects before aggregating by object name. So if big_table_X has 20,000 blocks in the buffer, we join 20,000 times to extend 20,000 times to aggregate by object name – when we could have aggregated by the (short) objd and joined just once.

So take out the reference to v$bh, and replace it with a view that does the aggregation, for example something like the following:

SELECT o.object_name, subobject_name, ct number_of_blocks
FROM
	DBA_OBJECTS o,
	(select
		/*+ no_merge */
		objd, count(*) ct
	from	v$bh
 	where	objd < power(2,22)
	and	status != 'free'
	group by
		objd
	)	bh
 WHERE o.data_object_id  = bh.objd
   AND o.owner     != 'SYS'
 ORDER BY ct, object_name, subobject_name
;        

Of course, you could then argue that if you’re going to play with v$bh, you should be brave enough to use obj$ and user$, rather than joining to an over-burdened dba_objects – eliminating ‘SYS’ on the understanding that ‘SYS’ has a user id of zero.

But that’s left as an exercise to the reader (and while you’re at it, you might as well use x$bh so that you don’t have to waste resoures doing the decode() that gets you the status, and I wonder if there’s any efficient point where you can adjust the code to avoid reporting objects which have been dropped but are still in the recycle bin if you’re on 10g).

 Just as an afterthought – even if you do make this query produce the correct results efficiently, what is it telling you ? After all, there may be multiple copies of each data block in the buffer**, so do you want to know about distinct blocks, or all blocks, and is there any significance in the two possible approaches ?

** In principle the parameter _db_block_max_cr_dba suggests that the limit should be 6 copies per block. I believe the parameter first appeared in version 6 of Oracle but there still seem to be cases where it doesn’t quite work, even in the very latest versions of Oracle.