Clustering factor hakkında bir inceleme

ERP alt sisteminde geçtiğimiz aylarda indeksler için yapılan iyileştirme çalışması sonucunda, bazı indekslerin hala oldukça yüksek “clustering_factor” değeri ürettiğini gözlemledim. Aşağıda bu sorunlu indekslerden birisi yer almakta.

            BTREE   LEAF    DISTINCT  CLUSTERING  INDEX      TABLE     TABLE

INDEX NAME  LEVEL  BLOCKS   KEYS      FACTOR      NUM ROWS   NUM BLKS  NUM ROWS

----------  -----  ------  ---------- ----------  ---------- --------  ----------

CUSTID_IDX   3     778150  77,842,100  17,163,350  77,842,100 865,805  77,043,200

…         

Yukardaki CUSTID_IDX indeksi gibi, oldukça yüksek clustering_factor değerini -aynen- muhafaza eden indeskler yüzünden, bu indeksler üzerinde aralıklı taramaların(index range scan) kullanıldığı sorgular çok yavaş çalışmaktaydı, çünkü tablodan oldukça fazla sayıda blok ziyaret edilmekteydi. Peki böyle bir durumda ne yapılması gerekmekte?

Bu soru, bazı önemli noktaların tekrardan gözden geçirilmesini zorunlu kılmaktadır. Başlangıç detayı olarak eklenmesi gereken nokta; indeksteki satır sayısının tablodaki satır sayısından daha fazla olmasıdır. Bununda sebebi, istatistik toplamasında %100 altında bir örnekleme değerinin ve “cascade” seçeneğinin kullanılmış olmasıdır. Oracle, sıklıkla indekslerde tabloda belirtilen örnekleme değerinden daha geniş bir değer kullanmaktadır.

Bu aslında, biraz daha az tercih edilen tekil olmayan(non-unique) indeskleri kullanan birincil anahtar(primary key) indekslerini açık şekilde kullanmak zorunda olan sorguların bulunduğu bir noktada, tablonun satır sayısını aşan bir clustering_factor değerine sahip olmadığı takdirde büyük bir mesele değildir. Bu durum, indeks uygunluğu değerlendirmesinde selectivity[tb_sel] * clustering_factor  deyimini kullanan maliyet hesaplamasının yan etkisi olmaktadır.

Eğer birincil anahtar clustering_factor değeri tablonun satır sayısından daha büyükse, o zaman tekil kolon indekslerinde, bu aynı zamanda kolonun “distinct” sayısından daha büyük olacak ve selectivity[tb_sel] * clustering_factor deyimi de primary_key_col=<sabit bir değer> yüklemine sahip değerden daha büyük olacaktır.

İlk baştaki asıl soruya gelirsek; clustering_factor değeri devamlı büyüyen bir indekse sahip olunduğunda ne yapılması gerekmektedir? Aslında bu soru dahada önemli bir soruyu doğurmaktadır. Gerçekten clustering_factor değeri doğruyumu söylemektedir?  Gerçekten var olan veri serpiştiricisi bir modellemedir, ve sorgu geliştirici bunun böyle olduğunu düşünen diğer bir modellemedir ve belkide veri serpiştiricisine sorgu geliştiricinin böyle düşünmesi gerektiği bilgisini yanlış olarak veren başka bir modellemede mevcuttur.  Bu cümle biraz karışık oldu, açalım isterseniz…

Eğer veriniz oldukça iyi kümelenmişse, bu durumda clustering_factor değeri düşük olacak ve sorgu geliştiricide büyük olasılıkla bu indeksi kullanacaktır. Verinin oldukça iyi şekilde kümelendiği, ancak bir şekilde clustering_factor değeri aşırı genişlemişse, bu durumda sorgu geliştirici sizin bu indeksin sorguda mutlaka kullanılması gerektiğini bilmenize rağmen bu indeksi görmezden gelecektir.

