Rastgele Yazılar : STARTUP ve SHUTDOWN

Rastgele Yazılar : CETURK Oracle Day ardından..

Rastgele Yazılar : USER TANIMLAMA(USER PRIVILEGES)

Rastgele Yazılar : Sisteme Connect Olan - Olmayan Kullanıcıları Tespit Etm...

Rastgele Yazılar : RAC(Real Application Cluster) Architecture(Mimarisi)

Rastgele Yazılar : Veri Madenciliği Gelişim Süreci ve Teknikleri

Rastgele Yazılar : Adım Adım Oracle BI

Rastgele Yazılar : Alınan Backupların Sağlamlığını Nasıl Test Edebiliriz

Rastgele Yazılar : UNION ALL Kullanımı

Rastgele Yazılar : ODI-Bağlantı Ayarları ve Repository Oluşturmak

• ANASAYFA
• PROGRAMLAMA

25 Mayıs 2011 Çarşamba

SQL Nedir, Parse Call ve Plan / Optimizer

Yazar : Ogan Ozdogan

SQL

SQL hakkında 2008 yılından kalma bir yazı yazmıştım. Buradan ulaşabilirsiniz. Bu yazımda biraz SQL'in ne olduğundan, hangi amaçlara hizmet ettiğinden bahsedeceğim ve ardından optimizer ile olan ilişkisinden, SQL planından ve SQL çalışma mantığından bahsedeceğim.

Bütün veritabanı operasyonları SQL sorguları aracılığı ile yapılmaktadır ve bir bilgisayar dilidir. Birçok insana göre bir programlama dili değildir fakat bana göre programlamaya yöneliktir. SQL bir çeşit arayüzdür ve veritabanındaki verilerle ve diğer bilgilerle konuşmamızı sağlar. Tıpkı iki farklı milliyetten olan insanın kendi aralarında İngilizce konuşarak anlaşması gibi. SQL kullanarak;

- Objeler yaratabilir, değiştirebilir ve düşürebiliriz.
- Tablolara satılar ekleyebilir, silebilir ya da güncelleyebiliriz.
- Verinin sorgulanmasında kullanabiliriz.
- Veritabanı objelerine olan erişmin kontrol edilmesini sağlayabiliriz.
- Veritabanının eş zamanlılığını ve tutarlılığını garanti altına alabiliriz.

SQL sorgularını bir C veya Java kodu içerisinde de kullanabilmek ve bu program aracılığı ile veritabanı ile konuşabilmek mümkündür.

 

SQL Veri Erişimi

Bütün programlama dillerini iki ana başlık altında toplayabilmek mümkündür. Dekleratif ve prosedürel diller. Dekleratif diller neyin yapılacağını, prosedürel diller ise işlerin nasıl yapılacağını anlatmak için kullanılırlar. SQL bir dekleratif dildir ve SQL derleyicisi (compiler) bir prosedür yaratarak, veritabanı üzerinde istenen işlerin tamamlanmasını sağlamaktadır.

SQL sorguları sayesinde mantıksal seviyede sorgulama yapılabilmektedir. Oldukça temel bir SQL sorgusunun örneğini verelim;

SQL> SELECT first_name, last_name
FROM hr.employees
WHERE first_name = 'John'
ORDER BY first_name, last_name;

WHERE koşuluna "predicate" yani yüklem denmektedir çünkü bir cümlenin yüklemi gibi çalışmaktadır. Veritabanı WHERE koşulunu sağlayan bütün verileri bize getirecektir. Bütün SQL sorguları optimizer'ı kullanırlar. Optimizer ise kullanıcı tarafından girilen sorguları daha efektif halde çalıştırmak için vardır. Bir Oracle veritabanı çeşitli yöntemlerle optimizer sonuçlarının iyileştirilmesini sağlamaktadır.

 

ANSI ve ISO standartları üzerine geliştirilen SQL, Oracle tarafından da kabul edilerek geliştirilmiştir. Bu standartlar her veritabanında aynıdır. ANSI ve ISO'nun SQL standartları ilişkisel bütün veritabanları tarafından kullanılmaktadır.

Oracle SQL'i, standart SQL'e göre çok daha fazla özelliklere sahiptir. Örneğin Oracle SQL'ine özel tablo birleştirme operasyonu ve işareti (+). SQL*Plus, SQL Developer ve Enterprise Manager ANSI/ISO standartlarında SQL sorgularını çalıştırmanızı sağlamaktadır.


