İçindekiler
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.
İyi çalışan bir sorgu öncelikle kullanıcıya en uygun sürede hizmetin verilmesi için gereklidir Ö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.
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.
select ad,soyad from calisan where calisan_id=765;
“Bind”da ise aşağıda belirtildiği gibi bir parametre kullanımı vardır.
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ı
SQL Trace istenilen bir andan yine istenilen bir ana kadar olan zaman dilimi içersinde session ya da veritabanı seviyesinde gerçekleşen tüm olayların CPU da harcanan zaman, parse-execute-fetch aşamalarında gecen süre ve adet bilgileri , I/O miktarı gibi SQL in performansı ile direk alakalı bilgilerin okunabildiği ortamdır. 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, parametre dosyasında(init.ora) bulunan user_dump_dest parametresi ile 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
ALTER SESSION SET sql_trace=TRUE; ALTER SESSION SET sql_trace=FALSE;
EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE); EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8'; ALTER SESSION SET EVENTS '10046 trace name context off';
oradebug setmypid
oradebug EVENT 10046 trace name context forever, level 12 oradebug event 10046 trace name context off
- Başka session için TRACE işlemi
İzlenmesi istenen sesssion’a ait SID ve SERIAL# bilgileri gv$session’dan elde edilir.
EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE); EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);
EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>''); EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>'');
ORADEBUG SETOSPID 1234; -- İsletim sistemi proses numarası ile debug başlatma ORADEBUG SETORAPID 123456; -- Oracle process ID’si ile debug başlatma
EXEC DBMS_SUPPORT.start_trace_in_session(sid=>123, serial=>1234, waits=>TRUE, binds=>FALSE); EXEC DBMS_SUPPORT.stop_trace_in_session(sid=>123, serial=>1234);
NOT : DBMS_SUPPORT paketi, varsayılan olarak yoktur, “dbmssupp.sql” komut dosyasını çalıştırarak SYS kullanıcısı olarak yüklenebilir.
@$ORACLE_HOME/rdbms/admin/dbmssupp.sql
NOT : trace açarken kullanabileceğimiz level(seviyeler) aşağıdaki gibidir.
- 0 : Trace yok.sql_trace’i kapatmak için kullanılır.
- 2 : sql_trace=true deyiminin karşılığına denk gelir
- 4 : level 2 ile aynı ek olarak sql lerin bind değerlerinide gösterir.
- 8 : level 2 ile aynı ek olarak wait events(bekeleme olaylarını) da gösterir.
- 12 : level 2 ile aynı ek olarak wait event ve bind değerleride gösterir.
NOT : Belirli bir sql için trace almak istersek aşağıdaki ifadeyi kullanabiliriz.
-- SQL Trace (10046) ALTER SYSTEM SET EVENTS 'sql_trace [sql: sql_id] bind=true, wait=true'; 10053 ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:sql_id]';
Trace Dosyalarına Tanımlama
TRACEFILE_IDENTIFIER parametresi session düzeyinde trace dosyasının ismini ayarlayabilmenizi sağlar.
ALTER SESSION SET TRACEFILE_IDENTIFIER = "MY_TEST_SESSION";
Trace dosyamıza isim vermemiş olsak dahi aşağıdaki sorguyla trace dosyamızın ismini öğrenebiliriz.
SET LINESIZE 100 COLUMN trace_file FORMAT A60 SELECT s.sid, s.serial#, pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) || '_ora_' || p.spid || '.trc' AS trace_file FROM v$session s, v$process p, v$parameter pa WHERE pa.name = 'user_dump_dest' AND s.paddr = p.addr AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
veya
oradebug tracefile_name
NOT : Bir session’ın sid numarasını bulurken gv$session viewinden veya aşağıdakilerden herhangi birini kullanabiliriz.
select sys_context('USERENV','SID') from dual;
veya
select to_number(substr(dbms_session.unique_session_id,1,4),'XXXX') mysid from dual;
veya
select * from v$mystat where rownum <=1;
veya
select distinct sid from v$mystat;
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.
<pre>
@$ORACLE_HOME/rdbms/admin/utlxplan
2. İzlenecek sorgu SQL*Plus’tan aşağıdaki şekilde çalıştırılır.
EXPLAIN PLAN SET STATEMENT_ID = 'TEST' FOR select * from dba_objects where object_id=100;
3. Plan, aşağıdaki sorgu ile görüntülenir.
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='TEST' connect by prior id=parent_id and statement_id='TEST';
Yada aşağıdaki gibi alternatif bir kullanış yönteminide kullanabiliriz.
EXPLAIN PLAN FOR select * from dba_objects where object_id=100;
@$ORACLE_HOME/rdbms/admin/utlxpls.sql
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 OFF
|
Default ayardır, autotrace raporu oluşturulmaz |
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 ON | Sorgu çalıştırılır, sonucu, explain plan’ı ve istatistikleri görüntülenir |
SET AUTOTRACE TRACE | Sorgunun explain plan’ı ve istatistikleri görüntülenir (sorgu çalıştırılır ama sonucu görüntülenmez) |
NOT : set timing on ile de SQL’ın response süresini öğrenebiliriz.
set lines 3000 SET TIMING ON set autotrace TRACE select * from dba_objects where object_id=100; Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 1318426500 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 2 | 414 | 5 (0)| 00:00:01 | | 1 | VIEW | DBA_OBJECTS | 2 | 414 | 5 (0)| 00:00:01 | | 2 | UNION-ALL | | | | | | |* 3 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 10 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 30 | 3 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 | |* 7 | FILTER | | | | | | | 8 | NESTED LOOPS | | 1 | 122 | 5 (0)| 00:00:01 | | 9 | NESTED LOOPS | | 1 | 106 | 4 (0)| 00:00:01 | |* 10 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 84 | 3 (0)| 00:00:01 | |* 11 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | I_USER2 | 1 | 22 | 1 (0)| 00:00:01 | | 13 | TABLE ACCESS CLUSTER | USER$ | 1 | 16 | 1 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 | | 15 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:00:01 | |* 16 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 | |* 17 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 | |* 18 | FILTER | | | | | | | 19 | NESTED LOOPS | | 1 | 104 | 3 (0)| 00:00:01 | | 20 | TABLE ACCESS STORAGE FULL | LINK$ | 1 | 88 | 2 (0)| 00:00:01 | | 21 | TABLE ACCESS CLUSTER | USER$ | 1 | 16 | 1 (0)| 00:00:01 | |* 22 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 9 consistent gets 0 physical reads 0 redo size 1608 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
4. GATHER_PLAN_STATISTICS Hint Kullanımı
GATHER_PLAN_STATISTICS hinti kullanarak çalışma zamanı istatistiği toplatabiliriz.
SET LINESIZE 1000 SET PAGES 100 SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM dba_objects WHERE object_id = 100;
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (NULL, NULL, 'ALLSTATS,BYTES,COST'));
Evet ben çalışma zamanı ile ilgili istatistikleri gösterirken sadece belirli kolonları göstermek için ‘ALLSTATS,BYTES,COST’ formatlarını kullandım ancak istenildiği takdirde bir çok ek istatistikte çeşitli formatlar kullanılarak sorgu ek sorgu istatistikleride görüntülenebilir. Bu formatlar ile iligili linkteki makale takip edilebilir. Şimdi kısaca listelenen istatistiklerin kısaca ne olduklarını söyleyelim.
• Starts: İlgili işlemin kaç kez başlatıldığını gösterir.
• E-Rows: Oracle’ın sorgu çalıştırmadan önce ilgili işlemden geçecek kayıt sayısı ile ilgili tahmini. (Bu tahmini hesaplamak için tablo istatistiklerini kullanıyor)
• E-Bytes: Oracle tarafından sorgu çalıştırılmadan önce ilgili işlemden geçecek datanın büyüklüğünün tahmini.
• Cost: İşlemin CBO (Cost Based Optimizer) tarafından hesaplanmış maliyeti.
• A-Rows: Sorgu çalışırken o işlemden geçen gerçek satır sayısı.
• A-Time: İlgili işlemin sorgunun çalışma zamanında ne kadar sürdüğü.
• Buffers: Çalışma zamanında memoryden yapılan logical read sayısı.
• Reads: İlgili işlemin çalışma esnasında yaptığı fiziksel okuma sayısı.
Sorgu istatistiklerinin gösterdiği değerleri inceleyerek belli yorumlarda bulunup sorgu iyileştirmek oldukça olası bir durum. Örneğin sorgu öncesinde hesaplanan satır sayısı ile sorgu sonrasında hesaplanan satır sayısı (E-Rows A-Rows) arasında çok büyük bir uçurum var ise bu sorguyu oluşturan tabloların istatistiklerinin yeniden toplanması gerektiği ile ilgili bir yorum getirilebilir.
SQL ID kullanarak da analiz edebiliriz.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f1c0ffsmyvnc3',0,'ALL'));
5. 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.
SELECT LPAD (' ', LEVEL) || operation operations, object_name, options, CARDINALITY FROM (SELECT * FROM v$sql_plan WHERE sql_id = 'f1c0ffsmyvnc3' AND child_number = 0) CONNECT BY PRIOR id = parent_id START WITH id = 0 ORDER BY id, position; 23 rows selected. Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 59174112 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 206 | 1 (100)| 00:00:01 | | 1 | SORT ORDER BY | | 2 | 206 | 1 (100)| 00:00:01 | |* 2 | CONNECT BY NO FILTERING WITH START-WITH| | | | | | |* 3 | FIXED TABLE FIXED INDEX | X$KQLFXPL (ind:4) | 1 | 184 | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(TO_NUMBER(DECODE("KQLFXPL_OPID",0,NULL,TO_CHAR("KQLFXPL_PAID")))=PRIOR "ID") filter("KQLFXPL_OPID"=0) 3 - filter("KQLFXPL_SQLID"='f1c0ffsmyvnc3' AND "KQLFXPL_CHNO"=0 AND "INST_ID"=USERENV('INSTANCE')) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 1677 bytes sent via SQL*Net to client 531 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 23 rows processed
NOT : 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.
http://www.e-kitap.org/OraclePerformansIyilestirme_No1.html
https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof
https://emrahmete.wordpress.com/2016/03/25/sorgu-optimizasyonu-icin-calisma-zamani-istatistiklerinin-incelenmesi/
Mustafa Bektaş Tepe
İyi Çalışmalar