Merhaba arkadaşlar bu yazıda  SQL işlemeyi (processing), optimizasyon yöntemlerini ve sorgu optimizerının SQL’i yürütmek için belirli bir planı nasıl seçtiğini anlatmaya çalışacağım.

Öncelikle optimizer’ın tanımı ile başlarsak optimizer bir SQL sorgusunun çalıştırmanın en etkili yolunu belirleyen gömülü/dahili bir yazılımdır.

Veritabanı full table scans, index scans, nested loops ve hash joins  gibi birçok şekilde bir SQL sorgusu çalıştırabilir. Optimizer, bir execution plan(yürütme plan) belirlerken, sorgudaki objeler ve koşullar ile ilgili birçok faktörü göz önünde bulundurur. Bu belirleme, SQL işlemede önemli bir adımdır ve yürütme süresini büyük ölçüde etkileyebilir.

NOT : Optimizer, Oralce veritabanı bir sürümünden diğer sürümüne aynı kararları vermeyebilir. Optimizer devamlı geliştirilen bir yazılım olduğundan çoğu zaman son sürümlerde daha iyi execution plan lar çıkartır.

Veritabanına bir sorgu geldiğinde optimizer aşağıdaki adımları gerçekleştirir;

  1. Optimizer, mevcut access paths (erişim yollarına) ve hint lere(ipuçlarına) dayanarak SQL ifadesi için bir dizi potansiyel plan oluşturur.
  2. Optimizer, her planın maliyetini data dictionary’deki istatistiklere dayanarak tahmin eder. İstatistikler, tabloların, dizinlerin ve ifadenin erişebildiği bölümlerin veri dağıtımı ve depolama özellikleri hakkında bilgiler içerir.

Cost, bildirimi belirli bir execution planla yürütmek için gereken beklenen kaynak kullanımıyla orantılı tahmini bir değerdir. Optimizer, Access path (erişim yollarının) maliyetini hesaplar bu hesap I/O, CPU ve RAM kullanım bilgilerini  içeren tahmini bilgisayar kaynaklarının kullanımıdır.

Daha yüksek maliyetli (yani coştu daha yüksek) olan planların daha düşük maliyetli (coştu daha düşük) olanlardan daha uzun sürmesi gerekir. Paralel bir plan kullanırken, kaynak kullanımı doğrudan geçen zamanla ilişkili değildir.

  1. Optimizer planları karşılaştırır ve en düşük costlu planı seçer.

Optimizer dan elde edilen çıktı, optimum uygulama yöntemini tanımlayan bir execution olandır. Planlar, Oracle Veritabanı’nın bir SQL ifadesini çalıştırmak için kullandığı adımların birleşimini gösterir. Her adım ya veritabanından fiziksel olarak satırları alır ya da ifadeyi veren kullanıcı için hazırlar.

Oracle Database tarafından işlenen herhangi bir SQL ifadesinde, optimizer aşağıda listelenen işlemleri gerçekleştirir.

Operasyon Açıklama
İfadelerin ve koşulların değerlendirilmesi Optimizer, önce sabitleri içeren ifadeleri ve koşulları olabildiğince tamamen değerlendirir.
İfade dönüşümü Örneğin, ilişkili alt sorguları veya view leri içeren karmaşık ifadeler için, optimizer orijinal ifadeyi eşdeğer bir birleşme ifadesine dönüştürebilir.
Optimize edici hedeflerin seçimi SQL ifadesi için bir optimizasyon yaklaşımı belirlenir.
Access path seçimi Sorgu tarafından erişilen her tablo için, optimizer tablo verilerini elde etmek için mevcut Access pathlerden birini veya daha fazlasını seçer.
Birleştirme sırasının seçimi İkiden fazla tabloyu birleştiren bir sorgu için, optimizer ilk önce hangi tablo çiftlerinin birleştirileceğini ve ardından hangi tablonun sonuçla birleştirileceğini seçer.

 

NOT : Bazen, belirli bir uygulamanın verileri hakkında optimizer için mevcut olandan daha fazla bilgiye sahip olabilirsiniz. Bu gibi durumlarda, optimizera bir ifadenin nasıl yürütülmesi gerektiği konusunda talimat vermek için SQL ifadelerindeki hintleri kullanabilirsiniz.

Optimizer Bileşenleri

Optimizer işlemleri şunları içerir: 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.

Oracle optimizer 1

Query Transformation

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.

Query Transformation, aşağıdakiler dahil olmak üzere birkaç sorgu dönüştürme tekniğini kullanır:

  • View Merging
  • Predicate Pushing
  • Subquery Unnesting
  • Query Rewrite with Materialized Views

View Merging

Bir sorguda başvurulan her view parser tarafından ayrı bir sorgu bloğuna genişletilir. Blok esas olarak view tanımını ve dolayısıyla view sonucunu temsil eder. Optimizer için bir seçenek, view sorgu bloğunu ayrı ayrı analiz etmek ve bir view alt planı oluşturmaktır. Optimizer daha sonra genel bir sorgu planı oluşturmak için view alt planını kullanarak sorgunun geri kalanını işler. Bu teknik genellikle düşük bir sorgu planına götürür çünkü view ayrı olarak optimize edilmiştir.

View merging işleminde, transformer, viewi temsil eden sorgu bloğunu içeren sorgu bloğuna birleştirir. Örneğin, aşağıdaki gibi bir görünüm oluşturduğunuzu varsayalım:

CREATE VIEW employees_50_vw AS
  SELECT employee_id, last_name, job_id, salary, commission_pct, department_id
  FROM   employees
  WHERE  department_id = 50;

Daha sonra viewi aşağıdaki gibi sorgulayalım.

SELECT employee_id
FROM   employees_50_vw 
WHERE  employee_id > 150;

Optimize, employees_50_vw sorgusunu aşağıdaki eşdeğer sorguya dönüştürmek için view mergingi kullanabilir:

SELECT employee_id
FROM   employees
WHERE  department_id = 50 
AND    employee_id > 150;

View merging optimizasyonu, yalnızca selections, projections, and joins içeren viewler için geçerlidir. Yani, mergeable views (birleştirilebilir görünümler) , set operatörleri, aggregate fonksiyonları , DISTINCT, GROUP BY, CONNECT BY vb. İçermez.

Optimize Edici’nin, kullanıcı tarafından verilen herhangi bir sorgu için view merging kullanmasını sağlamak için, kullanıcıya MERGE ANY VIEW ayrıcalığı vermelisiniz.

Predicate Pushing

Predicate pushing işleminde, optimizer ilgili öngörüleri içeren sorgu bloğundan görünüm sorgu bloğuna “iter”. Birleştirilmemiş görünümler için bu teknik, kaldırılmamış görünümün alt planını iyileştirir, çünkü veritabanı, endekslenmiş dizinleri erişmek veya filtreler olarak kullanmak için basılan tahminleri kullanabilir.

Örneğin, iki çalışan tablosuna referans veren bir görünüm oluşturduğunuzu varsayalım. Görünüm, aşağıdaki gibi UNION küme operatörünü kullanan bir bileşik sorguyla tanımlanır:

Bir view, index-based bir iç içe döngü (nested loop) (yani, index erişimi) join ile birleştirilemez, çünkü bir view, tabloyla zıt olarak tanımlanmış bir indexe sahip değildir. View yalnızca üç yöntem kullanarak diğer tablolarla birleştirilebilir: hash (karma), nested loop (iç içe), ve sort-merge(sıralama-birleştirme) join ile birleştirilebilir.

Birleştirme yordamını aşağı itme (JPPD) dönüşümü, daha optimal bir alternatif sağlayabilecek dizin tabanlı iç içe döngü birleştirme yöntemiyle bir görüntünün birleştirilmesine izin verir. Birleştirme yüklemi aşağı itme dönüşümünde, görünüm ayrı bir sorgu bloğu olarak kalır, ancak içerdiği sorgu bloğundan görünüme itilen birleştirme yüklemesini içerir. Böylece görünüm ilişkili hale gelir ve dış sorgu bloğunun her satırı için değerlendirilmelidir. Bu aşağı itilen birleşim, görünümün içine girdikten sonra görünümün içindeki taban tablolarında yeni dizin erişim yollarının açıldığını; bu, görünümün indeks tabanlı iç içe döngü birleştirme yöntemiyle birleştirilmesine izin verir, böylece optimize edicinin verimli bir yürütme planı seçmesini sağlar.

Optimizer merging viewle  sorguyu tamamen yeniden yazarken, Push Predicates viewi yerinde bırakır, ancak bir veya daha fazla predicatler dış sorguya döndürülen satırları kısıtlamak için kullanır (ve daha iyi bir yürütme yolu sağlar).

