PERFORMANS TUNING -3- SQL TRACE ve TKPROF

Mevcut bağlı olduğumuz session veya herhangi bir aktif session için sql durumlarını trace dosyaları üzerinden anlamlı hale getirerek takip etmek, bazı performans problemlerini yakalayabilmek amacıyla kullanılan faydalı bir araçtır.

Trace dosyalarımızın yeri için;

SQL> show parameter user_dump_dest

NAME            TYPE        VALUE
--------------- ----------- ---------------------------------------------
user_dump_dest  string      /u01/app/oracle/product/19.0.0.0/db/rdbms/log


SQL> select value from v$parameter where name='user_dump_dest';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/product/19.0.0.0/db/rdbms/log

SQL Trace İçeriği bize SQL durumu ile ilgili detaylı bilgiler sunacaktır.

  • Parse, execute, and fetch counts
  • CPU times & elapsed times
  • Physical reads & logical reads
  • Number of rows processed
  • Misses on the library cache
  • Username under which each parse occurred
  • Each commit and rollback

Sql Trace session bazlı veya Instance bazlı olarak enable edilebilir. Enable yapıldığında o session veya instance için tüm sql ifadelerinin istatistik bilgilerini trace dosyalarına yazacaktır. Sql trace enable yapılacağı zaman production ortamlar için aşağıdaki değerlere dikkat etmek gerekmektedir;

  • CPU kapasitesine (%25 CPU kullanımı artırabilir)
  • USER_DUMP_DEST doluluk oranına.
  • Yeterli disk alanı olduğundan emin olunuz.

Sql Trace etkinleştirdik diyelim bu trace dosyalarının okunması için bir araç gerekmektedir, bu araç TKPROF Programıdır.

SQL Trace açalım; eğer mevcut bulunduğunuz session için bu işlemi yapacaksak aşağıdaki gibi sqltrace enable edilir. Takibi yapılacak ifade execute edilir ve trace dosyası kapatılır.

--Trace dosyasına isim verilebilir aşağıdaki gibi

USER is "TEST"
SQL>
SQL> alter session set tracefile_identifier = MY_TRACE;

--trace işlemi mevcut session için enable edilir;

ALTER SESSION SET sql_trace = true;

--Trace edeceğimiz sql i çalıştıralım;

SQL> select * from log724db.tbl_digital_log where tag_id=1215

--Trace kapatılır

alter session set sql_trace=false;

Şimdi TKPROF ile dosyamızı okunabilir bir txt dosyası şekline getirelim

$ tkprof essodb_ora_71991_MY_TRACE.trc MY_TRACE.txt explain=TEST/Test123

$ more MY_TRACE.txt

SQL ID: 5x09tct46f25t Plan Hash: 2771471584

select *
from
 log724db.tbl_digital_log where tag_id=1215


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3     17.19      27.09     980599    2943682          1           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       11     17.19      27.09     980599    2943682          1           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 106
Number of plan statistics captured: 2

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  PARTITION RANGE ALL PARTITION: 1 1048575 (cr=981302 pr=490300 pw=0 time=10972068 us starts=1 cost=243941 size=345408 card=12336)
         0          0          0   PARTITION RANGE ALL PARTITION: 1 LAST (cr=981302 pr=490300 pw=0 time=10971998 us starts=92 cost=243941 size=345408 card=12336)
         0          0          0    TABLE ACCESS FULL TBL_DIGITAL_LOG PARTITION: 1 1048575 (cr=981302 pr=490300 pw=0 time=10971776 us starts=112 cost=243941 size=345408 card=12336)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       5        0.00          0.00
  reliable message                                2        0.00          0.00
  enq: KO - fast object checkpoint                2        0.00          0.00
  PGA memory operation                            5        0.00          0.00
  ASM IO for non-blocking poll                10607        0.00          0.01
  direct path read                             3414        0.00         10.00
  SQL*Net message from client                     5       39.91         70.31
********************************************************************************

Sql Trace enable farklı sessionlar için pid değerlerini kullanarak enable edilebilir. Sql trace i enable ederken seviyelerinide ayarlayabiliriz;

Trace Seviyeleri

Level 0 : trace oluşturulmayacak disable anlamındadır.
Level 2 : Standart sql trace dosyası oluşur.
Level 4 : Level 2 üzerine Bind veriable değerlerinide ekler
Level 8: Level 2 üzerine Wait eventleri ekler
Level 12:Level 2 üzerine hem Bind değişken değerlerini hemde Wait eventleri ekler

Kendi Sessionımız için;

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

--Kapatmak için;

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Başka bir session trace enable yapmak için sid ve serial# bilgilerine v$session view inden erişilir. Birçok şekilde trace enable edilebilir.

SQL> EXEC DBMS_SYSTEM.set_ev(si=>310, se=>1453, ev=>10046, le=>12, nm=>' ');
--kapatmak için;
SQL> EXEC DBMS_SYSTEM.set_ev(si=>310, se=>1453, ev=>10046, le=>0, nm=>' ');

--Veya 

SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>310, serial_num=>1453, waits=>TRUE, binds=>FALSE);
--kapatmak için
SQL> EXEC DBMS_MONITOR.session_trace_disable(session_id=>310, serial_num=>1453);

OS PID değeriylede SQL Trace enable edilebilir. PID değerini bulmak için aşağıdaki sql kullanılabilir.

select p.PID,p.SPID,s.SID 
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = '&SESSION_ID'
/

PID değeri ile trace etkinleştirmek için;

SQL>  select p.PID,p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.sid =310;

       PID SPID                            SID
---------- ------------------------ ----------
       226 20735                           310
--SPID Kullanarak

SQL> oradebug setospid 226
SQL> oradebug unlimit
SQL> oradebug setospid 20735
Oracle pid: 226, Unix process pid: 20735, image: [email protected]
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
 
--KAPATMAK İçin
SQL> oradebug event 10046 trace name context off
Statement processed.

VEYA

--PID Kullanarak 
SQL> oradebug setorapid 226
Oracle pid: 226, Unix process pid: 20735, image: [email protected]
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
--KAPATMAK İçin
SQL> oradebug event 10046 trace name context off
Statement processed.