SQL tuning araçları otomatik veya manueldir. Bu bağlamda, veritabanının kendisi tanı, tavsiye veya düzeltici eylemler sağlayabilirse, bir araç otomatik hale getirilir. Manuel bir araç, bu işlemlerin tümünü gerçekleştirmenizi gerektirir.

Tüm ayarlama araçları, veritabanı örneğinin topladığı dynamic performance view lerinin, istatistiklerin ve metriklerin temel araçlarına bağlıdır. Veritabanının kendisi, SQL ifadelerini ayarlamak için gereken verileri ve meta verileri içerir.

Otomatik SQL Tuning Araçları

Oracle Database, SQL tuningile ilgili birçok advisor sunar.

Ek olarak, SQL plan management performans gerilemelerini önleyebilen ve SQL performansını iyileştirmenize yardımcı olabilecek bir mekanizmadır.

Tüm otomatik SQL tuning araçları, SQL tuning setlerini giriş olarak kullanabilir. SQL tuning set (STS), execution istatistikleri ve execution context ile birlikte bir veya daha fazla SQL ifadesi içeren bir veritabanı nesnesidir. Otomatik sql tuning araçları aşağıdakiler gibidir.

  1. Automatic Database Diagnostic Monitor (ADDM)
  2. SQL Tuning Advisor
  3. SQL Access Advisor
  4. SQL Plan Management
  5. SQL Performance Analyzer

Automatic Database Diagnostic Monitor (ADDM)

ADDM, Oracle Veritabanına yerleşik kendi kendine diagnostic (teşhis) yazılımıdır.

ADDM, performans sorunlarının kök nedenlerini otomatik olarak bulabilir, düzeltme önerileri sağlayabilir ve beklenen faydaları ölçebilir. ADDM ayrıca hiçbir işlem yapılmayan alanları da belirler.

ADDM ve diğer advisorlar, istatistikleri toplamak, sürdürmek ve kullanmak için veritabanı bileşenlerine hizmet sağlayan bir altyapı olan Automatic Workload Repository (AWR) kullanır. ADDM, yüksek yüke neden olan SQL de dahil olmak üzere olası performans sorunlarını belirlemek için AWR’deki istatistikleri inceler ve analiz eder.

Örneğin, ADDM’yi gece çalışacak şekilde yapılandırabilirsiniz. Sabahları, soruna neyin neden olduğunu ve önerilen bir düzeltme olup olmadığını görmek için en son ADDM raporunu inceleyebilirsiniz. Rapor, belirli bir SELECT ifadesinin çok miktarda CPU tükettiğini gösterebilir ve SQL Tuning Advisor’ı çalıştırmanızı önerebilir.

ADDM analizi yukarıdan aşağıya doğru yapılır, önce semptomları tanımlar ve sonra performans problemlerinin kök nedenlerine ulaşmak için analizi inceler. ADDM, performans sorunlarını tanımlamak için DB time statistic (zaman istatistiğini) kullanır. Veritabanı zamanı (DB) zamanı, boşta olmayan tüm kullanıcı oturumlarının bekleme süresi ve CPU zamanı da dahil olmak üzere, kullanıcı isteklerini işlemede veritabanının harcadığı kümülatif zamandır.

Performans sorunlarını tanılamanın yanı sıra, ADDM olası çözümleri de önerir. Uygun olduğunda, ADDM, içinden seçim yapabileceğiniz birden fazla çözüm önerir.Örneğin;

  • Donanım değişiklikleri
  • CPU ekleme veya I/O sisteminin konfigurasyonu
  • Veri tabanı yapılandırması
  • Başlatma parametresi ayarlarının değiştirilmesi
  • Şema değişiklikleri
  • Bir tablo veya indexi bölümlere ayırma ya da automatic segment space management kullanma karma
  • Uygulama değişiklikleri
  • Indexler için önbellek seçeneğini kullanma veya bind variable kullanma
  • Diğer danışmanları kullanmak


Automatic database diagnostic monitoring varsayılan olarak etkindir ve CONTROL_MANAGEMENT_PACK_ACCESS ve STATISTICS_LEVEL başlatma parametreleri tarafından kontrol edilir.

Automatic database diagnostic monitoringi etkinleştirmek için CONTROL_MANAGEMENT_PACK_ACCESS DIAGNOSTIC + TUNING (varsayılan) veya DIAGNOSTIC olarak ayarlayın. CONTROL_MANAGEMENT_PACK_ACCESS’ın NONE olarak ayarlanması, ADDM dahil olmak üzere birçok Oracle Database özelliğini devre dışı bırakır ve kesinlikle önerilmez.

Automatic database diagnostic monitoringi etkinleştirmek için STATISTICS_LEVEL seçeneğini TYPICAL (varsayılan) veya ALL olarak ayarlayın. STATISTICS_LEVEL ayarını BASIC olarak ayarlamak, ADDM dahil birçok Oracle Database özelliğini devre dışı bırakır ve kesinlikle önerilmez.

SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL> 
SQL> show parameter STATISTICS_LEVEL               

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

I/O performansının ADDM analizi, kısmen, I/O alt sisteminin beklenen performansını tanımlayan tek bir argümana (DBIO_EXPECTED) bağlıdır. DBIO_EXPECTED değeri, tek bir veritabanı bloğunu mikrosaniye cinsinden okumak için geçen ortalama süredir. Oracle Database, çoğu sabit disk için uygun bir değer olan 10 milisaniyelik varsayılan değeri kullanır. Donanımınızın özelliklerine göre farklı bir değer seçebilirsiniz.

Depolama sisteminin I/O kapasitesinin nasıl değerlendirileceğini başka bir yazıda detaylı olarak analatacağım. Merak edenler dd komutunu veya DBMS_RESOURCE_MANAGER.CALIBRATE_IO paketini kullanabilir.

ADDM tüm bu analizleri yaparken AWR snapshotlarından yararlanır. Varsayılan olarak, Automatic Workload Repository (AWR) saat başı bir kez performans verilerinin anlık görüntülerini oluşturur ve 8 gün boyunca tutar. Hem anlık görüntü aralığı hem de saklama süresi için varsayılan değerleri değiştirebilirsiniz.

Varsayılan olarak, ADDM, bu süre zarfında AWR tarafından çekilen anlık görüntüleri analiz etmek için her saatte bir çalışır. Veri tabanı performans sorunları bulursa, bunları da enterprise manager aracılığıyla görebiliriz.

Sql plus’dan addm raporu oluşturmak için ise aşağıdaki sorguyu kullanabiliriz.

@$ORACLE_HOME/rdbms/admin/addmrpt.sql