Oracle veritabanındaki bütün operasyonların bir SQL sorgusu aracılığı ile gerçekleştirildiğini daha önce ifade etmiştim. Bir SQL ifadesi tanımlayıcılar, parametreler, argümanlar, isimler, veri tipleri ve SQL ayrılmış kelimeleri (reserved words) kullanmaktadır. Bir not olarak şunu ekleyebiliriz bu noktada; SQL ayrılmış kelimelerinin özel anlamı bulunmaktadır ve başka bir amaç için kullanılmamalıdır. Örneğin SELECT, WHERE gibi ayrılmış kelimeleri tablo adı olarak kullanmamalıyız.

SQL> SELECT first_name, last_name
FROM hr.employees;

Oracle SQL ifadeleri farklı biçimlerde konumlandırılmaktadır. Bunlar;

Bir DML ifadesinin çalıştırılması için Oracle veritabanı birden fazla aşama devreye sokmaktadır. Mesela bir tablo ya da indekse hangi yollardan ve sırayla erişileceği işleri değiştirebilir. Veritabanının, verilen SQL sorgusunu hangi yollardan çalıştıracağı ve istenilen veriyi getireceği, sorgu performansını oldukça etkilemekte ve sorgunun ne kadar hızlı cevap vereceğini değiştirmektedir. Bu sebeplerden dolayı optimizer'ın hesaplayacağı cost (maliyet) ve sizin sorgunuzun algortiması eşittir ne kadar hızlı sorgu yanıtıdır.

Cost Based Optimizer birkaç yol ile sorgunun nasıl çalıştırılacağına karar vermektedir. Bu koşullara sorgunun algortiması ve yapılandırılması, access path (erişim yolları), sistem veya obje istatistiklerinin güncelliği ve hint (ipucu). İpuçları ve erişim yolları hakkında daha detaylı konuşacağım. Herhangi bir SQL sorgusu için Oracle Optimizer aşağıdaki işlemleri gerçekleştirmektedir;

- Predicate (yüklem - WHERE koşulu) yapılandırması ve sorgunun bütün koşulları.
- Integrity constraint'lerinin (sınırlayıcı) gözden geçirilmesi.
- Optimizer hedefleri ve seçimleri.
- Erişim yollarının saptanması.
- Join sıralamasının hesaplanması.

Optimizer bu işlemleri yaparken her bir erişim yolu için bir cost, yani maliyet hesaplar. Bu maliyetlerin tamamına ve erişim yollarının sıralanmış haline "Execution plan" denmektedir. Çalıştırma planı bir Optimizer için yol haritasıdır ve SQL sorgularını nasıl ve hangi yollardan çalıştırması gerektiğini algılamaktadır. Alternatif erişim yolları ile oluşan çalıştırma planları arasından maliyeti en düşük olan plan geçerli kılınır ve ilgili SQL sorgusu çalıştırıldığı zaman o plan üzerinden çalıştırılır. Bir SQL sorgusunu çalıştırmadan da çalıştırma planını görebiliyorsunuz.

SQL> EXPLAIN PLAN FOR SELECT first_name, last_name FROM hr.employees;

Bir veritabanı yöneticisi Optimizer'ın çalışman mantığı üzerinde etkili olabilmektedir. Bunu yapabilmenin bir yolu SQL sorgusu içerisinde bulunan obje veya objelere ait sistemsel istatistiklerin toplanmasıdır. İstatistikler güncel olduğu zaman ilgili objeler hakkında Oracle veritabanı net bir bilgiye sahip olacak ve Optimizer'ın tavırlarında bir değişiklik göreceksiniz. Örneğin bir tablonuz var ve içerisinde bir milyon satır bulunmakta. İstatistikleri günceldir. Tablodaki 1 kayıt haricinde bütün satırları sildiğiniz ve COMMIT ettiğiniz zaman Oracle bu işlemi yapacaktır ancak Optimizer'ın haberi olmayacaktır. Optimizer'ın baktığı tablodaki istatistiklerin artık eski sayıldığını düşünürseniz, Optimizer tabloda hala bir milyon kayıt olduğunu zannedecektir. Bu durum çalışma mantığı üzerinde yanlışlıklara neden olacak, belki de FTS (Full Table Scan) yapılması gerekirken hala bir milyon kayıt varmış gibi indeks taraması yapılacaktır. İşte bu durumda performans ciddi olarak etkilenebilecektir.

Optimizer'ın hedefini aşağıdaki şekillerle değiştirebilirsiniz (örnektir);

- ALL_ROWS hint'ini kullanarak son kullanıcı uygulaması için en son satıra, olabilecek en hızlı şekilde gidilmesi sağlanır.
- FIRST_ROWS hint'ini kullanarak Optimizer'dan son kullanıcı uygulamasına ilk satırı, olabilecek en hızlı şekilde getirmesi istenir.


