Chao_ping:There is no "tally" of buffer
busy waits (bbw's) as related to segments. You can see total bbw's with this
query:SELECT w.class AS
block_class , w.COUNT AS
total_waits , w.TIME AS
time_waited , ROUND(w.COUNT /
(consistent_gets + db_block_gets), 3) AS bbw_ratio FROM
sys.v_$waitstat w , (SELECT
(SUM(DECODE(name, 'no work - consistent read gets', VALUE, 0)) +
SUM(
DECODE(
name
, 'cleanouts only - consistent read gets',
VALUE
,
0))
+ SUM(DECODE(name, 'rollbacks only - consistent read gets', VALUE,
0))
+ SUM(DECODE(name, 'cleanouts and rollbacks - consistent read gets', VALUE, 0)))
AS
consistent_gets
FROM sys.v_$sysstat) , (SELECT VALUE AS
db_block_gets
FROM v$sysstat
WHERE name = 'db block gets') WHERE w.COUNT > 0ORDER BY 3
DESC;You can see bbw's per datafile with this query:<FONT
face="Courier New">SELECT d.name AS
filename , d.file# AS
file_id , w.COUNT AS
waits ,
w.TIME , w.TIME / (DECODE(w.COUNT, 0, 1,
w.COUNT)) AS average FROM sys.x_$kcbfwait
w , sys.v_$datafile d
WHERE w.indx + 1 = d.file# AND w.indx < (SELECT
COUNT(*)
FROM sys.v_$datafile) AND w.COUNT > 0ORDER BY
w.COUNT DESC;When a given session is on a bbw, you can see what
segment the block is a part of with this query:<FONT
face="Courier New">SELECT LOWER(s.username) AS
username ,
s.osuser ,
s.sid ,
s.serial# , sw.event AS
wait_event , sw.seconds_in_wait AS
time ,
LOWER(
'file: '|| SUBSTR(df.file_name, INSTR(df.file_name, '/', -1) + 1) || CHR(10) ||
'object: ' || map.owner || '.' || map.segment_name ||
'('
|| bc.class || ')') AS details FROM sys.v_$session_wait
sw , sys.v_$session
s , sys.dba_data_files
df , (SELECT
owner
,
segment_name
,
segment_type
, file_id AS
file#
, block_id AS
lo_blk
, block_id + blocks - 1 AS
hi_blk
FROM dba_extents) map , (SELECT
file#
,
dbablk
,
DECODE(
class
, 1, 'data
block'
, 2, 'sort
segment'
, 3, 'save
undo'
, 4, 'segment
header'
, 5, 'save undo segment
header'
, 6, 'freelist
block'
, 7, 'system undo
header'
, 8, 'system undo
block'
, DECODE(MOD(class, 2), 1, 'undo header', 0, 'undo block')) AS
class FROM
sys.x_$bh) bc WHERE sw.sid = s.sid
AND sw.p1 = df.file_id AND sw.p1 =
map.file# AND sw.p2 BETWEEN map.lo_blk AND
map.hi_blk AND bc.file# =
sw.p1 AND bc.dbablk =
sw.p2 AND sw.event = 'buffer busy
waits';
However, I know of no way to get a
system-level view of what segments have been experiencing bbw's. If anyone knows
of any way, please let me know as I would love to know.
Regards,
Jon Walthour, BSCDCincinnati,
Ohio-----Original Message-----From: root_at_fatcity.com
[mailto:root_at_fatcity.com] On Behalf Of
chao_pingSent: Saturday, August 11, 2001 9:30 AMTo: Multiple recipients
of list ORACLE-LSubject: which segment need more freelist?hi,
grurus: i know if a table is frequently inserted/updated,
oracle will meet freelist (or freelist groups) contention, but how to find out
which table on earth meet the wait of the segment head
freelist? which view shall i refer
to? thanks.chao_ping.from
china.>±zˇr9,¶Ă©˘j?jžÉˇr´ČÂI‰óßç_çÓjzj˘h–žťqłŠ€¶DTş•˘ŠŠ‚¶u1™j ´ąrr˘\˛–ˇ˛®v…˛xb–jyµŠ59,®ťˇŠ‚·Á¶˝®˘É©l˘Ç§vŘ^ BĎr‰¦jw_˘ş->…ęâť?™«b˘yb‘ë.n?‰¸
Received on Sun Aug 12 2001 - 16:35:16 CDT
