SQL Performance Analyzer ile SQL komutlarının performans gelişimlerinin izlenmesi

Oracle Database 11g Sürüm 1 (11gR1)  sürümü ile kullanıma sunulan SQL Performance Analyzer aracı, zayıf performans gösteren SQL komutlarının mevcut durumu ve gerekli düzeltme işlemi sonunda  performanslarında meydana gelen değişimleri test ortamında kıyaslayarak, kaynak kullanımında ve çalıştırma planı maliyetinde meydana gelen olumlu/olumsuz gelişimleri okunabilir rapor formatında hazırlayarak, veritabanı yöneticilerine SQL cümlelerinin iyileştirilmesinin veritabanı üzerinde olumlu etkisini kolayca görmesini sağlanır. SQL Performance Analzyer aracında kıyaslama için önceki ve sonraki olarak adlandırılan iki tür şablon kullanılmaktadır. “Önceki” kelimesinden kasıt;  herhangi bir iyileştirme yapılmadan çalıştırılan zayıf SQL komutlarının mevcut durumudur.  “Sonraki” ise; gerekli  yapısal iyileştirme yapıldıktan sonra bu konfigürasyon değişikliğinin sistem üzerinde ne tür performans geliştirmesi yapacağının testine imkan veren bir analiz ve simülasyon metodudur.

Bu makelede, zayıf SQL komutlarının“önceki” ve “sonraki” arasındaki performans değişimlerini  kıyaslama simülasyonu yer alacak ve performans değişim sonuçlarının zengin formatta raporlanması bir örnek ile yapılandırılacaktır. Oracle Enterprise Manager(OEM) grafiksel arayüzünde yer alan adım-adım sihirbazlar yardımıyla oluşturulacak olan SQL Performans Analyzer görevinde “önceki “ve “sonrası” arasındaki değişikliklerinin kıyaslama simülasyonu kolayca yapılmaktadır. Bu işlem için izlenecek adımlar aşağıda sırasıyla  yer almaktadır.

• Oracle veritabanındaki örnek yükü kapsayacak olan zayıf performansa sahip SQL komutlarının yakalanması(SQL Tuning setler yardımıyla).
• Mevcut veritabanı sistemini kullanarak “önceki” olarak adlandırılan imajı/şablonu oluşturmak için örnek işyükünün mevcut performans etkisinin belirlenmesi.
• Oracle veritabanı sisteminde yapılan yapısal değişiklik sonucunda mevcut işyükünün “sonraki” durumuna karşılık değişim göstermiş performansının test edilmesi.
• Yapılan yapısal değişim sonucu hangi işyükü komponentlerinde pozitif veya negatif yönde değişim meydana geldiğinin bulunması için “önceki” ve “sonraki” arasında kıyaslamanın yapılması ,ayrıca hangi işyükü komponentleirnde  değişiklik olmadığının tespiti.
• Zayıf performans gösteren SQL komponentlerinin nasıl en iyi şekilde düzeltileceğinin belirlenmesi, böylece yeni ortamda bu SQL komutlarının en iyi şekilde çalışacağından emin olunması.

SQL Performance Analyzer aracının çalışmasını göstermek için,  bu yazıda kullanılacak olan örnek tabloların oluşturulması ve SQL komutlarının beliritildiği adımları, “önceki” ve “sonraki” değişim simülasyonları ile birlikte aşağıdaki gibi yapılandıracağım.

Uptime izleme aracı ile inaktif oturumların izlenmesi

Uptime izleme aracı ile Oracle veritabanında inaktif oturumlar hakkında uyarıları emaille almak oldukça kolaydır. Bu işlem için uptime izleme aracı içinden “Add service monitor” ile Oracle (basic checks) servisini seçmek yeterlidir. Ardından script altından inaktif oturumlar mevcutsa TRUE değerini döndürecek olan SQL komutunu tanımlanabilir. Servis kıyaslama alanında da değer olarak FALSE tanıtılırsa, ters bir durum oluşma durumunda uptime izleme aracı otomatik email ile bu durumu kritik alert seviyesinde ilgili profile bildirecektir.

 Alttaki örnekte 2 saatten fazla zamandır veritabanına bağlı inaktif oturum olduğunda anında emaille alert olarak döndürecek örnek yer almaktadır.

Uptime ile ilgili geniş bilgiye http://www.uptimesoftware.com/resources.php adresinden erişilebilir.

SQL komutları için delta değerlerinin izlenmesi

