Long running query'ler için Query Plan fixleme

 

Long running query'ler için Query Plan fixleme

Bir çoğumuzun baş belası ekran donuyor, web service timeout alıyor vs gibi sorunların başlıca nedeni atılan sorgunun uzun sürmesi. (execution time, fetch time etc.)

Bunun için index atmak en basic yöntem. Fakat bazen index olmasına ragmen hala sıkıntı yaşıyorsanız, büyük  ihtimalle sql planında bir bozukluk vardır. 

SQL PLAN FIXLEME

Yetki ve Parametre kontrolü

1. ilk olarak DB user’ımıza aşağıdaki gibi yetki tanımlanması gerekiyor.

 

grant execute on sys.dbms_spm to SIEBEL;

grant administer sql management object to SIEBEL;

grant SELECT_CATALOG_ROLE to SIEBEL;

 

2. sonraki adım baselines parametrelerini kontrol etmek.

show parameter baselines;

 bu sorgu sonucu değerlerin aşağıdaki gibi olması gerekiyor.

NAME                                                                  TYPE        VALUE

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

optimizer_capture_sql_plan_baselines               boolean     FALSE                                                                                                

optimizer_use_sql_plan_baselines                     boolean     TRUE   

eğer değilde aşağıdaki şekilde setleyebiliriz.

ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;

ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES =TRUE;

SQL ID Bulma

1. aşağıdaki sorgu ile uzun süren sorgumuzuun db üzerindeki sql id’sini bulabiliriz. (Logdaki ile aynı olmuyor kesinlikle)

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text

from v$sqltext_with_newlines t,V$SESSION s

where t.address =s.sql_address

and t.hash_value = s.sql_hash_value

and s.status = 'ACTIVE'

and s.username <> 'SYSTEM'

order by s.sid,t.piece

/

Emin olmak adına aşağıdaki sorgu ile de sorgunun uzun sürdüğünden emin olabiliriz. (MINS_RUNNING kolonu)

select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text, s.sql_id from v$session s

join v$sqltext_with_newlines q

on s.sql_address = q.address

where status='ACTIVE'

and type <>'BACKGROUND'

and last_call_et> 60

order by sid,serial#,q.piece;

  

Bu sql’lerden dönen sonuçtaki sql_id’yi alıp aşağıdaki gibi sogru çektiğimizde de full sql’I görüp logumuzdaki sql olduğuna emin oluyoruz.

 

select * from gv$sql where sql_id = 'byn0ka477qxhs';

SQL Planı Düzenleme ve Fixleme

1. ilk olarak aşağıdaki sorgu ile bizim sql’imizin gitme ihtimali olduğu planları görüntülüyoruz.

 

select inst_id,

       address,

       hash_value,

       sql_id,

       plan_hash_value,

       operation,

       optimizer,

       cost

  from gv$sql_plan

where id = 0

   and sql_id = 'byn0ka477qxhs';

        2. aşağıdaki sql bloğu ile yukarıdaki ekran görüntüsünde cost’u düşük olan query planımızın yüklenmesini sağlıyoruz. 

declare

  my_plans pls_integer;

begin

  my_plans := dbms_spm.load_plans_from_cursor_cache(sql_id          => 'byn0ka477qxhs', --SQL_ID

                                                        plan_hash_value => '2455938553', --Plan_Hash_Value

                                                       fixed           => 'NO');

end;

 

 aşağıdaki sql ile planın yüklendiğini kontrol edip aşağıdaki bir sonraki step için gerekli inputları alıyoruz.

 

select * from dba_sql_plan_baselines;

         3. son olarak artık tanımladığımız planı aşağdaki sql bloğu ile sorgunun planı olarak fixliyoruz. 

DECLARE

  l_plans_altered PLS_INTEGER;

BEGIN

  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(sql_handle      => 'SQL_73ce4820f270a9f0',

                                                      plan_name       => 'SQL_PLAN_77mk843t71aghf53e233f',

                                                      attribute_name  => 'fixed',

                                                      attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plan sabitlendi : ' || l_plans_altered);

END;

/

 

 

PS: aşağıdaki sql bloğu ile de tanımladığınız planı drop edebilirsiniz.

 

DECLARE l_plans_dropped PLS_INTEGER;

BEGIN

l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (sql_handle => 'SQL_73ce4820f270a9f0',plan_name => 'SQL_PLAN_77mk843t71aghaf83e035'); DBMS_OUTPUT.put_line('Plans Dropped: ' || l_plans_dropped);

END;

 

Geçmiş olsun artık ekranınızın açılması gerekiyor 😊