>ORACLE Tuning Advisor

>ORACLE PERFORMANS İYİLEŞTİRME
#1 – SQL Sorgu Çalışma Planının İzlenmesi
Yazar: Gizem Gürsel
© : http://www.oracledanismanlik.com ve E-Kitap.org

Her hakkı saklıdır.

Oracle veritabanında çalıştırılan her SQL sorgusu, bir çalıştırma planı (execution plan) doğrultusunda işletilir. Bu plan ile, hangi indekslere (varsa ve uygunsa) erişileceği, hangi tip “join” işlemlerinin gerçekleştirileceğine karar verilir. Çalışma planı, bir yerden bir yere giderken izlenecek birçok yol arasında en hızlı ulaşımı sağlayacak güzergahın seçilmesi olarak da düşünülebilir. Doğru seçilmemiş bir güzergah nedeniyle, 10 dakikada gidilebilecek bir yere 1 saatte ulaşılabilir1.

Neden SQL İzleme-İyileştirme?

İyi çalışan bir sorgu öncelikle kullanıcıya en uygun sürede hizmetin verilmesi için gereklidir. Kimlik Paylaşım Sistemi kullanılarak bir TC kimlik numarası sorgulaması yapılıyorsa, kullanıcının cevabını çok hızlı şekilde alması gerekmektedir. Öte yandan, mevcut donanım kaynaklarının verimli şekilde kullanımı için de sorguların iyi çalışması gerekmektedir. Kötü çalışan bir sorgu; disk, bellek ve CPU açısından da darboğazlara yol açabilmektedir. Sorgular üzerinde bazı hallerde yapılacak ufak rötuşlar bile çok önemli performans kazançları sağlayabilmektedir.

SQL iyileştirme işlemi ile genel olarak şunlar hedeflenmektedir2 :

*

Büyük tablolar üzerindeki gereksiz “full-table” erişimlerin indeksli erişime dönüştürülmesi,
*

Küçük tablolar üzerindeki “full-table” erişimleri cach’lemek,
*

İndeks kullanımının optimum düzeyde olduğunu garantilemek,
*

Optimal JOIN teknikleri kullanmak,
*

Tune complex subqueries to remove redundant access

Ne Zaman SQL İzleme-İyileştirme?

Bir SQL sorgusunun iyileştirilmesini çeşitli koşullar tetikleyebilir. Bunlar arasında kullanıcı tarafından işlemin uzun sürdüğü şeklinde yapılan geri bildirimler olabileceği gibi, yapılan izleme, istatistik toplama çalışmaları kapsamında sorgunun fazla kaynak kullandığının tespit edilmesi de yer alabilir. Üzerinde çalışılması gereken sorguya karar verildiğinde, inceleme ve test çalışmalarının, olabildiği ölçüde uygulamadan ve diğer ara katmanlardan bağımsız olarak gerçekleştirilmesi yararlı olacaktır. Buradaki amaç uygulamanın kendi yapısından kaynaklanan farklı işlemleri devre dışı bırakarak, sadece SQL’in optimizasyonuna yoğunlaşılmasıdır. Bu nedenle, kullanılan izleme araçları ile, sorunlu olduğu düşünülen SQL sorgusu tespit edildikten sonra doğrudan sorgu üzerinde çalışılmalıdır.

Sorgularda dikkat edilmesi gereken bir diğer husus da, “literal” veya “bind” kullanımıdır. “Literal”de, aşağıdaki örnekte olduğu gibi, where koşulu içinde bir değer (value) belirtilmiştir.

SQL> select ad,soyad from calisan where calisan_id=765;

“Bind”da ise aşağıda belirtildiği gibi bir parametre kullanımı vardır (B1)3.

SQL> select ad,soyad from calisan where calisan_id=:B1;

“Bind” ve “literal” kullanımda farklı çalışma planları ortaya çıkabileceğinden, orijinal sorguda hangisi kullanılmışsa, iyileştirme çalışmalarının da bunun üzerinde yoğunlaşması gerekir.

Production ortamındaki veritabanlarını olabildiği ölçüde yansıtan test veritabanları mevcut ise, sorgu iyileştirme çalışmalarının test veritabanlarında yapılması daha sağlıklı olacaktır. Her sorgudan önce buffer cache’in flush edilmesi veya veritabanının kapatılıp açılması ile daha doğru ölçüm değerleri elde edilebilecektir.

