ÇözümPark'a hoş geldiniz. Oturum Aç | Üye Ol
 
Ana Sayfa Makale Video Forum Resimler Dosyalar Etkinlik Hizmetlerimiz Biz Kimiz

Oracle

Oracle Tuning Advisor İle Sql Komutlarinin Performanslarinin İyileştirilmesi

 

Bu yazıda Oracle 10g ile gelen ve Oracle 11g R2 sürümünde dahada mesafe kateden SQL Tuning Advisor(SQL İyileştirme Tavsiyecisi) paketinden bahsedeceğim. Oracle Tuning Advisor paketi ile SQL iyileştirme görevleri, Oracle 11g Grid Control veya Enterprise Manager(EM) grafiksel arayüzünden sihirbazlar yardımıyla adım-adım basitçe oluşturulduğu gibi komut satırındanda oluşturulabilmektedir. Bu yazıda komut satırından hazırlanışını adım-adım yapılandıracağız ve yazı sonunda bir tavsiye raporu alacağız.

 

 

Oracle 11g sürümü itibariyle SQL Tuning Advisor otomatik olarak yüksek kaynak tüketen SQL komutlarına karşı çalıştırılsada, Oracle SQL Tuning Advisor(SQL İyileştirme Tavsiyecisi), talep olduğunda bir veya birçok SQL komutunun manuel olarak iyileştirilmesinde de kullanılmaktadır. Bunun için AWR raporu ile geçen hafta alınan CPU ve I/O toplamları kıyaslaması yapılmaktadır. Otomatik SQL iyileştirmesinin etkinleştirilmesi için;

 

 

dbms_auto_sqltune(
begin_exec   IN VARCHAR2 := NULL, --oto sql iyileştirme başlama zamanı
end_exec     IN VARCHAR2 := NULL, --oto sql iyileştirme bitiş zamanı
type         IN VARCHAR2 := TEXT | HTML | XML,
level        IN VARCHAR2 := TYPICAL | ALL | BASIC,
section      IN VARCHAR2 :=
FINDING | PLAN | INFORMATION | ERROR | ALL
,
object_id    IN NUMBER   := NULL,
result_limit IN NUMBER   := NULL)

 

 

ACCEPT_SQL_PROFILES adlı başlangıç parametresi değeri TRUE olarak ayarlanırsa SQL profilleride otomatik olarak kabul edilir. SQL profilleri diğer bir makale konusu olacak kadar kapsamlıdır, bu yazıda hiç girmeyeceğim. Ancak, otomatik SQL iyileştirme tavsiyecisinin etkinleştirilmesi, 11.2 sürümünde dahi hala “bug” lar olmasından dolayı pek tavsiye edilmez. Bu arada otomatik itileştirme etkinleştirildiğinde sistem üzerinde gereksiz bir kaynak tüketimi olacağıda göz ardı edilmemelidir.

 

 

Hazırlanacak tavsiye raporunda, komut içinde kullanılan tablo birleştirmelerinde varsa eksik indeksler ve anahtarlar tavsiye olarak işaret edilmektedir, ayrıca ulaşılan bloklar ve objeler ile ilgili detaylı bir çalıştırma planı kıyaslaması yer almaktadır. Bununla beraber SQ komutunun yeniden yapılandırılması(sorguda kullanılan birleştirme türlerinin uygunluğu,doğru sürücü tablo(lar) seçimi işaretivvb.), objeler üzerinde eksik istatistikler varsa bu eksik istatistiklerin toplanması ve SQL profil oluşturulması gibi tavsiyelerde bu raporda yer alır.

 

 

Birçok komutu iyileştirmek için öncelikle SQL iyileştirme setlerinin oluşturulması gerekmektedir. Tek bir komut için ise direkt olarak SQL iyileştirme görevi çalıştırılır.

 

 

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ı üzerine odaklansamda, Oracle EM grafiksel arayüzü üzerinden çektiğim snapshotlarıda ekleyeceğim. Ya manuel yada sihirbazları kullanabilirsiniz.Benim tercihim komut satırından PL/SQL paketlerini kullanmak olduğundan, bu yazıda da komut satırı kullanımı üzerine odaklanacağım. Komut satırı kullanımı pek çok kişinin gözünü korkutsada OEM veya Grid Control arayüzünden sihirbazların kullanımı çok daha kompleks ve karılıktır(en azından bana öyle geliyorJ)

 

 

