set pages 999
set lines 80
ttitle 'Contents of Data Buffers'
drop table t1;
create table t1 as
select
o.object_name object_name,
o.object_type object_type,
count(1) num_blocks
from
dba_objects o,
v$bh bh
where
o.object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM')
group by
o.object_name,
o.object_type
order by
count(1) desc
;
column c1 heading "Object|Name" format a30
column c2 heading "Object|Type" format a12
column c3 heading "Number of|Blocks" format 999,999,999,999
column c4 heading "Percentage|of object|data blocks|in Buffer" format 999
select
object_name c1,
object_type c2,
num_blocks c3,
(num_blocks/decode(sum(blocks), 0, .001, sum(blocks)))*100 c4
from
t1,
dba_segments s
where
s.segment_name = t1.object_name
and
num_blocks > 10
group by
object_name,
object_type,
num_blocks
order by
num_blocks desc
;
Wed Oct 23 page 1
Contents of Data Buffers
Percentage
of object
Object Object Number of data blocks
Name Type Blocks in Buffer
--------------------------- ------------ -------------- -----------
MTL_DEMAND_INTERFACE TABLE 38,745 100
FND_CONCURRENT_REQUESTS TABLE 16,636 88
WIP_TRANSACTIONS TABLE 14,777 100
WIP_TRANSACTION_ACCOUNTS TABLE 13,390 33
CRP_RESOURCE_HOURS TABLE 7,806 100
SO_LINES_ALL TABLE 7,576 100
ABC_EDI_LINES TABLE 7,041 100
BOM_INVENTORY_COMPONENTS TABLE 6,882 46
MTL_SYSTEM_ITEMS TABLE 4,747 63
WIP_TRANSACTION_ACCOUNTS_N1 INDEX 3,996 38
MTL_ITEM_CATEGORIES TABLE 3,390 100
RA_CUSTOMER_TRX_LINES_ALL TABLE 3,264 100
MRP_FORECAST_DATES TABLE 3,082 99
RA_CUSTOMER_TRX_ALL TABLE 2,739 97
WIP_OPERATIONS TABLE 2,311 34
SO_PICKING_LINES_ALL TABLE 2,006 100
MTL_DEMAND_INTERFACE_N10 INDEX 1,482 76
BOM_OPERATION_RESOURCES TABLE 1,456 45
ABC_EDI_ERRORS TABLE 1,427 100
ABC_EDI_HEADERS TABLE 1,188 100
