TROUG Days 2016 İzlenimler

Geçtiğimiz hafta TROUG’un bu yılki yıllık etkinliği TROUG Days 2016 28-29 Nisan tarihlerinde İstanbul Teknik Üniversitesinde gerçekleştirildi. Yurt dışından ve yurt içinden bir çok değerli konuşmacının katıldığı bu etkinlikte hem yazılım geliştiricileri, hem veritabanı yöneticilerini hemde sistem yöneticilerini adresleyen geniş yelpazede bir çok oturum yapıldı.

Yurt içinden ve yurt dışından gelen konuşmacıların büyük bir bölümü Oracle ACE Program’a dahil olan Dünyaca ünlü konuşmacılardan oluşuyordu.

Oracle ACE Director

– Bjöern Rost
– Debra Lilley
– Jože Senegačnik
– Julian Dontcheff
– Osama Mustafa
– Nassyam Basha
– Sten Vesterli
– Syed Jaffar Hussain
– Gokhan Atil
– Gurcan Orhan
– Kamran Aghayev

Oracle ACE
– Patrick Barel
– Anar Godjaev
– Emrah Mete
– Ferhat Sengonul
– Mahir M. Quluzade
– Talip Ozturk
– Zekeriya Besiroglu

Oracle ACE Associate
– Yves Colin
– Fevzi Korkutata
– Orhan Eripek

Etkinlikte Sunum Yapan Bir birinden Değerli Diğer Yerli ve Yabancı Konuşmacılar

• Yasin Baskan
• Hamza Tığlay
• Mehmet Eser
• İlknur Köksal
• Yalım K. Gerger
• Uwe Hesse
• Emre Baransel
• Özgür Umut Vurgun
• Ümit Kılıç
• Levent Yavuz
• Dr. Alex Winokur
• Fettullah Çabuk

İki gün boyunca teknik içeriği doyurucu olan 3 paralel salonda yaklaşık 42 sunum yapıldı. Sunumların ve sunucuların kalitesini değerlendirdiğimizde bu etkinliğin Dünya çapında bir etkinlik olduğunu söylemek çokta yanlış olmaz.

 

 

Etkinliğe katılımın oldukça iyi seviyedeydi. Sunumlarda, konuşmacı ve katılımcılar arasında çok güzel interaktif bir ortam oluştu. Bunun sonucu olarak da sunumların ve etkinliğin veriminin oldukça arttığını söyleyebilirim.

 

 

Etkinlikte bende “PL/SQL Best Practices” başlıklı sunumu gerçekleştirdim. Kendi açımdan oldukça güzel bir deneyim olduğunu söyleyebilirim. Oraya kadar gelip beni dinleyen arkadaşlara da bir kez daha teşekkür ederim. Sunum ile ilgili sorularınızı emrahmete@gmail.com adresine mail göndererek sorabilirsiniz.

İlk günü akşamında TROUG konuşmacılar için çok güzel bir yemek düzenledi. Bu yemek organizasyonu yabancı konuşmacıların katılımıyla oldukça neşeli ve eğlenceli geçti.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Keyifli ve eğlenceli bu yemek organizasyonu için TROUG yönetimine bir kez daha teşekkür ederim.

Etkinlik sosyal medyada da #TROUGDays hash tag i ile oldukça geniş yankı buldu. Etkinlik ile ilgili daha detaylı paylaşımlara ve resimlere ilgili hashtag linkini takip ederek ulaşabilirsiniz.

TROUG’un bugüne kadar yapmış olduğu bence en kapsamlı etkinlikti. Gerek katılımcılar, gerek konuşmacılar oldukça memnun bir şekilde etkinlikten ayrıldılar. Bu etkinliğin yapılmasında emeği geçen başta TROUG yönetim ekibine, Altın Sponsorlarımız Oracle Türkiye ve AXXANA‘ya, Gümüş Sponsorumuz Bilginç IT Academy‘ye sonsuz teşekkürler. Umarım bu ve benzeri etkinlikler daha fazla yapılarak faydalanma imkanını yeniden elde ederiz.