V$SQLAREA görünümü library cache içindeki imleçler ile ilişkili tüm alt imleçlerin istatistiklerini birlikte gruplandırır. Buna rağmen gruplandırma bazı işlemleri kolaylaştırırken, “library cache latch” için artan talep sebebiyle bu görünüme erişmek muhtemelen daha fazla kaynak tüketecektir. İstatistikler, sürekli alma(consistent gets) sayısı, diskten blok okumaları, çalıştırmalar, satır alıp getirmeler(fetches), sortlar, bellek kullanımı, yüklemeler, invalidationlar ve bazı diğer alt istatistikler için sağlanmaktadır. 

Oracle 9.2 sürümünden itibaren, bu görünüm tamamlanma süresi(elapsed time) ve CPU kullanımı bilgilerinide sağlamaktadır. Görünüm her 1 saniye ve 5 saniye arasında güncellenmektedir.

V$SQL görünümü tüm alt imleçleri gruplandırmadan benzer bilgiyi sağlamaktadır; böylece görünüme erişmek daha az kaynak tüketmekle beraber, ayrıca “adaptive cursor sharing(uyumlu imleç paylaşımı)”, bağlaç(bind) değişken tanımı/değeri uzunluk değişimleri, değişik optimizer parametreleri çalıştıran oturumlar, istatistik toplama gibi SQL komutu  için “değişken” çalıştırma planına sahip türlü alt imleçler için istatistikleride görmeye imkan vermektedir. Bu görünümde her 1 saniye ve 5 saniye arasında değişmektedir. 

Yüksek “DB Time” değeri hakkında bir inceleme

Yüksek CPU kullanımı ile kendini göstermeyen ve bekleme sebebiyle oluşan performans problemi ile karşılaşılırsa ne yapılır? Bekleme belki mandallar(latches), kilitler veya mesela yavaş disk gibi farklı kaynaklar yüzünden olabilmektedir. Oracle 10g sürümünden itibaren CPU zaman tüketimi ve beklemesini gözönünde bulunduran sözde zaman modeli istatistiklerini sunmaktadır. Bu istatistikler (V$SYS_TIME_MODEL) ve oturum seviyesi için (V$SESS_TIME_MODEL) sistem görünümlerinden elde edilebilir. İşin özünde, veritabanı instance içinde harcanan zaman değerlendirmeye alınmaktadır.

“DB Time”  veritabanı kullanıcı seviyesi çağrılarını yerine getirirken geçen zaman miktarının mikrosaniye bazında toplamıdır. PMON gibi instance arkaplan proseslerinde geçen süre bu hesaplamaya dahil edilmez.

“DB Time” metriğine dahil olan durumlar aşağıda yer almaktadır;

·         DB CPU

·         Bağlantı yönetimi çağrısı esnasında geçen süre

·         Sekuans yüklemesi esnasında geçen süre

·         SQL yürütmesi esnasında geçen süre

·         Ayrıştırma esnasında geçen süre

·         PL/SQL yürütmesi esnasında geçen süre

·         Gelen PL/SQL rpc çağrıları esnasında geçen süre

·         PL/SQL derlemesi esnasında geçen süre

·         Java yürütmesi esnasında geçen süre

TROUG DBA SIG toplantısındaki sunumum

Geçen hafta 31 Mayıs 2012 Perşembe günü Bilginç IT Academy'de düzenlenen TROUG DBA SIG toplantısında yapmış olduğum sunuma linke tıklayarak erişebilirsiniz. Tüm katılımcılara ve oturumu düzenleyen herkese çok teşekkür ederim.

SQL Profiller nelerdir ve neden ihtiyaç duyulur?

DBMS_XPLAN paketi ile SQL komutlarının çalıştırma planının analiz edilmesi istenildiğinde,  bazı durumlarda çalıştırma planı boyunca aşağıdaki satır görülebilmektedir.

Note

-----------

SQL profile "SYS_SQLPROF_012ad8267d9c0000" used FOR this statement

Bunun anlamı şudur; 

·         SQL komutunun değerlendirmesi esnasında harici bir yardımcı nesne kullanılmıştır.

·         Bu yardımcı nesne çalıştırma planını değiştirmiştir veya en azından etkilemiştir.

Bu noktada bir takım yardımlar almak faydalı olacaktır. Mesela, bu profil nesne nedir? Ne iş yapar? Ve en önemliside SQL komutlarını değerlendirirken neden “harici” bir yardıma ihtiyaç duyulmaktadır?

Oracle optimizer ile ilgili problem aslında, geleneksel olarak Oracle optimizer’ın çalıştırma planları oluşturmak için veri istatistiklerine güvenmesinden kaynaklanmaktadır.  İstatistikler aslında gerçek verinin oldukça basitleştirilmiş tanımı şeklinde yer almaktadır. Oldukça büyük GB boyutundaki bir tablonun kolon istatistikleri, sadece belirli şeyleri içermektedir, değerlerin toplamı, benzersiz(distinct) değerlerin miktarı, minimum ve maksimum değerler gibi... Diğer bir deyişle, istatistikler verinin genel şeklini yakalar, ancak pekçok alt seviye detay bilgisi kaybolmaktadır.   

