Herhangi bir SQL sorgusu çalıştırıldığında, istenilen veriye nasıl ulaşılacağına Optimizer birleşeni karar vermektedir. Oracle, Rule-Based Optimizer ve Cost-Based Optimizer olmak üzere iki adet optimizasyon seçeneği sunmaktadır.
Veri tabanına ulaşılırken, Rule-Based Optimizer (RBO) ile önceden tanımlanmış kurallar seti kullanılarak hangi yolun izleneceğine karar verilir. Diğer bir yöntem olan Cost-Based Optimizer’ın (CBO) Rule-Based Optimizer‘a göre daha kapsamlı ve karışık bir çalışma yapısı vardır. Kullanılacak olan en iyi yöntemi belirlenirken, çeşitli İstatistik bilgierini (tablo boyutları, kayıt sayıları, verilerin dağılımı vs.) kullanılmaktadır. Cost-Based Optimizer‘ının ihtiyacı olan veriyi sağlamak için veri tabanı objelerinin DBMS_STATS prosedürü kullanılarak analiz edilmeleri ve istatistiklerinin toplatılması gerekmektedir. Eğer bir tablonun analizi yapılmamışsa, Rule-Based Optimizer‘ın kuralları kullanılarak çalışma yolu belirlenir. Aynı sorguda bazı tablolar analiz edilmiş ve bazıları analiz edilmemiş ise, sistem öncelikli olarak Cost-Based Optimizer‘ını kullanmayı tercih edecektir.
Optimizer’a Neden İhtiyaç Duyulur
Aşağıdaki örnekleri incelediğimizde 1. resimde ilgili sorgudaki where condition olan alanda indeximiz mevcut ve optimizer tablo istatistik bilgilerine bakarak bir karar veriyor ve index kullanarak bu veriye ulaşmadın maaliyetinin daha düşük olduğu çıkarımını yapıyor ve yürütme planını buna göre oluşturuyor
Aşağıdaki ikinci reisme bakıldığında ise sorgu aynı sorgu ancak istatistik bilgileri farkllı ve tablonun filterelenen kısmı manager tabldaki katıtların %80 manager ise Bu durumda index kullanması sorguyu yavaşlatacaktır, dolayısıyla optimizer burada index kullanmak yerine Full Table Scan yapacaktır ve bu şekilde çalışacaktır.
Bir SQL çalıştırılırken önce private Sql Area ya oradan Library Cache’ e alınır burada daha sonra göreceğimiz parameterelere göre Library Cachede bu sql e benzer sql varsa buradaki sql plan alınır ve bu sql’e uygulanır, eğere burada bu sql ilk defa geldi veya database tarafıondan ilk defa çalıştırıldığı yorumu yapılırsa, parse edilir plan oluşturulur ve sql bu plana göre çalıştırılır.
SQl Pars aşamaları aşağıdaki gibidir
Oracle veritabanına bir sorgu geldiğinde Shared Pool içindeki Library Cache’de bu sql i bulamaz ise yeniden yürütme planı oluşturması işlemine Hard Pars denir ve veri tabanı açısından maaliyetli (CPU vs.) bir işlemdir. Eğer bir sorgu geldiğinde Library Cache de varsa bu yürütme planı alnırı kullanılır bunda da Soft parse denir.
Aynı sql i kullansakta bazı durumlarda library cachde bu sql yeni sql gibi algılanarak Hard Parse olabilir, buna örnek olarak Bind değişkenlerin kullanımı örnek verilebilir. Bind değişken kullanılmadığı zaman her gelen aynı sql yeni olarak değerlendirlerek Hard Pars yapılacak ve veritabanı performans kaybı yaşayacaktır. Aşağıdaki örnekte bu durum gözlenmektedir. zamanlara bakarsanız bind veriable kullanımı ile zamanın nasıl değiştiği açıkça görülmektedir.
SQL> CREATE TABLE HR.DENEME
(
COL NUMBER NOT NULL);
SQL> DECLARE
v_sql VARCHAR2 (100);
BEGIN
FOR i IN 1 .. 100000
LOOP
v_sql := 'insert into deneme values(' || i || ')';
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:42.10
SQL> rollback;
SQL> DECLARE
v_sql VARCHAR2 (100);
BEGIN
FOR i IN 1 .. 100000
LOOP
v_sql := 'insert into deneme values(:i)';
EXECUTE IMMEDIATE v_sql
USING i;
END LOOP;
END;
/
2 3 4 5 6 7 8 9 10 11
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.08
Query Transformation
CBO aynı zamanda bazı sorguları transforma uğratabilir ve değiştirerek çalıştırabilir buna Query Transformation Denir bu durumlardan bazıları aşağıda verilmiştir. Sorgulardaki değişimleri tek tek açıklamaycağım sorgular ve transformları dikkatli incelenirse sebepleri net bir şekilde anlaşılmaktadır.
1 Transform OR Expansion
SELECT *
FROM employees
WHERE job = 'CLERK' OR department_id = 10;
--TRANSFORM
SELECT *
FROM emp
WHERE job = 'CLERK'
UNION ALL
SELECT *
FROM emp
WHERE department_id = 10 AND job <> 'CLERK';
2. Transformer: Subquery
SELECT *
FROM accounts
WHERE custno IN
(SELECT custno FROM customers);
--TRANSFORM
SELECT accounts.*
FROM accounts, customers
WHERE accounts.custno = customers.custno; --(Cust No Primary Key veya Unique Key)3. Transformer: View Merging
CREATE VIEW emp_10 AS
SELECT empno, ename, job, sal, comm, deptno
FROM emp
WHERE deptno = 10;
SELECT empno FROM emp_10 WHERE empno > 7800;
--TRANSFORM
SELECT empno
FROM emp
WHERE deptno = 10 AND empno > 7800;4.Transformer Predicate Pushing
CREATE VIEW two_emp_tables AS
SELECT empno, ename, job, sal, comm, deptno FROM emp1
UNION
SELECT empno, ename, job, sal, comm, deptno FROM emp2;
--TRANSFORM
SELECT ename
FROM ( SELECT empno, ename, job,sal, comm, deptno
FROM emp1 WHERE deptno = 20
UNION
SELECT empno, ename, job,sal, comm, deptno
FROM emp2 WHERE deptno = 20 );5.Transformer: Transitivity
SELECT *
FROM emp, dept
WHERE emp.deptno = 20 AND emp.deptno = dept.deptno;
--TRANSFORM
SELECT *
FROM emp, dept
WHERE emp.deptno = 20 AND emp.deptno = dept.deptno
AND dept.deptno = 20;
Peki şimdi bu bu Cost-Based Optimizer bu Cost ları nasıl hesaplıyor ve Execution Planı neye göre seçiyor ona bir bakalım;
COST-Based Optimizer (CBO)
Rull Based Optimizer Kurallara göre plan seçerken, Cost Based Optimizer İstatistik bilgilerine göre planları seçecektir. Nedir bu istatistik bilgileri tablodaki kayıt sayısı, veri bloklarının durumu , indeks bilgileri, tablodaki bir satır uzunluğu, adedi gibi bilgilerdir.Tüm bu bilgilere göre CBO, farklı planlar oluşturur ve maliyeti (cost) en az olan execution planı seçer. Bu hesaplamayı yaparken Estimator tarafından COst hesaplanır ve Plan Generator tarafından da Planlar oluşturulur.
Estimator: Selectivity
Selectivity 0.0 ile 1.0 aralığuında bir değer gelecektir; 0 a yaklaştıkça High Selectivity, 1 e yaklaştıkça Low Selectivity kavramları karşımız çıkar.
Estimator: Cardinality
Cardinality=Selectivity x All Row Count
Çalışma planında beklenen satır sayısıdır. Aşağıdaki Örnek bir hesaplama verilmiştir.
SELECT days FROM courses WHERE dev_name = 'ANGEL';
--Tabloda distinct DEV_NAME sayısı 203
--COURSES Tablosunun Toplam Satır sayısı 1018
--Selectivity=1/203
--Cardinalty=(1/203)x1018=5.01Estimator: Cost Cost Hesaplanırken Block durumları, CPU ve diğer hesaplana Estimatorler ile bir algoritmaya sokulur ve bir iş birimi sayısı ortaya çıkar kısaca Cost , veritabanı sorguyu yürüttüğünde ve sonucunu ürettiğinde ortaya çıkması beklenen iş birimlerinin sayısını hesaplandığı bir formüldür.
Plan Generator
Plan Generator çeşitli access paths, join methods, ve join orders yöntemlerini gözden geçirerek bir sql için en uygun planları oluşturur, en iyi planların Maliyerlerine (Cost) bakılarak en uygun maliyetli (Cost) plan seçilir.
SQL> explain plan for
2 select e.last_name, d.department_name from employees e, departments d where e.department_id = d.department_id;
Explained.
SQL> select * from table(dbms_xplan.display);
Plan hash value: 1473400139
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 2862 | 5 (20)| 00:00:01 |
| 1 | MERGE JOIN | | 106 | 2862 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 107 | 1177 | 3 (34)| 00:00:01 |
| 5 | VIEW | index$_join$_001 | 107 | 1177 | 2 (0)| 00:00:01 |
|* 6 | HASH JOIN | | | | | |
| 7 | INDEX FAST FULL SCAN | EMP_DEPARTMENT_IX | 107 | 1177 | 1 (0)| 00:00:01 |
| 8 | INDEX FAST FULL SCAN | EMP_NAME_IX | 107 | 1177 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
6 - access(ROWID=ROWID)
22 rows selected.
Optimizer Davaranılarını Etkileyen Parametreler Aşağıda Verilmiştir
- CURSOR_SHARING: SIMILAR, EXACT, FORCE (Default Similar)
- DB_FILE_MULTIBLOCK_READ_COUNT
- PGA_AGGREGATE_TARGET
- STAR_TRANSFORMATION_ENABLED
- RESULT_CACHE_MODE: MANUAL, FORCE
- RESULT_CACHE_MAX_SIZE
- RESULT_CACHE_MAX_RESULT
- RESULT_CACHE_REMOTE_EXPIRATION
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ı (Optimizera göre) 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.
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. Genelde DSS veritabanlarında enable edilir.
Bir yanıt yazın