İçindekiler

Bir veritabanı sisteminde SQL çalıştırdığımız zaman arka planda nelerin olduğunu bilmek önemli. Bilindiği takdirde örneğin “bind variable kullanımı”, “bulk işlemler” vb. durumlarda Oracle tarafında olan işlemlerin nasıl etkilendiği farkında olunacak ve buna göre bir yaklaşım belirlemek mümkün olacaktır.

SQL işlemleri 2 ana başlık altında toplanır :

  • DDL(Data Definition Language) : “Data Dictionary” üzerinde değişikliğe sebeb olan create, drop, alter gibi işlemlerdir.
  • DML(Data Manipulation Language) : Veriye ulaşmak ya da değiştirmek amacıyla yapılan işlemlerdir.(Select, update,delete…vs.)

SQL Processing işlemi parsing, optimization, row source generation ve execution adımlarından oluşur. Sql ifadesine göre bazı adımlar atlanabilir.

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 sql ifadesi çalıştırılacağı zaman veritabanı üzerinde parse call talebi ile veritabanı üzerinde cursor açılır. Bu cursor session bazlı açılır ve private SQL area üzerinde tutulur. Cursor ve private SQL area program global area (PGA) içindedir. Parse call işlemi 3 aşamadan oluşur.

  • Syntax check
  • Semantic check
  • Shared pool check

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. . örneğin from yerine form yazılan bir sorgu syntax hatası verecektir.

SQL> SELECT * FORM employees;
SELECT * FORM employees
         *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Semantic Check

SQL de belirtilen objelere ulaşılabiliyor mu? Sorguda belirtilen tablolar var mı? Gene sorguda belirtilen kolonlar tabloda var mı? Mesela 2 tabloda aynı isimli kolon varsa ve bu kolon sorgumuzda isteniyorsa hangi tablo için getirilecek? Bu gibi durumların kontrolü semantic check kısmında yapılır. Örneğin varolmayan bir tabloyu sorguladığımızda aşağıdaki gibi hata alırız.

SQL> SELECT * FROM nonexistent_table;
SELECT * FROM nonexistent_table
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Shared Pool Check

“Shared Pool” , SGA da bulunan önceden çalıştırılmış SQL işlemlerinin , PL/SQL kodlarının , “dictionary cache” bilgilerinin ve tekrar kullanılabilir herşeyin tüm oturumlar (session) için tutulduğu memory (bellek) alanıdır. 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.

Peki yazılan SQL in önceden kullanılmış olduğu nasıl anlaşılır? Oracle çalışan her SQL işlemini bir Hash fonksiyonundan geçirir. Oluşan hash değeri ile bellekte bir yerde tutar. 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. Hash değeri oluşturken örneğin “select * from emp”  ile “select * from EMP” nin farklı SQL olarak adlandırılacağını hatırlatmakta fayda var. Yani yazılan SQL lerdeki büyük-küçük harf kullanımı bile önemlidir.

NOT : Bir SQL ifadesinin shared pool’da birden fazla planı olabilir. Genellikle, her plan farklı bir hash value’ye sahiptir. Aynı SQL ID’de birden fazla plan hash value’si varsa, veritabanı bu SQL ID için birden fazla planın bulunduğunu bilir.

Bir parse operasyonu iki aşamaya ayrılarak devam etmektedir;

Hard Parse

Oracle Database mevcut kodu tekrar kullanamıyorsa, uygulama kodunun yeni bir çalıştırılabilir sürümünü oluşturmalıdır. Bu işlem hard parse veya library cache miss olarak bilinir.

NOT : DDL işlemleri her zaman “hard parse” özelliktedir ve asla tekrar kullanılmazlar.

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ı concurrency (eş zamanlılık)’i azaltır ve sorgularda yavaşlamalara neden olmaktadır. Kaynaklar paylaşılamıyor olabilir.

Soft Parse