Daha sonra mevcut tüm snapshotları listeler ve başlangıç ve bitiş snapshotlarını rapor adıyla birlikte girmenizi ister ve sonuç olarak aşağıdaki gibi bir çıktı üretir.

DBMS_ADVISOR paketi, ADDM görevleri de dahil olmak üzere herhangi bir advisor görevi oluşturmak ve çalıştırmak için kullanılabilir. Aşağıdaki örnek, tipik bir ADDM raporu oluşturmak, çalıştırmak ve görüntülemek için nasıl kullanıldığını göstermektedir. Öncelikle dba_hist_snapshot’ından addm için kullanacağımız snapshotlarımızı seçiyoruz.

BEGIN
  -- Create an ADDM task.
  DBMS_ADVISOR.create_task (
    advisor_name      => 'ADDM',
    task_name         => 'ADDM_TEST_MUSTAFA',
    task_desc         => 'Advisor for snapshots 32217 to 32337.');

  -- Set the start and end snapshots.
  DBMS_ADVISOR.set_task_parameter (
    task_name => 'ADDM_TEST_MUSTAFA',
    parameter => 'START_SNAPSHOT',
    value     => 32217);

  DBMS_ADVISOR.set_task_parameter (
    task_name => 'ADDM_TEST_MUSTAFA',
    parameter => 'END_SNAPSHOT',
    value     => 32337);

  -- Execute the task.
  DBMS_ADVISOR.execute_task(task_name => 'ADDM_TEST_MUSTAFA');
END;
/

-- Display the report.
SET LONG 1000000 LONGCHUNKSIZE 1000000
SET LINESIZE 1000 PAGESIZE 0
SET TRIM ON TRIMSPOOL ON
SET ECHO OFF FEEDBACK OFF

SELECT DBMS_ADVISOR.get_task_report('ADDM_TEST_MUSTAFA') AS report FROM   dual;
SET PAGESIZE 24

İlgili Görünümler

Enterprise Manager veya GET_TASK_REPORT işlevini kullanmadan ADDM çıktısını görüntülemek için aşağıdaki görünümler kullanılabilir.

  • DBA_ADVISOR_TASKS – Mevcut görevler hakkında temel bilgiler.
  • DBA_ADVISOR_LOG – Mevcut görevler hakkında durum bilgisi.
  • DBA_ADVISOR_FINDINGS – Mevcut bir görev için tanımlanmış bulgular.
  • DBA_ADVISOR_RECOMMENDATIONS – Mevcut bir görev tarafından belirlenen problemler için öneriler.

Oracle Enterprise Manager Cloud Control (Cloud Control) 12c ‘de sunulan Real-Time ADDM, geleneksel olarak veritabanını yeniden başlatmanızı gerektiren yanıt vermeyen veya asılı kalan veritabanlarındaki sorunları analiz etmenize ve çözmenize yardımcı olur. Real Time ADDM, veritabanının geçerli performansını analiz etmek için önceden tanımlanmış bir dizi ölçüt üzerinden çalışır. Sorunu analiz ettikten sonra, Gerçek Zamanlı ADDM, deadlock, hang, shared pool contention ve diğer hatalar gibi tanımlanmış sorunları veritabanını yeniden başlatmanıza gerek kalmadan çözmenize yardımcı olur.

Veritabanının durumuna bağlı olarak, Real Time ADDM, Cloud Control kullanarak veritabanına bağlanırken iki farklı bağlantı modu kullanır:

  • Normal connection

Bu modda, Real-Time ADDM, veritabanına normal bir JDBC bağlantısı kurar. Bu mod, bazı bağlantıların mevcut olduğu durumlarda veritabanının kapsamlı performans analizini gerçekleştirmeyi amaçlar.

  • Diagnostic connection

Bu modda, Real-Time ADDM, veritabanına latch-less bir bağlantı gerçekleştirir. Bu mod normal bir JDBC bağlantısı yapılamadığında aşırı sıkışma durumları için tasarlanmıştır.

Oracle Database 12c ile başlayan Real-Time ADDM geçici veritabanı performans sorunlarını proaktif olarak tespit eder. Bunu yapmak için, Gerçek Zamanlı ADDM her 3 saniyede bir otomatik olarak çalışır ve veritabanındaki performans artışlarını teşhis etmek için bellekteki verileri kullanır.

Gerçek Zamanlı ADDM, aşağıdaki adımlarda açıklandığı gibi bir performans sorunu algılandığında otomatik olarak bir analizi tetikler:

  • Her 3 saniyede bir monitor process (MMON) kilitlenmeden veya kilitlenmeden performans istatistiklerini elde etmek için bir eylem gerçekleştirir.
  • MMON işlemi bu istatistikleri kontrol eder ve aşağıdaki tabloda listelenen sorunlardan herhangi biri tespit edilirse Real-Time bir ADDM analizini tetikler.
  • MMON prosesesi işlemi, raporu oluşturur ve AWR’de saklar.

Raporun meta verilerini görüntülemek için DBA_HIST_REPORTS görünümünü kullanın.

Sorun Koşul
High load Ortalama aktif oturumlar CPU çekirdek sayısının 3 katından fazla
I/O bound Single block okuma performansına dayalı etkin oturumlarda I/O etkisi
CPU bound Aktif oturumlar toplam yükün% 10’undan veya CPU kullanımı% 50’den büyük ise
Over-allocated memory physical memory’nin %95’den fazlası allocations are over 95% edilirse
Interconnect bound Single bloğu interconnect’den transfer süresine göre
Session limit Session limit’i dolarsa
Process limit Process limit’i dolarsa
Hung session Askıda kalan sessiomlar toplam sessionların %10’dan büyük olursa
Deadlock detected Deadlock olduğunda

NOT : Otomatik triggerlerin çok fazla sistem kaynağı tüketmemesini ve sistemi ele geçirmemesini sağlamak için, Gerçek Zamanlı ADDM birtakım denetimleri kullanır.

SQL Tuning Advisor

SQL Tuning Advisor, sorunlu SQL ifadelerini tanımlayan ve sorgu performansını nasıl artıracağınızı öneren internal bir yazılımıdır. Yani sql cümlelerini analiz eder ve performans artırıcı olabilecek tavsiyeleri sunar. SQL Tuning Advisor, Oracle Database Tuning Pack’deki sql diagnostic(teşhis) yazılımıdır.

SQL Tuning Advisor, bir veya daha fazla SQL sorgusunu girdi olarak alır ve sorgularda SQL tuning  yapmak için Automatic SQL Tuning Advisor’i çağırır. Advisor aşağıdaki analiz türlerini gerçekleştirir:

  • Eksik veya eski istatistikler için kontroller
  • SQL profilleri oluşturur

