Yaygın Oracle Bekleme Olayları ve İyileştirme Tavsiyeleri

Ugur INAL

17/04/2011

Bu yazıda, Oracle veritabanında performansı önemli derecede azaltan belli başlı “Oracle bekleme olaylarını” neler olduğu, bu bekleme olaylarını kabul edilir seviyelere çekmek için yapılması gereken eylemleri tavsiyeler ışığında inceleyeceğiz. Oracle veritabanı kullanan her veritabanı uzmanının mutlaka karşı karşıya kaldığı bu bekleme olaylarının hangilerinin veritabanı yönetimindeki eksikliklerden oluştuğunu, hangilerinin kullanılan programın yapısal eksikliklerinden olduğunu karşılaştıracağız. Ayrıca Dataguard veya RAC tabanlı bekleme olayları bu yazıda yer almadığından bu yapılarda Oracle veritabanı hizmeti kullanmayan pek çok firmanın veritabanı yöneticisi için faydalı bir yazı olacağını düşünüyorum.

Ayrıca bu yazıdaki bekleme olaylarını tespit etmek için SQL*Plus ekranından çalıştırılacak SQL cümleleri ile sorun tespiti vasıtasıyla, karışık analiz işlemleri/programları kullanım ihtiyacı ortadan kaldırılmıştır. Kullanılan analiz scriptleri tüm Oracle sürümleri ile uyumludur.

Belli başlı Oracle bekleme olayları aşağıda yer almaktadır.

db file sequential reads
db file scattered reads
log file parallel write
log file sync
buffer busy waits
free buffer waits
enqueue waits
cache buffer chain latch

Şimdi bu yukardaki bekleme olaylarını tek tek açarak, ne anlama geldiklerine, bunlara nelerin sebep olduğuna ve iyileştirme tavsiyelerine bakalım.

Bekleme Olayı: db file sequential reads
Üst sınır: V$SYSTEM_EVENT içinde ortalama bekleme süresi 15ms yi geçmemeli

Muhtemel sebepler:

Yanlış index kullanımı
Fragmente olmuş indexler
Belirli disk veya raw partitionlar üzerinde aşırı I/O trafiği
Kullanılan programda tasarım eksikliği
Index okuma performansı yüksek oranda ortalama bekleme süresine sebep olan yavaş I/O altsistemi ve/veya veritabanı dosyalarının fiziksel yerleşimindeki eksikliklerden etkilenmektedir.

Alınacak eylemler:

Tablodaki indexleri kontrol ederek doğru indeksin kullanıldığından emin olun. Bu noktada önbellekteki işlemlerden hangi indekslerin kullanılmadığını analiz edebilir ve gereksiz indeksleri bu şekilde düzeltebiliriz(Önbelleği gereksiz işgal eden indeksleri görmek için alttaki sorgu kullanılabilir)

select distinct b.owner, b.segment_name
from x$bh a, dba_extents b
where b.file_id=a.dbarfil
and a.dbablk between b.block_id
and b.block_id+blocks-1
and segment_type=’INDEX’
and b.owner = upper(‘&OWNER’)

Top SQL listesinden tabloda kullanılan indekslerin WHERE kısmındaki kolonların order sırasını kontrol ediniz.
Indeksleri yüksek kümeleme faktöründe tekrar oluşturun.
Ziyaret edilen blok miktarını azaltmak için bölümlendirme(partitioning) kullanın.
İyileştirme istatistiklerinin güncel olduğundan emin olun.
Yüksek I/O trafiğine sahip olan veri dosyalarını farklı veri dosyalarına taşıyın(Dengesiz I/O yükünü veridosyası bazında görmek için alttaki sorgu kullanılabilir)

select d.name,s.PHYRDS,s.PHYWRTS
from v$datafile d, v$filestat s
where d.file#=s.file#
order by 1

Çoklu bellek havuzlarını kullanmayı düşünün ve sık kullanılan indeks ve tabloları KEEP havuzunda tutun(SQL alanında sık işlem gören ancak KEEP havuzunda tutulmayan tabloları görmek için alttaki sorgu kullanılabilir)

