“CPU Time” Oracle bekleme olayı ve CPU önceliklerinin değiştirilmesi

“CPU Time” olayını, performans olaylarını incelerken genellikle Top5 bekleme olayı listesi içinde gözlemleriz. DBA penceresinden bakınca yaygın düşünce, CPU bekleme olayının bir darboğaz olduğu şeklindedir.  Aslında Oracle dökümanlarında da CPU tüketimi ilgili bir takım bekleme olaylarının darboğaz oluşturduğu şeklinde açıklamalar vardır.

Peki eğer sistem içindeki istatistiklerde CPU Tüketimi(%Util ve Çalışma Kuyruğu) kabul edilebilir seviyeler içindeyse ve yeterli kapasiteye sahiplerse, ancak Oracle hala CPU Time bekleme olayını Top 5 bekleme olayı içerisinde listeliyorsa ne yapılmalıdır?

Bu arada yüksek derecede istemsiz içerik değişmesi(Solaris üzerinde mpstat ile) veya içerik değişmesi(Linux üzerinde vmstat ile) gözlemlenebilir.  Burada açık olarak söylemek gerekirse bazı şeylerin doğru hesaplanmadığı anlaşılmaktadır.

CPU süresi, bir prosesin aşağıdaki iki noktadan birisi anlamına geldiğini gösterir;

• CPU üzerinde bu proses için zamanlanmış bir çalışma kuyruğu beklemesi vardır.
• Veya, proses bir CPU üzerinde halihazırda çalışmaktadır. 

Aslında bir DBA olarak bu durumda bizi ilgilendiren noktalar;

• Çalışma kuyruğu üzerinde bekleme süresini azaltarak, oturumun CPU üzerinde en kısa süre içinde çalışabilmesini sağlamak. Bu prosesin önceliğinin ele alınmasıyla ayarlanabilir.
• CPU üzerinde çalışmaya başlangıcından görev sonlanana kadar bir prosesin kesintiye uğramadan çalışmasının sürdürülmesi. CPU üzerinde proses için uygun zaman miktarı “Time Quanta-Zaman Ayarı” olarak tanımlanmaktadır.

Yukardaki her iki görüşte OS Zamanlayıcısı veya OS Dispatcher tarafından kontrol edilmektedir. Aşağıda bilgisayar terminolojisinde zamanlamanın(scheduling) ne anlama geldiğini açıklayan bir açıklama yer almaktadır.

DBMS_XPLAN paketi kullanarak çalıştırma planlarını farklı fonksiyonlar ile görüntülemek

DBMS_XPLAN paketi EXPLAIN_PLAIN komutu çıktılarını bir çok önceden tanımlanmış formatta görüntülemek için kullanılan oldukça kullanışlı bir PL/SQL paketidir. Ayrıca DBMS_XPLAN paketi kullanılarak bir SQL iyileştirme seti içinde veya AWR snapshotları içinde bulunan SQL komutlarının görüntülenmesinde de kullanılabilir.Bunun ilerisinde,  V$SQL_PLAN ve V$SQL_PLAN_STATISTICS_ALL görünümleri içerisinde saklanan bilgilere dayalı olarak, önbelleklenmiş SQL imleçleri içinde çalıştırma zamanı istatistiklerini ve SQL çalıştırma planını görüntülemeye yaramaktadır. Oracle 11.2 sürümünden itibaren iki farklı SQL planının farklılıları kolayca kıyaslanabilmekte, AWR snapshotu içinde hash id değerlerine göre farklı SQL planlarıda kıyaslayabilmektedir. Sonuçta, bir SQL planından çalıştırma ve açıklama planlarını görüntüleme imkanı veren bir pakettir.

DBMS_XPLAN paketi 10 tablo fonksiyonu sağlamaktadır, son 5 fonksiyon Oracle 11.2 sürümünden itibaren hizmettedir.

