Blog ' da Ara

Loading

8 Şubat 2012

Oracle Sql Tuning Metodları

Sql Tuning Metodoloji si aslında sql querylerin yani uygulama tarafından sürekli kullanılmakta olan sqllerin performansının artırılması için oluşturulmuş standart çalışmalar bütünüdür diyebiliriz. İşte bu yazımızda inefficient (kötü yazılmış sql) nedir tanımlayacak ve daha sonra bu sqlleri belirlerken yada monitör ederken kullanılacak ttolları ve yöntemleri inceleyeceğiz. En son kısımda da performans geliştirme için neler yapılabilir listeleyeceğiz.
    Bir sql Neden Yavaş Çalışır?
•    Bir sql in nasıl çalışacağınız belirleyen oracle optimizer (10g ve Sonrasında COST Based Optimizer) ‘ın kullandığı data yoğunluğu ve dağılımına dair istitistiklerin güncel olmamaıs ve doğruyu bilgiler sağlamaması.
•    Sql’in veriye erişirken kullandığı index, view gibi yapıların bozulması yada geçerliliğiniz yitirmesi.
•    Optimizerın bir sql için cost,cardinality gibi değerleri yanlış hesaplaması ve dolayısıyla yanlış bir şekilde sql i çalıştırması.
•    Kötü yazılmış sqller.Örneğin Union all yerine union kullanmak yada join içerisine gereksiz tabloların sokulması yada indexi kullanılmayacak hale sokan filtrelerin yazılması.

Şekil1:Performans Monitor ve Tuning Metodları
   
   
    Şekil1 de Performans Monitor yapabileceğiniz ve bu bilgileri kullanarak Tuning önerileri bulabileceğiniz toollar ve birbirleri ile bağlantıları gösterilmiştir. En yukarıda dikkat ettiyseniz services yazar. Aslında bu tooların hepsi servis bazında bir raporlarma yada monitoring yapar. İşte performans monitör yaparken de öncelikle database performansını kötü etkileyen servisi bulmak yapılacak en doğru işlemdir. Zaten çokda zor bir şey değildir. Yukarıdaki tooların hemen hemen hepsinde bu bilgi verilir. Sadece bilinç olmak açısından belirttim.
    Sonrasında performans darboğazı oluşturan event nedir bulunmaya çalışılır.Örneğin AWR raporlarında top 5 wait event bölümünde performas açısında en kötü olan 5 wait event sıralanır.Yine ASh raporunda da o an aktif olan sessionlara ait bilgiler bulunur. ASH raporunda Top Events Kısmında performans darboğazına sebeb olan   eventi bulabilirsiniz.
    Artık yapılacak şey bu eventi oluşturan veritabanındaki işlemi bulmaktır.Bu kimi zaman bir kullanıcı tarafından gelişi güzel yazılmış bir sql olabileceği gibi bir backup prosessi, log switch işlemi yada networksel bir durum bile olabilir.
    Eğer sorunu keşfettiyseniz çözüm için gereken %50 aksiyonu tamamlmışssınız demektir.Artık sorununuzu araştırmak ve çözüm geliştirmek sizin bilginize ve araştırma yönünüze kalmıştır. Yada aşağıda şekil 2 deki Enterprise Manager sayfalarından sorununuza uygun olanı kullanarakta çözüm üretebilirsiniz.
   
   
Şekil 2 : Enterprise Manager Performans Monitor And Tuning sayfaları