SELECT o.owner, object_name, object_type, COUNT(1) buffers
FROM SYS.x$bh, dba_objects o
WHERE tch > 10
AND lru_flag = 8
AND obj = o.object_id
AND o.owner = upper(‘&OWNER’)
GROUP BY o.owner, object_name, object_type
ORDER BY buffers;

İndeksler üzerinden veri erişimi sağlayan SQL cümleciklerinin işlem planlarını araştırın.
İndeksler üzerinden veri erişimini mümkün olduğunca fazla sağlayın.
Kullandğınız program OLTP mi veya DSS mi? Full tablo taramasımı daha verimlidir? SQL cümleciklerinde doğru “driving” tablolar kullanılıyormu? sorularının cevaplarını analiz edin.
İyileştirmenin hedefi hem mantıksal hemde fiziksel I/O trafiğini azaltmaktır. Bu noktada öncelikle yeterli önbelleğin olduğundan ve hızlı fiziksel sistemlerde ve doğru RAID yapısında çalıştığınızdan emin olun.

Tavsiyeler:

Oracle prosesi, SGA alanı içinde o an bulunmayan bloğu ister ve diskten SGA alanına bu bloğun okunması için bekler.
Sabit orandaki db file sequential read bekleme zamanını azaltmak için yapılan tüm iyileştirme işlemleri sonucunda bu oran hala yüksekse programınızda iyileştirmeye odaklanmanız gerekir.
Eğer indeksin DBA_INDEXES.CLUSTERING_FACTOR değeri tablodaki blokların sayısına yaklaşırsa bu durumda tablodaki pekçok satır sıralanmıştır. Bu arzulanan bir sonuç olacaktır.
Ancak, eğer kümeleme faktörü tabloadaki satır sayısına yaklaşırsa bu demektirki tablodaki pek çok satır rastgele sıralanmıştır ve bu sebeple bu operasyonu tamamlamak için çok daha fazla I/O meydana gelecektir. İndeksin kümeleme faktörünü iyileştirmek için tablo tekrar oluşturulabilir, böylece satırlar indeks anahtarına gore sıralanacak ve sonrasında indeksler tekrar oluşturulacaktır.OPTIMIZER_INDEX_COST_ADJ ve OPTIMIZER_INDEX_CACHING başlangıç parametreleri “nested loop” işlemlerini desteklemek için iyileştiriciyi etkiler ve full tablo taraması üzerinden indeks erişim yolunu seçer.
I/O tabanlı iyileştirmeler için Metalink Note# 223117.1 a bakınız.
Daha fazla db file sequential read iyileştirme metotları için Metalink Note# 34559.1 a bakınız.

Bekleme Olayı: db file scattered reads

Muhtemel sebepler:

Oracle oturumu çoklu komşu veritabanı bloklarını(DB_FILE_MULTIBLOCK_READ_COUNT değerine kadar) disk alanından SGA’ya okunmak için beklemektedir.
Çok fazla full tablo taramaları (FTS)
Çok fazla hızlı full indeks taramaları
Bellek havuzunda en fazla FTS ve/veya full indeks taramasına sebep olan 20 top obje alttaki sorgu ile bulunabilir.

SELECT * FROM
(SELECT SUBSTR(O.OWNER, 1, 15) OWNER,
SUBSTR(O.OBJECT_NAME, 1, 35) OBJECT,
COUNT(*) BLOCKS,
DECODE(O.OBJECT_TYPE, ‘TABLE’, ‘FULL TABLE SCAN’,
‘INDEX’, ‘FAST FULL SCAN’,
‘OTHER’) “SCAN TYPE”
FROM DBA_OBJECTS O, X$BH B
WHERE B.OBJ = O.DATA_OBJECT_ID AND
STANDARD.BITAND(B.FLAG, 524288) > 0 AND
O.OWNER != ‘SYS’
GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE
ORDER BY COUNT(*) DESC)
WHERE ROWNUM <=20;

Hangi 20 SQL cümlesinin aşırı FTS ve/veya aşırı full indeks taramasına sabep verdiği ise alttaki sorgu ile bulunabilir.