SQL İzleme Yöntemleri
1. TRACE Kullanımı

TRACE, bir sorgunun, batch işlemin veya tüm sistemin ölçümü için kullanılabilecek bir yöntemdir. Sistem üzerindeki darboğazların oluştuğu noktaların tespiti için yararlı olan geniş kapsamlı bir yöntemdir. TRACE4;

*

Sorguyu çalıştırır, çalışan sorguyla ilgili olarak istatistik üretir,
*

Uygulama geliştiricilerin sorgunun her bir bölümünü analiz etmesine yardımcı olur.

TRACE ile yapılan izlemede, user_dump_dest initora parametresi ilen belirtilen dizine ora_nnnnn.trc söz diziminde bir trace dosyası oluşturulur.

TRACE’in bulunulan session’dan başlatılması mümkün olduğu gibi, bir başka session’ın çalışması da izlenebilir.

Bulunulan session için TRACE işlemi :

SQL> alter system set timed_statistics=true;

SQL> alter session set max_dump_file_size=20000;

SQL> show parameter user_dump

NAME TYPE VALUE

—————- ——- ——————–

user_dump_dest string /usr/oracle10/rdbms/log/udump

SQL> alter session set SQL_TRACE true;

SQL> select MUSTERI_ADI from MUSTERI where MUSTERI_NO=1;

…..

SQL> select * from dual;

SQL> alter session set SQL_TRACE false;

Dump dosyasının oluştuğu dizinde, ilgili trace dosyasının içeriği incelenir.

Dump file /usr/oracle10/rdbms/log/udump/tst1_ora_1105.trc

……

=====================

PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=164924072960 hv=789637826 ad=’7b4fa270′

alter session set sql_trace true

END OF STMT

EXEC #1:c=1952,e=2048,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=164924072960

*** SESSION ID:(1633.1093) 2007-05-16 13:55:46.154

=====================

PARSING IN CURSOR #5 len=28 dep=0 uid=0 oct=3 lid=0 tim=164932504576 hv=4187904027 ad=’7b29b108′

select MUSTERI_ADI from MUSTERI where MUSTERI_NO=1;

……

Başka session için TRACE işlemi :

İzlenmesi istenen sesssion’a ait SID ve SERIAL# bilgileri elde edilir (Örnek olarak TEST kullanıcısı seçilmiştir).

SQL> alter system set timed_statistics=true;

SQL> alter session set max_dump_file_size=20000; — OS block

SQL> select SID, SERIAL# from v$session where username=’TEST’;

SID SERIAL#

———- ———-

163 110

Elde edilen bu SID ve SERIAL# değerleri, aşağıdaki şekilde kullanılarak trace başlatılır.

SQL>execute dbms_system.set_sql_trace_in_session(‘163′,’110’,true);

Trace’in sonlandırılması için aynı komut, false parametresi ile çalıştırılır (Aşağıdaki komu işletilmezse izlenen kullanıcının o session’dan çıkana kadar yapacağı sonraki işlemler de aynı trace dosyasına eklenir).

SQL>execute dbms_system.set_sql_trace_in_session(‘163′,’110’,false);

Dump dosyasının oluştuğu dizinde, ilgili trace dosyasının içeriği incelenir.

Dump file /usr/oracle10/rdbms/log/udump/tst1_ora_1105.trc

……

=====================

PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=164924072960 hv=789637826 ad=’7b4fa270′

alter session set sql_trace true

END OF STMT

EXEC #1:c=1952,e=2048,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=164924072960

*** SESSION ID:(1633.1093) 2007-05-16 13:55:46.154

=====================

PARSING IN CURSOR #5 len=28 dep=0 uid=0 oct=3 lid=0 tim=164932504576 hv=4187904027 ad=’7b29b108′

select MUSTERI_ADI from MUSTERI where MUSTERI_NO=1;

……

2. EXPLAIN PLAN Kullanımı

EXPLAIN PLAN ile, sorgu çalıştırılmaksızın, Oracle optimizer tarafından izlenecek plan görüntülenir. Bu yöntem, sorgunun çok uzun sürdüğü durumlarda tercih edilebilir. Sorgunun süresine, getireceği kayıtlara ihtiyaç olmadan sadece çalışma planının elde edilmesi için yararlıdır.

1.

