27 Kasım 2008 Perşembe

Data Concurrency and Consistency - Veri Uyumluluğu ve Tutarlılığı

Merhaba,



Oracle veritabanı bir ilişkisel veritabanı olduğundan tablolar arasındaki verilerin ilişkisi üzerine kurulmuştur. Bu ilişki bildiğimiz gibi primary ve foreign key başta olmak üzere index, synonym, pl/sql gibi destek üniteleri ile desteklenir. İlişkisel veritabanları birden çok kullanıcının aynı anda bağlı olacağı şekilde tasarlanmıştır. Bu da, yine birden çok kullanıcının aynı anda veritabanı içinde verilerin tutulduğu tablolara erişebileceği anlamına gelmektedir. Bugün anlatacağım konuda, birden çok kullanıcının aynı tablo üzerinde aynı anda aynı kolonu aynı değerlerle değiştirmesine kadar açıklamaya çalışacağım.

Veri uyumluluğu ve tutarlılığı birden çok kullanıcısı olmayan, yani sadece tekil kullanıcının bağlandığı -çoğu zaman test veya kişisel, bireysel sunucu diyebiliriz- durumlarda geçerliliğini yitirir. Çünkü tek kullanıcı istediği tabloyu değiştirebildiği için ve o anda bir başkasının da o tablodaki değişikliklerin üzerine yazamayacağı için veri tutarlılığından bahsedilemez. O veri, zaten tutarlı ve uyumlu olacaktır. Elektriklerin gitmesi, long operation dediğimiz işlemlerler sürerken veya ciddi bir pl/sql sorgusu çalıştırılırken ya da shutdown abort yazılmışken, veri uyumluluğu ve tutarlılığını sağlayabilen background processlerimiz de vardır. Bu tarz bir durumda bile Oracle, verilerin sağlığı için herşeyi yapmaktadır. SMON(System monitor) ve PMON(Process monitor) diye adlandırılan ve arkaplanda devamlı çalışan işlemler, Oracle'ın düzgün kapatılamadığı durumlarda devreye girer ve gerektiğinde "rollback" gerektiğinde "rollforward" yaparlar.


Tam olarak "Data Concurrency, Data Consistency" ne demek, şu şekilde açıklayabiliriz:

DATA CONCURRENCY: Birden çok kullanıcının aynı anda verilere erişmesine denir.

DATA CONSISTENCY: Her kullanıcı verilerin tutarlı halini görürler. Buna kullanıcının kendi yaptığı değişiklikler ve diğer kullanıcıların yaptığı değişiklikler de dahildir.

Oracle veritabanında, bir kullanıcı bağlı olduğu schemada, bir tablodaki veriye eriştiği zaman ve o veriyi "update" ederken, diğer hiçbir kullanıcı o veriyi update veya delete edemez. Çünkü ilk kullanıcı tablonun update etmeye çalıştığı kolonu üzerine kilit almıştır. Onun işlemi bittikten sonrada yani kolonu update ettikten sonrada kilit devam eder. Bu esnada buffer cache'de veri, dirty buffer olarak bekler. İlk kullanıcı henüz commit etmediği için aynı sessiondaki bütün kullanıcılar o verinin eski halini, yani update edilmemiş halini görürler. Update edilmiş halini tek gören ilk kullanıcımızdır. Bunun Oracle'da bu şekilde olmasının sebebi ise, kullanıcı yaptığı değişiklik veya değişikliklerden vazgeçebilir, rollback yapabilir. Ne zaman ki ilk kullanıcı commit eder, o zaman bütün kilitleri kaldırır ve diğer kullanıcılar da update edilmiş hallerini görmeye başlarlar. Yalnız, bu demek değildir ki, buffer cache'deki dirty buffer'lar commit ile yazılırlar. HAYIR, update, delete veya insert yani bir DML komutundan sonra gelen commit ile dirty buffer'ları dbf'lere (database file) yazmıyoruz. Bunun sebebi ise, redo logların dbf'lere yazılma işleminin son derece zor olduğudur. Veritabanının performansını çökermemek için verilerin dbf'lere yazıldığı belirli araklıklar vardır.

1) Buffer cache'deki dirty buffer miktarı 1/3'e ulaşırsa,
2) alter system switch logfile; yazılırsa,
3) Redo log'ların boyutu dolar ve diğer gruba geçerse,


VERILER REDO LOG'LARDAN DATABASE FILE'LARA YAZILIRLAR...


ANSI'nin belirlediği SQL standartlarına göre 3 çeşit "Preventable Phenomena" vardır. Yani 3 çeşit Önlenebilir fenomen vardır. Peki ne demek bu? Hemen açıklayalım.

Dirty Read: Bir başkası tarafından (bir başkasının transaction'ı tarafından) henüz commit edilmemiş verinin, diğer kullanıcının transaction'ı tarafından okunmasına denir.

Fuzzy Read: Bir transaction eğer daha önceden okuduğu veriyi tekrar okuduğunda, bir başka transaction'ın, ilk okuduğu veriyi commit etmesiyle, okuyacağı veri değişir ve buna da fuzzy read denir.

Phantom Read: Belirli bir arama koşulunu sağlayan ve belirli bir satır döndüren bir transaction yeniden aynı sorguyu sorguladığı zaman, bir diğer transaction'ın, aynı arama koşullarını sağlayan veriyi commit etmesiyle ortaya çıkan ortak sonuç kümesinden okuma işlemine denir.


Commit edilmemiş veri; dirty, fuzzy ve phantom olarak okunabilir, Commit edilmiş veri; fuzzy ve phantom olarak okunabilir, Tekrar tekrar okunan veri; sadece phantom olarak okunabilir. Bu şekilde sınırlanan okuma durumlarına ve oluşan katmana, Isolation Level (İzolasyon seviyesi) denir.

Oracle'ın garanti ettiği 2 tane uyumluluk kontrolü vardır. Bunlar, Statement level read consistency ve Transaction level read consistency dir. Bunlar, Oracle'ın her zaman bir sorgunun veri ile döneceğini ve her sorgunun veriye ulaşacağını garanti eden koşullardır.

Az önce bahsettiğim row locking yani satır bazında kilitleme duruma geri dönersek, satır kilitleme işlemi; bir kullanıcı o satırdaki veriyi update delete veya insert ederken satır bazında kilitleme gerçekleşir ve kilidi bulunduğu transaction ve kendisinin üzerine alır. Üzerineki kilit, commit veya rollback diyene kadar ya da ikisini de demeden veritabanından çıkana kadar (rollback yapar fakat autocommit on ise commit yaparak çıkar) geçerlidir. Bu kilit olduğu sürece aynı satıra erişmeye çalışan ve update etmeye kalkışan bütün transactionlar askıda kalırlar.

Bunu bir örnek ile göstermem gerekise;

İlk session'ımızı açtık ve ogan kullanıcısı ile bağlandık ...


C:\> sqlplus
ogan/deneme@orcl

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Nov 27 19:13:04 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> set timing on

SQL> drop table ogan_deneme purge;

Table dropped.

Elapsed: 00:00:04.38
SQL> create table ogan_deneme
2 (
3 kullanici_adi varchar2(40),
4 kullanici_soyadi varchar2(50)
5 );

Table created.

Elapsed: 00:00:00.09
SQL> insert into ogan_deneme

2 values ('ogan','ozdogan');

1 row created.

Elapsed: 00:00:00.01
SQL> / 1 row created.

Elapsed: 00:00:00.00
SQL> / 1 row created.

Elapsed: 00:00:00.00
SQL> select * from ogan_deneme;

KULLANICI_ADI KULLANICI_SOYADI

----------------------------------------

ogan ozdogan
ogan ozdogan
ogan ozdogan

Elapsed: 00:00:00.04

ogan_deneme tablomuza 3 tane duplicate veri aktardık ve şimdi bunları update ediyoruz ve row lock'ı üzerimize alıyoruz ...

SQL> update ogan_deneme
2 set kullanici_adi = 'ahmet';

3 rows updated.


Elapsed: 00:00:00.01
SQL> select * from ogan_deneme;

KULLANICI_ADI KULLANICI_SOYADI
-------------------------------------------

ahmet ozdogan
ahmet ozdogan
ahmet ozdogan

Elapsed: 00:00:00.03

Kullanici_adi satırı üzerinde henüz bir DDL çalıştırmadığımız veya commit ya da rollback yazmadığımız için row lock duruyor. Şimdi bunu aynı user ile bağlanan fakat farklı transaction'dan update etmeye çalışan kullanıcı ile nasıl olacağına bakalım ...

C:\> sqlplus
ogan/deneme@orcl

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Nov 27 19:23:19 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> select * from ogan_deneme;

no rows selected

Peki şimdi ne oldu? Hazır yeri gelmişken, veriler, diğer transaction tarafından commit edilmediği için görülemiyor ... Diğer transaction'a geçelim ve commit edelim ...

SQL> commit;

İlk ve ikinci transactionların ogan_deneme tablosunu nasıl gördüklerini görelim ...

SQL> select * from ogan_deneme;

KULLANICI_ADI KULLANICI_SOYADI

--------------------------

ahmet ozdogan
ahmet ozdogan
ahmet ozdogan

Elapsed: 00:00:00.04

Şimdi en kritik noktaya geldik. İlk transaction kullanici_adi'nı update etmeye çalışacak ve commit etmeyecek. Bu sırada ikinci transaction da aynı satırı update etmeye çalışacak ...

İlk oturum


SQL> update ogan_deneme
2 set kullanici_adi=
3 'ogan'
4 where kullanici_adi='ahmet';

3 rows updated.

Elapsed: 00:00:00.01

İkinci oturum

SQL> update ogan_deneme
2 set kullanici_adi='mehmet'
3 where kullanici_adi='ahmet';
...
...
...
...

* * * İkinci oturum aynı satırı update etmeye çalışırken askıda kaldı. Çünkü o satırın şu anki sahibi, kilidi koyan transaction ilk transactiondır. Kilitlenen transaction ve kilitleyen transaction'a bakalım ...

C:\> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Nov 27 19:44:10 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options


SQL> select * from dba_waiters;

WAITING_SESSION HOLDING_SESSION LOCK_TYPE
--------------- --------------- --------------------------
MODE_HELD

----------------------------------------
MODE_REQUESTED LOCK_ID1 LOCK_ID2
---------------------------------------- ---------- ----------
159 153 Transaction Exclusive Exclusive 589831 1127


159 numaralı session (ikinci) bekliyor, 153 numaralı session (ilk) onu
tutuyor.

SQL> select * from dba_blockers;

HOLDING_SESSION
---------------
153

SQL> select * from v$lock order by SID;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------
331C4324 331C433C 153 TM 54936 0 3 0 611 0
3323CD88 3323CDAC 153 TX 589831 1127 6 0 611 1
33C34398 33C343AC 159 TX 589831 1127 0 6 506 0
331C43E8 331C4400 159 TM 54936 0 3 0 1092 0

TX: Satır tipi kilitlemelere verilen isim TX'tir. Genelde DML omutları kullanılırken satırlara TX kiliti konulur

TM: Tablo tipi kilitlemelere verilen isim TM'dir. Tablolar DDL komutlarına karşı kilitlenirler.

SQL> select * from dba_locks order by session_id;

SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED

---------- -------------------------- ---------------------------------------- ------

153 DML Row-X (SX) None
153 Transaction Exclusive None
159 Transaction None Exclusive
159 DML Row-X (SX) None
164 Temp Segment Row-X (SX) None
165 RS Row-S (SS) None
165 XR Null None
165 Control File Row-S (SS) None
166 Redo Thread Exclusive None
167 Media Recovery Share None
167 Media Recovery Share None
167 Media Recovery Share None
167 Media Recovery Share None
167 PW Row-X (SX) None
167 Media Recovery Share None

15 rows selected.

Satır bazında exclusive kilitleri 153 ve 159 için görüyoruz ...

Bu aşamadayken bahsetmek istediğim bir durum var. "Deadlock" dediğimiz kilit çeşidir vardır. Eğer ilk transaction A tablosunu update ediyorsa, aynı anda diğer bir transaction B'yi update ediyorsa ve ikisi de commit etmediyse, tam bu esnada ilk transaction B'yi, ikinci transaction'da A'yı update etmeye çalışırsa (update edilmeye çalışılan satırların aynı olduğunu düşünelim) o zaman ikisi birden kilitlenir ve deadlock oluşur.


Bir başka deadlock oluşma yolunu aşağıdaki (tahiti.oracle.com'dan alınan bir örnektir) tabloda görebiliriz. Kurtulmanın yolu, dba olarak bağlanıp ikisinden birinin session'ını kill etmemiz gerekir. Kill edilen session, kendisini rollback edeceğinden, diğeri update'ine devam edebilecektir.

DEADLOCK





İkinci transaction kilitli durumda bekliyor. Onu kurtaralım :) İlk session'da commit ya da rollback diyelim. Fakat bunu derken diğer komut satırıda ekranın yanında dursun. İlk transaction commit edildiğinde ikinci transaction'ın nasıl davrandığına ve ilk ve ikinci session'ın ogan_deneme tablosunu nasıl gördüğüne bakın ...

Şimdi de "flashback query" özelliğinden bahsetmek istiyordum ancak ya vista'dan dolayı ya da blogger'dan dolayı explorer çok yavaşlamış durumda :) Bundan sonraki yazı için flashback query diyelim ve oradan devam edelim. Konu ile ilgili daha detaylı yazmak isterdim fakat yazabilmek için ter döküyorum olmuyor :) Okuduğunuz zaman aklınıza takılan birşey olursa lütfen mail atmaktan çekinmeyin.


İyi akşamlar,


Ogan

26 Kasım 2008 Çarşamba

"Index Organized Table" nedir?

Oracle'da birden fazla tablo yaratım seçeneği vardır. "create table" ile yarattığımız zaman "heap organized table" yaratmış oluruz. Heap, yani yığın, küme demektir. Bir diğer seçenek olarak Index Organized Table, Partitioned Table gibi seçenekler de mevcuttur. Bu tamamen o anki ihtiyaca ve kullanılması gereken tablo yapısına göre belirlenir.

Heap organized tablolar database file(.dbf) uzantılı dosyalarda ve extent-->block-->segment yapısı ile tutulur.

Neden "Index organized table"? Bir tablonun neden index ile yönetilmesini, organized edilmesini isteriz? Index Organized Table ne demek?

Index Organized Table bir B-tree index depolama çeşidir. Heap Organized Table'da olduğu gibi dağınık, sıralanmamış ve farklı yerde değildir. Index Organized tabloların verileri b-tree index yapısında tutulur ve primary key tarafından sort edilir.

Peki ne gibi faydaları var?

1) Primary Key kolonuna hızlı ve rastgele erişim sağlanır. Zaten index range scan yapıldığı için heap organized bir tabloya göre daha performanslıdır.
2) Çok daha düşük alan kaplar ve primary key orderında olduğu için range scan'de random scan'de bir heap tablo'ya göre daha hızlı gerçekleşir.

Bir Index Organized tablo, sanki bir heap tabloymuş gibi kullanılabilir. Yani, triggerlar yaratılabilir, partitioned olarak kullanılabilir. Ayrıca tablo üzerinde ikincil index yaratılmasına izin verir. Buna bitmap index de dahildir.

Bir heap tablo nasıl yaratılıyorsa, index organized tablo'da aşağı yukarı aynı şekilde yaratılır.

Örnek vermek gerekirse;

SQL> CREATE TABLE ogan_deneme(
2 kullanici_adi varchar2(40),
3 kullanici_numarasi number(20),
4 gorevi varchar2(50),
5 CONSTRAINT pk_ogan_deneme primary key (kullanici_adi))
6 ORGANIZATION INDEX
7 TABLESPACE users;

Table created.

Bir index organized tablo yaratmamız için primary key kullanmalıyız. Bu primary key üzerinden tablo organizasyonu kuruluyor ve primary key'in barındırdığı unique index ve not null özelliği ile korelasyon gerçekleşiyor.



İyi akşamlar,



Ogan

Response File kullanarak Non-Interactive Oracle Kurulumu

Merhaba,


Birçoğumuz Oracle'ı Windows tabanlı işletim sistemlerinde öğrendik ve öğrenmeye devam ediyoruz. Nedeni kurulumun, kullanımın ve çalıştırılmasının kolay oluşudur. Aslında teorik olarak doğru bir yaklaşım fakat bana göre bu kendimize yaptığımız bir kötülük. Nedeni ise gerçek hayatta bir production sistemin başına geçtiğimiz zaman önümüzde çoğu zaman ya bir terminal ekranı -ki bu bir linux ya da unix işletim sistemi olduğu anlamına gelir- ya da windows haricinde bir işletim sistemi gelir. Dolayısıyla windows'a kurulumla uzak yakın pek bir ilgisi yoktur.

Windows'da kernel ayarları yoktur, terminal denen shell'e komut gönderip onun da kernel ile konuşmasını sağlayan arayüz yoktur, uninstall ederken registry ile uğraşmak zorunda kalmayız gibi birçok faktör sayabiliriz.

Sistemimizin sağlıklı bir biçimde çalışmasını hedefliyorsak, bu işin %50'lik kısmı kurulum esnasında tamamlanır. Düzgün kurulmamış bir veritabanı ileride ciddi sorunlara dönüşebilmektedir.

Gelelim konumuza. Response file nedir? Öncelikle bir linux, unix, solaris, aix vb unix tabanlı sistemlere kurulum yaparken her zaman önümüzde grafiksel bir arayüz bulunmuyor. İş gereği uzaktan bağlanıyoruz ve çoğu zaman makinenin bile başında olamıyoruz. Elbetti ki makinenin başında olmayışımız, grafiksel bir takım arayüzlere ulaşamayacağımız anlamına gelmez. Çalıştığınız makine üzerinde grafik kartı dahi olmayabilir. Ben öncelikle grafik kartı olmayan bir makine üzerinde neler yapılabilir ondan bahsedeyim.

Unix işletim sistemlerinde X11 dediğimiz paketler vardır. X11 windows tabanlı olup genelde unix'ler için kullanılır ve unix sistemlerden grafiksel arayüzlerin çekilebilmesine yarar. Bu paketlerin gönderilmesi ve unix'ten cevap alınması ile windows'dan putty gibi ssh client'ı veya telnet ile bağlandığımız bir unix işletim sistemindeki grafiksel arayüzlerin çalıştırılmasını kendi işletim sistemimiz olan windows'a aktarabiliriz. Bu aktarım içinse Humming Bird "Exceed" veya XManager gibi özel yazılımlar kullanılır. Putty ile de X11 forwarding paketlerini işaretleyerek sonuç üretmek mümkün ancak unix tarafında DISPLAY parametresini şu şekilde set etmek gerekebilir:

# DISPLAY=:0.0 --> (Putty'de geçerli, xmanager ve exceed'de genelde 0.0 olur ancak kontrol edilmesi lazım)
# export $DISPLAY

Bu şekilde runInstaller executable dosyasını grafiksel olarak windows işletim sistemimizde çalıştırabiliriz. Sadece runInstaller değil, dbca, netca, patchset upgrade runInstaller gibi configuration assistantları da grafiksel arayüzlerle çalıştırmak mümkün.

Evet, bu kadar ön bilgi yeterli olacaktır sanırım. Şimdi, responsefile dediğimiz dosya, adı üzerinde bir cevap dosyasıdır. Peki neye cevap verir? Oracle responsefile'dan bir instance, client, database, net gibi configuration assistanları çalıştırırken hesap sorar. Grafiksel arayüzde sizden istediği bütün cevapları, bu yardımcı yazılımları çalıştırırken responsefile'dan okuyacaktır.

Responsefile ile gerçekleştirilen bütün kurulumlara "Silent ya da Non-interactive" kurulum denir. Az önce de dediğim gibi, son derece zor bir kurulum çeşidi olarak algılayabilirsiniz. "E grafik yoksa nasıl kuracağız?" gibi sorular kafınıza takılabilir. Bütün bunların cevabını işteyken acı çekerek öğrenmek yerine, yazının devamını takip ederek öğrenebilirsiniz.

otn.oracle.com'dan indirdiğimiz bir install dosyamız var diyelim. Örneğim kurmak istediğimiz işletim sistemi RHEL 4 (Red Hat Enterprise Linux 4) olsun. Önceki bütün aşamaları (preinstallation, kernel parametrelerinin ayarlanması, oracle kullanıcısı ve dba grubunun yaratılması vs) atlıyorum. Bu aşamada kafanıza takılan soruların bütün cevaplarını tahiti.oracle.com dan öğrenebilirsiniz.

Gunzip veya untar ettiğimiz Oracle kurulum dosyasının içinde -genelde runInstaller executable'ının olduğu yerde- response diye bir dosya göreceksiniz. İçerisinde ise her configuration assistant'a özel response file'larını göreceksiniz. DBCA çalıştırırken farklı, NETCA çalıştırırken farklı, runInstaller çalıştırırken farklı. Sadece Patchset'inkini göremezsiniz o da zaten patchset ile birlikte geliyor. Bu response file'lar bir şablon şeklindedir ve her parametreden önce o parametrenin ne olduğunu, örnek olarak ne yazılabileceğini anlatır. Bu parametreleri ilk başta tek tek geçerek ne olduğunu anlamanızda fayda var. İlk seferinde bunların hepsini düzenleyin ve sonraki kurulumlar için saklayın. İkinci seferinde size inanılmaz kolay gelecektir ve her kurulumunuzu response file ile yapmak isteyeceksiniz. Hem daha gelişmiş bir kurulum olur hem de sizin için artı bir bilgi kümesi haline gelir.

Response file'ın içeriğini ayarladığınız zaman artık Silent ya da non-interactive kuruluma geçebilirsiniz.

# ./runInstaller [-silent] [-nowelcome] [-nowait] -responsefile

Silent: Oracle kurulumunu silent modda çalıştırır ve kurulum penceresini açık tutar. Eğer bu modda çalışıyorsak, nowelcome opsiyonuna gerek yok.
Nowelcome: Kurulum penceresini açık tutar.
Nowait: Silent kurulum tamamlandığı zaman kurulum ekranını kapatır.
Responsefile: Kurulumun response file'ını belirtmek için kullanılır.
Response_file_name: Response file'ın adı ve önünde -eğere gerekiyorsa- absolute path'i.

Silent opsiyonuyla bu kurulumun bir non-interactive kurulum olduğunu ve bu non-interactive kurulumun bir response file'a bağlı olacağını, cevapların oradan alınacağını söylüyoruz. Ardından runInstaller da bu duruma göre kuruluma devam ediyor.

Bahsettiğim gibi, çoğu zaman önünüzde sadece bir terminal ekranı olacak. Onun için, en kritik komutları, en önemli adımları iyi bilmek, nasıl olsa ToAD gibi yazılımlar var oradan hallederim gibi düşüncelere kapılmamak lazım. Bu işin en önemli yolu sadece bir terminal ekranından müdahale edebilmektir. Bunu başarıp, bir Oracle kurabiliyorsanız, Oracle'ı upgrade edebiliyorsanız, çökmüş bir veritabanını ayağa kaldırabiliyorsanız işte o zaman oyun şimdi yeniden başlıyor demektir...

İyi akşamlar,

Ogan

25 Kasım 2008 Salı

Küçük bir Index Örneği

Merhaba,

Bu yazımda indexlerin kullanımı ile ilgili çok ufak ama son derece önemli olduğunu düşündüğüm birkaç örnek göstereceğim.

Index, bir kitabın sayfalarının tutulması, word dökümanında giriş sayfasından sonra gelen içindekiler bölümü veya süper marketlerdeki yiyecek bölümlerinin ayrılması gibidir. Bunların temel amacı aslında istenilen noktaya en hızlı şekilde ulaşmaktır. Peki neden en hızlı şekilde ulaşmak isteyelim? Cevap basit, "vakit, nakittir". Dolayısıyla veritabanının performansı için çoğu zaman index kullanmak isteyebiliriz. Tabii ki yeri geliyor ki index kullanmanın da full table scan yapmaktan daha berbar bir çözüm olduğunu görüyoruz.

Indexlerinde kendi içinde farklı farklı tipleri vardır. Bu index tiplerinin belirlenmesi veritabanındaki genel yapı ve tablonun içeriğine göre değişkenlik gösterebilir. B-tree indexlerin kullanılacağı bir tablodaki performans düşükken bu indexi reverse etmenin ya da bitmap index'e çevirmenin performans üzerindeki etkileri inanılmaz boyutlarda olabilir.

Ufak örneklerle başlayalım. (sys ile bağlıyız)

SQL> create user ogan identified by deneme;
User created.

SQL> grant connect,resource to ogan;

Grant succeeded.

SQL> conn ogan/deneme
Connected.
SQL> set linesize 600
SQL> set pagesize 150
SQL> create table ogan_deneme
2 (
3 username varchar2(40),
4 userid number (10)
5 );

Table created.

SQL> select * from user_indexes;

no rows selected

SQL> select * from user_constraints;

no rows selected

Bu aşamada birkaç veri girişi yapalım...

SQL> insert into ogan_deneme
2 values ('ogan',12345);

1 row created.

SQL> insert into ogan_deneme select * from ogan_deneme;

1 row created.

SQL> /

2 rows created.

SQL> /

4 rows created.

SQL> /

8 rows created.

SQL> /

16 rows created.

SQL> /

32 rows created.

SQL> /

64 rows created.

SQL> /

128 rows created.

SQL> select count(*) from ogan_deneme;

COUNT(*)
--------------
256

SQL> select * from ogan_deneme where userid=12345;

256 rows selected.

Elapsed: 00:00:01.88

Execution Plan----------------------------------------------------------
Plan hash value: 1896958321
---------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------
0 SELECT STATEMENT 256 8960 3 (0) 00:00:01
* 1 TABLE ACCESS FULL OGAN_DENEME 256 8960 3 (0) 00:00:01
--------------------------------------------------------------------------------

Statistics----------------------------------------------------------
5 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
3908 bytes sent via SQL*Net to client
587 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
256 rows processed

Bu aşamadan sonra index yaratalım ve neler olduğunu görelim. Yaratacağımız index bir b-tree index olacaktır.

SQL> create index index_deneme on ogan_deneme(userid);

Index created.

SQL> select index_name, table_name from user_indexes;

INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
INDEX_DENEME OGAN_DENEME

SQL> select * from ogan_deneme where userid=12345;

256 rows selected.

Elapsed: 00:00:00.53

Execution Plan----------------------------------------------------------
Plan hash value: 4118286141
--------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------------------
0 SELECT STATEMENT 256 8960 2 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID OGAN_DENEME 256 8960 2 (0) 00:00:01
* 2 INDEX RANGE SCAN INDEX_DENEME 256 1 (0) 00:00:01
--------------------------------------------------------------------------------------------

Statistics----------------------------------------------------------
13 recursive calls
0 db block gets
46 consistent gets
0 physical reads
0 redo size
3908 bytes sent via SQL*Net to client
587 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
256 rows processed

Daha önce çalıştırdığımız full table scan, içerik duplicate olduğu için 32 consistent gets çıktısı verdi. Ancak Index range scan ile arattığımız zaman bu rakam 46'ya ulaştı. Yani, veritabanının okuduğu alan sayısı arttı ve biz bunun düşük olmasını bekliyorduk. Dolayısıyla performans adına pek iç açıcı bir sonuç olmadığını görüyoruz.

SQL> drop index index_deneme;

Index dropped.

SQL> drop table ogan_deneme purge;

Table dropped.

SQL> create table ogan_deneme
2
(
3
username varchar2(40),
4 userid number(10)
5 );

Table created.

SQL> create index index_deneme on ogan_deneme(userid);

Index created.

SQL> insert into ogan_deneme 2 values('ogan',12345);
1 row created.

SQL> insert into ogan_deneme 2 values('ahmet',54321);
1 row created.

SQL> insert into ogan_deneme 2 values('mehmet',32145);
1 row created.

SQL> insert into ogan_deneme 2 values('ali',54123);
1 row created.

SQL> insert into ogan_deneme select * from ogan_deneme;
4 rows created.
SQL> /
8 rows created.
SQL> /
16 rows created.
SQL> /
32 rows created.
SQL> /
64 rows created.
SQL> /
128 rows created.
SQL> /
256 rows created.
SQL> /
512 rows created.
SQL> /
1024 rows created.

SQL> select * from ogan_deneme;

2048 rows selected.
Elapsed: 00:00:03.18
Execution Plan----------------------------------------------------------

Plan hash value: 1896958321
---------------------------------------------------------------------------------

Id Operation Name Rows Bytes Cost (%CPU) Time --------------------------------------------------------------------------------- 0 SELECT STATEMENT 2048 71680 3 (0) 00:00:01 1 TABLE ACCESS FULL OGAN_DENEME 2048 71680 3 (0) 00:00:01 ---------------------------------------------------------------------------------

Statistics----------------------------------------------------------

152 consistent gets

2048 rows processed

Şimdi bir de index range scan ile bakalım duruma...

SQL> select * from ogan_deneme where userid=12345 or userid=54321;

1024 rows selected.
Execution Plan----------------------------------------------------------

Plan hash value: 1212428791
--------------------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time --------------------------------------------------------------------------------------------- 0 SELECT STATEMENT 1024 35840 2 (0) 00:00:01 1 INLIST ITERATOR 2 TABLE ACCESS BY INDEX ROWID OGAN_DENEME 1024 35840 2 (0) 00:00:01 * 3 INDEX RANGE SCAN INDEX_DENEME 8 2 (0) 00:00:01 ---------------------------------------------------------------------------------------------
Statistics----------------------------------------------------------

154 consistent gets

1024 rows processed

Consistent gets 2 arttı. Şu aşamada sonuç olarak yine amaçsız bir index olarak karşımızda duruyor olabilir. Şimdi bu indeximizi işe yarar hale getirebilmek için tabloda birkaç değişiklik gerçekleştirelim.

SQL> drop index index_deneme;

Index dropped.

SQL> truncate table ogan_deneme;

Table truncated.

SQL> insert into ogan_deneme

2 values('ogan',12345);
1 row created.


SQL> insert into ogan_deneme

2 values ('ahmet',99999);
1 row created.


SQL> insert into ogan_deneme

2 values ('mehmet',88881);
1 row created.


SQL> insert into ogan_deneme

2 values ('ali',12333);
1 row created.


SQL> insert into ogan_deneme

2 values ('veli',24144);
1 row created.

SQL> select * from ogan.ogan_deneme;

Execution Plan----------------------------------------------------------Plan hash value: 1896958321
--------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time --------------------------------------------------------------------------------- 0 SELECT STATEMENT 5 175 3 (0) 00:00:01 1 TABLE ACCESS FULL OGAN_DENEME 5 175 3 (0) 00:00:01 ---------------------------------------------------------------------------------
Statistics----------------------------------------------------------

16 consistent gets

5 rows processed

Şimdi index yaratalım...

SQL> create index index_deneme on ogan_deneme(userid);

Index created.

SQL> select * from ogan_deneme where userid=12345;

Execution Plan----------------------------------------------------------

Plan hash value: 4118286141
-------------------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time -------------------------------------------------------------------------------------------- 0 SELECT STATEMENT 1 35 2 (0) 00:00:01 1 TABLE ACCESS BY INDEX ROWID OGAN_DENEME 1 35 2 (0) 00:00:01 * 2 INDEX RANGE SCAN INDEX_DENEME 1 1 (0) 00:00:01 --------------------------------------------------------------------------------------------
Statistics----------------------------------------------------------

3 consistent gets

1 rows processed

Çok az sayıda ama birbirinden farklı verileri barındıran bir tabloda index yaratılır mı? sorusunun en güzel cevabı. Tam 13 consistent gets daha düşük çıktı aldık. Sadece bir index yaratarak. Dolayısıyla 3-5 verili bir tabloda da index yaratmanın önemi çok açık olarak ortada. Bu tabloya, özellikle OLTP sistemlerde günde milyonlarca defa giriş olabileceğini düşünürseniz inanılmaz bir zaman kazancı olacaktır.

Indexler durduğu yerde disable / unusable olmazlar. Tablo'da bir movement olduğu zaman (alter table deneme move; , alter table deneme shrink space; gibi)

SQL> select * from ogan_deneme where userid=12345;

Execution Plan----------------------------------------------------------

Plan hash value: 1896958321
--------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time --------------------------------------------------------------------------------- 0 SELECT STATEMENT 1 35 3 (0) 00:00:01 * 1 TABLE ACCESS FULL OGAN_DENEME 1 35 3 (0) 00:00:01 ---------------------------------------------------------------------------------

Tablo'yu move ettik ve blockların bulunduğu yerleri değiştirdik. Dolayısıyla index disable duruma düştü.

SQL> alter index index_deneme rebuild online;

Bu noktada unutmamak gereken durum şudur. Index rebuild edilirken, table'da exclusive lock meydana gelir ve kimse tabloya erişemez. Yani, büyük çapta bir indexin çok uzun sürebileceğini düşünürsek sıkıntı olabilir.

SQL> select * from ogan_deneme where userid=12345;

Execution Plan----------------------------------------------------------

Plan hash value: 4118286141
-------------------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time -------------------------------------------------------------------------------------------- 0 SELECT STATEMENT 1 35 2 (0) 00:00:01 1 TABLE ACCESS BY INDEX ROWID OGAN_DENEME 1 35 2 (0) 00:00:01 * 2 INDEX RANGE SCAN INDEX_DENEME 1 1 (0) 00:00:01 --------------------------------------------------------------------------------------------

Index'i rebuild ettiğimiz zaman tekrar kullanılır duruma kavuştu. Az önceki gibi düşük sayıda farklı veriler içeren bir tablo'da bu sıkıntı olsaydı rebuild sırasında da sıkıntı yaşamazdık. Yalnız rebuild ederken index'in size'ı son derece dikkatle izlenmelidir.

Index'ler üzerine konuşmakla bitiremeyiz. Yukarıdaki örnekler sadece b-tree indexlerin unique olanlarıdır. Bunun reverse key, bitmap, partitioned, non-partitioned vb tipleri de mevcuttur. Partitionedların içerisinde global local ve bunların dışında fake indexler gibi index tiplerini de eklersek herbiri ayrı ayrı bir konu olarak saatlerce anlatılabilir. Best Practicelerini de eklersek Oracle içinde apayrı bir uzmanlık alanı bile oluşturabilecek kadar geniş bir konudur.

Bir veritabanının performansının önemli ölçüde değişmesini (iyileşmesini) istiyorsak, index yaratmalıyız demek zor ancak sağlıklı indexler ve doğru tercihler ile son derece faydalı olacaktır.

İyi geceler,

Ogan

Takip et: @oganozdogan