Şimdiye Kadar bu yazıda problem tespiti ile ilgili birkaç yönlendirici bilgi vermeye çalıştık.Bu tool ların kullanımı ile ilgili ilerde detaylı yazılarımız olacaktır. Aşağıda da problem çözümü ile ilgili tool ve yöntemlere göz atabilirsiniz.
Sql Tuning Araçları
Sql Tuning Advisor:Sql cümlelerini analiz eder ve performans artırıcı olabilecek tavsiyeleri sunar.
Sql Tuning Sets:Sql cümlelerinin tutulduğu bir repository database dir.Farklı makinalara taşınabilir.Sql Tuning Advisor, Sql tuning setleri kullanarak tekrar tekrar çalışabilir.Sql Tuning setler versiyon upgrade lerin optimizer uyumluluğunu test etmek içn dahi kullanılabilir.
Sql Access Advisor:Sql cümlelerini analiz eder ve index,view,partition,materialized viewlar ile ilgili tavsiyeler sunar.
Sql Performance Analyzer: Optimizer Upgrade  yada en basit anlamda bir index yaratılması sql cümlelerinin performansında bir sapmaya yol açabilir. İşte bu sapmayı belirler ve bize bilgi sunar.
Sql Monitor: Çalışan sqllerin performansını monitör etmemizi sağlar.
Sql Plan Management: Sql cümlelerinin planlarını kontrol eder ve kullanılabilirlik analizi yapar.Sql Plan Management ile farklı planları gözlemleyebilir ve bu planlara Sql Plan History de erişebilirsiniz.
Sql Tuning Çalışmalarında dba görevleri
1-İstatistiklerin güncel olup olmadığını kontrol etmeli ve güncel olmayan istatistikleri yenilemelidir.Aşağıdaki scriptle objelerin istatistiklerini görebilir ve istatistik toplayabilirsiniz.Stale kolonu ‘NO’ değerine sahipse istatistikler güncel demektir. Stale kolonu ’YES’ değerine sahipse istatistikleri güncellemeniz gerekir.
SELECT owner,
       table_name,
       partition_name,
       object_type,
       last_analyzed,
       global_stats,
       stale_stats
  FROM dba_tab_statistics
 WHERE owner = :schema_name
 SELECT owner,
       index_name,
       table_owner,
       table_name,
       partition_name,
       object_type,
       last_analyzed,
       global_stats,
       stale_stats
  FROM dba_ind_statistics
  where owner =:schema_name

    Tüm schemanın istatistiklerini toplamak için
       
        BEGIN
           DBMS_STATS.gather_schema_stats ('FMSDET',
                                   degree    => 8,
                                   options   => 'GATHER STALE',
                                   cascade   => TRUE);
END;
   
    Tablo istatistiklerini toplamak için;
       
        BEGIN
  DBMS_STATS.gather_table_stats
                                (ownname               => 'FMSDET',
                               tabname               => 'ENTITY',
                                estimate_percent      => 1,
                                CASCADE               => TRUE,
                                DEGREE                => 8,
                                 no_invalidate         => false,
                              granularity           => 'GLOBAL AND PARTITION',
                              method_opt            => 'FOR ALL INDEXED COLUMNS SIZE AUTO'
                               );
           COMMIT;
END;
/

    Index İstatistiklerini toplamak için;

        BEGIN
  DBMS_STATS.GATHER_INDEX_STATS( ownname => 'MARDATA'
  ,indname => 'kk_harbil_X'
  ,estimate_percent => 3
  ,degree => 2
  ,granularity => 'ALL',
  no_invalidate => FALSE
  )
COMMIT;
        END;
        /

    2-Maliyeti yüksek olan sqlleri bulmak. Bunun içinde aşağıdaki sql kullanılabilir. Aynı şekilde enterprise manager üzerinden top activity sekmesinden maliyeti yüksek sqller tespit edilebilir.Aşağıdaki script ile cpu bakımından maliyeti en yüksek 20 sql tespit edilebilir. Eğer Memory maliyeti yüksek sqlleri tespit etmek istiyorsanız order by 7 yerine order by 8 (8 burada memoryCount sql deki kolonunun kolon sırasıdır.Ayrıca modül ismide sql i çalıştıran programı aratmak içindir.Java ile yazılmış uygulamalarda genelde JDBC şeklindedir.)
       