Konuşmacılardan Bazı Kareler…

Oracle Cevaplıyor: Bloggers Meetup

Herkese Selam,

 

 

Geçtiğimiz günler de Oracle Türkiye’nin düzenlemiş olduğu “Büyük Veri ve Analitik” konu başlıklı Oracle Cevaplıyor etkinliğine katılma fırsatı buldum. Oracle Türkiye’nin ev sahipliğinde gerçekleşen etkinliğin oldukça keyifli ve faydalı geçtiğini söyleyebilirim. Oracle Türkiye’de konusunda uzman yöneticiler olan Murat Yeşil, Mehmet Gökmen ve Emrah Uysal biz blog yazarlarının hem sorularını cevapladılar hemde yakın gelecekte ki Big Data, Veri Analitiği ve Cloud teknolojileri üzerindeki projeksiyonlarını anlattılar.

Günümüzde oldukça trend olan bu konuları birde sektörün dinamiklerini iyi bilen ve farklı sektörlerdeki ihtiyaçları analiz eden insanlar tarafından dinlemek biz katılımcıların tamamı için oldukça faydalı oldu. Büyük veri ve Veri Analitiği konuları özelinde somut örnekler üzerinden çok faydalı bilgiler verildi. Bu konularda hali hazırda çalışan veya çalışmayı düşünen katılımcıların ilgisinin yüksek olduğunu söyleyebilirim.

Tüm Dünyada trend olan bu teknolojilerin, ülkemizde ki farkındalığı arttırmak için düzenlenen bu ve benzeri etkinlikler sektörde çalışan profesyoneller için oldukça değerli. Bu bağlamda bu etkinliği düzenlemesinde emeği geçen başta Oracle Türkiye ekibine ve ITAdvisor dergisine teşekkür ediyorum.

 

Oracle ACE Ödülü

Herkese Selam,

24.03.2016 günü itibari ile Oracle ACE programına ACE unvanı ile kabul olduğuma dair Oracle tarafından bir bilgilendirme emaili aldım. Oracle community sine yapmış olduğum katkılardan dolayı Oracle tarafından ACE ödülüne layık görüldüm. Bu programa bir Türk olarak kabul edilmek beni oldukça mutlu etti ve gururlandırdı. Bu unvana/ödüle beni aday gösteren ve adaylık sürecinde benden desteklerini esirgemeyen Gürcan Orhan, Steven Feuerstein ve Laura Ramsey‘e teşekkürü bir borç bilirim. Umarım bu tarz prestijli unvanlara sahip olan profesyoneller ülkemizde daha da fazlalaşır.

Oracle’ın adıma düzenlemiş olduğu profil sayfasına aşağıdaki linkten erişebilirsiniz.

https://apex.oracle.com/pls/apex/f?p=19297:4:::NO:4:P4_ID:15345

ACE Program tarafından gönderilen plaket ve  hediyeler.

 

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/

Çalışma Zamanında Üretilen Dinamik Sorgu Sonuçlarının Performanslı Üretimi

Herkese Selam,

Bu yazıda spesifik bir konuya performanslı bir çözüm önereceğim, umarım farkındalık anlamında faydalı bir yazı olur.

Çalışma zamanında (Run-Time) belirli parametrelere göre dinamik sorgu üretip,  bu sorguyu çalıştırıp dönen sonucu kullanmak zaman zaman yaptığımız işlemlerden.

Şimdi  örnek bir case üzerinden böyle bir ihtiyacı daha performanslı nasıl çözüm üretebileceğimizi inceleyelim.

Örneğimizi SH şeması altındaki SALES tablosunda yapacağız. Tablonun kolonları ve tipleri aşağıdaki gibidir.

