Sorgu Optimizasyonu için Çalışma Zamanı İstatistiklerinin İncelenmesi

Herkese Selam,

Bu yazıda, sorgu optimizasyonu için ilk adım sayılabilecek, çalışma zamanı istatistiklerinin toplanması ve incelenmesini anlatacağım umarım farkındalık anlamında faydalı bir yazı olur.

Yazıya klasik ancak doğru bir cümle ile başlamak istiyorum.

“Ölçemediğiniz herhangi bir şeyi, geliştiremezsiniz?” Bu cümleden hareketle sorgu optimizasyonu için ilk adım olarak sorgunun çalışma zamanı istatistiklerini incelemek doğru teşhisi koyma ve bu teşhise istinaden doğru çözümü uygulama anlamında bize oldukça yardım edecek.

Peki çalışma zamanı istatistikleri ne gibi soruların cevaplarını içerisinde barındırır?

• Sorgu ne kadar süre çalıştı?
• Disk üzerine ne kadar işlem yaptı?
• Sorgu ne kadarlık lojik okuma yaptı?
• Execution planda var olan herhangi bir bölüm kaç kez çalıştırıldı?

Peki sorgunun çalışma zamanı (run-time) istatistikleri default olarak toplanıyor mu?

• Çalışma zamanı istatistikleri default olarak sistem tarafından toplanmıyor. Çünkü bu işlemin maliyeti sistemin tamamını düşündüğümüzde oldukça fazla. Veritabanı sistemimizde herhangi bir t anında çalıştırılan sorgu sayısı binler mertebesinde. Her sorgunun çalışma zamanı istatistiğinin toplanması sisteme çok büyük bir yük bindirmekte bu yüzden default olarak toplanmamakta.

Çalışma zamanı istatistiklerinin default olarak toplanmadığına değindik. Şimdi çalışma zamanı istatistiklerinin toplanabilmesi için ne yapmamız gerektiğine bakalım.

Bu operasyonu tetiklemek için birden çok yöntem mevcut. Bunlar;

• Yazdığımız sorguya /*+ GATHER_PLAN_STATISTICS */ hinti ekleyerek çalıştırdığımız sorgu özelinde çalışma zamanı istatistiği toplayabiliriz.
• Session ve sistem seviyesinde SQL Tracing i aktif edip çalışma zaman istatistiği toplatabiliriz.
• Instance parametrelerinden STATISTICS_LEVEL parametresini ilgili değer ile değiştirerek sistem ve session seviyesinde çalışma zamanı istatistiği toplatılabilir.

Şimdi bu yöntemlerin nasıl kullanılacağına kısaca göz atalım.

• GATHER_PLAN_STATISTICS hinti kullanarak çalışma zamanı istatistiği toplatmak.

SET LINESIZE 1000

SELECT  /*+  GATHER_PLAN_STATISTICS */
      e.employee_id, d.department_id
  FROM HR.EMPLOYEES e, hr.departments d
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.DEPARTMENT_ID = 10;

SELECT *
  FROM TABLE (DBMS_XPLAN.display_cursor (NULL, NULL, 'ALLSTATS,BYTES,COST'));

Evet ben çalışma zamanı ile ilgili istatistikleri gösterirken sadece belirli kolonları göstermek için ‘ALLSTATS,BYTES,COST’ formatlarını kullandım ancak istenildiği takdirde bir çok ek istatistikte çeşitli formatlar kullanılarak sorgu ek sorgu istatistikleride görüntülenebilir. Bu formatlar ile iligili linkteki makale takip edilebilir.  Şimdi kısaca listelenen istatistiklerin kısaca ne olduklarını söyleyelim.

• Starts: İlgili işlemin kaç kez başlatıldığını gösterir.
• E-Rows: Oracle’ın sorgu çalıştırmadan önce ilgili işlemden geçecek kayıt sayısı ile ilgili tahmini. (Bu tahmini hesaplamak için tablo istatistiklerini kullanıyor)
• E-Bytes: Oracle tarafından sorgu çalıştırılmadan önce ilgili işlemden geçecek datanın büyüklüğünün tahmini.
• Cost: İşlemin CBO (Cost Based Optimizer) tarafından hesaplanmış maliyeti.
• A-Rows: Sorgu çalışırken o işlemden geçen gerçek satır sayısı.
• A-Time: İlgili işlemin sorgunun çalışma zamanında ne kadar sürdüğü.
• Buffers: Çalışma zamanında memoryden yapılan logical read sayısı.
• Reads: İlgili işlemin çalışma esnasında yaptığı fiziksel okuma sayısı.