SQL profili, bir SQL ifadesine özgü bir yardımcı bilgi kümesidir. Bir SQL profili, Automatic SQL Tuning sırasında keşfedilen en düşük optimizer tahminleri için düzeltmeler içerir. Bu bilgi, execution plandaki bir işlem ve seçicilik tarafından döndürülen veya gerçekte döndürülen satır sayısı olan cardinality için optimizer tahminlerini geliştirebilir. Bu gelişmiş tahminler, optimizer’ın daha iyi planlar seçmesine yol açar.

  • SQL plan baselines oluşturulması
  • Index oluşturulması
  • SQL sorgularını yeniden yazma
  • Farklı bir Access path’in performansı önemli ölçüde iyileştirip iyileştiremeyeceğini araştırıyor

SQL tuning Advisor çıktısı, her bir öneri ve beklenen yararı için bir gerekçeyle birlikte, tavsiye veya tavsiyeler şeklindedir. Tavsiye, nesnelerle ilgili istatistiklerin toplanması, yeni indexlerin oluşturulması, SQL ifadesinin yeniden yapılandırılması veya bir SQL profilinin oluşturulması ile ilgilidir. SQL ifadelerinin ayarını tamamlamak için önerileri kabul etmeyi seçebilirsiniz.

Automatic Tuning Optimizer, SQL Tuning Advisor tarafından kullanılan merkezi araçtır. Optimizer, SQL sorgularını birden fazla kaynaktan girdi olarak alabilir(ADDM,AWR,SQL area,Sql tuning sets), bu ifadeleri optimizeri kullanarak analiz edebilir ve ardından önerilerde bulunabilir.

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.

Cloud Control (tercih edilen) veya bir komut satırı arayüzü kullanarak Automatic SQL Tuning taskı etkinleştirebilir veya devre dışı bırakabilirsiniz. Cloud control la bunu arayüzden çok rahat halledebiliriz veya da komut satırından aşağıdaki yöntemlerden birini kullanırız.

  • DBMS_AUTO_TASK_ADMIN packageinden ENABLE veya DISABLE prosedürünü çağırırız.
BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE (
    client_name => 'sql tuning advisor'
,   operation   => NULL
,   window_name => NULL
);
END;
/

Aşağıdaki sorguylada kontrol edebiliriz.

COL CLIENT_NAME FORMAT a20

SELECT CLIENT_NAME, STATUS
FROM   DBA_AUTOTASK_CLIENT
WHERE  CLIENT_NAME = 'sql tuning advisor';
 
CLIENT_NAME          STATUS
-------------------- --------
sql tuning advisor   ENABLED
  • Automatic SQL Tuning Advisor dahil tüm advisor ve istatistiklerin toplanmasını devre dışı bırakmak için STATISTICS_LEVEL başlatma parametresini BASIC olarak ayarlayabiliriz.
ALTER SYSTEM SET STATISTICS_LEVEL ='BASIC';

Aşağıdaki sorguylada kontrol edebiliriz.

SHOW PARAMETER statistics_level

DBMS_AUTO_SQLTUNE packegi, SET_AUTO_TUNING_TASK_PARAMETER prosedürünü kullanarak task parametrelerini belirterek otomatik SQL tuning yapılandırmanıza olanak tanır.

ACCEPT_SQL_PROFILE ayar task parametresi, SQL profillerinin otomatik olarak uygulanıp uygulanmayacağını (true) mı yoksa kullanıcı müdahalesi mi (false) istediğini belirtir. Varsayılan değer AUTO’dur, yani bir SQL profilinde en az bir SQL ifadesi varsa doğru, bu koşul yerine getirilmezse false olur. 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.

NOT : Otomatik uygulama etkinleştirildiğinde, advisor yalnızca SQL profilleri oluşturmak için önerilerde bulunur. Yeni dizinler oluşturma, optimizer istatistikleri toplama ve SQL plan taban çizgileri oluşturma gibi öneriler otomatik olarak uygulanmıyor.

COLUMN parameter_value FORMAT A30

SELECT parameter_name, parameter_value
FROM   dba_advisor_parameters
WHERE  task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND    parameter_name = 'ACCEPT_SQL_PROFILES';

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 tuning advisor görevi oluşturulur.
  3. SQL tuning advisor görevi çalıştırılır.
  4. SQL tuning advisor görevinin sonuçları görüntülenir.
  5. SQL iyileştirme görev sonuçlarının görüntülemesi ve uygulanması

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  => 'SQLT_WKLD_STS' 
,   description  => 'STS to store SQL from the private SQL area' 
);
END; 

Bir STS’yi SQL ifadeleriyle yüklemek için, DBMS_SQLTUNE veya DBMS_SQLSET paketindeki LOAD_SQLSET prosedürünü kullanılır.  STS’yi doldurmak için standart kaynaklar AWR, başka bir STS veya shared SQL areadır.

  • Shared SQL Area’dan yükleme

Shared SQL area’da 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, cursor önbelleğinde bulunan SQL textlerinin içinde employees kelimesi geçen ve HR kullanıcısına ait tüm SQL komutları seçilip SQLT_WKLD_STS adlı SQL seti içerisine yüklenmektedir.

DECLARE
  c_sqlarea_cursor   DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN c_sqlarea_cursor  FOR
     SELECT VALUE(X)
     FROM   TABLE( DBMS_SQLTUNE.select_cursor_cache(
                basic_filter   => 'sql_text LIKE ''%employees %''  and parsing_schema_name = ''HR''',
                attribute_list => 'ALL')
            ) X;
  DBMS_SQLTUNE.load_sqlset(sqlset_name     => 'SQLT_WKLD_STS',
                           populate_cursor => c_sqlarea_cursor );
END;
/
  • AWR raporundan yükleme

13 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
  c_awr  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN c_awr 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     => 'SQLT_WKLD_STS',

                           populate_cursor => c_awr);
END;
/

SQL tuning advisor görevi oluşturulur

SQL tuning görevi oluşturmak için DBMS_SQLTUNE.CREATE_TUNING_TASK functionını çalıştırırız.

Aşağıdakilerden herhangi birinin ayarlama görevlerini oluşturabilirsiniz:

  • Tek bir SQL sorgusu metni
  • Birden fazla ifade içeren bir SQL tuning seti
  • Shared SQL areadan SQL ID aracılığıyla seçilen bir SQL sorgusu
  • AWR raporundaki bir SQL komutununun SQL ID değeri seçilerek

Scope parametresi bu işlev için en önemli olanlardan biridir. Bu parametreyi aşağıdaki değerlere ayarlayabilirsiniz:

  • LIMITED

SQL Tuning Advisor, istatistiksel kontrollere, erişim yolu analizine ve SQL yapı analizine dayalı öneriler sunar. SQL profil önerileri oluşturulmaz.

  • COMPREHENSIVE