SELECT * FROM
(SELECT SUBSTR(SA.SQL_TEXT, 1, 68) SQL_TEXT,
SA.DISK_READS DISK_READS
FROM V$SQLAREA SA WHERE
(SA.ADDRESS, SA.HASH_VALUE) IN
(SELECT ADDRESS, HASH_VALUE FROM V$SQL_PLAN
WHERE OPERATION = ‘TABLE ACCESS’ AND
OPTIONS = ‘FULL’ OR
OPERATION = ‘INDEX’ AND
OPTIONS LIKE ‘FAST FULL%’)
ORDER BY 2 DESC)
WHERE ROWNUM <=20;

Alınacak Eylemler:

DB_FILE_MULTIBLOCK_READ_COUNT parametresini ayarlayarak çoklu blok I/O işlemlerini optimize edin.
Bölümlendirmeleri alt bölümlere budamak ziyaret edilen bloklarının sayısını azaltır.
Çoklu tampon havuzları kullanmayı düşünün ve sık kullanılan indeks ve tabloları KEEP pool içinde saklayın.
En çok bekleme olaylarını başlatan SQL cümlelerini iyileştirin. Burdaki amaç fiziksel ve mantıksal okuma sayısını azaltmaktır.
SQL sonucu erişilen data indeks FFSmi yoksa full tablo taraması sonucumu gelmeli?
İndeks aralığı veya unique taramamı en etkili olacaktır ?
Sorgu doğru “driving” tabloyu kullanıyormu?
SQL cümleleri ilgili hash veya ilgili merge birleşmelerine dayanıyormu?
Eğer full taramalar(FS) uygunsa parallel sorgu cevap süresini iyileştiriyormu?
Amaç, hem fiziksel hemde mantıksal I/O lar için istekleri azaltmaktır.Buna erişmek için en iyi yol SQL ve program iyileştirmesi olacaktır.
LAST_ANALYSED tarihini control ederek tüm istatistiklerin güncel veriyi temsil ettiğinden emin olun.

Bekleme Olayı: log file parallel write

Muhtemel sebepler:

LGWR redo log önbellek içeriğini online log dosyalarına yazarken bekler.
I/O, online redo log dosyalarını tutarken altsistemde bekler.

Alınacak Eylemler:

Oluşturulan redo miktarını azaltın.
Çok gerekmedikçe tablespaceleri sıcak yedekleme(hot backup) modunda bırakmayın.
Redo log dosyaları için RAID 5 kullanmayın, RAID 1 tercih edilebilir.
Redo log dosyaları için daha hızlı diskler kullanın.
İçeriklerin çakışmaması için arşiv redo log dosyaları ve online redo log dosyalarının birbirlerinden farklı disklerde tutulduğundan emin olun.
SQL cümleciklerinde gerektiğinde NOLOGGING veya UNRECOVERABLE seçeneklerini kullanmayı dikkate alın.

Bekleme Olayı: log file sync

Üst sınır: V$SYSTEM_EVENT içinde ortalama bekleme süresi 15ms yi geçmemeli

Muhtemel sebepler:

Oracle arkaplan prosesleri COMMIT veya ROLLBACK lerden birini sonuçlandırmak için beklemektedir.

Alınacak Eylemler:

LGWR prosesini disklere doğru iyi çıktı almak amacıyla iyileştirin.Örneğin, redo logları RAID 5 te tutmayın, RAID-1 tercih edin.
İşlemleri yığınlayarak tüm COMMIT sayısını azaltın ki daha az sayıda COMMIT işlemleri olsun..

Tavsiyeler:

Bakınız Metalink Reference Note# 34592.1
Log dosyası senkronizasyonunda yüksek beklemeleri optimize etmek için bakınız Metalink referans Note# 125269.1
Redo log önbellek iyileştirmesi ve redo latch içeriği iyileştirmesi için bakınız Metalink Referans Note# 147471.1

Bekleme Olayı: buffer busy waits

Üst sınır: V$SYSTEM_EVENT içinde ortalama bekleme süresi 45ms yi geçmemeli

Muhtemel sebepler:

Yoğun bellek beklemeleri I/O sekmeli Oracle sistemlerinde olağandır.
Bu olayın meydana geldiği 2 temel neden vardır: Başka bir oturum bloğu bellek içinden okumaktadır veya başka bir oturum isteğimize karşılık belleği uyumsuz bir modda tutmaktadır.
Bu beklemeler okuma/okuma,okuma/yazma veya yazma/yazma içeriklerine işaret eder.
Oracle oturumu belleğe iliştirmek için beklemektedir. Bellek okunmadan veya değiştirilmeden once iliştirilmelidir.Sadece bir proses belleği herhangi bir zamanda iliştirebilir.
Bu beklemeler daha çok satır içerecek kadar daha geniş blok büyüklüklerince pekiştirilir.
Bu beklemeler bir oturum önbellek içindeki bir veritabanı bloğuna erişmek istediğinde ancak bellek meşgul olup erişemediğinde meydana gelir. Sık sık ilişkili bloklarda bu beklemeler oluşuyorsa hangi ilişkili segmentlerin buna sebep olduğunu bulmak için alttaki sorguyu çalıştırın. HDR kolonu bunun header blok olup olmadığını gösterecektir.

SELECT SUBSTR(SEGMENT_NAME, 1, 30), SEGMENT_TYPE,
DECODE(<block#> – BLOCK_ID + EXTENT_ID, 0, ‘YES’, ‘NO’) HDR
FROM DBA_EXTENTS
WHERE FILE_ID = <file#> AND
<block#> BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS – 1;

Hangi data dosyalarının buffer busy waits olayına sebep olduğunu görmek için ise alltaki sorgu faydalıdır.

SELECT * FROM
(SELECT COUNT, FILE#, SUBSTR(NAME, 1, 50) FILE_NAME
FROM X$KCBFWAIT, V$DATAFILE
WHERE INDX + 1 = FILE#
ORDER BY COUNT DESC)
WHERE ROWNUM <=20;

Birkaç proses aynı bloğu tekrar tekrar okuduğundan ötürü bu beklemeler sık sık olur. Mesela, farklı kullanıcılar aynı anda aynı bloğa erişim sağlıyorsa.

Alınacak Eylemler:

Yoğun bellek bekleme sayısını azaltmanın ana yolu sistem üzerindeki toplam I/O yu azaltmaktır.Blok tipine bağlı olarak uygulanacak eylemler aşağıdaki gibi farklıdır.

Data blokları için;

Çok fazla erişilen blokları programınızdan çıkartın.
Tekrarlı tarama/seçimsiz indeksleri kontrol edin.
Objeleri daha yüksek PCTFREE oranlarında yeniden oluşturun, böylece blok başına satır sayısını azaltmış olursunuz.
Farklı prosesler tarafından indekslerdeki aynı lokasyona eklenen indeksleri kontrol edin.
INITRANS ve MAXTRANS değerlerini artırın, PCTUSED değerini azaltın.Bu şekilde tablolar daha az sıkışır.
Blok başına satır sayısını azaltın.
db_block_buffers başlangıç parametresi değeri artırılabilir.

Segment Başlığı

FREELISTS ve FREELIST GROUP sayısını artırın.

Undo Başlığı

Rollback segment sayısını artırın.

Tavsiyeler:

buffer busy waits olayında beklemede olan prosesin sebep kodu bekleme olayının P3 parametresinde yayınlanır.
En sık karşılaşılan bekleme olaylarından olan 130 ve 220 arası referanslar için Metalink Note # 34405.1 bakınız.
Yoğun ve rastgele bellek bekleme performans problemleri için Metalink Note# 155971.1 bakınız.

Bekleme Olayı: free buffer waits

Muhtemel sebepler:

Boş tampon beklenmekte ancak bellekte çok fazla kirli tamponlar yüzünden bellekte yeterli boş alan yok.
Ya önbellek çok küçük veya DBWR değiştirilmiş tamponları diske yazmak için çok yavaş kalmakta.
DBWR yazma taleplerini sürdürememekte.
Muhtemelen yetersiz büyüklükte redo log dosyaları veya yüksek veritabanı işlemleri sebebiyle çok fazla checkpoint meydana gelmekte.
Geniş sort ve full tablo taramaları, tamponu DBWR prosesinin diske yazabileceğinden çok daha hızlı şekilde değişime uğramış bloklarla doldurmakta.
Eğer kirli tamponların sayısı DBWR prosesinin yığın başına yazabileceği miktardan daha fazla diske yazma ihtiyacı duyarsa, bu bekleme olayı oluşabilir.

Alınacak Eylemler:

Checkpoint sıklığını azaltın. Online redo log dosyası büyüklüğünü artırın.
Tampon bellek büyüklüğünü sorgulayın. SGA alanında tampon belleği artırmayı değerlendirin.
disk_asynch_io = true
DBWR prosesi sayısını CPU sayısına bağlı olarak arttırın.
DBWR prosesi çok fazla bu bekleme olayına sebep oluyorsa, FAST_START_MTTR_TARGET değerini azaltıp deneme yapın. Ancak şunu unutmayın bu parametre azaltılırsa DBWR üstünde çok aşırı yazma yoğunluğu oluşur.
Veri dosyalarını diskler veya disk kontroller üzerine serpiştirerek sıcak noktaların olmadığından emin olun.
Önceden sort işlemleri ve verileri yeniden organize etmek yardımcı olabilir.

Tavsiyeler:

Önbellek ve DBWR yi anlamak ve iyileştirmek için bakınız Metalink Note# 62172.1
Önbellek içinde sıcak blokların nasıl belirleneceği için bakınız Metalink Note# 163424.1

Bekleme Olayı: enqueue waits

Muhtemel sebepler:

Talep edilen moda karşılık, uyumsuz bir mod içinde başka oturumlar tarafından tutulan kilitler sebebiyle kuyruk sonuna atılan bekleme olayıdır. Bekleyen kuyruğa alma kilitleri tespit etmek için alttaki sorgu kullanılabilir.

select /*+ ordered */
l.type || ‘-‘ || l.id1 || ‘-‘ || l.id2 “RESOURCE”,
nvl(b.name, lpad(to_char(l.sid), 4)) sid,
decode(
l.lmode,
1, ‘      N’,
2, ‘     SS’,
3, ‘     SX’,
4, ‘      S’,
5, ‘    SSX’,
6, ‘      X’
) holding,
decode(
l.request,
1, ‘      N’,
2, ‘     SS’,
3, ‘     SX’,
4, ‘      S’,
5, ‘    SSX’,
6, ‘      X’
) wanting,
l.ctime seconds
from sys.v_$lock l, sys.v_$session s, sys.v_$bgprocess b
where s.sid = l.sid and
b.paddr (+) = s.paddr
order by   l.type || ‘-‘ || l.id1 || ‘-‘ || l.id2,   sign(l.request), l.ctime desc

TX İşlem Kilidi

Genellikle tablo oluşum kaynaklı hatalar veya program geliştirmedan kaynaklanan hatalar
Satır bazlı kilitler kısmındaki çakışmaları belirtir.
Bir işlem başka bir işlem tarafından kilitlenmiş satırları güncellemeye veya silmeye çalıştığından bu bekleme oluşur.
Bu bekleme olayı genellikle program katmanındaki sorunlardan kaynaklanır.

TM DML Kuyruksonu Kilidi

Genelikle yabancı anahtarların tek tek indekslenmediği durumlarda program katmanındaki sorunlardan kaynaklanır. Bu tarz kilitleri nelerin tetiklediğini belirlemek için alttaki sorgu kullanılabilir.

select /*+ ordered */
n.name constraint_name,
u.name ||’.’|| o.name table_name,
c.name column_name
from
(
select /*+ ordered */ distinct
cd.con#, cd.obj#
from sys.cdef$ cd, sys.tab$ t
where
cd.type# = 4 and   — foriegn key
t.obj# = cd.robj# and
bitand(t.flags, 6) = 0 and
not exists (   — not indexed
select null
from
sys.ccol$ cc, sys.ind$ i, sys.icol$ ic
where
cc.con# = cd.con# and
i.bo# = cc.obj# and
bitand(i.flags, 1049) = 0 and
ic.obj# = i.obj# and
ic.intcol# = cc.intcol#
group by
i.obj#
having
sum(ic.pos#) = (cd.cols * cd.cols + cd.cols)/2
)
) fk,
sys.obj$ o, sys.user$ u, sys.ccol$ cc, sys.col$ c, sys.con$ n
where
o.obj# = fk.obj# and
o.owner# != 0 and   — ignore SYS
u.user# = o.owner# and
cc.con# = fk.con# and
c.obj# = cc.obj# and
c.intcol# = cc.intcol# and
n.con# = fk.con#
order by

ST Kilidi

Drop, truncate veya coalesce gibi eylemler içeren ST kilidine gereksinim duyan UET$(kullanılmış extent) ve FET$(boş extent) tablolarını değiştiren veritabanı işlemleri.
ST kilitlerindeki çakışmalar dictionary yönetimli tablespace yapısında birden fazla oturumun aktif olarak dinamik disk alanını paylaştığı yada serbest bıraktığını işaret eder.

Alınacak Eylemler:

Bekleme ve bekleme sürelerini azaltın. TX kuyruk tabanlı kuyruğa alma beklemeleri gördüğünüzde ilk adım olarak bloklayanın kim olduğunu bulun ve aynı kaynak için birden fazla bekleyen olup olmadığını araştırın.
Mod 3 içindeki TM kuyruk beklemeleri öncelikli olarak indekslenmemiş yabancı anahtar kolonları olmasından kaynaklıdır.
Oracle 9i için yabancı anahtarlarda indeks oluşturun.
Lokal yönetimli tablespace kullanın.
Tüm geçici tablespacesleri yeniden oluşturun. CREATE TEMPORARY
TABLESPACE TEMPFILE…

Tavsiyeler:

Eşzamanlı kilitleyebilmede önkuyruk sayısını maksimum sayıda kontrol etmek için ENQUEUE_RESOURCES parametresi kullanılır. Bakınız Metalink Reference Note# 34566.1
Kuyruk sonunda bekleyen oturumları izlemek için bakınız Metalink Note# 102925.1
V$LOCK görünümü ve kilitleme modları için bakınız Metalink Note:29787.1

Bekleme Olayı: cache buffer chain latch

Muhtemel sebepler:

Bu mandal(latch) data blokları için arama yapılırken elde edilir. Önbellek blok sırasıdır ve taramaya ihtiyaç duyulduğunda her bir sıra alt mandal tarafından korunur.
Bu bekleme olayının bir diğer ortak sebebi ise sıcak bloklardır. Birden fazla oturum bu bekleme olayının alt sırasındaki bir veya daha fazla bloğa tekrar tekrar erişiyorsa bu bekleme olayı meydana gelir.
EXECUTION başına yüksek BUFFER_GETS(mantıksal okuma-bellekten) değerine sahip SQL cümleleri baş suçludur. Önbellkte çok fazla yer meşgul eden SQL cümlelerini görmek için alttaki sorgu kullanılabilir.Örnekte 5MB’den fazla BUFFER_GETS sebep olan cümleler listelenir.

SELECT substr (sql_text, 1,100) “Stmt”, count (*) “Count”,
round(sum (sharable_mem)/1024/1024,2) ||’ MB’ “Mem”,
sum (users_opening) “Open”,
sum(parse_calls) “Hard Parses”,
sum (executions) “Exec”
FROM v$sql
GROUP BY substr (sql_text, 1,100)
HAVING round(sum (sharable_mem)/1024/1024,2)>5;

Birden fazla eşzamanlı SQL oturumu aynı veri setini hedef gösteren yetersiz SQL leri yürütmektedir.

Alınacak Eylemler:

Bu bekleme olayı için çakışmaları azaltmak genellikle iyileştirme ile mantıkssal I/O oranını azaltmaya ve SQL ilişkili I/O gereksinimini azaltmaya ihtiyaç duymaktadır. Yüksek I/O oranı çok fazla erişilen blokları işaret eder.
Tabloyu export edin, PCTFREE oranını anlamlı olarak arttırın ve verileri import edin.Bu metot blok başına satır sayısını azaltacak ve bu satırları pek çok blok üzerine dağıtıcaktır. Tabi bu da depolama alanı ihtiyacını artıracak ve full tablo taramaları yavaşlayacaktır.
Tabloda blok başına kayıt sayısını minimize edin.
Indeksler için bunları daha yüksek PCTFREE değerlerinde yeniden oluşturun, tabi bunun indeksin yüksekliğini artıracağını akılda tutarak.
Blok boyutunu azaltmayı gözönünde bulundurun.
İndex ve buna bağlı tabloların aynı blok boyutunda olduğundan emin olun.
Hash latch sayısı, DB_BLOCKS_HASH_LATCHES parametresi ile değiştirilebilir.

Makalemizin sonuna geldik, umarım faydalı bir makale olmuştur. Bir sonraki makalemizde görüşmek üzere