Oca/17

Oracle Veritabanında SQL İzleme Yöntemleri

Yazan : Mustafa Bektaş Tepe Kategori : Oracle

İç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 =&amp;gt; TRUE);
EXEC DBMS_SESSION.set_sql_trace(sql_trace =&amp;gt; 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=&amp;gt;123, serial#=&amp;gt;1234, sql_trace=&amp;gt;TRUE);
EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=&amp;gt;123, serial#=&amp;gt;1234, sql_trace=&amp;gt;FALSE);

EXEC DBMS_SYSTEM.set_ev(si=&amp;gt;123, se=&amp;gt;1234, ev=&amp;gt;10046, le=&amp;gt;8, nm=&amp;gt;'');
EXEC DBMS_SYSTEM.set_ev(si=&amp;gt;123, se=&amp;gt;1234, ev=&amp;gt;10046, le=&amp;gt;0, nm=&amp;gt;'');

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=&amp;gt;123, serial=&amp;gt;1234, waits=&amp;gt;TRUE, binds=&amp;gt;FALSE);
EXEC DBMS_SUPPORT.stop_trace_in_session(sid=&amp;gt;123, serial=&amp;gt;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 &amp;lt;=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