Oracle Veritabanında Index Kavramı ve Kullanımı -3

Merhaba Arkadaşlar,

Bu yazımda Oracle veritabanında index konusuna devam edeceğim.  Bu yazım da önceki 2 yazımın devamıdır bu yüzden önceki 2 yazımı da okumanızı tavsiye ederim. Bu yazımda önceki yazıda musteri tablosuna attığım index üzerinden devam edip index maintenance kavramını anlatacağım.

Indexler bizim için önemli bir obje olduğu için Production sistemlerde Performansın iyi olması açısından belli aralıklarda  index maintenance yapmamız gerekmektedir. Indexler üzerinde yaptığımız operasyonlar genellikle aşağıdaki gibidir.

• Index lerin görünmez yapılması
• Index Rebuild işlemi
• Index lerin adının değiştirilmesi
• Index lerin Silinmesi

Indexlerin Görülmez Yapılması: Indexler Oracle 11g ile beraber artık görünmez yapılabiliyor. Indexlerin görünmez yapılması demek Query Optimizer tarafından görünmemesi demektir yani sorgular çalışırken Index fiziksel olarak olmasına rağmen görünmez yapıldığı için Optimizer bunu görmeyeceği için Indexi kullanmayacaktır. Bu özellikle Indexlerin tablolar üzerindeki performanslarını test ederken çok işimize yaramaktadır. Oracle 11g den önce bu ihtiyacımızı Indexleri kullanım dışı ( Unusable ) bırakarak yapmaya çalışıyorduk ancak bu defa da Indexi tekrar kullanıma aldığımız zaman Index i Rebuild etmek zorunda kalıyorduk buda büyük tablolarda çok fazla zaman almaktaydı ve tabloya lock atıldığı için problem yaratmaktaydı. Index lerin Oracle 11g ile beraber Invisible ( Görünmez ) özelliğinin getirilmesiyle Indexi Rebuild yapmadan görünmez yapıp tekrar görünür yapabiliyoruz. Oluşturulan bir indexi aşağıdaki gibi Invisible ( Görünmez )olarak değiştirebiliyoruz.

SQL> Alter index MID_IX invisible;

Invisible modda olan bir indexi aşağıdaki gibi visible ( Görünür ) yapabiliriz.

SQL> Alter index MID_IX visible;

Index Rebuild İşlemi: Tablolarımızdaki Indexler zaman içerisinde tabloların yoğun DML işlemleri ( Insert, Delete vbvb) görmesi sonucunda bozulabilir. Index in bozulması demek kimyasal bozulma gibi anlaşılmasın sadece storage parametrelerindeki bazı değişikliklerden dolayı gerçek performansını vermemesi şeklindedir. Indexler DML işlemlerinin dışında da farkı tablespace lere taşındığında ve UNUSABLE ile devre dışı bırakıldıklarında bozulurlar. İşte belirttiğim bu nedenlerden ötürü bozulan Indexlerin eski gücüne ( Performanslarına ) kavuşmaları için Rebuild edilmeleri gerekmektedir. Bozulan Indexler aşağıdaki gibi Rebuild edilirler.

SQL> alter index <index_name> rebuild;
SQL> alter index MID_IX rebuild;

Production ortamında yukardaki gibi indexi rebuild etmenizi tavsiye etmem çünkü production ortamındaki indexi bu şekilde rebuild etmek demek tabloya gereksiz yere Lock atmak demektir. Bunun yerine Tablonun kesintiye uğramaması için tabloya indexleri Online olarak atmamız gerekir. Indexleri online olarak aşağıdaki gibi rebuild edebiliriz.

SQL> ALTER INDEX <index name> REBUILD ONLINE;
SQL> ALTER INDEX MID_IX REBUILD ONLINE;

Production ortamında çok kullanılan tabloları Online Rebuild yaparken bu işlemi paralel yapmak işinizin daha kısa sürede bitmesini de sağlayacaktır. Parallel kullanırken CPU sayınızdan çok parallelik vermemeniz önerilir. Indexler aşağıdaki gibi parallel ve online olarak rebuild edilebilir.

SQL> ALTER INDEX <index name> REBUILD ONLINE PARALLEL <paralellik_sayısı>;
SQL> ALTER INDEX MID_IX REBUILD ONLINE PARALLEL 4;

Peki şimdi en önemli soruyu soralım Bir indexin Rebuild edilip edilmemesi gerektiğini nasıl anlayacağız ? Yani kafamıza göre gerekmedikçe bir indexi Rebuild etmemiz demek kaynakları boşa harcamak demektir. Indexlerin Rebuild e ihtiyacı olup olmadığını belirlemek çok önemlidir bunun için sürekli maintenance çalışmaları yapılmalıdır.

Tablolarımıza ait indexlerin Index edilip edilmemesi gerektiğinin kararını SYS.INDEX_STATS tablosunu sorgulayarak karar verebiliriz. Bu tabloyu ilk sorguladığımız zaman aşağıdaki gibi boş gelecektir çünkü index imize ait istatistikler toplanmamıştır.

SQL> select * from index_stats;
no rows selected
SQL>

Index e ait istatistiklerin toplanması için Analyze index komutu ile Validate Structure komutunun kullanılması gerekir. Index e ait istatistikler aşağıdaki gibi toplanır.

SQL> ANALYZE INDEX <index_name> VALIDATE STRUCTURE;
SQL> ANALYZE INDEX MID_IX VALIDATE STRUCTURE;

Index_stats tablosunda her zaman için tek bir kayıt olacaktır bununda sebebi bu tabloda aynı anda tek bir indexe ait veri olacaktır.  Her bir indexe ait bu veriler için ayrı ayrı yukardaki analyze index komutu çalıştırılmalıdır. Ör; HR Şemasına ait Employee tablosundaki EMP_NAME_IX indexine ait istatistiklere bakmak için aşağıdaki gibi Analyze komutu çalıştırılır.

