TKPROF ile SQL uygulamalarının performans analizi

TKPROF, SQL izleme(trace) dosyalarını sırasıyla analiz etmek ve bu izleme dosyalarında yer alan bilgilerden okunabilir formda raporlar hazırlamak için işletim sistemi seviyesinde kullanılan bir Oracle yardımcı aracıdır.  TKPROF aracını kullananarak ayrıntıların çağrılması platformdan platforma değişiklik göstermesine rağmen, TKPROF tüm Oracle sürümlerince desteklenmektedir ve tüm Oracle veritabanı sürümlerinde aynı işleve sahiptir.

SQL izlemesi, bütün bir instance için veya bireysel oturumlar için etkinleştirilen veya devredışı bırakılan bir işlemdir. Bir oturum için SQL izlemesi etkinleştirildiğinde, oturumu tutan Oracle prosesi, tüm veritabanı çağrılarını ve operasyonları ile ilgili detaylı bilgileri bir izleme dosyasına yazar. Özel veritabanı olayları, bind variables(bağlaç değişkenler) gibi daha spesifik bilgilerin sırasıyla Oracle’ın izleme dosyasına yazılmasına sebep vermek içinde ayarlanabilir.  

SQL izlemesi, bireysel SQL komutlarındaki performans bilgilerini içerir. Her bir komut için aşağıdaki istatistikleri oluşturur:

• Çözümleme(parse), çalıştırma(execute) ve satır alıp getirme(fetch) sayıları
• CPU süresi ve tamamlama süresi
• Fiziksel okumalar ve mantıksal okumalar
• İşlem gören satır sayısı
• Library cache üzerindeki kayıplar(misses)
• Her bir çözümleme işlemindeki kullanıcı adı
• Her bir commit and rollback işlemi
• Her bir SQL komutu için bekleme olayı ve her bir izleme dosyası için özet bilgisi

Eğer SQL oturumunu için SQL komutunun imleci(cursor) kapanmışsa, SQL izlemesi aynı zamanda aşağıdakiler ile ilgilide satır kaynak bilgilerini sağlayacaktır.

• Her bir SQL komutunun güncel çalışma planını gösteren satır işlemleri
• Satır üzerindeki her bir işlem için tamamlama süresi, satır sayısı, ardışık okumalar(consistent reads) ve fiziksel yazma sayısı 

Bir instance veya oturum için SQL izlemesini etkinleştirmek münkün olmasına rağmen, bunların yerine DBMS_SESSION veya DBMS_MONITOR paketlerinin kullanılması tavsiye edilmektedir. Bir oturum veya instance için SQL izlemesi etkin olduğunda, kullanıcı oturumu veya instance oturumu içinde çalıştırılan tüm SQL komutlarına ait performans istatistikleri izleme dosyalarına yazılır. SQL izlemesinin kullanılması veritabanında şiddetli performans etkisi oluşturabilir ve aşırı CPU kullanımı, yetersiz disk alanı gibi artan sistem yüklerine sebebiyet verebilir.

Aslında, SQL izleme dosyaları text dosyaları olduğundan bir editor programla açıldığında rahatlıkla okunabilir formattadır, yani binary değildir. Ancak, aşırı oranda satırlar tekrarlı olmaktadır, çok ayrıntılıdır ve kısmen karışık formattadır. Örneğin, eğer bir uygulama bir imleç açıp bir seferde tek satır imlecinden 1,000 satır alıp getirirse, izleme dosyası içinde 1,000 tane farklı giriş olacaktır.  Tabii, çıplak gözle bunların ne olduğunu anlamak imkansız hale gelecektir. İşte bu sebeple, izleme dosyasını sırasıyla daha kolayca anlaşılabilecek bir formata dönüştürmek için, işletim sistemi komut satırından çağrılan bir araç olan, TKPROF kullanılır.

