Büyük partition tablolarda istatistiklerin toplanması

Genellikle büyük partition tablolarda optimizer istatistiklerinin toplanması ve sürdürülmesi ile ilgili pek çok yeni DBA’nin kafasında soru işaretleri vardır. Bu soruları aslında iki konu etrafında toplayabiliriz:

1. Sorgular, eskimiş veya varolmayan partition seviyesi istatistiklerine erişirken menzil dışı değerler sebebiyle bir alt optimal planı alabilir.
2. Global istatistik toplaması zaman ve sistem kaynakları açısından aşırı derecede pahalıdır.

Bu yazıda bu iki sorunu tanımlayıp Oracle 10g ve Oracle 11g sürümlerinde bu iki sorunun temelini inceleyeceğiz.

Menzil dışı (out of range) değerler
Büyük tablolar sıklıkla, sorgu performansını artırmak ve veri yönetimini kolaylaştırmak için “partition” olarak adlandırılan daha küçük bölümlere ayrıştırılır. Oracle sorgusu, SQL sorgusu için iyi bir çalıştırma planı seçmek için hem bütün tablonun istatistiklerini(global istatistikler), hemde bireysel partitionların istatistiklerini(partition istatistikleri) değerlendirmeye almaktadır. Eğer sorgu sadece tekil bir partitiona erişmek ihtiyacı duyarsa, optimizer sadece bu erişilen partition’un istatistiklerini kullanmaktadır. Eğer sorgu birden fazla partitiona erişirse, global ve partition istatistiklerinin kombinasyonunu kullanılmaktadır. Partition kolonların minimum ve maksimum değerlerini şu şekilde düzeltir; İlk partition kolonun maksimum değeri olarak yüksek sınır bölümlendirmesini kullanır ve “range partitioned” tablolar için ilk bölümlendirilmiş kolonun minimum değeri olarak bir önceki partitionun yüksek sınır bölümlendirme değerini kullanır. İsteğe bağlı olarak, hedefin blok sayısı, satır sayısı gibi diğer bazı istatistikleride devredışı bırakır.

Farzedinki STOK adlı tablonun STOK_GIRIS kolonundan dörtte bir oranında “range partition” şeklinde bölümlendirilmiş bir tablo mevcut. Her günün sonunda veri en son partition içine yüklenmekte, ancak istatistikler sadece partitionun tamamen dolduğu yılın dörtte birlerinde toplanmakta. Global ve partition seviyeli istatistiklerin(sadece tamamen dolu partitionlar için) güncel olduğunu varsayarak, alt-optimal planının “menzil dışı” olmasını önlemek için aşağıdaki adımlar izlenebilir.



“Menzil dışı” tanımı, bir değerin WHERE cümlesi yüklemi içinde [minimum, maksimum] kolon istatistiklerince domain dışından belirtilen değeridir.  Optimizer, yüklem değeri ve maksimum değer arasındaki mesafeye(yüklem değerinin maksimum değerden büyük olduğunu farzedin) bağlı olarak seçiciliği eşit olarak dağıtmaktadır. İşte bu, daha uzaktaki değer maksimum değer, daha düşük değer ise seçicilik olarak ortaya çıkar. Bu durum en sık tarih kolonundan “range partition” yapılanmasında, bu range partitiona yeni bir partition ilavesinde ve ardından satırlar hala yeni partition içine eklenirken sorgulanan tablolarda sıklıkla meydana gelmektedir. Partition istatistikleri, toplu yüklemeler yüzünden hızlı biçimde eskimektedir, hatta istatistikler periyodik olarak güncellense bile. Optimizer tarafından bilinen maksimum değer, “menzil dışı“ durumuna doğru şekilde yol vermez. Seçiciliğe az önem verilmesi, sıklıkla sorgu optimizerının alt bir optimal plan seçmesine yol vermektedir. Örneğin; sorgu optimizer “full scan” daha iyi bir seçenek olsa bile, “index access” metodunu seçecektir.


“Menzil dışı” durumu Oracle 10.2 ve 11g ile uygun olan, yeni kopya tablo istatistikleri kullanılarak önlenebilmektedir. Bu prosedür kaynak (alt) partitionun istatistiğini hedef(alt) partitiona kopyalar, ayrıca kolonlar, local(partition) indeksler gibi bu objeye bağlı istatistiklerinide kopyalar.

1. Tablo istatistikleri DBMS_STATS içinden LOCK_TABLE_STATS prosedürü kullanılarak kilitlenir. Bu istatistiğin, otomatik istatistik görevleri ile karışmasının önüne geçmek için gereklidir. EXEC DBMS_STATS.LOCK_TABLE_STATS('HR','STOK');
   
2. Her bir yeni partition içine başlangıç yüklemesine başlamadan once(diyelimki STOK_Q4_2010), COPY_TABLE_STATS komutunu kullanarak bir önceki partition içinden istatistikler kopyalanır. İstatistik kilidini geçersiz kılmak için FORCE => TRUE takısı prosedür içine eklenmelidir.

EXEC DBMS_STATS.COPY_TABLE_STATS ('HR', 'STOK', 'STOK_Q3_2010', 'STOK_Q4_2010', FORCE=>TRUE);

Pahalı global istatistik toplaması

