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.

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

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)

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.

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;

Clustering factor hakkında bir inceleme

ERP alt sisteminde geçtiğimiz aylarda indeksler için yapılan iyileştirme çalışması sonucunda, bazı indekslerin hala oldukça yüksek “clustering_factor” değeri ürettiğini gözlemledim. Aşağıda bu sorunlu indekslerden birisi yer almakta.

            BTREE   LEAF    DISTINCT  CLUSTERING  INDEX      TABLE     TABLE

INDEX NAME  LEVEL  BLOCKS   KEYS      FACTOR      NUM ROWS   NUM BLKS  NUM ROWS

----------  -----  ------  ---------- ----------  ---------- --------  ----------

CUSTID_IDX   3     778150  77,842,100  17,163,350  77,842,100 865,805  77,043,200

…         

Yukardaki CUSTID_IDX indeksi gibi, oldukça yüksek clustering_factor değerini -aynen- muhafaza eden indeskler yüzünden, bu indeksler üzerinde aralıklı taramaların(index range scan) kullanıldığı sorgular çok yavaş çalışmaktaydı, çünkü tablodan oldukça fazla sayıda blok ziyaret edilmekteydi. Peki böyle bir durumda ne yapılması gerekmekte?

Bu soru, bazı önemli noktaların tekrardan gözden geçirilmesini zorunlu kılmaktadır. Başlangıç detayı olarak eklenmesi gereken nokta; indeksteki satır sayısının tablodaki satır sayısından daha fazla olmasıdır. Bununda sebebi, istatistik toplamasında %100 altında bir örnekleme değerinin ve “cascade” seçeneğinin kullanılmış olmasıdır. Oracle, sıklıkla indekslerde tabloda belirtilen örnekleme değerinden daha geniş bir değer kullanmaktadır.

Ortalama aktif oturumlar(Average Active Sessions - AAS) ile ani yüklerin zaman aralıklarının bulunması

“Ortalama Aktif Oturumlar”(Average Active Session - AAS) metriği, Oracle veritabanının tüm  çalışma performansının ölçümünde olağandışı bir şekilde basit ve bir o kadar kullanışlı şekilde hizmet sunmaktadır.  AAS metriği, kısaca “DB Time” değerinin “işlem süresi-elapsed time” değerine bölünmesiyle tanımlanmaktadır.

Sırayla, “DB Time”, hem CPU üzerinde hemde boşta olmayan bekleme durumlarında sıkışmış tüm oturumlar tarafından harcanan zaman olarak tanımlanmaktadır. Diğer bir deyişle, “DB Time” tüm aktif oturumlar tarafından harcanan zamanların toplamı olarakta düşünülebilir.

Mesela, bir dakikalık gözlem süresince aktif olan dört oturum olduğunu düşünelim. Her bir oturum toplamda dört dakikalık “DB Time” değerini vercek şekilde numeratörde birer dakikalık toplam  “DB Time” değerine karşılık gelecektir. Bu örnekte, “geçen süre”  paydası bir dakikadır. Her iki değerin bölümü AAS değeri olarak 4 sonucunu vermektedir. Bu örnektende görüldüğü gibi, 4 AAS metriği 4 aktif oturuma sahip olmaya karşılık gelmektedir. Bu örnekte, ortalama olarak dört aktif oturum bulunmaktadır.

Eğer bu örneği çeşitli dönemlerde aktif durumda olan daha fazla oturum ilave ederek genelleştirirsek, AAS metriğinin hesaplaması gözlem periyodu süresince hala ortalama sayıda aktif  oturumlar hakkında bir fikir verecektir. 

AAS metriği CPU sayısı ile kıyaslandığında oldukça kullanışlı olmaktadır.  Eğer AAS metriği CPU sayısını çok aşarsa, bu durumda veritabanı performansı zarar görecektir. Öte yandan, eğer AAS metriği belirgin şekilde CPU sayısından düşükse, o zaman veritabanı performansı iyi olacaktır. Veritabanı perfomansını sekteye uğratan üst sınırdaki AAS metriğinin eşikdeğeri, uygulamanın davranışına ve son kullanıcıların beklentilerine bağlıdır. Bu yüzden, bu eşikdeğeri uygulama bağlamında belirlenmelidir. AAS eşikdeğeri belirlendiğinde, bu metrik oldukça güvenilir, hazır biçimde bütün veritabanı performans sorunlarının teşhisinde hizmet verecektir. Aslında, “DB Time“ metriğinin düşürülmesi Oracle ADDM iyileştirme aracının yegane hedefi olmaktadır.