Örneğin, iki employee tablosuna referans veren bir görünüm oluşturduğunuzu varsayalım. View, aşağıdaki gibi UNION küme operatörünü kullanan bir bileşik sorguyla tanımlanır:

CREATE VIEW all_employees_vw AS
  ( SELECT employee_id, last_name, job_id, commission_pct, department_id
    FROM   employees )
  UNION
  ( SELECT employee_id, last_name, job_id, commission_pct, department_id
    FROM   contract_workers );

Daha sonra viewi aşağıdaki gibi sorgulayalım:

SELECT last_name
FROM   all_employees_vw
WHERE  department_id = 50;

View bir bileşik sorgu olduğundan, optimizer, view sorgusunu erişen sorgu bloğunda birleştiremez. Bunun yerine, optimizer , WHERE koşulunu department_id = 50 ifadesini görünümün bileşik sorgusuna iterek erişim ifadesini dönüştürebilir. Eşdeğer dönüştürülmüş sorgu aşağıdaki gibidir:

SELECT last_name
FROM   ( SELECT employee_id, last_name, job_id, commission_pct, department_id
         FROM   employees
         WHERE  department_id=50
         UNION
         SELECT employee_id, last_name, job_id, commission_pct, department_id
         FROM   contract_workers
         WHERE  department_id=50 );

Subquery Unnesting

Subquery’de optimizer transforms, iç içe geçmiş bir sorguyu eşdeğer bir join sorgusuna dönüştürür ve ardından join işlemini en iyi duruma getirir. Bu dönüşüm, optimizer’ın join optimizer tekniğinden yararlanmasını sağlar. Optimizer, bu dönüşümü ancak sonuçta elde edilen join ifadesinin orijinal ifadeyle aynı satırları döndürmesi garanti edilirse ve alt sorgular AVG gibi toplu işlevler içermiyorsa gerçekleştirilebilir.Örneğin;

SELECT * 
FROM   sales
WHERE  cust_id IN ( SELECT cust_id FROM customers );

customers.cust_id sütunu primary key olduğundan optimizer karmaşık sorguyu aynı verileri döndürmesi garanti edilen aşağıdaki join ifadesine dönüştürebilir:

SELECT sales.* 
FROM   sales, customers
WHERE  sales.cust_id = customers.cust_id;

Optimizer karmaşık bir ifadeyi join ifadesine dönüştüremezse, ana ifade ve alt sorgu için execution planlarını ayrı ifadelermiş gibi seçer. Optimizer daha sonra alt sorguyu yürütür ve üst sorguyu yürütmek için döndürülen satırları kullanır. Genel sorgu planının yürütme hızını iyileştirmek için, optimizer alt planları verimli bir şekilde sipariş eder.

Query Rewrite with Materialized Views

Materialized view, referans aldığımız sql’in o anki verilerini  fiziksel olarak tutan view/objedir.

Veritabanı ile ilişkili sorgu ile uyumlu bir kullanıcı sorgusu bulduğunda bir materialized view, sonra veritabanı sorgu materialized görünüm açısından yeniden yazabilirsiniz.

Sorgu sonucunun çoğu önceden hesaplandığından, bu teknik sorgu yürütülmesini iyileştirir.

Query transformer, kullanıcı sorgusu ile uyumlu olan tüm materyalize görünümleri arar ve kullanıcı sorgusunu yeniden yazmak için bir veya daha fazla materyalize görünüm seçer. Bir sorguyu yeniden yazmak için gerçekleştirilmiş görünümlerin kullanımı maliyete dayalıdır. Yani, materyalize görünümler olmadan oluşturulan plan materyalize görünümler ile oluşturulan plandan daha düşük bir maliyete sahipse, optimizer sorguyu yeniden yazmaz.

Her ay satılan dolar miktarını toplayan cal_month_sales_mv adlı aşağıdaki maddeleştirilmiş görüşü göz önünde bulundurun:

Materialized view yaratmanın ve korumanın en büyük yararlarından biri, tablo veya viewler kullanarak ifade edilen bir SQL sorgusunun, detay tablolarında tanımlanan bir veya daha fazla materialized viewe erişen, sorguya dönüştüren query rewrite özelliğinden yararlanabilme yeteneğidir. Transformers, son kullanıcı veya uygulama için şeffaftır, müdahale gerektirmez ve SQL sorgusundaki  somutlaştırılmış viewe referans vermez. Query rewrite işlemi saydam olduğundan, uygulama kodunda SQL’i geçersiz kılmadan materialized viewler index gibi eklenebilir veya bırakılabilir. “Oracle Sorguyu Ne Zaman query rewrite yapıyor?” Bir sorgunun query rewrite için yerine getirilmesi gereken koşullar nelerdir?

Sorgu, query rewrite ihtiyacı olup olmadığını belirlemek için çeşitli kontrollerden geçer. Sorgu denetimlerden herhangi birinde başarısız olursa, sorgu, materyalize view yerine ayrıntı tablolarına uygulanır. Bu, tepki süresi ve işlem gücü açısından maliyetli olabilir.

Optimizer, sorgunun view açısından ne zaman query rewrite (yeniden yazılacağını) algılamak için iki farklı yöntem kullanır. İlk yöntem, sorgunun SQL metnini Materialized view tanımının SQL metni ile eşleştirmeye dayanır. İlk yöntem başarısız olursa, optimizer, joinleri, selections, veri sütunlarını, gruplama sütunlarını ve sorgu ile Materialized view arasında toplu işlevleri karşılaştırdığı daha genel yöntemi kullanır.

Sorgu query rewrite, aşağıdaki SQL ifadeleri türündeki sorgular ve alt sorgular üzerinde çalışır:

  • SELECT
  • CREATE TABLE … AS SELECT
  • INSERT INTO … SELECT

Örnek yapacak olursak; Her ay satılan dolar miktarını toplayan cal_month_sales_mv adlı aşağıdaki materialized viewı göz önünde bulundurun:

CREATE MATERIALIZED VIEW cal_month_sales_mv
  ENABLE QUERY REWRITE 
AS
  SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars
  FROM   sales s, times t 
  WHERE  s.time_id = t.time_id
  GROUP BY t.calendar_month_desc;

Satış sayısının ayda bir milyon civarında olduğunu varsayalım. View, her ay için satılan dolar tutarı için önceden hesaplanmış toplamları içerir. Her ay için satılan tutarın toplamını isteyen aşağıdaki sorguyu dikkate alın:

SELECT t.calendar_month_desc, SUM(s.amount_sold)
FROM   sales s, times t
WHERE  s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

Query rewrite kullanılmadan, veritabanı doğrudan sales tablosuna erişmeli ve satılan miktarın toplamını hesaplamalıdır. Bu yöntem, satışlardan milyonlarca satır okumayı içerir ve bu da sorgu response time’ı her zaman artırır. Ayrıca join işlemi, sorgu yanıtını daha da yavaşlatır, çünkü veritabanı joini birkaç milyon satırda hesaplamak zorundadır. Query rewrite işleminde, optimizer şeffaf olarak sorguyu aşağıdaki gibi yeniden yazar:

SELECT calendar_month, dollars
FROM   cal_month_sales_mv;

Estimation

Bu bileşenin görevi verilen bir sql execution plan’ın maliyet (cost’unu) hesaplamaktır. Estimator bu amaca ulaşmak için üç farklı ölçme türü üretir:

  • Selectivity (Seçilebilirlik)
  • Cardinality (Kardinalite)
  • Cost (Maliyet)

Selectivity  (Seçilebilirlik)

Selectivity çalıştırdığımız sorgumuz sonucunda dönen satır sayısının, toplam satır sayısına oranı olarak düşünebiliriz. Dönen satır sayısı tablo, view , join veya GROUP BY işlecinin sonucu olabilir. Selectivity, last_name = ‘Smith’ gibi bir sorgu şartının veya last_name = ‘Smith’ AND job_type = ‘Clerk’ gibi bir şartın birleşimine bağlıdır .

Formülüze Edicek olursak: (number of return rows) / (total rows)

Selectivity bir sorgunun ne kadarlık bir kayıt döndüreceği hakkında bilgi söyleyen ve optimizer’ın bu bilgiye bakara hangi erişim yöntemini seçmesi gerektiğine karar verdiği bir parametredir. Selectivity’yi 0-1 sclasında düşündüğümüzde 0’a yakın durum güçlü selectivity, 1’e yaklaştıkça zayıf selectivity olarak kategorize edebiliriz.

Yukarıdada bahsettiğimiz 2 kategori ve bu kategoriler içinde kullanmamızın uygun olduğu etkin veri erişim yollarını listeleyelim.

Weak (Zayıf) Selectivity(dönen satır sayısının çok olduğu durum. 1’e yakın hal)

  • Full Table Scans
  • Full Partition Scans
  • Full Index Scans

