Oracle Sorgu Planı Kararlılığı: Histogramlar, Bind Değişkenleri, ACS ve Plan Gerilemesi
Aynı SQL ifadesinin veri dağılımı, istatistikler, bind değerleri ve Adaptive Cursor Sharing nedeniyle farklı yürütme planlarına geçebilmesini; plan gerilemesinin tanılanması ve sınırlandırılması açısından ele alır.
Oracle'da zaman zaman şu durumla karşılaşılır: SQL metni değişmemiştir, tablo yapısı değişmemiştir; buna rağmen sorgu bir gün milisaniyeler içinde, başka bir gün saniyeler içinde çalışır. Böyle bir durumda doğrudan “optimizer yanlış plan seçti” sonucuna gitmek yerine optimizer'ın o planı hangi bilgilerle seçtiğini incelemek gerekir.
Ben plan kararlılığı problemini dört soruyla ayırıyorum: Oracle kaç satır bekliyordu, gerçekte kaç satır geldi, hangi bind değeri kullanıldı ve bu cursor hangi istatistik koşulunda üretildi?
1. Tahmin ile gerçek satır sayısı birbirini tutuyor mu?
Yürütme planındaki maliyet kadar cardinality tahmini de önemlidir. Bir adımda Oracle 100 satır bekleyip gerçekte 5 milyon satır alıyorsa sonraki join yöntemi, erişim yolu ve bellek ihtiyacı hatalı bir varsayım üzerine kurulabilir.
Bu yüzden plan incelemesinde yalnız EXPLAIN PLAN çıktısına güvenmek yerine mümkün olduğunda gerçek çalışma istatistikleriyle tahmini ve gerçekleşen satır sayılarını karşılaştırmak gerekir.
Temel soru şudur:
Plan pahalı mı, yoksa planı pahalı hale getiren cardinality tahmini mi yanlış?
2. Histogram gerçekten gerekli mi?
Histogramlar sütundaki değer dağılımı eşit olmadığında optimizer'a ek bilgi sağlayabilir. Ancak her sütuna histogram eklemek plan kararlılığı sağlamaz. Gereksiz veya sık değişen histogramlar bazı iş yüklerinde tam tersine daha değişken seçimlere yol açabilir.
Özellikle çok eğik veri dağılımında aynı predicate farklı değerler için bütünüyle farklı seçicilik gösterebilir. Bu durumda tek bir “ortalama” seçicilik bütün bind değerlerini doğru temsil etmeyebilir.
Histogram kararını şu üç bilgiyle birlikte değerlendirmek daha sağlıklıdır:
- veri dağılımı,
- predicate biçimi,
- bind kullanım şekli.
3. Bind peeking neden önemlidir?
Oracle bir cursor ilk kez parse edilirken bazı sürüm ve koşullarda başlangıçtaki bind değerini plan seçiminde kullanabilir. İlk değer çok seçici, sonraki değerler ise çok geniş bir veri kümesine karşılık geliyorsa ilk plan bütün çağrılar için uygun olmayabilir.
Bu noktada Adaptive Cursor Sharing (ACS) devreye girebilir. Oracle aynı SQL için bind duyarlılığını izleyerek farklı selectivity aralıklarında farklı child cursor'lar oluşturabilir.
Bu mekanizma “aynı SQL her zaman aynı planı kullanır” varsayımının neden pratikte doğru olmadığını gösterir.
4. Plan gerilemesi tam olarak ne zaman başladı?
Performans sorunu incelenirken yalnız kötü planı görmek yetmez. Değişimin zamanını bulmak daha değerlidir.
Kontrol edilmesi gerekenler arasında şunlar bulunur:
- istatistik toplama zamanı,
- tablo veya indeks büyüklüğündeki değişim,
- histogram değişikliği,
- yeni child cursor oluşumu,
- optimizer parametreleri,
- sürüm veya patch değişikliği,
- bind değerlerinin dağılımı,
- SQL Plan Management kayıtları.
AWR, ASH ve cursor geçmişi gibi kaynaklar varsa plan hash value değişiminin hangi dönemde başladığı incelenebilir. Amaç “kötü planı” tek başına etiketlemek değil, plan geçişinin nedenini bulmaktır.
Planı sabitlemek her zaman çözüm değildir
SQL Plan Baseline veya başka bir plan yönetim yöntemi, bilinen iyi bir planı korumak için yararlı olabilir. Fakat veri dağılımı zamanla değişiyorsa eski planı sonsuza kadar zorlamak da başka bir performans problemine dönüşebilir.
SQL Plan Management baseline, kabul edilen plan kümesini kontrol etmeye yarar; hatalı kardinalite tahminini, eksik indeksi veya uzun süren transaction'ı doğrudan düzeltmez. Planın kararlılığı ile sorgunun iş kuralına göre doğru sonuç üretmesi ayrı doğrulama hedefleridir. Özellikle veri dağılımı değiştiğinde kontrollü karşılaştırma hem temsilî bind değerlerini hem de gerçek sorgu hacmini kapsamalıdır.
Bu nedenle iki ayrı hedef vardır:
- beklenmeyen plan gerilemesini sınırlandırmak,
- gerçekten daha iyi yeni planların seçilebilmesine izin vermek.
Plan kararlılığı ile plan değişmezliği aynı şey değildir.
Güvenli inceleme sırası
Üretim sisteminde doğrudan hint eklemek veya optimizer parametresi değiştirmek yerine şu sıra daha az risklidir:
- iyi ve kötü çalışmanın SQL_ID ve plan hash değerlerini belirlemek,
- tahmini/gerçek cardinality farklarını incelemek,
- bind değerleri ve veri dağılımını karşılaştırmak,
- istatistik ve histogram değişim zamanını kontrol etmek,
- child cursor ve ACS davranışını incelemek,
- değişikliğin etkisini temsil edici veri ve yük altında doğrulamak.
Oracle'da plan incelemesi yapılırken SQL_ID, CHILD_NUMBER, plan hash değeri ve ilgili bind değerleri aynı çalışma bağlamıyla kaydedilir. DBMS_XPLAN.DISPLAY_CURSOR üzerinden görülen gerçek satır istatistikleri yalnız bu istatistiklerin toplanmış olduğu yürütmeler için anlamlıdır; tahmini kardinalite ile ölçülen kardinalite ayrı tutulur. Adaptive Cursor Sharing, farklı bind seçicilikleri için birden fazla child cursor oluşmasına izin verebildiğinden tek bir SQL_ID için tek bir değişmez yürütme planı varsayılamaz.
Oracle performansında kararlılık, optimizer'ı etkisizleştirmek değil; optimizer kararlarının neden değiştiğini gözlenebilir ve geri alınabilir hale getirmektir. Oracle Veritabanı ve PL/SQL içindeki mimari kavramlar bu incelemenin daha geniş bağlamını oluşturur.
Seçicilik Gruplarıyla Karşılaştırma
Bir planı yalnız tek bind değeriyle değerlendirmek, dağılımın uçlarındaki davranışı gizleyebilir. Dar, orta ve geniş sonuç kümeleri üreten temsili bind grupları aynı veri ve istatistik sürümü üzerinde karşılaştırılır. Geçen süreyle birlikte dönen satır, mantıksal okuma ve yürütme sayısı kaydedilir. Plan hash değeri aynı kalsa bile veri hacmi, beklemeler ve önbellek durumu farklılaşabilir.
İnceleme, mevcut yürütme bilgilerinin okunmasıyla başlayabilir; üretimde ortak önbelleği boşaltmak veya bütün sisteme izleme açmak ölçümün önkoşulu değildir. AWR ve ASH gibi özelliklerin kullanımı sürüm, yetki ve lisans koşullarıyla birlikte ele alınır. Tek bir gözlemden bütün bind değerlerine uygulanacak plan zorlaması çıkarılmaz.
Kaynakça