SQL Tuning Advisor, sınırlı kapsam dahilinde gerçekleştirdiği tüm analizleri ve SQL profillemeyi gerçekleştirir.

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;
DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext   CLOB;
BEGIN
  my_sqltext := 'SELECT /*+ ORDERED */ * '                      ||
                'FROM employees e, locations l, departments d ' ||
                'WHERE e.department_id = d.department_id AND '  ||
                      'l.location_id = d.location_id AND '      ||
                      'e.employee_id < :bnd'; my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_text => my_sqltext
,         bind_list   => sql_binds(anydata.ConvertNumber(100))
,         user_name   => 'HR'
,         scope       => 'COMPREHENSIVE'
,         time_limit  => 60
,         task_name   => 'STA_SPECIFIC_EMP_TASK'
,         description => 'Task to tune a query on a specified employee'
);
END;
/ 

veya

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,
  con_name         IN VARCHAR2  := NULL,
  database_link_to IN VARCHAR2  := NULL)
RETURN VARCHAR2;
/
  • Bir SQL ID’den 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,
  con_name         IN VARCHAR2  := NULL,
  database_link_to IN VARCHAR2  := NULL)
RETURN VARCHAR2;
  • 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,
  con_name         IN VARCHAR2  := NULL,
  dbid             IN NUMBER    := NULL,
  database_link_to IN VARCHAR2  := NULL)
RETURN VARCHAR2;
  • 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,
  database_link_to IN VARCHAR2  :=  NULL)
RETURN VARCHAR2;

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

Birkaç örnek yapacak olursak;

  • SQL ID formatına göre iyileştirme
EXEC :test_task:= DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '7c6hmwaywnha9', task_name => 'test_task_bycache');
  • SQL text formatından iyileştirme;
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
 sql_text => 'SELECT quantity_sold FROM sales s, times t WHERE s.time_id = t.time_id AND s.time_id = TO_DATE(''24-NOV-00'')');
  • LIMITED scope içinde iyileştirme;
EXEC :test_task:= DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '7c6hmwaywnha9', scope => 'LIMITED', task_name => 'test_task_bycachescopelimited');
  • AWR içinden ilgili SQL ID numarasına göre iyileştirme;
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(begin_snap => 1, -

end_snap => 2, sql_id => 'ay1m3ssvtrh24');

  • 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 :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
  sqlset_name  => 'my_workload', -
  rank1        => 'BUFFER_GETS', -
  time_limit   => 3600, -
  description  => 'tune my workload ordered by buffer gets');

SQL tuning advisor  görevi çalıştırılır

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.

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK('test_task');
END;
/

SQL tuning advisor  görevinin sonuçları görüntülenir

Ç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 veya V$ADVISOR_PROGRESS 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.

SQL iyileştirme görev sonuçlarının görüntülemesi ve uygulanması

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.

SQL ID formatına göre iyileştirme

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.

SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'STA_SPECIFIC_EMP_TASK' )
FROM   DUAL;

İ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;
/

Komple bir örnek yapacak olursak;

Mesela aşağıdaki gibi bir SQL çalıştıralım

SELECT JOB_TITLE, DEPARTMENT_NAME, LAST_NAME, START_DATE
  FROM HR.JOB_HISTORY
       JOIN HR.JOBS USING (JOB_ID)
       JOIN HR.DEPARTMENTS USING (DEPARTMENT_ID)
       JOIN HR.EMPLOYEES USING (EMPLOYEE_ID)
 WHERE TO_CHAR (START_DATE, 'YYYY') BETWEEN 2000 AND 2005;

SQL ID’sine göre bir sql iyileştirme çalıştıralım.

DECLARE
   stmt_task   VARCHAR2 (64);
BEGIN
   stmt_task :=
      DBMS_SQLTUNE.
       create_tuning_task (
         sql_id            => '18tty0q87xg03',
         plan_hash_value   => '596485830',
         time_limit        => 3600,
         task_name         => 'Tune_18tty0q87xg03',
         description       => 'Task to tune 18tty0q87xg03 sql_id');
END;
/

Oluşturduğumuz iyileştirmeyi çalıştırırız.

EXECUTE dbms_sqltune.execute_tuning_task('Tune_18tty0q87xg03');

SQL iyileştirmenin raporunu görüntüleriz

SQL> SET LONG 1000
SQL> SET LONGCHUNKSIZE 1000
SQL> SET LINESIZE 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'Tune_18tty0q87xg03' )
  2  FROM   DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_18TTY0Q87XG03')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : Tune_18tty0q87xg03
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 3600
Completion Status  : COMPLETED
Started at         : 01/28/2019 14:35:52
Completed at       : 01/28/2019 14:35:52


DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_18TTY0Q87XG03')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : 18tty0q87xg03
SQL Text   : SELECT JOB_TITLE, DEPARTMENT_NAME, LAST_NAME, START_DATE
               FROM HR.JOB_HISTORY
                    JOIN HR.JOBS USING (JOB_ID)
                    JOIN HR.DEPARTMENTS USING (DEPARTMENT_ID)
                    JOIN HR.EMPLOYEES USING (EMPLOYEE_ID)
              WHERE TO_CHAR (START_DATE, 'YYYY') BETWEEN 2000 AND 2005

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_18TTY0Q87XG03')
----------------------------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------

Önerilen optimizasyonu uygulamak için uygulama komut dosyasını da oluşturabilirsiniz:

SELECT dbms_sqltune.script_tuning_task('Tune_0t5fw0gtf0665', 'ALL') FROM dual;

SQL Access Advisor

Sql cümlelerini analiz eder ve index,view,partition,materialized viewlar ile ilgili tavsiyeler sunar.

SQL Access Advisor, SQL tuning set de dahil olmak üzere çeşitli kaynaklardan gelen girdileri kabul eder ve ardından önerilerde bulunur.

Materialized view, partition ve indexler karmaşık, veri yoğun sorgulamalar için optimum performans elde etmek amacıyla bir veritabanını ayarlarken çok önemlidir. SQL Access Advisor girdi olarak gerçek bir iş yükü alır veya bir şemadan varsayımsal bir iş yükü türetir. Advisor daha sonra daha hızlı execution path için erişim yapıları önerir. Danışman aşağıdaki avantajları sağlar:

  • Uzman bilgisine sahip olmanızı gerektirmez
  • Optimizerda bulunan kurallara dayalı kararlar verir.
  • SQL erişiminin tüm yönlerini tek bir danışmanda kapsar
  • Cloud Control’de basit, kullanıcı dostu GUI sihirbazları sağlar
  • Önerilerin uygulanması için komut dosyaları oluşturur