Oracle 11.2.0.2 Data Guard performansını genel olarak değerlendirmek

Oracle 11.2.0.2 Data Guard standby veritabanlarını yapıya ilave ettikten sonra birincil veritabanı performansını doğru şekilde değerlendirmek için,  aynı uygulama profili ve yüklemesiyle Dataguard yapılandırmasının öncesinin ve  sonrasının V$SYSMETRIC_SUMMARY  görünümünden ve AWR snapshotlarından alınacak istatistik geçmişleriyla kıyaslanması gerekmektedir.

Data Guard standby uygulaması öncesinde ve sonrasında uygulama profilini değerlendirmek için aşağıdaki istatistikler kıyaslanabilir:

·         İşlem başına fiziksel okuma

·         İşlem başına fiziksel yazma

·         İşlem başına CPU kullanımı

·         İşlem başına redo üretimi

Uygulama performansını değerlendirmek için aşağıdaki istatistikler kıyaslanabilir:

·         Saniye başı redo üretimi veya redo oranı

·         Saniye başı kullanıcı commit işlemi veya saniye başı işlem

·         Saniye başı veritabanı zamanı

·         İşlem başına yanıt süresi

·         SQL servisi yanıt süresi

SQL komutlarının ayrıştırılması safhası hakkında

SQL ile ilgili bir performans sorunu oluştuğunda,sorunu anlamak ve ardından bir çözüm bulabilmek için ilk olarak SQL komutlarının veritabanında nasıl işlendiğinin anlaşılması gerekmektedir. Uygulama, veritabanı instance’ına her bağlandığında, eninde sonunda veritabanı sunucusunda yaslı olan bir sunucu prosesine atanır. Bu sunucu prosesi, veritabanı ve son kullanıcı uygulaması arasındaki arayüzü desteklemektedir. Bu proses ayrıca SQL komutu sentaksını kontrol etmek, SQL komutunun nasıl yürütüldüğünün ve sonuçların son kullanıcı uygulamasına nasıl döndüğünün belirlenmesinden sorumludur. SQL komutu, sunucu prosesi tarafından alındığında, “hafif ayrıştırma-soft parse” meydana gelmektedir. Bu safhada komut sentaksı doğruluk için kontrol edilmekte, komut içinde referans verilen objelere kullanıcının erişim yetkisinin olduğu doğrulanmakta ve SQL “karma” değeri(SHV-SQL hash value) komut metninde hesaplanmaktadır. SHV, veritabanı içinde komutu kolayca belirlemek için kullanılan numerik bir değerdir. Ardından, SGA içindeki “library cache” de SHV in halen mevcut olup olmadığının belirlenmesi için araştırılır. Aynı yada diğer bir sunucu prosesi, benzer bir SQL komutunu halihazırda çalıştırdığında bu olay meydana gelir.  Eğer SHV bulunursa, sunucu prosesi komut hakkında “library cache” içinde saklanan bilgiyi getirir. Bu, yürütme planı olarak bilinen, sorgu içinde çeşitli nesnelere erişim için kullanılan algoritmaları içermektedir.  Komut alındıktan sonra, eğer bağlaç değişkenler(bind variables) komut içinde mevcutsa, aslına uygun değerler yerine geçer ve yürütme planı tarafından belirtilen işlemler sunucu prosesi tarafından yapılır. SQL komutlarını yürütürken bütünsel olarak en düşük kaynak maliyetini ürettiği ve en hızlı yanıt süresini verdiğinden dolayı bu arzu edilen bir olaylar dizisi olmaktadır.

Sorgu geliştirici ile ilgili genel kavramlar

Sorgu geliştirici(query optimizer) ile ilgili kullanılan temel kavramlar aşağıda yer almaktadır.

Değerlendirme(Estimation)

Değerlendirici, verilen bir yürütme planının tüm maliyetini saptamaktadır. Bu hedefe ulaşmak için değerlendirici 3 değişik tipte ölçü üretmektedir.