Strong (Güçlü) Selectivity (dönen satır sayısının az yada tek olduğu durum. 0’a yakın hal)

  • Rowid Access
  • Index Access
  • Single Table Hash Cluster Access

Optimizer, istatistiklerin bulunup bulunmadığına bağlı olarak selectivity tahmin eder:

  • İstatistik mevcut değilse

OPTIMIZER_DYNAMIC_SAMPLING başlatma parametresinin değerine bağlı olarak, optimizer dinamik örnekleme veya dahili bir varsayılan değer kullanır. Veri tabanı türüne bağlı olarak farklı iç varsayılanlar kullanır. Örneğin, bir eşitlik şartı için iç varsayılan (last_name = ‘Smith’), bir aralık belirtiminden (soyadı> ‘Smith’) göre daha düşüktür, çünkü eşitlik şartının daha küçük bir satır kesiti döndürmesi beklenir.

  • İstatistikler mevcutsa

İstatistikler kullanılabilir olduğunda, estimator selectivityi tahmin etmek için istatistikleri kullanır. 150 farklı çalışan soyadı olduğunu varsayın. Eşitlik soyadı soyadı> ‘Smith’ yordamı için, seçicilik, bu örnekte, .006 olan, soyadı 150 farklı değerden 1’ini içeren satırları seçtiği için, soyadı, adın farklı değerlerinin sayısının karşılığını verir.

Last_name sütununda bir histogram varsa, estimator, farklı değerlerin sayısı yerine histogramı kullanır. Histogram, bir sütundaki farklı değerlerin dağılımını yakalar, bu nedenle özellikle skewed data içeren sütunlar için daha iyi selectivity tahminleri verir.

Cardinality (Kardinalite)

Cardinality sorgu sonucu satır kümesinden dönmesi beklenen toplam satır adedidir.Bir tablo, view, bir join sonucu vs. olabilir. Bir sql için cardinality tahminlerini ve gerçek değerlerini aşağıdaki gibi öğrenebiliriz.

Cost (Maliyet)

Cost , bir işlemde kullanılan iş birimlerini veya kaynakları temsil eder. Optimizer, iş birimi olarak disk I/O , CPU kullanımı ve bellek kullanımnı baz alır. İşlem, tablo scanning, bir tablodan satırlara bir index kullanarak erişme, iki tabloyu bir araya getirme (join) veya bir satır kümesini sıralama(order by) şeklinde olabilir. Maliyet, veritabanı sorguyu yürüttüğünde ve sonucunu ürettiğinde ortaya çıkması beklenen iş birimlerinin sayısıdır.

Access path, bir tablodan veri almak için gerekli iş birimlerinin sayısını belirler. Access path, table scan, fast full index scan veya index scan olabilir.

  • Table scan veya fast full index scan

Table scan veya fast full index scan sırasında, veritabanı tek bir I/O’da diskten birden fazla blok okur. Bu nedenle, taramanın maliyeti taranacak blok sayısına ve çoklu blok okuma sayısı değerine bağlıdır.

  • Index scan

Index Scan’ın maliyeti, B-tree’deki seviyelere, taranacak index leaf (yaprağı) bloklarının sayısına ve index veririsndeki rowid kullanılarak alınacak satır sayısına bağlıdır. Rowid kullanarak satırları almanın maliyeti, index clustering factor bağlıdır.

Plan Generation

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.

  • Join Order : Sorgudaki tabloların birleşme sıralarının alternetfilerini değerlendirir.
  • Query Subplans : Optimizer, iç içe geçmiş her bir alt sorguyu veya bölünmemiş görünümü ayrı bir sorgu bloğu ile temsil eder ve bir alt plan oluşturur. Veri tabanı, sorgu bloklarını aşağıdan yukarıya doğru optimize eder. Bu nedenle, veritabanı ilk önce en içteki sorgu bloğunu optimize eder ve bunun için bir alt plan oluşturur ve daha sonra son olarak tüm sorguyu temsil eden dış sorgu bloğunu oluşturur. Bir sorgu bloğu için olası planların sayısı, FROM yan tümcesinde join öğelerinin sayısıyla orantılıdır. Bu sayı, join öğelerinin sayısıyla katlanarak artmaktadır.
  • Cutoff for Plan Selection : Plan Generation, en düşük maliyetli planı bulurken denediği plan sayısını azaltmak için dahili bir kestirme yol kullanır. Kestirme yol, mevcut en iyi planın maliyetine dayanır. Mevcut en iyi maliyet büyükse, plan oluşturucu daha düşük bir maliyet planı bulmak için alternatif planları araştırır. Mevcut en iyi maliyet küçükse, Generation aramayı hızlı bir şekilde sonlandırır, çünkü daha fazla maliyet iyileştirme önemli olmayacaktır.

Bind Variable Peeking

Yazılan SQL ya da PL/SQL kod daha önce kullanılmamış ve “shared pool” da yoksa sırasıyla “parsing” ve “optimization” aşamalarını geçirdikten sonra row-source-generator ve son olarak gerçek anlamda veriyi geitren “execution” aşamalarından geçer(hard parse).

”Shared Pool” da var ise elimizde “optimizer plan” var demektir ve bu da dogrudan “execution” aşamasına geçilebilir anlamına gelir (soft parse).

Burada “parse” maliyetli bir kısımdır. Bunu aşmanın en güzel yanı bir kere “parse”  yapılınca aynı kodun devamlı oarak bir daha “parse” görmeden çalışmasını sağlamaktır. Bu da “bind variablekullanımı ile mümkündür. Örneğin; Aşağıdaki özelliklerde 100.000 kayıtlık emp tablosu olduğunu düşünelim;

DESCRIBE emp

Name                   Null?    Type
---------------------- -------- ----------------------------------
ENAME                           VARCHAR2(20)
EMPNO                           NUMBER
PHONE                           VARCHAR2(20)
DEPTNO                          NUMBER
VARIABLE deptno NUMBER
EXEC :deptno := 9

SELECT /*ACS_1*/ count(*), max(empno) 
FROM   emp 
WHERE  deptno = :deptno;

COUNT(*) MAX(EMPNO)
---------- ----------
    10         99

Optimizer, yukarıdaki sorguyu aşağıdaki gibi gerçekleştirmiş hesaplar;tek farkı her bu sorgu çalıştırıldığında hard parse olmaması.

select /*ACS_1*/ count(*), max(empno)
from emp
where deptno = 9;

Adaptive Cursor Sharing