• DISPLAY - Bir plan tablosunun içeriğini gösterir ve formatlar.
• DISPLAY_AWR - AWR içerisinde saklanan SQL komutunun çalışma planı içeriklerini listeler ve formatlar.
• DISPLAY_CURSOR - Herhangi yüklenmiş bir imlecin(cursor) çalıştırma planı içeriklerini görüntüler ve formatlar.
• DISPLAY_SQL_PLAN_BASELINE - Eldeki SQL planınca tanımlanan SQL komutu için bir veya daha fazla açıklama planını görüntüler.
• DISPLAY_SQLSET - SQL iyileştirme seti içinde saklanan komutların çalıştırma planı içeriklerini görüntüler ve formatlar.
• DIFF_PLAN_CURSOR - Girilen child imleç numarasından türetilen iki SQL planının farklılıklarını kıyaslar.
• DIFF_PLAN_AWR - Girilen farklı hash id değerlerince tanımlanan iki SQL planının farklılıklarını kıyaslar.
• DIFF_PLAN - İki SQL planının referans planını kıyaslar.
• DIFF_PLAN_SQL_BASELINE - plan_names parametresi vasıtasıyla belirtilen 2 SQL planını kıyaslar.
• DIFF_PLAN_OUTLINE - Girilen özet tarafından oluşturulan iki SQL planını kıyaslar. 

Oracle 11g sürümünde DBMS_STATS.SET_*_PREFS prosedürü

Oracle 11g öncesi sürümlerde DBMS_STATS.SET_APRM prosedürünü kullanarak DBMS_STATS.GATHER_*_STATS prosedürünce kullanılan parametrelerin varsayılan değerini değiştirmek için kullanılmaktaydı. Yapılan herhangi bir değişikliğin kapsama alanı tüm sonraki operasyonlardır. Oracle 11g sürümünde DBMS_STATS.SET_PARM prosedürü artık kullanılmamaktadır ve tablo, şema, veritabanı ve global seviyedeki herhangi bir parametre için öcelik atamaya izin veren bir dizi prosedür ile yer değiştirmiştir. Bu yeni prosedürler DBMS_STATS.SET_*_PREFS olarak adlandırılır ve daha iyi ayrıntılı kontrol sunar.

Ancak, hangi prosedürün ne zaman kullanılması gerektiği ve bu prosedürler üzerinden hangi hiyerarşinin olduğu noktasında bazı karışıklıklar olabilmektedr. Bu yazıda DBMS_STAT.SET_*_PREFS  prosedürü kullanılarak nasıl değişiklikler yapılacağına göz atacağız

Oracle proseslerinin bellek kullanımının izlenmesi

Oracle prosesleri tarafından kullanılan belleği anlamak için öncelikle Linux ps komutu ile işletim sistemi seviyesinde proses bazında bellek kullanım listesini alıp, ardından Oracle içerisinden aynı proseslerin arkaplanda kullanıldığına bakmamız gerekmektedir. Kısaca, smon Oracle arkaplan prosesine ps komutu ile bakalım ve ardından SQL*Plus üzerinden aynı prosesin programlar içerisinde görelim.

# ps -afe | grep ora_smon

oracle    2944     1  0 07:02 ?        00:00:01 ora_smon_aysun

root      3181  3148  1 07:28 Pts/2    00:00:02 grep ora_smon    

SQL> SELECT spid,program,background

       FROM v$process

      WHERE spid = 2944;

SPID  PROGRAM                                  BACKGROUND

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

2944  oracle@linux2.localdomain(SMON)          1

Bu iki sorgu sonucunda anahtar nokta, UNIX proses ID ve Oracle SPID değerlerinin aynı olmasıdır. Bu benzersiz değer kullanılarak, Oracle üzerinden Linux/UNIX’e veya Linux/UNIX üzerinden Oracle’a doğru prosesler araştırılabilir. Bir Oracle instance başladığında veya bir uygulama Oracle veritabanını kullandığında, ilişkili Oracle prosesleri tahsis edilmekte ve bellek tüketmektedir. Bu bellek kullanımına farklı yollardan bakılabilmektedir.

