ORACLE PERFORMANS TUNING

Oracle Performans Tuning ayarlama işlemleri dört adımdan oluşur:
a) Server ve Network Ayarları:
Burada oluşabilecek CPU aşırı yüklenmeleri, hafıza yetersizliği veya I/O darboğazları Oracle’da ne kadar performans ayarı yapılsa bile giderilemez. Bu yüzden öncelikle buranın yapılandırılması gerekir.
b) Instance ayarları : SGA ve başlangıç parametrelerini doğru şekilde ayarlamayı içerir. Doğrudan db_block_buffers, shared_pool_size ve sort_area_size tanımlarında kaynakların yetersizliğini gidermekle ilgilenir. Ayrıca Oracle için önemli olan optimizer_mode gibi parametrelerlede ilgilenir.
c) Nesne ayarları : Tablo ve indekslerin ayarlamalarını içerir. PCTFREE, PCTUSED ve FREELISTS ayarları bir serverın performansında çok etkindir.
d) SQL cümleleri: Belkide binlerce SQL cümlesini incelemeyi gerektirdiği için en çok zaman alan çalışmalar burada yapılır. Oracle ipuçlarını kullanarak ve herbir cümlenin çalışma planını dikkatlice gözden geçirerek bir çalışma yapılır.
a) SERVER VE NETWORK AYARLARI
a. RHEL 3/4 sistemlerde smp çekirdek 16GB hafızaya kadar destek verir. Normalde 16GB-64GB arası hafızaya ship serverlarda tüm hafızayı kullanmak için hugemem linux çekirdeği kullanılması gerekir. Ancak 8GB’den fazlası kullanılacaksa gene hugemem çekirdek kullanılması tavsiye edilir. Çünkü bu çekirdek büyük hafıza adreslemesinde daha başarılıdır.
b. X68 mimarilerde fiziksel hafızanın ilk 16MB-896MB kısmı çekirdek için ayrılmış ve “low memory” (ZONE_NORMAL) alanıdır. Bir çok çekirdek kaynağı burada yeralır. Dolayısıyla burası kritik bir bölgedir. Burada boşaltma yapabilmek için çekirdek, “high memory” alanına aktarma yapar. Eğer çekirdek bunu yapmayı başaramaz ve “low memory” alanı dolarsa çekirdek sistemi askıya alır.
c. Çekirdeğin en güncel sürümünün ev donanımların güncel sürücülerinin yüklenmiş olmasına dikkat edilmelidir. Uyumlu sürüciler genelde /lib/modules//kernel/drivers/addon dizinine kurulur.
• Yüklü çekirdek lerin listesi için: $ rpm -qa | grep kernel
• O anda çalışan çekirdek versiyonu : $ uname –r
• 2.4.21-32.0.1.ELhugemem çekirdeğini kurmak için kernel-hugemem RPM indirilir ve şu çalıştırılır: # rpm -ivh kernel-hugemem-2.4.21-32.0.1.EL.i686.rpm
• Kullanılmayan eski çekirdeği kaldırmak için : # rpm –e
d. Hafıza kullanımı: Kullanılmakta olan hafızayı görmek için : grep MemTotal /proc/meminfo yada free komutu kullanılır. Linux boş duran hafızayı I/O ihtiyacını azaltmak ve hızlanmak için devamlı kullanır. Bu yüzden bu komutlarla çıkan sonuçlarda hafızanın neredeyse tamamı kullanılıyor gibi görünür. Ama gerçekte çoğu uygulamalar tarafından değil çekirdeğin I/O erişimin azaltma ihtiyacı yüzünden doludur. Uygulamalara hafızada yer gerektiğinde çekirdek bunları diske yazarak yer açar.
e. Page Cache: Dosyaların veya çalışabilir programların verilerini tutan bir disk önbelleğidir. Diskten okumaları azaltmak için kullanılır.
f. Virtual Memory ayarları sysctl adlı programlama odaklı bir komutla sistem çalışır durumdayken yapılabilir. Bu komutla aynı zamanda /etc/sysctl.conf dosyasında yeralan virtual machine parametreleride ayarlanır ki sistem reboot edildiğinde yeni ayarları bu dosyadan okuyabilsin.
g. bdflush dosyasında 6 tanesi ayarlanabilir 9 parametre yeralır. Amacı önbellekte tutulacak sayfalardan hangilerinin boşaltılıp diske yazılacağının değerlendirmesini yapmaktır.
• nftrac : bdflsh çalışmasının aktif olması için önbellek alanında bulunması gereken dirty page yüzdesi.
• ndirty: bdflsh işleminde diske yazma yapabilmek için önbellek alanında bulunması gereken en çok dirty page sayısı.
• interval : bdflsh yinelemeleri arasındaki gecikme anı adedi. (bir an 10ms’lik zaman)
• age_buffer : Normal alanların diske boşaltılması için gerekli yaşlanma zamanı.
• nfract_sync : Önbellek alanında sayfaların hafıza yerine diske yazılması için gereken dirty page yüzdesi.
• nfract_stop_bdflush : bdflsh işleminin tekrar bekleme moduna geçmesi için önbellek alanında bırakılması gereken dirty page yüzdesidir.
h. Swap Space: Uzun süren işlemlerin olduğu serverlarda swap bölümü kullanmak çok faydalıdır. Bu işlemler esnasında diskte ulaşılacak alanlar aynı olacağından bunların hafızada önbelleğe alınması I/O ihtiyacını azaltacaktır. Oracle tarafından tavsiye edilen swap ölçüleri:
RAM Swap alanı
1GB-2GB RAM * 1,5
2GB-8GB RAM
8GB’den çok RAM * 0,75
Swap kullanımı şu komutlarla görülebilir:
• grep SwapTotal /proc/meminfo
• free
• cat /proc/meminfo
i. $ vmstat 3 100 komutuyla o anki swap kullanımı görülebilir. Burada si ve so sütunları diskten swap’a alınan ve swap’tan diske boşaltılan sayfa miktarlarını gösterir. Eğer sürekli bir swap hareketi burada görülüyorsa ya daha fazla fiziksel hafıza eklenmeli yada SGA büyüklüğü düşürülmelidir. Geçmişteki swap hareketlerini görmek için sar komutundan yararlanılır:
# sar –W –f /var/log/sa/sa12
j. Shared Memeory (paylaşılan hafıza alanı): İşlemlere yaygın kullanılan yapı ve verileri koymaları için tahsis edilen paylaşılan hafıza parçasıdır. Oracle shared memory alanlarını Oracle işlemleri tarafından kullanılan SGA için kullanır. Shared memory ayarlarını görmek için:
$ ipcs –lm
k. SHMMAX :Bir linux işleminin sanal adres alanında kullanabileceği tek bir shared memory bölümünün byte cinsinden büyüklüğüdür. SGA, shared memory’den oluştuğu için SHMMAX, SGA’yıda kısıtlar. Bu yüzden SHMMAX, SGA’dab biraz daha büyük olmalıdır, aksi durumda “ORA-27123: unable to attach to shared memory segment” hatası alınır.Bir shared memory bölümünün büyüklüğünü öğrenmek için : # cat /proc/sys/kernel/shmmaxSHMMAX değerini çalışma anında değiştirmek için :
# echo 2147483648 > /proc/sys/kernel/shmmaxAlternatif olarak SHMMAX değerini çalışma anında değiştirmek için :
# sysctl -w kernel.shmmax=2147483648Değişiklikleri kalıcı yapmak için boot dosyasına ekler :
# echo “kernel.shmmax=2147483648″ >> /etc/sysctl.confl.
SHMMNI : Sistemde en çok kaç adet shared memory bölümü bulunacağını belirler. Oracle enaz 4096 segmen olmasını önerir.SHMMNI adedini öğrenmek için :
# cat /proc/sys/kernel/shmmniÇalışma anında değeri atamak için :
# echo 4096 > /proc/sys/kernel/shmmniAlternatif olarak çalışma anında değeri atamak için :
# sysctl -w kernel.shmmni=4096Değişiklikleri kalıcı yapmak için boot dosyasına ekler : # echo “kernel.shmmni=4096″ >> /etc/sysctl.confm.
SHMALL : Sistem genelinde kullanılabilecek shared memory page toplam adedini belirler. Bu değer enaz SHMMAX/PAGE_SIZE kadar olmalıdır. PAGE_SIZE eğer büyük hafıza alanları kullanmayı sağlayacan Big Pages veya Huge Pages kullanılmıyorsa genellikle 4096 byte olarak ele alınır. Geçerli değeri 2097152 byte olur. Buradan bir sistemdeki toplam shared memory miktarıda 2097152*4096 (SHMALL*PAGE_SIZE) = 8 GB olur.
Sistemde kullanılan PAGE_SIZE değerini : $ getconf PAGE_SIZE
Sistemdeki en çok shared memory page adedini öğrenmek için :
# cat /proc/sys/kernel/shmallSHMALL değerini sistem çalışırken değiştirmek için :
# echo 2097152 > /proc/sys/kernel/shmallAlternatif olarak SHMALL değerini sistem çalışırken değiştirmek için :
# sysctl -w kernel.shmall=2097152Değişiklikleri kalıcı kılmak için : # echo “kernel.shmall=2097152″ >> /etc/sysctl.conf
n. Shared Memory alanının silinmesi: Bazen bir instance çöktüğünde Oracle’ın shared memory bölümünü elle silmek gerekebilir. Kullanılan shared memory bölümlerini listelemek için : $ ipcs –mBir shared memory alanı hakkında detay bilgilere erişmek için
shmid değeri kullanılır: $ ipcs -m -i 32768Bir shared memory bölümünü silmek içinde
shmid kullanılır : $ ipcrm shm 32768Bir shared memory bölümünü silmenin alternetif yolu Oracle sysresv programıdır : $ sysresvo.
File Handles : Bir linux sisteminde aynı anda açık olabilecek dosya sayısını belirler. Oracle bütün sistem için en azından 65536 olmasını önerir. Öğrenmek için : cat /proc/sys/fs/file-maxMevcutta kullanımda olan açık dosya sayısını öğrenmek için : cat /proc/sys/fs/file-nrDeğeri sistem çalışırken değiştirmek için iki yol : # echo 65536 > /proc/sys/fs/file-max# sysctl -w fs.file-max=65536Değişiklikleri kalıcı yapmak için : # echo “fs.file-max=65536″ >> /etc/sysctl.conf
p. Network Ayarları: ethernet kartı ayarlarını değiştirmek için ethtool komutu kullanılabilir. Ethernet ayarlarını görmek için :
# ethtool eth0
Bir kartın hızını değiştirmek için :
# ethtool –s eth0 speed 1000 duplex full autoneg off
Değişikliği kalıcı yapmak için /etc/sysconfig/network-scripts/ifcfg-eth0 dosyasına ETHTTOOL_OPT çevre değişkeni eklenir:
ETHTOOL_OPTS=”speed 1000 duplex full autoneg off”
q. Network Çekirdek Ayarları: Oracle, linux sistemlerde kendi iç haberleşmesinde geçerli protokol olarak UDP kullanmaya başladı. Instance’lar arasındaki haberleşmedede bundan yararlanılıyor. Oracle geçerli send buffer size ve receive buffer size değerlerini 256KB olarak atanmasını öneriyor.
b) INSTANCE AYARLARIInstance performansını ayarlamanın en iyi yolu öncelikle sistemle ilgili farklı istatistiki bilgilerin sağlıklı şekilde elde etmekten geçer. Sistemin çok kullanıldığı zaman dilimlerinin bilinmesi önem taşır. Bu aşamada temel olarak istatistiki bilgiler aşağıdaki alanlarda toplanabilir;
a. Uygulamanın istatistikleri (response time, transation miktarı vb..)
b. Veritabanı istatistikleric. İşletim sistemi istatistiklerid. Disk I/O istatistiklerie. Network istatistikleriPerformans ayarlarken sorunun doğru tesbit edilmesi en önemli noktadır. Bunun içinde yukarıdaki istatistikler ile sorun çıktığında karşılaşılan istatistikler karşılaştırılabilir. Bir perfomans ayarı yaparken şu adımlarda çalışma yapılabilir:
Problemin Tanımlanması : Performansın normal kabul edilebilmesi için olması gereken transaction sayısı, sistemin cevap süresi (response time), gibi değerler gözden geçirilmelidir. Yavaşlıktan bütün instance’mı yoksa bir uygulama, bir çalışma veya sadece bir kullanıcımı etkilendiği belirlenir. Problemin sistem yoğunluğunun en çok olduğu zamanlardamı yoksa birdenmi ortaya çıktığı yada yapılan bir işleminmi buna sebep olduğunun bilinmesi gerekir. Yavaşlamanın büyüklüğünün ve yaygınlığının ölçülebilmesi gerekir.
Server’ın kontrol edilmesi: Server donanım özelliklerinin kontrol edilip oluşturduğu darboğazların belirlenmesini ifade eder.
• CPU kullanımının detaylı incelenmesi, oracle processleri ve oracle’a ait olmayan processlerin ve CPU tüketimlerinin kontrol edilmesi önemlidir. Oracle CPU istatistikleri aşağıdaki
V$SYSSTAT : Bütün sessionlar tarafından oluşturulan CPU kullanımıo
V$SESSTAT: Session başına CPU kullanım istatistiklerini verir
V$RSRC_CONSUMER_GROUP: Oracle Database Resource Manager çalışırken herbir CPU tüketici grubunun ne kadar tüketim yaptığı bilgisini verir.
• I/O kullanımının kontrol edilmesi diğer bir kritik parametredir.
Disk erişim kuyruğunun 2’den büyük olması vey disk hizmet süresinin 20-30 ms’nin üzerinde olması disk erişiminde darboğaz olduğu anlamına gelir. Bu durumda I/O performansının birden çok diske dağıtılması gerekmektedir.
Bir seçenek olarak programın daha az I/O üretmesi mümkün olabiliyorsa buradada iyileştirmelere gidilebilir.
Database dosyasının olduğu diskte başka dosyalarda varsa ve bunlarada yoğun erişim yapılıyorsa bunlar başka disklere alınarak I/O performansının bölünmesi engellenmiş olur.
V$SYSTEM_EVENT: Oracle wait event’lerinden I/O ile ilgili olanları görebilmek için kullanılır. “Burada sequential read”, “parallel write” gibi olaylar kontrol edilir.
V$SQLAREA: SQL cümleleri tarafından oluşturulan I/O eventlerini ve fiziksel okumaları verir. Fiziksel okumalar cümlelerde yapılacak değişikliklerle yada order by ifadesinde yapılacak değişiklikle azaltılabilir.
• Network o Kullanımında ping süreleri, hattaki collision sayıları buradaki performans iyileştirme ayarlarındandır. o Buradaki trafiğin azaltılması için yoğun zamanlarda networkten büyük veri transferlerinin geçişi durdurulabiliro Her defasında servera ulaşıp veri transfer edilecekse bunun yerine server tarafında oluşturulacak bir toplu işlem ile bu isteklerin bir defada halledilmesi yoluna gitmek daha anlamlı olur.
c) NESNE AYARLARI
• Optimized Index Kullanın. SQL cümlelerinin iyileştirilmesi ağırlıklı olarak indexlere dayanır. Indexler iki ucu keskin bıçak gibidir. İyi kullanılırsa performansa çok büyük etkisi vardır. Aynı şekilde kötü yapılandırıldığında da etkisi negatiftir.
• Indexler oluşturulurken kullanılan field sayıları, sıraları ve tipleri çok önemlidir. Index içinde kullanılacak alanların olabildiğince uniqeue alanlar olmalarına dikkat edilmelidir. Aksi takdirde bu indexte yapılan aramalardan çok fazla sonuç döneceği için sorgu yavaşlayacaktır. Indexler oluşturulurken eklenen fieldların sırasıda bu mantık içinde önem taşır.
• Tablodan çok sayıda kayıt silindiğinde tabloyu SHRINK etmek ve indexlerini yeniden oluşturmak performansı arttırır. Çünkü index’lerin ve tablonun arasında kayıt olmayan boş alanları olması bir çeşit fragmantasyon olarak düşünülebilir.
• Tablodan yüklü sayıda kayıt silineceğinde bu veritabanını uzun süre meşgul eder. Bu kadar uzun süre silme işlemiyle uğraşması veritabanının diğer işlemlere ayıracağı zamanıda azaltacağı için performans sorunlarına yol açacaktır. Bunun önüne geçmek için silme işlemini birkaç cümleye ayırarak kısım kısım kayıtları silmek ve her bir cümle çalıştırılıp silme işlemi tamamlandıktan sonra aralarda 3 dakika kadar beklemek sistem performansında çok fazla yük olmayı engelleyecektir.
• Tablolarda oluşturulan indexlerin sayısını çok fazla yapmak iş yükünüde arttırır. Dolayısıyla tabloda yapılan bir değişiklik bütün indexleri etkileyeceği için performansada etki edecektir.
• Foreign Key olan alanlarda index yapmak bu alanlarda yapılacak update işlemlerinde kontrol edilecek kayıt sayısını belirgin şekilde düşürür.
d) SQL CÜMLELERİ
Oracle SQL cümlelerini iki aşamada işler: yorumlamak (parse) ve çalıştırmak (execution). Bunlarda iyileştirme yapmak SQL zamanınıda kısaltacaktır. SQL cümlesinde performans ayarı yapmak için cümlenin doğru çalıştığını görmek gerekir. Verimli SQL cümlesi yazmak ile anlaşılabilir cümle yazmak arasında ince bir çizgi vardır.
1. Veriyi anlayın ve tablo yapılarını iyi inceleyin.
2. Tablodaki bütün sütünları indekslemeyin. Sadece gerekenleri indeksleyin.
3. FROM’dan sonra yazılan tablo isimlerinin sırası tabloların kullanım yoğunluğuyla doğru orantılı olmalıdır. Yani eç fazla satır döndürecek tablo en önce olacak şekilde sıralanmalıdır.
4. Eğer bir VIEW üç tabloyu birleştiriyorsa ve ihtiyacınız olmayan veriyi de yüklüyorsa onu kullanmayın.5. Indeksli sütünlarda NOT IN ve NOT = ifadelerinden kaçının. Bunlar optimizerın indeksleri kullanmasına mani olur.
6. where amount != 0 yerine where amount > 0 şeklindeki ifadeleri tercih edin.
7. Indeksli alanlarda where project_category is not null şeklindeki ifadeler kullanmayın. Null kullanmak indeks kullanmaya mani olur.8. WHERE ifadesinden sonra bir sütün kullanılması düşünülüyorsa indekslenmiş olmalıdır.
9. NOT IN yerine NOT EXISTS kullanın.
10. Sütünlara erişirken daima tablolar için konulan takma isimleri (alias) kullanın.
11. Eğer bir sorgu bir tablodaki kayıtların %20’sinden fazlasını okuyacaksa bunun yerine full table scan kullanın.
12. Mümkün olduğu durumlarda OR yerine UNINON kullanın.
13. Karakter alandaki bir indeksi kullanmak istemiyorsanız alana bir null değer ekleyin, sayısal alandaki bir indeksi kullanmak istemiyorsanız alana bir sıfır ekleyin. Mesela salary+0. Bu işlem istediğimiz indeksi kullanmamıza imkan verir.
14. SQL cümlelerinin Oracle tarafından paylaşılabilir olması için cümlenin büyük/küçük harf, kelime arası boşulklar ve şema nesnelerinin isimleri açısından herşeyiyle aynı olması gerekir. Aksi takdirde cümle yeniden yorumlanacaktır.

SELECT NAME FROM S_CUSTOMER WHERE ID = 212; Asıl cümle
SELECT NAME FROM s_customer WHERE ID = 212; Yeniden yorumlanır
SELECT NAME FROM s_customer WHERE ID = 212; Yeniden yorumlanır

Oracle hafızasında SQL cümle paylaşımın fazla olabilmesi için:o Cümlelerde ya büyük yada küçük harf kullanılıdır.o Her yeni SQL cümlesi yeni bir satırda yazılır.o Bütün kelimeleri tek boşlukla ayrılır.Cümlelerde değişken kullanılmalıdır. Böylelikle cümleler hep aynı görüneceği için paylaşılabilir.
Paylaşılabilir
SELECT * FROM emp WHERE emp_no = :B1; Bind value: 123
SELECT * FROM emp WHERE emp_no = :B1; Bind value: 987
Paylaşılamaz
SELECT * FROM emp WHERE emp_no = 123;
SELECT * FROM emp WHERE emp_no = 987;

Cümlelerde tablolara takma isim verirken bir kural geliştirilip standart olarak kullanılmalıdır.

15.    WHERE ifadesinden sonra kullanılan sütün isimlerinde hesaplama yaptırılırsa (bir fonksiyon çalıştırmak gibi), bunlar üzerinde indeks olsa bile gözardı edilir. Buda yavaşlığa sebep olur.

 16.    VIEW’larında performans ayarlarının yapılmasında aynı kurallara göre bir çalışma mutlaka yapılmalıdır.

17.    SELECT ifadelerinde HAVING kelimesinden kaçınmak lazımdır. Çünkü HAVING ifadesi bütün satırlar yüklendikten sonra uygulanır. Sıralamalarda hız için mümkün olduğunca WHERE kullanılmalıdır. HAVING sadece özetleme çalışmalarında kullanılmalıdır.

 18.    Bir cümle içindeki altsorguların (subquery, table lookup) sayısını olabildiğince azaltmak gerekir. Özellikle SELECT veya birden çok sütünda UPDATE olan cümleler için geçerlidir.