SPM ve SqlPatch ile Sql Plan Sabitleme (Fixing)

Veritabanlarında zaman zaman doğru çalışan Sql'lerin planlarında bozulmalar görebiliriz. Bazen beklediğimiz index'lerin kullanılamadığını, bazen de tamamen full table scan yapıldığını görürüz. Bu durum temelde gereğinden fazla blok okuyarak kaynak yormaya ve sorgu sürelerinde uzamaya sebep olan performans problemleri doğurmaktadır. Bu yazıda Oracle'da SPM ve SqlPatch olmak üzere iki farklı yöntem ile Sql sabitlemeyi inceleyeceğiz. Keyifli okumalar dilerim...

1- SPM ile Sql Sabitleme (Fixing)

İlk yöntem olan SPM (SQL Plan Management) ile başlayacağız. Uygulama boyunca ben görsel olarak daha anlaşılır olması için editör üzerinden DBA olan bir kullanıcı ile çalışacağım. Aynı uygulama SYS ile SqlPlus üzerinden de uygulanabilir.

Yukarıdaki sorgu örneğinde employees tablosundaki tüm kayıtlar getirilmek isteniyor. Normal şartlar altında bu sorgunun full table scan yapması Optimizer tarafından doğal bir davranıştır. Fakat incelediğimiz bu sorgunun index kullanmak yerine full table scan yapan hatalı bir sorgu planı olduğunu farz ederek devam edelim. Sorgunun employee_id üzerindeki index'i kullanarak çalışmasını sabitleyelim.

select * from hr.employees;

Hatalı plana ait sql_id ve plan_hash_value değerleri bulunur.

select sql_id, child_number, hash_value, plan_hash_value, sql_plan_baseline, sql_patch, sql_text from (g)v$sql where sql_text='select * from hr.employees';

Şikayete sebep olan sorgu planı incelendiğinde Table Access Full olduğunu görüyoruz. Bu sorgu ile sql_id ve child_number değerlerini bildiğimiz sql'lerin planlarını inceleyebiliriz.

select plan_table_output from table ( dbms_xplan.display_cursor('7jk33n4f4mpy9', 1, 'ALL +OUTLINE') );

Problem saptandıktan sonra doğru bir çalıştırma planı elde edebilmek için index hinti yardımı ile tekrar sorgu çalıştırılır ve parse edilmesi sağlanır.

select /*+ INDEX(employees, EMP_EMP_ID_PK) */ * from hr.employees;

Hint eklenen sorgunun sql_id ve plan_hash_value değerlerini v$sql view'inden yakalıyoruz. Unutmayın ki hint eklenen sorgunun sql_id'si değişecektir!

select sql_id, child_number, hash_value, plan_hash_value, sql_plan_baseline, sql_patch from (g)v$sql
where sql_text='select /*+ INDEX(employees, EMP_EMP_ID_PK) */ * from hr.employees';

Hint yardımı ile elde ettiğimiz sorgu planının index'i kullanıp kullanmadığını kontrol ediyoruz. Bu aşamada doğru plan elde edilemezse sabitleme işlemine geçilemeyeceğinden devam edemeyiz. Bu sebeple eğer index kullanımına engel olacak tip uyumsuzluğu, index uyumsuzluğu gibi farklı problemler var ise önce onların çözümesi gerekir.

select plan_table_output from table ( dbms_xplan.display_cursor('a2n9kdch2ywg9', 0, 'ALL +OUTLINE') );

İlk olarak hatalı sql plan, plan_hash_value ile SPM'e yüklenir.

DECLARE
    result NUMBER;
BEGIN
    result := DBMS_SPM.load_plans_from_cursor_cache (sql_id => '7jk33n4f4mpy9', plan_hash_value => 1445457117);
END;

Yüklenen kötü planın dba_sql_plan_baselines view'ine geldiği görülür.

select created, sql_handle, plan_name, enabled, accepted, fixed, autopurge from dba_sql_plan_baselines where created > sysdate-1 order by created desc;

Yüklenen kötü planın sql_handle değeri, iyi planın sql_id ve plan_hash_value değerleri ile linklenir.

DECLARE
    result NUMBER;
BEGIN
    result := DBMS_SPM.load_plans_from_cursor_cache (
    sql_id          => 'a2n9kdch2ywg9', 
    plan_hash_value => 1361983096,
    sql_handle      => 'SQL_9966171d4d89b8e4'
    );
