Performanslı SQL Yazmanın En Etkin Yolu Düşünce Tarzımızı Değiştirmek

Herkese Selam,

Performanslı çalışan SQL sorguları yazmak kurumsal yazılım dünyasında en büyük problemlerden bir tanesi.

Developement ortamlarında yazılan sorguların hızlı sonuçlar üretmesi ancak aynı kodun production ortamlarına alınmasıyla beraber production ortamlarındaki data miktarının hacimce çok daha fazla olması hasebiyle  performans sorunlarının yaşanmaya başlaması veritabanı üzerinde projeler/servisler geliştiren her şirketin yüzleştiği en temel problemler aslında.

Bu tarz problemlerin (performansız çalışan veritabanı işlemleri) bir çok seviyede nedenleri olabilir ve bu nedenler incelenebilir ancak ben bu yazıda bu tarz problemlerin başlangıcı sayılabilecek en temel nokta olan “sorgu yazarken nasıl düşünmeliyiz” konusuna değineceğim.

Bu zamana kadar yaşamış olduğum deneyimler SQL geliştiricilerin sorgu yazarken çözdükleri probleme genelde prosedürel olarak yaklaştıklarıydı. Prosedürel yaklaşım insan aklına, bir problemin çözümü olarak gelebilecek en hızlı çözüm yaklaşımı aslında. Bunun nedeni ise SQL geliştiricilerin neredeyse çok büyük bir kısmının aynı zamanda Java, C# veya herhangi bir programlama dili ile de haşır neşir olmaları. Java, C# vb. diller aslında insanın düşünme stilini prosedürel olarak çalışmaya eğitiyor çünkü bu diller ile uygulama geliştirirken bolca IF .. THEN .. ELSE,  FOR .. LOOP,  WHILE .. DO, CASE .. WHEN gibi yapıları kullanıyoruz. Bu durumda ister istemez, bir veri kümesini  bir business logic’den geçirirken her bir kaydı ayrı ayrı(buna obje de diyebiliriz) değerlendirmemiz anlamına geliyor (row by row processing). Bu yaklaşım (yani prosedürel yaklaşım) belki java vb. dilleri ile yazılım geliştirirken doğru bir yaklaşım olsa da konu veritabanı seviyesinde sorgu yazmaya (SQL) / veritabanı işlemleri yapmaya geldiğinde aynı sonuç ortaya çıkmamakta.

İşimiz veritabanı seviyesinde operasyon yapmaksa (SQL Yazmak) uğraştığımız problemin çözümü ile ilgili yaklaşımımız prosedürel yerine bütünsel (SET Based, Küme Yaklaşımı) olmalı. Yani yazdığımız sorguları veya veritabanı operasyonlarını performanslı yapabilmek için  problemimizin çözümü adına ilk yaklaşımımızın bütünsel olmasına özen göstermeliyiz. Çünkü işleyeceğimiz veri kümesini toplu olarak işleyebilmek, satır satır işlemeye göre oldukça performanslı sonuçlar üretmekte.

Şimdi örnek bir problem üzerinden sorumuzu 2 yöntemlede çözmeye çalışalım ve yaklaşımları performans olarak birbirleriyle kıyaslayalım.

Soru: customers tablosundaki her müşterinin sales tablosunda kaç kaydının olduğunu bulmak istiyorum. 

Prosedürel Yaklaşım ile;

SET AUTOTRACE ON
SELECT 
      c.cust_id,
       (SELECT COUNT (*)
          FROM sh.sales s
         WHERE s.cust_id = c.cust_id)
          sa_count
  FROM SH.CUSTOMERS c;

Plan hash value: 881374884

¦statistikler
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    2454756  consistent gets
          0  physical reads
          0  redo size
     925474  bytes sent via SQL*Net to client
      41104  bytes received via SQL*Net from client
       3701  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      55500  rows processed

prosedurel

 

Şimdi bütünsel yaklaşım ile sorgumuzu yazalım.

