„buffer busy waits“ und „Next“ Extent Größe

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

Das könnte Sie auch interessieren

Bleiben Sie informiert:

its-people hilft Ihnen...

Weitere Blogthemen:

its-people – wir machen Ihre IT moderner,
leistungsfähiger und sicherer

Erfahren Sie bei einem persönlichen Gespräch, wie wir Sie gewinnbringend unterstützen können. Suchen Sie sich einen passenden Zeitpunkt aus. Wir melden uns. Versprochen!