END;

Yüklenen iyi planın dba_sql_plan_baselines view'ine geldiği görülür.

select created, sql_handle, plan_name, enabled, accepted, fixed, autopurge from dba_sql_plan_baselines where created > sysdate-1 order by created desc;

Doğru plan sabitlenir (fixing).

DECLARE
    result NUMBER;
BEGIN
    result := DBMS_SPM.alter_sql_plan_baseline (
    sql_handle      => 'SQL_9966171d4d89b8e4',
    plan_name       => 'SQL_PLAN_9kthr3p6smf748b1782f2',
    attribute_name  => 'fixed', 
    attribute_value => 'yes'
    );
END;

Doğru planın otomatik olarak silinmemesi için autopurge=no set edilir.

DECLARE
    result NUMBER;
BEGIN
    result := DBMS_SPM.alter_sql_plan_baseline (
    sql_handle      => 'SQL_9966171d4d89b8e4',
    plan_name       => 'SQL_PLAN_9kthr3p6smf748b1782f2',
    attribute_name  => 'autopurge', 
    attribute_value => 'no'
    );
END;

SPM'e ilk yüklediğimiz kötü plan daha sonra kullanılmaması için silinir.

DECLARE
    result NUMBER;
BEGIN
    result := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle      => 'SQL_9966171d4d89b8e4',
    plan_name       => 'SQL_PLAN_9kthr3p6smf74cf314e9e'
    );
END;

Geriye kalan doğru plan istediğimiz hale gelmiş oldu.

select created, sql_handle, plan_name, enabled, accepted, fixed, autopurge from dba_sql_plan_baselines where created > sysdate-1 order by created desc;

Sorgu tekrar çalıştırılmadan önce ilgili SQL, address ve hash_value değerleri ile Shared Pool'dan silinerek ilk defa parse edilecekmiş gibi hazırlanır. RAC (Real Application Cluster) sistemlerde her instance kendi planını ürettiği için her bir instance'ın kendi hafızasından bu çalıştırma planları silinmelidir!

Not: Shared Pool purge komutu verildikten sonra aynı sql_id ile çalışan farklı aktif session'lar var ise bu session'lar öldürülmeden plan Shared Pool'dan silinmeyecektir!

select inst_id, sql_id, child_number, address, hash_value, sql_plan_baseline,sql_patch from (g)v$sql where sql_id='7jk33n4f4mpy9';

exec sys.dbms_shared_pool.purge('000000006F655248,474601417','C');

Sorgu test edilmek için tekrar çalıştırılıp, parse edilmesi sağlanır.

select * from hr.employees;

Sorgu yeniden çalıştırıldıktan sonra v$sql view'ine bakıldığında sql_plan_baseline'ın kullanıldığı görülür.

select sql_id, child_number, hash_value, plan_hash_value, sql_plan_baseline, sql_patch from (g)v$sql where sql_text='select * from hr.employees';

Sorgu planı dbms_xplan.display_cursor ile tekrar incelendiğinde index kullanımı görülür. Böylece SPM ile Sql Sabitleme işlemini başarıyla uyguladık.

select plan_table_output from table ( dbms_xplan.display_cursor('7jk33n4f4mpy9', 2, 'ALL +OUTLINE') );

sql_plan_baseline silinmek istenirse sql_handle ve plan_name ile bu işlem yapılır. Şimdi sabitlediğimiz bu planı silerek, SqlPatch ile sabitleme adımına hazırlık yapıyoruz.

DECLARE
    result NUMBER;
BEGIN
    result := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle      => 'SQL_9966171d4d89b8e4',
    plan_name       => 'SQL_PLAN_9kthr3p6smf748b1782f2'
    );
END;

Yeni uygulamaya geçmeden önce aynı zamanda Shared Pool'dan da bu sorgu planını siliyoruz.

select inst_id, sql_id, child_number, address, hash_value, sql_plan_baseline,sql_patch from gv$sql where sql_id='7jk33n4f4mpy9';

exec sys.dbms_shared_pool.purge('000000006F655248,474601417','C');

2- SqlPatch ile Sql Sabitleme (Fixing)

