Blog ' da Ara

Loading

6 Temmuz 2011

TEMEL INDEX PRENSIPLERI

Merhabalar,
Bu yazımızda Oracle Performans geliştirmleri konsuunda bir çerçeve çizip, farklı açılardan tanımlamalar yaptıktan sonra query tuning için gerekli olan index prensiplerinden ve temel query tuning prensiplerinden örnekler ile bahsetmeye çalışacağız.
                Öncelikle Oracle Performans Tuning Nedir? Şeklinde bir soruya benim vereceğim cavap; Oracle Veritabanına dayalı uygulamaların mümkün olan en kısa zamanda işlevini gerçekleştirmesi için yapılan çalışmalar diyebiliriz.

 
                Bir uygulamanın Tuninge ihityaç duymasının sebebi ise, işlediği yada okuduğu verilerin, veritabanındaki tablolarda mevcut olan veriler olduğu için, bu verilere ulaşma zamanı gecikebilir.Şöyle ki eğer bir tabloda ki veri sayısı devasa boyutlara ulaştığında örneğin milyonlar yaa milyarlar olduğunda , oracle bu tabloadan bu veriyi bulması için bir teknik kullanmalıdır.Yani verinin yerini ancak bilirse direk ulaşabilir.Aksi takdir de tüm verileri kontrol etmesi gerekecektir ki buda uygulamayı bekletmesine sebep olur.Mesela 20 GB lik bir tabloyu kullanan bir uygulamada performans çalışması yapılmamış ise bu uygulamanın hızı, bilgisayarınıza 20 GB lik veriyi remote kopyalamaktan daha   yavaştır diyebiliriz.İşte bu yavaşlığı engellemede Performans geliştirmeleri büyük önem taşır. Performans Tuning işlemlerini şöylece guruplandırabilriiz.
                1-)Veritabanı modellendirilirken (İlişkelerin tanımlanması ve uygulamanın kullanacağı verilerin tutulduğu tabloların tasarım aşaması): Bu aşamada tasarımcı, veritababını performansa yönelik tasarlamaıs gereklidir.Tabloların tutabileceği data miktarını, transaction yoğunluğunu, dataların tipini, bu tablolara yapılacak DML ve DDL yoğunluklarını tahmin edip yada uygulama mimarisini bilen kişilerle bir araya gelip, performans sıkıntısı oluşturmayacak şekilde data akış diyagramını sağlayacak tablolar ve ilişkiler tanımlamalıdır.