TABLE sh.sales
 
 Name                                      Null     Type                        
 ----------------------------------------- -------- --------------
 PROD_ID                                   NOT NULL NUMBER                      
 CUST_ID                                   NOT NULL NUMBER                      
 TIME_ID                                   NOT NULL DATE                        
 CHANNEL_ID                                NOT NULL NUMBER                      
 PROMO_ID                                  NOT NULL NUMBER                      
 QUANTITY_SOLD                             NOT NULL NUMBER(10,2)                
 AMOUNT_SOLD                               NOT NULL NUMBER(10,2)                

Şimdi simülasyonumuzu nasıl yapacağımızdan bahsedelim. Amacım SH.SALES tablosunun kullanıcı tarafından run time’da belirlenen kolonlarına göre dinamik bir SQL üretip bu sonucu ekranda göstermek. Sonuç ekranda göstermenin haricinde bir işlem de olabilir ancak ben rahat anlaşılması ve simülasyonunu kolaylaştırmak amaçlı bu yolu tercih ediyorum.

Gerçekleme adımlarım şöyle olacak;

1- Gönderilen parametreleri alıp içeride dinamik sorgu oluşturacak ve geriye bir sys_refcursor döndürecek bir fonksiyon yaratacağım.

2- Daha sonra 1 numaralı adımda ürettiğim sys_refcursor’ı alıp çalıştırıp ve ekranda gösterilmesi için geriye sonuçları döndürecek bir fonksiyon yaratacağım.

3.- Son adımda ise 2 numaralı fonksiyonumun ürettiği sonuçları bir select yardımı ile ekranda gösterilmesini sağlayacak sorgumu yazacağım. Bu sonucu Oracle’ın TABLE metodunu kullanarak yapmayı planlıyorum bundan dolayı 2 tane type yaratmaya ihtiyacım var bu işi gerçeklemek için.

İlk olarak alt yapımı kurmak için tiplerimi yaratacağım. İlk yaratacağım tip ekrana döndüreceğim sonuçların kolonlarını içeren bir obje olacak.

CREATE OR REPLACE TYPE typ_sh_sales AS OBJECT
                  (PROD_ID NUMBER,
                   CUST_ID NUMBER,
                   TIME_ID DATE,
                   CHANNEL_ID NUMBER,
                   PROMO_ID NUMBER,
                   QUANTITY_SOLD NUMBER (10, 2),
                   AMOUNT_SOLD NUMBER (10, 2));

ikinci adım olarak ise TABLE metodunu kullanabilmek için geriye yukarıda yarattığım tipte bir array döndürülmesini sağlamak. Bu yüzden yukarıda yarattığım tipin bir array’ini yeni bir tip olarak yaratıyorum.

CREATE OR REPLACE TYPE typ_sh_sales_arr IS TABLE OF typ_sh_sales;

Evet 2 ayrı kullanacağım tipi oluşturduktan sonra alt yapımı kurmak için 1 numaralı adımda bahsetmiş olduğum dinamik SQL’i üretip geriye bir cursor döndürecek fonksiyonu yaratıyorum.

CREATE OR REPLACE FUNCTION FN_GENERATE_SQL_FOR_SALES (
P_PROD_ID          NUMBER,
P_CUST_ID          NUMBER,
P_TIME_ID          DATE,
P_CHANNEL_ID       NUMBER,
P_PROMO_ID         NUMBER,
P_QUANTITY_SOLD    NUMBER,
P_AMOUNT_SOLD      NUMBER)
   RETURN SYS_REFCURSOR
IS
   v_sql        VARCHAR2 (4000);
   where_pred   VARCHAR2 (4000) := ' where 1=1 ';
   ref_sales    SYS_REFCURSOR;