Bind variable ve cursor sharing yani benzer SQL statement`a sahip SQL`lerin aynı exeçtuion planı kullanmalarını görmüştük. Ancak bununda dezavantajlarının olduğunu söylemek mümkün. Mesala benzer olan üç tane SQL ifademiz var, ve cursor sharing oluyorsa ilk çalısan SQL için olusturulan exeçution plan diger SQL`ler içinde çalışaçak ve eğer  (Full Table Scan) yapıyorsa ondan sonra gelen SQL`ler içinde aynı durum olacaktır. Ama diğer SQL`ler için FTS avantajlı olmayabilir. Bu durumda cursor sharing avantajlı durumdan çıkacaktır. Bunu bir örnekle açıklayacak olursak; STATECODE alanında indeks tanımlı olduğunu ve veri dağılımının aşağıdaki gibi olduğunu düşünelim:

SELECT STATE_CODE, COUNT(1) FROM CUSTOMERS GROUP BY STATE_CODE; 
ST COUNT(1)
-- ---------- 
NY 994901 
CT 5099

Yukarıda görüldüğü üzere veri uniform (düzenli) biçimde dağılmamış, tüm verinin %5’i ‘CT’ geri kalanı ise NY değerine sahip. Bu tip veriye “SKEWED DATA”(asimetrik/eğri/düzensiz veri) ismi verilir.

Bu durumda aşağıdaki sorguyu çalıştıracak olursak:

SELECT * FROM CUSTOMERS WHERE STATE_CODE = 'NY' AND TIMES_PURCHASED > 3 ;

Execution Plan;
---------------------------------------------------------- 
Plan hash value: 2008213504 

------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT | | 895K| 26M| 1532 (9)| 00:00:19 | 
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 895K| 26M| 1532 (9)| 00:00:19 | 
------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
1 - filter("TIMES_PURCHASED"> AND "STATE_CODE"='NY')

Sorgu full table scan gerçekleştirdi. ‘NY’ değerlerinin tablonun %95’ini oluşturduğunu düşünürsek doğru olan da buydu.

şimdi sorguyu tekrar, bu sefer ‘CT’ değeri ile çalıştıralım:

SELECT * FROM CUSTOMERS WHERE STATE_CODE = 'CT' AND TIMES_PURCHASED > 3

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 4876992 
--------------------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 --------------------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT | | 4589 | 138K| 56 (2)| 00:00:01 | 
|* 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 4589 | 138K| 56 (2)| 00:00:01 | 
|* 2 | INDEX RANGE SCAN | IN_CUST_STATE | 5099 | | 12 (0)| 00:00:01 |
 --------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
 --------------------------------------------------- 
 1 - filter("TIMES_PURCHASED">3) 
 2 - access("STATE_CODE"='CT')

Şimdi ise tablonun sadece %5’inde olan ‘CT’ değeri için index kullanıldı. Bu da doğru bir karar.

Şimdi ise BIND VARIABLE kullanımını görelim:

var state_code varchar2(2) 
exec :state_code := 'CT' 
PL/SQL procedure successfully completed. 

SELECT MAX(TIMES_PURCHASED) FROM CUSTOMERS WHERE STATE_CODE = :STATE_CODE 2;

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 296924608 
-------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
-------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 6 | 1511 (8)| 00:00:19 | 
| 1 | SORT AGGREGATE | | 1 | 6 | | | 
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 500K| 2929K| 1511 (8)| 00:00:19 | 
-------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
2 - filter("STATE_CODE"=:STATE_CODE)

Yine %5’lik ‘CT’ değerini kullanmamıza rağmen index kullanımını beklerken full table scan gerçekleşti. Buna ne sebep oldu? Neden optimizer index yerine fts tercih etti?

Bunun cevabı bind peeking denilen çok bilindik bir cursor sharing problemidir. Bir sorgu ilk parse edildiği sırada kullanılan string literaller ne ise o değerler ile ele alınır. Execution planı vs. herşey sorgu ilk çalıştırıldığı  ‘NY’ parametresine göre ayarlanmıştır, dolayısı ile FTS yapması doğaldır. Buna “First hard parsed plan wins” de denir.

Burada açıkca görülüyor ki, çoğu zaman performans iyileştirmesine sahip olan bind variable kullanımı SKEWED veri yapılarında, selectivity’nin farklı olduğu kolon değerlerinde daha büyük problemlere yol açabiliyor.

Oracle 11g sürümünden sonra bu durum içinde bir çözum oluşturmuştur oracle bu sorunu Adaptive cursor Sharing ile aşabilmektedir. Adaptive Cursor Sharing Cost Based Optimizer (CBO)’ın execution planları oluştururken, bir statement için daha fazla execution plan tutmasına olanak sağlayan yapı olarak ön plana çıkmaktadır.

Oracle 11g ile birlikte, sorgu her çalıştırıldığında her seferinde aynı cache’lenmiş execution plan’ı çalıştırmak yerine, planın değiştirilmesi gerekip gerekmediği sorgulanır.

Eğer bir cursor içerisinde bind-variable varsa, veritabanı bu variable’ı belli bir süre gözlemler ve planın yeniden oluşturulup oluşturulmaması gerektiğini inceler. Planın yeniden hesaplanması gerektiğine karar verirse cursor ” Bind-Sensitive ” olarak işaretlenir.

Uygun sorgular için veritabanı, zaman içinde erişilen verileri farklı bind variable için izler ve belirli bir bind variable için en uygun cursor seçimini sağlar.

Adaptive cursor sharing, veritabanı için varsayılan olarak etkindir ve devre dışı bırakılamaz.  Adaptive cursor sharing 14’ten fazla bind variable (değişken) içeren SQL ifadeleri için geçerli değildir.

Adaptive Cursor Sharing`den bilgileri alabilmemiz için bize yardımcı views(görüntüler) mevcuttur.

  • $SQL : tablosunda – cursorun bind variable’a duyarlı olup olmadığını öğrenebiliriz.IS_BIND_SENSITIVE (duyarlı),  IS_BIND_AWARE (duyarlı değil)
  • V$SQL_CS_HISTOGRAM : SQL ifadesinin bind sensitive(duyarlı) olup olmadığını 3 adet frequency histogram ile yorumlamamızı sağlar. Ayrıca child cursorların kaçar kere çalıştığı bilgisinide içerir.
  • V$SQL_CS_SELECTIVITY : Bir SQL ifadesi ile beraber gelen where koşulundaki değerleri, bunların selectivitysini ve high value – low value aralığını tutar.
  • V$SQL_CS_STATISTICS : Adaptive Cursor’ın ne kadar paylaşıldığını gösteren istatistik bilgilerini iceren bir view.

V$SQL view’ına göz atacak olursak:

SELECT IS_BIND_SENSITIVE, IS_BIND_AWARE, SQL_ID, CHILD_NUMBER FROM V$SQL 
WHERE SQL_TEXT = 'SELECT COUNT(1) FROM CUSTOMERS WHERE STATE_CODE = :STATE_CODE AND TIMES_PURCHASED > 3';
IS_BIND_SENSITIVE IS_BIND_AWARE SQL_ID CHILD_NUMBER 
----------------- ------------- ------------- ------------ 
Y Y 7cv5271zx2ttg 0 
Y N 7cv5271zx2ttg 1

Farklı değerler potansiyel olarak planı değiştirebiliyorsa, o cursor “Bind-Sensitive” olarak işaretlenir ve IS_BIND_SENSITIVE alanı “Y” yapılır.

Birkaç çalışmadan sonra, veritabanı cursor hakkında daha fazla bilgi edinir ve değişken değerler için yeni excution plan oluşturulup oluşturulmayacağına karar verir. Eğer oluşturulmasına karar veriyorsa IS_BIND_AWARE alanı “Y” yapılır.

Bind-Sensitive Cursors

Bind-sensitive cursor (duyarlı), optimal planı bir bind variable değerine bağlı olabilecek bir cursordur. Veritabanı, farklı bir planın yararlı olup olmadığını belirlemek için farklı bind value kullanan bind-sensitive cursor ‘un davranışını izler. Cursor’un bind-sensitive (duyarlı) olup olmadığına karar vermek için optimizertarafından kullanılan ölçütler şunlardır:

  • Optimizer, selectivity (seçicilik) tahminleri oluşturmak için bind value lere bakar.
  • Bind value içeren sütunda bir histogram bulunmaktadır.

Aşağıdaki  örneğin execution planına bakarsak;