Aslında Performans geliştirmelerinde en önemli ve temel çalışma modellemedir.Çünkü model olarak DML ve DDL darboğazlarına olanak sağlayan yada sağlayacak şekilde tasarlanmış bir veritabanını obje, query yada hardware bazında tune etmek bazen imkansız hale gelebilir.
2-)Donanım seçilmesi:Donanım’ dan kastımız uygulamanın ve oracle veritabanının üzerinde koşacağı cpu, ram, disk yapısı’nın performansıdır.Donanım eğer yüksek performansı desteklerse performans geliştirmelerinde de donanımsal engel yaşanmayacağından, memory de yada disk hızından yararlanırkende en az kısıtlılıkla iyi bir performans kazanılmış olur.
3-) Oracle Instance Tuning; Oracle instance performans geliştirmelerinde donanıma bağlı olarak etkin bir rol oynar.çünkü oracle’nin donanımsal kaynakları ne kadar kullanacağına dair kararları instance parametrelerini düzenlemekten geçer.
4-)Query Tuning:Uygulama içerisinde çağrılan sql querylerin performansını geliştirmektir.Bu kimi zaman bu querylerin ulaştığı tablolara index tanımlama ile çözümlenebilirken, Kimi zaman query yeni baştan yazılması gerekmektedir.Bu noktada uygulamanın arayüzüde değişebilir.
Performans geliştrimelerindeki metodolojiyi kategorize etmeye çalıştık.Bizim asıl odak noktamız temel index prensipleri ve performans bakımından doğru querylerin yazılması için temel prensiplerin üzerinde durmak olacak.
TEMEL INDEX PRENSIPLERI
İndexler bir kitabın içindekiler kısmı gibidir.İndexler ile oracle işlemcisi istenen row direk okuyabilir ve tek bir rowu bulmak için tüm tabloyu okumak zorunda kalmaz.
İndexler, eğer aranan veriler tablonun %5 inden daha az ise kullanılır, aksi takdirde oracle için tüm tabloyu full okumak daha kazançlıdır.
İndexlerin kullanılması için index kolonunun, query nin where koşulunda filtre olarak kullanılması gerekir.
Bir veya daha fazla kolon üzerine index oluşturulabilir.
Eğer querynin where koşulunda fonksiyon kullanılmak zorunda ise buna yönelik özel index oluşturulabilir.
İndexler genel olarak SELECT, UPDATE, DELETE işlemlerini hızlandırırken,MERGE, INSERT işlemlerini yavaşlatabilir.
Bir indexin kullanılabilir olması için query nin indexi bastırıcı modda olmaması gereklidir.  Bunun için querynin yazımına dikkat edilmelidir.İndexi bastıran yazım tipleri aşağıdaki gibidir.
n  NOT EQUAL à <> yada != işaretinin where koşulunda kullanılması.
Mesela customer tablosunun cust_id kolonu indexli dahi olsa aşağıdaki query ile bu indexin kullanılması imkansız hale gelir.
Select  cust_id,cust_name
From customers
Where cust_id <>225;
Not equal işareti where koşulunda bulunduğunda oracle belirtilen değerden farklı değerleri belirlemek için tüm tabloyu okur. Eğer büyük bir tablo ile çalışıyorsak bu performans kaybına sebep olur.
Peki eğer bir kritersen farklı dataları çekmem gerekiyorsa bu işi performaslı bir şekilde nasıl yapabilirim?
Bu nokta da tavsiyem aşağıdaki query ile işlem gören datalar tablonun nekadar lık bir ksımıdır, araştırmalısınız.
Select (select count(*) from (YOUR_QUERY)
/select count(*) from (TABLE_ACCESSED))*100 data_selectivity
from dual;
Eğer yukarıdaki query size 5 den daha büyük bir değer dönüyorsa bu noktada not equal kullanıp kullanmamak öenmli değildir.Ama 5 den küçük bir değer dönüyorsa bu noktada indexin kullanımı enbgelleniyor demektir ve siz ima ettiğiniz dataları subquery yazarka yada başka bir kaç operatör yardımı ile ima etmeniz gerekmektedir.
n  IS NULL veya IS NOT NULL; Kelimelerinin where koşulunda kullanılması;
Not equel operatöründe olduğu gibi is null ve is not null kelimeleride index sahibi bir kolon için filtre olarak kullanılıyorsa bu kolon üzerinde ki index kullanılamaz.
Bu noktada yapılacak şey yine işlem gören dataların seçiciliğine  bakıp, duruma göre farklı bir çözüm geliştirmek gerekebilir.
Eğer null olan rowlar  %5 lik bir kısımdan daha az ise bu durumda bu durumda null olan kolonun indexlenmesi aşağıdaki gibi yapılabilir.
Bir  t  tablosunda b kolonunun null olan rowları olduğunu ve biz bu rowları indexlemek istediğimizi varsayarsak;
            create table t (a  number,number);

Yukarıdaki gibi basit çe tablomuzu oluşturuyoruz,
     
            begin
                  for i in 1 .. 100
                        loop
                         if mod (i, 25) = 0 then
                              insert into t (a, b)
                              values   (i, null);
                               else
                               insert into t (a, b)
                               values   (i, i * 2);
                         end if;

                        commit;
                  end loop;
      end;

Bir for döngüse ile tablomuza test verileri atıyoruz;
                create index i on t (nvl(b,0));

şeklinde index oluşturuyoruz ve daha sonra select * from t where b is null; şeklinde olan  sql sorgu aşağıdaki gibi değiştiriyoruz..
                select * from t where nvl(b,0)=0;

Birinci sorgu index kullanımını engeller , 2. sorgu ise i indexinin kullanımını engellemez.oysaki aynı rowları getirmmektedir.Aşağıdaki şekillerde execution planları (sorgu işleyiş planı) gözükmektedir.

  1.sorgu(index kullanmaz)  

2.sorgu(index kullanır)
                                          
n  Fonksiyon kullanılması;
SQL querylerin where koşulunda eşitliğin sol tarafında fonksiyon kullanılması yine indexlerin kullanılmasını engelleyen bir durumdur.Mesela  select ... where upper(first_name)= 'ali' şeklinde bir kullanım index kullanımını engeller.
Bu durumda ya sql değiştirilmeli ve bu fonsiyon kullanımından vazgeçilmeli yada aşağıdaki gibi bir fonksiyon oluşturulmalıdır.Bu noktada unutulmamalıdırki, indexlerinde hem işlem hemde depo anlamında bir maliyeti vardır, ve büyük tablolarda bu kayde değer bir biçimde kendini göstermektedir.
create index idx_uper_first_name on employees (upper(first_name));
Yukarıdaki gibi   eğer where koşulunda fonskyion kullanılması zorunlu ise örnekteki gibi bir fonksiyon index kullanılması gerekli olacaktır.
n  Yanlış data tipinin sorgulanması;
Örnek şema olan hr üzerinden yine örnek verecek olursak department_id kolonunun data tipi number dir ve sorgular where department_id=10 şeklinde geliştirilmelidir.
Eğer biz sorgumuzu where department_id='10' şeklinde geliştirirsek oracle bize yine doğru sonucu dönecektir, ancak number bir alana char filtre uygulandığı için bu kolon üzerinde index olsa bile kullanılmayacaktır.
Bir kolonun index2e ihtiyacı olup olmadığını belirlemek için o kolona ait selectivity yani data seçiciliğine bakarız. Selectivity aslında dataların homojenlik yada heterojenlik durumunu sorgulamaktır.Bu durumda data dağılımı ne kadar heterojen ise indexlerde o derecede gerekli ve performas kazandırıcı olacaktır.
Selectivity = sorguda işlem gören veri miktarı / toplam veri miktarı
Bu noktada aşağıdaki sorgunun seçiciliğini inceleyelim;