BEGIN
   v_sql := 'SELECT TIME_ID,
                   QUANTITY_SOLD,
                   PROMO_ID,
                   PROD_ID,
                   CUST_ID,
                   CHANNEL_ID,
                   AMOUNT_SOLD
              FROM SH.SALES ';

   IF P_PROD_ID IS NOT NULL
   THEN
      where_pred := where_pred || ' and prod_id = ' || P_PROD_ID;
   END IF;

   IF P_CUST_ID IS NOT NULL
   THEN
      where_pred := where_pred || ' and cust_id = ' || P_CUST_ID;
   END IF;

   IF P_CHANNEL_ID IS NOT NULL
   THEN
      where_pred := where_pred || ' and CHANNEL_ID = ' || P_CHANNEL_ID;
   END IF;

   IF P_PROMO_ID IS NOT NULL
   THEN
      where_pred := where_pred || ' and PROMO_ID = ' || P_PROMO_ID;
   END IF;

   IF P_AMOUNT_SOLD IS NOT NULL
   THEN
      where_pred := where_pred || ' and AMOUNT_SOLD = ' || P_AMOUNT_SOLD;
   END IF;


   IF P_TIME_ID IS NOT NULL
   THEN
      where_pred := where_pred || ' and TIME_ID = ' || P_TIME_ID;
   END IF;

   OPEN ref_sales FOR v_sql || where_pred;

   RETURN ref_sales;
END;

Evet yukarıda yaratmış olduğum fonksiyonda gelen parametrelerin durumuna göre dinamik bir sorgu üretip bu içeriği çalıştırılmak üzere bir fonksiyona gönderiyorum.

Şimdi yukarıda yarattığım fonksiyonun döndürdüğü sys_refcursor ı alıp fetch edecek ve sonuçları döndürecek metodu yazıyorum.

CREATE OR REPLACE FUNCTION FN_GENERATE_SQL_FOR_SALES (i_cursor SYS_REFCURSOR)
   RETURN typ_sh_sales_arr
   PIPELINED
   PARALLEL_ENABLE(PARTITION i_cursor BY ANY)
IS
   tuple   typ_sh_sales
              := typ_sh_sales (NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL);
BEGIN
   LOOP
      FETCH i_cursor
      INTO tuple.TIME_ID,
           tuple.QUANTITY_SOLD,
           tuple.PROMO_ID,
           tuple.PROD_ID,
           tuple.CUST_ID,
           tuple.CHANNEL_ID,
           tuple.AMOUNT_SOLD;

      EXIT WHEN i_cursor%NOTFOUND;
      PIPE ROW (tuple);
   END LOOP;

   CLOSE i_cursor;

   RETURN;
END;

Evet yukarıdaki fonksiyonda temel olarak gelen Cursor’ın fetch edilip her fetch işlemi yapıldığı anda sonucun o an geriye döndürülmesi sağlanıyor. Bunu sağlayan mekanizma PARALLEL PIPELINE mekanizması. Bu mekanizma ile ilgili detaylı bilgiye, nasıl kullanıldığına ve faydalarına “Oracle Parallel Pipelined Functions” başlıklı yazımdan bakabilirsiniz. Bu kullanılan alt yapı sayesinde sorgu sonucu çok hızlı bir şekilde ve daha az hafıza alanı tüketerek geriye dönmekte.

Şimdi işlemimizin son adımı olarak Test kısmına geçelim ve bu şekilde kurulmuş bir alt yapıyı nasıl select edeceğimize bakalım.

 

SELECT *
  FROM TABLE (
          FN_REILL_SQL_FOR_SALES (
             FN_GENERATE_SQL_FOR_SALES (P_PROD_ID => 13,
                                        P_CUST_ID         => NULL,
                                        P_TIME_ID         => NULL,
                                        P_CHANNEL_ID      => NULL,
                                        P_PROMO_ID        => NULL,
                                        P_QUANTITY_SOLD   => NULL,
                                        P_AMOUNT_SOLD     => NULL)));

/* 6002 rows selected.
Elapsed: 00:00:03.46 */

Evet görüldüğü üzere arka planda ön taraftan gönderdiğim parametreler ile dinamik SQL oluşturup bu SQL’i parallel ve pipelined bir biçimde execute edip sonucu ekrana döndürmüş oldum. Bu aşamada TABLE metodunu kullandım. Bu metot sayesinde geriye array döndüren bir fonksiyonu tablo gibi okumuş oldum. Klasik execute immediate yöntemiyle benzer işlemi yapmış olsaydık performansımız ve memory kullanımımızın ne kadar farklı olduğunu gözlemleyebilirdik.

 