Automatic Tuning Optimizer, SQL Access Advisor tarafından kullanılan merkezi araçtır.

Bir sorgu için partitioning ve index oldukça önemlidir ve sorgu performansını direkt olarak iyileştirebilir. Bu sebepten dolayı SQL Access Advisor bu konular üzerine yoğunlaşmaktadır. Bu danışmanın bir diğer faydalı yanı ise bu tavsiyelerde bulunurken iş yüküne göre de hareket etmektedir. Index ve partitioning eklemek performansı arttırabilir ancak fiziğin temel kanunu olarak bir yandan enerji gelirken diğer yandan enerji çıkmalıdır. Buradaki durumda ise çıkan kaybettiğimiz disk alanı, giren ise artan performansdır. SQL Access Advisor bunun da optimum düzeyde gerçekleşmesini sağlar ve orantılı bir ölçüde tavsiyelerde bulunur.

SQL Access Advisor Enterprise Manager kullanılarak çalıştırılabilir, görüntülenebilir ya da izlenebilir. Bunun yanı sıra DBMS_ADVISOR paketi kullanılarak da aynı işlemler yapılabilir.

SQL Access Advisor’un bize sunduğu index tavsiyeleri arasında, b-tree index, bitmap index and function-based index’ler yer almaktadır. Bu 3 tip index’in kullanım amacı ve yeri farklıdır ancak varsayılan olarak yaratılan ve daha sık kullanılan index tipi b-tree index’tir. Materialized view olarak verdiği tavsiyeler ise MV’nin fast ya da full olarak tazelenmesi üzerinedir. Bu danışman, TUNE_MVIEW prosedürünü kullanarak karar vermektedir ve aynı zamanda bir MV’nin nasıl optimize edileceğini açıklar.

SQL Access Advisor’ı kullanmak için aşağıdaki dört adımı uygularız;

  • Task (görev) oluşturma : Bir danışman görevi data dictionary içerisindeki bilgilerin derlendiği ve sonuçların analiz safhasında ilgili danışman tarafından kullanıldığı görevlerdir. SQL Access Advisor’un bize bir tavsiye sunabilmesi için öncelikle bir görev yaratmamız gerekmektedir. Bunu yerine getirmek için DBMS_ADVISOR_QUICK_TUNE prosedürünü kullanabilirsiniz ya da DBMS_ADVISOR.CREATE_TASK prosedürü de bir görev yaratacaktır. EM üzerinden de yapabilirsiniz tabii ki. Bir görevin özelliklerini değiştirmek isterseniz ise DBMS_ADVISOR.SET_TASK_PARAMETER prosedürünü kullanabilirsiniz.
  • Workload tanımlarız : Bir workload SQL sorgularından oluşmaktadır. Bunun yanı sıra her SQL sorgusu için istatistikler ve özelliklerini içermektedir. İki tip workload bulunmaktadır, bunlar full ve partial’dır. Full olan tipte bütün SQL sorguları için veriler tutulmakta iken partial için sadece SQL sorgularının alt kümeleri saklanmakadır. Bu ikisi arasındaki farklardan birisi de full workload işe yaramayan ve kullanılmayan MV’ler için de bilgi içermektedir.

SQL Access Advisor’ı bir workload olmadan kullanamazsınız. Veritabanı, bir workload’u SQL Tuning Set olarak saklamaktadır. DBMS_SQLTUNE paketi ile workload’a ulaşabilir ve diğer danışman görevleri ile paylaşabilirsiniz. Workload bağımsız bir obje olduğu için DBMS_ADVISOR.ADD_STS_REF prosedürünü kullanarak kimin için tanımlanacağını belirleyebilirsiniz.

  • Öneri oluşturulur : Görevler oluşturulduktan ve bu görevlere bir workload atandıktan sonra DBMS_ADVISOR.EXECUTE_TASK prosedürü kullanılarak ilgili görev çalıştırılabilir. Ortaya çıkan bütün tavsiyeler SQL Access Advisor Repository’sinde bulundurulmaktadır. Oluşan her bir tavsiye bir veya birden çok adımdan oluşabilmektedir.
  • Öneriler uygulanır : Katalog, yani data dictionary görüntüleri kullanılarak oluşturulan tavsiyeleri görüntüleyebilirsiniz. Bununla birlikte DBMS_ADVISOR.GET_TASK_SCRIPT prosedürü ile de bir script yarattırabilirsiniz. Enterprise Manager üzerinden görüntülemek isterseniz “Viewing Recommendation” kısmı karşınıza gelecektir. Bütün tavsiyeleri kabul edeceksiniz diye bir koşul elbette bulunmamaktadır. Ancak şöyle bir durum bulunmaktadır ki örneğin SQL Access Advisor bize bir tablo üzerinde partition yaratmamız gerektiğini iletti. Bu yerine gelmeden tablo üzerinde bir lokal indeks tanımlayamazsınız. Tavsiyelere uyduktan veya bir kısmına uymadıktan sonra yapacağımız en son aşama ise ilgili sorgunun performansı arttı mı yoksa artmadı mı bunu incelemektir.

SQL Plan Management

SQL plan management, optimizer’ın execution planları otomatik olarak yönetmesini sağlayan ve veritabanının yalnızca bilinen veya doğrulanmış planları kullanmasını sağlayan önleyici bir mekanizmadır.

Bu özellik sayesinde veritabanında çalışan bütün sql komutlarının tüm planları sql plan baseline olarak saklanır.Yeni bir plan ortaya çıktığında hemen kullanılmaz işaretlenir, Eğer performansı eskisinden daha iyiyse devreye alınır. Çeşitli sebeblerle planlar performanslarda kötüleşmelere yol açabilir yetersiz analiz,bind variable, optimizer sürümündeki değişikliklerden, optimizer istatistiklerinden, şema değişikliklerinden, sistem ayarlarından ,dengesiz veri dağılımı gibi. Bu özellik sayesinde beklenmeyen plan değişimi olmayacaktır.

Bir SQL ifadesi hard parse olduğunda, cost based optimizer birkaç yürütme planı oluşturur ve en düşük maliyetli olanı seçer. Bir SQL plan baseline varsa, optimizer, yeni oluşturduğu planı, SQL plan baselinedeki planlarla karşılaştırır. Kabul edilen olarak işaretlenen bir eşleşme planı bulunursa, plan kullanılır. SQL plan baseline, yeni oluşturduğu planla eşleşen kabul edilmiş bir plan içermiyorsa, optimizer kabul edilen planları SQL plan baseline’de değerlendirir ve en düşük maliyetli olanı kullanır. Başlangıçta optimizer tarafından üretilen execution plan SQL plan baseline de mevcut olanlardan daha düşük bir maliyete sahipse, kabul edilmeyen bir plan olarak taban çizgisine eklenir, bu nedenle performansta bir azalmaya neden olmaması doğrulanana kadar kullanılmaz.  Bir sistem değişikliği mevcut kabul edilen tüm planları etkiliyorsa, bu nedenle tekrar kullanılamaz kabul edilirse, optimizer planı en düşük maliyetle kullanır.