Her bir SQL komutunun ne kadar çözümlendiği, çalıştırıldığı ve satır getirdiği(fetch) sayılarıyla birlikte bu raporda gösterilir. CPU süresi, tamamlanma süresi, mantıksal ve fiziksel okumalar ve işlenen satırların library cache içindeki kayıpları ve tekrarlama(recursion) seviyesiyle birlikte ayrıca raporlanır. TKPROF, ayrıca opsiyonel olarak rapor içindeki her bir SQL komutu için, çalışma planının her bir adımında ne kadar satır işlendiği sayısıyla birlikte, bütün bir çalışma planıda(execution plan)içerebilir.

Eğer istenirse, bu TKPROF raporu içinde SQL komutlarının ne kadar kaynak tükettiğide sırasıyla listelenebilir. Ayrıca, SYS kullanıcısı tarafında “data dictionary” objeleri yönetmek için yinelemeli yayınlanan SQL komutlarıda dahil edilebilir veya hariç tutulabilir. TKPROF, izlenen oturumdan SQL komutlarını bir kuyruk(spool) dosyasınada yazabilir.

SQL izlemesini instance seviyesinde etkinleştirmek için SQL_TRACE başlangıç parametresinin TRUE olarak ayarlanması ve veritabanının yeniden başlatılması gerekmektedir. Bu işlemden sonra veritabanında açılan tüm oturumlar SQL izleme modunda olacaktır, yani tüm SQL işlemleri izleme dosyalarına kayıt edilecektir, hatta PMON ve SMON işlemleri bile.

Pratikte SQL izlemesinin instance seviyesinde etkinleştirilmesi genelde uygun olmaz, tüm gerekli gereksiz SQL komutlarının izlenmesi sisteme oldukça yüksek ek yük getirecektir. Bu sebeple sadece performansı etkileyen oturumların izlenmesi ve/veya istenen SQL komutlarının oturum katmanında izlenmesi tavsiye edilir. Oturum katmanında SQL izlemesini etkinleştirmek (TRUE) ve devredışı bırakmak (FALSE) için aşağıdaki komut kullanılmaktadır.

ALTER SESSION SET sql_trace = TRUE | FALSE;

İzlenmesini istediğimiz oturumda ALTER SESSION komutunun yürütülmesinin mümkün olmadığı durumlarda (önceden paketlenmiş uygulamalar gibi), veritabanına DBA kullanıcısı olarak başka bir oturumdan bağlanarak DBMS_SYSTEM paketi ile SQL izlemesi açılabilir veya kapanabilir. Bunu yapmak için V$SESSION görünümüne sorgu çekilerek izlenmesi istenen oturumun SID ve seri numarası bulunabilir ve ardından aşağıdaki gibi DBMS_SYSTEM paketi çalıştırılabilir.