Execution Öncesinde ve Sonrasında “Execution Plan”

Herkese Selam,

Bu yazıda sizlere EXECUTION PLAN‘nı sorguyu çalışmadan önce ve sorguyu çalıştıktan sonra inceleyip, sık düşülen bir yanılgıyı ortaya koymaya çalışacağım. Umarım farkındalık anlamında faydalı bir yazı olur.

Hakim olunan yanılgılardan biri, bir sorguyu çalıştırmadan önce o sorgu için aldığımız EXECUTION PLANIN, sorgunun çalışma zamanında(run time) aynı plan ile çalışacağına inanmamızdır. Çoğu zaman sorguyu çalıştırmadan önce çıkan execution plana bakarak, o sorgunun yanlış planda çalışıp, çalışmayacağı hakkında yorumlar yapıp sorguyu henüz hiç çalıştırmadan müdahale (HINT vb yollar ile) edebiliyoruz. Bu durumda belkide run time’da doğru planda çalışacak bir sorgunun, doğru planda çalışmasına engel olarak uygulama veya servislerimizin yanlış planda belkide performanssız çalışmasına sebep oluyoruz.

Genellikle bu tarz müdehaleleri test veya developement ortamlarında yapmak, production ortamlarında kötü sonuçlar elde etmemiz anlamına gelebilir. Test ve developement ortamları çoğunluk la hem data hemde metadata anlamında production ortamları ile aynı olmadığı için bu ortamlarda alınan planlara güvenip olası bir tuning çalışmasına girmek işin başlangıcında hatalar yapmamıza sebebiyet verebilir.

Peki bu durumu önlemek için ne yapabiliriz? Bu tarz durumları önlemek için uygulanabilecek en iyi çözüm,  execution öncesinde alınan plan hakkında yorum veya aksiyon almadan önce en az 1 kez, olabiliyorsa bir kaç kez sorguyu çalıştırıp run time’da nasıl bir plan ile çalışıp çalışmadığını kontrol edip buna göre aksiyon almaktır.

Unutulmamalıdır ki; Oracle Cost base optimizer arka planda milisaniyeler mertebesinde belli parametrelere bakarak sorgu planları üretmekte ve yaptığı hesaba göre en uygun olanı ile de sorguyu çalıştırmakta. Bu bağlamda bir sorgu için doğru planın üretilmesini sağlayabilmek için öncelikle gerekli işlemleri veritabanı üzerinde yapmak gerekir. Bu işlemler tablo istatistiklerini güncel tutmak, tabloları gerektiği yerlerde partition veya index gibi yapılar ile destekleyerek veriye erişim performansını arttırmak hemen ilk akla gelenler.

Oracle Cost Base optimizer bazen ortamda çalışan sorguların planlarını da değiştirebilmekte. Buna neden olan şeyler;
• İstatistiklerin büyük ölçüde değişmesi.
• Optimizer ile ilgili database parametrelerinde değişiklik yapılması
• Yazılan sorgunun değişikliğe uğraması

Burada olası en büyük seçenek istatistiklerin değişmesi. Cost based optimzer istatistikleri yoğun bir şekilde kullandığı için olası major değişiklikte sorgu planının bundan etkileneceği unutulmamalıdır. Bu bağlamda plan stabilizasyonu bağlamında tablo istatistiklerinin etkisi oldukça fazla olduğu söylenebilir.

Bind variable kullanmanın sistem performansını pozitif yönde etkilediği yadsınamaz bir gerçek. Ancak bind variable ların bind peeking adında negatif bir etkisi de bulunmakta. Bu konu düşünüldüğünde bind variable kullanılarak yazılan SQL’lerde sorgular bazı parametreler için istenildiği gibi çalışmayabilir. Yine bu durumu anlayabilmek için sorgu öncesinde ve run time’daki planlar karşılaştırılarak gerekli aksiyon alınabilir.