Bir Optimizer'ın bileşenleri varmadan önce girdisi yapılan bileşene "parsed query" yani parse edilmiş SQL sorgusu denmektedir. İlerleyen bölümlerde parsed sorgudan bahsediyor olacağım. Optimizer bileşenlerini aşağıdaki diyagramda görebilirsiniz;


Optimizer sırasıyla aşağıdaki aşamalardan geçerek ve bileşenlerini kullanarak işini yapmaktadır;

1) Optimizer parser'dan geçmiş parsed SQL sorgusunu kabul eder ve hint ve erişim yollarının yardımı ile de potansiyel planları oluşturmaya çalışır.
2) Data Dictionary'daki istatistikler baz alınarak bütün potansiyel SQL plan'larının maliyetleri çıkartılır.
3) Optimizer en düşük olan maliyetli planı bulur ve kullanır.

Query Transformer: Parse edilmiş sorgunun yapısının değiştirilmesine ihtiyaç varsa ve bu faydalı bir çalışma olarak kabul edilmekteyse Query Transformer sorgunun yapısını değiştirebilir. Bunun amacı daha iyi execution plan çıkartmaktır.
Estimator: Bu bileşenin görevi maliyet (cost) hesaplamaktır. Şimdi bu maliyetlerin nasıl hesaplandığı ve nelere bakıldığını anlatacağım.
a) Seçilebilirlik; Yüklemler kontrol edilir ve sorgunun neleri talep ettiği gözden geçirilir. WHERE koşulundakiler ölçülmektedir.
b) Kardinalite; Satır kümesinden gelen satırların sayısını simgeler.
c) Maliyet (Cost); Cost Based Optimizer'ın en önemli noktalarından birisi olan maliyeti, yapılan işin ya da kaynağın miktarını ölçmek için kullanıyoruz. Disk I/O, CPU kullanımı ve bellek tüketimi işin üniteleridir ve maliyeti oluşturur.
Plan Generator: Bir diğer bileşen olan plan oluşturucu girilen sorguyla ilgili olarak birden fazla plan çıkartmaya çalışmaktadır. Ardından en düşük olanı seçmektedir. Optimizer her bir alt sorgu için (subquery) ayrı alt planlar oluşturmaktadır. Başka tipte plan oluşturabilmek için farklı erişim yolları, join operasyonları ve join sıralamaları gerçekleştirmektedir. Bu farklılıklarda günün sonunda farklı birer plan olarak çıkartılır ve maliyetlerine bakılır.

Erişim yolları veritbanındaki veriye nasıl ulaşılması gerektiğini gösteren yol haritalarıdır. Örnek olarak bir sorgunun indeks üzerinden getirilmesi, indeks üzerinden ilerlemeyen sorguya göre farklıdır. Bir genelleme olarak küçük bloklardaki satırlara index erişim yolu ile gitmek daha mantıklıyken, büyük bir tablodaki bütün verilere ulaşırken tablonun hepsini okumak daha mantıklı olabilir. Bir Oracle veritabanı, veriye ulaşırken farklı erişim yolları kullanmaktadır ve aşağıda listelenmektedir;

1) Full Table Scans: Bu tipte aramlarada bütün tablonun satırları teker teker okunur ve yüklemdeki isteğe göre filtrelenir. Veritabanı bir segmentteki bütün blokları sırasıyla okur, buna high water mark altında kalanlar da dahildir.
2) Rowid Scans: Rowid bir satır için adres bilgisini içermektedir ve blok içerisinde nerede bulunduğunu göstermektedir. Veritabanı ilk olarak seçilen satırların rowid'sine ulaşır ve bunu ya WHERE koşulundaki isteğe göre ya da bir indeks range scan ile yapabilir. Ardından seçilen her bir satırı rowid'lerinden getirir.
3) Index Scans: SQL sorgusu tarafından istenen değerlerin bir indeks araması ile getirilmesi işlemidir. Eğer bir ifade içerisinde yalnızca indeksin bulunduğu sütunlar talep edilmiş ise bir indeks taraması ile direkt olarak bu indeks içerisindeki değerlere ulaşılmaktadır.
4) Cluster Scans: Bir indeksli tablo cluster'dan veri erişimi sağlayabilmek için kullanılan erişim yoludur. Rowid'leri bulunarak yapılan bir taramadır.
5) Hash Scans: Bir hash cluster'dan veri erişimi sağlayabilmek için kullanılan erişim yoludur.

Cost Based Optimizer geçerli ve olabilecek erişim yollarından, tahmin edilen en düşük maliyete sahip olan plan kombinasyonunu seçer ve uygulamaya sokar.


