ORACLE PERFORMANS İYİLEŞTİRME
#1 - SQL Sorgu Çalışma Planının İzlenmesi
Yazar: Gizem Gürsel
© : 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
Kaydol:
Kayıt Yorumları (Atom)
Hiç yorum yok:
Yorum Gönder