Şimdi soruguyu çalıştırmadan önce ve çalıştırdıktan sonra sorguların planlarının nasıl alınabileceğine bakalım. Akabinde bind variable içeren sorguları nasıl monitör edebilirizi inceleyelim.

-- Execution oncesi (sqlplus üzerinden asagidaki gibi alinabilir)
EXPLAIN PLAN
   FOR
      SELECT * FROM hr.employees;

SELECT * FROM TABLE (DBMS_XPLAN.display);

veya kullandığımız editörler üzerinden’de herhangi bir sorgu yazmadan kısayollar vasıtası ile planı görebiliriz.

Toad için : Sorgu seçildikten sonra Ctrl+E kombinasyonuna basılarak execution plan görülebilir. Çıktı aşağıdaki formatta olur.

PLSQL Developer için: Sorgu seçilip F5 tuşuna basılarak execution plan görüntülenebilir.

Oracle SQL Developer için: Sorgu seçilip F10 tuşuna basılarak execution plan görüntülenebilir.

Peki sorgu çalıştıktan sonra sorgunun çalıştığı planı nasıl görebilirim? Burada kullanılabilecek bir çok yöntem söylenebilir ancak benim en sık kullandığım yöntem sorgu içine bir comment ekleyerek sorgunun sql_id sini bulmayı kolaylaştırıp sorgunun çalıştığı planı görüntülemek oluyor.

-- Sorguyu ilk etapta çalıştırıyoruz.
SELECT  /* emrahmete */ *                                                       
        FROM hr.employees;
-- Çalıştırma işlemi bittikten sonra  sorgunun sql_id sini buluyorum
SELECT *
  FROM v$sql
 WHERE sql_text LIKE '%emrahmete%';   

-- Şimdi bulduğumuz id ile planı görünteleyelim.
SET linesize 2000
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor('b80a109vxwkkf'));

Evet sorgunun öncesinde ve sonrasında nasıl bir plan ile çalıştığını görüntüledik. Şimdi birde bind variable kullandığımız sorguların nasıl bir planda execute edildiğine bakalım.

-- bind variable tanımlayıp sorguya verdim ve sorguyu calistirdim
VARIABLE bind NUMBER;
EXEC :bind := 100;

SELECT * /*binding*/
  FROM hr.employees e
 WHERE E.EMPLOYEE_ID > :bind;

-- calisan sorgumun sql_id sini alıyorum 
SELECT *
  FROM v$sql
 WHERE sql_text LIKE '%binding%';

-- sorgumun planına bakıyorum
SET linesize 2000
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor('c8w9zmdbpxa6p'));

 

Evet sorgumun planından hangi değerle execute edildiğini göremiyorum bu yüzden o değerleri görüntülemek için aşağıdaki sorguyu yazıyorum.

SELECT hash_value,
       sql_id,
       name,
       datatype_string,
       last_captured,
       value_string
  FROM v$sql_bind_capture
 WHERE sql_id = 'c8w9zmdbpxa6p';

Evet bu yazıda normal durumda ve binding durumunda planları nasıl görüntüleyeceğimizi ve yorumlamak için nelere dikkat edeceğimize değindik umarım farkındalık anlamında faydalı bir yazı olmuştur.

Semi-Join ve Anti-Join Optimizasyonu

Herkese Selam,

Bu yazıda sizlere Semi-Join ve Anti-Join yöntemlerinin performans etkilerinden bahsedeceğim umarım farkındalık anlamında faydalı bir yazı olur.

Semi-Join 

Semi-Join yöntemi, eğer sorgu içerisinde bir subquerry varsa ve bu subquerry’de IN, EXISTS veya =ANY  gibi ifadeler ile  ana sorguya bağlanmış ise kuvvetle muhtemel Oracle Cost Based optimizer tarafından iki kümeyi bağlayabilmek için seçilecek yöntem olacaktır.