Aşağıda Oracle 11 EM konsolundan SYS ile oturum açıp, “Performans” tabı altında “Advisor” sekmesi altında “SQL Tuning Advisor” linkini tıklıyoruz.

 

 

image001

 

 

 

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ğini anlatacağım.

 

 

 

  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;

/

 

 

Veya aşağıdaki gibi “Create SQL Tuning Set” seçilir. Aşağıdaki örnekte AWR snapshotunda yer alan SQL komutlarından seçim yapılarak set içine alınacak veriler bulunur ve seçilir.

 

 

image002

 

 

image003

 

 

 

Ardından SQL seti SQL cümlelerinin kaynak tüketimine göre sıralanmış olarak oluşturulur.

 

 

 

image004

 

 

 

image005

 

 

Aşağıda manuel olarak SQL set oluştururken veri alımında kullanılan metotlar yer almaktadır.

 

 

 

  • İ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) ve 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;

/

 

 

 

  1. 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. Birinci bölümde iyileştirlme görevinde çoklu komutlardan oluşturulan SQL setlerinin nasıl hazırlandığını görmüştük.

 

 

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;

 

 

 

bind_list: ANY DATA tipinde bind değişkenlerinin sıralı listesi(mesela 100 adlı bind değişkeni için => sql_binds(anydata.ConvertNumber(100))

plan_hash_value: SQL çalıştırma planının hash değeri

sqlset_name: Daha önceden oluşturulan SQL setinin adı

time_limit: Optimizer’ın derleme için harcayacağı saniye değerinden süre

basic_filter:  SQL iyileştirme seti içinden kaynak kullanımı ile ilgili filtreleme yapabilmek için kullanılan filtre değeri veya değerleri

result_limit: Filtre sonucunda göre Top N sıralaması.

result_percentage: Toplam ölçüt değerininin yüzdesi (örneğin paylaşımlı alanının %5 ini kullanan SQL komutlarını bulmak gibi…)

scope: LIMITED veya SCOPE_COMPREHENSIVE değerini alır. Sınırlı yada daha kapsamlı durumlar için tercih edilir, LIMITED seçilirse SQL profil analizi es geçilir.

rank 1-2-3: Oracle kaynak kullanım tercihleri(varsayılan “elapsed_time” değeridir, eğer değiştirilmek istenirse veya yeni kaynak verileri eklenmek istenirse rank2,rank3 parametresine eklenir.)

plan_filter: Aynı komut için birden fazla plan seçildiğinde kullanılan plan filtresidir (plan_hash_value) Aşağıdaki değerlerden birisini alır.

 

 

 

  • LAST_GENERATED: En güncel zaman mührüne sahip plan
  • FIRST_GENERATED: En eski zaman mührüne sahip plan
  • LAST_LOADED: En güncel first_load_time istatistiği olan plan
  • FIRST_LOADED: En eski first_load istatistiğine sahip plan
  • MAX_ELAPSED_TIME: Maksimum tamamlanma süresine sahip plan
  • MAX_BUFFER_GETS: Maksmimum tampon alımına sahip plan
  • MAX_DISK_READS: Maksimum disk okumasına sahip plan
  • MAX_DIRECT_WRITES: Maksimum direkt yazma değerine sahip plan
  • MAX_OPTIMIZER_COST: Maksimum optimizer cost değerine sahip plan

 

 

 

Aşağıda bu paket ile ilgili her üç tip veri alım kriteri örnekleri yer almaktadır. İlk olarak senaryoda kullanılan HR şeması objelerinin incelenmesi için HR kullanıcısına ADVISOR yetkisini tamamlayıp, ardından 2 farklı değişken atıyorum.

 

 

 

GRANT ADVISOR to HR;

variable test_task VARCHAR2(80);

variable test_task2 VARCHAR2(80);

 

 

 

  • SQL text formatından iyileştirme;

 

 

 

EXEC :test_task:= DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_text => ‘SELECT e.employee_id, d.department_name, e.salary, e.hire_date FROM employees e,departments d WHERE e.salary IN (SELECT AVG(salary) FROM employees GROUP BY department_id) AND e.department_id=d.department_id’,

user_name => ’HR’, task_name => ‘test_task’);

 

 

 

  • SQL ID formatı(imleç önbelleğinden);

 

 

 

