Das Warten auf „busy buffers“ kann die unterschiedlichsten Ursachen haben. So werden 18 Block-Kassen unterschieden die sich nicht nur auf Daten-, Undo-, Sort- oder Index-Block (bzw. auf deren Header) beziehen, sondern auch bis hinunter auf Extent- und Datenfile-Ebene gehen.
Mein Problem
Nach dem Umzug meiner Datenbank von Solaris auf Linux mittels Datapump, zeigten die Statspack Reports immer das Event „buffer busy waits“ unter den Top-5. Die Anwendung auf der Instanz war unverändert; vielleicht ein verändertes Userverhalten (wo wir ja jetzt wesentlich flotter unterwegs waren). Mein erster Gedanke: „Hot Blocks“.
Die Analyse
Im Nachhinein gibt die v$session_wait leider nicht mehr viel her, also versuche ich es mit der v$session_wait_history und suche nach dem Event.
EVENT SID sum sum P1TEXT P1 P2TEXT P2 P3TEXT P3
wait_time wait_cout
buffer busy waits 433 1 1 file# 7 block# 601840 class# 1
buffer busy waits 228 588 6 file# 7 block# 2 class# 13
buffer busy waits 513 588 6 file# 6 block# 2 class# 13
Ok. File# 7 ist das Daten Tablespace und 6 das Tablespace für die Indizes, aber was bitte schön ist die class# 13?
Recherchen ergeben: Class#13 steht für „file header block„
Demnach handelt es sich hier nicht um die sonst üblichen konkurrierenden Zugriffe auf gleiche Datenblöcke, sondern spielt sich weiter unten auf der Datenfile Ebene ab.
Zudem wurden, dass kam dann so nebenbei heraus, zu den betroffenen Zeiten verstärkt Daten über die Anwendung in die Datenbank geladen.
Die Analyse der Datenfiles ergabt, dass beim initialen Anlegen der beiden Tablespases keine Größe für Next Extents angegeben wurde. Somit wurde ein Defaultwert von 8KB (was genau einem Block entspricht) angenommen.
Dementsprechend war das System,während der Ladevorgänge, ständig damit beschäftigt die Datenfiles um 8KB zu erweitern.
Die Lösung
Ich habe dann die Größe der Next Extents auf 100MB gesetzt und das Event „buffer busy waits“, zumindest was die Block-Klasse 13 betrifft, gehört der Vergangenheit an.
Abfrage der System Events
Mit folgendem SQL habe ich mir geholfen.
SELECT nvl(s.schemaname, 'disconnected') as "Schema Name"
, w.sid
, w.event
, e.wait_class
, w.wait_time
, w.wait_count
, upper(w.p1text) as "P1 Type"
, w.p1 as "P1 Value"
, upper(w.p2text) as "P2 Type"
, w.p2 as "P2 Value"
, upper(w.p3text) as "P3 Type"
, w.p3 as "P3 Value"
, decode (nvl(upper(w.p3text),'|')
, 'CLASS#'
, decode (w.p3
,1 ,'data block'
,2 ,'sort block'
,3 ,'save undo block'
,4 ,'segment header'
,5 ,'save undo header'
,6 ,'free list'
,7 ,'extent map'
,8 ,'1st level bmb'
,9 ,'2nd level bmb'
,10,'3rd level bmb'
,11,'bitmap block'
,12,'bitmap index block'
,13,'file header block'
,14,'unused'
,15,'system undo header'
,16,'system undo block'
,17,'undo header'
,18,'undo block'
)
,''
) as "Block Type"
, case when nvl(upper(w.p3text),'|') in ('CLASS#','BLOCKS','SET-ID#','BUF_PTR')
then ( select x.segment_name
from dba_extents x
where x.file_id = w.p1
and w.p2 between x.block_id and x.block_id + x.blocks -1
)
end as "Segment Type"
, s.sql_hash_value
FROM (
SELECT x.event
, x.event#
, x.sid
, sum(x.wait_time) as wait_time
, sum(x.wait_count) as wait_count
, x.p1
, x.p2
, x.p3
, x.p1text
, x.p2text
, x.p3text
FROM v$session_wait_history x
WHERE x.wait_time > 0
GROUP BY x.event, x.event#, x.sid, x.p1text, x.p2text, x.p3text, x.p1, x.p2, x.p3
) w
, v$event_name e
, v$session s
WHERE e.event# = w.event#
AND s.sid(+) = w.sid
AND e.wait_class not in ('Idle')
AND w.event = 'buffer busy waits'
AND s.schemaname(+) like '%'||'&schema'||'%'
ORDER BY s.schemaname,w.sid;
Bildnachweise:
Titelbild: © sqldev-101614-2340766-ci