Bir Oracle veritabanındaki objelerin detaylarının bütününe Optimizer istatistikleri denmektedir. Bildiğimiz klasik normal dağılım, regresyon, korelasyon gibi istatistikler terimlerle bir ilgili bulunmadığını düşünmekteyim. Oracle iş içleri gereği bir istatistiki hesaplamanın yapılıp yapılmadığını bilmiyorum ancak dokümantasyona göre "istatistiksel olarak doğru" ve "dağılım" kelimeleri geçmektedir. Bu bağlamda ne tipte bir istatistiki operasyonun gerçekleştirildiğini net olarak kestiremiyorum fakat işin en temelinde tabloya ait "doğru, istatistiki bilgi" ifadesinin yer aldığını söyleyebilirim. Basit bir normal dağılımda Z veya T değerine bakılıyorsa bile konumuzun Oracle veritabanı yönetimi olduğunu hatırlatıyorum ve daha ileri yazılarımda muhasebe, istatistik ya da işletme ekonomisi gibi konulardan bahsetmeyi umuyorum. Hazır konu biraz Oracle'dan sapmışken, işletmeyle ilgili konularda da benimle iletişim halinde kalabilirsiniz. Yardımcı olmaktan mutluluk duyarım.

Bir Optimizer istatistiği aşağıdaki istatistiki bilgileri içermektedir;

1) Tablo İstatistikleri: Ortalama satır uzunluğu, toplam satır sayısı veya blok sayısı gibi obje seviyesi istatistiklerini içermektedir.
2) Sütun İstatistikleri: Veri dağılımı içerisindeki NULL ve tekil değerlerin istatistiğini içermektedir.
3) İndeks İstatistikleri: İndeks seviyelerini ve indeks yaprak sayılarını tutan istatistiklerdir.
4) Sistem İstatistikleri: CPU ve I/O utilizasyon istatistiklerini içermektedir.

Bir Oracle veritabanı otomatik olarak sistem ve bütün veritabanına ait objelerin istatistiklerini toplayabilmektedir. Bunu, yanılmıyorsam 10g ile aramıza katılan GATHER_STATS_JOB aracılığı ile sağlamaktadır. Bir önceki yazımda DBMS_STATS paketinden bahsetmiştim. DBMS_STATS paketini kullanarak elle istatistiklerin toplanmasını da sağlayabilirsiniz. Optimizer optimizasyonu bu istatistikler baz alınarak yapılır ve normal şartlar altında data dictionary içerisinde barındırılır. Bu istatistikleri lütfen performans istatistikleri ile karıştırmayınız (dinamik performans görüntüleri içerisinde tutulan).


Benim yazılarımı devamlı okuyan okurlarım bilirler, teknik terimleri bile Türkçe'ye çevirmeye çalışırım çünkü benim size "hint" demem her ne kadar bir şeyleri açıklasa da Türkçe'sini bilmenin ve kullanmanın faydalı olduğuna inanıyorum. Bu yüzden bu andan itibaren hint yerine ipucu kelimesini kullanıyor olacağım. Oracle'da çok merak edilen konulardan birisi olan optimizer ipuçları, Cost Based Optimizer'ın davranışlarına emir vermek demiktir. "Bak, sen bunu yanlış biliyorsun, bu şekilde davranmalısın" demenin Oracle'cası veya SQL sorgu dilinde olanıdır. İpuçları bir yorumdur ve Optimizer'a bir eğitmen gibi davranmantakdır. Optimizer zaten en düşük olan maliyeti seçmektedir ancak kimi zaman seçtiği maliyet "kendine göre düşüktür" ve bizim veri hakkında daha fazla bilgimiz olduğu için Optimizer'ı yönlendirmek isteyebiliriz. Uygulamayı tasarlayan geliştirici arkadaşların sıklıkla başvurduğu bir yöntemdir fakat guru diye tabir edebileceğim insanların pek hoşlanmadığı bir çözümdür. Bunun nedeni ise ipuçları dinamik değildir ve Optimizer'ın karakteristiği gereği değiştirilmeleri gerekebilir. Bir ipucu o an için iyi sonuçlar verirken, aradan geçen bir aydan sonra sorguyu inanılmaz derecede yavaşlatabilir ya da çok fazla kaynak tüketmesine neden olabilir. Bu bağlamda ipuçlarını kullanmadan önce istatistiklerin toplanması, verinin detaylı analizin yapılması, sorgunun tune edilmesi ve algoritmasının gözden geçirilmesi geremektedir.

Aşağıda bir Optimizer ipucunun kullanımı göstermekteyim;