select * from ( SELECT parsing_schema_name "Owner",
         last_load_time,
         last_active_time,
         executions,
         rows_processed,
         ROUND (rows_processed / executions) AS "RowCount",
         ROUND (cpu_time / executions) AS "CpuTimeCount",
         ROUND (buffer_gets / executions) AS "MemoryCount",
         ROUND (disk_reads / executions) AS "DiskReadCount",
         executions "# of Executions",
         (elapsed_time / executions) / 1000000 AS "TimeSpend sec.",
         DBMS_LOB.SUBSTR (SQL_FULLTEXT, 4000, 1) AS SqlFullText
    FROM gv$sql
   WHERE executions > 0
     AND parsing_schema_name = :schema_name
     AND module like '%&module_name%'
     AND TRUNC (last_active_time) = TRUNC (SYSDATE)
ORDER BY 7 DESC)
where rownum <21       

    3-Excution planları kontrol etmek ve gerekirse accecs pathleri yönlendirmek. Yani FULL scan yapan sqlleri tespit edip index den okumasını sağlamak ve eğer index yoksa index oluşturmak gibi.
    4-Access pathler yani mesela indexler yeterli değilse yeni index stratejileri oluşturmak veya indexleri rebuild etmek.

    Uygulamadaki Genel Performans Problemleri
   
1-Yanlış bağlantı Yapılandırması: Uygulamanın her bir yapacağı iş için veritabanına connect olması ve sonrasında disconnect olmasıdır. Bu  durumda bağlantı sürecince geçen zaman wait time olarak yansıyacak ve buda uygulamanın yavaş çalışmasına sebebp olacaktır.Bunun yerine bir kez bağlantı kuran ve daha sonra o bağlantı üzerinden işlemlerini yapan connectionlar tercih edilmelidir.
   
    2-Shared Pool Alanının Yanlış kullanımı: Bind  variable kullanılmazsa oracle uygulamanın filtre değeri değişen tüm sqllerini farklı sql olarak tanımlayacak ve herbiri için execution plan oluşturmaya yönelik yeniden parse edecektir.Bu durumda shared pool dolacak ve library cache lock lar oluşacaktır.
Shared pool dolması bile o sql i bekleyen ekran yavaş çalışacaktır.

    3-Ineffektif sql : Poor performans sağlayan sqllerin yazılması.Bu konuda geniş bir araştırma yapmanızı öneririm ancak öncesinde daha önce yazdığımız temel index prensipleri yazımızı okumak faydalı olacaktır.
   
    4-Uygunsuz Acces Pathler: Gereksiz yere index kullanımı yada full tablo taramalarıda uygulamaların yavaşlamasında önemli bir etkendir.
   
    5-Excessive Serialization:Aşır seri transactionların  kullanımı undo segmentleri kullnırken birden çok kullanıcının beklemsine yol açacaktır.

    Proaktif Tuning Metodu
   
Proaktif tuning metodu aslında tuning işlemlerine veritabanını design ederken başlamak demektir. Şöyleki; Basit  tablo tasarlama, Uygulama tarafından yönetilecek datanın performans cycle larından geçirilerek modellenmesi, Tabloların relationlarının güçlü tasarlanması ve fazla index gerektirmeyecek şekilde business model tasarlanması (Fazla index kullanımı insert işlemlerini yavaşlatacaktır). View ların efektif bir biçimde kullanımı yine diskk yerine memory kullanımını sağlayacağı için uygulamayı hızlandırıcı bir etkendir.
Parse maliyetinden kaçınmak için bind variable kullanılamlı ve cursor shaing ‘ e önem verilmelidir. Yazılan her bir sql planı kontrol edilmeli, ne kadar data çekmesi gerektiği ve ne kadar  data çektiği karşılaştırılmalıdır.
                                                                                                               Özcan YILDIRIM

2 yorum:

"Sorularınız ve Eleştirileriniz Değerlidir"