Oracle, buna ” conservative plan selection strategy ” diyor, çünkü optimizer tercihen yeni bir plan daha iyi performans gösterse bile denenmiş bir deneme planı kullanıyor. Sadece yeni planın iyi performans gösterdiği kanıtlandığında, kullanım kabul edilecektir.

Varsayılan değeri FALSE olan OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parametresinin değeri, sistemin SQL plan baseline otomatik olarak yakalayıp yakalamayacağını belirler. TRUE olarak ayarlandığında, sistem SQL ifadeleri için bir plan geçmişi kaydeder. Belirli bir ifade için ilk plan otomatik olarak kabul edildi olarak işaretlenir. Bu noktadan sonra oluşturulan alternatif planlar performansın bozulmasına neden olmadığı doğrulanıncaya kadar kullanılmaz. Kabul edilebilir performansa sahip planlar, evrim aşamasında SQL plan baseline eklenir.

İstersek DBMS_SPM paketi ile manüel olarak cursor cache’den veya SQL tuning set aracılığı ile SQL Plan baseline oluşturabiliriz.

Aşağıdaki kod, varolan bir SQL ayarındaki tüm ifadeleri SQL taban çizgilerine yüklemek için LOAD_PLANS_FROM_SQLSET fonkisyonunu kullanır. Gerekirse yüklenen SQL ifadelerini sınırlandırmak için bir filtre uygulanabilir.

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(
    sqlset_name => 'my_sqlset');
END;
/

Mesela SQL id ile yapmak istersek;

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '1fkh93md0802n');
END;
/

Şimdi SQL plan baseline kullanarak adım adım bir örnek yaparsak. Manuel yükleme kullanacağız, bu nedenle otomatik plan yakalamayı kapatın.

Mesela aşağıdaki gibi bir SQL çalıştıralım

ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;

Tablo oluşturup içine veri atalım

CREATE TABLE spm_test_tab (
  id           NUMBER,
  description  VARCHAR2(50)
);

DECLARE
  TYPE t_tab IS TABLE OF spm_test_tab%ROWTYPE;
  l_tab t_tab := t_TAB();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    l_tab.extend;
    l_tab(l_tab.last).id := i;
    l_tab(l_tab.last).description := 'Description for ' || i;
  END LOOP;
  
  FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO spm_test_tab VALUES l_tab(i);
  
  COMMIT;
END;
/

EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);

Tabloda bir sorgu çalıştıralım ve full table scan yapdığını görelim

SET AUTOTRACE TRACE

SELECT description
FROM   spm_test_tab
WHERE  id = 99;
------------------------------------------------------------------------------------------
| Id  | Operation                 | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |              |     1 |    25 |    14   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| SPM_TEST_TAB |     1 |    25 |    14   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

V$SQL viewini kullanarak SQL_ID değerini bulalaım;

SELECT sql_id
FROM   v$sql
WHERE  sql_text LIKE '%spm_test_tab%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%'
AND    sql_text NOT LIKE '%EXPLAIN%';

SQL_ID
-------------
gat6z1bc6nc2d

SQL_ID kullanaral SQL plan baseline’a yükleme yapalım.

SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => 'gat6z1bc6nc2d');
    
  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
Plans Loaded: 1

PL/SQL procedure successfully completed.

DBA_SQL_PLAN_BASELINES viewi SQL plan baseline ler hakkında bilgi verir. Hem etkinleştirilen hemde kabul edilen baseleni mizle ilgili tek plan olduğunu görebiliriz.

SELECT sql_handle, plan_name, enabled, accepted 
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%spm_test_tab%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SYS_SQL_7b76323ad90440b9       SYS_SQL_PLAN_d90440b9b65c37c8  YES YES

Hard parse’ı zorlamak için shared alanı boşaltırız. id alanına index atar, sql’i yeniden çalıştırırz.

ALTER SYSTEM FLUSH SHARED_POOL;

CREATE INDEX spm_test_tab_idx ON spm_test_tab(id);
EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);

SELECT description
FROM   spm_test_tab
WHERE  id = 99;

Execution Plan
----------------------------------------------------------
|   0 | SELECT STATEMENT          |              |     1 |    25 |    14   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| SPM_TEST_TAB |     1 |    25 |    14   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Hard parse’a zorladığımız halde, sorgunun yeni oluşturulan indexi kullanmadığına dikkat edin. DBA_SQL_PLAN_BASELINES görünümüne bakarak nedenini görebiliriz.

SELECT sql_handle, plan_name, enabled, accepted 
FROM   dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SYS_SQL_7b76323ad90440b9       SYS_SQL_PLAN_d90440b9b65c37c8  YES YES
SYS_SQL_7b76323ad90440b9       SYS_SQL_PLAN_d90440b9ed3324c0  YES NO

SQL plan baseline şimdi ikinci bir plan içeriyor, ancak henüz kabul edilmedi.

Aşağıdaki sorgu, SQL plan baseline’i geliştirmek ve ilgili raporu çıkarmak için EVOLVE_SQL_PLAN_BASELINE işlevini kullanır.

SET LONG 10000
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_7b76323ad90440b9') from dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_7B76323AD90440B9')
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SQL_7b76323ad90440b9
  PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_7B76323AD90440B9')
--------------------------------------------------------------------------------
  COMMIT     = YES

Plan: SQL_PLAN_7qxjk7bch8h5ted3324c0
------------------------------------
  Plan was verified: Time used .02 seconds.
  Plan passed performance criterion: 15.33 times better than baseline plan.
  Plan was changed to an accepted plan.

                            Baseline Plan      Test Plan       Stats Ratio
                            -------------      ---------       -----------
  Execution Status:              COMPLETE       COMPLETE

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_7B76323AD90440B9')
--------------------------------------------------------------------------------
  Rows Processed:                       1              1
  Elapsed Time(ms):                  .131           .016              8.19
  CPU Time(ms):                      .123           .016              7.69
  Buffer Gets:                         46              3             15.33
  Physical Read Requests:               0              0
  Physical Write Requests:              0              0
  Physical Read Bytes:                  0              0
  Physical Write Bytes:                 0              0
  Executions:                           1              1

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

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_7B76323AD90440B9')
--------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1

DBA_SQL_PLAN_BASELINES vşiewi, kabul edilen ikinci planı gösterir.