V$PROCESS görünümünde toplam bellek kullanımını raporlayan ilave bazı kolonlar bulunmaktadır. V$PROCESS görünümü, proses için kontrol bilgisi ve veriyi içeren özel bellek bölgesi olan “PGA belleği” üzerinde raporlama yapan kolon setlerine sahiptir. Aşağıdaki sorguyu kullanarak, üstteki SQL örneği sonucu bulunan smon arkaplan prosesine bakabilir ve ne kadar PGA bellek tahsis edildiğini görebilmekteyiz. Sonuçların daha anlaşılır olması için sonuçları byte değerinden MB değerine dönüştürüyorum.

ADRCI aracı ile kaza paketlerinin oluşturulması

Automatic Diagnostic Repository Command Interface (ADRCI), Oracle veritabanı teşhis verisini yönetmek için kullanılan bir komut satırı aracıdır. ADRCI, Oracle 11g sürümü ile kullanıma sürülmüş ve aşağıdaki işlemleri yapmaktadır;

• Otomatik Tanı Ambarı(ADR) içindeki tanı verisini görüntüler.
• Sağlık izleme raporlarını görüntüler.
• Kaza ve problem bilgilerini Oracle Desteğe iletmek için zip dosyasına paketler.

Tanı verisi; kaza ve problem tanımlarını, izleme dosyalarını, dumpları, sağlık izleme raporlarını, alert log girişlerini ve daha fazlasını içermektedir.

ADRCI zengin komut setine sahiptir ve gerek scriptler içinde gerekse interaktif modda kullanılabilmektedir. İlave olarak SQL*Plus üzerinden SQL ve PL/SQL komutlarının çalıştırılması gibi aynı şekilde ADRCI komutları içeren scriptlerde çalıştırılabilmektedir.

SQL Tuning Advisor ile SQL komutlarının iyileştirilmesi - Bölüm 2

Bir önceki yazıda SQL Tuning Advisor ile SQL komutlarının iyileştirilmesi yazısının ikinci bölümünde SQL iyileştirme görevlerinin oluşturulması adımından devam ediyorum.

 2. SQL iyileştirme görevinin(task) oluşturulması

İyileştirilme görevleri tek bir SQL komutunun textinden, birden fazla komutu barındıran bir SQL setinden yada paylaşımlı havuzdaki veya AWR raporundaki bir SQL komutununun SQL ID değeri seçilerek oluşturulabilir.

Bununla beraber standart bir kullanıcının iyileştirme görevi oluşturabilmesi için; önce ADVISOR hakkına sahip olması ve ardından ilgili kullanıcının şema objeleri üzerinde bu fonsiyonun çalıştırılması gerekmektedir.

Aşağıda SQL iyileştirme görevini oluşturmak için kullanılan PL/SQL paketleri yer almaktadır.

• Bir SQL textinden, bind değişkenli yada bind değişkensiz SQL iyileştirme görevi oluşturmak;

DBMS_SQLTUNE.CREATE_TUNING_TASK(

  sql_text         IN CLOB,

  bind_list        IN sql_binds := NULL,

  user_name        IN VARCHAR2  := NULL,

  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,

  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,

  task_name        IN VARCHAR2  := NULL,

  description      IN VARCHAR2  := NULL)

• Bir SQL textinden plan_hash_value değerine göre SQL iyileştirme görevi oluşturmak;

DBMS_SQLTUNE.CREATE_TUNING_TASK(

  sql_id           IN VARCHAR2,

  plan_hash_value  IN NUMBER   := NULL,

  scope            IN VARCHAR2 := SCOPE_COMPREHENSIVE,

  time_limit       IN NUMBER   := TIME_LIMIT_DEFAULT,

  task_name        IN VARCHAR2 := NULL,

  description      IN VARCHAR2 := NULL)

• Bir AWR raporundan ilgili snapshot aralığında  SQL iyileştirme görevi oluşturmak;