Soft parse, hard parse olmayan herhangi bir parse’dır. Gönderilen sorgu, shared pool daki yeniden kullanılabilir bir SQL sorgusu ile  aynıysa, Oracle Database mevcut kodu yeniden kullanır. Bu kodun yeniden kullanılmasına library cache hit de denir. Genel olarak, soft parse,hard parse’a tercih edilir, çünkü veritabanı optimizasyon ve row source generation adımlarını atlar ve doğrudan execution’a devam eder. Aşağıdaki grafik, özel bir sunucu mimarisindeki bir UPDATE deyiminin shared pool kontrolünün basitleştirilmiş bir gösterimidir.

. Bununla birlikte alakasız SQL lerde aynı hash değerini üretebileceğinden bunların gerçekten aynı SQL olup olmadığı kontrolude yapılmalıdır. Bunu sağlamak için “semantic(anlamsal)” ve “environment(ortam)” kontrolleri yapılır.

  • Anlamsal Kontrol(Semantic Match) : Kısaca görünürde aynı olan SQL lerin “aynı anlama” gelip gelmediğini kontrol edilmesidir.Örneğin bir EMP tablomuz A şemasının altında olsun ve “select * from EMP” çalıştırsın.İkinci bir B kullanıcısı da EMP tablosuna sahip olsun ve aynı sorguyu çalıştırsın.Tablo adı aynı ama şemalar ve dolayısıyla oluşturulan sorgu planları(query plan) farklı olacaktır.Hatta bir C ve bir de EMP tablosuna sahip  D kullancısı olsun.C nin de D nin EMP tablosuna select hakkı ve bir de synonym bilgisi olsun.Bu durumda C ,EMP için aynı sorguyu yazsada aslında D şeması altında bulunan D.EMP tablosunu sorgulama istemiştir.Şu durumda görünürde aynı ama “semantic(anlam)” açıdan farklı birden fazla “aynı” sorgu olmuş oldu.Bu şekilde örnekler arttırılabilir.
  • Ortam Kontrolü (Environment Match) :Anlamsal(Semantic) kontrolden sonra bir ortamın aynı olup olmadığı konrolü yapılır.Burada kasıt yeni çalıştırlacak SQL ile eşleşme olan önceden çalışmış SQL ‘in aynı ortam şartlarında olup olmamasıdır.Örneğin Oracle veritabanımız 9i olsun ve “select * from EMP” sorgusu “OPTIMZER_GOAL = RULE” ‘a göre yapılmış olsun.Daha sonra çalıştırılan aynı sorgu “OPTIMIZER_GOAL = FIRST_ROWS” şeklinde bir düzenleme yapılan ortamda çalıştırılmış ise “query plan” ‘lar kullanılamaz ve bir “hard parse” yapılması zorunlu hale gelir.

Optimizer

Optimizer ile ilgili detaylı bilgiyi buradaki yazımda daha önce detaylı olarak anlatmıştım ama genede yazı bütünlüğü açısından o yazının bir özetini de buraya koyuyorum.

Optimizer Bileşenleri

Query Transformation

View Merging

Predicate Pushing

Subquery Unnesting

Query Rewrite with Materialized Views

Estimation

Selectivity  (Seçilebilirlik)

Cardinality (Kardinalite)

Cost (Maliyet)

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.

Row Source Generation

Row Source Generation ile ilgili detaylı bilgiyi buradaki yazımda daha önce detaylı olarak anlatmıştım ama genede yazı bütünlüğü açısından o yazının bir özetini de buraya koyuyorum.

Plan Generation

Bind Variable Peeking

Adaptive Cursor Sharing

Bind-Sensitive Cursors

Bind-Aware Cursors

Access Path

Full Table Scans

Rowid Scans

Index Scans

Assessing I/O for Blocks, not Rows

Index Unique Scan

Index Range Scan

Index Range Scans Descending

Index Skip Scans

Full Index Scans

Fast Full Index Scans

Index Joins

Bitmap Indexes

Cluster Access

Hash Access

Sample Table Scans

Optimizer Nasıl Access Path’ini Belirler?

JOIN

Nested Loops Join

Hash Join

Sort-Merge Join

Cartesian Join

Outer Join

Nested Loop Outer Join

Hash Outer Join

Execution Plan Nasıl Okunur

Preorder Traversal