·         Seçicilik(Selectivity):  Bu ölçü, satır setinden satırların bir bölümünü temsil etmektedir. Seçicilik, sorgudaki city=’Pekin’ gibi bir yükleme veya (city=’’Pekin’ AND product=’tomato’) gibi yüklem kombinasyonlarına bağlıdır.

·         Önem(Cardinality): Bu ölçü, satır setindeki satır sayısını temsil etmektedir.

·         Maliyet(Cost): Bu ölçü, kullanılan kaynağın veya işin birimini temsil etmektedir.Sorgu geliştirici, iş birimi olarak  disk I/O sunu, CPU kullanımını ve bellek kullanımını kullanır.

Eğer istatistikler uygunsa, değerlendirici bu ölçümleri birbiriyle kıyaslamak için kullanır. İstatistikler, ölçümlerin doğruluk derecesini artırmaktadır.  

Oracle 11g de PGA ve SGA tavsiyecileri arasında bellek dağıtımı hesaplamaları ve AMM

Oracle 11g de PGA ve buffer cache tavsiyecileri arasındaki bellek dağıtım prosedürü daha az karmaşıktır. Ortalama direkt IO zamanını hesaplamak veya PGA tavsiyecisi verisini bytes değerinden tamamlanma süresine çevirmek gerekli değildir, çünkü V$PGA_TARGET_ADVICE view görünümü zaten tamamlanma süresi beklentilerini dahil etmektedir. Bu sebeple Oracle 10g den daha az karışık olan aşağıdaki sorgu kullanılarak her iki tavsiyeci direkt olarak kıyaslanabilmektedir:

Oracle 10g için PGA ve Buffer Cache bellek dağıtımlarında tavsiyecilerin kullanımı

IO bekleme olayları sıklıkla bizi doğru bellek iyileştirme yönüne yönlendirmektedir. Örneğin tüm bekleme olayları tampona alınmış IO için ise, PGA yı artırmak muhtemelen yardımcı olmayacaktır. Ancak, hem geçici segment beklemeleri hemde tampona alınmış IO beklemeleri önemli  ise, en iyi deneme bile belirgin değişme yapmayabilir.

V$PGA_TARGET_ADVICE ve V$DB_CACHE_ADVICE görünümleri  artan veya azalan bellek veya PGA boyutunun muhtemel etkisini belirlemek için tavsiyeler sunmaya yardımcı olmaktadır.  Bu görünümleri kullanarak PGA ve SGA arasında nasıl en iyi bellek tahsisini belirleyebileceğimizi görebiliriz.  

Oracle 10g’de, bu prosedür oldukça komplike hale getirilmiştir, çünkü PGA tavsiyecisi geçen süre ile ilgili tahminler içermemektedir.  Oracle 10g de ideal prosedür aşağıdaki gibidir:

1.       Geçici segment direkt IO işlemleri için ortalama süre ve blok sayısı belirlenmektedir.

2.       Bu ortalamalar kullanılarak V$PGA_TARGET_ADVICE  içindeki byte miktarı, geçen süreye dönüştürülür.

3.       V$DB_CACHE_ADVICE  içindeki bu PGA tamamlanma  süreleri, tampon önbellek tavsiyeci tamamlanma süresi ile birleştirilerek, hangi hedef birleşmelerinin tüm tamamlanma süreleri içinde azami artış gösterdiğinin belirlenmesi sağlanır.  

Başka bir veritabanından Statspack verisinin import edilmesi

Analiz etmek için Statspack verisini bir veritabanından başka bir veritabanına taşımak istenebilir. Şöyle diyelim; üretim veritabanının PERFSTAT şeması ayda bir sefer export ediliyor, ardından yer kazanmak için Statpack tabloları TRUNCATE ile  düşürülüyor. Bu silinen snapshotlar, geçmiş snapshotlara ihtiyaç duyulduğu zamanlarda(mesela geçen ayın snapshotlarına bakılmak istendiğinde) başka bir veritabanına import edilebilir. Açık olarak,devam eden snapshot yakalama prosesinde karışıklık yaratabileceğinden dolayı bu import işleminde üretim veritabanı hedef olamaz.  