SELECT sql_handle, plan_name, enabled, accepted 
FROM   dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SYS_SQL_7b76323ad90440b9       SYS_SQL_PLAN_d90440b9b65c37c8  YES YES
SYS_SQL_7b76323ad90440b9       SYS_SQL_PLAN_d90440b9ed3324c0  YES YES

NOT : Kabul edilmeyen planlar, evolve_sql_plan_baseline fonksiyonunu çalıştırarak doğrulanabilir. Bu işlev kabul edilmeyen planı uygular ve performansını en iyi kabul edilen planla karşılaştırır. Uygulama, kabul edilmeyen planın plan geçmişine eklendiği tarihte geçerli olan koşullar (örneğin, bind values, parametreler, vs.) kullanılarak gerçekleştirilir. Eğer kabul edilmeyen planın performansı daha iyi ise, fonksiyon kabul edilmesini sağlayacak ve böylece bunu SQL plan baseline ekleyecektir.

Önceki testi tekrarladığımızda doğru execution plan ile çalıştıgını gözlemleyebiliriz.

SET AUTOTRACE TRACE

SELECT description
FROM   spm_test_tab
WHERE  id = 99;

Execution Plan
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SPM_TEST_TAB     |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SPM_TEST_TAB_IDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

DBA_SQL_PLAN_BASELINES viewini sorgulamanın yanı sıra, SQL plan baseline ilgili bilgilere DBMS_XPLAN paketi üzerinden erişilebilir. DISPLAY_SQL_PLAN_BASELINE tablo fonksiyonu, belirli bir plan veya SQL plan baseline’deki tüm planların üç formattan birinde (BASIC, TYPICAL veya ALL) biçimlendirilmiş bilgileri görüntüler. Aşağıdaki örnek, belirli bir plan için varsayılan biçim (TYPICAL) raporunu görüntüler.

set lines 300
SET LONG 10000
SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_d90440b9ed3324c0'));

SQL Performance Analyzer

SQL Performance Analyzer aracı, zayıf performans gösteren SQL komutlarının mevcut durumu ve gerekli düzeltme işlemi sonunda performanslarında meydana gelen değişimleri test ortamında kıyaslayarak, kaynak kullanımında ve çalıştırma planı maliyetinde meydana gelen olumlu/olumsuz gelişimleri okunabilir rapor formatında hazırlayarak, veritabanı yöneticilerine SQL cümlelerinin iyileştirilmesinin veritabanı üzerinde olumlu etkisini kolayca görmesini sağlanır. SQL PerformanceAnalzyer aracında kıyaslama için önceki ve sonraki olarak adlandırılan iki tür şablon kullanılmaktadır. “Önceki” kelimesinden kasıt; herhangi bir iyileştirme yapılmadan çalıştırılan zayıf SQL komutlarının mevcut durumudur. “Sonraki” ise; gerekli yapısal iyileştirme yapıldıktan sonra bu konfigürasyon değişikliğinin sistem üzerinde ne tür performans geliştirmesi yapacağının testine imkan veren bir analiz ve simülasyon metodudur. SQL Performance Analyzer kullanacağımız veri tabanı değişikliği, istediğiniz kadar büyük veya küçük olabilir:

  • Veritabanı, işletim sistemi veya donanım yükseltmeleri.
  • Veritabanı, işletim sistemi veya donanım yapılandırma değişiklikleri.
  • Veritabanı başlatma parametresi değişir.
  • Index ekleme veya materialized view gibi şema değişiklikleri.
  • Optimizer istatistiklerinin güncellenmesi
  • SQL profilleri oluşturma veya değiştirme.

Veritabanı Database Replay’ın aksine, SQL Performance Analyzer sistemdeki iş yükünü aynısını yapmaz ve çoğaltmaz. Sadece performans istatistiklerini toplayan her bir ifadeden geçer.

SQL Performance Analyzer, DBMS_SQLPA paketi kullanılarak veya Enterprise Manager kullanılarak manuel olarak çalıştırılabilir.

SQL performans analyzer SQL tuning set lerini gerektirir ve SQL tuning set leri, SQL içermedikçe anlamsızdır, bu nedenle ilk görev bazı SQL sorguları çalıştırılmalıdr.

Aşağıdaki kod SPA_TEST_USER adlı bir test kullanıcısı ve my_objects adında bir tablo oluşturulur.

CREATE USER spa_test_user IDENTIFIED BY spa_test_user
QUOTA UNLIMITED ON users;

GRANT CONNECT, CREATE TABLE TO spa_test_user;

CONN spa_test_user/spa_test_user

CREATE TABLE my_objects AS SELECT * FROM all_objects;

EXEC DBMS_STATS.gather_table_stats(USER, 'MY_OBJECTS', cascade =&gt; TRUE);

Bu şema bizim “önceki” durumumuzu temsil ediyor. Aşağıdaki sorguları yayınlayınız.

SELECT COUNT(*) FROM my_objects WHERE object_id <= 100;
SELECT object_name FROM my_objects WHERE object_id = 100;
SELECT COUNT(*) FROM my_objects WHERE object_id <= 1000;
SELECT object_name FROM my_objects WHERE object_id = 1000;
SELECT COUNT(*) FROM my_objects WHERE object_id BETWEEN 100 AND 1000;

Dikkat edin, tüm ifadeler şu anda eklemlenmemiş OBJECT_ID sütuna referansta bulunur. Daha sonra değiştirilen “sonra” durumunu oluşturmak için bu sütuna index ekleyeceğiz.

Select ifadeleri şimdi shared poolda olduğundan, SQL tuning set oluşturmaya başlayabiliriz.

CONN / AS SYSDBA

EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'spa_test_sqlset');

Daha sonra, SELECT_CURSOR_CACHE tablo fonksiyonundan, SPA_TEST_USER şeması tarafından parse edilen ve “my_objects” kelimesini içeren tüm SQL ifadelerini içeren cursoru almak için kullanılır. Çıkan cursor, LOAD_SQLSET prosedürü kullanılarak SQL tuning setine yüklenir.

DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR
     SELECT VALUE(a)
     FROM   TABLE(
              DBMS_SQLTUNE.select_cursor_cache(
                basic_filter   => 'sql_text LIKE ''%my_objects%'' and parsing_schema_name = ''SPA_TEST_USER''',
                attribute_list => 'ALL')
            ) a;
                                               
 
  DBMS_SQLTUNE.load_sqlset(sqlset_name     => 'spa_test_sqlset',
                           populate_cursor => l_cursor);
END;
/

DBA_SQLSET_STATEMENTS viewi, hangi ifadelerin SQL tuning set ile ilişkilendirildiğini görmemizi sağlar.

