Buffer Busy Wait Nedir ve Nasıl Tune Edilir?

Buffer Busy Wait event, bir session buffer cache içerisinde ki bir data bloğuna erişmek istediğinde, bu blok başka bir session tarafından kullanılıyorsa ortaya çıkan bir bekleme durumudur.  Buffer cache deki bahsedilen durumda olan data bloğu datafileden buffer cache alınıyor yada buffer cache içerisinde değiştiriliyor olabilir.

Buffer Busy wait eventi ile karıştırılmamalıdır.Buffer busy eventi Automatic Storage Management (ASM) kullanan veritabanlarında cache edilen metadaya erişimde yaşanan bekleme olayı olarak tarif edilir.

V$SESSION gibi viewlara baktığınızda buffer busy wait event’e ait parametreler görürsünüz.Şimdi bu parametrelerin ne olduğunu ve nasıl kullanılabileceğini inceleyelim.

P1: File#  (Beklenen data bloğunu içeren datafilenin numarasıdır. DBA_DATA_FILES viewında FILE_ID kolonu ile eşitlenerek beklenen bloğun ait olduğu data fileye dair bilgiler edinilebilir.)

P2: Block# (DBA_EXTEND viewında bulunan block_id kolonuna denk gelir.)

P3: Class#

Buffer busy wait event gördüğünüzde, hangi bloğun beklendiğini bulmak için aşağıdaki sql den yaralanabilirsiniz.

SELECT tablespace_name, file_name

    FROM dba_data_files

   WHERE file_id = &v$session.p1;

Eğer P1 parametresinin değerini dba_data_files içerisinde göremiyorsanız, file muhtemelen temp filedir yada dba_files parametresinden değeri büyüktür. Bu durumda şağıdaki sql i kullanarak bloğu içerisinde barından datafileyi bulabilirsiniz.

SELECT tablespace_name, file_name 

    FROM dba_temp_files f, v$parameter p

   WHERE p.name='db_files'

     AND f.file_id+p.value = &v$session.p1;

Yada Aşağıdaki sql ile segment tipi, segment sahibi ve segment ismini belirleyebilirsiniz.

SELECT  owner , segment_name , segment_type 

    FROM  dba_extents

   WHERE  file_id = &v$session.p1

     AND  &v$session.p2 BETWEEN block_id AND block_id + blocks -1

Aşağıdaki sql yardımıyla hangi dosyaların en fazla beklenen blokları içerdiğini görebilirsiniz.

SELECT count, file#, name

    FROM x$kcbfwait, v$datafile

   WHERE indx + 1 = file#

   ORDER BY count;

Buffer Busy Wait Tuning Nasıl Yapılır?

Buffer Busy Wait çeşitli bloklara erişim sırasında yaşanan problem durumudur. Şöyleki bir session bazı blokları okumak için memory ‘ e alırken yada memorydeki boğu değiştirirken  diğer bir sessin bu blok ile ilgili işlemini yapmak için önce diskten okuma yapıyor ve okuduğunu memory’e almak için buffer bekliyor. İşte bu durumda ilk çıkış noktamız hangi bloğun beklendiğini keşfetmek olmalıdır.

Hangi tip blok üzerinde işlem yaparken buffer busy wait event oluşuyor? Bu sorunun cevabı eventi oluşturan sessiona ait p1,p2,p3 değerleri ile  yukarıda verdiğimiz sqlleri kullanarak hangi bloğun beklendiğini bulmak olacaktır. Ayrıca sistemde genel olarak ne tip bloklar bekleniyor aşağıdaki sorgu sonucunda görebilirsiniz.

SELECT *

  FROM v$waitstat

 WHERE COUNT > 0 order by 2 desc;

                   

                    İyileştirme çalışmaları içinde  aşağıda en sık görülen blok tiplerine gore belirtilen çalışmalar yapılabilir.Buffer busy wait hangi blok tipinde sıkça görüldüğünüde aşağıdaki sql ile hızlıca görebiliriz.

SELECT 'Segment Header' class,

       a.segment_type,

       a.segment_name,

       a.partition_name

  FROM dba_segments a, v$session_wait b

 WHERE a.header_file = b.p1

   AND a.header_block = b.p2

   AND b.event = 'buffer busy waits'

UNION ALL

SELECT 'Freelist Groups' class,

       a.segment_type,

       a.segment_name,

       a.partition_name

  FROM dba_segments a, v$session_wait b

 WHERE b.p2 BETWEEN a.header_block + 1

                AND (a.header_block + a.freelist_groups)

   AND a.header_file = b.p1

   AND a.freelist_groups > 1

   AND b.event = 'buffer busy waits'

UNION ALL

