Bu makalemizde SQL izleme yöntemlerini ve iyilestirme senaryolarina bakis açimizi irdeleyecegiz.Çogu kez raporlarimizin saatlerce çalistigi durumlar olmustur veya çok kisa sürede çalismasi gereken sorgularimizin neden uzun süre çalistigini merak etmisizdir.Simdi bu tip problemler için çözüm yöntemlerine kisaca bi bakalim
Neden SQL Izleme-Iyilestirme?
SQL iyilestirme islemi ile genel olarak sunlar hedeflenmektedir
- Büyük tablolar üzerindeki gereksiz “full-table” erisimlerin indeksli erisime dönüstürülmesi,
- Küçük tablolar üzerindeki “full-table” erisimleri cach’lemek,
- Indeks kullaniminin optimum düzeyde oldugunu garantilemek,
- Optimal JOIN teknikleri kullanmak,
- Tune complex subqueries to remove redundant access
Ne Zaman SQL Izleme-Iyilestirme?
Bir SQL sorgusunun iyilestirilmesini çesitli kosullar tetikleyebilir. Bunlar arasinda kullanici tarafindan islemin uzun sürdügü seklinde yapilan geri bildirimler olabilecegi gibi, yapilan izleme, istatistik toplama çalismalari kapsaminda sorgunun fazla kaynak kullandiginin tespit edilmesi de yer alabilir. Üzerinde çalisilmasi gereken sorguya karar verildiginde, inceleme ve test çalismalarinin, olabildigi ölçüde uygulamadan ve diger ara katmanlardan bagimsiz olarak gerçeklestirilmesi yararli olacaktir. Buradaki amaç uygulamanin kendi yapisindan kaynaklanan farkli islemleri devre disi birakarak, sadece SQL’in optimizasyonuna yogunlasilmasidir. Bu nedenle, kullanilan izleme araçlari ile, sorunlu oldugu düsünülen SQL sorgusu tespit edildikten sonra dogrudan sorgu üzerinde çalisilmalidir.
Sorgularda dikkat edilmesi gereken bir diger husus da, “literal” veya “bind” kullanimidir. “Literal”de, asagidaki örnekte oldugu gibi, where kosulu içinde bir deger (value) belirtilmistir.
SQL> select ad,soyad from calisan where calisan_id=765;
“Bind”da ise asagida belirtildigi gibi bir parametre kullanimi vardir
SQL> select ad,soyad from calisan where calisan_id=:B1;
“Bind” ve “literal” kullanimda farkli çalisma planlari ortaya çikabileceginden, orijinal sorguda hangisi kullanilmissa, iyilestirme çalismalarinin da bunun üzerinde yogunlasmasi gerekir.
Production ortamindaki veritabanlarini olabildigi ölçüde yansitan test veritabanlari mevcut ise, sorgu iyilestirme çalismalarinin test veritabanlarinda yapilmasi daha saglikli olacaktir. Her sorgudan önce buffer cache’in flush edilmesi veya veritabaninin kapatilip açilmasi ile daha dogru ölçüm degerleri elde edilebilecektir.
SQL Izleme Yöntemleri
1. TRACE Kullanimi
TRACE, bir sorgunun, batch islemin veya tüm sistemin ölçümü için kullanilabilecek bir yöntemdir. Sistem üzerindeki darbogazlarin olustugu noktalarin tespiti için yararli olan genis kapsamli bir yöntemdir. TRACE
- Sorguyu çalistirir, çalisan sorguyla ilgili olarak istatistik üretir,
- Uygulama gelistiricilerin sorgunun her bir bölümünü analiz etmesine yardimci olur.
TRACE ile yapilan izlemede, user_dump_dest initora parametresi ilen belirtilen dizine ora_nnnnn.trc söz diziminde bir trace dosyasi olusturulur.
TRACE’in bulunulan session’dan baslatilmasi mümkün oldugu gibi, bir baska session’in çalismasi da izlenebilir.
Bulunulan session için TRACE islemi :
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 dosyasinin olustugu dizinde, ilgili trace dosyasinin içerigi 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;
……
Baska session için TRACE islemi :
Izlenmesi istenen sesssion’a ait SID ve SERIAL# bilgileri elde edilir (Örnek olarak TEST kullanicisi seçilmistir).
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# degerleri, asagidaki sekilde kullanilarak trace baslatilir.
SQL>execute dbms_system.set_sql_trace_in_session('163','110',true);
Trace’in sonlandirilmasi için ayni komut, false parametresi ile çalistirilir (Asagidaki komu isletilmezse izlenen kullanicinin o session’dan çikana kadar yapacagi sonraki islemler de ayni trace dosyasina eklenir).
SQL>execute dbms_system.set_sql_trace_in_session('163','110',false);
Dump dosyasinin olustugu dizinde, ilgili trace dosyasinin içerigi 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 Kullanimi
EXPLAIN PLAN ile, sorgu çalistirilmaksizin, Oracle optimizer tarafindan izlenecek plan görüntülenir. Bu yöntem, sorgunun çok uzun sürdügü durumlarda tercih edilebilir. Sorgunun süresine, getirecegi kayitlara ihtiyaç olmadan sadece çalisma planinin elde edilmesi için yararlidir.
- Izleme islemini yapacak kullanicinin öncelikle bir PLAN_TABLE tablosuna sahip olmasi gerekir. Genellikle $ORACLE_HOME/rdbms/admin dizini altina yer alan utlxplan.sql script’i, izleme islemini yapacak kullanici tarafindan SQL*Plus’tan çalistirilir.
SQL> @utlxplan
- Izlenecek sorgu SQL*Plus’tan asagidaki sekilde çalistirilir.
SQL> explain plan
set statement_id=’MUSTERI’ for
select MUSTERI_ADI
from MUSTERI
where MUSTERI_NO=1;
- Plan, asagidaki sorgu ile görüntülenir.
SQL> select lpad(‘ ‘,2*(level-1)) || operation || ‘ ‘
|| options || ‘ ‘ || object_name || ‘ ‘ ||
Decode(id,0, ‘Cost = ‘ || position) “Query Plan”