select * from hr.employees where employee_id=100;
Selectivity bulma işlemini yapan bir pl/sql blok aşğıdaki gibi oluşturulabilir,
      DECLARE
   amount_of_row   NUMBER;
   total_row       NUMBER;
BEGIN
   SELECT   COUNT ( * )
     INTO   amount_of_row
     FROM   (SELECT   *
               FROM   hr.employees
              WHERE   employee_id = 100);

   SELECT   COUNT ( * )
     INTO   total_row
     FROM   (SELECT   * FROM hr.employees);
   DBMS_OUTPUT.put_line (
      'Selectivity : ' || ROUND (amount_of_row / total_row, 4) * 100
   );
END;

Burada elde ettiğimiz selectivity değeri 5 den küçükse, employee_id kolonu üzerine index tanımlamak gerekli ve performansı önemli ölçüde artırır nitelikte olacaktır.
Bununla birilite bilinmesinde fayda olacak bir diğer konuda oracle da kullanılan index tipleridir.Bir kolonun içerdiği dataların tipi ve saçılma grafiği göz önüne alındığında farklı index tiplerinini kullanılması gerekebilir.
Bu index tipleri;
B-TREE INDEX; Genel amaçlı kullanılan indexdir.Aşağıdaki gibi employees tablosunun employee_id kolonuna b-tree index oluşturulabilir.
create or replace index idx_1 on employees(employee_id);
BITMAP INDEX; Veri ambarı uygulamalarında daha çok kullanılan index türüdür.Karar mekanizmalarının yoğun olduğu durumlarda yüksek performans sağlar.Örneğin Bir kolonda sadece çalışanların cinsiyet bilgisi (bayan/erkek) tutuluyorsa bu kolon üzerine bitmap index tanımlamak gerekebilir.
create bitmap index b_idx_1 on employees (employee_sex);
HASH INDEX; Cluster yada hash cluster oluşturduğunuzda bir cluster key tanımlarsınız.Oracle bu cluster key'i ile alakalı dataları aynı blok üzerinde tutar.Bu gibi durumlarda hash index kullanımı b-tree index kulanımından 4 kat daha hızlı olacaktır.
REVERSE KEY INDEXES;Eğer disk üzerinde ardışıl datalar yükleniyorsa bu durumda indexler I/O ile ilgili darboğazlara sebep olabilir.Bu problemi çözmek için indexlerin farklı diskler yada farklı tablespaceler içinde  tutmak gerekir.Reverse key index ksıtlı disk miktarına sahip ve aynı zamanda yüksek miktarda data girişi çıkışının olduğu sistemkler için bir performans çözümü olabilir.
FUNCTION BASED INDEX; Bazı durumlarda query nin where koşulunda fonksiyon kullanılması şart olur ve budurumda normal index bastırılır ve index kullanımı engellenir.Bu tip problemlerin çözümü için Fonksiyon index kullanılabilir.
Bu konu ile ilgili açıklama ve örnek daha önce vermiştik.
Ancak Fonksiyon indexlerin kullanılabilmesi için query_rewrite_enabled parametresi true olarak set edilmelidir.
ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE;
GLOBAL ve LOCAL INDEX;Local indexler partition yapılmış bir tabloda aynı partition key için ve yine aynı partition sınırları içinde oluşturulan indexlerdir.Local indexler b-tree yada bitmap indexler olabilir.Global index ise bir çok tablo için yine birçok key üzerinde oluşturulmuş ancak tek bir index partition olan indexlerdir.

8 yorum:

  1. Türkçeye biraz daha dikkat.

    YanıtlaSil
  2. Çok güzel olmuş , teşekkürler

    YanıtlaSil
  3. az ayrıntılı

    YanıtlaSil
  4. Bu konuda gördüğüm nadir yazılardan biri elinize bilginize sağlık.

    YanıtlaSil
  5. Bakış açısı ve anlatım gayet güzel. İndex tiplerinin her biri için bir örnek olsaydı çok iyi olurdu. Türkçe kullanımına dikkat etmek Özcan Abi için faydalı olacaktır.

    YanıtlaSil

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