Önbellekte gereksiz blok işgal eden indeksler

SQL cümlesinde kullanılan indeks tarafından çağrılan bloklar gerçekten SQL performansını iyileştirmektemi? Buna kesin olarak evet demek mümkün değil. Önbellekte blok işgal eden segmentlerin sahiplerini, tiplerini ve isimlerini bilmek SQL iyileştirme çalışmalarında oldukça kullanışlıdır. Bunun yanında hangi objelerin en fazla önbelleği işgal ettiğini yüzdesel olarak bilmekte SQL iyileştirme çalışmalarında nereye öncelikli olarak odaklanmamız gerektiğini bize gösterecektir. Bu sebeple öncelikle önbellekte o an bulunan indeksleri gözlemlemek gerekir. Burdaki soru, bu indeksler ilişkli tablo tarafından gerçekten kullanılmaktamıdır?
SQL cümlelerinde ilgili indeksin kullanımını zorlayan hintlerin kullanıldığı durumlarda, bu indeksler sonucu önbellekte blok işgali en önemli bekleme olaylarından birisi olmaktadır. Eğer bu indeksler sorgu işlemlerinde seçimi olmayan indeksler ise, başka segmentlerin tablolarına ait gerekli bloklara önbellekte yeterli yer kalmadığından “buffer busy waits”  bekleme olayı meydana gelebilecektir, bu da sorgu cevap süresini uzatacaktır.

Önbelleği gereksiz yere işgal eden ve ilgili sorgu sonucunda kullanım gereksinimi olmayan indekslerin ne kadar yer işgal ettiğini ve bunların hangi indeksler olduğunu bulmak için V$SQLTEXT görünümüne sorgu çekilebilir.

Bilhassa, son bir yıldır Oracle veritabanı performans iyileştirme uzmanı olarak görev aldığım çalışmalarda önbellek ve SQL iyileştirme çalışmalarında oldukça faydalı sonuçlar veren bu 3 sorgu alttadır. Program geliştirici ve rapor geliştirici gibi farklı iş kollarına sahip ve her iki işkolununda birbiriyle pek fazla işbirliği içinde olmayan ortamlarda Oracle Tuning Advisor uzmanlarının işi, gereksiz indekslerin bilhassa hintler ile SQL cümlesi içinde çağrılması sebebiyle gerçekten zor olmakta ve gerek program geliştiricilere gerekse rapor geliştiricilere kendilerinden kaynaklanan bu önbellek tabanlı darboğazları anlatması zahmetli olmakta ve bu noktada “kötü adam” olmamak adına somut analiz sonuçları ile birlikte çözüm önerileri sunmak önem kazanmaktadır.

Önbellekte blok işgal eden segmentleri görmek için;

SELECT o.*, d_o.owner, d_o.object_name, object_type, o.buffers, o.avg_touches

FROM ( SELECT obj object, count(1) buffers, AVG(tch) avg_touches

FROM X$bh

GROUP BY obj) o,

dba_objects d_o

WHERE o.object = d_o.data_object_id

ORDER BY owner, object_name;

Segmentlerin önbellekte blok işgal yüzdesini görmek için;

SELECT tot_occ_bufs.TotOccBufs,o.*,d_o.owner, d_o.object_name, object_type,

ROUND((o.buffers/tot_occ_bufs.TotOccBufs)*100,2) || '%' PctOccBufs

FROM (SELECT obj object, count(1) buffers, AVG(tch) avg_touches

FROM X$bh

GROUP BY obj) o,

(SELECT COUNT(1) TotOccBufs

FROM X$bh

WHERE state != 0) tot_occ_bufs,

dba_objects d_o

WHERE o.object = d_o.data_object_id

ORDER BY round((o.buffers/tot_occ_bufs.TotOccBufs)*100,2),owner, object_name;

Farklı blok büyüklüklerinin kullanıldığı segmentler için KEEP ve RECYCLE havuzlarının kullanımı Oracle 9i den itibaren mümkün olmadığından, bu tür farklı blok büyüklüğüne sahip segmentlerin önbellekte ne kadar blok işgali yaptığını gözlemlemek için alttaki sorgu kullanılmaktadır.

SELECT DECODE(wbpd.bp_id,1,'Keep',

2,'Recycle',

3,'Default',

4,'2K Pool',

5,'4K Pool',

6,'8K Pool',

7,'16K Pool',

8,'32K Pool',

'UNKNOWN') Pool,

bh.owner, bh.object_name object_name, count(1) NumOfBuffers

FROM X$kcbwds wds, X$kcbwbpd wbpd,

(SELECT set_ds,x.addr,o.name object_name,

u.name owner

FROM sys.obj$ o,

sys.user$ u, X$bh x

WHERE o.owner# = u.user#

AND o.dataobj# = x.obj

AND x.state !=0

AND o.owner# !=0 ) bh

WHERE wds.set_id >= wbpd.bp_lo_sid

AND wds.set_id <= wbpd.bp_hi_sid

AND wbpd.bp_size != 0

AND wds.addr=bh.set_ds

GROUP BY DECODE(wbpd.bp_id,1,'Keep',

2,'Recycle',

3,'Default',

4,'2K Pool',

5,'4K Pool',

6,'8K Pool',

7,'16K Pool',

8,'32K Pool',

'UNKNOWN'), bh.owner,bh.object_name

ORDER BY 1,4,3,2;