Press ESC to close

SQL PLAN FIX -1 (BASELINE)

  • Oracle Veritabanları bir SQL çalışmadan önce bu sql için Optimizer çalışır , bu sql in en uygun çalışma şekli çıkarılır ve bu sql_id için bir plan id oluşturulur ve plan set edilir, bu işleme Hard Pars işlemi diyorduk, aynı sql tekrar geldiğinde bu sql_id nin plan id si buydu diyor ve direk uygun plandan çalışması sağlanıyordu bu işlemede soft pars diyorduk, bazı durumlarda bir sql in birden fazla planı olabilir, Veritabanı istatistik bilgileri , veritabanı üzerindeki o anki iş durumu vb. gibi nedenler ile aynı sql farklı planlardan çalışabilir bazende istemediğimiz planlardan çalışabilir. Performasn konusunda daha detaylı bilgi için aşağıdaki yazılara göz atabilirsiniz.
  1. https://www.farukcevik.com.tr/blog/2021/10/16/oracle-performance-awr-baselineaddmash-vs/

  2. https://www.farukcevik.com.tr/blog/2021/10/17/perfomance-tuning-addm-ash-dynamic-performasn-views/

 
Peki bir sql in birden fazla planı var ve biz belli bir plandan çalışmasını istiyorsak bu durumda plan fix işlemi gerekir. Sql Plan fixlemenin genel geçer bilinen iki yolu vardır , bunlara ek olarak birde outline fix denen bir yöntem vardır, outline fix yöntemi aynı sql de sql_id değişirse ve oplanlarda değişirse kullanılacabilecek bir yöntem olarak karşımıza çıkmaktadır.
 
  1. SQL PLAN FIX BASELINE
  2. SQL PLAN FIX PROFILE (COE.SQL)
  3. SQL PLAN FIX OUTLINE
 Öncelikle bir sql in birden fazla sql planı var mı? plan id leri nedir ? nasıl bakılır bunlara bir göz atalım?, bir sql_id nin planları nedir?  awr planları nedir?, cursor planları nedir? bunlara dbms_xplan üzerinden hem planlara hemde birden falza planı varmı bakılabilir, aşağıdaki sorgular ile sql_id leri kontrol edebiliriz.
SQL> select * from table (dbms_xplan.display_cursor(sql_id=>'gnjmz1udfd9cg'));

SQL> select * from table (dbms_xplan.display_awr('gnjmz1udfd9cg'));

v$active_session_history ile dba_hist_active_sess_history view ve tabloları sorgulanarakta bu sql ler için farklı planlar varmı varsa bu farklı planlar nedir sorgulanabilir

SQL> SELECT distinct SQL_PLAN_HASH_VALUE FROM DBA_HIST_ACTIVE_SESS_HISTORY WHERE SQL_ID='gnjmz1udfd9cg';
         1014706028
         2256970134
                  0
                
 SQL> select distinct SQL_PLAN_HASH_VALUE from v$active_session_history where sql_id='gnjmz1udfd9cg';
         1014706028
         2256970134
                  0

Bir sql_id birden fazla planlının olup olmadığını aşağıdaki sorgu ile bakabilirsiniz.  Toplu olarak tüm view ve tablolara aynı anda sorgu atmak için aşağıdaki sql kullanılabilir.

WITH 
p AS (
SELECT plan_hash_value
  FROM gv$sql_plan
 WHERE sql_id = TRIM('&&sql_id')
   AND other_xml IS NOT NULL
 UNION
SELECT plan_hash_value
  FROM dba_hist_sql_plan
 WHERE sql_id = TRIM('&&sql_id.')
   AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
       SUM(elapsed_time)/SUM(executions) avg_et_secs
  FROM gv$sql
 WHERE sql_id = TRIM('&&sql_id')
   AND executions > 0
 GROUP BY
       plan_hash_value ),
a AS (
SELECT plan_hash_value,
       SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
  FROM dba_hist_sqlstat
 WHERE sql_id = TRIM('&&sql_id')
   AND executions_total > 0
 GROUP BY
       plan_hash_value )
SELECT p.plan_hash_value,
       ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
  FROM p, m, a
 WHERE p.plan_hash_value = m.plan_hash_value(+)
   AND p.plan_hash_value = a.plan_hash_value(+)
 ORDER BY
       avg_et_secs NULLS LAST;

iki adet sql planımız var peki 1. Plan 225 ile başlayan sonraki plan ise 101 ile başlayan planlardır. Biz planın değişmesini istemediğimiz halde optimizer tarafından bazen farklı bir plan devreye alınabiliyor, bu durumda sql’i eski plana fix yapmak istiyoruz, ilk olarak BASELINE ile fix yapalım, 

  1. SQL PLAN FIX BASELINE

Plan Yüklenir;

declare
    lv_plan number;
begin
    lv_plan:=dbms_spm.load_plans_from_cursor_cache  
        (sql_id=>'gnjmz1udfd9cg',
        plan_hash_value => '2256970134',
        fixed =>'NO');
end;

Planı yükledikten sonra dba_sql_baselines view ından sql_handle ve plan_name bilgilerini alalım, bu bilgiler ile de istediğimiz sql planın fix işlemini gerçekleştirelim;

SQL> select sql_handle, plan_name,fixed from dba_sql_plan_baselines where origin like '%MANUAL%';

SQL_HANDLE                     PLAN_NAME                                FIX
------------------------------ ---------------------------------------- ---
SQL_4b1050e80764fe67           SQL_PLAN_4q42hx03q9zm72613255d           NO

Şimdi Planı fixleyelim

declare
 lv_plan_fx number;
begin
 lv_plan_fx:=DBMS_SPM.alter_sql_plan_baseline(
            sql_handle=>'SQL_4b1050e80764fe67',
            plan_name => 'SQL_PLAN_4q42hx03q9zm72613255d',
            attribute_name => 'fixed',
            attribute_value => 'YES');
end;

plan fix oldumu kontrol edelim, FIX kısmı YES olmalı

SQL> select sql_handle, plan_name,fixed from dba_sql_plan_baselines where origin like '%MANUAL%';

SQL_HANDLE                     PLAN_NAME                                FIX
------------------------------ ---------------------------------------- ---
SQL_4b1050e80764fe67           SQL_PLAN_4q42hx03q9zm72613255d           YES

SQL Plan FIX yaptık sonrasında Optimizer üzerindeki bu fixi tekrar kaldırmak istersek aşağıdaki gibi DROP edebiliriz

DECLARE lv_plans_dropped PLS_INTEGER; 
BEGIN 
lv_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (sql_handle => 'SQL_4b1050e80764fe67',plan_name => 'SQL_PLAN_4q42hx03q9zm72613255d'); DBMS_OUTPUT.put_line('Plans Dropped: ' || lv_plans_dropped); 
END; 
/

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir