TROUG - Oracle EBS Day 2013

ORACLE SQL Tunning’e Bir Çırpıda Bakış

Bu makalede Oracle ile ilgili bazı temel bilgilere ve özellikle de performans konusuna değinmek istiyorum.

Veri Blokları
Data Block : Mantıksal olarak veri tutan en küçük veri birimidir. Küçükten büyüğe doğru
Extent ,Segment ,Tablespace ,Database olarak devam eder.
Fiziksel olarak ise OS blokları ve data file lerdan oluşur.
Tablespace : Bir dosya sistemindeki klasör gibi nitelendirebiliriz. Mantıksal olarak datafile bazında verileri fiziksel olarak gruplar. Schema ise tablespace’den farklı olarak mantıksaldır ve kullanım hakları ile ilgili bir gruplama yapar. Yani bir schema bir kullanıcının kullanmaya yetkili olduğu objeleri tutar. Bir kullanıcı bir tablespace’deki bütün objeleri kullanamayabilir.

*** : Performans faktörlerinden bir tanesini sistemin nasıl bir sistem olduğu etkiler. İki türlü sistem vardır. Bunlar OLAP(Online Analytic Processing). yani raporlama sistemi büyük select sorguları çalıştırılır , diğeri ise OLTP (Online Transaction Processing) bu sistemde de küçük sorgular ancak çok kullanıcılı ve yoğun bir sorgu trafiği vardır.
Sistem türüne göre block size’lar belirlenmeli ve gerekli optimizasyonlar yapılmalıdır.
Örneğin çok büyük miktarda veri raporlanıyorsa data bloklar büyük set edilmeli çünkü bir seferde (1 fiziksel okumada)getirilecek veri miktarı daha fazla olur.

Bellek Yönetimi Oracle yapı itibari ile SGA(System Global Area) ve PGA(Program Global Area) olmak üzere 2 tür bellek yapısı vardır.Bu yapıda SGA bütün veritabanı kullanları için ortak iken PGA ise connection bazlı olarak açılır. bu memory alanları Oracle’da performansı direk olarak etkiler.Belleklerin iyi paylaştırılması ciddi anlamda performans kazandırır.Zaten tunning yaparken en çok karşımıza sorun olarak IO çıkacak yani performamızı belirleyen en önemli kriterlerden bir tanesi fiziksel okuma. Ne kadar az diske gidersek o kadar hızlanacağımızı aklımızın bir köşesinde tutalım.

*** : Ne kadar az fiziksel diske gidersek o kadar yüksek performans sağlarız. Bellekten çalışmak diskten çalışmaya göre daha hızlıdır.

Yukardaki şekilde de görülen yapıları bizim için önemli olanları kısaca tanımlayalım.
Library Cache : Her sql sorgusu çalıştırıldığında sorgu parse edilir ve bir tane execution plan denilen bir plan oluşturur. Bu plan hangi tabloda hangi indexler ile nereye bağlantı yapıldı , costu ne gibi bir takım özellikleri barındırır. İşte bir sorgu bir kez çalıştırıldığında oluşan execution plan bu bellek alanına yazılır. Eğer aynı sorgu bir kez daha çalıştırılırsa eğer Library Cache’te varsa tekrardan sorguyu parse etmez ve aynı planı kullanarak performans kazanır.
Data Dictionary Cache : Sql cümlesi bir tabloya erişmeye çalıştığında (SELECT * FROM tablo1) bu tabloya select atma hakkı var mı , bu tablonun istatistikleri neler gibi bir takım bilgilerin tutulduğu bellek alanıdır.
Buffer Cache : Bir tabloya bellek alanı boşken ilk sefer select sorgusu çalıştırılırsa direk olarak fiziksel diskten okur.Ancak bir kez çalıştıktan sonra bu bellek alanından okuyarak sonucu daha hızlı getirir.
Large Pool : Paralel ve Shared Server gibi işlerde kullanılabilir. Opsiyoneldir.
Log Buffer : Burada transaction bilgileri var. Veritabanı herhangi bir sebepten dolayı kapanırsa buradan son yapılan çalışmaları elde edebiliriz.

Redo Buffer,Java Pool ve Stream Pool

PGA ise kullanıcı bazlıdır. Oracle’a açılan her bir User process için server tarafında bir server process tahsis edilir. Yani her bir kullanıcı için serverda bellekten yer ayrılır.

*** : Gereksiz connectionların hafızadan temizlenmesi performansı artırır.

*** : Oracle 8i de bellek yönetimi tamamen admine bırakılmıştı.Her bir cache e teker teker boyutlarını vermek gerekiyordu.Library Cache’e ayrı Data Dictionary Cache’e ayrı. 10g ile birlikte sadece PGA ve SGA ya bellek alanları vermek durumundaydık , alt cache’lere teker teker verme zahmetinden kurtulmuş olduk. 11g ile birlikte bu yönetimi Oracle tamamen kendisi yapabilmektedir.

Performansınızı Uçurun

1- Tablo istatistiklerinin yanlış olması : Oracle execution plan oluştururken istatistiklere bakar. İstatistiklerde bir tabloda kaç kayıt var vs gibi bilgiler mevcuttur. Eğer sık insert alan bir tablo ise istatistikler sorgu çalışacağı anda yanlış olabilir.

Bunu bir örnekle modelleyelim. Stok adında bir tablomuz olsun başlangıçta 10 kaydımız var diyelim daha sonra toplu birşekilde tabloya import yaptık ve 9000000 kaydımız oldu diyelm. Oracle normal şartlarda sadece belli zamanlarda tabloların istatistiklerini tekrardan düzenler bu yüzden import işlemini yaptıktan sonra istatistiklerde kayıt sayısı hala 10 görülecektir.

SELECT * FROM Stok WHERE Kategori=’Bilgisayar’ sorgusunu çalıştırdık diyelim.

Stok tablomuzda Katagori kolonunda indeksimiz olmasına rağmen kayıt sayısı az olduğunu sanan Oracle bu indeksi kullanmayıp Full Table arama yapabilir. Bu da sistemi bir hayli yorar. Halbuki istatistikler tam olsa index range scan yaparak çok daha az maliyet ile kayıtları getirebilir.

2- Sorgularda eşitliğin sol tarafında fonksiyon kullanmak index kullanımına engel olur. Örnek
SELECT * FROM Stok WHERE UPPER(StokAdi)=’KLAVYE’
Bu sorguda StokAdi kolonunda indeks olsa dahi indeks kullanılmaz ve FULL TABLE çalışır.

3-Sub queryleri FROM’a yazmak en hızlısıdır.
Kötü Örnek:
Select Count(*) FROM Stok S WHERE S.Fiyat < 2*(SELECT AVG(Fiyat) FROM StokHareket SH WHERE S.StokKod=SH.StokKod)

Düzeltilmiş Örnek:
Select Count(*) FROM Stok S ,(SELECT StokKod,AVG(Fiyat) Fiyat FROM STokHareket GROUP BY StokKod ) SH WHERE SH.StokKod=S.StokKod AND S.Fiyat<2 .sh.fiyat="" font="">

4- Eğer kesişen kayıt almadığından eminseniz UNION kullanmak yerine UNION ALL kullanmak daha hızlı olacaktır. Çünkü UNION ALL kesişen kayıtlarla uğraşarak vakit kaybetmez.

5- WHERE TO_CHAR(Fiyat) = :fiy yerine WHERE Fiyat = TO_NUMBER(:fiy)

6- ORDER BY,NOT IN,IN,UNION,DISTINCT gibi ifadeler kullanılırken dikkatli olmak lazım çünkü maliyeti çok yüksek.

7- Doğru yerde doğru indeks kullanımı çok önemlidir. Nerede indeks kullanmalı konusu biraz tecrübe ve biraz da sistemin genel yapısı ile alakalıdır.

8- Çok sık değişen ve küçük tablolarda indeks kullanmak çok da doğru değildir.

9- Genel olarak bütün mantığımız execution planı mümkün olduğu kadar az çalıştırtmak. Library Cache mizde yeteri kadar sorgu çalışmış olmalı. Eğer oracle veritabanı uzun süre çalıştıktan sonra kapanırsa açılınca performansı düşük olacaktır çünkü cache’deki bütün execution planlar silinmş olacaktır. O yüzden veritabanını kapatmamalıyız.

10-Materalized View kullanmak : Eğer sık değişen büyük veriniz varsa mutlaka kullanın.

11- Bir sorguda AND ile bağlanan iki filtre varsa WHERE ad=’KORAY’ AND Soyad=’adsds’ ikili index yapmak bu sorguyu daha çok performanslı çalıştırır.

12- Index kullanırken de ne tip index tipini doğru seçmek gerekir. İki tip indeksimiz var. B-tree ve Bitmap Index. Hangisini kullanacağımıza doğru karar vermek çok önemli. Veri tekrarı az ise B-Tree : Örnek kolon stok kodu , Tekrar eden kayıt çok ise Bitmap : Örnek Cinsiyet. Primary Key ve Unique Key kolonlar B-TREE indekse örnektir.

13- Partition Yapalım. Range,Hash,List ...vs Partitions.

14- Birden fazla CPU varsa paralel indexlerden faydalanalım.

15- Cluster kullanımı: Örneğin bir adı,soyadı,tel gibi bilgi çok fazla yerde kullanılıyorsa buları cluster haline getirebiliriz. CREATE CLUSTER ....

16- INDEX ORGANIZE TABLE : Bir tablo çok fazla değişmiyorsa tabloyu yaratırken indeksli yaratırız ve sıralı bir şekilde kaydeder.Çok hızlı select yavaş insert. Primary Key şart
CREATE TABLE ...... ORGANIZATOIN INDEX

17- Data dağılımları düzgün tablolar için bind variable kullanmak hız kazandırır.Çünkü ilk seferde sorgunun execution planı oluşturulur ve bir sonraki değeri farklı dahi olsa bir önceki plandan işlem görür. Örnek bölge kolonu için her bölgede aşağı yukarı 99 ile 101 kayıt varsa kullanmak mantıklı.

HINTS : Execution Planlara Hint ile baskıda bulunup değiştirtebiliriz ancak 11g ile artık buna çok fazla da ihtiyacımız kalmadı. Zaten en iyi maliyetli planı Oracle buluyor. Ancak spesific durumlarda ve test amaçlı kullanılabilir.

SELECT /*+ INDEX(t name_idx)*/ FROM tablo t where name=’erdem’

Oracle Enterprise Manager
Oracle’ın yönetimsel aktivitelerini takip etmek için kullanışlı bir uygulamadır. Bu bir Oracle servisidir ve uzun süre alan sql’leri akalamktan tutunda sqller ile ilgili tavsiye vermeye kadar bir çok işlevi vardır. Ayrıca incelenmesi gereken bir konudur.Biz sadece bazı küçük modüllerini inceleyeceğiz.

1- SQL Tunning Advisor : Oracle Enterprise menüsü üzerinden bu araca gelerek mevcut bir sorgunuza olumlu bir tavsiye alabilirsiniz. Örneğin şu tabloda şu kolonda index yap gibi. Mutlaka kullanılması tavsiye edilir.

2- SQL Access Advisor : Index ve Partition tavsiyesinde bulunur. Daha detaylı tavsiyeler içerir.

Global Temporary Table Oluşturmak

Oracle’ da tablo create ederken eğer  tablonun tipini belirtmez iseniz default olarak ilişkisel ve kalıcı olarak tablonuzu oluşturacaktır. Create table komutunuz içerisinde global temporary ifadesini kullanırsanız temporary segmentlerde tutacağınız geçici bir tablo create etmiş olursunuz.  Temporary tabloları normal tablolardan çok farklı olarak düşünmemek gerekir, bildiğimiz yöntem (create table ile ) ile create etmiş olduğunu tablolar üzerinde neler yapabiliyorsanız burada da yapabilirsiniz. Temporary olmasından kaynaklı olarak, temporary olarak create edilmiş bir tablo tüm sessionlar tarafından görülebilir ve sadece o session içerisinde varlığını idame ettirir, session sonlandığında temporary table’ da otomatik olarak drop olacaktır.

Temporary table’ ın sytanx’ ından biraz bahsedelim;

CREATE [ GLOBAL TEMPORARY ] TABLE [ schema. ]table

   [ (relational_properties) ]

   [ ON COMMIT { DELETE | PRESERVE } ROWS ]

   [ physical_properties ]

   [ table_properties ] ;

Aslında standartın dışında çokm fazla bişey yok, sadece create komutunun başında global temporary ifade var, sonunda iki farklı opsiyonu olan ON COMMIT PRESERVE ROWS veya ON COMMIT DELETE  ROWS  komutu yer alıyor. Bunların ne ifade ettiğinden bahsedeceğiz. Burada şunu hemen belirtelim temporary tablolar üzerinde yapılan DML işlemleri (yani özellikle update, insert, delete operasyonları) normal tablo üzerinde yapılan DML işlemlerine göre gayet hızlı olacaktır. Çünkü temp tablolar üzerinde yapılan işlemler (temp tablolar yapısı gereği nologging create edilirler) log üretmezler (yani burada yapılan işlemlere ait redo kaydı oluşmaz) dolayısıyla da daha hızlı transactionlar olacaktır. Baştada belirttiğim gibi bu tabloları çalışma mantığı açısından (session bazında olduğunu saymazsak) çok da bir fark yok aslında, temp tablolar üzerinde index create edebilirsiniz. Eğer bir yazılımcı olsaydım elimden geldiğince transactionım içerisinde temp tabloları kullanmaya çalışırdım. Eğer bir sürü join ile bir tablodan veri almak benim işimi çok uzatıyor ise, aynı transaction ile bu veriyi daha basit bir query ile temp bir tabloda oluşturup, sonrasında ihtiyacım varsa indexlerini create edip burdan kullanmak zaman ve performans açısından çok daha verimli olabilir.

Tablomuzu create ederken, create komutumuzun sonunda iki farklı komut eklememiz gerektiğinden bahsettik, şimdi bunu açıklayalım ;

ON COMMIT DELETE ROWS ; Bu komutu kullanırsanız oluşturduğunuz temp tablo içerisindeki verileriniz gönderdiğiniz her COMMIT’ de otomatik olarak silicek demektir.

ON COMMIT PRESERVE  ROWS ; Eğer bu komutu kullanırsanız açtığınız session boyunca temp tablo kalıcı olarak duracaktır, sessionı sonlandırdığınız anda tüm verilerinizi kaybedeceksiniz demektir.

Temp tabloyu create ederken bu iki komutu da kullanmaz iseniz tablonuz default olarak ON COMMIT DELETE ROWS  ile create edilecektir. (ki default opsiyonu budur)

Bunlarla ilgili bir örnek yapalım ;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

DROP TABLE ERDEM.TEST; Table dropped. CREATE GLOBAL TEMPORARY TABLE KAMIL.TEST (   AD     VARCHAR2(20 BYTE),   NO     NUMBER(5),   ADRES  VARCHAR2(30 BYTE) ) ON COMMIT DELETE ROWS ; Table created. insert into test values (‘erdem’,25,’istanbul’) ; 1 row created. insert into test select * from test ; 1 row created. select count(*) from test ;   COUNT(*)   ———-          2 1 row selected. commit; Commit complete. select count(*) from test ;   COUNT(*)   ———-          0 1 row selected.

Commit ile birlikte tüm data silinmiş oldu.  PRESERVE seçeneği create edilmiş olduğunda commit de herhangi bir data kaybı yaşanmıyor ancak session sonlandığında datalarda otomatik olarak siliniyor.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

DROP TABLE ERDEM.TEST; Table dropped. CREATE GLOBAL TEMPORARY TABLE KAMIL.TEST (   AD     VARCHAR2(20 BYTE),   NO     NUMBER(5),   ADRES  VARCHAR2(30 BYTE) ) ON COMMIT PRESERVE ROWS ; Table created. insert into test values (‘erdem’,25,’istanbul’) ; 1 row created. insert into test select * from test ; 1 row created. select count(*) from test ;   COUNT(*)   ———-          2 1 row selected. commit; Commit complete. select count(*) from test ;   COUNT(*)   ———-          2 1 row selected

Yine bir ekleme yapmak istiyorum. Temp tablolar parallel transactionlar ile select edilebilirler. (Bunu şu yüzden yazmak istedim, oracle 8i’ de bu özellik yoktu)

Bir sessionda aşağıdaki query’ i çalıştırıp;

1 2	select /*+ parallel (test,16) */  count(*) from test order by 1,2 desc

Diğer bir sessionda da aşağıdaki query ile parallel sessinoları select ettiğimde ;

QCSID           COUNT(SID)

125                 17

 125 nolu sid’ ye ait kendisi ile bilikte 16 tane parallel sesisonda bu sorguyu çalıştığını görebiliyorum. Aynı koşullar tüm DML işlemleri içinde geçerlidir.