Datawarehouse ortamlarda bir veya daha fazla boş partition içine direkt olarak “bulk load” işlemi yapmak çok yaygındır. Bu partition istatistiklerini eskitir ve ayrıca global istatistikleride eskimiş yapabilir. Etkilenmiş partitionlardan ve tüm tablodan istatistiklerin yeniden toplanması zaman alıcı olabilir. Geleneksel olarak istatistik toplaması iki geçişli bir yapıda yapılmaktadır:

• İlk geçişte, global istatistikleri toplamak için tablo taranır.
• İkinci geçişte, değişen partitionların partition seviyeli istatistikleri toplanır.

Global istatistik toplamak için, bir tablonun full taraması tablonun boyutuna bağlı olarak çok pahalı olabilir. Unutmamak gerekir ki tüm bir tablonun tarama işlemi, partitionun küçük bir altkümesi değişse bile yapılmaktadır.

Oracle 11g’de, partition istatistiklerinden global istatistiklerin türetilmesiyle global istatistikler hesaplanırken bütün tablonun taramasından kaçınılmaktadır. Bazı istatistikler partition isatistiklerinden kolayca ve kesin olarak türetilir. Örneğin; global seviyede satır sayısı partitionların satır sayısı toplamıdır, partition histogramlardan global histogramlar türetilebilir. Ancak, bir kolonun farklı değerlerinin sayısı(NDV) partition seviyesi NDV lerden türetilemez. Böylece, Oracle partition seviyesinde her bir kolon için “özet” olarak çağrılan başka bir yapı oluşturur. “Özet” farklı değer örnekleri olarak değerlendirilir. NDV özetlerden tam olarak türetilebilir. Ayrıca, birçok özet tek bir özet olarak birleştirilebilir. Global NDV, bütün partition seviyesi özetlerin birleştirilmesiyle özetlerden türetilir. Kısaca toplamak gerekirse;

1. İstatistikler toplanır ve sadece değişen partitionlar için özet oluşturulur.
2. Oracle otomatik olarak global özet içine partition seviyesi özeti birleştirir.
3. Global istatistikler, global özet ve partition seviyesi istatistiklerinden otomatik olarak türetilir.

STOK tablosu               Özetler

19-Aralık-2010  ------->   S1

20-Aralık-2010  ------->   S2

21-Aralık-2010  ------->   S3                    GLOBAL

22-Aralık-2010  ------->   S4            İSTATİSTİKLER

23-Aralık-2010  ------->   S5

Artalan onarma özelliği varsayılan olarak devredışıdır, INCREMENTAL tablo önceliğinin TRUE olarak değiştirilmesiyle etkinleştirilir. Belirli bir şema veya veritabanı seviyesindede ayrıca etkinleştirilebilir.  

Şimdi varsayalımki yukardaki senaryoda kullanılan STOK adlı tablo STOK_TARIH kolonunda “gün” üzerinden “range partitioned tablo” şeklinde bölümlendiriliyor.  Her günün sonunda veri en son partition içine yükleniyor ve partition istatistikleri toplanıyor. Global istatistikler ise, zaman ve kaynak yoğunluğundan dolayı tek seferde her ayın sonunda toplanıyor. Artalan özellikte her yükleme sonunda global istatistik toplama için aşağıdaki adımlar izlenecektir.

1. Tablonun artalan(incremental) özelliği açılır.

EXEC DBMS_STATS.SET_TABLE_PREFS('HR','STOK','INCREMENTAL','TRUE');

2. GATHER_TABLE_STATS komutu kullanarak her yükleme sonunda tablo istatistikleri toplanması sonunda partition isminin tanımlanmasına gerek bulunmaz.Ayrıca  granularite parametreside tanımlanmaz. Bu komut ile partitionların eskimiş veya kayıp istatistikleri toplanacak ve partition seviyesi istatistikler ve özetlere bağlı olarak global istatistikler güncellenir. EXEC DBMS_STATS.GATHER_TABLE_STATS('HR','STOK');

Artalan onarım özelliği Oracle 11.1 sürümünde yayınlanmıştır. Ancak, Oracle 10.2.0.4 sürümüde aynı fonksiyonerliği sağlar. Oracle 10g sürümünde GATHER_TABLE_STATS  prosedürünün GRANULARITY parametresi için 'APPROX_GLOBAL AND PARTITION' adlı yeni bir değer kullanılır. Bu değerde artalan onarım özelliği gibi davranır,tek farklılık ise global seviyede indeksin farklı anahtar sayısı ve bölümlendirilmemiş kolonlariçin NDV güncellenmez. Bölümlendirilmiş kolon için partition seviyesinde NDV toplamı olarak NDV güncellenir. Ayrıca, unique indekslerin NDV kolonları tablonun satır sayısı olarak ayarlanır. Genelde, global seviyede bölümlendirilmemiş kolon NDV’si daha az sıklıkla eskir. “approx_global” seçeneği pek çok global istatistiği tam olarak sağladığından beri global istatistiklerin varsayılandan(tabloda %10 değişimde) daha az sıklıkla toplanması mümkün olabilecektir

Aşağıda bunun Oracle 10g  üzerinde kullanımı ile ilgili örnek yer almaktadır; Unutmadan belirtmek isterim, approx_global seçeneğini 10.2.0.4 ve 11.1.0.7 sürümünde sorunsuz kullanmak için 10g için 8719831, 11g için 8877251 bug yamasının yüklenmesi gerekmektedir.

 EXEC DBMS_STATS.GATHER_TABLE_STATS (‘HR', 'STOK', 'STOK_22ARA2010', GRANULARITY => 'APPROX_GLOBAL AND PARTITION');