CBO ve Karmaşıklığı

Selamlar,

Biliyorum, bir aydır herhangi bir giriş yapamadım ama yüksek lisansım hızlı bir şekilde ilerlediğinden dolayı biraz boşladım açıkçası fakat yazmaya devam edeceğim.

Oracle veritabanı, CBO (Cost Based Optimizer) adında çok karmaşık bir yapıya sahip. Bu yapının temel amacını aslında daha önceki girişlerimde belirtmiştim. Kısaca, yeniden belirtmem gerekirse CBO, verilen SQL'i hangi yol ile çalıştıracağını tespit eden bir sistem. Bu sistem aslına bakarsanız oldukça faydalı ve en düşük maliyetli sorgu yolunu seçmekte başarılı. Ancak bunun da bir yere kadar gidiyor olması gerekiyor çünkü en nihayetinde bir otomizasyon ve araç.

CBO kimi zaman tablo üzerindeki istatistikler güncel olsa bile doğru sonucu vermiyor olabilir. Burada dikkat, CBO zaten doğru sonuca gidiyor ama "optimum" sonuca gitmiyor olabilir. Bu da şu demek oluyor, CBO kendi hesapladığı maliyeti en düşük olan çalıştırma yoluna girer. Bunu da tablo üzerindeki istatistikleri baz alarak yapar.

Geçtiğimiz günlerde başıma gelen bir olaydan bahsetmek istiyorum. Üzerinde sorgu hazırladığım ve çalıştığım tablonun toplam boyutu yaklaşık 600GB kadar ve satır sayısı da 1 milyar 200 milyon civarında. Neyse ki performans açısından oldukça iyi durumdayız (!) çünkü bu tablo günlük partition'lara ayrılmış bir tablo. Tablo üzerinde 8 tane sütundan oluşan bir composite PK (Primary Key) indeksi bulunmakta ve tablodaki istatistikleri yeni toplamıştım. Fakat üzerinde çalıştığım sorgu bana çok düşük bir cost ve indeks kullanımı gösterse de sadece 96 satırı istediğim bir sorgunun bana gelmesi 16 dakika sürüyordu. Buradaki önemli nokta veritabanında herhangi bir wait event bulunmamaktayken bana bunu yapıyordu.

Her zamanki gibi en iyi dostum olan ADDM'e başvurdum. Sordum, bana PK indeksi kullandığını, join yaptığım tablo için de indeks kullandığını gösteriyorsun ey CBO. Peki neden nested loop ile de birleştirmene rağmen bana istediğim sonucu, istediğim zamanda vermiyorsun? Bana verdiği tek bir cevap vardı. "Indeks". Verdiği indeks cevabı aslında ilk bakışta garip geliyor çünkü zaten PK'nın kavradığı bir partitioned indeks'e sahibim ama ADDM'in bana önerdiği indeks 8 değil, 4 composite indeks sütunundan oluşuyordu.

Üzerinde çalıştığım sorgu bloğu oldukça karmaşıktı ve where ifadesinde yaklaşık 5 ayrı eşitlik bulunuyordu ve bu büyük tablonun dışında bilgi çekmem gereken bir başka referans tablosu daha bulunuyordu.

ADDM'in dediğini yerine getirdim ve partitioned indeksimin yaratılmasına başladım. Indeksi önceki gece 9'da yaratmaya başladım ve ertesi gün 12:30'da tamamladı. Sonuçlar karşısında gerçekten çok şaşırdım. O benim 16 dakika süren sorgum 97 milisaniyede geliyordu.

İndeks konusunda daha önce seminerina katıldığım Richard Foote, bu arkadaşlarla ciddi anlamda ilgileniyor ve çok ilginç noktalara değiniyor. Bu noktalardan yola çıkarsak; işte tam bu örnekte olduğu gibi kimi zaman CBO yanılabiliyor ve unutmayın ki CBO maliyeti en düşük sorgunun en hızlı olması gibi bir düşünce kesinlikle doğru değil. Benim çalışmalarımda /*+ FULL(TABLO_ADI) */ hint'ini kullandığım zaman sorgunun maliyeti 100 katına çıkıyordu (CBO'ya göre) ve CBO bu yüzden tercih etmiyordu fakat bu hint ile sorgu 16 dakika değil, 4 dakikada geliyordu. CPU sayısına göre PARALLEL(TABLO_ADI, DEFAULT) hint'i ile de biraz daha hızlandırılabiliyordu.

