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 > 0
ORDER BY 3 DESC;
You can see bbw's per datafile with this query:
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 > 0
ORDER 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:
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';
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 > 0
ORDER BY 3 DESC;
You can see bbw's per datafile with this query:
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 > 0
ORDER 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:
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.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of chao_ping
Sent: Saturday, August 11, 2001 9:30 AM
To: Multiple recipients of list ORACLE-L
Subject: 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.
>zr9,íj?jɡrI_jzjhqDTu1jrr\vxbjy59,ɩlǧv^ Brjw_-
>?byb.n?
Regards,
Jon Walthour, BSCD
Cincinnati, Ohio
Cincinnati, Ohio
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of chao_ping
Sent: Saturday, August 11, 2001 9:30 AM
To: Multiple recipients of list ORACLE-L
Subject: 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.
>zr9,íj?jɡrI_jzjhqDTu1jrr\vxbjy59,ɩlǧv^ Brjw_-
>?byb.n?
