bhla.sql

-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

--------------------------------------------------------------------------------
--
-- File name:   bhla.sql (Buffer Headers by Latch Address)
-- Purpose:     Report which blocks are in buffer cache, protected by a cache
--              buffers chains child latch
--
-- Author:      Tanel Poder
-- Copyright:   (c) http://www.tanelpoder.com
--              
-- Usage:       @bhla <child latch address>
--              @bhla 27E5A780
-- 	        
--	        
-- Other:       This script reports all buffers "under" the given cache buffers
--              chains child latch, their corresponding segment names and
--              touch counts (TCH).
--
--------------------------------------------------------------------------------

col bhla_object head object for a40 truncate
col bhla_DBA head DBA for a20
col bhla_obj head OBJ for 99999999999

WITH bclass AS (SELECT class, ROWNUM id from v$waitstat)
select  /*+ LEADING(bh) */
	bh.obj bhla_obj,
	o.object_type,
	o.owner||'.'||o.object_name		bhla_object,
  bclass.class,
	bh.tch,
	file# ||' '||dbablk			bhla_DBA,
	bh.state,
	bh.mode_held,
	bh.dirty_queue				DQ,
	trim(to_char(bh.flag, 'XXXXXXXX'))	||':'||trim(to_char(bh.lru_flag, 'XXXXXXXX')) flg_lruflg
from
	x$bh		bh,
	dba_objects	o,
  bclass
where
	  bh.obj = o.data_object_id
and bh.class = bclass.id (+)
and	hladdr = hextoraw(lpad('&1', vsize(hladdr)*2 , '0'))
order by
	tch desc
/