Sonuç olarak bu yazımda ifade etmek istediğim nokta, her zaman sorgulayın. Diagnostic Pack satın aldıysanız mutlaka SQL Tuning Set oluşturun ve sorgunun ne durumda olduğunu kontrol edin. SQL Tuning Advisor'ın söylediklerini dinleyin CBO'nun değil. CBO %100 doğruya gidecek diye birşey asla yok. Herşeyi Oracle'a bırakmayın, sistemin mutlaka rutin kontrollerini yapın, ağır sorguları yeniden, yeniden inceleyin. Unutmayın, indeks kullanmak her zaman daha hızlı değildir. Indeks'lerin de tipleri ve çalışma mantıkları vardır. Önce bu mantığın ne olduğunu öğrenin ve "full table scan" ile farkı nedir bunu anlayın.

Önümüzdeki haftalarda daha da yoğun olacağım için detaylı, teknik bir bilgi paylaşamayabilirim ancak benim yorumlarımı Oracle Forum'larından takip edebilirsiniz.

LOG FILE SYNC

Selamlar,

Birçoğunuza "log file sync" yazısı yabancı gelmeyecektir. Bu bir çeşit veritabanı bekleme olayıdır ve veritabanı üzerinde koşmakta olan bağlantıların takılabileceği bir olaydır. Bu beklemenin sebeplerini ve neler yapılması gerektiğini aşağıda belirteceğim yalnız şunu da eklemem gerekiyor ki; AWR raporundaki "Top 5 Wait Events" alanında bu olayı görüyorsanız eğer aksiyon almanız gerekebilir diyebilirim.

Bu kullanıcı commit ya da rollback komutunu gönderdiği zaman bu bağlantının redo bilgisi LGWR tarafından redo log dosyasına yazılır. Bu durumda veritabanı commit ya da rollback beklemeleri ile bu redo log'a yazma işlemini tamamlar.

Eğer log file sync bekleme olayı önemli ölçüde beklemeyi sistem üzerinde yaratmış ise ortalama beklemeyi gözlemlemek gerekmektedir. Eğer ortalama bekleme düşük ancak bekleyen bağlantı adedi fazla ise bu durumda uygulamanın her insert komutu ardından commit ettiğini söyleyebiliriz. Uygulama mantığı böyle bir yapı gerekiyor olabilir ancak transaction'ın atomik bir yapısı olduğunu ve bu yapı içerisindeki commit ya da rollback işlemlerinin ciddi anlamda önemli olduğunu da eklemek zorundayım. Bir bankacılık sisteminde paranın bir hesaptan diğerine aktarılırken kaybolması gibi birşey söz konusu olamaz ve buradaki en önemli ve sihirli kelime "commit" olacaktır. Sonuç olarak uygulama da bu bekleme adetlerini, her satırdan sonra commit etmek yerine 50-100 satır sonra commit ederekte çözebilir. Bu tamamen uygulamanın nasıl geliştirildiğine bağlıdır aslında.

Bu gözlemlemenin sonucunda eğer bekleme ciddi boyutlarda ise "log writer" bekleme olaylarını ayrı ayrı incelemek gerekmektedir. Bunun yanında, yukarıda özetlediğim durumun aksine, ortalama bekleme süresi yüksek ve I/O da yüksek ise aşağıdaki aşamaları sınamak gerekebilir;