İzleme işlemini yapacak kullanıcının öncelikle bir PLAN_TABLE tablosuna sahip olması gerekir. Genellikle $ORACLE_HOME/rdbms/admin dizini altına yer alan utlxplan.sql script’i, izleme işlemini yapacak kullanıcı tarafından SQL*Plus’tan çalıştırılır.

SQL> @utlxplan

2.

İzlenecek sorgu SQL*Plus’tan aşağıdaki şekilde çalıştırılır.

SQL> explain plan

set statement_id=’MUSTERI’ for

select MUSTERI_ADI

from MUSTERI

where MUSTERI_NO=1;

3.

Plan, aşağıdaki sorgu ile görüntülenir.

SQL> select lpad(‘ ‘,2*(level-1)) || operation || ‘ ‘

|| options || ‘ ‘ || object_name || ‘ ‘ ||

Decode(id,0, ‘Cost = ‘ || position) “Query Plan”

from PLAN_TABLE

start with id=0 and

statement_id=’MUSTERI’

connect by prior id=parent_id

and statement_id=’MUSTERI’;

Query Plan

—————————————————————-

SELECT STATEMENT Cost = 220

TABLE ACCESS BY INDEX ROWID MUSTERI

INDEX RANGE SCAN MUSTERI_IDX_NO

Daha basit planlar için aşağıdaki sorgu formatı da kullanılabilir.

SQL> select operation, options, object_name, parent_id

from PLAN_TABLE

where statement_id=’MUSTERI’

order by id;

OPERATION OPTIONS OBJECT_NAME PARENT_ID

——————– ————— ————— ———-

SELECT STATEMENT

TABLE ACCESS BY INDEX ROWID MUSTERI 0

INDEX RANGE SCAN MUSTERI_IDX_NO 1

3. AUTOTRACE Kullanımı

AUTOTRACE özelliği, sorgunun EXPLAIN PLAN’ının yansıra TRACE ve TKPROF’da yer alan istatistiklerin çoğunu (fiziksel okuma sayısı, toplam okuma vb) verir. AUTOTRACE’in aşağıdaki şekilde parametreleri vardır:

SET AUTOTRACE ON

Sorgu çalıştırılır, sonucu, explain plan’ı ve istatistikleri görüntülenir

SET AUTOTRACE ON EXP

Sadece explain plan görüntülenir

SET AUTOTRACE ON STAT

Sadece istatistikler görüntülenir (sorgu çalıştırılır ama sonucu görüntülenmez)

SET AUTOTRACE TRACE

Sorgunun explain plan’ı ve istatistikleri görüntülenir (sorgu çalıştırılır ama sonucu görüntülenmez)

SQL> SET AUTOTRACE ON

SQL> select count(name) from scott.emp

where name=’JOHN’;

Count(Name) — Sorgunun Sonucu

———–

10

Query Plan — Sorgunun Çalışma Planı

———-

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE

2 1 INDEX (RANGE SCAN) OF ‘EMP_IDX’ (NON_UNIQUE)

Statistics — Sorguyla İlgili İstatistik

———-

0 recursive calls

0 db block gets

1 consistent gets

1 physical reads

0 redo size

223 bytes sent via SQL*Net to client

274 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disks)

1 rows processed

4. V$SQL_PLAN Kullanımı

İyileştirilmesi istenen sorgunun izlenmesinin yanı sıra, o an çalışmakta olan bir sorgunun çalışma planının alınmasında da kullanılabilecek bir başka yöntem de V$SQL_PLAN view’ının kullanımıdır. Bu yöntemle, TEST adlı kullanıcının çalıştırmakta olduğu sorgunun nasıl izlenebileceği aşağıda örneklenmiştir.

*

SYS kullanıcısı, TEST kullanıcısının SID bilgisini aşağıdaki şekilde alır.

SQL> select SID from v$session where username=’TEST’;

SID

———-

1628

*

Alınan bu SID bilgisi aşağıdaki sorguya parametre olarak verilir.

SQL> select sql_id, sql_child_number from v$session

where sid=1628;

SQL_ID SQL_CHILD_NUMBER

————- —————-

9y8jx3vvy33d8 0

TEST kullanıcısının çalıştırmakta olduğu SQL’in değerleri yukarıdak sorgu ile alındıktan sonra, aşağıdaki sorguya parametre olarak verilir.

SQL> set pages 20000

SQL> set lines 120

SQL> column operations format a30

SQL> column object_name format a25

SQL> column options format a15

SQL>