EXEC :test_task:= DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '7c6hmwaywnha9', task_name => ‘test_task_bycache’);

 

 

 

  • LIMITED scope içinde iyileştirme;

 

 

 

EXEC :test_task:= DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '7c6hmwaywnha9', scope => 'LIMITED', task_name => ‘test_task_bycachescopelimited’);

 

 

 

  • SQL komutunu iyileştirmek için derleme zamanına sadece 10 dakika verilmek istenirse;

 

 

 

EXEC :test_task:= DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '7c6hmwaywnha9', time_limit => 600, task_name => ‘test_task_bycachetimelimited’);

 

 

 

  • AWR içinden ilgili SQL ID numarasına göre iyileştirme;

 

 

 

EXEC :test_task2:= DBMS_SQLTUNE.CREATE_TUNING_TASK(begin_snap => 102,

   end_snap => 103, sql_id => 'lq5m4mtflgw9k', task_name => ‘test_task_byawr’);

 

 

 

  • SQL iyileştirme seti kullanarak iyileştirme; Bu işlemden önce ilgili SQL seti yüklenmelidir. Aşağıdaki örnekte, “buffer_gets” sıralamasına göre bir saat süre boyunca test_sqlset setinden iyileştirme işlemi yer almaktadır.

 

 

 

EXEC :test_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(

  sqlset_name  => 'test_sqlset',

  rank1        => 'BUFFER_GETS',

  time_limit   => 3600,

  task_name => ‘test_taskbysqlset’);

 

 

 

Oracle EM veya Grid Control grafiksel arayüzü üzerinden SQL iyileştirme görevinin oluşturulması örneği aşağıda yer almaktadır.

 

 

 

image006

 

 

 

 

  1. SQL iyileştirme görevinin çalıştırılması

 

 

 

SQL iyileştirme görevini oluşturduktan sonra bu görevin çalıştırılması gerekmektedir. Böylece SQL iyileştirme prosesi başlatılmış olur ve derleme zamanı işlemeye başlar. Bu derleme zamanı, görevi oluştururken verilen “time_limit” değerine bağlıdır. Bu süre sonunda derleme işlemi biter.

 

 

 

BEGIN

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(‘test_task’);

END;

/

 

 

 

Çalışmakta olan bir SQL iyileştirme görevinin durumunu kontrol etmek için USER_ADVISOR_TASKS veya DBA_ADVISOR_LOGS görünümlerine, görevin çalıştırılma sürecini kontrol etmek için ise V$SESSION_LONGOPS görünümüne sorgu çekilebilir. Aşağıdaki ilk sorgu, derlenmesi devam eden iyileştirme görevlerini listeler, ikinci sorgu ise derlenmiş iyileştirme görevlerini listeler.

 

 

 

SELECT SOFAR, TOTALWORK

FROM V$ADVISOR_PROGRESS

WHERE TASK_NAME = ‘test_task';

 

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

 

SELECT task_name, status

FROM dba_advisor_log

WHERE owner = 'HR';

 

 

TASK_NAME           STATUS

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

test_task           COMPLETED

 

1 row selected.

 

 

 

  1. SQL iyileştirme görev sonuçlarının raporlanması

 

 

 

Bu adımda çalıştırılan SQL iyileştirme görevlerinin sonuçları rapor olarak alınmaktadır. Bu işlem için DBMS_SQLTUNE.REPORT_TUNING_TASK fonksiyonu çalıştırılmaktadır. Bu raporda SQL iyileştirme tavsiyesicisinin bulguları ve tavsiyeleri yer almaktadır. Text, HTML veya XML formatında rapor alınabilir ve analiz raporu tipik, temel ve kapsamlı(all) şeklinde olabilmektedir.

 

 

