PERFORMANS TUNING 4 – ORACLE OPTIMIZER

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.01

Estimator: 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.