Optimizer’ın doğru indeksi kullanmama sebepleri

Bazı durumlarda SQL komutu indeks kullanmadığından veya doğru indeksi tercih etmediğinden kötü performans gösterebilmektedir. Eğer uygun bir indeks yoksa, optimizer muhtemelen full tablo taraması(FTS) yapacaktır. Bazen çoğunlukla ideal olduğu düşünülen indeks yerine başka bir indeks seçilip kullanılacaktır. Eğer optimizer bir indeks kullanmıyor veya yanlış olduğu düşünülen bir indeks kullanıyorsa, aşağıdaki durumların gözden geçirilmesi gerekmektedir:

  v Uygun indeksin var olduğundan emin olun. Uygun indeks, SQL komutunun filtre kısmındaki şartların, mümkün olan en fazla sayıda sorgudaki filtre sırasına uygun olarak, kolon isimlerini karma indeks şeklinde barındıran indekstir. Eğer bu şekilde bir indeks yer almıyorsa, optimizer atlanan kolonların benzersiz değerleri başına efektif olarak “index range scan” işlemi çalıştıran indeks ile INDEX SKIP SCAN işlemini kullanabilecektir. Bu gibi operasyonlar, atlanan kolonların benzersiz değerlerinin sayısı düşük olduğunda değer kazanacaktır. Aksi takdirde,  bu operasyon verimsiz olma eğilimine girecektir. Atlanan kolon sonrası veya eşitsizlik kıyaslamasında kullanılan kolon sonrasında indeks tanımına dahil olan herhangi bir kolon, taranacak indeks yaprak blok sayısını azaltmak için kullanılamayabilir. Bu durum çalıştırma planında “predicate information” bölümünü incelerken görülebilmektedir. İndeks bloklarına direkt olarak erişmek için kullanılamayacak olan kolonlar indekse etki eden ilave FILTER yüklemi olarak belirecektir.

  v  Seçicilik/önem (selectivity/cardinality) beklentisini kontrol edin.  

  v  Indeksin “clustering factor” değerini kontrol edin. İndeks erişim yolu üzerinden bir tabloya erişim yapıldığında, tamamlanma zamanının çoğu(ve böylece optimizer’ın hesaplanan maliyeti) indeks tarafından belirtilen her bir tablo satırına rastgele erişimden meydana gelmektedir.  Eğer tablo verisi, indekse benzer şekilde sıralanmışsa, hesaplanan maliyet, tabloya bu rastgele dağıtık tekil satır indeks erişimine nazaran belirgin şekilde daha düşük olacaktır, çünkü diskten aynı tablo blok okuması, indeksten birçok bitişik tekil tablo satır taleplerini tahmin etmek için kullanılabilecektir. İndeks sırasına göre incelenen tablo satırları rastgele dağıtıldığında, indeks tarafından başvurulan her bir satır diksten okunmak için potansiyel olarak farklı bir tablo bloğuna ihtiyaç duyar. Tablo ve indeks arasındaki sıralı ilişki, kümeleme faktörü(clustering factor) tarafından temsil edilmektedir. Düşük kümeleme değeri(tablonun toplam blok sayısına yakın olan değer), indeksin tablo verisine benzer şekilde sıralandığını gösterirken, yüksek kümeleme değeri(tablo içindeki satır sayısına yakın olan değer) ise bu tablodan belirgin şekilde farklı tasnif(sort) sırasına sahip indeksi ifade etmektedir ve böylece aynı sayıdaki satırlara erişirken çok sayıda bloğun okunmasını gerektirmektedir. Tabloda fiziksel olarak saklanan satırların sırasını değiştirmek tek bir indeksin kümeleme faktörü değerini azaltabilirken, geri kalan indekslerin kümeleme faktörünü ise yükseltecektir. Farklı indekslerin muhtemelen farklı sort sırası vardır. Mevcut bir indekse ilave kolonlar ekleyerek, rastgele sıralanan tablo satırlarına erişimlerin sayısını azaltma metodunun denenmesi mümkün olabilse de, bu sefer ilave kolon eklenmesi ile kümeleme değeri daha da artabilecektir. Diğer bir seçenek ise, “heap-organize” yapısındaki bu tablonun indeks organize tablo(IOT) olarak olarak değiştirilmesini denemek olabilir, ancak bu yaklaşımın geçerliliği kolonların sayısı ve boyutuna bağlı olabildiği kadar IOT içinde ikincil indekslerin gereksiniminede bağlıdır, çünkü ikincil indeksler tablo verisi değiştirildikçe daha az geçerli olma eğilimindedir. Yüksek kümeleme faktörüne sahip indeksler, TABLE ACCESS BY INDEX ROWID operasyonları için nispeten daha yüksek maliyet hesaplamasında olacaktır ve bu sebeple optimizer full tablo tarama erişim yolunu veya  farklı bir indeksi seçebilecektir. Aslında, bir indeksin kümeleme faktörünü düşürmek için en etkili yol, o indeksi ve bağlı olduğu tabloyu düşürüp yeniden oluşturmaktan geçmektedir.

  v  Üstü kapalı(implicit) veri tipi dönüşümünü ve WHERE takısında kullanılan fonksiyonları kontrol ediniz. Herhangi bir veri tipi dönüşümü veya WHERE takısında herhangi bir fonksiyon kullanımı, optimizerın fonksiyonun kullanıldığı kolon için oluşturulan indeksi seçmemesine sebebiyet verecektir ve bu da optimizer’ın selectivity/cardinality beklentisini etkileyecektir.   Açık dönüşüm fonksiyonları kullanımı içinden üstü kapalı(implicit) veri dönüşümlerinden kaçının. Eğer gerekiyorsa, fonksiyon kullanılan bu kolonları içerdikleri fonksiyon yapısında fonksiyonel indeksler şeklinde indekslemek bu sorunun üstesinden gelecektir.

  v  OPTIMIZER_INDEX_COST_ADJ ve OPTIMIZER_INDEX_CACHING  parametrelerini, varsayılan dışı değerleri aldığı durumlarda kontrol edin.  Bu iki parametreyi kullanarak indeks erişim maliyetlerini azaltmak, gereksiz indeks kullanımına sebebiyet verebilecektir, özellikle farklı indeksler kullanarak çeşitli erişim yollarının azaltılmış maliyet hesaplamaları, aynı bu hesaplanan maliyet ile sonuçlanıyorsa. OPTIMIZER_INDEX_COST_ADJ parametresini indeks erişim maliyetlerini azaltmak için kullanmak yerine, sunucu donanımının performans karakteristiğini doğru şekilde yansıtan full tablo taraması operasyonlarının maliyetini arttırmak için sistem istatistiklerini değiştirme yolu tavsiye edilmektedir. Sistem istatistiklerinin değiştirilmesi; maliyet düşüş uygulamasından sonra birçok indeksin aynı maliyet ile sonuçlanması durumlarının sayısını sınırlandırmaya yaramaktadır.   Bazı durumlarda,  OPTIMIZER_INDEX_CACHING parametresi sistem istatistikleri kullanılsa bile yeterlidir, özellikle optimizer “nested loop” birleştirmelerinin veya in-list operatörlerinin önbellekleme etkisini değerlendirmeye almıyorsa.  

  v  NO_INDEX veya FULL gibi indeks kullanımını engelleyen hintlerin ilgili sorguya ilave edilip edilmediğini kontrol edin. Eğer bu hintler sorgu amacına uygun değilse, bunları sorgudan çıkarın.

  v  Optimizer tarafından anlamsal veya yazım hatasına sahip hintler nedeniyle, gerekli indekslerin değerlendirmeye alınamayacağını bilerek, bu hintlerin yazım ve anlam  hatası içerip içermediğini kontrol edin.

  v  SQL komutu için SQL Erişim Tavsiyecisi(SQL Access Advisor) tarafından sunulan gelişmiş indeks kullanımı ve/veya materalized view ilavesi gibi diğer yapısal değişiklik tavsiyelerini kontrol edin ve değerlendirmeye alın.