Row source generation optimizer tarafından optimum execution’ı çıkarmak için kullanılan bir yazılımdır. SQL engine tarafından adım adım aşamalarla veri getirilir. Her adımda satır kümesi döner ve diğer adım için kullanılır. Her adımda dönen bu satır kümesine row source denir. Row source bir tablo, view veya joinlenmiş veri grubu olabilir. Row source generator çıkarılan row sourcelardan oluşan execution plana row source tree denir. Row source tree içinde tablo sıralaması, adı geçen tablolar için erişim methodu(table access by index, table access full), join işlemleri için join methodu(hash join), filter sort veya aggregate gibi veri operasyon bilgileri içerir.

Aşağıda örnek bir execution planı yer almaktadır. Soyadı A ile başlayan tüm çalışanların last_name, job_title, department_name kolonlarının getirildiği bir select sorgusu inceleyelim.

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%')

Execution

Row source generator tarafından üretilen tree üzerindeki her row source için SQL engine çalışır. Bu işlem DML işlemleri için zorunlu bir adımdır. Row source tree üzerindeki plan tersten okunur. Bottom up (Preorder Traversal algoritması) yaklaşımına göre execute edilir.  Mesela yukarıdaki execution plan iş akışı grafiğine dökülecek olursa aşağıdaki gibi bir grafik ortaya çıkar.

Siyah renkli kutular fiziksel i/o yaparak obejeden veri alır.

  • Adım 6, departments tablosundandan tüm satırları almak için full table scan kullanır.
  • Adım 5, jobs tablosundan tüm satırları almak için full table scan kullanır.
  • adımda emp_name_ix indexi üzerinde A ile başlayan değerler için arama yapılır ve bulunan değerler için rowid döner.  Örneğin, Atkinson için AAAPzRAAFAAAABSAAe rowid değeri dönsün.

Saydam kutularla gösterilen adımlar row sourceda (satır kaynaklarında) çalışır. Oeprasyonel işlemler.

  • adımda 3. ve 5. adımdaki row source içindeki her row için eşleşen kayıtlar 1. adım için döndürülür.
  • adımda 2. adımdaki row source ile 6. adımdaki row source içindeki rowlardan eşleşen row seti sonuç olarak döndürülür.

Execution sırasında, veriler bellekte(sga’da) değilse, veritabanı verileri diskten belleğe okur. Veritabanı aynı zamanda veri bütünlüğünü sağlamak için gerekli olan lock ve latch leri alır ve SQL yürütme sırasında yapılan değişiklikleri loga kaydeder. Bir SQL ifadesini processingin son aşaması cursoru kapatmaktı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.

Genel olarak, bir sorgu, Oracle Database read consistency (okuma tutarlılığı) mekanizmasını kullanarak verileri alır; bu, bir sorgu tarafından okunan tüm veri bloklarının zaman içindeki tek bir noktayla(point) tutarlı olmasını sağlar. Read consistency, geçmiş veri sürümlerini göstermek için verileri geri alır. Örneğin, bir sorgunun full table scan ile 100 veri bloğu okuması gerektiğini varsayalım. Sorgu ilk 10 bloğu işlerken, DML farklı bir oturumda bloğu 75 olarak değiştirecek işlem yaptı. Fakat ilk oturum Oracle’ın garantisini veridiği read consistency sayesinde undo verilerini de kullanarak 100 blokluk veriyi kullanır.

Verileri değiştirmesi gereken DML ifadeleri, yalnızca değişiklik başladığında arama ölçütleriyle eşleşen verileri almak için read consistency kullanır. Daha sonra, bu ifadeler mevcut durumlarında olduğu gibi veri bloklarını alır ve gerekli değişiklikleri yapar.

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;

CREATE TABLE mytable (mycolumn INTEGER);

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.

https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/sql-processing.html#GUID-B3415175-41F2-4EBB-95CF-5F8B5C39E927
http://abdullahcetinkaya.blogspot.com/2018/09/sql-processing.html
https://hakkioktay.wordpress.com/2007/03/15/statement-processing/
http://www.oganozdogan.com/2011/05/sql-nedir-parse-call-ve-plan-optimizer.html

Mustafa Bektaş Tepe
İyi Çalışmalar

Loading