Döndürdüğü sonuç itibari ile standart inner join’e benzesede aralarında bazı temel farklar mevcuttur. Inner join yönteminde ana sorgudan çekilen her kayıt için, alt sorguda eşleşmenin sağlandığı tüm kayıtlar geriye döndürülürken, Semi-Join’de alt sorguda ilk eşleşme sağlandığı takdirde, alt sorguda arama yapılmaya devam edilmeden üst sorguya dönülerek yeni bir kayıt alınarak işleme devam edilir.

Şimdi bir sorgu ile optimizer’ın semi join seçimini gözlemleyelim.
Sorgu: Satis tablosunda en az 1 kez alış veriş kaydı olan müşterileri listeleyelim.

SELECT /* https://emrahmete.wordpress.com */ *
  FROM SH.CUSTOMERS c
 WHERE EXISTS
          (SELECT '1'
             FROM sh.sales s
            WHERE C.CUST_ID = S.CUST_ID); 

-- Execution Plani Görelim
SELECT *
 FROM v$sql
 WHERE sql_text LIKE '%https://emrahmete.wordpress.com%';

SET LINESIZE 2000
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('ccaruwgqtq88q'));

SELECT /* https://emrahmete.wordpress.com */    *
  FROM SH.CUSTOMERS c
 WHERE c.cust_id IN (SELECT s.cust_id
                       FROM sh.sales s);


-- Execution Plani Görelim
SELECT *
FROM v$sql
WHERE sql_text LIKE '%https://emrahmete.wordpress.com%';

SET LINESIZE 2000

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('335xqjwmy1v7y'));

Evet yukarıdaki 2 sorgudan da görüleceği üzere her iki planda Semi-Join kullanarak aynı sonuca ulaştı.

Peki aynı sorguyu standart join yönetemleri ile yapsaydık performans anlamında nasıl bir sonuç elde edecektik.

Inner joinin ürettiği sonuç ile semi-joinin ürettiği sonucun aynı olamayacağını yukarıda açıklamıştık. Bu bağlamda bu sorgu sonucunu inner join ile çözmek için DISTINCT söz deyimini kullanmalıyız.

SET TIMING ON
SELECT  /* https://emrahmete.wordpress.com  */ 
DISTINCT C.CUST_ID
  FROM sh.customers c, sh.sales s
 WHERE C.CUST_ID = S.CUST_ID;
 /* 7059 rows selected.
Elapsed: 00:00:05.68 */
 
-- Execution Plani Görelim
SELECT *
  FROM v$sql
 WHERE sql_text LIKE '%https://emrahmete.wordpress.com%';

SET LINESIZE 2000

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('db8bys0f37pq6'));

SET TIMING ON
SELECT /*  https://emrahmete.wordpress.com */  C.CUST_ID
  FROM SH.CUSTOMERS c
 WHERE EXISTS
          (SELECT '1'
             FROM sh.sales s
            WHERE C.CUST_ID = S.CUST_ID);    
/* 7059 rows selected.
Elapsed: 00:00:04.18 */            
             
-- Execution Plani Görelim

SELECT *
  FROM v$sql
 WHERE sql_text LIKE '%https://emrahmete.wordpress.com%';

SET LINESIZE 2000

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('8sbu2vth574h9'));

Evet gördüğümüz planlardaki cost ve çalışma sürelerini incelediğimizde aynı sorgu sonucunu semi-join ile daha hızlı bir şekilde gerçeklendiğini görmekteyiz.

Sorgularımızın Semi-Join metodunu kullanabilmesi için belli kısıtlamalar var. Sorgu optimizasyonu yaparken bu yöntemi kullanmak istiyorsak mutlaka aşağıdaki hususları göz önünde bulundurmalıyız aksi takdirde sorgumuz dilediğimiz gibi çalışmayacaktır.