SET AUTOTRACE ON
SELECT 
        c.cust_id, COUNT (s.cust_id) jh_count
    FROM SH.CUSTOMERS c, sh.sales s
   WHERE c.cust_id = s.cust_id(+)
GROUP BY c.cust_id;

Plan hash value: 716053480

¦statistikler
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        742  consistent gets
          0  physical reads
          0  redo size
     925474  bytes sent via SQL*Net to client
      41104  bytes received via SQL*Net from client
       3701  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      55500  rows processed

setbased

Evet iki sorgu arasındaki consistent get sayılarına yani buffer cache den okudukları blok sayılarını incelediğimizde aradaki farkın devasa olduğunu görmekteyiz. Prosedürel yaklaşım ile yani satır satır işlenecek şekilde yazdığımız sorgunun yaptığı okuma sayısı ile bütünleşik yaklaşım ile yazdığımız sorgu arasındaki okuma sayısı farkı çalışma performansları arasındaki durumuda açıkça ortaya koyuyor.

Başka bir örnekte; yine sık gözlemlediğim bir alışkanlık olan SQL cümlesi içerisinden PL/SQL fonksyonu çağırılması. Bu da yine prosedürel çalışmaya örnek gösterilecek bir problem çözme yaklaşımı. SQL içerisinden PL/SQL kodu çağırmanın performansı etkileyen başka handikapları da mevcut ama bu yazıda o seviyede bir performans problemine değinmeyeceğim.

Soru: customers tablosundaki her müşterinin alışveriş toplamını bulan kodu yazalım.

Prosedürel Yaklaşım:

İlk etapta her müşterinin toplamını hesaplayan bir PL/SQL fonksiyonu yaratalım ve daha sonra kodumuzda bu fonksiyonu call edip çıktılara bakalım.

CREATE OR REPLACE FUNCTION get_grand_total (
   p_cust_id_in IN SH.CUSTOMERS.CUST_ID%TYPE)
   RETURN NUMBER
IS
   r_grand_total   NUMBER;
BEGIN
   SELECT SUM (amount_sold)
     INTO r_grand_total
     FROM sh.sales
    WHERE cust_id = p_cust_id_in;

   RETURN r_grand_total;
END;

SET AUTOTRACE ON
SELECT cust_id, 
            get_grand_total (cust_id) grand_total 
FROM sh.customers;


¦statistikler
----------------------------------------------------------
      55503  recursive calls
          0  db block gets
    3066293  consistent gets
          0  physical reads
          0  redo size
     890447  bytes sent via SQL*Net to client
      41104  bytes received via SQL*Net from client
       3701  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      55500  rows processed

plpro

Şimdi bütünsel yaklaşım ile sorgumuzu yazalım.

SET AUTOTRACE ON
  SELECT c.cust_id, SUM (amount_sold)
    FROM SH.CUSTOMERS c, sh.sales s
   WHERE c.cust_id = s.cust_id(+)
GROUP BY c.cust_id;


¦statistikler
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1841  consistent gets
          0  physical reads
          0  redo size
     890452  bytes sent via SQL*Net to client
      41104  bytes received via SQL*Net from client
       3701  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      55500  rows processed

plbut

Bu örnekte de benzer durumun söz konusu olduğunu consistent get ve recursive calls çıktılarına bakarak anlayabiliriz.

Evet sorgularımız da işleyeceğimiz datayı  kayıt kayıt işlemeyi düşünmektense, toplu olarak işlemeyi düşünmek daha performanslı veritabanı işlemleri yapmanın ilk adımı. Veritabanı işlemlerimizi yaparken problemleri çözmeye bu şekilde yaklaşmak gün sonunda daha az kaynak tüketen ve daha hızlı çalışan işler üretmemize olanak sağlayacaktır. Umarım farkındalık anlamında faydalı bir yazı olmuştur.

 

Kaynaklar:

https://mwidlake.wordpress.com/2009/06/02/what-are-consistent-gets/

 

Advertisements

About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Uncategorized and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s