Örnekteki senaryoda hesaba katılması gereken; STATS$IDLE_EVENT adlı Statpack tablosu belirli Statpack sürümünde eksik olan ilave bekleme olaylarını içerebilmektedir. PERFSTAT sahipliğindeki tabloların kaba kuvvetle düşürülmesi yaklaşımı ve ardından import işlemi ile bu tabloların tekrar oluşturulmasına izin verilmesi bu özelliği ortadan kaldırmaktadır. Bu sebeple bundan sonraki yaklaşım herhangi bir Statpack tablosunu düşürmez.  Bunun yerine bütünlük kısıtlamalarını(integrity constraints) devredışı bırakır, sptrunc.sql ile tabloları düşürür ve mevcut tablolara veriyi import etmek için IGNORE=Y import takısını kullanır.

Başlangıç noktası olarak, spcreate.sql ile Statspack kurulu bir veritabanı gerekmektedir. Bu hedefteki Statspack sürümü export edilen dump dosyasının Statspack sürümü ile aynı olmalıdır. Otomatik snapshot yakalama özelliği devredışı bırakılmış olmalı ve öncelikle sptrunc.sql ile tüm mevcut snapshotlar sistemden temizlenmelidir.

Oracle 11g Dataguard mimarisinde medya recovery hizmetlerinin iyileştirilmesi

Media recovery hizmetlerinin amacı, bir veritabanını uygun zaman dilimine geri döndürmek veya primary veritabanındaki tüm aktif işlemlerin birebir olarak fiziksel standby veritabanındada uygulanmasıdır. Zaten bu yazınında amacı Data Guard mimarisinde primary veritabanındaki işlemlerin fiziksel standby veritabanınında da uygulanması esnasında muhtemel darboğazlarını işaret etmek ve bunlarla ilgili gerekli iyileştirme işlemlerinin neler olduğunun gösterilmesidir.

Optimizer’ın doğru indeksi kullanmama sebepleri

Bazı durumlarda SQL komutu indeks kullanmadığından veya doğru indeksi tercih etmediğinden kötü performans gösterebilmektedir. Eğer uygun bir indeks yoksa, optimizer muhtemelen full tablo taraması(FTS) yapacaktır. Bazen çoğunlukla ideal olduğu düşünülen indeks yerine başka bir indeks seçilip kullanılacaktır. Eğer optimizer bir indeks kullanmıyor veya yanlış olduğu düşünülen bir indeks kullanıyorsa, aşağıdaki durumların gözden geçirilmesi gerekmektedir:

  v Uygun indeksin var olduğundan emin olun. Uygun indeks, SQL komutunun filtre kısmındaki şartların, mümkün olan en fazla sayıda sorgudaki filtre sırasına uygun olarak, kolon isimlerini karma indeks şeklinde barındıran indekstir. Eğer bu şekilde bir indeks yer almıyorsa, optimizer atlanan kolonların benzersiz değerleri başına efektif olarak “index range scan” işlemi çalıştıran indeks ile INDEX SKIP SCAN işlemini kullanabilecektir. Bu gibi operasyonlar, atlanan kolonların benzersiz değerlerinin sayısı düşük olduğunda değer kazanacaktır. Aksi takdirde,  bu operasyon verimsiz olma eğilimine girecektir. Atlanan kolon sonrası veya eşitsizlik kıyaslamasında kullanılan kolon sonrasında indeks tanımına dahil olan herhangi bir kolon, taranacak indeks yaprak blok sayısını azaltmak için kullanılamayabilir. Bu durum çalıştırma planında “predicate information” bölümünü incelerken görülebilmektedir. İndeks bloklarına direkt olarak erişmek için kullanılamayacak olan kolonlar indekse etki eden ilave FILTER yüklemi olarak belirecektir.

CPU üzerinde “çözümleme zamanı” sorunu

Bir komutun çözümlemesi, bir PARSE çağrısının parçası olarak gerçekleştirilmesi için zorunlu olarak gerekmemekte, ancak başlangıç çözümlemesinden itibaren imlecin geçersiz olduğu durumlarda EXEC çağrısı vasıyasıyla da tetiklenebilmektedir. Bu demektirki, izleme dosyasında pek çok PARSE çağrısı görülmemesine rağmen “çözümleme” ile ilişkili performans sorunlarınız olabilmektedir. TKPROF aracı üzerinden görülebilen bununla ilgili istatistikler, çözümleme esnasında library cache içindeki kayıplar(misses) olarak ve çalıştırma esnasında ise library cache içindeki kayıplar(misses) olarak görülmektedir.  