SQL> ANALYZE INDEX HR.EMP_NAME_IX VALIDATE STRUCTURE;
Index analyzed.
SQL>

Ardından index_stats komutunu çalıştırdığımızda aşağıdaki gibi bazı değerler gelecektir.

SQL> select * from index_stats;

Yukardaki resimde index_stats tablosunun sonucu bulunmaktadır. Yukardaki kolonlardan en önemlilerini kırmızı kare içine aldım. Sorgu sonucunda dönen 3.kolonda indexin adı bulunmaktadır. Bir indexe ait Dönen sonuçtaki HEIGHT,LF_ROWS ve DEL_LF_ROWS değerleri aşağıdaki gibi olduğunda bu indexleri Rebuild etmemiz gerekecektir.

1. Index_stats tablosundaki HEIGHT değeri 4 ten büyük olduğu zaman.
2. DEL_LF_ROWS / LF_ROWS oranı %20 den büyük olduğunda.

Yukardaki örneğe baktığımız zaman çok küçük olan Employee tablosunda bu değerler istenenenden daha düşük olduğu için Rebuild etmeye gerek olmadığını görüyoruz.

Şimdi asıl örneğimiz olan 100 milyon kayıtlık Musteri tablomuz için aynı değerlere bakıyoruz eğer bu değerler yukardaki kriterlere uyuyorsa o zaman indeximizi rebuild etmemiz gerekecektir. İlgili Tablodan çok miktarda kayıt silip index imizi analiz edelim. Aşağıdaki sorguyu çalıştırıp Height değerine yada DEL_LF_ROWS / LF_ROWS oranına bakıyoruz eğer bu değerler yukardaki verilen Threshold değerlerinden büyükse indeximiz Rebuild edilmelidir diyoruz. Bunun için aşağıdaki sorguyu çalıştırıyorum.

SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*100 as rebuild_orani FROM INDEX_STATS;

Yukarda Sorgunun sonucuna baktığımız zaman Height değeri 4 ten büyük olmadığı için DEL_LF_ROWS / LF_ROWS oranına bakıyorum. Bu oran sorgu sonucunda REBUILD_ORANI alias ıyla göründüğü gibi %69 çıkmış. Bu değer %20 inin çok çok üstünde olduğu için bu indexi Rebuild etmemiz gerektiğine karar veriyoruz. Bu Tablonun Production ortamında olduğunu düşünüp aşağıdaki gibi indeximizi Online ve Paralel olarak Rebuild ediyoruz.

SQL> ALTER INDEX MSDEVECI.MID_IX REBUILD ONLINE PARALLEL 4;
Index altered.
SQL>

Yukardaki sorguyu tekrar çalıştırdığımda yeni değerler aşağıdaki gibidir.

SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*100 as rebuild_orani FROM INDEX_STATS;

Yukarda sorgunun sonucuna baktığımız zaman MID_IX indexini Rebuild ettiğimiz zaman Height değeri 3 ten 2 ye Rebuild oranıda %69 dan 0 a inmiş. Artık index imiz Rebuild e ihtiyaç duymadığı için maximum performansla çalışabilecektir.

Indexlerin Adının Değiştirilmesi: Indexlerin adlarını ihtiyaç duyduğumuzda aşağıdaki gibi değiştirebiliriz. Adı değişen index ardından hemen rebuild edilmelidir.

SQL> ALTER INDEX <orjinal_index> RENAME TO <indexin_yeni_adı>;
SQL> alter index MID_IX rename to MUSID_IX;

Indexlerin Silinmesi: Daha öncede belirtmiştim Index ler eğer gereksiz ise yada kullanılmıyorsa sistem kaynaklarını boşa tükettikleri için silinmesi gerekir. Indexler aşağıdaki gibi silinebilir.

SQL> DROP INDEX <index_name>;
SQL> drop index MID_IX;

Böylece bu yazımın da sonuna gelmiş bulunmaktayım bir sonraki yazıda görüşmek dileğiyle esen kalın.

Oracle Exadata SQL Server Goldengate Weblogic EBS ve Linux konusunda aşağıdaki konularda 7×24 Uzman Danışmanlara yada Eğitimlere mi İhtiyacınız var [email protected] adresine mail atarak Bizimle iletişime geçebilirsiniz.

– Oracle Veritabanı Danışmanlığı
– Oracle Veritabanı Bakım ve Destek
– Exadata Danışmanlığı
– Exadata Bakım ve Destek
– SQL Server Veritabanı Danışmanlığı
– SQL Server Veritabanı Bakım ve Destek
– Goldengate Danışmanlığı
– Goldengate Bakım ve Destek
– Linux Danışmanlığı
– Linux Bakım ve Destek
– Oracle EBS Danışmanlığı
– Oracle EBS Bakım ve Destek
– Weblogic Danışmanlığı
– Weblogic Bakım ve Destek
– Oracle Veritabanı Eğitimleri
– Oracle VM Server Danışmanlığı
– Oracle VM Server Bakım ve Destek
– Oracle EPPM Danışmanlığı
– Oracle EPPM Bakım ve Destek
– Oracle Primavera Danışmanlığı
– Oracle Primavera Bakım ve Destek
– Oracle Eğitimleri
– SQL Server Eğitimleri
– Goldengate Eğitimleri
– Exadata Eğitimleri
– Linux Eğitimleri
– Oracle EBS Eğitimleri
– Oracle VM Server Eğitimleri
– Weblogic Eğitimleri
– Oracle EPPM Eğitimleri
– Oracle Primavera Eğitimleri