DBMS_SQLTUNE.CREATE_TUNING_TASK(

  begin_snap      IN NUMBER,

  end_snap        IN NUMBER,

  sql_id          IN VARCHAR2,

  plan_hash_value IN NUMBER   := NULL,

  scope           IN VARCHAR2 := SCOPE_COMPREHENSIVE,

  time_limit      IN NUMBER   := TIME_LIMIT_DEFAULT,

  task_name       IN VARCHAR2 := NULL,

  description     IN VARCHAR2 := NULL)

• Bir SQL setinden filtreleme şartlarına uygun SQL iyileştirme görevi oluşturmak;

DBMS_SQLTUNE.CREATE_TUNING_TASK(

  sqlset_name       IN VARCHAR2,

  basic_filter      IN VARCHAR2 :=  NULL,

  object_filter     IN VARCHAR2 :=  NULL,

  rank1             IN VARCHAR2 :=  NULL,

  rank2             IN VARCHAR2 :=  NULL,

  rank3             IN VARCHAR2 :=  NULL,

  result_percentage IN NUMBER   :=  NULL,

  result_limit      IN NUMBER   :=  NULL,

  scope             IN VARCHAR2 :=  SCOPE_COMPREHENSIVE,

  time_limit        IN NUMBER   :=  TIME_LIMIT_DEFAULT,

  task_name         IN VARCHAR2 :=  NULL,

  description       IN VARCHAR2 :=  NULL

  plan_filter       IN VARCHAR2 :=  'MAX_ELAPSED_TIME',

  sqlset_owner      IN VARCHAR2 :=  NULL)

DBMS_SQLTUNE.CREATE_TUNING_TASK paketinde önemli olan bazı parametrelerin ne anlama geldiğine bakarsak;

SQL Tuning Advisor ile SQL komutlarının iyileştirilmesi - Bölüm 1

SQL Tuning Advisor(SQL İyileştirme Tavsiyecisi), talep olduğunda bir veya birçok SQL komutunun manuel olarak iyileştirilmesinde de kullanılmaktadır. Birçok komutu iyileştirmek için öncelikle SQL iyileştirme setlerinin oluşturulması gerekmektedir.

SQL Tuning Advisor için gerekli olan veriler aşağıdaki gibi pekçok farklı kaynaktan sağlanabilir.

• ADDM( Automatic Database Diagnostic Monitor)

Ana veri sağlama kaynağı ADDM’dir. Varsayılan olarak ADDM proaktif olarak her saat başı bir sefer çalışır ve son bir saat boyunca aşırı yüklü SQL komutlarını içeren bir kısım performans problemlerini belirlemek için AWR tarafından toplanan anahtar istatistikleri analiz eder. Eğer aşırı yüklü SQL belirlenirse, ADDM bu SQL komutu üzerinde SQL Tuning Advisor’ı çalıştırmayı tavsiye eder.

• AWR(Automatic Workload Repository)

İkinci en önemli veri sağlama kaynağıda AWR’dir. AWR,CPU tüketimi ve bekleme süresi gibi ilişkili istatistikler tarafından sıralanan aşırı yüklü SQL komutlarını içeren sistem aktivitelerinin düzenli snapshotlarını çeker. İlgili AWR raporuna bakıldığında en çok kaynak tüketen SQL komutları belirlenebilir. Oracle, bu SQL komutları için otomatik iyileştirme tavsiyelerini sağlamasına rağmen manuel olarakta SQL Tuning Advisor çalıştırılabilir. AWR normalde çektiği bir snapshotu sekiz gün saklar.

• Paylaşımlı SQL alanı

Üçüncü veri kaynağı ise paylaşımlı SQL alanıdır. Henüz AWR tarafında snapshhotu çekilmemiş olan son çalıştırılan SQL komutlarını iyileştirmek için kullanılmaktadır.

• SQL iyileştirme seti (STS)

Diğer bir muhtemel veri sağlama kaynağı ise SQL iyileştirme setidir. SQL iyileştirme seti birçok SQL komutunun çalıştırma içeriklerini saklayan bir veritabanı objesidir.

SQL İyileştirme Tavsiyecisinin çalıştırılması