Burada bunların görülme sayısı, komutun optimizasyon aşamasıda dahil ne kadar sayıda “full parse-tam çözümleme” ihtiyacını duyduğunu temsil ederken, halbuki TKPROF çıktısındaki çözümleme istatistiklerinde görülebilen “hafif çözümleme-soft parse” çağrıları daha düşük kaynak tüketim hassasiyetindedir.

İdeal olarak, bir uygulama imleci bir sefer çözümleyerek hem “hafif”,  hemde “sert” çözümleme çağrılarının üstesinden gelebilir ve aynı imlecin daha sonraki çalıştırma işlemleri için gerekli olan en uzun sürede bu imleci açık olarak tutmaktadır.  İmlecin geçersiz(invalidated) olmasının önlemi alındığında, imleç bir sefer çözümlenecek ancak pek çok kez çalıştırılabilecektir.

10g sürümden 11g sürüme yükseltmede SQL çalıştırma planlarında kesintisiz performansın sağlanması

Pek çok DBA in ve PL/SQL geliştiricininde bildiği üzere, veritabanında bir parametre değişikliği tüm veritabanı çapında etkin olmaktadır. Bu durum,  SQL komutlarında kullanılan bir “hint” in, outline nın veya obje güncellemesini kapsayacak istatistikleri etkileyen SQL profilinin tam tersidir.

DBA’in veritabanını 10g sürümlerden 11g sürüme yükseltmesinde beklentilerinden birisi, yükseltme sonundaki platformda 10g deki çalıştırma plan varsayılan ayarlarının muhafaza edilmesidir. Ancak bu, 11g ortamında masum bir beklenti olarak kalacaktır!

Değişikliklerden geri dönülmesi  ve/veya daha önceki sürümlerdeki herhangi bir değişikliğin kaldırılması performansı oldukça etkileyebilecektir. Bu yüzden 10g performans baseline’larını  11g de de aynen alıkoyma fırsatı mümkün olursa, bu durum yükseltme sonunda çalıştırma planı maliyeti ile ilişkili karşılaşılacak sorunlar noktasında en önemli kurtarıcı olacaktır. Bu noktada “10g planları 11g planlarından dahamı iyidir” sorusu kafa karıştırıcı olacaktır. Elbette böyle bir iddia %100 kanıtlanır olmasada, 11g deki pek çok “gizli” parametrenin 10g deki değerlerinden oldukça farklı olması sebebiyle SQL çalıştırma planı performanslarında gerçekten dramatik düşüşler olabilecektedir. Aslında 10g öncesi 9i platformundan yükseltmelerde bu tür sıkıntılar mevcuttu, ancak 11g deki performans kaybı daha önceki sürüm yükseltmelerinden daha dramatik olmaktadır. Bu tür 11g sürümüne yükseltme sonrası çalıştırma planı performans problemlerinin detaylı analizlerine ve teknik incelemelerine, Coşkan Gündoğar'ın aşağıdaki yazı dizilerinden erişebilirsiniz, bu dizilerde oldukça faydalı analizler sebepleriyle beraber yer almaktadır.

·         http://coskan.wordpress.com/2011/03/04/plan-stability-through-upgrade-to-11g-why-is-my-plan-changed-extra-nested-loop/

·         http://coskan.wordpress.com/2011/02/17/plan-stability-through-upgrade-why-is-my-plan-changed-new-optimizer-parameters/

İşte bu tür parametrelerin varsayılan ayarlarındaki bariz değişiklikler yüzünden meydana gelebilecek sıkıntılı durumlarda, DBA lere hızlıca performans iyileştirme imkanı vermek ve aslında 10g deki SQL profillerinde uzun çabalar sonunda erişilen gelişmiş çalıştırma planı performansının 11g’de de aynen devam etmesini sağlamanın en iyi metotlardan birisi; 10g çalıştırma planlarının baseline koleksiyonunu dışarı almak ve sistem 11g sürümüne yükseltildiğinde bu koleksiyonun 11g SQL Plan Baseline içerisine yüklenmesi olmaktadır.

Açık kursör hatalarının çözümü hakkında bir inceleme