Aşağıda SQL iyileştirme setlerinin raporlanması için kullanılan sentaks yer almaktadır.

 

 

 

DBMS_SQLTUNE.REPORT_TUNING_TASK(

   task_name     IN  VARCHAR2,

   type          IN  VARCHAR2   := TEXT | HTML |XML ,

   level         IN  VARCHAR2   := TYPICAL | BASIC | ALL ,

   section       IN  VARCHAR2   := FINDING | PLAN | INFORMATION | ERROR | ALL ,

   object_id     IN  NUMBER     := NULL,

   result_limit  IN  NUMBER     := NULL -- rapor içinde bulunacak maksimum SQL komut sayısı-- )

 

 

 

Bunun yanında Oracle 11.2.0.2 sürümünden itibaren otomatik SQL iyileştirme görev sonuçlarının raporlanması aşağıdaki komutla yapılır. Ancak hala oto rapor fonksiyonuyla ilgili “bug” lar mevcuttur(mesela fonsiyonda üstteki maneul raporlama gibi LEVEl ve SECTION parametreleri varsayılan ayarlardan değiştirilirken alınan ORA-01748 hatası gibi…).

 

 

SELECT dbms_auto_sqltune.report_auto_tuning_task FROM dual;

 

 

 

Aşağıda bu örnekte kullandığım test_task adlı iyileştirme görevinin raporu yer almaktadır. LEVEL ve SECTION parametre değerlerini ALL olarak atıyorum.

 

 

 

SET LONG 10000

SET PAGESIZE 1000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('test_task', ‘TEXT’, ‘ALL’, ‘ALL’) FROM dual;

 

 

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_TASK','TEXT','ALL','ALL')

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

 

GENERAL INFORMATION SECTION

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

Tuning Task Name                  : test_task

Tuning Task Owner                 : HR

Tuning Task ID                    : 256

Scope                             : COMPREHENSIVE

Time Limit(seconds)               : 1800

Completion Status                 : COMPLETED

Started at                        : 05/09/2011 15:44:49

Completed at                      : 05/09/2011 15:44:55

Number of Index Findings          : 1

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

Schema Name: HR

SQL ID     : 7c6hmwaywnha9

SQL Text   : SELECT e.employee_id, d.department_name, e.salary, e.hire_date FROM employees e,departments d WHERE e.salary IN (SELECT AVG(salary) FROM employees GROUP BY department_id) AND

e.department_id=d.department_id

 

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

FINDINGS SECTION (1 finding)

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

 

1- Index Finding (see explain plans section below)

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

  The execution plan of this statement can be improved by creating one or more indices.

 

  Recommendation (estimated benefit: 100%)

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

  - Consider running the Access Advisor to improve the physical schema design or creating the recommended index.

  - Create index HR.IDX$$_01000001 on HR.EMPLOYEES('SALARY');

 

  Rationale

  ---------

Creating the recommended indices significantly improves the execution plan of this statement. However, it might be preferable to run "Access Advisor" using a representative SQL workload as opposed to a single statement. This will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption.

 

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

EXPLAIN PLANS SECTION

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

 

1- Original

-----------

Plan hash value: 3509108563

 

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

| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU) | Time     |

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

|   0 | SELECT STATEMENT             |             |     2 |    96 |     9  (23) | 00:00:01 |

|   1 |  NESTED LOOPS                |             |     2 |    96 |     9  (23) | 00:00:01 |

|*  2 |   HASH JOIN SEMI             |             |     2 |    64 |     8  (25) | 00:00:01 |

|   3 |    TABLE ACCESS FULL         | EMPLOYEES   |   107 |  2033 |     3   (0) | 00:00:01 |

|   4 |    VIEW                      | VW_NSO_1    |     1 |    13 |     4  (25) | 00:00:01 |

|*  5 |     FILTER                   |             |       |       |

|          |