Bu kayıp detay bilgisini telafi etmek ve hala mantıklı ve doğru tahminler sağlamak için, optimizer veri hakkında bazı varsayımları bulundurmaktadır. Bilhassa optimizer bu durumlarda şu şekilde düşünmektedir: 

·         Veri değerleri uniform olarak dağılmıştır(diğer bir deyişle 2 nolu değer sıklıkla 5 nolu değerdir)

·         Kayıtlar uniform olarak dağılmıştır(diğer bir deyişle fiziksel bir kümeleme veya veri sıralaması yoktur).

·         Değerlerin sıralaması aralıksızdır(diğer bir deyişle aralık arasında bir boşluk yoktur)

İlk TROUG DBA SIG toplantısı

Bu ay TROUG’un DBA SIG Toplantısı 31 Mayıs tarihinde Bilginç IT Akademide gerçekleşecek.  Bu Oracle Kullanıcıları Grubunun ilk DBA SIG Toplantısı olacak. Sunumda benimde “Ham izleme dosyalarını okuma metotları” başlığı altında bir konuşmam olacak. 

Sunum konuları olarak, Data Masking, Partitioning, performance tuning, trace (ham izleme) dosyalarını okumak gibi Oracle DBA’lerin genelini ilgilendirecek konular seçildi.

Etkinlik Programı:

13:00-14:00 Açılış Konuşması + Data Masking Nedir? – Zekeriya Besiroglu

İçerik: Data masking kullanarak veritabanının güvenliği arttırmak, test sistemlerinde gerçek veri kullanmadan gerçek veri performansını yakalamak, Data Masking uygulamaları.

14:00-14:15 Kahve Molası

14:15-15:00 Enterprise Manager ile Performance Tuning – Gökhan Atıl

İçerik: Enterprise Manager hakkında ön bilgi, Enterprise Manager Performance Tuning açısından DBA’lere ne gibi olanaklar sağlar, Enterprise Manager Top Acitivity Page nasıl yorumlanır

15:00-15:15 Kahve Molası

15:15-16:00 Oracle Partitioning Nedir ve Nasıl Yönetilir? – Kamil Türkyılmaz

İçerik: Partitioning’in tarihçesi ve gelişimi, Partitioning çeşitleri, Parititioning yöntemine nasıl karar verilir, partitioning kullanımının artıları ve eksileri.

16:00-16:15 Kahve Molası

16:15-17:00 Ham izleme dosyalarını okuma metotları – Uğur İnal

İçerik: Ham izleme dosyalarının okuması, izleme metotları, TKPROF ve trace analyzer araçları, bu araçların farklılıkları, verilerin okunması ve önemli değerler

Kapasite sınırı nedeniyle katılımcıların iletişim formunu kullanarak kayıt yaptırması gerekmektedir. 

Bilginç Ulaşım Bilgileri: http://www.bilginc.com/default.aspx?TABLE=icerikler&ID=7&RELID=0

EXACT_MATCHING_SIGNATURE ve FORCE_MATCHING_SIGNATURE hakkında

Oracle dökümanlarında EXACT_MATCHING_SIGNATURE normalize SQL textinde hesaplanan imzadır şeklinde tanımlanmıştır. Buradaki normalizasyon kavramı beyaz alanların çıkarılması ve gerçek olmayan tüm stringlerin büyük harfe dönüştürülmesidir. CURSOR_SHARING parametresi EXACT olarak ayarlandığında bu imza kullanılırken, FORCE_MATCHING_SIGNATURE kolon değeri ise CURSOR_SHARING parametresi FORCE olarak ayarlandığında Oracle’ın hesapladığı imzadır.  

Şimdi bu kolonların alacağı bazı değerleri ve bunların anlamlarını aşağıda inceleyelim.  

I. SENARYO:

EXACT_MATCHING_SIGNATURE  ve FORCE_MATCHING_SIGNATURE değerleri sıfır olarak ayarlanmıştır. Bu senaryo eğer komut tipi PL/SQL paketi, INSERT,ALTER INDEX,LOCK, SET ROLE ve mevcut SYS tablolarından($,# olanlar, ancak görünümler hariç) SELECT/UPDATE işlemleri  veya dahili Oracle komutları içindir.  Bunun yanında daha fazla komut olabilir, ancak Oracle SQL in yeniden kullanılamayacağını düşünecek ve ardından değerleri 0 olarak işaretleyecektir.

Oracle 11.2 sürümünde DBMS_PARALLEL_EXECUTE paketi

Oracle 11.2 sürümü ile yeni gelen DBMS_PARALLEL_EXECUTE prosedürü, büyük veri setlerini artalan şekilde güncellemeye izin veren bir pakettir. Temel olarak, ROWID veya nümerik kolon veya kullanıcı tabanlı SQL komutuna dayalı satırlara ayırarak daha küçük veri yığınları oluşturulur, ardından bu yığınlar paralel şekilde güncellenir.  Bu paketin avantajları ise; bu yığınlar bireysel olarak commit edilmektedir ve ROWID’e dayalı yığınlar olduğunda daha iyi performansa ulaşılmaktadır.   

DBMS_PARALLEL_EXECUTE neden önemlidir?

1.    Büyük bir tekil işlem birçok işlem yığınına bölünebilmektedir.

2.    Golden Date veya Streams gibi bağlı sistemlerde daha az etki yapmaktadır.

3.    Yığınlar ROWID ve bloklara bağlı olduğunda paralel DML işlemleri ile kıyaslandığında daha iyi performans göstermektedir.

4.    Daha az undo alanına ihtiyaç duyar ve bu sebeple ORA-01555 hatası ile karşılaşma ihtimali daha düşük olmaktadır.

5.    Hata ile karşılaşıldığında rollback işlemine etkisi şiddetli olmamaktadır.

6.    Kilit süresi daha düşüktür.

7.    Hatalı yığın işlemlerini yeniden başlatabilmektedir.

8.    Diğer paralel özelliklerin aksine, DBMS_PARALLEL_EXECUTE  paketi  Enterprise Edition lisansına şimdilik ihtiyaç duymamaktadır.

9.    Manuel kodlama gereksinimini azaltmakta ve paralel işlemler üzerinden daha iyi tekdüze dağıtım yapabilmektedir.

Trace Analyzer aracı ile izleme dosyalarının analizi

Ham formattaki izleme dosyalarını okunabilir formata dönüştürmek için kullanılan en yaygın araç olan TKPROF ne yazıkki bind değişkenlerini rapora ekleyememektedir. Ayrıca  TKPROF içinde aynı anda birden fazla işlem tarafından kullanılan bloklar listelenmemektedir. Bu gibi kısıtlamalar sebebiyle, TKPROF aracına  alternatif olarak Oracle’ın ücretsiz bir aracı olan “Trace Analyzer” aracı Oracle Metalink’ten indirilip kullanılabilir. Böylece, aynı anda birden fazla işlem tarafından kullanılan ve kilite sebebiyet veren segmentler ve ilgili sıcak bloklarda raporlandığı gibi, ilgili SQL komutlarının hash değerleri ve kullanılan farklı bind değerlerinin yaptığı bekleme istatsitikleri ve yürütme planı bilgilerinide görme imkanına sahibiz.

“Trace Analyzer” aracı ile tek bir ham izleme dosyası analiz edilebildiği gibi, istenirse birden fazla ham izleme dosyasıda tek bir rapor gövdesinde alınabilmektedir. Analiz işlemi sonunda elde edilen zip dosyada TKPROF raporu yer aldığı gibi, çok kapsamlı bir HTML formatta raporda yer almaktadır.

Trace Analyzer aracı ile izleme analizinin raporlanması işleminden önce aşağıdaki adımların sırasıyla uygulanması gerekmektedir.

Standalone Oracle 10g veritabanının 10g RAC veritabanına dönüştürülmesi

Standalone Oracle 10g veritabanın Oracle 10g RAC veritabanına dönüştürülmesi işlemi öncesinde tüm düğümlerde clusterware yazılımının kurulmuş olması ve sunucuların RAC yapısında hizmet verecek şekilde fiziksel olarak yapılandırılmış olması gerekmektedir. Bu kurulum öncesi işlemlerin tamamlandığından emin olunduktan sonra daha önceden stand alone olarak hizmet veren bir Oracle 10g veritabanının RAC veritabanına dönüştürme işlemi için aşağıdaki adımların uygulanması yeterli olacaktır.

Veritabanındaki en son bekleme olaylarının analizi

Oracle 11g veritabanında en son bekleme olaylarını gerek kullanıcılar, gerek SQL komutları ve gerekse objeler bazında gözlemlemek ve analiz etmek için V$ACTIVE_SESSION_HISTORY görünümü kullanılabilir. Bu sistem görünümü ile veritabanında oturumların en son bekleme olayları ile ilgili istatistik bilgilerine erişilebilmektedir.  Bu görünümün V$SESSION ve DBA_USERS gibi sistem görünümü ve tabloları ile birleşimi sonucu elde edilen ve faydalı olacağına inandığım bazı analiz sorguları aşağıda yer almaktadır;