Mesala Bind Variable Peeking örneğindeki SQL`i degerlendirelim.

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     1 |     7 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                   |     1 |     7 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |    70 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Plan, optimizer 9 değerinin selectivity (% 1) nedeniyle beklenen index range scan seçtiğini gösterir. Cursor ile ilgili istatistikleri görüntülemek için V$SQL’yi sorgulayabilirsiniz:

COL BIND_SENSI FORMAT a10
COL BIND_AWARE FORMAT a10
COL BIND_SHARE FORMAT a10
SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI", 
       IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"
FROM   V$SQL
WHERE  SQL_TEXT LIKE 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
           0          1          56 Y          N          Y

Yukarıdaki çıktıda gösterildiği gibi, bu statement için child cursor vardır ve bir kez çalıştırıldı. Child cursor ile  az sayıda buffer gets ilişkilendirildi. Deptno verileri skewed(asimetrik/eğri) olduğundan, veritabanı bir histogram yarattı. Bu histogram, cursor  bind-sensitive  olarak işaretlemek için veritabanını yönlendirmiştir (IS_BIND_SENSITIVE Y).

Bind-Aware Cursors

Bir cursor’ın bind-aware hale gelip, “bu tabloda skewed veri vardır, ben skewed verilerin her biri için ayrı execution planlar ile çalışacağım” diyebilmesi için arka planda bir mekanizma vardır. v$sql_cs_statistics tablosunun rows_processed isimli kolonun bu mekanizmada kilit rolü vardır.

Bu kolon, hard-parse sırasında güncellenir ve sorgunun döndürdüğü kayıt sayısının bir temsilidir.

rows_processed alanının değerlerinin büyüklüğüne göre v$sql_cs_histogram view’ının ilgili bucked_id ‘sine sahip kaydının count’ı arttırılır.

Küçük row sayısı için v$sql_cs_histogram.bucket_id(0), kaydının

Orta row sayısı için v$sql_cs_histogram.bucket_id(1), kaydının ve

Büyük row sayısı için v$sql_cs_histogram.bucket_id(2), kaydının count sayısı arttırılır.

V$SQL_CS_HISTOGRAM, frekansı child-cursor bazında üç adet bucket’a dağıtarak, ilgili SQL sorgusunun bind-sensitive olup olmadığını anlamak için kullanılır.

SELECT * FROM V$SQL_CS_HISTOGRAM WHERE SQL_ID = '7CV5271ZX2TTG';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT 
-------- ---------- ------------- ------------ ---------- ---------- 
45C8218C 2144429871 7cv5271zx2ttg 5 0 0 
45C8218C 2144429871 7cv5271zx2ttg 5 1 2 
45C8218C 2144429871 7cv5271zx2ttg 5 2 0 
45C8218C 2144429871 7cv5271zx2ttg 4 0 8
 ... and so on ... 
45C8218C 2144429871 7cv5271zx2ttg 0 2 0

Bu bucket aralıkları yaklaşık olarak şöyledir:

Bucket 0 < 1000 < Bucket 1 < 1000000 < Bucket 2

Bu histogram bucket’ları, Bind Sensitive olan bir sorgu her çalıştırıldığında güncellenir. Böylece sorgunun kaç kayıtlık bir küme üzerinde sürekli çalıştırıldığı gözlemlenir ve hatırı sayılır bir veri kümesi üzerinde sürekli bir çalışma varsa sorgu Bind Aware hale gelir.

Access Path

Access Path, veritabanından verilerin getirilme yollarına verilen isimdir. Genel kural olarak, OLTP tipindeki düşük hacimli veri döndüren sorgularda index access path’leri kullanılıyor olmalıdır, oysa full scan tablonun büyük bir bölümüne erişirken daha verimlidir.  Tablonun, büyük bir kısım verisine ulaşılmak isteniyorsa full table scan daha performanslı olacaktır.

Data access path’leri aşağıda listelenmiştir:

Full Table Scans

Bu tip veri erişiminde, tablo segmentinin tüm rowları okunur, filtre varsa koşula uymayan değerler çıkarılır. Oracle, FTS yaparken sequential bir okuma gerçekleştirir. Segment, extent yapısından hatırlayacak olursak, belli bir segmentin ilgili blokları bitişik olarak tutulur, dolayısı ile tek bir blok okuyan I/O işleminden ziyade, çoklu blok okuma buradaki hızı arttıracaktır. Yani bloklar bitişik olduğundan, veritabanı işlemi hızlandırmak için I/O çağrılarını tek bir bloktan daha büyük yapabilir. FTS’de okuma hızı 1 blok ile DB_FILE_MULTIBLOCK_READ_COUNT parametresinde belirtilen değer arasında olur. Veritabanı her bloğu sadece bir kez okur.

Büyük miktarda veriye erişmek için neden tam tablo taraması daha hızlı diye soracak olursak; Full table scans, bir tablodaki blokların büyük bir kısmına erişilirken index range scans daha az maliyetlidir. Full table scan daha büyük I/O çağrıları kullanabilir ve daha az büyük I/O çağrısı yapmak, birçok küçük arama yapmaktan daha ucuzdur.

Optimizer aşağıdaki durumlarda Full Table Scans kullanır;

  • Lack of Index : Sorgu varolan indexleri kullanamıyorsa, full table scan kullanır. Örneğin, sorguda indexe alınmış sütunda kullanılan bir fonksiyon varsa, optimizer indexi kullanamaz ve full table scan kullanır.
  • Large Amount of Data : Optimizer, sorgunun tablodaki blokların çoğunu gerektirdiğini düşünüyorsa, indexler mevcut olmasına rağmen ull table scan kullanır.
  • Small Table : Tablo, veritabanının tek bir I/O çağrısında okuyabileceği high water mark altındaki DB_FILE_MULTIBLOCK_READ_COUNT bloktan daha azını içeriyorsa, full table scan erişilen tabloların fragmantosyonundan bağımsız olarak index range scan mevcut indekslerden daha ucuz olabilir.
  • High Degree of Parallelism : Tablo için yüksek paralellik derecesi, optimizer range scan’a göre full table scans doğru eğirir.

Rowid Scans

Rowid, bir row’un (satırın) hangi datafile içerisinde, hangi bloğa ait olduğunu ve blok içerisindeki lokasyonun belirten bir ID’dir. Bir satıra rowid ile erişmek en hızlı kayıt getirme yöntemidir.

Bir tabloya rowid ile gidebilmek için önce rowid’lerin elde edilmesi gerekir; bu rowid leri ya WHERE koşuluna uyacak şekilde tablonun kendisini tarayarak ya da, index taraması yaparak elde eder.

Index Scans

Index Scan, optimizer tarafından en iyi execution planı üretmek için için Sql Engine tarafından kullanılan bir erişim yoludur.

Bir SQL cümlesi sadece indexli alanları sorguluyorsa, veritabanı bu değerleri sadece indexe giderek ve tabloya hiç uğramayarak getirebilir. Indexli alanların haricinde, tabloda yer alan bir veya daha fazla indexsiz alan da isteniyorsa, veritabanı rowid’ler veya cluster scan yardımıyla tabloya giderek istenilen kayıtları getirir.

Index scan için birden fazla yol vardır; (Daha Önce bu erişim yollarını anlattığım için linkleri koydum)

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

Veritabanı, aynı cluster key değerine sahip tüm satırları, dizine alınmış bir kümede depolanan bir tablodan almak için cluster index kullanır. Indexe alınmış bir clusterda, veritabanı aynı cluster key değerine sahip tüm satırları aynı veri bloğunda depolar. Cluster scan yapmak için, Oracle Database ilk önce cluster indexini tarayarak seçilen satırlardan birinin satır sayısını elde eder. Oracle Database daha sonra bu satır sırasına göre satırları bulur.

Hash Access

Bir hash cluster’dan veri erişimi sağlayabilmek için kullanılan erişim yoludur.

Sample Table Scans

Sample table scan, basit bir tablodan veya joinlerden, rastgele bir veri örneği alır.

İfadenin tablo kısmından sonra SAMPLE veya SAMPLE BLOCK ifadelerini kullanınca veritabanı bu erişim yolunu kullanır.Örnekleme yaparken örnek tablo taraması gerçekleştirmek için, veritabanı tablodaki belirli bir satır yüzdesini okur.

Optimizer Nasıl Access Path’ini Belirler?

Optimizer aşağıdaki faktörlere göre bir erişim yolu seçer;

  • İfade için kullanılabilir erişim yolları,
  • Her access path veya path kombinasyonunu kullanarak ifadeyi çalıştırmanın tahmini maliyeti,

Access Path seçmek için, optimizer ilk olarak, deyimin WHERE deyimindeki ve FROM deyimindeki koşulları inceleyerek hangi Access path lerin kullanılabileceğini belirler. Optimizer daha sonra mevcut Access pathleri kullanarak bir dizi olası execution planı oluşturur ve her planın maliyetini hesaplar. Son olarak, en iyileştirici, yürütme planını tahmini en düşük maliyetle seçer.

Optimizer Access path seçerken aşağıdaki durumlardan etkilenir;

  • Hint Kullanmak : Sorgumuzda hint kullanırak istediğimiz Access path’e yönelik yönlendirme yapabiliriz.
  • Eski İstatistik : Örneğin bir tablo oluşturulduğundan beri hiç analiz geçilmemişse ve tablo da büyük miktarda veri dahi olsa optimizer tablonun küçük olduğunu düşünül full table scan’e sokabilir.

JOIN

Join, birden fazla tablodan veri alan sorgulardır. WHERE tümcesinde bir birleştirme koşulunun varlığı, tablolar arasındaki ilişkiyi tanımlar. Joinde, bir satır kümesine iç (inner), diğerinde dış (outer) denir.

Join sorgusu için Optimizer execution plan seçmek için aşağıdaki kararları alması gerekir.

  • Access Path: İstenilen verilerin, veritabanından nasıl çekileceğini belirtir. Yani, index kullanılacak mı yoksa FTS mi yapılacak, index kullanılacaksa ne tip bir indeks scan yöntemi kullanılacak (index full scan, index skip scan, vb.).
  • Join Order: Oracle bir seferde iki adet tabloyu joinleyebilir. İkiden fazla tablonun joinlendiği durumlarda, Oracle önce iki adet tabloyu joinler; join sonucu elde edilen result set ile geri kalan tabloları yine ikili devam edecek şekilde sonunda açıkta hiç tablo kalmayacak şekilde tamamlar. Join işlemine ne kadar az kayıt katılırsa sorgu o kadar performanslı olur. Bu sebeple Oracle, önce her zaman küçük tabloları daha sonra büyük tabloları joinlemeye çalışır. Hızlı bir çalışma ile joinlenen küçük tablolardan çıkan azalmış sonuç kümesi ile en sonunda büyük tablo veya tabloları joinleyerek optizasyon yapmaya çalışır.
  • Join Method (Yöntemleri): İki tablonun nasıl joinleneceğini belirten yöntemlerdir, bir anlamda join algoritmasının ne olacağının belirlenmesidir. Birçok join yöntemi vardır:
    • Nested Loops Join
    • Hash Join
    • Sort-Merge Join
    • Cartesian Join
    • Cluster Join

Nested Loops Join

Nested Loop Join, aşağıdaki koşullar geçerli olduğunda faydalıdır:

  • Join işleminde tablonun küçük bir alt kümesine erişim yapılıyorsa.
  • Join koşulu, ikinci tabloya erişim için verimli ise.

Nested loop join iç içe 2 ya da daha fazla döngü şeklinde çalışan sorgular olarak özetlenebilir. İçteki (Inner) ve dıştaki (outer) tablo önemlidir. Inner ve outer tabloya optimizer (CBO) kendisi karar vermektedir. Bu seçim yapıldıktan sonra seçilen “Outer” tablo “driving table” olarak adlandırılır.

Nested Loop algoritması şu şekilde çalışır:

  • Optimizer, “driving table” olan küçük olan, seçici olarak görev yapacak olan tabloyu seçer ve bunu outer table olarak belirler.
  • Diğer tablo, yani outer table’daki her değer için sorgulanacak büyük tablo ise “inner table” olarak belirlenir.
  • Outer table’daki her değer için, inner table sorgulanır ve kayıt(lar) getirilir. Bu, tıpkı büyük bir tabloya art-arda sorgu atmaya benzer, tek fark, sorgulardaki değerlerin küçük tablodan getirilmesidir.
NESTED LOOPS 
	outer_loop --&gt; küçük tablo 
	inner_loop --&gt; büyük tablo

Oracle 11g’den önceki versiyonlarda, outer tablodaki her değer için teker teker inner tabloya giderek değerleri getiriyordu. Fakat bu yöntem, verilerin diskte olduğunu düşünürsek yüksek fiziksel I/O yani performans düşüşüne sebep oluyordu. 11g ile birlikte nested loops işlemi optimize edilerek fiziksel I/O yerine, istenilen verilerin tümünü batch olarak getiren vektörel I/O kullanılmaktadır. Aşağıdaki örnekte buna benzer bir sorgu yazalım:

set autotrace traceonly
set lines 300
SELECT e.first_name, e.last_name, e.salary, d.department_name
    FROM hr.employees e, hr.departments d
    WHERE d.department_name IN ('Marketing', 'Sales')
      AND e.department_id = d.department_id;

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |    19 |   722 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                   |    19 |   722 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |                   |    20 |   722 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS STORAGE FULL | DEPARTMENTS       |     2 |    32 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   220 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
  • Sorguda dikkat edilecek olursak 11g’deki yeni implementasyonun sonucu olarak 2 adet nested loop gözükmektedir. Bu durumda hr.departments tablosundaki satırlar ilk joinin outer kısmını oluşturur. İlk joinin inner tarafında ise emp_department_ix indexi vardır ve hr.departments tablosu inner tarafındaki emp_department_ix indexi ile joinler.
  • Buradan çıkan result set, 1 numaralı loop için yine outer tarafdadır ve inner tarafdaki EMPLOYEES tablosuna erişim buradan çıkan sonuçların rowid’leri ile yapılarak loop’lar tamamlanır.

CBO, nested loop çalıştırırken küçük olan outer tablodan, büyük olan inner tabloya doğru çalışır, aynı bir for loop gibi. O yüzden, execution plan’da yer alan ORDER – sıra önemlidir.

 

Hash Join

Join e katılan tablolardan küçük olanın verilerinin hash mantıgı ile memory cekilmesi, diğer tablonun sorgulanıp memory de oluşturulan “hash table” ile karşılaştırılması esasına dayanır.Çekilecek veri büyük ise genelde uygun olan yöntemdir.“USE_HASH” hint’i ile optimizer ın “hash join “ i kullanması zorlanabilir. Bu yöntem en iyi şekilde tablolardan küçük olanı memory alanına sığabiliyorsa çalışır, her iki tablo üzerinden sadece birer kez geçilir. Aksi takdirde TEMP tablespace kullanılacaktır.

drop table t1;
drop table t2;
create table t1 nologging as select object_id, object_name, object_type from all_objects;
create table t2 nologging as select object_id, object_name, object_type from all_objects;
analyze table t1 compute statistics;
analyze table t2 compute statistics;

select * from t1, t2
where t1.object_id = 1234
and t1.object_name = t2.object_name;

-----------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     2 |   144 |   311   (1)| 00:00:04 |
|*  1 |  HASH JOIN                  |         |     2 |   144 |   311   (1)| 00:00:04 |
|   2 |   JOIN FILTER CREATE        | :BF0000 |     1 |    36 |   156   (1)| 00:00:02 |
|*  3 |    TABLE ACCESS STORAGE FULL| T1      |     1 |    36 |   156   (1)| 00:00:02 |
|   4 |   JOIN FILTER USE           | :BF0000 | 94653 |  3327K|   156   (1)| 00:00:02 |
|*  5 |    TABLE ACCESS STORAGE FULL| T2      | 94653 |  3327K|   156   (1)| 00:00:02 |
---------------------------------------------------------------------------------------

Sort-Merge Join

Sort-Merge Join, Oracle’ın büyük tablolar arasında eşitsizlik operatörleri ile yapılan join işlemlerindeki stratejisidir.

Bu join yönteminde hash ve nested join tiplerindeki outer(driving) ve inner(driven) tablo kavramları yoktur. Sort(sıralama) ve Merge(birleştirme) aşamalarından oluşan işlem aşağıdaki gibi gerçekleşir:

  • Oracle, A tablosunun tüm verisini okur ve verileri join’de yer alan kolon(lar)a göre sıralar.
  • Oracle, B tablosunun tüm verisini okur ve verileri join’de yer alan kolon(lar)a göre sıralar.
  • Her iki tablonun da sıralama işlemleri bittikten sonra, Oracle bu sıralanmış olan veriyi join edilen kolonlar üzerinde kayıt-kayıt kıyaslayarak uyuşan veriyi birleştirerek döner.Oracle optimizer 2Çoğu zaman Hash-Join, Sort-Merge Join’den daha performanslı çalışır. Fakat şu durumlarda Oracle, sort-merge join kullanımını CBO tarafından tercih edebilir:
    • Join işleminde kullanılan alanlar üzerinde aynı zamanda sort işlemi de varsa, sort-merge join zaten veriyi sıralı biçimde getirdiğinden dolayı tercih edilebilir.
    • Tablolar <,>,<=,>= gibi eşitsizlik operatörleri ile joinlenmişse, hash-join kullanılamaz, nested-loop kullanılabilir ancak joinlenen tablolar büyükse nested-loop çok kötü bir performans sunacaktır.

    Verinin index’den okunabildiği durumlarda, sort-merge join’in sort işleminin yapılmasına gerek kalmaz, index ile okunan veri zaten sıralıdır.

    Sort-Merge Join işlemi aşağıdaki sebeplerden ötürü zorunlu olmadıkça kullanılmamalı ve hint ile zorlanmamalıdır.

    • Tablo verileri memory alanına sığmazsa, çok yüksek bir TEMP tablespace kullanımı olabilir.
    • Sort-merge join algoritmasını düşünecek olursak, verilerin gösterilmeye başlayabilmesi için her iki tabloda da önce sort işlemlerinin tamamlanmsaı gerekmektedir. Sırf bu yüzden, OLTP uygulamalarında sakınılarak, bu özellikler göz önünde bulundurularak kullanılmalıdır. (Örneğin paging yapan bir sorgulama)
    • Sort işlemleri, yüksek CPU tüketir.

    Sort-Merge Join işlemi /*+ USE_MERGE(M SM) */ hint’i ile zorlanabilir.

    Cartesian Join

    Join yapılan her tablonun her satırı birbiri ile joinlenir.  Elde edilecek sonuç ilk tablonun kayıt sayısı ile ikinci tablonun kayıt sayılarının çarpımıdır.

    Genelde join koşulunun unutulması ile karşımıza çıkar, fakat bazen performanslı olduğu durumlar da olabilir. Cartesian join gözümüze çarparsa sebebini mutlaka araştırmalıyız. Şu gibi sebepleri olabilir:

    • Join koşulu yoksa.
    • Optimize edebilmek için. : Cartesian çarpımı küçük kayıtlar için çok hızlı çalıştığından ötürü, bazen optimizer çok küçük tabloların aynı büyük tabloyla join edildiği durumlarda önce bu iki küçük tabloyu kendi aralarında cartesian olarak çarpabilir.  Tek kayıt dönüleceği bilinen bazı durumlarda da cartesian çarpım görmek mümkündür:
    • ORDERED Hint’inde join edilecek tabloların sırasında bir sorun varsa.

     

    Outer Join

    İki tablo arasındaki outer join işleminde, bir A tablosunun verileri, B tablosunda bir veya daha fazla eşleşen veri bulunduğu zaman döndürülür. Outer-join ve klasik join işleminin farkı; A tablosundaki verilerin yanlızca bir kez dönüdürülmesidir. B tablosunda, A tablosundaki veri için birden fazla eşleşme olsa bile A tablosundan yanlızca bir row döndürülür. CBO, EXISTS ve IN yapılarında outer-join kullanmayı tercih edebilir.

    Outer-Join kullanılarak yapılan SQL işlemleri:

    • Nested Loop Outer Join
    • Hash Outer Join
    • Sort Merge Outer Join
    • Full Outer Joins : Full outer join, sol ve sağ dış birleşimlerin bir kombinasyonu gibi davranır.

    Nested Loop Outer Join

    Oracle outer-join yapmaya karar verdiği takdirde, eğer joinde kullanılan alan, asıl tablo ve alt-tablo için yeteri kadar selective ise nested loop tercih edilebilir.

    Oracle, nested loop outer-join işlemini şu şekilde gerçekleştirir:

    1. Asıl sorgu(dış) sorgudaki tablo “outer tablo”(driving table) olarak belirlenir.
    2. İç sorgu ise “inner tablo”(driven table) olarak belirlenir.
    3. Outer tablo’daki her kayıt için inner tabloya gidilerek koşulu sağlayan durum var mı diye bakılır.
    4. Eğer koşulu sağlayan en az bir adet kayıt varsa bu kayıt döndürülür ve loop’dan çıkılır.

    4.adımdaki “Loop’dan çıklır” ifadesi outer-join’in asıl mantığını oluşturur, outer tablonun o kaydı için loop’un geri kalanını çalıştırmak artık gereksizdir, çünkü sorgu zaten eşleşme durumu var mı sorusunun sorgusudur; eşleşen kayıtları getir sorgusunun değil.

    Nested-loop outer join yöntemi /*+ NL_SJ */ hint’i ile zorlanabilir. Fakat bu hint, outer sorguya değil inner sorguya yazılmalıdır.

    Inner sorgu’daki hiçbir kolon sorgulanamaz.

    Bu join yönteminde, koşula uyan ilk kayıtların gösterilmesi çok hızlıdır. Paging yapan bir uygulama düşünürsek, ilk kayıtlara erişim hızlı olacaktır, fakat son eşleşen kayıtlara erişim hızzı düşüktür.

    Hash Outer Join

    Oracle Hash-outer Join yaparken join işlemini hızlandırmak için RAM alanını kullanır. (HASH_AREA_SIZE – PGA) Bunun için, Outer tablo taranır, RAM hash tablosu oluşturulur ve inner tablodaki eşleşen kayıtlar aranmaya başlar. Hash-outer join tercih edilmesinde, hash_area_size isimli Oracle parametresinin rolü vardır, bu parametre değeri ne kadar büyükse Oracle o denli Hash-outer join yapmak isteyebilir.

    Outer tablo ve inner tablodaki join yapılan kolonlar selective olmadığı durumlarda outer join işlemi hash-outer join kullanabilir.

    Oracle’da hash-outer join işlem i şu şekilde gerçekleşir:

    1. Oracle, asıl-dış sorguyu hash tablosu olarak belirler(driving). Bu hash tablosu, driving tablonun join edilen alanı üzerinde hash fonksiyonu çalıştırılarak RAM üzerinde oluşturulur.
    2. Oracle inner sorgudaki tabloyu probe tablosu (driven table) olarak belirler. Probe tablosu üzerindeki join alanına yine aynı hash fonksiyonunu uygulayarak taramaya başlar ve uyuşma olan tablolar ile RAM üzerindeki hash tablosuna gidilir.
    3. RAM tablosuna giden probe kaydı için uyuşma var ise bu kayıt döndürülür ve hash tablouna bir flag ile uyuşma ile karşılaşıldığı bilgisi işaretlenir.
    4. RAM tablosuna giden probe kaydı için uyuşma var ise fakat flag zaten işaretli ise kayıt dödürülmez.

    Hash-outer Join kullanımı /*+ HASH_SJ */ hint’i ile zorlanabilir. Bu hint EXISTS veya IN kullanan inner query tarafına yazılmalıdır.

    Execution Plan Nasıl Okunur

    Bir SQL ifadesini yürütmek için, Oracle Veritabanı’nın birçok adımı gerçekleştirmesi gerekebilir. Her adım veri tabanından row ları (veri satırlarını) fiziksel olarak alır veya bir şekilde ifadeyi yayınlayan kullanıcı için hazırlar. Oracle Database’in bir sorgu çalıştırmak için kullandığı adımların birleşimine execution plan der. Execution plan, ifadenin eriştiği her tablo için bir Access path ve uygun join yöntemiyle tabloların bir sıralamasını (join order) içerir.

    Optimizer tarafından SQL ifademiz için oluşturulan execution planı görmek istersek daha önceden yazdığım yazıdaki  yöntemlerden birini kullanabilirsiniz.

    PLAN çıktısındaki yürütme sırası, sağa girintili en uzak çizgiyle başlar. Bir sonraki adım, o çizginin ebeveyni. İki satır eşit girintili ise, üst satır normalde ilk yürütülür. Bir örnekle anlatacak olursak;

    Execution planı başarıyla anlamak için, plan adımlarının uygulanma sırasını bilmeniz gerekir.

    SQL execution plan okumak her zaman zor olmuştur, ancak açıklama planı adımlarının yürütüldüğü doğru sırayı belirlemeye yardımcı olacak bazı püf noktaları vardır.

    SQL execution planları, aşağıda göreceğiniz preorder traversal(ters çapraz) algoritması kullanılarak yorumlanır.

    • Execution planı okumak için en içteki ifadeyi arayın. Bu genellikle yapılan ilk ifadedir ancak her zaman DEĞİL!
    • Çoğu durumda, aynı düzeyde iki ifade varsa, önce ilk ifade yürütülür.

    Başka bir deyişle, yexecution planları en girintili işlemden başlayarak içeriden dışarıya okunur.

    Mesala aşağıdaki bu plana bir bakın. Hangi işlem ilk önce yürütülür?

    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |    10 |   650 |     7  (15)| 00:00:01 |
    |*  1 |  HASH JOIN         |      |    10 |   650 |     7  (15)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| JOB  |     4 |   160 |     3   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL| EMP  |    10 |   250 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    

    Cevap, job tablosundaki full table scan işleminin ilk önce gerçekleştirileceğidir.

     

    Başka bir örnek plana bakalım ve onu okuyalım.

      ID  Par Operation
       0      SELECT STATEMENT Optimizer=FIRST_ROWS
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
       2    1     NESTED LOOPS
       3    2       TABLE ACCESS (FULL) OF 'DEPT'
       4    2       INDEX (RANGE SCAN) OF 'IX_EMP_01' (NON-UNIQUE)
    

    Bu SQL execution adımları sırasını gözden geçirerek, işlemlerin sırasının 3,4, 2, 1 olduğunu görüyoruz. Bunun nedeni 3 ve 4 ‘ün en içteki ifade olduğu ve bunlardan 3’ün üstte olduğu için en başta 3 ile başlar.

    İşte bu yürütme planının grafiği:

    Oracle optimizer 3Bu sorgunun nasıl yürüdüğünü görmek için ağacı ters sırayla değiştiririz. En soldan en derideki dala, ağacı yukarı hareket ettir ve her daldan sağa geç.

    Başka bir örnek yapacak olursak;

    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=150000 Bytes=3300000)
       1    0   HASH JOIN (Cost=864 Card=150000 Bytes=3300000)
       2    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=826 Card=1 Bytes=10)
       3    2       INDEX (FULL SCAN) OF 'IX_DEPT_01' (NON-UNIQUE) (Cost=26 Card=1)
       4    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=150000 Bytes=1800000)
    

    Cevap 3,2,4,1

    Başka bir örnek daha;

    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=168 Card=82 Bytes=3936)
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Bytes=12)
       2    1     NESTED LOOPS (Cost=168 Card=82 Bytes=3936)
       3    2       MERGE JOIN (CARTESIAN) (Cost=4 Card=82 Bytes=2952)
       4    3         TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)
       5    3         BUFFER (SORT) (Cost=2 Card=82 Bytes=2132)
       6    5           TABLE ACCESS (FULL) OF 'BONUS' (Cost=2 Card=82 Bytes=2132)
       7    2       INDEX (RANGE SCAN) OF 'IX_EMP_01' (NON-UNIQUE) (Cost=1 Card=1)
    

    Cevap 4,6,5,3,7,2,1

    Oracle optimizer 4

    Burada 2. adımda üç ve yedi, iki çocuk ve 3. adımda dört ve beş iki çocuk olduğunu görüyoruz. 5. adımda yalnız bir çocuk var oda 6. adımdır.

    preorder traversal kurallarımıza göre, yürütme planı adımları 4. adımda başlar.

    Başka bir örnek;

    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2871 Card=2 Bytes=143)
       1    0   UNION-ALL
       2    1     SORT (GROUP BY) (Cost=2003 Card=1 Bytes=59)
       3    2       FILTER
       4    3         HASH JOIN (Cost=1999 Card=1 Bytes=59)
       5    4           INDEX (FAST FULL SCAN) OF 'XIN8OPS_FLT_LEG' (UNIQUE) 
       6    4           INDEX (RANGE SCAN) OF 'XIN3BAG_TAG_FLT_LEG' (UNIQUE)
       7    1     SORT (GROUP BY) (Cost=868 Card=1 Bytes=84)
       8    7       FILTER
       9    8         NESTED LOOPS (Cost=864 Card=1 Bytes=84)
      10    9           HASH JOIN (Cost=862 Card=1 Bytes=57)
      11   10             INDEX (FAST FULL SCAN) OF 'XIN1SCHED_FLT_LEG' (UNIQUE)
      12   10             INDEX (FAST FULL SCAN) OF 'XIN8OPS_FLT_LEG' (UNIQUE)
      13    9           INDEX (RANGE SCAN) OF 'XIN2BAG_TAG_FLT_LEG' (UNIQUE)
    

    Cevap 5, 6, 4, 3, 2, 11, 12, 10, 13, 9, 8, 7, 1

    Preorder Traversal

    İkili ağaç(binary tress) üzerinde dolaşma birçok şekilde yapılabilir Ancak, rastgele dolaşmak yerine, önceden belirlenmiş bir yönteme, bir kurala uyulması algoritmik ifadeyi kolaylaştırır. Önce-kök (preorder), kök-ortada (inorder), sonra-kök (postorder) olarak adlandırılan üç değişik dolaşma şekli çeşitli uygulamalara çözüm olmaktadır.

    1. Preorder (Önce Kök) Dolaşma: Önce kök yaklaşımında ilk olarak root(kök), sonra left (sol alt ağaç) ve ardından right (sağ alt ağaç) dolaşılır.

  1. Inorder (Kök Ortada) Dolaşma: Inorder dolaşmada önce left (sol alt ağaç), sonra root (kök) ve right(sağ alt ağaç) dolaşılır.
  2. Postorder (Kök Sonda) Dolaşma: Postorder yaklaşımında ise, önce left (sol alt ağaç), sonra right (sağ alt ağaç) ve root (kök) dolaşılır.

Örnek: “25, 14, 23, 40, 24, 23, 48, 7, 5, 34, 10, 7, 17, 36” değerlerine sahip düğümler için ikili ağaç gösterimini oluşturunuz ve üç farklı preorder, inorder ve postorder sıralama yöntemine göre yazınız.Oracle optimizer 6

Preorder : 25-14-7-5-10-7-23-17-24-23-40-34-36-48
Inorder : 5-7-7-10-14-17-23-23-24-25-34-36-40-48
Postorder : 5-7-10-7-17-23-24-23-14-36-34-48-40-25

Şimdi preorder için bunu nasıl yaptık adım adım inceleyelim.
Kök ile başladık yani 25. Kökten sonra sol işlem bu da 14. 14 ün kendiside alttakilerin kökü olduğu için aynı şekilde onunda solu 7 yi yazdık. Aynı şekilde 5 te yazıldı. 5 ten sonra bir şey yok sağ yazıldı 10 ve 10 dan sonra gelen 7. 14 e kök demiştik sol tarafı bitti şimdi sağ tarafındayız. Sağında 23 ile başladık onun solunda 17 sağında 24 ve 23 sırasıyla yazdık. Artık en üstteki 25 in kökün sol tarafı bitti şimdi sağ tarafında. 40 ile başladık onun solu 34 oda bir kök solu yazmamız lazım solda bir şey yok sağı 36 yı yazdık. 40 solu bitti şimdi sağı kaldı o da 40. Böylece preorder sıralaması bitti.

Optimizer Davranışını Denetleme

Optimizer’ın davranışını kontrol etmek için kullanabileceğiniz başlatma parametreleri aşağıdaki gibidir. SQL execution performansını artırmak için çeşitli optimizer özelliklerini etkinleştirmek için bu parametreleri kullanabiliriz.

  • CURSOR_SHARING : Shared pool içerisinde tutulan sqllerin benzerleri geldiğinde aynı plan ile diğerlerininde çalışıp çalışmayacağının set edildiği parametredir (literal(numara veya karakter) kullanımını görüp bunun birer bind variable olarak plan’larının saklanmasını sağlar)
    • EXACT(default) : Çalışan sqlerin cache’ den çalışması için sorguların birebir aynısı olması gerekmektedir.
    • FORCE : Cache de Çalışan sql lerin benzeri varsa mevcut execeution plan kullanılmasını zorlar.(Yani yazilan, ve benzer butun sql`ler icin ilk olusutural execution planlarin ortak kullanilmasi)
    • SIMILAR : sql’ lerin birebir aynı olmasa da benzeyenler için (execution planlarına da bakar) cache den çalıştırmaya yönlendirir.
  • DB_FILE_MULTIBLOCK_READ_COUNT : Full table scan veya index fast full scan sırasında tek bir I/O’de okunan blok sayısını belirtir. Optimizer, Full table scan ve index fast full scan ını maliyetlendirmek için DB_FILE_MULTIBLOCK_READ_COUNT değerini kullanır. Daha büyük değerler full table scan için daha ucuz bir maliyetle sonuçlanır ve optimizer index scan yerine full table scana seçmesine neden olabilir. Bu parametre açıkça ayarlanmamışsa (veya 0 olarak ayarlanmışsa), varsayılan değer, verimli bir şekilde gerçekleştirilebilen ve platforma bağlı olan maksimum I/O boyutuna karşılık gelir. Ancak Bu parametreyi değiştiriken Makinamızın CPU RAM gibi değerlerini göz önüne almalıyız.
  • OPTIMIZER_INDEX_CACHING : Nested loop ve IN-list iteratorları tercih etmek için maliyet tabanlı optimizasyon davranışını ayarlamanıza izin verir. Optimizer’ın buffer cache’de bulunduğunu varsayması gerek index bloklarının yüzdesini belirler.
  • OPTIMIZER_INDEX_COST_ADJ : İndeks maliyetinin araştırılmasını ayarlar. Değer aralığı 1 ila 10000’dür. Varsayılan değer 100’dür; bu, indexlerin normal maliyetlendirme modeline göre bir access path olarak değerlendirildiği anlamına gelir. 10 değeri, bir index access path maliyetinin, bir index access path normal maliyetinin onda biri olduğu anlamına gelir.
  • OPTIMIZER_MODE : Başlangıçta optimizer’ın modunu ayarlar. ALL_ROWS, FIRST_ROWS_n, ve FIRST_ROWS değerlerini alabilir.
    • ALL_ROWS : Optimizer, oturumdaki tüm SQL ifadeleri için, istatistiklerin varlığına bakmaksızın cost-based bir yaklaşım kullanır ve en iyi verim hedefiyle optimize eder (ifadenin tamamını tamamlamak için minimum kaynak kullanımı). Bu varsayılan değerdir.
    • FIRST_ROWS_n : Optimizer, istatistiklerin varlığına bakmaksızın cost based bir yaklaşım kullanır ve n’nin 1, 10, 100 veya 1000 olduğu ilk satır sayısını döndürmek için en iyi yanıt süresi hedefiyle optimize eder.
    • FIRST_ROWS : Optimizer, ilk birkaç sıranın hızlı teslimatı için en iyi planı bulmak için maliyet ve sezgisel bir karışım kullanır. FIRST_ROWS geriye dönük uyumluluk ve plan istikrarı için mevcuttur; bunun yerine FIRST_ROWS_n kullanın.
  • PGA_AGGREGATE_TARGET : Hash join ve sıralamalar için ayrılan RAM miktarını kontrol eder.
  • STAR_TRANSFORMATION_ENABLED : star (yıldız) sorgularına cost based bir sorgu dönüşümünün uygulanıp uygulanmayacağını belirler.

https://docs.oracle.com/cd/E25178_01/server.1111/e16638/optimops.htm
https://emrahmete.wordpress.com/2012/10/20/veriye-etkin-erisim-yontemleri-1/
https://docs.oracle.com/database/121/DWHSG/qrbasic.htm#DWHSG-GUID-D43AE3AB-0778-49EA-9C8D-983F0910FE05
http://www.oganozdogan.com/2011/05/sql-nedir-parse-call-ve-plan-optimizer.html
http://oracleguard.com/
https://hakkioktay.wordpress.com/2007/03/15/join-islemi-uzerine/

Mustafa Bektaş Tepe
İyi Çalışmalar

1,707 total views, 27 views today