İkinci uygulamada SqlPatch yazarak bir sorguya kullanmasını istediğimiz index hint'ini vereceğiz. Yine aynı şekilde bu sorgunun full giden hatalı bir plana sahip olduğunu farz edelim.

Aynı sql üzerinden devam edelim bu kez SqlPatch yöntemini kullanacağız. Sorgu bir defa çalıştırılarak hatalı plan oluşması sağlanır.

select * from hr.employees;

Hatalı plana ait sql_id ve plan_hash_value değerleri bulunur.

select sql_id, child_number, hash_value, plan_hash_value, sql_plan_baseline, sql_patch from (g)v$sql where sql_text='select * from hr.employees';

dbms_xplan.display_cursor yardımı ile plan ve outline verisi incelenir. Outline verisinde employees tablosuna full gidildiğini görebiliriz.

select plan_table_output from table ( dbms_xplan.display_cursor('7jk33n4f4mpy9', 1, 'ALL +OUTLINE') );

Daha sonra iyi planı oluşturacak hint'li sorgu çalıştırılır.

select /*+ INDEX(employees, EMP_EMP_ID_PK) */ * from hr.employees;

İyi plana ait sql_id ve plan_hash_value değerleri bulunur.

select sql_id, child_number, hash_value, plan_hash_value, sql_plan_baseline, sql_patch from (g)v$sql
where sql_text='select /*+ INDEX(employees, EMP_EMP_ID_PK) */ * from hr.employees';

Bu kez plan incelendiğinde index üzerinden sorgulama yapıldığı görülür. Outline verisindeki index kullanımını işaret eden iki satır SqlPatch'de kullanılmak üzere kopyalanır.

select plan_table_output from table ( dbms_xplan.display_cursor('a2n9kdch2ywg9', 0, 'ALL +OUTLINE') );

SqlPatch hazırlama aşamasına geçebiliriz. Hatalı plan ile çalışan sql_id ve bir önceki adımda kopyaladığımız outline verisini kullanarak SqlPatch yazılır.

DECLARE
    patch_name varchar2(200);
BEGIN
    patch_name := sys.dbms_sqldiag.create_sql_patch( 
        sql_id => '7jk33n4f4mpy9',
        hint_text => ' INDEX(@"SEL$1" "EMPLOYEES"@"SEL$1"                         
                       ("EMPLOYEES"."EMPLOYEE_ID")) '
        );
END;

Sorgu çalıştırılmadan önce hatalı plana ait çalıştırma planları Shared Pool'dan silinir. Aynı sql_id ile çalışan farklı aktif session'lar var ise öldürülür.

select inst_id, sql_id, child_number, address, hash_value, sql_plan_baseline,sql_patch from (g)v$sql where sql_id='7jk33n4f4mpy9';

exec sys.dbms_shared_pool.purge('000000006F655248,474601417','C');

Sorgu test edilmek için tekrar çalıştırılır.

select * from hr.employees;

Sorgu yeniden çalıştırıldıktan sonra v$sql view'ine bakıldığında sql_patch'in kullanıldığı görülür.

select sql_id, child_number, hash_value, plan_hash_value, sql_plan_baseline, sql_patch from (g)v$sql where sql_id='7jk33n4f4mpy9';

Sorgu planı dbms_xplan.display_cursor ile tekrar incelendiğinde index kullanımı görülür. Böylece SqlPatch ile Sql Sabitleme işlemini de başarıyla uygulamış olduk.

select plan_table_output from table ( dbms_xplan.display_cursor('7jk33n4f4mpy9', 1, 'ALL +OUTLINE') );

Yazılan bir SqlPatch silinmek istenirse, patch adı ile aşağıdaki gibi silinir.

select sql_id, child_number, hash_value, plan_hash_value, sql_plan_baseline, sql_patch from (g)v$sql where sql_id='7jk33n4f4mpy9';

exec sys.dbms_sqldiag.drop_sql_patch('SYS_SQLPTCH_0184af01c0c80001');

Böylece hint'ler yardım ile Oracle'da hatalı oluşturulmuş veya bozulmuş olan planların SPM ve SqlPatch yöntemleri kullanılarak nasıl düzeltilip, sabitleneceğini (fixing) öğrenmiş olduk.

---

Gelecek yazılarımızda görüşmek üzere, sağlıcakla kalın.