Eğer veriniz çok kötü kümelenmişse, bu durumda clustering_factor değeri büyük olacak ve sorgu geliştici sadece unique tarama, indeks salt ve bitmap dönüşüm yolu gibi özel durumlarda indeksi kullanacaktır, ancak bu da kesin değildir. Eğer veriniz çok kötü kümelenmiş ancak clustering_factor değeri küçükse (bu durum ancak tablo istatistiklerine 

D

BMS_STATS.set_table_stats 

ile manuel manipülasyon yapıldığında olacaktır), sorgu geliştirici bu indeksi kullanacak ve sonuçta aşırı miktarda mantıksal I/O’ya sebebiyet verecektir.   

Benim örneğimde clustering_factor değeri oldukça yüksek ve Oracle tablodan oldukça fazla sayıda blok ziyaret ettiği için, kullanıcılar indeks aralık taramalarının(index range scans) kullanıldığı sorguların çok yavaş çalıştığından şikayet etmekteler.

Bu durumda muhtemelen clustering_factor ile ilgili bir problem yoktur; eğer veri için tabloya gitmek gerekiyorsa, veriyi almak için mutlaka tabloya gidilecektir, yani bundan kaçış yoktur. Buna rağmen, clustering_factor değeri küçük olabilir ki aslında zaman geçtikçe bu daha sıkı çalışmalar sonunda ilgili sorgular için kullanılan indeksin uygun olarak tanımlanmadığı anlamına gelir.

Bu noktayı basit bir örnekle açıklarsak, müşteri verilerini içeren bir sorgu düşünün. Her zaman bir müşterinin son 4 haftasının verilerini elde etmek istediğimizi düşünün;

    select  ard.*

    from    customer_ent ard

    where   ard.cust_id = 4274

    and     ard.ord_date > trunc(sysdate) - 30

    order by ard.ord_date desc;

Eğer (cust_id)  kolonunda bir indeks mevcutsa, zaman geçtikçe Oracle daha çok tablo bloklarını ziyaret edecek ve tabloyu ziyaret ettikten sonra ancak çok eski kayıtları  dışarı atacaktır.

Eğer (cust_id,ord_date) kolonları üzerinde bir indeks mevcut olursa, zaman geçtikçe tablo ziyareti esnasındaki işyükü sabit kalacaktır, çünkü sorgulanan verinin hacmi oluşturulan geçmiş veri büyüklüğünden bağımsız olmaktadır. Bu noktada sorgudaki filtre kolon sırasına göre indeksin oluşturulması gerekmektedir (i.e cust_id, ord_date)

Elbette, örnekte kullanılan tablo (cust_id) kolonu üzerinde indeks-cluster olarak yeniden oluşturulursa, aşırı ziyaret edilen blok sayısı azaltılmış olur(buna rağmen hala aşırı sayıda satır ziyaret edilecek) ve bunun belki faydasıda olabilir.

Alternatif olarak, eğer tablo (ord_date) kolonundan bölümlendirilirse(partition), sorgu bölümlendirme eliminasyonunu kullanarak ve (cust_id) kolonu üzerindeki tekil indeksi kullanarak gereksiz blok ziyaretini azaltacaktır. Buna rağmen geniş sayıda bölümlendirme kullanılıyorsa, bu durum indeks blok ziyaretini istenmeyen seviyelere yükseltebilir.

Sonuçta, clustering_factor kolayca çok geniş boyuta ulaşabilir ve veri serpiştirici modellemesini doğru şekilde yansıtmayabilir, bu da sorgu geliştiricinin indeksin kullanılması gereken durumlarda bu indeksin kullanımını göz ardı etmesine sebebiyet verebilir. İlgili tablo her ne kadar yeniden oluşturulsada, bu sadece tek bir indeksin clustering_factor değerinin düşük değerde olmasını sağlayacak, ancak bu tablonun üzerindeki diğer indeksler hala oldukça yüksek clustering_factor değerine sahip olacaktır. Eğer clustering_factor değeri gerçekten veri serpiştiriciyi yansıtmazsa, tablo içinde daha çok blok ziyaret edildiğinden dolayı sorgular yavaşlayacaktır. Bu durumda, tablodaki ziyaret sayısını azaltmak üzere ilgili sorgunun filtre kısmında kullanılan diğer kolonlarınında bir bütün olarak indekse dahil edilerek oluşturulması ihtiyacı doğacaktır. Ayrıca, bu tablolarda bölümlendirmeninde (partitioning) bir çözüm olduğu unutulmamalıdır.