SQL iyileştirme tavsiyecisini çalıştırmanın en basit yolu Enterprise Manager konsoludur. Diğer bir yol ise, DBMS_SQLTUNE paketini komut satırından kullanarak SQL iyileştirme tavsiyecisini çalıştırmaktır ki bu yazıda DBMS_SQLTUNE paketi kullanımını inceleyeceğiz.

SQl iyileştirme tavsiyecisinin çalıştırılması için aşağıdaki adımların izlenmesi gerekmektedir.

1. Eğer birden fazla SQL komutu iyileştirilecekse bir SQL iyileştirme seti oluşturulur.
2. Bir SQL iyileştirme görevi oluşturulur.
3. SQL iyileştirme görevi çalıştırılır.
4. SQL iyileştirme görevinin sonuçları görüntülenir.
5. Tavsiyeler yerindeyse uygulanır.

Şimdi yukardaki beş adımın her birini sırasıyla inceleyelim. Bu yazıda sadece SQL iyileştirme setinin oluşturulması ve içerisine filtrelenmiş SQL komutlarının nasıl yükleneceğine bakacağız.

DBMS_SQLTUNE paketi ile SQL uygulamalarının analizi

Oracle 11g sürümünden itibaren, DBMS_SQLTUNE paketi içinden SELECT_SQL_TRACE fonksiyonu çalıştırılmaktadır. Bu fonsiyonun amacı; SQL izleme dosyasının içeriğini bir SQL iyileştirme seti içerisine yüklemektir. Bu yazımın amacı SELECT_SQL_TRACE fonksiyonunu kullanarak SQL komutlarının içeriğinin SQL izleme dosyasından görülebilmesinin kullanımını ve faydalarını göstermektir.

SELECT_SQL_TRACE özelliği Oracle 11.1.0.7 sürümünden itibaren işlemdedir, yani bu sürüm öncesi Oracle 11g sürümlerinde çalışmaz. Kısaca DBMS_SQLTUNE.SELECT_SQL_TRACE fonsiyonu ile kullanılan parametrelerin kısaca anlamlarına ve nasıl kullanıldığına gelirsek;

dbms_sqltune.select_sql_trace(

            directory => 'TRACE',

            file_name => 'aysun_ora_21351.trc',

            select_mode => 1 | 2 )

directory: SQL izleme dosyalarının yer aldığı fiziksel lokasyonu işaret eden sanal dizinin ismidir. CREATE DIRECTORY komutu ile oluşturulur.

file_name: SQL izleme setine yüklenecek olan SQL izleme dosyasının adı.

select_mode: 1 veya 2 integer değerinden birini alır. 1=> SINGLE_EXECUTION, yani tek bir SQL çalıştırmasını döndürür 2=>ALL_EXECUTIONS, yani tüm SQL çalıştırmalarını döndürür

İlk olarak senaryoyu belirleyelim.

• Gerekli veritabanı objelerini oluşturup obje istatistiklerini topluyoruz. Bu amaçla 10,000 satırdan oluşan test1 adında bir tablo ile bu tablonun id kolonunu işaret eden primary key indeksini oluşturup, tablonun tüm satırlarını istatistiklemeye dahil etmek üzere tablo istatistiklerini topluyorum.

SQL> CREATE TABLE test1

  2  AS

  3  SELECT rownum AS id, dbms_value.string(‘U’,50) AS adres

  4  FROM dual

  5  CONNECT BY level <= 10000

  6  ORDER BY dbms_random.value;

SQL> ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY (id);

SQL> BEGIN

  2    dbms_stats.gather_table_stats(

  3      ownname          => hr,

  4      tabname          => 'test1',

  5      estimate_percent => 100,

  6      method_opt       => 'for all columns size 1'

  7    );

  8  END;

  9  /