Eğer çok sık “Maksimum Open Cursors exceeded error” hata mesajı ile karşılaşılıyorsa, ilk yapılması gereken şeylerden birisi open_cursors başlangıç parametresinin değerinin kontrol edilmesidir. Aşağıdaki gibi açık kursörlerin mevcut değeri görüntülenebilir.

SQL> show parameter open_cursors

NAME         TYPE        VALUE

------------ ----------- ------

open_cursors integer     300

OPEN_CURSORS parametresi bir oturumun bir seferde maksimum sayıda açabileceği kursör sayısını ayarlar ve açık kursör sayısını kontrol etmek için kullanılmaktadır. Bu değerin çok düşük seviyede ayarlanması ORA-01000 hatasının alınması ile sonuçlanmaktadır. OPEN_CURSORS parametresi için çok büyük bir değerin belirlenmesinin bir zararı yoktur (pek mümkün olmasada tüm oturumların eşzamanlı olarak maksimum kürsör dışarı vereceği beklenmediği durumlar dışında), böylece bu parametreyi daha yüksek bir değere artırarak genellikle kursör tabanlı hatalar kolayca çözülebilir. Ancak, bu değerin arttırlması her zaman bu sorunu çözmeyebilir. Böyle durumlarda bu açık kursörleri kullanan proseslerin hangileri olduğunun bulunması gerekmektedir.  

SQL> select a.value, s.username,s.sid,s.serial#,s.program,s.inst_id

from gv$sesstat a,gv$statname b,gv$session s

where a.statistic# = b.statistic# and s.sid=a.sid

and b.name='opened cursors current';

GV$OPEN_CURSOR (veya V$OPEN_CURSOR) görünümleri her bir kullanıcı oturumunun o an açtığı, çözümlediği(parse) ve önbelleğe aldığı tüm kursörleri göstermektedir. Aşağıdaki sorgu kullanılarak yüksek sayıda açık ve çözümlenmiş veya önbelleğe almış oturumlar belirlenebilmektedir.

SQL> select saddr, sid, user_name, address,hash_value,sql_id, sql_text

from gv$open_cursor

where sid in

(select sid from v$open_cursor

group by sid having count(*) > &esik_degeri);

Bu sorgu tanımlanan eşik değerinden daha yüksek sayıdaki açık kursörler için bütün oturumları listelemektedir. Bu şekilde, sorgu çıktısı sınırlandırılabilmekte ve açık, çözümlenmiş veya yüksek sayıda  kursörleri önbellekleyen oturumlar üzerine odaklanılabilmektedir.

Yukardaki sorgu sonucunda sorunlu oturum için gerçek SQL kodu ve açık kursör sayısı aşağıdaki gibi elde edilebilmektedir:

ASM disk ve disk grubu I/O performansının izlenmesi

Oracle 11.2 sürümü ile gelen iostat özelliği ile disk başına okuma ve yazma işlemleri gerek I/O operasyonu olarak gerekse bytes değeri şeklinde listelenebilmektedir.

iostat [-et][--io] [--suppressheader] [--region] [-G diskgroup] [interval]

-e  takısı hata istatistiklerini gösterir (yazma/okuma)

-t  TIMED_STATISTICS değeri TRUE olarak ayarlandıktan sonra saniyenin yüzdesi değerinde toplam I/O zamanını veren zaman bazlı istatistik.

-G  izlenecek disk grubunun adı

-interval   komutun x saniye aralıklarla tekrarı

Pek çok iostat komutu gibi ilk aralıkta çalışma işlemi toplam istatistikleri getirmektedir ve arkadan gelen aralıklar önceki rapordan itibarenki süreyi kapsar.

ASMCMD> iostat -t -G DATA01 5

Group_Name  Dsk_Name                    Reads         Writes        Read_Time     Write_Time

DATA01        DATA01 _CD_00_ED2HCELL12  368823115776  398765133824  15652.064264  115609.999195

DATA01        DATA01 _CD_00_ED2HCELL13  360830513152  399665251328  15293.415546  108496.371997

Group_Name Dsk_Name                 Reads   Writes  Read_Time Write_Time

DATA01     DATA01_CD_00_ED2HCELL12   0.00      0.00  0.00      0.00

DATA0      DATA01_CD_00_ED2HCELL13   0.00   6553.60  0.00      0.00

Bu bilgi V$ASM_DISK_IOSTAT görünümünden de çıkarılmaktadır.