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.

Eğer birden fazla SQL komutu iyileştirilecekse bir SQL iyileştirme seti oluşturulur.
Bir SQL iyileştirme görevi oluşturulur.
SQL iyileştirme görevi çalıştırılır.
SQL iyileştirme görevinin sonuçları görüntülenir.
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.

1 – SQL iyileştirme setinin oluşturulması

Birden fazla SQL komutunu tek bir SQL seti içerisinde toplamak için önce bir set oluşturulmalı ve ihtiyaca uygun SQL komutları filtrelenip bu sete yüklenmelidir. İlgili SQL komutlarının filtrelenmesinde paylaşımlı bellek alanı veya herhangi bir AWR raporu kullanılabilir.

İlk adım olarak bir SQL seti aşağıdaki gibi oluşturulmalıdır.

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => ‘test_sqlset’);
END;
/

– İmleç önbelleğinden yükleme

Paylaşımlı SQL alanından imleç önbelleğinde bulunan ve filtreleme şartlarına uyan SQL komutlarını seçip, ilgili SQL setine yüklemek için MS_SQLTUNE.SELECT_CURSOR_CACHE ve DBMS_SQLTUNE.LOAD_SQLSET komutlarının birlikte kullanıldığı bir PL/SQL bloğu çalıştırılır.

Aşağıdaki örnekte, imleç önbelleğinde bulunan SQL textlerinin içinde TBL_STOKGIRIS kelimesi geçen ve HR kullanıcısına ait tüm SQL komutları seçilip test_sqlset adlı SQL seti içerisine yüklenmektedir.

DECLARE
  testgiris  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN testgiris FOR
     SELECT VALUE(X)
     FROM   TABLE( DBMS_SQLTUNE.select_cursor_cache(
                basic_filter   => 'sql_text LIKE ''%tbl_stokgiris%''  and parsing_schema_name = ''HR''',
                attribute_list => 'ALL')
            ) X;

  DBMS_SQLTUNE.load_sqlset(sqlset_name     => 'test_sqlset',
                           populate_cursor => testgiris);
END;
/

DBMS_SQLTUNE.SELECT_CURSOR_CACHE fonksiyonu ile birlikte kullanılan parameterlerin ne anlama geldikleri aşağıda yer almaktadır.

DBMS_SQLTUNE.SELECT_CURSOR_CACHE (
  basic_filter        IN   VARCHAR2 := NULL,
  object_filter       IN   VARCHAR2 := NULL,
  ranking_measure1    IN   VARCHAR2 := NULL,
  ranking_measure2    IN   VARCHAR2 := NULL,
  ranking_measure3    IN   VARCHAR2 := NULL,
  result_percentage   IN   NUMBER   := 1,  -> sıralamaya bağlı top N listesinin yüzdesel değeri
  result_limit        IN   NUMBER   := NULL, à Top sınır listesi
  attribute_list      IN   VARCHAR2 := NULL à BASIC | TYPICAL | ALL değerlerinden birisi)

Aşağıda basic_filter parametresi ile kullanılan bazı örnekler yer almaktadır.

basic filter => ‘buffer_gets > 500′ — 500 tampon alımı yapan SQL komutları alır
basic filter => ‘elapsed_time > 5000000′ — En az 5 saniye çalışan tüm komutları alır
basic_filter => ‘sharable_mem > 5242880’ — 5 MB üzerinde paylaşımlı bellek kullanan tüm komutları alır
basic_filter => ‘parse_calls > 300 and executions < 2* parse_calls’ -- En az 300 hard parse yapan tüm komutları alır

Bunun yanında tamamlanma süresine göre büyükten küçüğe TOP 10 SQL sıralamasındaki komutları imleç önbelleğinden almak için;

DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
basic_filter => ‘ELAPSED_TIME’,
result_percentage => 1,
result_limit => 10)

Önbellek içinde tampon alımlarının %80’ini kullanan SQL komutlarını imleç önbelleğinden almak için;

DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
basic_filter => 'BUFFER_GETS',
result_percentage => .8)

- AWR raporundan yükleme

AWR raporundan SQL setine ilgili filtrelenmiş SQL komutlarını yüklemek için;

DBMS_SQLTUNE.SELECT_WORKLAOD_REPOSITORY (
  begin_snap        IN NUMBER,
  end_snap          IN NUMBER,
  basic_filter      IN VARCHAR2 := NULL,
  object_filter     IN VARCHAR2 := NULL,
  ranking_measure1  IN VARCHAR2 := NULL,
  ranking_measure2  IN VARCHAR2 := NULL,
  ranking_measure3  IN VARCHAR2 := NULL,
  result_percentage IN NUMBER   := 1,
  result_limit      IN NUMBER   := NULL
  attribute_list    IN   VARCHAR2 := NULL)

veya

DBMS_SQLTUNE.SELECT_WORKLAOD REPOSITORY (
  baseline_name     IN VARCHAR2,
  basic_filter      IN VARCHAR2 := NULL,
  object_filter     IN VARCHAR2 := NULL,
  ranking_measure1  IN VARCHAR2 := NULL,
  ranking_measure2  IN VARCHAR2 := NULL,
  ranking_measure3  IN VARCHAR2 := NULL,
  result_percentage IN NUMBER   := 1,
  result_limit      IN NUMBER   := NULL)
  attribute_list    IN   VARCHAR2 := NULL)

Fonksiyondaki parametrelerin anlamları SELECT_CURSOR_CACHE fonksiyonundaki parametreler ile aynıdır. Sadece AWR raporunun başlangıç ve bitiş snapshot parametreleri(ilk paket) ile snapshot baseline ismi(ikinci paket) ek parametrelerdir.

Şimdi AWR raporlarından SQL komutlarını alıp ilgili SQL setine yükleme örneğini inceleyelim. 713 ve 721 arasındaki AWR snapshotlarında tamamlanma süresi en uzun olan 10 SQL komutu çekilip test_sqlset adlı SQL seti içine yüklenmektedir.

DECLARE
  testgiris  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN testgiris FOR
     SELECT VALUE(X)
     FROM   TABLE( DBMS_SQLTUNE.select_workload_repository
                begin_snap => 713
                end_snap => 721
                basic_filter => ‘elapsed_time’
                result_limit => 10
                attribute_list => 'ALL')
            ) X;                                               

  DBMS_SQLTUNE.load_sqlset(sqlset_name     => 'test_sqlset',
                           populate_cursor => testgiris);
END;
/

Uğur İnal tarafından yayınlandı