SELECT a.segment_type || ' block' class,

       a.segment_type,

       a.segment_name,

       a.partition_name

  FROM dba_extents a, v$session_wait b

 WHERE b.p2 BETWEEN a.block_id AND a.block_id + a.blocks - 1

   AND a.file_id = b.p1

   AND b.event = 'buffer busy waits'

   AND NOT EXISTS

          (SELECT 1

             FROM dba_segments

            WHERE header_file = b.p1 AND header_block = b.p2);

                   

                    Data block Contention: Data block la rile ilgili işlemlerde iki durum için farklı çözümler mevcuttur.Şimdi bu durumları açıklayalım.

                    Durum 1:Birden çok session aynı anda ve aynı data bloğuna erişmek istiyorsa ve bu data bloğuda memoryde mevcut değilse, bu data bloğu diskten okunmaya başlar ve bloğu okumaya çalışan diğer sessionlar için buffer busy wait event meydana gelir ve beklemeye girerler.

                                Çözüm Önerileri;

                    1-Uygulamanın level of concurrency (aynı anda ddl ve dml çalıştırma yoğunluğu) durumunun azaltılması. Ancak bu Pratik olarak kolay bir işlem değildir.Uygulamanın mantığı değişeceği için bu durumda uygulama yavaşlayabilir.

                    2-Buffer busy wait evente sebep olan sqllerin incelenmesi ve tune edilemsi etkili bir çözüm olarak karşımıza çıkar.Bu sqlleri tune ederken physical and logical reads (diskten ve memoriden okuma oranlarının azaltılması) azaltılmasına odaklanılmalıdır.

                    3-Tablolardaki FREELIST ve FREELIST GROUP saysısının artırırlmasıda performansda önemli bir pozitif etki sağlayacaktır.Bu işlemi sonra değineceğimiz segment header ile ilgili kısımda daha detaylı görebilirsiniz.

                    Durum2:Birden çok session buffer cache de mevcut olan bir data bloğunun içindeki farklı rowları değişitirmek isterlerse, işlem yapan session dışındaki sessionlar için buffer busy wait event meydana gelir ve beklemeye girerler.

                    1-Durum 1 deki level of concurrency azaltılması yada partition metodunun değiştirilmesi fayda sağlayacaktır.

                    2-Eğer database bloksize 16K gibi büyükse bir bloktaki row sayısının azaltılması effektif bir çözüm olacaktır.Bunun için tablonun PCTFREE değerinin artırılması çözüm olabilir.PCTFREE default değeri %10 dur. Yada ALTER TABLE table_name MINIMIZE_RECORD_PER_BLOCK komutu çalıştırılabilir. Ayrıca tablonun Initrans değerini artırmayı deneyin ancak 100 den Fazla yapmayın. Default değeri tablolar için 1 indexler için 2 dir.

                    3-Objeyi başka bir tablespacede daha küçük blok size ile create etmekte etkili  bir çözüm olacaktır.

                    Segment Header Contention;Eğer buffer busy wait event data segment headerler üzerindeki yoğunlaşıyorsa bu durumda data segment headerlada yoğun işlemler oluştuğu durumu ortaya çıkar.

                                Çözüm Önerileri;

                    1-Buffer busy wait eventin oluştuğu objelerin freelist ve freelist group sayılarının artırılması çözüm olacaktır.Öncelikle yukarıdaki sql ile segment adı ve segment tipi belirlenmelidir.Sonrasında en azından iki adet freelist groubu olacak şekilde obje tekrar oluşturulmalı ve freelist sayısıda artırılmalıdır.

                    Aşağıda tablo 1 de freelist ve freelist_group syaıları değiştirilmiştir.Bu durumda data blok ve segment header contention miktarlarında ve bekleme sürelerindeki değerleri inceliyiniz.

V$WAITSTAT	Test #1 Freelists = 1 Freelist Groups = 1		Test #2 Freelists = 12 Freelist Groups = 1		Test #3 Freelists = 4 Freelist Groups = 3
Class	Count	Time	Count	Time	Count	Time
data block	656432	534104	9299	6986	3045	1447
sort block	0	0	0	0	0	0
save undo block	0	0	0	0	0	0
segment header	384272	150444	72337	110850	79	6
save undo header	0	0	0	0	0	0
free list	0	0	0	0	8400	8503
extent map	0	0	0	0	0	0
1st level bmb	0	0	0	0	0	0
2nd level bmb	0	0	0	0	0	0
3rd level bmb	0	0	0	0	0	0
bitmap block	0	0	0	0	0	0
bitmap index block	0	0	0	0	0	0
file header block	1	0	0	0	0	0
Unused	0	0	0	0	0	0
system undo header	0	0	0	0	0	0
system undo block	0	0	0	0	0	0
undo header	2388	36	166	18	155	51
undo block	0	0	0	0	0	0

Tablo1:freelist and  freelist group test.

                    2-PCTFREE ve PCTUSED değerlerini artırmayı deneyin. PCTUSED değeri bir bloğun olabileceği maximum doluluk oranıdır. Default değeri 40 dır. Bu şu demek olurki bir  bloğa yeni very girilmesi için bloğun %40 dan daha Fazla dolu olmamalıdır. PCTFREE ise blok içindeki verilere gelebilecek updateleri control edebilebilmek için ayrılmış boş alandır. Default değeri 10 dur.

                    3-Next extent size ın çok küçük olmadığından emin olun.

                    Ayrıca tablo 1 de gördüğünüz 1^st level bmp, 2^nd level bmp ve 3^rd level bmp contentionlar görüyorsanız bu contentionlar ASSM (Automatic segment space management) ile ilgilidir ve çözüm için metalinkten bilinen bugları araştırmalısınız yada support almalısınız.

                    Undo Segment  Header Contention: Freelist sayısını artırmayı deneyin ve freelist gruplarını kullanın.Daha Fazla rollback segment create etmeyi deneyin.

                    Undo Block Contention:Undo block contention için eğer undo tablespace ve undo retention yeterince buyuk ise veritabanında yapılabilecek bir çalışma maalesef bulunmamaktadır.Undo block contention uygulamanın aynı anda update çalıştıran çok Fazla sessiona sahip olduğunu gösterir.Uygulama farklı zamanlarda dml ve query çalışmaya yönelik geliştirilmelidir.

                    Freelist Contention: Freelist sayısını artırın. Eğer parallel serverlar kullanıyorsanız herbir serverın kendi freelist gubu olduğundan emin olun.

                                                                                                Özcan YILDIRIM