SELECT sql_text
FROM   dba_sqlset_statements
WHERE  sqlset_name = 'spa_test_sqlset';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT object_name FROM my_objects WHERE object_id = 100
SELECT COUNT(*) FROM my_objects WHERE object_id <= 100
SELECT COUNT(*) FROM my_objects WHERE object_id BETWEEN 100 AND 1000
SELECT COUNT(*) FROM my_objects WHERE object_id <= 1000
SELECT object_name FROM my_objects WHERE object_id = 1000

Artık bir SQL tuning setimiz var, SQL performans analyzer kullanmaya başlayabiliriz.

DBMS_SQLPA paketi, SQL performans analyzer yönetmek için kullanılan PL / SQL API’dir. İlk adım, CREATE_ANALYSIS_TASK functionı kullanarak, SQL tuning set adına geçen ve sonuçtaki görev adını not alan bir analiz görevi oluşturmaktır.

CONN / AS SYSDBA

VARIABLE v_task VARCHAR2(64);
EXEC :v_task :=  DBMS_SQLPA.create_analysis_task(sqlset_name => 'spa_test_sqlset');

PL/SQL procedure successfully completed.

SQL> PRINT :v_task
 
V_TASK
--------------------------------------------------------------------------------
TASK_97961

Ardından, herhangi bir değişiklik yapılmadan önce performans hakkında bilgi toplamak için, veritabanının geçerli durumuna göre ayarlanan SQL tuning set içeriğini yürütmek için EXECUTE_ANALYSIS_TASK prosedürünü kullanın. Bu analiz çalıştırması before_change olarak adlandırılmıştır.

BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => :v_task,
    execution_type  => 'test execute',
    execution_name  => 'before_change');
END;
/

Şimdi “önce” performans bilgisine sahibiz, “sonra” performansını test edebilmemiz için bir değişiklik yapmamız gerekir. Bu örnek için OBJECT_ID sütunundaki test tablosuna bir index ekleyeceğiz. Yeni bir SQL * Plus oturumunda aşağıdaki ifadeleri kullanarak index oluşturun.

CONN spa_test_user/spa_test_user

CREATE INDEX my_objects_index_01 ON my_objects(object_id);

EXEC DBMS_STATS.gather_table_stats(USER, 'MY_OBJECTS', cascade => TRUE);

Şimdi, asıl oturumumuza dönebilir ve veritabanı değişikliğinden sonra performansı test edebiliriz. EXECUTE_ANALYSIS_TASK prosedürünü bir kez daha kullanın, analiz görevini “after_change” olarak adlandırın.

BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => :v_task,
    execution_type  => 'test execute',
    execution_name  => 'after_change');
END;
/

Analiz öncesi ve sonrası görevler tamamlandıktan sonra, bir karşılaştırma analizi görevi yürütmeliyiz. Aşağıdaki kod, EXECUTION_PARAMS parametresindeki name-value  çiftlerini kullanarak karşılaştırmak için analiz görevlerini açıkça isimlendirir. Bu ihmal edilirse, en son iki analiz çalışması karşılaştırılır.

BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name        => :v_task,
    execution_type   => 'compare performance', 
    execution_params => dbms_advisor.arglist(
                          'execution_name1', 
                          'before_change', 
                          'execution_name2', 
                          'after_change')
    );
END;
/

Bu son analiz çalışması tamamlandığında, REPORT_ANALYSIS_TASK işlevini kullanarak karşılaştırma raporunu kontrol edebiliriz. İşlev, raporu ‘METİN’, ‘XML’ veya ‘HTML’ biçiminde içeren bir CLOB döndürür. Kullanımı aşağıda gösterilmiştir.

SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET TRIMSPOOL ON
SET TRIM ON

SPOOL /tmp/execute_comparison_report.htm

SELECT DBMS_SQLPA.report_analysis_task(:v_task, 'HTML', 'ALL')
FROM   dual;

SPOOL OFF

SQL Tuning Set Taşınması

Yukarıda listelenen örneklerde, testler aynı sistem üzerinde gerçekleştirilmiştir. Gerçekte, production sisteminizde bir ayar kümesi oluşturmak istemeniz ve ardından SQL Performance Analyzer’ı bir test sisteminde çalıştırmanız daha olasıdır. DBMS_SQLTUNE paketi, SQL tuning set staging tablosunda depolayarak taşımanıza olanak tanır.

İlk olarak, CREATE_STGTAB_SQLSET prosedürünü kullanarak staging tablosunu oluşturun.

CONN sys/password@prod AS SYSDBA

BEGIN
  DBMS_SQLTUNE.create_stgtab_sqlset(table_name      => 'SQLSET_TAB',
                                    schema_name     => 'SPA_TEST_USER',
                                    tablespace_name => 'USERS');
END;
/

Ardından, SQL tuning set staging tablosuna dışa aktarmak için PACK_STGTAB_SQLSET prosedürünü kullanın.

BEGIN
  DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name          => 'SPA_TEST_SQLSET',
                                  sqlset_owner         => 'SYS',
                                  staging_table_name   => 'SQLSET_TAB',
                                  staging_schema_owner => 'SPA_TEST_USER');
END;
/

SQL tuning seti staging tablosuna paketlendiğinde, tablo Datapump, Export / Import veya bir veritabanı bağlantısı kullanılarak test sistemine aktarılabilir. Test sistemine geçtikten sonra SQL tuning seti UNPACK_STGTAB_SQLSET prosedürü kullanılarak alınabilir.

BEGIN
  DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name          => '%',
                                    sqlset_owner         => 'SYS',
                                    replace              => TRUE,
                                    staging_table_name   => 'SQLSET_TAB',
                                    staging_schema_owner => 'SPA_TEST_USER');
END;
/

https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/automatic-performance-diagnostics.html#GUID-0A1DA0E7-098C-40CC-9975-B9BF4F29C942
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/introduction-to-sql-tuning.html#GUID-42BD7803-2538-4469-9D60-88B9C66BB7DE
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/sql-tuning-advisor.html#GUID-A6419DB4-DBF5-43A3-89DF-F0DE74E58D4C
http://www.cozumpark.com/blogs/oracle/archive/2011/05/15/oracle-tuning-advisor-ile-sql-komutlarinin-performanslarinin-iyilestirilmesi.aspx
https://blog.yannickjaquier.com/oracle/dbms_sqltune-concrete-example.html
http://www.oganozdogan.com/2011/02/sql-access-advisor.html
http://www.cozumpark.com/blogs/oracle/archive/2011/08/20/oracle-sql-performance-analyzer-ile-sql-komutlarinin-iyilestirilmesi.aspx
https://oracle-base.com/articles/11g/sql-performance-analyzer-11gr1

Mustafa Bektaş Tepe
İyi Çalışmalar

2,346 total views, 3 views today