• SQL izlemesini etkinleştiriyorum. Bu arada Oracle 11g itibariyle SQL izlemesinde devreye giren PLAN_STAT adlı parametreyide kullanıyorum. PLAN_STAT parametresi, satır kaynak istatistiklerinin hangi sıklıkta izleme dosyalarına yazıldığını belirtmeye yaramaktadır. Bu parametre 3 değer alabilmektedir. NEVER: çalıştırma planı hakkında hiç bir bilgi izleme dosyasına yazılmaz. FIRST_EXECUTION: Çalıştırma planı ile ilgili bilgi ilk çalıştırmadan sonra izleme dosyasına yazılır. ALL_EXECUTIONS: Her çalıştırma sonrasında çalıştırma planı ile ilgili bilgi izleme dosyasına yazılmaktadır. Bu önemli bir parameterdir, çünkü Oracle 10g’de bilhassa imleçleri uzun zaman açık tutan uygulamalar için,  her bir imleç için çalıştırma planları hakkında bilgiye izleme dosyalarında rastlamak mümkün değildi.

Event 10046 izleme dosyaları ile SQL komutlarında bekleme olaylarının analizi

10046 genişletilmiş SQL izleme dosyaları, bir instance için değişik seviyelerde detay almak için kullanılmaktadır. Genişletilmiş SQL izlemeleri, level 4(bind değişkenleri), level 8 (bekleme olayları) veya level 12 (bind değişkenleri ve bekleme olayları)  Event(olay) 10046 ayarı ile etkinleştirilir. Olay 10046’yı etkinleştirebilmek için bazı metotlar vardır, genelde Oracle sürümüne bağlı olsada ve oturum içinde interaktif olarak ayaralanamayan olaylara dahil olsada, bu metotlar aşağıda yer almaktadır.  

  ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

veya

  EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,12,'');

veya

  EXEC SYS.DBMS_SUPPORT.START_TRACE_IN_SESSION(SID,SERIAL#,WAITS=>TRUE,BINDS=>FALSE);

veya

  EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>SID,SERIAL_NUM=>SERIAL#,WAITS=>TRUE,BINDS=>TRUE)

veya

  EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,TRUE);

İlgilenilen oturumun V$SESSION görünümünde SID ve SERIAL# kolonlarına sorgu çekilerek, SID ve seri numarası elde edilebilir. SYS.DBMS_SYSTEM.SET_EV metodu, Oracle 11g sürümünde artık desteklenmesede, diğer metotların olmadığı eski sürümlerde çok iyi şekilde çalışmaktadır. Yukardaki metotların bazılarının eski Oracle sürümlerinde çalışmadığını bildirmekte fayda var. Oracle sunucusunun udump dizininde izleme dosyaları oluşturulacaktır(Oracle 11g sürümünden itibaren trace dizini altında oluşturulur). Bununla beraber izlenmesi istenen oturum veya oturumlarda, logon olduklarından itibaren otomatik olarak 10046 izlemesinin aktif olması isteniyorsa, bir logon tetikleyicisi oluşturulabilir. Aynı programı çalıştıran oturumlar veya belirli bir programı çalıştıran tek bir oturum, bu logon tetikleyicisinin hedef alanına dahil edilebilir. 10046 izlemeleri ayrıca SQL*Plus ORADEBUG özelliği ilede etkinleştirilebilir.

Aşağıdaki örnekte ilk üç harfi CRY veya MVX ile başlayan programları çalıştıran oturumlar için logon oldukları andan itibaren 10046 izlemesi başlatılacaktır.  Programın yolu  V$SESSION görünümünün PROGRAM kısmında yer almaktadır. DECODE fonksiyonunu kullanarak ilave program listeleri için tetikleyici etkinleştirilebilir.  

CREATE OR REPLACE TRIGGER LOGON_10046_IZLEME AFTER LOGON ON DATABASE

DECLARE

 CALISTIR INTEGER;