EXECUTE SYS.dbms_system.set_sql_trace_in_session (<SID>, <serial#>, TRUE | FALSE);

Bir oturum için ilk seferinde SQL izlemesi etkinleştirildiğinde, oturumu tutan Oracle sunucusu user_dump_dest başlangıç parametresince belirtilen dizinde izleme dosyasını oluşturur. Oracle sunucusu, veritabanı işlemlerini gerçekleştirmek için uygulama tarafından çağrıldıkça sunucu prosesi izleme dosyasının üzerine yazmaya devam eder.

Multi-thread sunucu(MTS) kullanarak veritabanını izlemek biraz daha karışıktır, çünkü uygulamadaki her bir veritabanı çağrısı farklı sunucu proseslerince toplanacaktır. Bu durumda, her bir sunucu prosesi o proses tarafından gerçekleştirilen işlemler hakkında izleme bilgilerini içeren bir izleme dosyası oluşturacaktır. Yani, uygulamanın veritabanı ile nasıl entegre olduğu hakkında büyük resmi görebilmek için birden çok izleme dosyası birlikte kombine olarak kullanılacaktır. Bununda ötesinde, eğer tek seferde birden çok oturum izleniyorsa, izleme dosyasındaki bir işlemin hangi oturuma ait olduğunun söylenmesi zor olacaktır.  Bu sebepler yüzünden, SQL izlemesi ile veritabanı oturumu izlemesinde “dedicated” sunucu modu kullanılmalıdır.

SQL izleme dosyası detaylı süre bilgisi içermektedir. Varsayılan olarak Oracle 11g sürümünde süre izlemesi etkindir. Eğer süre devredışı bırakılırsa, zaman izlemesi olmayacağından dolayı izleme dosyası içindeki bütün zaman görüntüleri sıfır olarak gözükecektir. Süre bazlı izlemeyi etkinleştirmek için başlangıç parametresindeki parametre aşağıdaki gibi değiştirilmeli ve instance yeniden başlatılmalıdır.  

ALTER SYSTEM SET timed_statistics=true SCOPE=SPFILE;

Oturum katmanında süre bazlı izlemenin etkinleştirilmesi için aşağıdaki komut bu oturumdan çalıştırılır.

ALTER SESSION SET timed_statistics = TRUE | FALSE;

SQL izlemesinin etkinleştirilmesi sonucunda sistem üzerine ek bir yük bineceğinden bahsetmiştik, hatta bazı DBA’ler bunun %25’e kadar performansta olumsuz etki yapacağını belirtmektedirler. Bunun yanında SQL izlemesinin etkinleştirilmesi, gereğinden fazla izleme dosyalarının oluşturulmasına sebebiyet verecektir. Bu sebeplerle, SQL izlemesini gerektiğinde ve kararınca kullanmak gerekmektedir.

Linux/UNIX sistemlerde Oracle temel izinleri ayarlayarak, sadece oracle kullanıcısı ve dba adlı Unix grubu tarafından izleme dosyaları okunabilir. Unix/Linux sistemlerden oturum açan diğer sistem kullanıcılarının izleme dosyalarını okuyabilmesi isteniyorsa, bu durumda aşağıdaki başlangıç parametresi ayarlanmalıdır. Ancak bunun bir güvenlik zaafiyeti oluşturacağını unutmamanız gerekmektedir.

ALTER SYSTEM SET “_trace_files_public” = true SCOPE=SPFILE;

Eğer veritabanı sunucusuna olması gerektiğinden çok çağrı yapan oturumlar tespit edilmişse ve bunlar izlenecekse, izleme dosyası oldukça büyük tutulmalıdır. İzleme dosyasının maksimum büyüklüğü max_dump_file_size parametresince tanımlanmaktadır. UNIX/Linux sistemlerde bu parameyre 512 bytelık blok birimleri olarak belirlenmiştir. Böylece, 10240 lık bir ayar izleme dosyasını 5MB büyüklüğündeki bir parçaya kadar sınırlandırmış olacaktır. Bunun yanında 2GB dan daha büyük izleme dosyasının UNIX/Linux sistemlerde izlenmesinde “could not open trace file xxx_xx_xxx.trc” hata mesajına karşılık izleme dosyası lokasyonu içerisinde mkfifo komutu yeni bir pipe oluşturularak bir prosesin dosyaya yazarken başka bir prosesin bu dosyayı okumasına imkan vererek verinin normal anonim pipe şeklinde akmasına imkan sağlanır. 2GB dan daha büyük SQL izleme dosyası kullanıldığında TKPROF işleminin başarılı okuma yapması için aşağıdaki adımlar uygulanabilir. Aşağıdaki örnekte test_ora_3419.trc adlı SQL izleme dosyası 2 GB üzerindedir ve normalde TKPROF ile çağrıldığında hata mesajı vermektedir. 2GB üzerindeki her bir izleme dosyasının TKPROF ile çağrılması için aşağıdaki 3 adımın uygulanması gerekmektedir. Alternatif olarak 2GB dan daha büyük bir kuyruk dosyası oluşturarakta bu sorun çözülebilir(detaylar için bakınız Metalink Note 94486.1)

1. izleme dosyaların bulunduğu lokasyon içinde yeni bir pipe oluşturulur.

$ mkfifo izlemepipe

2. tkprof ile bu pipe dosyasından çıktı oluşturulur. 

$ tkprof izlemepipe test_ora_3419.out

3. Başka bir oturumdan izleme dosyasının olduğu dizin değiştirilir ve aşağıdaki komut çalıştırılır.

$ cat test_ora_3419.trc > izlemepipe

SQL izleme dosyası maksimum büyüklüğü aştığında, veritabanı sunucu prosesi izleme dosyasına izleme bilgisini yazmayı durdurur. UNIX/Linux sistemlerde eğer max_dump_file_size parametresi ayarlanmadıysa izleme dosya büyüklüğünde maksimum bir limit yoktur.

Eğer bir oturum izleniyorsa ve izleme dosyası max_dump_file_size parametresince belirlenen limiti aşmak üzere olduğu anlaşıldıysa, dinamik olarak limit gözardı edilebilir ve böylece izleme bilgisi kaybedilmez. Bunu yapmak izleme dosyasına yazan prosesin ORACLE PID’sini bulmak için V$PROCESS görünümünün PID kolonuna sorgu yapılır. Ardından SQL*Plus içinden aşağıdaki komutlar çalıştırılır.

CONNECT / AS SYSDBA

ORADEBUG SETORAPID <pid>

ORADEBUG UNLIMIT

İzleme dosyasında TKPROF aracının çalıştırılması

TKPROF kullanılmadan önce izleme dosyasının oluşturulması ve yerinin belirlenmesi gerekmektedir. Oracle izleme dosyalarını -- PMON gibi daemon prosesleri haricindeki işlemleri --  user_dump_dest başlangıç parametresinde belirlenen dizine yazar. Unix/Linux sistemlerde, izleme dosyası, bu dosyaya yazan sunucu prosesinin işletim sistemi PID’si ile örtüşen bir isme sahiptir.

Eğer user_dump_dest dizininde çok sayıda izleme dosyası varsa, istenen dosyanın bulunması biraz teferrüatlı olabilir. Bunun üstesinden gelmenin en önemli yolu dosyaların oluşturulma zamanına bakmaktır. Diğer bir metot ise oturum katmanında izleme dosyasının etkinleştirilmesinde kullanılan komut içine bu izleme dosyasını diğerlerinden farklı kılıp anlaşılması için bir “yorum” eklemektir. Bununla ilgili örnek aşağıda yer almaktadır.

ALTER SESSION /* Module hr_22042011.c */ SET sql_trace = TRUE;

Bunun yanında hangi oturumların hangi izleme dosyasına işlem yaptığını görmek için aşağıdaki sorguda kullanılabilir. Bunun neticesinde izlenmesi istenen oturumun SQL komutlarının yer aldığı izleme dosyasına TKPROF ile izleme başlatılabilir.

select b.username, c.value || '\' || lower(d.value) || '_ora_' ||

       to_char(a.spid, 'fm00000') || '.trc' "TRACE_FILE"

from v$process a, v$session b, v$parameter c, v$parameter d

where a.addr   = b.paddr

and c.name     = 'user_dump_dest'

and d.name     = 'db_name'

and b.username is not null;

USERNAME TRACE_FILE

-------- ------------------------------------------------------------

SYS      /u01/app/oracle/diag/rdbms/ugur/ugur/trace/UGUR_ora_3345.trc

HR       /u01/app/oracle/diag/rdbms/ugur/ugur/trace/UGUR_ora_3564.trc

ARDA     /u01/app/oracle/diag/rdbms/ugur/ugur/trace/UGUR_ora_3708.trc

Bunun yanında Oracle 11g itibariyle hangi oturumun hangi izleme dosyasına yazdığını bulmak daha kolaydır. Bulunması istenen izlenen dosyasının adı ilgili oturumundan aşağıdaki sorgunun çalıştırılmasıyla kolayca bulunur.

select value from v$diag_info where name=’Default Trace File’;

Unix/Linux platformlarında TKPROF uygulamasını çalıştırmak için terminal penceresinden aşağıdaki komut çalıştırılır.  

tkprof <trace file> <output file> [explain=<username/password>] [sys=yes | no] [table=<schema>.<table>] [insert=<filename>] [record=<filename>] [sort=<keyword>]

Eğer TKPROF komutunu herhangi bir arguman kullanmadan çalıştırırsanız tüm seçenekler ekrana listelenecektir. Basit formda, TKPROF komutu bir çıktı dosya ismi ve bir SQL izleme dosyası ile tanımlanır. TKPROF, izleme dosyasını okuyacak ve belirlenen bir çıktı ismi altında bu rapor dosyasını oluşturacaktır. TKPROF, veritabanına bağlanmaz ve bu rapor SQL komutları için çalıştırma planları içermez. SYS kullanıcısı tarafında yinelemeli olarak çalıştırılan SQL komutlarıda bu rapor içinde yer alır ve SQL komutları izlenen oturumunda çalıştırma sırasına göre listelencektir.

Eğer “explain” kelimesi eklenirse TKPROF veritabanına bağlanır ve izleme dosyası içinde bulunan her bir SQL komutu için bir EXPLAIN PLAN çalıştırır. Açıklama planı sonuçları bu rapor dosyası içinde yer alacaktır. TKPROF komutunda tanımlanan kullanıcı adının izlenen oturumun kullanıcısı ile aynı olması gerekmektedir. Sırasıyla explain kelimesini kullanmak için bir plan tablosuna gerek yoktur, TKPROF bir tane oluşturacak ve gerektiğinde kendi plan tablosunu düşürecektir. 

Eğer sys=n olarak tanımlanırsa, TKPROF, SYS kullanıcısı olarak Oracle tarafından işlenen SQL komutlarını rapora dahil etmeyecektir. Bu raporu daha özlü hale getirecek ve sadece uygulama tarafından halihazırda çalıştırılan komutları içerecektir. Aslında bu idealdir, çünkü Oracle dahili SQL komutları Oracle veritabanı kernel yazılımcıları tarafından en üst seviyede optimize edilmiştir ve bizim bunlar üzerinde kafa yormamıza gerek yoktur.

Eğer “insert” kelimesi kullanılırsa bu rapora ilave olarak bir SQL scripti oluşturulacaktır. Bu SQL scripti tkprof_table adında bir tablo oluşturacak ve raporda yer alan her bir SQL komutu için bir satır ekleyecektir. Bu satır, izlenen SQL komutunun textini ve rapor içinde görünen tüm istatistiklerini içerecektir. Bu özelliği TKPROF raporunu efektif olarak veritabanına yüklemek ve istatistikleri analiz ve manipüle etmek için SQL’i kullanır. Nadiren kullanımı olsa gerektiği anlarda faydalı bir özelliktir.

Eğer “record” kelimesi eklenirse, izleme etkinken uygulama tarafından yayınlanan her bir SQL komutunun bir kopyasını içeren bir SQL scripti oluşturulur. Aslında bu bilgi TKPROF raporunun kendisindende elde edilebilir, ancak bu durumda kayıt etmek için kopyala-yapıştır gibi zahmetli işlemlere girilecektir.

“Sort” kelimesi pekçok durumda kullanışlıdır. Tipik olarak bir TKPROF raporu yüzlerce SQL komutu içerebilir, ancak pek çok durumda sadece belirli SQL komutlarını analiz etmek ihtiyacı duyulmaktadır.  İşte bu durumlarda, “sort” kelimesi SQL komutlarının listesini sıralamaya izin verecek, böylece tüm dosyayı taramak zahmetine girilmeyecektir.  Bazı durumlarda sort komutları işlem yapmaz, örneğin “tüketilen CPU zamanı”  bazında komutlar tasnif(sort) edilmez , bunun yerine “çözümleme(parsing) için harcanan CPU zamanı” veya “satırları alıp getirme(fetching) için harcanan CPU zamanı” bazında tasnif yapılabilir. Tasnif(sort) seçenekleri aşağıda yer almaktadır.

PRSCNT          Çözümleme sayısı

PRSCPU         Çözümleme için harcanan CPU zamanı

PRSELA          Çözümleme süresince geçen(elapsed) zaman

PRSDSK          Çözümleme esnasında diskten yapılan fiziksel okuma sayısı

PRSQRY          Çözümleme süresince sürekli blok okuma sayısı

PRSCU            Çözümleme esnasında mevcut blok okuma sayısı

PRSMIS           Çözümleme süresince library cache kayıpları sayısı

EXECNT         Çalışma(execute) sayısı

EXECPU        Çalışma için harcanan CPU zamanı

EXEELA         Çalışma süresince geçen zaman

EXEDSK         Çalışma esnasında diskten yapılan fiziksel okuma sayısı

EXEQRY          Çalışma süresince sürekli blok okuma sayısı

EXECU           Çalışma esnasında mevcut blok okuma sayısı

EXEROW        Çalışma esnasında işlenen satır sayısı

EXEMIS          Çalışma süresince library cache kayıpları sayısı

FCHCNT        Fetch sayısı

FCHCPU        Satırları alıp getirme(fetching) için harcanan CPU zamanı

FCHELA         Satırları alıp getirme esnasında geçen zaman

FCHDSK         Satırları alıp getirme esnasında diskten yapılan fiziksel okuma sayısı

FCHQRY         Satırları alıp getirme süresince sürekli blok okuma sayısı

FCHCU          Satırları alıp getirme esnasında mevcut blok okuma sayısı

FCHROW       Alıp getirilen satır sayısı.

USERID          İmleci çözümleyen kullanıcının userid’si

Aşağıda  UGUR_ora_3564.trc adlı SQL izlemesinin TKPROF ile çağrılma işlemi yer almaktadır. HR şemasının SQL komutları ile ilişkili sort seçenekleri OUT.PRF adı altında bir çıktı dosyasına yazılacak ve STOREHR.SQL adı altında bir SQL scripti oluşturulacaktır.

TKPROF UGUR_ora_3564.trc OUTPUT.PRF

EXPLAIN=hr/hr INSERT=STOREHR.SQL SYS=NO

SORT=EXECPU,FCHCPU,EXECU,EXEROW,PRSDSK,FCHROW

İlave olarak, tüm izleme dosyası için bekleme olayları dosyanın sonunda özetlenir. Oturum için bekleme olaylarının izleme dosyasına yazıldığından emin olmak için aşağıdaki komut çalıştırılır.

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

Bu 10046 izleme olayını ilerki yazıda detaylı olarak örneklerle inceleyeceğim için bu yazıda kullanımına ve detaylarına girmeyeceğim.

Eğer bekleme bilgiside TKPROF raporunda yer alırsa, ilgili bekleme olayları TKPROF çıktısında aşağıdaki gibi bir bölümde yer alacaktır.

Event waited on             Times Waited Max.Wait Total Waited

--------------------------  ------------ -------- ------------

db file sequential read     7884         0.12     5.43

direct path write           953          0.00     0.00

direct path write temp      861          0.00     0.05

db file parallel read       12           1.61     6.12

db file scattered read      4452         0.08     1.39

direct path read            7173         0.00     0.05

direct path read temp       7201         0.00     0.46

rdbms ipc reply             27           0.00     0.04

SQL*Net message to client   2            0.00     0.00

SQL*Net message from client 2            0.00     0.00

TKPROF raporlarının okunması

Bütün TKPROF raporları; TKPROF sürümü, raporun hazırlandığı tarih ve zaman, kullanılan izleme dosyasının adı, kullanılan tasnif seçenekleri ve rapor içindeki kolon başlıklarının özet tanımını listeleyen bir başlık ile başlar ve özet istatistik serileri ile biter. Aşağıda örnek bir TKPROF başlığı yer almaktadır.

TKPROF: Release 11.1.0.7.0 - Production on Wed Apr 27 12:28:45 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Trace file: UGUR_ora_3564.trc

Sort options: default

***************************************************************

count    = number of times OCI procedure was executed

cpu      = cpu time in seconds executing

elapsed  = elapsed time in seconds executing

disk     = number of physical reads of buffers from disk

query    = number of buffers gotten for consistent read

current  = number of buffers gotten in current mode (usually for update)

rows     = number of rows processed by the fetch or execute call

**************************************************************

TKPROF raporunun ana gövdesi, SQL izlemesi etkinken veritabanında çalıştırılan her bir farklı SQL komutuna ait bir girişten meydana gelir. Bir önceki cümle ile oynarken bir takım incelikler vardır. Eğer bir uygulama, mesela “müşteriler” tablosuna 50 sefer sorgu çekerse ve her seferinde farklı musteri_id değeri kullanırsa, bu durumda TKPROF raporunda 50 adet farklı giriş olacaktır. Ancak, eğer musteri_id değeri için bind değişkeni kullanılırsa, rapor içinde bu komutun 50 sefer çalıştırıldığı işareti ile birlikte sadece tek bir giriş gözükecektir.  Dahada ötesinde ayrıca bu raporda, veritabanının kendisi tarafından başlatılan “tekrarlamalı işlemler” olarakta adlandırılan “data dictionary” ve “dictionary cache” gibi işlemleri uygulayan SQL cümleleride işlem sırasına göre yer alacaktır.

TKPROF raporu içindeki her bir SQL cümlesi tek satırda ardışık yıldızlarla ayrılmaktadır. Her bir girişin ilk bölümü SQL komutunu ve bu SQL komutu ile ilişkili çözümleme, çalıştırma ve satır alıp getirme istatistiklerini listeler. Aşağıda örnek bir giriş yer almaktadır.

**************************************************************

SELECT   *

FROM     emp

ORDER BY emp_id

call     count  cpu  elapsed  disk  query current  rows

------- ------  ---- -------  ----- ----- -------  ----

Parse   1       0.01 0.02     0     0     0        0

Execute 1       0.00 0.00     0     0     0        0

Fetch   14      0.59 0.99     0     33633 0        194

------- ------  ---- -------  ----- ----- -------  ----

total   16      0.60 1.01     0     33633 0        194

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: HR  [recursive depth: 0]

**************************************************************

Dosyanın içine baktığımızda; ilk satırda, SQL izlemesi etkinken uygulamanın bu komutu bir sefer çözümlediğini(parse) görüyoruz. Çözümleme işlemi için 0.01 saniyelik CPU tüketimi 0.02 saniye geçen sürede meydana gelmektedir. Buna rağmen herhangi bir fiziksel I/O ve hatta herhangi bir tampon(buffer) okuması gerekmeketedir.

İkinci satırda ise çalıştırma(execute) sayısının tek sefer olduğunu görüyüruz. Bu tek sefer çalıştırma için sistem 0.01 saniyenin altında CPU tüketmekte ve çalıştırma için geçen süre 0.01 saniye altında olmaktadır. Tekrardan, herhangi bir fiziksel I/O işlemi veya tampon okuması olmamaktadır. Burada ilginç olan nokta, çözümleme ve çalıştırma sürecinde Oracle’ın kaynakları çalıştırma yerine çözümleme esnasında kullanması ve böylece çözümleme sonrası çalıştırma tüketiminin genelde çok düşük kalmasıdır. 

Bir sonraki satır, SQL komutunun 14 satır alıp getirme(fetch) işlemiyle 14 satır çağırdığını ve toplamda 194 satır aldığını göstermektedir. Bu 14 çağrı süresince toplamda 0.59 saniye CPU tüketilmiş ve işlem için geçen süre 0.99 saniye olmaktadır. Fizisel bir I/O işlemi olmamış ve sürekli okuma modunda 33,633 tampon okuması meydana gelmektedir. Diğer bir kelime ile “fetch” işleminde, tampon bellek içinde 33,633 hit olmakta ve herhangi bir kayıp meydana gelmemektedir(fiziksel I/O oluşmadığından). Ben bu sorguyu SQL*Plus üzerinden çalıştırdığımdan, tek bir “fetch” çağrısında pekçok satır alınıp getirilmesinden dolayı SQL*Plus’ın dizin arayüzü kullandığını görüyoruz. 

İlk bölüm dışında kalan kısımlarda; tek bir library cache kaybının olduğunu (yani SQL komutu henüz paylaşımlı havuzda değildir), çalıştırma planının CHOOSE optimizer modu ile gerçekleştiğini, çözümleme işleminin ise HR şeması tarafından gerçekleştirildiğini gözlemliyoruz.

SQL komutlarının yinelemelerine ve library cache istatistiklerine bakarak, uygulamanın Oracle paylaşımlı SQL olanaklarına uygun olup olmadığı belirlenebilir. Bind değişkenleri kullanılıyormu veya her bir sorgu boştan çözümleme gerektiren tekil bir komutamı ait?

Çözümleme, çalıştırma ve satırları alıp getirme sayılarından, uygulamanın Oracle API’lerini düzgün şekilde kullanıp kullanmadığıda görülebilmektedir. Uygulamalar tek seferde satırları alıp getiriyormu? Uygulama, bir imleci açık bırakıp bir sonraki  çözümlemeleri engelleyeceği yerde, aynı imleci yüzlerce sefer yeniden çözümlüyormu?  Uygulama SQL cümlelerini PL/SQL blokları içine gömmek yerine, oldukça fazla sayıdaki basit SQL işlemlerimi gönderiyor veya dizin bind’lerini mi kullanıyor?

CPU ve I/O istatistiklerine bakarak en çok sisTem kaynaklarını tüketen komutlar gözlemlenebilir. Bazı uygulamaların yapısı yeniden geliştirilerek, daha az CPU ve I/O tüketimi olabilirmi? Az biraz tampon okumaları traşlanarak çalıştırma planında biraz rahatlama yapılabilirmi? belki komutlar olması gerektiğinden sık çalıştırılıyor, bu yinelemeler azaltılabilirmi? Bunun yanında tablolar yanlış birleştirme (join) metodu kullanıldığından dolayı sırasına göre alıp getirmesi gerekenden daha fazla satırmı işlemekte? veya birbirinin aynısı(duplicate) kayıtların çağrılması gerekmediği halde çağrılıyormu?

TKPROF raporu olduğundan uzun gözükebilir ve bakıldığında ilkel bir rapor formatına sahip olabilir, ancak çok eski sürümlerden bugünlere gelmesine rağmen bilhassa SQL komutlarının performansının geliştirilmesinde çok kritik bir öneme ve faydaya sahiptir. Her ne kadar SQL komutlarının analizi ve performansının iyileştirilmesi Oracle 11g sürümünde “advisor” lar ile otomatikleştirilmiş olsada, sistem üzerindeki yüklerin analizi ve bu bekleme olaylarına göre SQL komutlarının omurgalarının iyileştirmesinde TKPROF’a alternatif bir Oracle built-in aracı bulunmamaktadır.

Daha sonraki yazımda 10046 izleme olayının TKPROF ile entegrasyonu ve 10046 izleme sonucunda kompleks sorgularda gözlemlenen bekleme olaylarının genel veritabanı performansı üzerine etkisinin analizini işleyeceğim.