Bir önceki yazımda ODI Uygulama Performans İyileştirme calısmasına ek olarak ,du yazımdada ODI üzerinden yapılan geliştirmelerde yaşanılan performans sorunlarının tespit edilmesi ve diğer yapılabilecek iyileştirmelerden sizlere bahsetmek istedim.
1> ODI Interface'lerinde çalışma süresiyle ilgili performans iyileştirmeleri için öncelikle yavaş çalışan interface ler 3. maddedeki query lerle ODI respository'den bulunmalıdır.Bu işlemden sonra, ODI paketlerine bölünerek paralellik işlemleri ile veya Yeni geliştirilecek ODI Knowledge Module'leriyle performans iyileştirmesi yapılabilir.
Örneğin IKM Incremental Update KM dışında IKM Delete Insert KM ile Update yapılan işlemleri azaltarak performans iyileştirmesi
2> ODI KM lerde Database verisyonuna bağlı yapılacak iyileştirmeler.
ODI KM lerde yapılacak ilk iyileştirme ara tablolara NOLOGGING ve PARALLEL, /*+APPEND*/ ile yapılacak iyileştirmelerdir. Bunlar yapıldıktan sonra Exadata veya Database versiyonuna bağlı olarak yapılacak iyileştirmeleri uygulayabiliriz.
Örneğin Exadata'da Compress for Query High, Compress for Query Low değişiklikleri yapıldıktan sonra, Tablolara Optimum 8 paralellik verilmeli, Tabloların büyüklüğüne size'ına baplı olarak bu paralellikler artırılabilir.
3>Performansı düşük ODI interface'lerini tespit etmek için,
create table long_running_interfaces as
select pop_name,avg(step_dur) step_dur
from
(select distinct pop.pop_name,log.step_dur
from
odiw.snp_pop pop,
odiw.snp_sess_step sess,
odiw.snp_step_log log
where
pop.pop_name = sess.step_name
and sess.sess_no = log.sess_no
and sess.nno = log.nno
and STEP_BEG between to_date(20150325,'yyyymmdd') and to_date(20150330,'yyyymmdd'))
group by pop_name
create table long_running_interfaces2 as
select pop_name,avg(step_dur) step_dur
from
(select distinct pop.pop_name,log.step_dur
from
odiw.snp_po pop,
odiw.snp_sess_step sess,
odiw.snp_step_log log
where
pop.pop_name = sess.step_name
and sess.sess_no = log.sess_no
and sess.nno = log.nno
and STEP_BEG between to_date(20150325,'yyyymmdd') and to_date(20150330,'yyyymmdd'))
group by pop_name
select * from long_running_interfaces order by step_dur desc
select * from long_running_interfaces2 order by step_dur desc
select SS.SESS_NAME, ST.TASK_NAME3, S.NNO, S.SCEN_TASK_NO, sum(S.TASK_DUR) from
ODIW.SNP_SESS_TASK_LOG S,
ODIW.SNP_SESS_TASK ST,
ODIW.SNP_SESSION SS
WHERE
S.SESS_NO = ST.SESS_NO AND
S.NNO = ST.NNO AND
S.SCEN_TASK_NO = ST.SCEN_TASK_NO AND
S.SESS_NO = SS.SESS_NO AND
S.TASK_BEG >= TRUNC(SYSDATE) - 1
GROUP BY SS.SESS_NAME, ST.TASK_NAME3,ST.TASK_NAME3,S.NNO,S.SCEN_TASK_NO
ORDER BY 5 DESC NULLS LAST
---- bakilacak
select ST.TASK_NAME1,ST.TASK_NAME3,sum(S.TASK_DUR) from
ODIW.SNP_SESS_TASK_LOG S,
ODIW.SNP_SESS_TASK ST
WHERE
S.SESS_NO = ST.SESS_NO AND
S.NNO = ST.NNO AND
S.SCEN_TASK_NO = ST.SCEN_TASK_NO AND
S.TASK_BEG >= TRUNC(SYSDATE) - 1
GROUP BY ST.TASK_NAME1,ST.TASK_NAME3
ORDER BY 3 DESC NULLS LAST
-- bakilacak
select SESS_NAME, SUM(SESS_DUR) from ODIW.SNP_SESSION
WHERE
SESS_BEG >= TRUNC(SYSDATE) - 1
GROUP BY SESS_NAME
ORDER BY 2 DESC NULLS LAST
---- bakilacak
select ST.TASK_NAME2,sum(S.TASK_DUR) from
ODIW.SNP_SESS_TASK_LOG S,
ODIW.SNP_SESS_TASK ST
WHERE
S.SESS_NO = ST.SESS_NO AND
S.NNO = ST.NNO AND
S.SCEN_TASK_NO = ST.SCEN_TASK_NO AND
S.TASK_BEG >= TRUNC(SYSDATE) - 1
GROUP BY ST.TASK_NAME2
ORDER BY 2 DESC NULLS LAST
select S.TASK_DUR,ST.* from
ODIP.SNP_SESS_TASK_LOG S,
ODIP.SNP_SESS_TASK ST
WHERE
S.SESS_NO = ST.SESS_NO AND
S.NNO = ST.NNO AND
S.SCEN_TASK_NO = ST.SCEN_TASK_NO AND
S.TASK_BEG >= TRUNC(SYSDATE) - 1 AND
ST.TASK_NAME2 = 'CC_EOD_FSUM_EOC_REDISCOUNT_SUMMARY_ALL'
ORDER BY 2 DESC NULLS LAST
---- interface getting all warning list
-----------------------------------------------------------
-----------------------------------------------------------
SELECT DISTINCT PROJECT_NAME, POP_NAME, POP_TYPE
FROM (SELECT DISTINCT p.PROJECT_NAME, f.FOLDER_NAME, a.POP_NAME,
CASE
WHEN a.WSTAGE='E' THEN 'NORMAL_POP_1'
ELSE 'TEMP_POP_1' END AS pop_TYPE,
e.COL_NAME source_col_name,
e.SOURCE_DT source_data_type,
CASE
WHEN e.SOURCE_DT = 'DATE'THEN 7
ELSE e.LONGC
END source_prec,
CASE
WHEN e.SOURCE_DT != 'NUMBER' THEN 0
ELSE e.SCALEC
END source_scale,
b.COL_NAME target_col_name,
b.SOURCE_DT target_data_type,
CASE
WHEN b.SOURCE_DT = 'DATE' THEN 7
WHEN ( b.SOURCE_DT = 'NUMBER' AND b.LONGC = 0) THEN 38
ELSE b.LONGC
END target_prec,
b.SCALEC target_scale
FROM SNP_PROJECT p,
SNP_FOLDER f,
SNP_POP a,
SNP_POP_COL b,
SNP_POP_MAPPING c,
SNP_TXT_CROSSR d,
SNP_TXT_HEADER m,
SNP_COL e
WHERE (1 = 1)
AND p.i_project = f.i_project
AND f.i_folder = a.i_folder
AND a.i_pop = b.i_pop
AND b.i_pop_col = c.i_pop_col
AND c.i_txt_map = d.i_txt
AND d.i_txt = m.i_txt
AND d.i_col = e.i_col
AND INSTR (SUBSTR (UPPER (m.full_text), 0, 100),'CASE') = 0
AND INSTR (SUBSTR (m.full_text, 0, 100), '(') = 0
AND INSTR (SUBSTR (m.full_text, 0, 100), '||') =0
AND INSTR (SUBSTR (m.full_text, 0, 100), '<%') =0
AND b.SOURCE_DT IS NOT NULL
UNION ALL
SELECT DISTINCT p.PROJECT_NAME, f.FOLDER_NAME, a.POP_NAME,
CASE
WHEN a.WSTAGE='E' THEN 'NORMAL_POP_2'
ELSE 'TEMP_POP_2' END AS pop_TYPE,
e.COL_NAME source_col_name,
e.SOURCE_DT source_data_type,
CASE
WHEN e.SOURCE_DT = 'DATE' THEN 7
ELSE e.LONGC
END source_prec,
CASE
WHEN e.SOURCE_DT != 'NUMBER' THEN 0
ELSE e.SCALEC
END source_scale,
t.COL_NAME target_col_name,
t.SOURCE_DT target_data_type,
CASE
WHEN t.SOURCE_DT = 'DATE' THEN 7
WHEN ( t.SOURCE_DT = 'NUMBER' AND t.LONGC = 0) THEN 38
ELSE t.LONGC
END target_prec,
t.SCALEC target_scale
FROM SNP_PROJECT p,
SNP_FOLDER f,
SNP_POP a,
SNP_POP_COL b,
SNP_COL t,
SNP_POP_MAPPING c,
SNP_TXT_CROSSR d,
SNP_TXT_HEADER m,
SNP_COL e
WHERE (1 = 1)
AND p.i_project = f.i_project
AND f.i_folder = a.i_folder
AND a.i_pop = b.i_pop
AND b.i_col = t.i_col
AND b.i_pop_col = c.i_pop_col
AND c.i_txt_map = d.i_txt
AND d.i_txt = m.i_txt
AND d.i_col = e.i_col
AND INSTR (SUBSTR (UPPER (m.full_text), 0, 100),'CASE') = 0
AND INSTR (SUBSTR (m.full_text, 0, 100), '(') = 0
AND INSTR (SUBSTR (m.full_text, 0, 100), '||') =0
AND INSTR (SUBSTR (m.full_text, 0, 100), '<%') =0
AND b.SOURCE_DT IS NULL
UNION ALL
SELECT DISTINCT p.PROJECT_NAME, f.FOLDER_NAME, a.POP_NAME,
CASE
WHEN a.WSTAGE='E' THEN 'NORMAL_POP_3'
ELSE 'TEMP_POP_3' END AS pop_TYPE,
x.COL_NAME source_col_name,
x.SOURCE_DT source_data_type,
CASE
WHEN x.SOURCE_DT = 'DATE' THEN 7
ELSE x.LONGC
END source_prec,
CASE
WHEN x.SOURCE_DT != 'NUMBER' THEN 0
ELSE x.SCALEC
END source_scale,
t.COL_NAME target_col_name,
t.SOURCE_DT target_data_type,
CASE
WHEN t.SOURCE_DT = 'DATE' THEN 7
WHEN ( t.SOURCE_DT = 'NUMBER' AND t.LONGC = 0) THEN 38
ELSE t.LONGC
END target_prec,
t.SCALEC target_scale
FROM SNP_PROJECT p,
SNP_FOLDER f,
SNP_POP a,
SNP_POP_COL b,
SNP_COL t,
SNP_POP_MAPPING c,
SNP_TXT_CROSSR d,
SNP_TXT_HEADER m,
SNP_POP_COL x
WHERE (1 = 1)
AND p.i_project = f.i_project
AND f.i_folder = a.i_folder
AND a.i_pop = b.i_pop
AND b.i_col = t.i_col
AND b.i_pop_col = c.i_pop_col
AND c.i_txt_map = d.i_txt
AND d.i_txt = m.i_txt
AND d.i_pop_col = x.i_pop_col
AND INSTR (SUBSTR (UPPER (m.full_text), 0, 100),'CASE') = 0
AND INSTR (SUBSTR (m.full_text, 0, 100), '(') = 0
AND INSTR (SUBSTR (m.full_text, 0, 100), '||') =0
AND INSTR (SUBSTR (m.full_text, 0, 100), '<%') =0
AND b.SOURCE_DT IS NULL
AND d.i_col IS NULL)
WHERE (1 = 1)
AND (SOURCE_PREC > TARGET_PREC OR SOURCE_SCALE > TARGET_SCALE)
-- AND POP_NAME='COG_LOCATION_INFO_07.1'
ORDER BY PROJECT_NAME, POP_NAME, POP_TYPE
--- error handling
SELECT
CAST(A.SESS_NO ||' -> '|| SESS_NAME||' -> '|| STEP_NAME || ' -> ' ||
CASE
WHEN STEP_TYPE='F' THEN 'INTERFACE'
WHEN STEP_TYPE='VD' THEN 'VARAIBLE DECLARATION'
WHEN STEP_TYPE='VS' THEN 'SET/INCREMENT VARAIBLE'
WHEN STEP_TYPE='VE' THEN 'EVALUATE VARIABLE'
WHEN STEP_TYPE='V' THEN 'REFRESH VARIABLE'
WHEN STEP_TYPE='T' THEN 'PROCEDURE'
WHEN STEP_TYPE='OE' THEN 'OS COMMAND'
WHEN STEP_TYPE='SE' THEN 'ODI TOOL'
WHEN STEP_TYPE='RM' THEN 'REVERSE ENGINEERING MODEL'
WHEN STEP_TYPE='CM' THEN 'CHECK MODEL'
WHEN STEP_TYPE='CS' THEN 'CHECK SUB MODEL'
WHEN STEP_TYPE='CD' THEN 'CHECK DATA STORE'
WHEN STEP_TYPE='JM' THEN 'JOURNALIZE MODEL'
WHEN STEP_TYPE='JD' THEN 'JOURNALIZE DATA STORE'
END || ' -> ' ||
SUBSTR(A.ERROR_MESSAGE,1, 3500) AS VARCHAR2(4000))
FROM
<%=odiRef.getObjectName("SNP_SESS_TASK_LOG")%> a ,
<%=odiRef.getObjectName("SNP_SESSION")%> t,
<%=odiRef.getObjectName("SNP_SESS_STEP")%> p
WHERE
TRUNC(A.TASK_BEG) = TRUNC(SYSDATE)
AND A.TASK_STATUS = 'E'
AND A.SESS_NO =T.SESS_NO
AND T.CONTEXT_CODE='<%=odiRef.getContext( "CTX_CODE" )%>'
AND A.SESS_NO = P.SESS_NO
AND A.NNO = P.NNO
AND A.SESS_NO ='<%=odiRef.getSession("SESS_NO")%>'
-- SQL QUERY FOR SEARCHING KEYWORD IN FILTER-JOIN-MAPPING
SELECT DISTINCT POP.POP_NAME, POP.I_POP, 'FILTER-JOIN' AS TYPE,TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) ASTXT FROM ODIWCRS.SNP_POP POP,ODIWCRS.SNP_POP_COL COL,ODIWCRS.SNP_POP_MAPPING MAP,ODIWCRS.snp_pop_clause CLS
,ODIWCRS.snp_txt_header HDR
WHERE POP.I_POP=col.i_pop(+)
AND COL.I_POP_COL=MAP.I_POP_COL(+)
AND MAP.I_DATA_SET=CLS.I_DATA_SET(+)
AND CLS.I_TXT_SQL=HDR.I_TXT(+)
AND hdr.i_txt_orig=124
AND (TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) LIKE '%2008%' OR
TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) LIKE '%2009%' OR
TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) LIKE '%2010%' OR
TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) LIKE '%2011%' OR
TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) LIKE '%2012%' OR
TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) LIKE '%2013%' OR
TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) LIKE '%2014%' OR
TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) LIKE '%2015%'
)
UNION ALL
SELECT DISTINCT POP.POP_NAME, POP.I_POP,'MAPPING' AS TYPE,TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) AS TXT FROM ODIWCRS.SNP_POP POP,ODIWCRS.SNP_POP_COL COL,ODIWCRS.SNP_POP_MAPPING MAP
,ODIWCRS.snp_txt_header HDR
WHERE POP.I_POP=col.i_pop(+)
AND COL.I_POP_COL=MAP.I_POP_COL(+)
AND MAP.I_TXT_MAP=HDR.I_TXT(+)
AND hdr.i_txt_orig=130
AND (TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) LIKE '%2008%' OR
TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) LIKE '%2009%' OR
TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) LIKE '%2010%' OR
TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) LIKE '%2011%' OR
TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) LIKE '%2012%' OR
TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) LIKE '%2013%' OR
TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) LIKE '%2014%' OR
TO_CHAR(SUBSTR(HDR.FULL_TEXT,1,1000)) LIKE '%2015%'
);