BEGIN

  SELECT DECODE(SUBSTR(UPPER(PROGRAM),1,3),'CRY',1,'MVX',1,0)

      +DECODE(INSTR(PROGRAM,'\',-1),0,0,DECODE(SUBSTR(UPPER(SUBSTR(PROGRAM,INSTR(PROGRAM,'\',-1)+1)),1,3),'CRY',1,'MVX',1,0))

    INTO CALISTIR FROM V$SESSION

    WHERE SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1);

  IF CALISTIR > 0 THEN

    EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';

  END IF;

END;

/

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.

Indekslerin daha büyük bloğa sahip tablespace içerisine taşınması ve performanslarının durumu

Eğer bir indeks daha büyük blok büyüklüğündeki tablespace içinde yeniden inşa edilirse IFFS(Index Fast Full Scan) performansında iyi veya kötü yönde bir değişiklik olurmu?

• Rastgele giriş yapılan indeksler %70-75 aralığında PCT_USED değerine sahipken, bu indeksler, doldurulan farklı evreler içinde eş zamanlı olarak rastgele 50-50 blok bölünmesi meydana getirir. Bir indeksi yeniden insa ederek, %15 kadar indeks yoğunluğu artabilir ve böylece tüm indeks büyüklüğü azalabilir. Azalan blok geneli aynı zamanda indeks büyüklüğü ve blok büyüklüklerinin farklılıklarına bağlı olarak, indeksi de bir miktar azaltabilir. Fast Full Index Scan üyelik maliyetleri indeksin tüm büyüklüğü ile orantılı olduğundan, bir indeksi birleştirmek(defragmantation), en fazla potensiyel faydalar sunan erişim yolu olmaktadır. Bir indeksin büyüklüğünü azaltmak bu yüzden daha sonraki performansı etkiler.  Oysaki, mevcut blok büyüklüğünde indeksin yeniden inşası muhtemelen aynı benzer sonuca ulaşır(artı blok genel giderleri), indeksi sıkılaştırır ve potensiyel olarak daha iyi performans ile sonuçlanır. Buna karşın, indeks bloğu bir kez tekrardan bölünmeye başladımı, neticede bu indeks kendisinin önceki durumuna tekrar geri döner. İşte bu yüzden, daha büyük blok büyüklüğünün ötesinde, indeksin birleştirilmesi (defragmentation) genel bir performans iyileştirme yolu olmaktadır.

• İndeksi daha büyük bloğa sahip tablespace içinde saklayarak, bu indeksin ait olduğu tablodan farklı bir fiziksel veri dosyası içinde saklamak gerekmektedir. Bu veri dosyası performansı arttırmak için nispeten daha hızlı bir disk olmalı ve indeksler dışında başka dosyalar içermemelidir. Daha büyük blok büyüklüğünden ziyade, indeksin depolandığı yeni yerin fiziksel karakteristikleri esasında performansı etkiler. Eğer bir indeks aynı blok büyüklüğünde, daha büyük bloğa sahip indeksler ile aynı fiziksel karakteristiklere sahip yerde yeniden inşa edilirse, daha sonraki performans aynı şekilde artar(veya azalır).

Tabii bunun yanında pekçok muhtemel sebepler vardır, sistem daha büyük blok büyüklüğüne sahip indeksler kullanırken, daha az meşgul olmakla beraber indeksin daha büyük kısmı fiziksel olarak önbelleklenir.

Indeks Fast Full Scan, nadirende olsa bir ölçeklenebilirlik sorunudur. Bu noktada, aslında gerçekten kullandığımız uygulamaların yüzlerce büyüklükte eşzamanlı indeks fast full scan yapmasını istermiyiz? Uygulamayı bu gereksiz indeks fast full scan giderlerinden kurtarmak, indeksleri boş yere daha geniş bloğa sahip tablespace içine taşımaktan daha verimli bir yol olacaktır. Tabii bu başka bir yazıda tartışma konusu olur. 

Peki, esas bu yazının ana konusuna gelelim. Indeks Fast Full Scan işleminin performansı indeksin daha büyük bloğa sahip tablespace içine taşınması neticesinde değişirmi? Kesinlikle, değişir…Ancak, bu bahsedilen değişim, indeksin daha büyük bloğa sahip olması sonucunda çoklu blok(multiblock) okuma performansının geliştirilmiş olması anlamına gelmez.  

"db file sequential read" bekleme olayı üzerine detaylı bir inceleme

db file sequential read bekleme olayı, indekslere,rollback(veya undo) segmentlerine, rowid yoluyla erişilen tablolara, kontrol dosyalarına ve veri dosyası başlıklarına karşı tek-blok okuma işlemi gerçekleştiren işlemlerin SQL komutlarınca(hem kullanıcı hemde tekrarlamalı) başlatılır. Bu bekleme olayı sistem çapında beklemelere göre genellikle top 5 bekleme olayları içinde yer almaktadır.

Bu objeler için fiziksel I/O istekleri gayet normaldir, yani db file sequential read bekleme olayının veritabanındaki varlığı, illada veritabanı veya uygulama ile ilgili birşeylerin yanlış olduğu anlamına gelmez. Bir oturumun bu olay üstünde uzun zaman harcaması da her zaman kötü bir şey olmayabilir. Buna karşılık, bir oturum eğer kuyruğa ekleme(enqueue) veya “latch free” olayları için çok fazla zaman harcıyorsa, işte bu durum iyiye işaret olmaz. İşte bu iki olay tekli blok okuma konusunu çetrefilli bir duruma sokar. Peki ne zaman “db file sequential read” olayı sorun haline gelir? Aşırılık nasıl tanımlanır? Ne zaman ve nereye bir çizgi çekmelisiniz?  Aslında bu sorulara Oracle uzmanlarınında şu ana kadar kesin cevapları yoktur, hala araştırılır ve standart cevaplar bulunmamıştır hala ne yazıkki… Mesela, db file sequential read beklemesi proses yanıt süresinin büyük bir bölümünü temsil ediyorsa o zaman “aşırılık” kelimesi bir anlam kazanır. Diğer bir yol ise, bilimsel olmayan bir avam yaklaşımı takınarak, kullanıcılar bu bekleme olayından sıkıntı yaşayıp çığlık atana kadar beklemek olacaktır. Tabii bu durum profesyonel bir yaklaşım olmaz, hele hele proaktif olarak performans geliştirme iddiasında iseniz.

“db file sequential read” bekleme olayının üç parametresi vardır: file#, first block# ve block count. Bu bekleme olayı “User I/O” bekleme sınıfı altında yer almaktadır. db file sequential read bekleme olayı ile karşılaşıldığında aşağıdaki anahtar noktalara dikkat edilmesi gerekir.

• Bir Oracle prosesi, SGA içinde olmayan bloğa ihtiyaç duymakta olup bu veritabanı bloğunun diskten SGA alanına okunmasını beklemektedir.
• Bireysel oturumlarda bakılması gereken en önemli iki değer, TIME_WAITED ve AVERAGE_WAIT kolonları olacaktır.
• Dikkate değer db file sequential read bekleme olayı sıklıkla uygulama katmanından kaynaklanır. 

Aslında, V$SESSION_EVENT görünümünden “db file sequential read” bekleme olayında hangi oturumların yüksek TIME_WAITED değerine sahip olduğunu kolayca keşfedebiliriz. TIME_WAITED değeri LOGON_TIME değeri ile birlikte değerlendirilmeli ve daha kesin bir analiz için bu oturuma ait diğer boşta olmayan olaylar ile kıyaslanmalıdır.  Günler ve hatta haftalar boyunca açık olan oturumlarda db file sequential read olayı süresi dolgunca miktarda birikir.  Bu durumda, TIME_WAITED değeri sorun olmayabilir. Ayrıca, TIME_WAITED diğer boşta olmayan olaylar ile birlikte bir perspektife konduğunda, bu durum bizleri gafil avlanmaktan korur. Daha büyük önem taşıyan başka bekleme olayları bulunabilir. Aşağıdaki örneğe dayalı olarak, SID # 31 dikkati çekmeli ve diğerlerine göre daha kısa zamandan beri oturum açık olmasına rağmen yüksek bekleme süresinin nedeni incelenmelidir.