“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;

/