• Yazdığımız sorgular IN, EXISTS veya =ANY içermeli.
• EXISTS veya IN içeren sorgularımızın where koşulunu OR ile başka bir condition ile birleştirmemeliyiz.
• EXISTS kullanıyorsak mutlaka dış sorguya bağımlı yani correlated olmalı.

Anti Join

 Adreslediği problem olarak semi-join in zıttıdır diyebiliriz.  Yine inner join ve farklı alternatif yollar ile bu problemleri çözebilsekte anti-join daha optimize ve daha performanslı sonuçlar ortaya koyabilmekte.

 

Temel olarak çalışma mekanizması, dışdaki sorgudan gelen ve içerdeki sorguda karşılığı olmayan sonuçları döndüren sorgulama yöntemi olarak ifade edebiliriz.

 

Şimdi örnek bir soru ile bu yöntemi ve performansını inceleyelim.

Sorgu: Satis tablosunda hiç kaydı olMAyan müşterileri listeleyen sorguyu yazalım.

NOT IN

SET TIMING ON
SELECT /* https://emrahmete.wordpress.com */    *
  FROM SH.CUSTOMERS c
 WHERE c.cust_id NOT IN (SELECT s.cust_id
                       FROM sh.sales s);

-- Execution planını görelim
SELECT *
  FROM v$sql
 WHERE sql_text LIKE '%https://emrahmete.wordpress.com%';

SET LINESIZE 2000

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('4h3jxygcczh64'));

NOT EXISTS

SET TIMING ON
SELECT /* https://emrahmete.wordpress.com */  C.CUST_ID
  FROM SH.CUSTOMERS c
 WHERE NOT EXISTS
          (SELECT '1'
             FROM sh.sales s
            WHERE C.CUST_ID = S.CUST_ID);
/*48441 rows selected.
Elapsed: 00:00:28.08*/

-- Execution Plani Görelim
SELECT *
FROM v$sql
WHERE sql_text LIKE '%https://emrahmete.wordpress.com%';

SET LINESIZE 2000
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('1wu6k0hh2zwvj'));

 

Şimdi aynı sorguyu standart minus ile çözmeye çalışalım.

SET TIMING ON
SELECT CUST_ID 
FROM SH.CUSTOMERS
MINUS
SELECT CUST_ID
FROM SH.SALES;
/*48441 rows selected.
Elapsed: 00:00:30.69*/

SELECT *
  FROM v$sql
 WHERE sql_text LIKE '%MINUS%';

SET LINESIZE 2000

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('53j1b490zwvxf'));

Evet planları ve çalışma istatistiklerini incelediğimizde optimizer ın anti-join seçimi ile daha performanslı bir iş yaptığını görmekteyiz.

Sorgularımızın  Anti-Join metodunu kullanabilmesi için belli kısıtlamalar var. Sorgu optimizasyonu yaparken bu yöntemi kullanmak istiyorsak mutlaka aşağıdaki hususları göz önünde bulundurmalıyız aksi takdirde sorgumuz dilediğimiz gibi çalışmayacaktır.

• NOT IN, NOT EXISTS veya !=ALL conditionlarını kullanarak sorgumuzu yazmalıyız.
• NOT EXISTS kullanıyorsak, subquerry nin dış sorguya bağlı olmasına dikkat etmeliyiz. Yani correlated bir sorgu yazmalıyız.
• NOT IN veya NOT EXISTS kullanılarak bir yapı kurduysak OR conditionı ile başka bir koşul koymamalıyız.
• Eğer 10g veritabanı kullanıyorsak ve NOT IN ile bu işi gerçeklemeye çalışıyorsak subquerry den dönen sonuç içerisinde NULL bir değer gelmemesini garanti etmeliyiz. aksi takdirde anti-join yöntemi kullanılamaz. (NVL veya NOT NULL Constraint  veya IS NOT NULL yapıları kullanılarak sağlanabilir.)

 

REFERENCES

https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm

https://jonathanlewis.wordpress.com/join-ordering-pt2/

PRO Oracle SQL