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.
SQL Standartları
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.
SQL İfadelerine Genel Bakış
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;
Data Definition Language (DDL)
Veritabanının yapısını değiştiren DDL komutlarıdır. DDL ile yapabileceğiniz işlemler;
- CREATE, DROP, ALTER komutları ile veritabanı objelerinin yapılarının değiştirilmesi. Kullanıcılar üzerindeki işlemler de dahildir.
- TRUNCATE komutu ile objelerin kendisini kaldırmadan içerisinde bütün verilerin silinmesinin sağlanması.
SQL> TRUNCATE TABLE hr.employees;
SQL> CREATE USER ogan IDENTIFIED BY deneme;
NOT: TRUNCATE komutu DELETE komutunun yarattığı kadar undo verisi yaratmaz aksine hiç undo verisi yaratmaz. DELETE komutundan daha hızlıdır ve DELETE trigger'larının tetiklenmesini gerçekleştirmez.
- GRANT, REVOKE yetkilerinin kullanılması.
- AUDIT ve NOAUDIT komutları ile denetleme mekanizmalarının devreye alınması, devreden çıkartılması.
- COMMENT komutu ile data dictionary'e yorum eklemek için kullanılır.
DDL komutları ile objeye erişen uygulamanın yapısını değiştirmeden objenin özelliklerini değiştirebiliyorsunuz. Aynı şekilde veritabanındaki kullanıcılar etkilenmeden yine objenin özelliklerini değiştirebiliyorsunuz.
SQL> GRANT SELECT ON hr.employees TO ogan WITH GRANT OPTION;
SQL> INSERT INTO hr.employees (last_name, first_name) VALUES ('ozdogan','ogan');
SQL> REVOKE SELECT ANY DICTIONARY FROM ogan;
SQL> DROP TABLE hr.employees;
DDL komutlarından sonra gizli COMMIT ya da ROLLBACK komutu çalıştırılmaktadır ve DDL komutlarından önce de yine gizli COMMIT koşulmaktadır. DDL komutlarını bir SAVEPOINT bloğunun içerisine sokarsanız dikkatli olmanız gerekir zira DML komutlarından sonra koşacağınız bir DDL komutu ile gizli COMMIT koşulacağından geride kalan işlemlerin tamamı, aynı transaction içerisinde COMMIT edilmiş sayılırlar, buraya dikkat.
Data Manipulation Language (DML)
DML komutlarını varolan veritabanı objelerinin sahip olduğu verileri manipüle ederken kullanmaktayız. DDL komutları bize veritabanının yapısının değiştirilmesinin şansını sunarken, DML içerğin değiştirilmesinin şansını sunmaktadır. Örneğin ALTER TABLE bir DDL komutu olup objenin özelliğini ve yapısını değiştirirken, INSERT komutu objenin içerisine veri eklemeye yaramaktadır.
Genelde kullanılan DML komutları arasında;
- SELECT komutunu kullanarak tablo ya da görüntü (view) veritabanı objelerinden bilgi çekmek için kullanılır.
- INSERT komutu ile bir görüntü ya da tabloya veri girişinde bulunabilirsiniz.
- UPDATE DML komutunu kullanarak varolan sütun değerlerinin değiştirilmesini sağlayabilirsiniz.
- MERGE INTO komutunun sağladığı update veya insert özelliklerinden faydalanmak.
- DELETE ile verilerin silinmesinin sağlanması.
- EXPLAIN PLAN ile SQL execution plan (çalıştırma planı) çıkartılması.
- LOCK TABLE ile bir tablo üzerinde kilit almak için kullanılmaktadır.
SQL> DELETE hr.employees
WHERE last_name = 'King';
SQL> UPDATE hr.employees
SET salary = 10000
WHERE employee_id = 100;
Birden çok DML'in arka arkaya girilmesi ile bir transaction oluşmaktadır. Transaction'la ilgili yazdığım yazıya buradan ulaşabilirsiniz. DDL komutlarında olduğu gibi hiçbir DML komutu gizli COMMIT veya ROLLBACK çalıştırmamaktadır. Genelde verilen örnek banka işlemleridir. Hesaptan almak ve karşıya aktarmak bir transaction'dır ve işlem bittikten sonra COMMIT gönderilmektedir. Bu DML bloğu sık kullanılan bir örnektir.
Transaction Control Statements
Transaction kontrol ifadeleri ile DML ifadelerinin kontrol edilmesini sağlayabilmekteyiz;
- COMMIT komutu ile DML ifadelerinin sağlama alınması, kesinleştirilmesi sağlanmaktadır. COMMIT komutu ile bir DML transaction'ı sonlandırılmaktadır.
- ROLLBACK ya da ROLLBACK TO SAVEPOINT komutu ile bir transaction içerisinde geriye gidilebilir ya da bütün bir transaction'dan vazgeçilebilir.
- SAVEPOINT komutuyla ROLLBACK yapabileceğiniz noktaları tanımayabiliriz.
- SET TRANSACTION komutu ile başlattığımız transaction'ın özelliklerini değiştirebiliriz.
- SET CONSTRAINT ile bir kısıtlayıcının (constraint) özelliklerini değiştirebiliriz.
SQL> SET TRANSACTION NAME 'ogan_transaction';
SQL> SAVEPOINT ogan_savepoint_1;
SQL> COMMIT COMMENT 'ogan_guncelleme';
Session Control Statements
Bir session (bağlantı) hakkındaki bilgileri değiştirdiğimiz, güncellediğimiz SQL ifade tipi session kontrol ifadeleridir.
- ALTER SESSION komutu ile SQL trace özelliğini açıp kapatabiliriz.
- SET ROLE ile geçerli bağlantı için rolleri açıp kapatabiliriz.
SQL> ALTER SESION SET SQL_TRACE = TRUE;
SQL> SET ROLE ALL EXCEPT ogan_role_man;
System Control Statements
Veritabanı instance'ının özelliklerinin değiştirilmesi için kullanılmaktadır. Bir tane sistem kontrol ifadesi vardır o da ALTER SYSTEM'dir.
SQL> ALTER SYSTEM SWITCH LOGFILE; --> Redolog grup geçisini sağlar.
SQL> ALTER SYSTEM KILL SESSION '100,203' --> 100 session id'li, 203 serial numaralı bağlantıyı öldürür.
Embedded SQL Statements
Gömülü SQL ifadeleri DDL, DML için değil, prosedürel dil programı içerisinde kullanılır.
- DECLARE CURSOR, OPEN, CLOSE ile Cursor'ların tanımlanması, tahsis edilmesi ya da bırakılması için kullanılır.
- DECLARE DATABASE, CONNECT komutları ile bağlanılacak veritabanının belirlenmesi.
- DECLARE komutu ile değişkenlerin atanması.
- DECSRIBE ya da DESC (synonym) ile objelerin tanımlarının izlenmesi.
- FETCH ile veritabanından veri alınmasını sağlayabiliriz.
Optimizer'a Genel Bakış
Geçmişte yazdığım hiçbir optimizer yazısı olmadığını gördüm ve açıkçası biraz üzüldüm. Çok ciddi ve önemli bir konu olduğunu düşünüyorum.
Optimizer ya da diğer isimleri ile query optimizer veya cost based optimizer bir veritabanı parçasıdır ve SQL ifadelerinin nasıl koşulması gerektiğinin yolunun çıkartılmasını sağlar. Bütün SQL ifadeleri optimizer'ı kullanmaktadır. "Cost" adı verilen bir miktar hesaplanmakta ve SQL ifadeleri bu yönden koşturulmaktadır. Cost dediğimiz öğenin nasıl hesaplandığını açıklayacağım.
Optimizer Kullanımı
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.
Optimizer Bileşenleri
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.
Access Paths (Erişim Yolları)
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.
Optimizer İstatistikleri
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).
Optimizer Hints
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;
SELECT /*+ FIRST_ROWS(25) */ employee_id, department_id
FROM hr.employees
WHERE department_id > 50;
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 182
| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 26 | 182
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | |
Yüklem bilgisi olarak departman id'leri 50'den büyük olan, hr kullanıcısına ait employees tablosundan, employee_id ve department_id bilgilerinin ilk 25 satırını istediğimizi gösterdik. Koyu olarak işaretlediğim alana hint ya da ipucu denmektedir. İpucunu kaldırarak yeniden deneyebilirsiniz. Ben çıktısını gösteriyor olacağım;
SELECT employee_id, department_id
FROM hr.employees
WHERE department_id > 50;
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cos
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 350 |
|* 1 | VIEW | index$_join$_001 | 50 | 350 |
|* 2 | HASH JOIN | | | |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 50 | 350 |
| 4 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 50 | 350 |
SQL İşlemeye Genel Bakış
Bu bölümde bir Oracle veritabanının SQL sorgularını nasıl işlediğine bakacağız. Bu işlemeye yukarıda anlattığım Cost Based Optimizer safhası da dahildir ve bir bütünü kapsar.
SQL İşlemenin Aşamaları
1) SQL Parsing: Yukarıdaki diyagramda gözüktüğü üzere SQL işlemesinin ilk aşaması SQL sorgusunun parse edilmesidir. Parse etmeyi şöyle açıklayabilirim. Bir uygulamaya ya da bir operasyona anlamlı gelecek şekle getirmektir. Binary bir dosyayı parse ederek CSV formatında bir dosyaya çevirirseniz, sizin için anlamlı bir hale gelecektir ancak öncelikle parser'ın binary dosyanın dilinden anlıyor olabilmesi ve sizin elinizde bunu tercüme edecek bir dokümantasyonun bulunması gerekmektedir. Oracle'ın yaptığıda aslında bundan çok farklı değildir. Oracle da kendisi için anlamlı gelecek bir format oluşturmaya çalışır fakat sistematiği biraz farklıdır.
Bir uygulama bir SQL ifadesi çalıştırdığı zaman bir de "parse call" yapar, yani bir parse isteğinde bulunur ve veritabanından bu parse işlemini istediğini söyler. Parse istediği (parse call) veritabanında bir "cursor" yaratır ve SQL ifadesinin parse edilmiş halini barındırır. Bu ifade "Private SQL Area" dediğimiz session'a özel bir alanda saklanır. Yaratılan cursor ve özel SQL alanı PGA içerisinde yer almaktadır. PGA içerisinde yer aldığından dolayı diğer session'larla (bağlantı) paylaşılmamaktadır.
Bir parse call'da 3 işlem yapılmaktadır ve aklınızda daha iyi kalabilmesi için 3S işlemi olarak ifade edeceğim. Bunlar;
a) Syntax Check: Bir Oracle veritabanı her SQL ifadesinin sentaksının veya diğer bir ifade ile söz diziminin doğru olduğunu kontrol etmek zorundadır. Eğer söz dizimi doğru yazılmış ise sonrası gelebilir. Aksi halde hata basmak durumundadır. Örnek;
SQL> SELECT * FORM employees;
SELECT * FORM employees
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
b) Semantic Check: Bu kontrol sırasında FROM kısmında kullandığımız tablonun adının doğru olup olmadığı, doğru ise SELECT kısmındaki sütunların, ilgili tabloda olup olmadığı kontrol edilmektedir. Örnek;
SQL> SELECT * FROM nonexistent_table;
SELECT * FROM nonexistent_table
*
ERROR at line 1:
ORA-00942: table or view does not exist
c) Shared Pool Check: Parse aşamasındayken shared pool alanı kontrol edilir. Kaynak bakımından çok yüklü olan parse aşamasının atlanması için shared pool kontrol edilir ve her bir SQL ifadesi için bir hash değer çıkartır. Bu hash değere SQL_ID denir ve V$SQL.SQL_ID olarak kontrol edilebilir. Ne zaman bir SQL ifadesinin parse call'u veritabanına ulaşırsa, veritabanı shared SQL area olarak adlandırılan bir alanda daha önce parse edilmiş bir SQL planı olup olmadığını kontrol eder. Bunu da az önce bahsettiğim SQL_ID aracılığı ile sağlar.
Bir parse operasyonu iki aşamaya ayrılarak devam etmektedir;
1) Hard Parse: Eğer bir Oracle veritabanı daha önceki kodu yeniden kullanamazsa bu durumda uygulama kodu için yeni bir versiyon çıkartması gerekmektedir. Bu operasyonun adı hard parse'tır ya da diğer bir ismi ile "library cache miss"dir. Bu arada çok önemli bir not, her DDL komutu için hard parse yapılması zorunludur ve yapılır. Hard parse işlemi sırasında data dictionary ve library cache defalarca taranır ve veritabanı tarafından erişilir. Bir veritabanı bu alanlara eriştiği zaman "latch" ismini verdiğimiz ve library cache ya da data dictionary üzerinde oluşan "lock" yani kilitler oluşmaktadır. Satır seviyesinde bir kilit değil, mantıksal bir kilittir. Latch olarak adlandırılan bu kilit mekanizmaları eş zamanlılık çatışmalarına ve sorgularda yavaşlamalara neden olmaktadır. Kaynaklar paylaşılamıyor olabilir.
2) Soft Parse: Soft parse bir hard parse işlemi değildir. Diğer bir adı ile "library cache hit"tir. Eğer girilen SQL ifadesinin parse call sırasında shared pool içerisinde yeniden kullanılabilecek durumda olan bir planı varsa bu durumda soft parse gerçekleşir. Genel olarak soft parse > hard parse olması tercih edilebilen bir durumdur.
Yukarıdaki diyagramda bir dedicated sunucu için UPDATE işleminin gerçekleştirdiği shared pool taramasını görmektesiniz. Private SQL Area'da oluşan cursor ve SQL_ID, library cache üzerinde aranıyor. Bir eşleşme sağlanıyor ve soft parse aşamasına geçiliyor. Bir önemli not vermek istiyorum, SQL ifadelerinin aynı olması, her kullanıcı için soft parse işlemi gerçekleştirileceğini garanti altına almaz. Şema seviyesindeki obje isim farklılıklarından dolayı SQL ifadeniz hard parse'a geçebilir. Bütün şartların, kullanıcı ve SQL sorgusu dahil, aynı olduğu bir ortamda bile ortamsal nedenlerden dolayı hard parse işlemi gerçekleşebilir. Bir örnek;
ALTER SYSTEM FLUSH SHARED_POOL;
SELECT * FROM my_table; --> #1
ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS;
SELECT * FROM my_table; --> #2
ALTER SESSION SET SQL_TRACE=TRUE;
SELECT * FROM my_table; --> #3
Yukarıdaki örnekte SELECT ifadesi üç farklı optimizer ve veritabanı ortamında gerçekleşmektedir. SELECT ifadeleri aynı olsa dahi ortam farklı olduğu için her bir sorguda yine farklı SQL_ID'leri ve cursor'lar oluşacak, hard parse gerçekleşecektir.
SQL Optimizasyonu
Bir sorgunun optimizasyonu, Optimizer'ın seçtiği en düşük maliyetli sorgu planı olarak taınmlanmaktadır. Veritabanı, bir SQL sorgusunun optimizasyonunu, sorgu içerisinde bulunan objelerin istatistiklerine göre yapmaktadır. Bunu yaparken de data dictionary'yi kullanmaktadır.
Bir Oracle veritabanı ilk defa çalıştırılan bütün DML komutları için mecburen hard parse yapmak durumunda kalabilir. DDL komutları hiçbir zaman optimize edilmez ne var ki içerisinde bir DML içeriyorsa (alt sorgu olarak), bu durumda DML optimize edilebilir.
SQL Satır Kaynağı Yaratılması
"Row Source Generator" bir optimal execution plan kabul eder. Bir satır kaynağı execution plan içerisinde yer alan aşamalardan gelen kontrol yapısını içermektedir. Satır kaynağı bir tablo, görüntü ya da grup veya join operasyonundan gelen sonuç olabilir.
Satır kaynak yaratıcısı bir satır kaynak ağacı oluşturur ve içerisinden birden fazla satır kaynağı barındırır. Bu satır kaynakları aşağıdaki bilgileri göstermektedir;
- SQL ifadesindeki tabloların sıralaması.
- SQL ifadesindeki her bir tablonun erişim yolunun ne olduğu.
- SQL ifadesindeki tabloların etkilendiği join operasyonları ve metodu.
- Filter, sort ya da toplama gibi veri manipülasyon işlemleri.
Aşağıdaki örnek AUTOTRACE açık durumda görülen bir execution plan'ını içermektedir. Bu sorgunun execution plan'ı aynı zaman satır kaynak yaratıcısının da çıktısıdır;
SELECT e.last_name, j.job_title, d.department_name
FROM hr.employees e, hr.departments d, hr.jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
AND e.last_name LIKE 'A%' ;
Execution Plan
----------------------------------------------------------
Plan hash value: 975837011
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 189 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 3 | 189 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 3 | 141 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 60 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_NAME_IX | 3 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | JOBS | 19 | 513 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
2 - access("E"."JOB_ID"="J"."JOB_ID")
4 - access("E"."LAST_NAME" LIKE 'A%')
filter("E"."LAST_NAME" LIKE 'A%')
Gördüğünüz gibi predicate (yüklem ya da WHERE koşulu) içerisinde bulunan 3 öğe "Predicate Information" kısmında gösterildi ve filter operasyonu gerçekleşti. Bu aşamalarda satır kaynaklarını teker teker görmekteyiz.
SQL Çalıştırılması
Bir SQL ifadesinin çalıştırılması (execution) sırasında satır kaynak yaratıcısının oluşturduğu ağaç yapısı üzerinden geçilir ve ağaç yapısı içerisindeki bütün satır kaynakları teker teker çalıştırılır. Bu aşama bir DML işlemesi sırasındaki tek zorunlu aşamadır.
"Execution Tree" ya da parse ağacına ait diyagramı aşağıda gösteriyorum;
Yukarıdaki diyagramda numaralandırılmış her aşamaya satır kaynağı, ağaca ise satır kaynak ağacı denir. Bu ağac satır kaynak yaratıcısı tarafından oluşturulmuştur. Çalıştırmanın sırası görüntünün aksine aşağıdan yukarıya doğru ilerlemektedir.
Diyagramda siyahla gösterilen aşamalar veritabanındaki bir objeden fiziksel olarak verinin elde edildiğini göstermektedir. Bu aşamalar erişim yoludur.
- 6ncı aşamada departments tablosundaki bütün satırlara full table scan yapılarak erişilmektedir.
- 5nci aşama ise jobs tablosundan verileri yine full table scan ile getirmektedir.
- 4ncü aşamada emp_name_ix indeksi kullanılarak, A ile başlayan bütün rowid'ler bulunmaktadır.
- 3ncü aşamada employees tablosundan satırlar getirilmektedir ve bu satırlar 4ncü aşamadaki rowid'lerden gelenlerdir.
- 2nci aşamada bir hash join yani yığın birleşme yapılmaktadır. Burada 3 ve 5nci aşamalardan gelen satır kaynakları kabul edilmektedir ve 1nci aşamaya gönderilmektedir. Daha önce bahsettiğim gibi bu tipte olan birleştirmeler de birer satır kaynağıdır.
- 1nci aşama başka bir hash join yapmaktadır ve 2 ve 6ncı satır kaynaklarından gelenleri kabul etmektedir.
Bütün bu çalıştırma safhaları içerisinde eğer SQL ifadesinde istenen veri seti bellekte bulunmuyorsa veritabanı diskten fiziksel okumalar yapmaktadır. Bununla birlikte gerekli olan durumlarda latch ya da lock olarak tabir edilen kilit mekanizmaları da devreye girmektedir. SQL çalıştırmasının ve SQL ifadesinin en son aşamasında ise ilk başta açılan cursor kapatılır.
Oracle DML İşlemlerini Nasıl İşliyor?
Genelde bütün DML işlemleri bir sorgu bileşeni içermektedir. Bir sorguda, cursor'ın çalıştırılmasıyla birlikte ortaya çıkan sorgular, veri seti olarak adlandırılan (result set) bir satırlar kümesinde barındırılır.
Genel olarak bir veritabanında sorgunun ne kadar satır getireceği, en son satıra varılıncaya kadar bilinemez. Oracle veritabanı verileri bir fetch mekanizması ile getirir ve bunlara "fetch calls" adı verilmektedir. Bazı sorgular için veritabanı ilk satırı mümkün olduğunca hızlı getirir, buna karşın bazıları için bütün veri setini daha ilk satır için fetch call yapmadan oluşturabilir.
Oracle DDL İşlemlerini Nasıl İşliyor?
Oracle veritabanı DDL komutlarını DML komutlarından farklı işleme almaktadır. Örnek olarak bir CREATE TABLE komutu çalıştırdığınız zaman veritabanı, DML'de olduğu gibi bir optimizasyon yapmaz. Bunun yerine Oracle veritabanı DDL komutunu direkt olarak parse eder ve girilen komutu işleme alır.
Genel olarak Oracle veritabanı bir DDL komutunu çalıştırmak için mükerrer SQL işlemleri çağırmaktadır. Örnek;
SQL> CREATE TABLE ogan_deneme (kullanici_adi VARCHAR2);
Oracle veritabanı her ne kadar siz tek bir DDL girmiş olsanız ve amacınız sadece basit bir tablo yaratmak olsa bile, arka planda bir düzine sorgu çalıştırmaktadır. Bu mükerrer SQL sorguları;
- CREATE TABLE komutundan hemen önce COMMIT emrinin verilmesi.
- CREATE TABLE komutunun çalışması için kullanıcı haklarının geçerli olup olmadığının kontrolünün gerçekleştirilmesi.
- Yaratılacak tablonun hangi tablespace üzerinde bulunacağının kesinleştirilmesi.
- Tablespace kotasının aşılmadığının ya da var olduğunun kontrolünün sağlanması.
- İlgili şemada bulunan diğer objelerle isim çakışmasının olmadığının anlaşılması.
- Data Dictionary içerisine tablo ve tablonun sütunları ile ilgili bilgilerin girilmesi.
- DDL komutu başarıyla sonlanırsa COMMIT aksi halde ROLLBACK operasyonunun gerçekleştirilmesi.
İyi çalışmalar dilerim.
Ogan