select lpad(‘ ‘, level ) || operation operations,

object_name, options, cardinality

from (select * from v$sql_plan

where sql_id=’9y8jx3vvy33d8′

and child_number=0)

connect by prior id=parent_id

start with id=0

order by id, position;

OPERATION OPTIONS OBJECT_NAME PARENT_ID

——————– ————— ————— ———-

SELECT STATEMENT

TABLE ACCESS BY INDEX ROWID MUSTERI 0

INDEX RANGE SCAN MUSTERI_IDX_NO 1

*

Çalıştırılan sorgu “bind” kullanıyorsa, bu durumda hangi gerçek değerlerle sorgunun çalıştırıldığı da, aşağıdaki sorgu ile görüntülenir.

SQL>

col name format a10

col value_string format a50

select name, datatype, value_string

from v$sql_bind_capture

where sql_id=’9y8jx3vvy33d8′ and child_number=0

order by position;

Bind Kullanımı

İzlenen SQL sorgusunda “bind” kullanımı varsa, yapılan iyileştirme çalışmaları kapsamında yine bind kullanımının simule edilmesi gereklidir. “Bind” kullanımında parametrelere nasıl değer aktarılacağı aşağıda örneklenmiştir.

Orijinal sorgunun aşağıdaki gibi A1 ve A2 ile temsil eden bind parametrelerle çalıştırıldığını varsayalım.

select count(*) from MUSTERI

where bolge=:A1 and musteri_adi=:A2;

SQL*Plus’tan yapılacak izleme çalışması öncesinde aşağıdaki şekilde tanımlamalar yapılmalıdır.

SQL> variable A1 number;

SQL> variable A2 varchar2(20);

SQL> execute :A1 := 34;

SQL> execute :A2 := ‘YILMAZ’;

Bu tanımlamaların ardından, sorgunun izlenmesine başlanır. Örnekte AUTOTRACE kullanımı yer almaktadır.

SQL> set autotrace trace exp stat

SQL> select count(*) from MUSTERI

Where bolge=:A1 and musteri_adi=:A2;

Execution Plan

———————————————————-

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (FULL) OF ‘MUSTERI’ (TABLE)

Statistics

———————————————————-

0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

613 bytes sent via SQL*Net to client

660 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

DEĞERLENDİRME

SQL izleme yöntemleri arasında yer alan AUTOTRACE ve EXPLAIN PLAN kullanımı, sorgular üzerinde varsayımsal çalışma planı oluşturmaktadır. TRACE ve V$SQL_PLAN ise, sorgu için uygulanan gerçek çalışma planını oluşturur. Bu nedenle TRACE ve SQL$PLAN’ın diğer yöntemlere göre kesin sonuç ürettiği değerlendirmesi yapılabilir. Bununla birlikte, AUTOTRACE, gerek kullanımının kolaylığı gerekse aynı session içinde izlemenin ve sorgu çalıştırma işleminin bir arada yapılabilmesi nedeniyle çoğu durumda tercih edilebilir.

1 Oracle 10g sürümü ile birlikte “rule-based” olarak nitelenen optimizer devre dışı kaldığından bu doküman, “cost-based” optimizer kullanımına göre düzenlenmiştir.

2 Oracle Tuning The Definitive Reference, Donald K.Burleson, Alexey B.Danchenkov, Rampant Techpress, s.555

3 Genel olarak ifade edilmesi gerekirse, bind kullanımı, kullanılan parametre değerleri dışında herşeyi aynı olan sorguların tekrar parse edilmesi gerekliliğini ortadan kaldırır, böylece parse zamanından ve CPU kullanımından tasarruf sağlar.

4 Oracle 9i Performance Tuning Tips&Techniques, Richard J.Niemiec, Oracle Press, s.206.
——–

Burada yer alan bilgi ve belgelerin kullanımı birden çok kişi ve kurumu ilgilendirebilmektedir. Bu nedenle hiçbir biçimde yazılı, görsel ve sayısal ortamlarda çoğaltılmaması gerekmekte yalnızca E-Kitap.org sitesi üzerinden kullanılması gerekmektedir. Buradaki bilgilerin kullanılmasından ötürü çıkabilecek hiçbir zarar E-Kitap.org’u bağlamaz. Kullanıcılar bunu peşinen kabul etmiş sayılırlar.

http://www.e-kitap.org/OraclePerformansIyilestirme_No1.html

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s