1) Redo log'ların olduğu disk'ler üzerindeki aktiviteleri azaltın ya da redo log için ayrı bir disk kullanın.
2) Redo log lokasyonları için kullanacağınız başka disk'ler archiver'ın log writer üzerindeki etkisini minimize edecektir.
3) Redo log'ları daha hızlı disk'lere taşıyabilirsiniz. Örneğin RAID 5 disk'ten RAID 1 disk'e taşımak gibi.
4) "Raw device" kullanmayı, yazma işlemini hızlandırmak için düşünebilirsiniz.
5) Uygulamanın cinsine ve yapısına göre işlenen COMMIT'leri her N adet satır için yapmayı tercih edebilirsiniz. Her satır girişinden sonra yapılan commit, daha fazla log file sync beklemelerine sebep olabilirken, commit'leri biraz azaltmak daha az log file sync beklemelerine sebep olabilir.

İyi çalışmalar.

Ogan

Automatic Workload Repository & Create Snapshot

Merhabalar,

Oracle 10g ile birlikte aramıza katılan bir özellik olan Automatic Workload Repository (AWR), diagnostic pack bedelini ödediğiniz zaman kullanabileceğiniz bir özelliktir. AWR ve statspack farklı araçlardır ancak amaçları aynıdır ve istenildiği zaman statspack kullanımı yine söz konusudur.

Bugün bahsetmek istediğim konu bu raporları elle nasıl yaratabiliriz? Oracle aslında bizim için bunu her saat başı yapıyor ve sistemi kontrol edebilmemiz bir bize bir AWR raporu sunuyor. Bu noktada çok kısa bahsetmek istiyorum ki AWR dışında bir de ADDM denen bir kavram vardır, yani Automatic Database Diagnostic Monitor. ADDM'in amacı AWR raporlarını inceleyerek, bize taleplerde bulunması. Örneğin AWR raporunda bir SQL veritabanını çok fazla yormuş. ADDM bu SQL'i inceleyerek bize; tablo üzerinde index mi yaratmalıyız, index var ancak bitmap olmalı ya da yeni bir SQL profili geliştirmek gibi faydalı bilgiler sunar.

Gelelim konumuza. AWR raporlarını genelde enterprise manager (EM) aracılığı ile kontrol edebilirsiniz. Ancak bazı durumlarda saatlik alınan AWR raporunu okumayı değil, kendi aldığınız AWR raporunu incelemeyi tercih edebilirsiniz. Bunu tercih etmenizdeki en büyük etken ise o andaki performans problemlerini teşhis edebilmek olabilir.

Aşağıdaki komut ile manuel olarak snapshot'ımızı alabiliriz;

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

Ya da

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

Bir snapshot aralığını silmek ve veritabanının bilgisinden çıkartmak isterseniz eğer;

BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 1, high_snap_id => 10 dbid => 1921262300);
END;
/

Peki bu noktada şöyle bir soru sorabilirsiniz; "Neden AWR raporları Oracle tarafından her saatinde başında alınmakta ve bunu değiştiremez miyiz?" Cevap, evet değiştirebilirsiniz. Hemen bir örnek göstereyim;

BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 14400,
interval => 15, dbid => 1921262300);
END;
/

Retention period değeri AWR raporunun kaç gün boyunca saklanacağını, internal değeri ise kaç dakikada bir snapshot alınması gerektiğini temsil etmektedir. Daha sık ya da daha geç AWR raporu görmek isterseniz eğer yukarıdaki MODIFY_SNAPSHOT_SETTINGS sizin için biçilmiş kaftan diyebilirim.

DBA_HIST_WR_CONTROL data dictionary view'u ise size veritabanınızın AWR ayarlarını gösterecektir. MODIFY_SNAPSHOT_SETTINGS ile herhangi bir değişikliği yerine getirmeniz durumunda DBA_HIST_WR_CONTROL view'unu sorgulayarak en son halini görüntüleyebilirsiniz.

AWR snapshot'larını aldıktan sonra da awrrpt.sql isimli script'i koşmanız gerekiyor. Bu script'in amacı size bir AWR raporu hazırlamak.

awrrpt.sql scripti ise aşağıdaki dizinde bulunmaktadır;

$ORACLE_HOME/rdbms/admin/

Bu script'i çalıştırmak için;

cd $ORACLE_HOME/rdbms/admin/

sqlplus / as sysdba

SQL> @awrrpt.sql

İyi çalışmalar,

Ogan