|   6 |      HASH GROUP BY           |             |     1 |     7 |     4  (25) | 00:00:01 |

|   7 |       TABLE ACCESS FULL      | EMPLOYEES   |   107 |   749 |     3   (0) | 00:00:01 |

|   8 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0) | 00:00:01 |

|*  9 |    INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0) | 00:00:01 |

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

 

Query Block Name / Object Alias (identified by operation id):

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

 

   1 - SEL$5DA710D3

   3 - SEL$5DA710D3 / E@SEL$1

   4 - SEL$683B0107 / VW_NSO_1@SEL$5DA710D3

   5 - SEL$683B0107

   7 - SEL$683B0107 / EMPLOYEES@SEL$2

   8 - SEL$5DA710D3 / D@SEL$1

   9 - SEL$5DA710D3 / D@SEL$1

 

Predicate Information (identified by operation id):

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

 

   2 - access("E"."SALARY"="$nso_col_1")

   5 - filter(AVG("SALARY")>0)

   9 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

 

Column Projection Information (identified by operation id):

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

 

   1 - (#keys=0) "E"."SALARY"[NUMBER,22], "E"."EMPLOYEE_ID"[NUMBER,22],

       "E"."HIRE_DATE"[DATE,7], "D"."DEPARTMENT_NAME"[VARCHAR2,30]

   2 - (#keys=1) "E"."SALARY"[NUMBER,22], "E"."EMPLOYEE_ID"[NUMBER,22],

       "E"."HIRE_DATE"[DATE,7], "E"."DEPARTMENT_ID"[NUMBER,22]

   3 - "E"."EMPLOYEE_ID"[NUMBER,22], "E"."HIRE_DATE"[DATE,7],

       "E"."SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22]

   4 - "$nso_col_1"[NUMBER,22]

   5 - AVG("SALARY")[22]

   6 - (#keys=1) "DEPARTMENT_ID"[NUMBER,22], AVG("SALARY")[22]

   7 - "SALARY"[NUMBER,22], "DEPARTMENT_ID"[NUMBER,22]

   8 - "D"."DEPARTMENT_NAME"[VARCHAR2,30]

   9 - "D".ROWID[ROWID,10]

 

2- Using New Indices

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

Plan hash value: 2906953147

 

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

| Id  | Operation                     | Name           | Rows  | Bytes | Cost (% CPU)| Time     |

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

|   0 | SELECT STATEMENT              |                |     2 |    96 |     7 (29)| 00:00:01 |

|   1 |   NESTED LOOPS                 |                |     2 |   96 |     7 (29)| 00:00:01 |

|   2 |   NESTED LOOPS                |                |     2 |    64 |     6 (34)| 00:00:01 |

|   3 |    VIEW                       | VW_NSO_1       |     1 |    13 |     4 (25)| 00:00:01 |

|   4 |     HASH UNIQUE               |                |     1 |     7 |     4 (25)| 00:00:01 |

|*  5 |      FILTER                   |                |       |       |     |                |

|   6 |       HASH GROUP BY           |                |     1 |     7 |     4 (25)| 00:00:01 |

|   7 |        TABLE ACCESS FULL      | EMPLOYEES      |   107 |   749 |     3 (0)| 00:00:01  |

|   8 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |     2 |    38 |     1 (0)| 00:00:01  |

|*  9 |     INDEX RANGE SCAN          | IDX$$_01000001 |     2 |       |     0 (0)| 00:00:01  |

|  10 |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS    |     1 |    16 |     1 (0)| 00:00:01 |

|* 11 |    INDEX UNIQUE SCAN          | DEPT_ID_PK     |     1 |       |     0 (0)| 00:00:01 |

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

 

Query Block Name / Object Alias (identified by operation id):

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

 

   1 - SEL$5DA710D3

   3 - SEL$683B0107 / VW_NSO_1@SEL$5DA710D3

   4 - SEL$683B0107

   7 - SEL$683B0107 / EMPLOYEES@SEL$2

   8 - SEL$5DA710D3 / E@SEL$1

   9 - SEL$5DA710D3 / E@SEL$1

  10 - SEL$5DA710D3 / D@SEL$1

  11 - SEL$5DA710D3 / D@SEL$1

 

Predicate Information (identified by operation id):

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

 

   5 - filter(AVG("SALARY")>0)

   9 - access("E"."SALARY"="$nso_col_1")

  11 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

 

Column Projection Information (identified by operation id):

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

 

   1 - (#keys=0) "E"."EMPLOYEE_ID"[NUMBER,22], "E"."HIRE_DATE"[DATE,7],

       "E"."SALARY"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30]

   2 - (#keys=0) "E"."EMPLOYEE_ID"[NUMBER,22], "E"."HIRE_DATE"[DATE,7],

       "E"."SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22]

   3 - "$nso_col_1"[NUMBER,22]

   4 - (#keys=1) AVG("SALARY")[22]

   5 - AVG("SALARY")[22]

   6 - (#keys=1) "DEPARTMENT_ID"[NUMBER,22], AVG("SALARY")[22]

   7 - "SALARY"[NUMBER,22], "DEPARTMENT_ID"[NUMBER,22]

   8 - "E"."EMPLOYEE_ID"[NUMBER,22], "E"."HIRE_DATE"[DATE,7], 

       "E"."SALARY"[NUMBER,22],

       "E"."DEPARTMENT_ID"[NUMBER,22]

   9 - "E".ROWID[ROWID,10], "E"."SALARY"[NUMBER,22]

  10 - "D"."DEPARTMENT_NAME"[VARCHAR2,30]

  11 - "D".ROWID[ROWID,10]

 

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

 

 

 

Yukardaki raporda iyileştirme görevi kapsamındaki SQL komut textinin 1. kısım mevcut mimaride çalıştırılması halinde çalıştırma planını, 2. kısım ise tavsiye sonucunda çalıştırılması halinde yeni çalıştırma planını göstermektedir.

 

 

Aşağıda Oracle EM veya Grid Control grafiksel arayüzünden alınan bir tavsiye raporu örneği yer almaktadır.

 

 

 

image007

 

 

 

image008

 

 

 

İyileştirme işlemi tamamlandığında veya ilerde bu SQL iyileştirme görevlerine ihtiyaç kalmadığında, tüm içeriğiyle beraber Oracle sistemden silinebilir.

 

 

BEGIN

  DBMS_SQLTUNE.drop_tuning_task (task_name => 'test _task');

  DBMS_SQLTUNE.drop_tuning_task (task_name => 'test _taskbycache');

  DBMS_SQLTUNE.drop_tuning_task (task_name => 'test_taskbycachescopelimited');

DBMS_SQLTUNE.drop_tuning_task (task_name => 'test_taskbycachetimelimited');

DBMS_SQLTUNE.drop_tuning_task (task_name => 'test_taskbyawr');

  DBMS_SQLTUNE.drop_tuning_task (task_name => 'test_taskbysqlset');

END;

/

 

 

Bu makalede Oracle 10g itibariyle kullanıma sunulan, SQL komutlarını otomatik olarak iyileştirmek için tavsiye raporları sunan Oracle Tuning Advisor ile SQL iyileştirme görevlerinin hazırlanmasını inceledik.

 

Tarih : 15 Mayıs 2011 Pazar 15:39 Yayınlayan: Ugur INAL

Yorumlar

 

Hakan UZUNER

Uğur hocam çok ince makaleler yazıyorsun, gerçekten tebrik ederim, anlayana bulunmaz kaynaklar bunlar :)

Mayıs 15, 2011 15:57
 

Ufuk TATLIDİL

Elinize sağlık.

Mayıs 15, 2011 21:48
 

Tayfun DEĞER

Elinize sağlık.

Mayıs 16, 2011 10:41
 

Ugur INAL

Teşekkürler Hakan hocam :)

Mayıs 17, 2011 15:55
Kimliksiz yorumlar seçilemez kılınmış durumdadır.

Yazar: Ugur INAL

http://uguroracle.blogspot.com

Bu Kategori

Hızlı aktarma