Bu yazıda Oracle veritabanı uzmanlarının en sık karşılaştığı sorunlardan olan; problem yaşayan kullanıcıların yaptığı işlemlerinin online olarak izlenmesi ve hangi işlemlerin iyileştirme için hedeflenmesi, kullanıcıların kullandığı programın aynı komutta farklı bind değişkenleri girildiğinde SQL Planında ne tür bir değişme olduğu, bunun genel sistem performansına olumlu veya olumsuz etkileri nelerdir gibi komplike sorulardır. Ayrıca Oracle sistem performansının değerlendirilmesinde ilgili SQL komutlarının yaptığı beklemeler, kilitler ve farklı bind değişkenleri kullanıldığında muhtemel ORA hatalarıda bazen bir Oracle DBA’in saatlerini almaktadır. İşte bu noktada ham izleme dosyaları başka hiç bir sistemin veremeyeceği çok değerli bilgileri saklamaktadır.
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 bloklarda(biz buna “hot blocks” diyoruz) raporlandığı gibi ilgili SQL komutlarının hash değerleri ve aldığı farklı bind değerlerini yaptığı beklemeler ve yürütme planı çerçevesinde 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.
1. İlk aşamada; Trace Analyzer aracının Oracle Metalink’ten indirilmesinden sonra SQL*Plus komut satırından SYS ile oturum açıp TRCA aracı aşağıdaki gibi kurulmalıdır. Benim trca dosyalarım C:\trca altında yer almaktadır.
SQL> @C:\trca\install\tacreate.sql
Kurulum esnasında bazı sorulara cevap verilmelidir:
· Optional Connect Identifier: Bazı kısıtlamalı sistemlerde @<instance_adı> şeklinde belirtilir. Opsiyoneldir. Eğer gerekli değilse boş bırakıp enter tuşuna basın.
· TRCANLZR password: Trace Analyzer aracını çalıştırmak için şifre. Opsiyoneldir.
· TRCANLZR default tablespace: Trace Analyzer kurulumunda gerekli olan tablo ve görünümlerin kurulacağı varsayılan tablespace ismi. USERS tablespace varsayılandır.
· TRCANLZR Temporary Tablespace: Trace Analyzer çalışırken bilgilerin tutulacağı geçici tablespace. Varsayılan olarak TEMP dir.
· Type of large objects in TRCA repository: TRCA ambarında büyük objelerin geçici veya kalıcı olarak tutulma seçeneği. T olarak geçici olması tavsiye edilir.
2. Oturum bazlı izlemenin etkinleştirilmesi gerekmektedir. İzleme dosyaları varsayılan olarak udump dizini altında yer almaktadır ve izleme açıldığında çok sayıda izleme dosyası oluşturulacağından, hangi dosyanın bizim izlemek istediğimiz kullanıcının SQL işlemlerini içeren izleme dosyası olduğunun belirlenmesinde sıkıntı olacağından, başlayacak olan izleme dosyasına bir IZLEME1 önekli bir “isim tanımlayıcı” ekliyorum.
SQL> ALTER SESSION SET tracefile_identifier=’IZLEME1′;
3. Ardından izlenmesini istediğimiz oturumun SID ve SERIAL# değerleriyle birlikte bekleme ve bind değişkenlerininde izleme dosyasında tutulmasını istediğimizden aşağıdaki izleme komutunu devreye sokuyoruz. Örneğimde 10 numaralı SID ve serial numarası ise 115.
SQL>exec dbms_support.start_trace(sid=> 10, serial#=>115, waits=>true,binds=>false);
NOT: İlgili kullanıcının hangi SID ve SERIAL# değerine sahip olduğunu bulmak için aşağıdaki sorgu kullanılabilir.Aşağıda TEST domainindeki UGUR adlı aktif durumdaki windows kullanıcısının SID ve SERIAL# değerini buluyorum.
SQL> SELECT OSUSER, SID, SERIAL#, USERNAME, STATUS
FROM v$SESSION where UPPER(OSUSER) = ‘TEST\UGUR’
AND status = ‘ACTIVE’;
4. SQL*Plus ekranından SYS kullanıcısı ile oturum açıp aşağıdaki gibi Trace Analzer aracı ile “nunicist_ora_656_IZLEME1.trc” adlı izleme dosyasının analizini yapıyorum. Sonuç olarak zip uzantılı bir dosya oluşturulacaktır.
SQL> @C:\trca\run\trcanlzr.sql nunicist_ora_656_IZLEME1.trc
Eğer birden fazla izleme dosyasının analizinitek raporda yapmak istersek, bu durumda udump dizininde control_file.txt adında bir text dosya oluşturup, analizinin yapılmasını istediğimiz tüm izleme dosyalarının adlarını bu text dosya içine alta alta yazıyoruz. Bu durumda aşağıdaki gibi çalıştırıldığında control_file.txt dosyası içindeki izleme dosyaları TRCA tarafından okunur, analiz edilir ve tek bir rapor formatında listelenir.
SQL> @C:\trca\run\trcanlzr.sql control_file.txt
Aşağıda “Trace Analyzer” aracı ile sekiz adet ham izleme dosyasının birleştirilip, analiz edilerek elde edilen tekil raporda yer alan önemli kısımları ele alacağım. Elde edilen zip dosyasının içindeki HTML raporunu kullanmaktayım.
Bu HTML formatındaki analiz raporun ilk kısmında yer alan “Response Time Summary” bölümünde izleme dosyasındaki işlemlerin toplam cpu kullanım süresi, yüzdesel cevap süresi, toplam işlem süresi, boşta bekleme süresi gibi genel değerler yer almaktadır.
Hem recursive(özyinelemeli) hemde non-recursive işlemlerin genel anlamda çözümleme(parse), çalıştırma(execute), satır alıp getirme(fetch) istatistikleriyle birlikte genel bekleme istatistiki bilgileri yer almaktadır. Aşağıda non-recursive ile ilgili bilgilerin olduğu snapshot yer almaktadır.
Bu kısımların altında rapor kapsamında yer alan izleme dosyalarının(veya dosyasının) içinde, %10 eşik değerini toplam cevap süresinde(total response time), işlem süresinde(elapsed time) ve CPU süresinde(CPU time) aşan SQL komutları sırasıyla yüzdesel değerleriyle yer almaktadır. SQL Text kısmında ilgili SQL cümlesinin üzerine mouse ile geldiğinize o SQL cümlesinin bütününü görebilmektesiniz.
“SQL Genealogy” kısmında rapor kapsamındaki tüm SQL komutlarını çalıştırılma sayısı, recursive cevap süresi ve bireysel cevap süresi gibi kriterlerde görebiliriz. Sorunlu SQL komutları kırmızı ile belirtilerek seçimde yardımcı olmaktadır.
“SQL Self – Time, Totals, Waits, Binds and Row Source Plan” bölümü altından hash değerlerine göre SQL komutlarının bireysel olarak bekleme istatistikleri, aldıkları bind değerleri ile ilgili istatistiksel bilgiler, satır kaynak planları yer almaktadır.
Aşağıdaki gibi çalıştırılan SQL komutunun yürütme planı ve bekleme istatistikleri yer alacaktır. Burada her bir WHERE şartında geçen kıyaslamaların hangi satır kaynak işlemini yaptığı, indeks kullanıp kullanmadığı, Full Table Scan yapıp yapmadığı görülebildiği gibi her bir satırın ne kadar kaynak tükettiği ve ne tür bekleme olayına sebebiyet verdiği gözlemlenebilmektedir.
Bunun ardından yer alan “Relevant Executions” kısmında ise bu SQL komutunun benzer şekilde çalıştırıldığı farklı bind değerleri ile ilgili genel liste yer almaktadır. Burada hangi bind değerinin ne kazda SQL cevap süresine sahip olduğu, ne kadar sürede işlmein tamamlandığı ve işlem süresince ne kadar CPU tükettiği gibi istatistiksel bilgiler yer almaktadır.
“Rank” kolonundan hangi bind değerinin ne kadar yanıt süresi verdiği yüzdesel görülmektedir. “Rank” kolonundaki değerlere tıklandığında ise ilgili bind değerinin execute-fetch istatistiği, yaptığı bekleme bilgileri ve bind değişkenin veri tipleri, veri uzunluğu ve aldığı değerler listelenmektedir.
Daha sonraki kısımda ise izleme dosyasındaki işlemler tarafından kullanılan segmentlerin I/O istatistik bilgileri listelenmektedir. Buradan hangi objenin hangi I/O bekleme olayına sebebiyet verdiği görülebilmektedir. Tablo ve indekslerin hem obje bazımda hemde bağlı oldukları blok sayısı olarak hangi bekleme olayına sebebiyet verdiği, ne kadar süre bekleme yaptığı gerek toplam, gerekse ortalama bazda listelenmektedir.
Bunun ardından ise en fazla dokunulan “hot” objeler dosya numarası ve blok id leri ile birlikte listelenir. Bu kısım veritabanı performansında oldukça önemli bir kısım olarak karşımıza çıkmaktadır.
Ardından SQL işlemlerinde varsa alınan ORA hataları kodu, meydana gelme zamanı ve ilgili SQL Text ile birlikte raporlanır, ayrıca birden fazla izleme dosyası trace analyzer ile tek raporda alındıysa her bir izleme dosyasının işlem özet bilgiside “Transaction Summary” kısmında yer alır. Aşağıdaki ORA-01722 kodlu hata mesajları, kullanıcının 16220719891 numaralı SQL komutunda yanlış bind değeri kullandığını işaret etmektedir.
Trace Analzer aracı özet olarak; performans sorunu yaşanan uygulamalarda gerek oturum bazlı, gerekse veritabanı bazlı yapılan izlemeler sonucu tüm SQL işlemlerinin gerek bekleme bazlı, gerek çalıştırma planı bazlı ve gerekse farklı bind değişkenlerinin sisteme etkisini ham veri olarak tutan izleme dosyalarının profesyonel açıdan sistem tarafından otomatikman analiz edilmesinde oldukça geniş bakış açıları sunmakta ve oluşturulan zengin içerikli HTML raporu ile gerçek darboğazların tespitinde tüm Oracle DBA’lere oldukça faydalı olmaktadır. Diğer bir önemli özelliği ise Oracle tarafından müşterilerine ücretsiz olarak sunulmasıdır.