Sorgu istatistiklerinin gösterdiği değerleri inceleyerek belli yorumlarda bulunup sorgu iyileştirmek oldukça olası bir durum. Örneğin sorgu öncesinde hesaplanan satır sayısı ile sorgu sonrasında hesaplanan satır sayısı (E-Rows  A-Rows) arasında çok büyük bir uçurum var ise bu sorguyu oluşturan tabloların istatistiklerinin yeniden toplanması gerektiği ile ilgili bir yorum getirilebilir. Aynı zamanda bu tarz istatistiksel eksikler CBO tarafından yanlış planların çıkarılarak sorguların beklenenden performanssız çalışmasına neden olabilir.

• Yöntemlerden bir diğeri ise session ve sistem seviyesinde STATISTICS_LEVEL parametresini setleyip o an sistem seviyesinde ve ilgili session seviyesinde çalışan tüm SQL’lerin çalışma zamanı istatistiklerinin toplanması sağlanabilir.

ALTER SYSTEM SET statistics_level=all;
ALTER SESSION SET statistics_level=all;

Evet iki seçenek içinde gerekli cümleleri yukarıya ekledim. İster çalıştırılan spesifik bir sessiondaki tüm sorgular için çalışma zamanı istatistiği toplanacağı gibi. Tüm sistemde çalışacak sorguların tamamı içinde çalışma zamanı istatistikleri toplatılabilir. Ancak unutulmamalıdır ki; Sorgu için çalışma zamanı istatistiği toplatmak sisteme ek bir yük bindirmektedir. Bu durumu sistemdeki tüm sorgu seviyesinde yapmak, tüm sistemin genel performansını oldukça kötü etkileyeceğinden dolayı bu parametrenin sistem seviyesinde çalıştırılması önerilmez.

ALTER SESSION SET statistics_level=all;

SET LINESIZE 1000

SELECT     e.employee_id, d.department_id
  FROM HR.EMPLOYEES e, hr.departments d
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.DEPARTMENT_ID = 10;
SELECT *
  FROM TABLE (DBMS_XPLAN.display_cursor (NULL, NULL, 'ALLSTATS,BYTES,COST'));

• Yöntemlerden bir diğeri ise SQL Trace işlemini gerçekleştirmek. Bu yöntem, sorgunun çalışma zamanında ne gibi işlemler yaptığını anlamanın en eski, en iyi bilinen ve en fazla bilgi içeriği bulabileceğimiz  yöntemi. Ancak çalıştığımız ortamlarda bunu developer seviyesinde elde etmek oldukça güç olabilir. Sessionımız için açılan trace dosyaları veritabanının kurulu olduğu makinede oluşacağından bunu elde etmek için bir DBA’den destek istemek zorunda kalabiliriz. Bu nedenle diğer 2 yöntem ile belli aşama kayd edip hala yeteri kadar bilgi edinemediysek veya sorunu anlayamadıysak son opsiyon olarak DBA ile beraber çalışarak ilgili sorguların trace dosyları oluşturularak çalışma anında neler yaptığı ile ilgili çok detaylı bir bilgi elde edilebilir. Trace dosyasının nasıl oluşturulacağını merak eden arkadaşlar aşağıdaki linki takip edebilirler.

https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof#generating_sql_trace_files

Evet bu yazıda sorgu optimizasyonu için nereden başlamamız gerektiğini anlayabilme adına çalışma zamanı istatistiklerinin toplanması ve yorumlanması üzerinde durduk. Umarım farkındalık anlamında faydalı bir yazı olmuştur.

Kaynaklar

https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof#generating_sql_trace_files

https://hoopercharles.wordpress.com/2010/03/01/dbms_xplan-format-parameters/