İçindekiler
Selamlar ,bu yazımda Veritabanlarında Index kavramının ne olduğunu ve nasıl kullanıldığını anlatacağım. Index kavramı özellikle Oracle Veritabanlarının Performance tuning çalışmaları konusunda çokça karşımıza çıkar.
Veritabanı teknolojilerinde verilerimizin tutulduğu tablolardaki verilere daha hızlı erişebilmek için oluşturduğumuz nesnelere Index denir. Index, bir tablo veya tablo cluster ile ilişkilendirilen, veri erişim hızını arttıran, opsiyonel segment yapısıdır. Bir veya daha fazla alan üzerinde tanımlanan index yardımı ile, tablo segmenti içerisinde rastgele dağılmış küçük veri kümelerine yani disk üzerine rastgele dağılmış blok kümelerine erişim hızı sağlanır. Bir tablo segmentinin belli oranda kayıt içeren bir küme verisine erişirken gereksiz yere tüm tablo verisinin disk üzerinde taranması (FTS) engellenir ve disk I/O işlemi azaltılır.
Index i anlamak için başka bir yazıdan elde ettiğim örnek çok güzel; Ör: Elinizde 100.000 farklı kelime içeren İngilizce-Türkçe sözlüğünün olduğunu düşünün ve siz sürekli olarak bilmediğiniz ingilizce kelimeleri sözlükten bulup türkçe karşılıklarına bakmaktasınız. Eğer bu İngilizce Türkçe sözlüğünüz Kelimelerin baş harf(ler)ine göre sıralanmasaydı ne olurdu ? Siz mesela Computer kelimesini bulmak için neredeyse Tüm sözlüğü baştan aşağı taramanız gerekecekti. Buda haliyle hem çok zaman alacaktır hemde sizi gereksiz yere yoracaktır. Ancak eğer sözlüğünüz Kelimelerin baş harf(ler)ine göre sıralanmışsa, ki mantıklı olan yolda budur bu durumda siz Computer kelimesini bulmak için Sözlüğün içindekiler kısmından C harfinin başladığı sayfaya direk gider ve aramanızı burada gerçekleştirirsiniz. İşte buradaki Sözlüğün içindekiler kısmı ve kelimelerin baş harfine göre sıralanması mantığı ile Veritabanlarında kullanılan Index mantığı aynı şeydir.
Oracle veritabanında bir tablodan veri sorgulandığı zaman Oracle ilk olarak o tabloya ait index varmı yokmu bunu kontrol eder. Eğer index var ise ve istenilen kayıt sayısı tablonun ortalama %15 ine eşit veya daha az ise Oracle istenen verileri Index üzerinden bulup dönderir. Bu tip bir veri sorgulama normalden daha kısa sürede ve daha az maliyetli olarak gerçekleşir ve buna Index Scan denir. Eğer index yoksa yada index varken istenen kayıt sayısı tablonun tamamının %15 inden fazla ise Oracle bu durumda indexten gitmenin daha maliyetli olduğunu düşünüp Full Table scan dediğimiz tablonun tamamını tarar. Full table scan yada Index scan olup olmamasını Query Optimizer karar verir. Yukarda belirttiğim %15 oranıda yine bazen değişiklik gösterse de Oracle ın genel itibariyle bu konuda sunduğu threshold değeri %15 tir. Bu %15 değeri büyük tablolar için geçerli olsada denediğim küçük tablolarda %40 lara kadar varabiliyor. Ancak dediğim gibi genel itibariyle Oracle diyorki eğer sorguladığınız veri, tablonuzun %15 ine eşit yada küçükse tablonuzda index varsa veriyi çok hızlı bir şekilde ve daha az maliyetle size sunmak için ben indexi kullanırım diyor. Böylece istenen verileri bulmak için index kullanıldığında I/O miktarı düştüğü için hem hızlı bir şekilde veriler bulunur hemde sistem kaynakları çok tüketilmez. Tablolarımızda Index kullanmanın avantajları olduğu gibi dezavantajları da vardır. Index kullanmanın dezavantajları aşağıdaki gibidir.
- Indexler fiziksel olarak diskte yer kapladığı için gerekmedikçe Index kullanmak ekstra disk maliyetini artıracaktır.
- Indexler genel anlamda sorgularımızda performansı artırırken DML ( insert,update,delete ) işlemlerini ise yavaşlatmaktadır. Özellikle çok fazla DML işlemleri yapılan tablolarda index kullanmamak gerekir.
- Index kullandığımız zaman veritabanının maintenance ( index maintenance ) yükü artacağı için ekstra bir yük gelicektir.
Genel itibariyle şöyle bir durum ortaya çıkarabiliriz. Veritabanlarında çok fazla sorgu (Select) yapılan tablolarda (Genellikle Rapor çekilen OLAP sistemlerindeki tablolar ) Index kullanmak performans açısından çok faydalı olduğu gibi sistem kaynaklarınıda yormaması yönüyle de bizim için vazgeçilmez bir nimetdir. Veritabanlarında çok fazla DML( Data Manipulation Language – Insert,Update,Delete) işlemleri yapılan tablolarda (Genellikle OLTP sistemlerde görülen tablolar ) Index kullanmak maliyetli ve performans sorunlarına yol açacaktır o yüzden gerekmedikçe böyle tablolarda kullanılmamalıdır.
İndexler aşağıdaki özelliklere sahiptir:
- Usability (Kullanabilirlik) : İndexler kullanılabilir (varsayılan) veya kullanılamaz durumda olabilirler. Kullanılamaz bir index DML işlemleri tarafından korunmaz ve optimizer tarafından yoksayılır. Kullanılamaz bir index bulk loads (büyük hacimli) işlerin performansını artırabilir. Bir indexi silip yeniden oluşturmak yerine, indexi kullanılamaz hale getirebilir ve daha sonra yeniden oluşturabilirsiniz. Kullanılamayan indexler , index segment alanı tüketmez. Kullanılabilir bir indexi kullanılamaz hale getirdiğinizde, veritabanı indexi segmentini siler.
- Visibility (Görünürlük) : İndexler görünür (varsayılan) veya görünmez durumda olabilirler. Görünmez bir index DML işlemleriyle korunur ve varsayılan olarak optimizer tarafından kullanılmaz. İndexi görünmez yapmak, kullanılamaz hale getirmek veya düşürmek için bir alternatiftir. Görünmez indexler, bir indexin silinmesini sınamadan veya genel uygulamayı etkilemeden geçici olarak indexleri kullanmadan önce özellikle kullanışlıdır.
Composite Indexes
Kompozit (Birleşik) index olarak da adlandırılan index tipi, tablodaki birden çok sütunda bulunan bir indexdir. Kompozit bir index deki sütunlar, verileri alacak sorgular için en anlamlı olan sıraya göre görünmeli ve tabloda bitişik olmamalıdır.
Kompozit indexler, WHERE tümcesinin kompozit index içindeki sütunların tümüne veya baş kısımlarına başvuru yaptığı SELECT ifadeleri için verilerin alınmasını hızlandırabilir. Bu nedenle, tanımda kullanılan sütunların sırası önemlidir. Genel olarak, en sık erişilen sütunlar önce gider.
Unique and Nonunique Indexes
İndexler unique veya unique olmayan olabilirler. Unique indexler, bir tablonun hiçbir iki satırının ana sütun veya sütunda yinelenen değerleri olmadığını garanti eder. Örneğin, iki çalışan aynı çalışan kimliğine sahip olamaz. Böylece, benzersiz bir indexde, her veri değeri için bir satır bilgisi vardır. Bloklar veriler sütun verileriyle ile sıralanır.
Unique olmayan indexler, indexe alınmış sütun veya sütunlardaki değerleri çoğaltmaya izin verir. Örneğin, çalışanlar tablosunun ilk_adı sütunu birden fazla Mike değeri içerebilir. Unique olmayan bir index, satır içi sıralamaya dahil edilmez, bu nedenle unique olmayan indexler, sütün verilerine ve rowid’e (artan) göre sıralanır.
Oracle Veritabanı, bitmap index veya cluster key index sütun değeri null olduğunda, tüm sütunların boş olduğu tablo satırlarını indekslemez.
Index Çeşitleri
Oracle Database, performans işlevselliği sağlayan birkaç indexleme alternatifi sunar. Indexler şu şekilde sınıflandırılabilir:
- B-tree indexes : Bu index standart index türüdür. Primary key ve son derece seçici(selectivity) indexler için mükemmeldirler. Kompozit indexler olarak kullanıldığında, B-tree indexleri, indexe alınmış sütunlara göre sıralanmış verileri alabilir. B-tree indexleri aşağıdaki alt türlere sahiptir:
- Index-organized tables : Normal tablolarda (Heap-Organized Table) veriler disk üzerinde hangi bloğa sığıyorsa oraya saklanır, index-organized tablolarda ise veriler, PK için tanımlanmış olan index yapısı içerisinde saklanır. Tablonun kendisi bir index’tir. Bu tip tablolara PK ile erişim çok daha hızlıdır.
- Reverse key indexes : Bu index tipinde, indeksin byte’ları tersinde çevrilerek saklanır, örneğin 103 değeri 301 olarak saklanır. Bunun amacı indekse yapılan insert işlemlerinin birden çok bloğa yayılmasını sağlamaktır.
- Descending indexes : Default olarak ascending olarak tanımlanan index yapısı DESC keyword’ü ile ters bir dizilimle DESCENDING olarak saklanabilir.
- B-tree cluster indexes : Bu index tipi, cluster yapıdaki tabloların cluster key’lerini indexlemek için kullanılır. Index, bir row’u point etmek yerine cluster key ile ilişkili olan row’ları içeren bloğu point eder.
- Bitmap and bitmap join indexes : Bu index türü belli başlı değerlerin sürekli olarak tekrar etmesi durumunda kullanılır.
- Function-based indexes : Index tanımlı alanın, WHERE koşulunda bir fonksiyon içerisinde kullanılması kaçınılmaz ise bu alanı fonksiyonu ile beraber indexlememiz gerekir; aksi halde index kullanılmaz.
- Application domain indexes : Uygulamaya özel özelleştirilmiş bir indexdir.
B-Tree Index
Bu index türü en çok kullanılan ve Oracle da default olarak gelen Index türüdür, primary-key ve yüksek selectivity’ye sahip alanlar için en uygun indeks tipidir. B-tree indeks tipinde veri, indekse göre sıralı biçimde döndürülebilir.
B*Tree yapısında 3 farklı seviyeden bahsedilir. Bunların ilki en tepede duran “root”tur. En altta da “leaf” denilen seviye ve bu ikisi arasında da “branch” (lar) bulunur.Örneğin “root” değerimiz 50 olsun. Bundan küçükleri sola , büyükleri sağa , branch tada boyle bir ayrım yaptığımzı düşünerek yeniden bir dallanma gerçekleştirdiğimizde (leaf) , kabaca bir B Tree oluşturmuş oluruz.(Bu yapının etkili kullanımı için dengeli bir yapıda tutulması gerekmektedir.Yani veriler bir tarafa dogru yığılma yapmamalıdır.) En etkili avantajı ağaç üzerinde milyonlarca kayıt olsa bile en fazla 2 ya da 3 I/O ile bir kayda ulaşmanın mümkün olmasıdır. (Dezavantaj ise bir düğümün silinmesi ya da değiştirilmesi durumunda ağacın yeniden organize edilmesi ihtiyacının olmasıdır.)
Oracle acısından bu ağacın önemine gelecek olursak , Oracle indeks yapısını bu ağaç yapısı üzerine oturtmuştur.Yani bir kolon üzerinde bir indeks oluşturduğunuzda bu indeks (kolon değeri) ve o verinin bulundugu satır bilgisi(rowid) bu ağaçta uygun yere yerleştirilir.(ROWID, verinin fiziksel adresidir). Leaf seviyesinde ya tek bir değer olur ya da bir değer aralığı bilgisi bulunur. Ama hepsi sıralıdır. Leaf seviyeler birbirine linkli liste mantığına göre bağlıdır.
Örneğin test tablomuzda “id” kolonu üzerinde “index” oldugunu varsayarsak; Bu aşamada “id” kolonunun değerleri artan sırada dizilir.Degeri ve tablodaki satır adresi(rowid) bilgisi saklanır, bu şekilde indeksimiz oluşturulmuş olur.
select * from test where id = 12345
sorgusunda normal şartlarda indeks tarama (index scan) yapılır, önce “id” değeri indeksten bulunur ve satırının rowid bilgisine ulaşılır ve rowid bilgisi ile tabloya nokta atışı yapılarak veriler getirilir. “….where id between 200 and 300…” gibi bir sorguda “leaf” ler üzerinde gezilerek aralık (range) bilgisine ulaşılır.B Tree indeks yapısında tekil(unique) olmayan bir indeks değeri yoktur.
- index oluşturmak için aşağıdaki sorguyu kullanabiliriz.
create index test_index on test (id);
- İndexi silmek için aşağıdaki sorguyu kullanabiliriz.
drop index test_index;
Index Scans
Index Scan, optimizer tarafından en iyi execution planı üretmek için için Sql Engine tarafından kullanılan bir erişim yoludur.
Bir SQL cümlesi sadece indexli alanları sorguluyorsa, veritabanı bu değerleri sadece indexe giderek ve tabloya hiç uğramayarak getirebilir. Indexli alanların haricinde, tabloda yer alan bir veya daha fazla indexsiz alan da isteniyorsa, veritabanı rowid’ler veya cluster scan yardımıyla tabloya giderek istenilen kayıtları getirir.
Index scan için birden fazla yol vardır;
- Assessing I/O for Blocks, not Rows
- Index Unique Scans
- Index Range Scans
- Index Range Scans Descending
- Index Skip Scans
- Full Index Scans
- Fast Full Index Scans
- Index Joins
- Bitmap Indexes
Assessing I/O for Blocks, not Rows
Oracle, bloklarla I/O yapar. Bu nedenle, optimizer’ın full table scan kullanma kararı, satırlara değil erişilen blokların yüzdesinden etkilenir. Buna index clustering factor denir. Bloklar tek satırlar içeriyorsa, erişilen satırlar ve erişilen bloklar aynıdır.
Bununla birlikte, çoğu tablo her blokta birden çok satır içerir. Sonuç olarak, istenen sıra sayısı birkaç blokta bir araya toplanabilir veya daha fazla sayıda bloğa yayılabilir.
Index clustering factor indexin bir özelliği olsa da, index clustering factor aslında benzer indeksli sütun değerlerinin tablodaki veri blokları içindeki yayılması ile ilgilidir.
Daha düşük bir index clustering factor, bireysel sıraların tabloda daha az blok halinde konsantre olduğunu gösterir. Buna karşılık, yüksek bir index clustering factor, bireysel sıraların tablodaki bloklar arasında daha rasgele dağıldığını gösterir. Bu nedenle, yüksek bir index clustering factor, satırları satırsız almak için bir aralık taramasının kullanılmasının daha pahalı olacağı anlamına gelir, çünkü tablodaki daha fazla bloğu verileri döndürmek için ziyaret edilmesi gerekir. Örneğin;
- 9 satırlık bir tablo var,
- Tablo da col1 sütünu için non-unique bir index var,
- C1 sütünu A,B ve C değerlerini saklasın,
- Tablonun sadece 3 tane bloğu olduğunu düşünelim.
Senaryo 1 ; index clustering factor aşağıdaki diyagramda görüldügü gibi düşüktür. Bunun nedeni, c1 için aynı indekslenmiş sütun değerlerine sahip satırların, tablodaki aynı fiziksel blokların içinde bulunmasıdır. A değerine sahip tüm satırları döndürmek için aralık taraması kullanmanın maliyeti düşüktür, çünkü tabloda yalnızca bir blok okunması gerekir.
Block 1 Block 2 Block 3
——- ——- ——– A A A B B B C C C |
Senaro 2 : Tablodaki aynı satırlar, index değerleri tablo blokları boyunca dağılmış olacak şekilde (aynı sıralama yerine) dağıtılacak şekilde yeniden düzenlenirse, index clustering factor daha yüksektir. Bunun nedeni, tablodaki üç bloğun hepsinin col1’de A değeri olan tüm satırları alabilmek için okunması gerektiğidir.
Block 1 Block 2 Block 3
——- ——- ——– A B C A B C A B C |
Index Unique Scan
Bu scan işlemi, en fazla, tek bir rowid döndürür. Oracle, UNIQUE veya PRIMARY KEY alanlarda bir işlem varsa Index Unique Scan gerçekleştirir.
----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)| | 1 | NESTED LOOPS | | 3 | 189 | 10 (10)| | 2 | NESTED LOOPS | | 3 | 141 | 7 (15)| |* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)| | 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)| |* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | | | 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)| |* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | | -----------------------------------------------------------------------------------
Sırasıyla job_id_pk ve dept_id_pk indeksleri kullanılarak iş ve departments tablosunda index taraması yapılır.
Index Range Scan
Index Range Scan, selectivity verilere erişmek için kullanılan ortak bir işlemdir. Veri tabanı genellikle seçici verilere erişmek için bir dizin aralığı taraması kullanır. Veriler, artan index sütunlarına göre döndürülür. Aynı değere sahip birden çok satır, artan sırada satır sırasına göre sıralanır.
Optimizer, aşağıdakiler gibi koşullarda belirtilen bir indexin bir veya daha fazla önde gelen sütununu bulduğunda bir range scan kullanır:
- col1 = :b1
- col1 < :b1
- col1 > :b1
Örneğin, orders tablosu eski bir sistemden alındı ve siz eski sistemde kullanılan referansa göre siparişi sorguluyorsunuz. Bu referansın order_date olduğunu varsayalım.
SELECT order_status, order_id FROM orders WHERE order_date = :b1;
--------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 3 (34)| | 1 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 20 | 3 (34)| |* 2 | INDEX RANGE SCAN | ORD_ORDER_DATE_IX | 1 | | 2 (50)| --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ORDERS"."ORDER_DATE"=:Z)
Bu oldukça seçici bir sorgu olmalı ve istenen satırları almak için sütundaki dizini kullanarak sorguyu görmelisiniz. Döndürülen veriler, order_date için satır satırlarına göre artan sırada sıralanır. Order_date dizin sütunu burada seçilen satırlar için aynı olduğundan, veriler satır satırlarına göre sıralanır.
NOT : Index Range Scan (Min/Max), Oracle, index içerisinde 0(sıfır) veya daha fazla satır karşılığı bulmasına rağmen, sadece ilk veya son kaydı okuması yettiği durumlarda (MIN veya MAX aggregate function) bu scan yöntemini tercih eder.
Index Range Scans Descending
Index range scan descending verilerin azalan sırayla döndürülmesi dışında index range scan ile aynıdır. Indexler, varsayılan olarak, artan düzende saklanır. Genellikle, bu tarama ilk önce en son veriyi geri almak için azalan bir sırayla veri sipariş ederken veya belirli bir değerden daha düşük bir değer ararken kullanılır.
Index Skip Scans
Index Skip Scan, kompozit(birleşik) bir indexin ilk kolonları olmadığı zaman gerçekleşir. Index içerisindeki ilk kolonlar es geçilir yani SKIP edilir.
Oracle, indexin çeşitli bölgelerinden 0 veya daha fazla row okuyarak, tablo üzerinde bu rowlara erişiyor olabilir.
Kompozit indexin SKIP edilen bölümlerinde ne kadar az distinct değer varsa, Index Skip Scan o kadar başarılı olur.
Örneğin, employees tablosunda (ex, employee_id, address) üzerinde bir kompozit(bileşik) indeks olduğunu (sex, employee_id) düşünün. Bu kompozit indexi bölmek, biri M, diğeri F için olmak üzere iki mantıksal alt indexe neden olur. Bu örnekte, aşağıdaki index verilerinin bulunduğunu varsayalım:
Bu örnekte, aşağıdaki index verilerinin bulunduğunu varsayalım:
(‘F’,98) (‘F’,100) (‘F’,102) (‘F’,104) (‘M’,101) (‘M’,103) (‘M’,105) |
İndex mantıksal olarak aşağıdaki iki alt indexe ayrılır:
- İlk alt indeks, F değerine sahiptir.
- İkinci alt indeks M değerine sahiptir.
Sex sütunu aşağıdaki sorguda atlanır:
SELECT * FROM employees WHERE employee_id = 101;
İndex full scan taraması gerçekleştirilmez, ancak önce F değerine sahip altindex aranır, ardından M değerine sahip alt indexin aranması yapılır.
Full Index Scans
Oracle tüm index segmentini okur ve belirtilen koşula uygun kayıtlar için rowid yardımı ile tabloya erişir. Dahada açacak olursak;
Index ler yalnızca, kendileri üzerinden rowid listesi çıkarıp bu rowidlere karşılık kayıtları bulmak için tablolara gitmezler, indexler doğal olarak tablo üzerindeki bir yada birden çok kolonu key olarak(composite yada composite olmayan indexler) aldıklarından ötürü üzerlerinde key olarak aldığı data yıda barındırmaktadırlar. Eğer gelen sorgunun dönüşü index key’in olduğu kolon ise rowid’yi alıp tabloya gitme gereksinimi kalmadan index keyi okuyup geriye sorgu sonucu olarak döndürebilmektedir. Bu sayede çok önemli bir veri erişim optimizasyonunu oracle kullanıcılarına sağlamaktadır.
Eğer index querry nin ihtiyaç duyduğu tüm datayı üzerinde barındırıyorsa, full table scan, full partition scan yerine full index scan yapılarak sorgu sonucu döndürülür. Full index scan, full table ve full partition scana göre çok daha efektif ve az lojik okuma yapan bir yöntemdir. Bunun nedenide index segmentlerinin tablo segmentlerinden daha ufak olmasıdır.
Örneğin customer tablomuz üzerinde customer_id kolonu üzerinde bir index olsun. customer tablosuna şöyle bir select gönderirsek;
select customer_id from customer where customer_id>500;
Sorgu sonucu disk üzerinde bulunan tabloya ait data bloklarına gitmeden direk döner. Çünkü sorgunun cevabı doğrudan index içinde mevcut, Oracle bu durumda full index scan yaparak tüm datayı indexten okur ve işlemi bitirir.
Başka bir örnek verecek olursak mesela bir uygulamanın aşağıdaki sorguyu çalıştırdığını düşünelim;
SELECT department_id, last_name, salary FROM employees WHERE salary > 5000 ORDER BY department_id, last_name;
Ayrıca department_id, last_name ve salary bir dizinde kompozit bir index olduğunu varsayalım. Oracle Database full index scan yaparak, sıralı olarak okur (bölüm numarası ve soyadına göre sıralanır) ve salary özniteliğini filtreler. Bu şekilde, veri tabanı, çalışanlar tablosundan daha küçük bir veri taraması yapar, bu da sorguya dahil edilenden daha fazla sütun içerir ve verilerin sıralanmasından kaçınır.
Full index scan’de sort işlemi gerekmeyebilir, çünkü veriler zaten index key’e göre sort edilmiştir.
Aşağıdaki durumlarda full index scan gerçekleşir:
- ORDER BY ifadelerinde, order by içerisinde yer alan tüm alanlar index’in tanımı içerisinde varsa ve ORDER BY içerisindeki kolon sırası ile index içerisindeki pozisyon sırası uyuşuyorsa.
- SORT MERGE JOIN yapılıyor ise; tüm sorgulanan kolonlar index içerisinde yer alıyorsa, sorgudaki kolon yazım sırası index içerisindeki pozisyon sırası ile uyuşuyorsa.
- GROUP BY varsa ve group by’da kullanılan alanlar index içerisinde tanımlı ise.
Fast Full Index Scans
Fast full index scan, Full Scan’e alternatif olarak, tüm veri index içerisinde yer alıyorsa gerçekleşir. Indexi oluşturan kolonlardan en az bir tanesinde not null constaint olması şartı vardır.
Fast full index scan, tabloya hiç gitmeden sadece indexin içerisindeki veriye erişir. Sıralama (sort) işlemi elimine edilemez çünkü veri index anahtarına göre sıralı değildir. Tüm index multiblock okuma yaparak (full index scan de yapılamaz) okunur ve paralelleştirilebilir.
OPTIMIZER_FEATURES_ENABLE parametresi ile veya INDEX_FFS hinti ile FFIS işlemi yapılmak istendiği belirtilebilir. FFIS bitmap indexler üzerinde yapılamaz. FFIS, normal full index scan işleminden daha hızlıdır.
NOT : Eğer index buffer cache de yoksa, index disk üzerinden getirilmeye başlanır. İndexin büyük olduğu durumlarda okunacak blok sayısı artacağından dolayı daha fazla blok okuması yapılacak. Index full scan yöntemi blokları tek tek okuduğundan toplam blok sayısı, toplam okuma sayısına eşit olucak ve yöntem yetersiz hale gelicek. Bu tarz durumlarda multiblock read tekniğini kulannabilen index_fast_full scan yöntemini gerekli hinti verek kullanmak okuma sayısını düşürüp etkin bir yönteme sistemizmizi tekrar çevirebilir.
Index Joins
Index Join, sorguda başvurulan tüm tablo sütunlarını içeren birkaç indexin karma birleşimidir. Bir indeks birleşimi kullanılıyorsa, tüm ilgili sütun değerleri indekslerden alınabildiği için tablo erişimi gerekmez.
INDEX_JOIN hintiyle bir kullanabiliriz.
Bitmap Indexes
Bitmap joib anahtar değerleri için bir bitmap ve her bit konumunu bir rowıd dönüştüren bir eşleme işlevi kullanır. Bitmapler, AND ve OR koşullarını çözmek için Boolean işlemlerini kullanarak WHERE yan tümcesinde çeşitli koşullara karşılık gelen dizinleri verimli bir şekilde birleştirebilir.
Index-organized tables
Index Organized Table normal bir tablonun aksine, kendi yapısına sahip bir tablodur. Row id’si yoktur.
Tablosuz bir index gibi düşünülebilinir. Bu tip tablolar B-tree indeks yapısına benzer bir yapıda saklanan tablolardır. Normal tablolarda (Heap-Organized Table) veriler disk üzerinde hangi bloğa sığıyorsa oraya saklanır, index-organized tablolarda ise veriler, PK için tanımlanmış olan index yapısı içerisinde saklanır. Tablonun kendisi bir index’tir. Bu tip tablolara PK ile erişim çok daha hızlıdır.
Indeks-Organize tablolar verilerin bir arada tutulması gerektiği hallerde ve fiziksel olarak belli bir sırada tutulması gerektiği hallerde kullanışlıdır ve genelde bilgi çekme, spatial ve OLAP işlemlerinde çok kullanılır.
CREATE TABLE TEST ( ID NUMBER PRIMARY KEY, NAME VARCHAR2(20) , SURNAME VARCHAR2(20) ) ORGANIZATION INDEX ;
Reverse key indexes
Bu index tipinde, indeksin byte’ları tersinde çevrilerek saklanır, örneğin 103 değeri 301 olarak saklanır. Bunun amacı indekse yapılan insert işlemlerinin birden çok bloğa yayılmasını sağlamaktır.
Bu tip indexin asıl kullanım amacı, B-tree index’in sağ tarafınaki leaf blocklarda oluşabilecek bir (sıkışma) contention’ı engellemektir. Bu problem özellikle RAC sistemlerinde, birden fazla instance aynı bloğu update etmeye çalışınca ortaya çıkar. Örneğin, ORDERS isimli bir sipariş tablomuz olsun ve tablo üzerinde ORDER_ID isimli bir PK alanı tanımlı olsun. ORDER_ID alanı genellikle bir sequence tarafından beslenen ve monoton artan bir numaradır. Bir instance 20 numaralı siparişi yazarken, diğer bir instance da 21 numaralı siparişi aynı leaf bloğuna yazmaya kalkar ve contention oluşur.
Not: Contention engellemek için yapılan bu yöntem index verisinin disk üzerine dağıtılmasına, sıralı olmamasına ve dolayısı ile INDEX RANGE SCAN işlemine engel olabilir. Index contention’ın engellemek için başka yöntemler de vardır.
CREATE INDEX t1_id_idx ON t1 (id) REVERSE;
Descending indexes
Default olarak ascending olarak tanımlanan index yapısı DESC keyword’ü ile ters bir dizilimle DESCENDING olarak saklanabilir. Örneğin bir uygulama sürekli olarak soyadına göre artan, departman id’sine göre ise azalan sorgulama yapıyor olsun. O zaman last_name ve department_id alanları üzerinde şu şekilde bir index tanımlamak performans artışına sebep olur, index sort işleminden bizi kurtarır.
CREATE INDEX emp_name_dpt_ix ON hr.employees(last_name ASC, department_id DESC);
B-tree cluster indexes
Bu index tipi, cluster yapıdaki tabloların cluster key’lerini indexlemek için kullanılır. Index, bir row’u point etmek yerine cluster key ile ilişkili olan row’ları içeren bloğu point eder.
Örneğin department_id cluster key’ine sahip employees_departments_cluster isminde bir cluster oluşturalım ve bir cluster index oluşturalım.
CREATE CLUSTER employees_departments_cluster (department_id NUMBER(4)) SIZE 512; CREATE INDEX idx_emp_dept_cluster ON CLUSTER employees_departments_cluster;
Daha sonra, bu cluster içerisinde employees ve departments isminde iki adet tablo tanımlayalım.
CREATE TABLE employees ( ... ); CLUSTER employees_departments_cluster (department_id); CREATE TABLE departments ( ... ); CLUSTER employees_departments_cluster (department_id);
Son olarak, bu tablolara veri eklediğimiz zaman, Oracle aynı department_id'li employee tablosu ve department tablosu row'larını aynı bloklarda, heap yapısında saklar ve tanımlanan index ile de işaret eder.
Bitmap indexes
Bu index türü B-Tree den sonra en çok kullanılan index türüdür. Bu index türü belli başlı değerlerin sürekli olarak tekrar etmesi durumunda kullanılır. Mesela Cinsiyet Kolonu için sadece 2 değer vardır Erkek ve Bayan olmak üzere bu değer her kullanıcı için sürekli olarak tekrar eder yada Evet Hayır cevabı gibi sınırlı sayıda değer girilebilecek kolonlar bunlara bir örnektir. Bitmap dediğimiz şey bit haritasıdır ve 0 – 1 lerden oluşmaktadır.
Bitmap indeksleme çok fazla sayıda satıra sahip tablolardaki düşük çeşitlilik (cardinality) sahibi sütunlar üzerinde yapılmalıdır. Unutmamanız gereken bir başka konu ise bitmap indeksleme sizin için gerçek bir şeytana dönüşebilir! Bitmap indekslemenin olduğu tablo çok fazla güncelleniyor ve sürekli DML operasyonuna maruz kalıyorsa eğer bir daha düşünün çünkü bir bitmap indeksi güncellemenin maliyeti oldukça yüksektir ve kimi zaman yığın bir yüklemeden önce kullanılamaz hale getirilir ve yükleme bittikten sonra yeniden oluşturulurlar. Bunun sebebi ise bitmap indekslerin sahip olduğu bit’lerin sıkıştırılmış bir formatta bulundurulması. Yani bitmap indeks aslında bir sıkıştırılmış indekstir ve doğal olarak yerden de tasarruf edebilirsiniz. Bitmap indeksler yapısı gereği rowid içermek yerine bir bit tutarlar. Az sonraki örnekte bitmap indeksin sahip olduğu bit’leri nasıl rowid’ye değiştirdiğini göstereceğim. Bu durumun akabinde, değiştirilen rowid’ler de bir anahtar değere sahip olacaktırlar (verinin kendisi).
Bitmap indekslerin diğer indekslerden farklı olduğu bir başka durum ise NULL değer yani değersiz değeri indeksleyebilmelidir. Bu durum bir takım sorguların müthiş hızlı çalışmasını sağlayabilir (örneğin tek ve yegane null değeri kabul ederek çalışan count(*) gibi). Ayrıca bitmap indekslerin kaplayacağı disk boyutu da standart bir b-tree indeksten daha az olacaktır!
Aşağıdaki örneğimizde bir bitmap indeksin işleri nasıl kolaylaştırdığını göstereceğim;
SQL> create table test as select * from all_objects; Table created. SQL> set timing on SQL> SET AUTOTRACE ON SQL> select count(*) from test; COUNT(*) ---------- 94645 Elapsed: 00:00:00.21 Execution Plan ---------------------------------------------------------- Plan hash value: 1950795681 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 370 (1)| 00:00:05 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS STORAGE FULL| TEST | 97532 | 370 (1)| 00:00:05 | --------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 6 recursive calls 0 db block gets 1434 consistent gets 1357 physical reads 0 redo size 528 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Gördüğünüz gibi basit bir okumanın maliyeti bize ciddi bir yük getirdi. Bütün tablo tarandı ve 1357 veri bloğundan fiziksel olarak bilgi istendi. Çok kötü! Şimdi bitmap index oluştutup deneyelim;
create bitmap index test_bitmap on test (object_type); SQL> SET AUTOTRACE ON SQL> set timing on SQL> select count(*) from test; COUNT(*) ---------- 94645 Elapsed: 00:00:00.05 Execution Plan ---------------------------------------------------------- Plan hash value: 3143598967 -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | BITMAP CONVERSION COUNT | | 97532 | 6 (0)| 00:00:01 | | 3 | BITMAP INDEX STORAGE FAST FULL SCAN| TEST_BITMAP | | | | -------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 32 recursive calls 0 db block gets 117 consistent gets 322 physical reads 0 redo size 528 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1 rows processed
Bitmap indexin veriyi nasıl sakladığıyla ilgili örnek yaparsak çok daha iyi analaşılacağını düşünüyorum.
SQL> SELECT cust_id, cust_last_name, cust_marital_status, cust_gender 2 FROM sh.customers 3 WHERE ROWNUM < 8 ORDER BY cust_id; CUST_ID CUST_LAST_ CUST_MAR C ---------- ---------- -------- - 1 Kessel M 2 Koch F 3 Emmerson M 4 Hardy M 5 Gowen M 6 Charles single F 7 Ingram single F 7 rows selected.
cust_gender sütun çıktısı için bitmap index aşağıdaki gibi olur. Her cinsiyet için bir tane olmak üzere iki ayrı bitmapten oluşur.
Value | Row 1 | Row 2 | Row 3 | Row 4 | Row 5 | Row 6 | Row 7 |
M | 1 | 0 | 1 | 1 | 1 | 0 | 0 |
F | 0 | 1 | 0 | 0 | 0 | 1 | 1 |
Bitmap Join Indexes
Bitmap Join Index, iki yada daha fazla tablonun join’i için tanımlanan bir bitmap indexidir. A ve B tablosunun ID kolonuları üzerinden join edileceğini ve bitmap join indexini A tablosu üzerine B’nin belli bir X kolonu için tanımlayacağımızı düşünelim. Bu durumda, B tablosundaki X kolonundaki her bir değer bir bit’e karşılık gelir. Index ise A tablosu üzerinde tanımlanır ve A tablosundaki her row için bit değerlerinden oluşan bir bitmap oluşur. Bu bitmap, A tablosundaki ilgili row’un B tablosundaki değerleri içerip içermediğini gösterir. 1: içeriyor 0: içermiyor.
Devamlı olarak aşağıdaki gibi sorgulanan bir tablomuz olsun:
SELECT COUNT(*) FROM employees, jobs WHERE employees.job_id = jobs.job_id AND jobs.job_title = 'Accountant';
Bu tablolar üzerinde aşağıdaki gibi bir bitmap join index tanımlayabiliriz:
CREATE BITMAP INDEX employees_bm_idx ON employees (jobs.job_title) FROM employees, jobs WHERE employees.job_id = jobs.job_id;
Function-based indexes
Bu index tipine daha önce değinmiştik; Index tanımlı alanın, WHERE koşulunda bir fonksiyon içerisinde kullanılması kaçınılmaz ise bu alanı fonksiyonu ile beraber indexlememiz gerekir; aksi halde index kullanılmaz. Bu konuda en çok karşılaşılan fonksiyonlar INSTR, SUBSTR, LOWER, UPPER gibi fonksiyonlardır. Bitmap indexler de funciton-based olarak tanımlanabilirler.
select * from hr.employees where lower(first_name)=’test’;
Create index MAD_IX on hr.employees(lower(first_name)); |
Application domain indexes
Domain index, bir uygulamaya özel özelleştirilmiş bir indexdir. Oracle Database, aşağıdakileri yapmak için extensible indexing sağlar: Ayrı bir yazıda detaylı olarak analatacağım
- Belgeler, mekansal veriler, görüntüler ve video klipler gibi özelleştirilmiş, karmaşık veri türleri için dizinler yerleştirin
- Özel indeksleme teknikleri kullanılacağı zaman
Database’ de yer alan indexleri sorgulamak için aşağıdaki sorgulardan faydalanabiliriz ;
select table_name, index_name
from dba_indexes;
select table_name, index_name, column_name, column_position
from dba_ind_columns
order by table_name, index_name, column_position;
İndex Kullanımını Etkileyen Yanlışlıklar
Sorgu yazımındaki kimi hatalardan dolayı indexli kullanılması gerekilen durumlarda index’ in kullanılmadığına şahit olabilirler. Bu tarz durumların neler olabileceğine sırayla bakalım ;
- Eşit Değildir Operatörünün Kullanımı (‘<>’ ve ‘!=’) : İndexler sadece tablo içerisindeki datayı bulmakta kullanılırlar. Dolayısıyla size sorgularınızda tabloda olmayan bir kaydı “eşit değildir” operatörü ile sorgulamak isterseniz oracle index kullanımını iptal edecektir. EMP scheması altındaki employees tablosunda emploee_id kolonunda index bulunmaktadır. Eşit operatörü ile aşağıdaki sorguyu kontrol ettiğimizde index kullandığını görürüz; Eşit değildir operatörü ile sorgunun execution planına batığımızda sorgunun index kullanmadan full table scan yaptığını görüyoruz.
set lines 300 set autotrace on SELECT * FROM hr.EMPLOYEES WHERE employee_id <> 5; Execution Plan ---------------------------------------------------------- Plan hash value: 1445457117 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS STORAGE FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - storage("EMPLOYEE_ID"<>5) filter("EMPLOYEE_ID"<>5) Statistics ---------------------------------------------------------- 8 recursive calls 0 db block gets 16 consistent gets 6 physical reads 0 redo size 10139 bytes sent via SQL*Net to client 597 bytes received via SQL*Net from client 9 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 107 rows processed
- Is Null veya Is Not Null Operatörünün Kullanılması : Sorgunun Where kısmında is null veya is not null kullanırsanız, oracle index kullanımını engelleyecektir.
- Function Kullanımı : Eğer function –based index kullanmıyorsanız, indexli kolonlar üzerinde Where cümleciği içerisinde indexli kolonlar üzerinde function kullanırsanız optimizer index kullanımını engelleyecektir.
- Farklı Data Type’ larının Compare Edilmesi : En zor performans sorunlarından biride, farklı data type’ larının karşılaştırılmasının sonucunu bulmaktır. Oracle farklı data typelarının karşılaştırılmasına kızmamakla birlikte, bu şekilde işlem yapılmasını önermemektedir. Oracle varchar2 data type’ ini sizin özel bir işlem yapmanıza gerek kalmadan number’ a convert etmektedir.
Index Kullanımını Takip Etmek
Yaratılan ama kullanılmayan index’ler sadece alan kaybı yaratmaz. Aynı zamanda DML performansınızı da etkiler. Elbetteki kimse, kullanılmasın diye index oluşturmaz. Fakat zaman içinde gelen sorgular değişebilir; bir zamanlar kullandığınız index’lere artık gerek kalmayabilir. Bu tarz durumlarda karar vermek zor olabiliyor.
Oluşturulmuş indekslerin kullanılıp kullanılmadığı izlenmelidir. Kullanılmayan indeksler drop edilmelidir. Fakat kullanımı açtığınızda her kullanılmayan indekslerin drop edilmesi sorunlara sebep olabilir. Indeks kullanım izlenmesinin açıldığında , ilgili indeksin Cost Based Optimizer tarafından kullanılıp kullanılmadığı bilgisine ulaşabiliriz. Bu bilgi indeksin drop edilmesi gerekliliğine yeterli değildir. Örneğin primary key olan indekslerin kullanımı hep NO gelecektir. Buna istinaden drop edildiğinde ciddi sorunlara sebep olur. Özetle , kullanılmayan indeksler primary key ile ilişkili değilse , herhangi bir sql profili ile ilişkili değilse drop edilebilir.
- Index’i ALTER INDEX … MONITORING USAGE komutuyla takibe alıyoruz.
create table test(id number,name varchar2(20)); create index test_index on test (id); ALTER INDEX test_index MONITORING USAGE;
- Aşağıda yazdığım sorguyu kullanarak, index kullanım durumunu görebiliriz.
NOT : 12C öncesi veritabanları için V$OBJECT_USAGE, 12c için ise DBA_OBJECT_USAGE view’i kullanılmalıdır.
set lines 200 trims on column INDEX_NAME format a40 column TABLE_NAME format a40 column MONITORING format a10 column USED format a10 column START_MONITORING format a20 column END_MONITORING format a20 SELECT * FROM V$OBJECT_USAGE;
- Index kullanacak bır sorgu çalıştırıyoruz gerekiyoruz.
set lines 300 set autotrace on select count(*) from test where id = 1; Execution Plan ---------------------------------------------------------- Plan hash value: 3793203774 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX RANGE SCAN| TEST_INDEX | 1 | 13 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 53 recursive calls 3 db block gets 100 consistent gets 0 physical reads 664 redo size 525 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed
- Sorgu calıştıktan sonra, artık used sütununu yes
- İstersek index monitor’leme işlemini aşağıdaki gibi kapatabiliriz.
alter index test_index_idx01 nomonitoring usage;
- Tekrar aktivasyon yapıldığında, kullanım durumu ‘NO’ oluyor. Index’i kullanacak bir sorgu gelene kadar da, USED sütunu aynı şekilde kalıyor. Peki… monitoring’i kapatıp açmadan, sayacımızı sıfırlamak istersek, ne yapabiliriz? Her seferinde NOMONITORING ardından MONITORING yazmak zahmetli olurdu. Bunun yerine, tekrar MONITORING dememiz yeterli oluyor. Bu arada Veritabanı kapatılıp, açılırsa da, bir kayıp olmuyor.
- Diyelim ki, bir süre gözlemledik ve index’in kullanılmadığına karar verdik. Sonraki aşamada ne yapabiliriz? Direkt index’i drop etmek iyi bir seçenek olmayabilir. ALTER INDEX … INVISIBLE komutunu kullanıp, erişime kapatabiliriz.
ALTER INDEX test_index INVISIBLE;
- Index’i gizli hâle aldık ama henüz drop etmedik. Günün birinde bir kullanıcı çıkıp, performans sorunu olduğunu söyledi. Sorunun kapatılan index’ten olduğunu düşünmüyorsunuz; fakat index’i kullanıp, test etmek lâzım. Bu durumda, genele açmadan, sadece kendi oturumunuzdan index’i görülebilir yapabilirsiniz.
alter session set optimizer_use_invisible_indexes=TRUE;
- Büyük bir tablonuz olduğunu düşünelim. Bu büyük tablo üzerinde bir index yaratmayı düşünüyorsunuz ama gelecek sorguların bunu kullanıp-kullanmayacağından emin değilsiniz. Bu index’i oluşturmadan, optimizer’ın davranışını nasıl kestirebiliriz? Bunun da güzel bir yolu var: Sanal Index yaratmak.
CREATE INDEX test_index_virtual ON test ( id ) NOSEGMENT; alter session set "_use_nosegment_indexes"=true;
- Indexler eğer 1 kez bile kullanılıyorsa index monitoring yaptığımızda kullanılıyor olarak gösterir bize ancak biz indexlerin tarih bazında kullanım sayısını öğrenmek istediğimizde aşağıdaki gibi bir yolu izleyebiliriz.
- Index kullanım sayılarını tarih bazında sorgulamak üzere iki geçici tablo oluşturuyorum:
CREATE TABLE idx_TABLE1 ( OWNER VARCHAR2 (30 BYTE), INDEX_NAME VARCHAR2 (30 BYTE), FIRST_LOAD_TIME VARCHAR2 (20 BYTE), LAST_LOAD_TIME VARCHAR2 (10 BYTE), LAST_ACTIVE_TIME VARCHAR2 (10 BYTE), TOTEXEC NUMBER, RUN_DATE DATE DEFAULT SYSDATE ); CREATE TABLE idx_TABLE2 ( OWNER VARCHAR2 (30 BYTE), INDEX_NAME VARCHAR2 (30 BYTE), FIRST_LOAD_TIME VARCHAR2 (20 BYTE), LAST_LOAD_TIME VARCHAR2 (10 BYTE), LAST_ACTIVE_TIME VARCHAR2 (10 BYTE), TOTEXEC NUMBER, RUN_DATE DATE DEFAULT SYSDATE );
- Oracle support un Doc ID 1033478.6 sindeki do_explain procedure ünü create ediyorum:
CREATE OR REPLACE PROCEDURE do_explain (addr IN VARCHAR2, sqltext IN VARCHAR2) AS dummy VARCHAR2 (1100); mycursor INTEGER; ret INTEGER; my_sqlerrm VARCHAR2 (85); BEGIN dummy := 'EXPLAIN PLAN SET STATEMENT_ID='; dummy := dummy || '''' || addr || '''' || ' FOR ' || sqltext; mycursor := DBMS_SQL.open_cursor; DBMS_SQL.parse (mycursor, dummy, DBMS_SQL.v7); ret := DBMS_SQL.execute (mycursor); DBMS_SQL.close_cursor (mycursor); COMMIT; EXCEPTION -- Insert errors into PLAN_TABLE... WHEN OTHERS THEN my_sqlerrm := SUBSTR (SQLERRM, 1, 80); INSERT INTO plan_table (statement_id, remarks) VALUES (addr, my_sqlerrm); -- close cursor if exception raised on EXPLAIN PLAN DBMS_SQL.close_cursor (mycursor); END; /
- Oracle support un Doc ID 1033478.6 sindeki scrpitleri customize ederek tarih bilgilerini çekiyorum ve toplanan bilgileri geçici tablolarıma aktarıyorum:
CREATE OR REPLACE PROCEDURE idx_big_tables_prc AS -- declare CURSOR c1 IS SELECT address, sql_text, DISK_READS, EXECUTIONS, PARSE_CALLS, FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME FROM gv$sqlarea WHERE command_type IN (2, 3, 6, 7) AND parsing_schema_id != 0; CURSOR c2 IS SELECT addr, sql_text FROM sqltemp; addr2 VARCHAR (16); sqltext v$sqlarea.sql_text%TYPE; dreads v$sqlarea.disk_reads%TYPE; execs v$sqlarea.executions%TYPE; pcalls v$sqlarea.parse_calls%TYPE; f_load_time v$sqlarea.FIRST_LOAD_TIME%TYPE; l_load_time v$sqlarea.LAST_LOAD_TIME%TYPE; l_active_time v$sqlarea.LAST_ACTIVE_TIME%TYPE; BEGIN EXECUTE IMMEDIATE 'drop table sqltemp'; EXECUTE IMMEDIATE 'create table sqltemp (ADDR VARCHAR2 (16), SQL_TEXT VARCHAR2 (2000), DISK_READS NUMBER, EXECUTIONS NUMBER, PARSE_CALLS NUMBER, FIRST_LOAD_TIME VARCHAR2(20), LAST_LOAD_TIME DATE, LAST_ACTIVE_TIME DATE)'; OPEN c1; FETCH c1 INTO addr2, sqltext, dreads, execs, pcalls, f_load_time, l_load_time, l_active_time; WHILE (c1%FOUND) LOOP INSERT INTO sqltemp VALUES (addr2, sqltext, dreads, execs, pcalls, f_load_time, l_load_time, l_active_time); COMMIT; FETCH c1 INTO addr2, sqltext, dreads, execs, pcalls, f_load_time, l_load_time, l_active_time; END LOOP; CLOSE c1; OPEN c2; FETCH c2 INTO addr2, sqltext; WHILE (c2%FOUND) LOOP do_explain (addr2, sqltext); FETCH c2 INTO addr2, sqltext; END LOOP; CLOSE c2; INSERT INTO idx_TABLE1 (OWNER, INDEX_NAME, FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, TOTEXEC) SELECT owner, name index_name, FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, SUM (totexec) totexec FROM ( SELECT p.owner, p.name, FIRST_LOAD_TIME, TO_CHAR (s.LAST_LOAD_TIME, 'dd.mm.yyyy') LAST_LOAD_TIME, TO_CHAR (s.LAST_ACTIVE_TIME, 'dd.mm.yyyy') LAST_ACTIVE_TIME, SUM (s.executions) totexec FROM sqltemp s, (SELECT DISTINCT statement_id stid, object_owner owner, object_name name FROM plan_table WHERE operation = 'INDEX') p WHERE s.addr = p.stid AND p.name IN (SELECT DISTINCT index_name FROM dba_indexes WHERE table_name = 'TABLE1') GROUP BY p.owner, p.name, s.FIRST_LOAD_TIME, s.LAST_LOAD_TIME, s.LAST_ACTIVE_TIME ORDER BY 2 DESC) GROUP BY owner, name, FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME; INSERT INTO idx_TABLE2 (OWNER, INDEX_NAME, FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, TOTEXEC) SELECT owner, name index_name, FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, SUM (totexec) totexec FROM ( SELECT p.owner, p.name, FIRST_LOAD_TIME, TO_CHAR (s.LAST_LOAD_TIME, 'dd.mm.yyyy') LAST_LOAD_TIME, TO_CHAR (s.LAST_ACTIVE_TIME, 'dd.mm.yyyy') LAST_ACTIVE_TIME, SUM (s.executions) totexec FROM sqltemp s, (SELECT DISTINCT statement_id stid, object_owner owner, object_name name FROM plan_table WHERE operation = 'INDEX') p WHERE s.addr = p.stid AND p.name IN (SELECT DISTINCT index_name FROM dba_indexes WHERE table_name = 'TABLE2') GROUP BY p.owner, p.name, s.FIRST_LOAD_TIME, s.LAST_LOAD_TIME, s.LAST_ACTIVE_TIME ORDER BY 2 DESC) GROUP BY owner, name, FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME; COMMIT; DELETE FROM plan_table WHERE statement_id IN (SELECT addr FROM sqltemp); COMMIT; END; /
- Oluşturduğum idx_big_tables_prc procedure u sch. job ile 6 saatte bir çalıştırarak geçici tablolarımı besliyorum:
BEGIN SYS.DBMS_SCHEDULER. CREATE_JOB ( job_name => 'SYS.IDX_BIG_TABLES', start_date => TO_TIMESTAMP_TZ ('2015/01/01 10:00:00.000000 +03:00','yyyy/mm/dd hh24:mi:ss.ff tzr'), repeat_interval => 'FREQ=HOURLY; INTERVAL=6; BYMINUTE=00; BYSECOND=0', end_date => NULL, job_class => 'DEFAULT_JOB_CLASS', job_type => 'STORED_PROCEDURE', job_action => 'SYS.IDX_BIG_TABLES_PRC', comments => NULL); END;
- Belli bir süre sonra aşağıdaki sorgularımla index kullanım sayısı düşük indexleri belirliyorum, önce invisible a çekip bir müddet izledikten sonra indexleri drop ederek hem ASM de yer kazanıyorum hem de günlük backupların IO sunu düşürmüş oluyorum:
select index_name, run_date, sum(totexec) from idx_TABLE1 group by index_name, run_date order by 2; select index_name, run_date, sum(totexec) from idx_TABLE1 where index_name ='IX_TEST_ID' group by index_name, run_date order by 2; select index_name, run_date, sum(totexec) from idx_TABLE1 having sum(totexec)< 50 group by index_name, run_date order by 2;
Indeksler ve Foreign key
http://www.mustafabektastepe.com/2016/09/21/indexi-olmayan-foreign-key-leri-cikartan-sql-scripti/
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.
alter index test_index 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.
alter index test_index 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.
alter index test_index rebuild online parallel 8;
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 rebuild 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.
select * from index_stats;
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.
ANALYZE INDEX test_index 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.
Yukardaki resimde index_stats tablosunun sonucu bulunmaktadır. 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.
- Index_stats tablosundaki HEIGHT değeri 4 ten büyük olduğu zaman.
- DEL_LF_ROWS / LF_ROWS oranı %20 den büyük olduğunda.
https://docs.oracle.com/cd/E11882_01/server.112/e41573/toc.htm
https://docs.oracle.com/cd/E36909_01/server.1111/e25789/indexiot.htm
https://mehmetsalihdeveci.net/2013/06/22/oracle-veritabaninda-index-kavrami-ve-kullanimi-1/
http://www.kamilturkyilmaz.com/2012/04/08/index-kullanimi-olusturulmasi-cesitleri-faydalari-uzerine-%E2%80%A6/
http://ozcan-yildirim.blogspot.com/2011/07/temel-index-prensipleri.html
http://www.cagataycebi.com/oracle/25_index_monitoring.html
https://docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_in.htm
https://ferhatdemirci.wordpress.com/2010/08/25/oracle-indexler/
https://anargodjaev.wordpress.com/2012/02/28/tablo-yonetimi-btree-index/
https://emrahmete.wordpress.com/2012/12/15/veriye-etkin-erisim-yontemleri-2/
http://www.cagataycebi.com/oracle/25_index_monitoring.html
http://www.kamilturkyilmaz.com/2012/04/08/index-kullanimi-olusturulmasi-cesitleri-faydalari-uzerine-%E2%80%A6/
https://www.upwork.com/hiring/data/common-sql-programming-mistakes/
https://orhaneripek.com/2017/02/14/oracle-index-usage-count/
Mustafa Bektaş Tepe
İyi Çalışmalar