Bu makalemizde SQL izleme yöntemlerini ve iyileştirme senaryolarına bakış açımızı irdeleyeceğiz.Çoğu kez raporlarımızın saatlerce çalıştığı durumlar olmuştur veya çok kısa sürede çalışması gereken sorgularımızın neden uzun süre çalıştığını merak etmişizdir.Şimdi bu tip problemler için çözüm yöntemlerine kısaca bi bakalım

Neden SQL İzleme-İyileştirme?

SQL iyileştirme işlemi ile genel olarak şunlar hedeflenmektedir

• 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

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

• 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

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

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

1. İ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;

1. 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”