Tunning issue !!

Moderator: NorbertKrupa

Post Reply
adrian.oprea
Intermediate
Intermediate
Posts: 163
Joined: Tue Jun 19, 2012 2:44 pm
Location: Rio de Janeiro
Contact:

Tunning issue !!

Post by adrian.oprea » Fri May 10, 2013 7:53 pm

Hy guys ,

I am trying to do some tunning on a query we have , and i can get it to run faster.
I have all the statistics up to date, runned the DBD on the query and created all the projections like wise, no spill needed , but still the results are not that good.

Any ideas on how to aproach this !! or is there any material on how to do performance tunning on Vertica beside the doc on their site ?
Done all the distinct and ordered them all !! , also partitions were created !! on specific columns !!

Thx !! any idea is well received :) !

Code: Select all

------------------------------ 
QUERY PLAN DESCRIPTION: 
------------------------------

Access Path:
| +---> ANALYTICAL [Cost: 13M, Rows: 416K] (PATH ID: 1)
| |      Analytic Group
| |       Functions: sum(), sum(), sum(), sum(), sum(), sum(), sum(), sum(), sum(), sum(), sum(), sum(), sum(), sum(), sum(), sum(), sum(), sum(), sum(), sum(), sum()
| |       Group Sort: to_char(PROB.DAT_AUT_PROBI, 'yyyy') ASC, to_char(PROB.DAT_DPC_PROBI, 'yyyy') ASC, PROB.NUM_PROBI ASC, PROB.TMT_PROBI ASC, TPFBI_TIPO_FINAN_HIST__ALIAS_.NOM_TPFBI ASC, PTHBI_PROCESSO_TIPO_FINAN_HIST.IDC_ALT_TPF_PTHBI ASC, CASE WHEN (PTHBI_PROCESSO_TIPO_FINAN_HIST.IDC_ALT_TPF_PTHBI IS NULL) THEN MAPBI_PROCESSO_MAPEAMENTO_TF.ITM_MAPBI ELSE TPFBI_TIPO_FINAN_HIST__ALIAS_.ITM_TPFBI END ASC, CASE WHEN (PTHBI_PROCESSO_TIPO_FINAN_HIST.IDC_ALT_TPF_PTHBI IS NULL) THEN MAPBI_PROCESSO_MAPEAMENTO_TF.CAT_ITM_MAPBI ELSE TPFBI_TIPO_FINAN_HIST__ALIAS_.CAT_ITM_TPFBI END ASC, CASE WHEN (PTHBI_PROCESSO_TIPO_FINAN_HIST.IDC_ALT_TPF_PTHBI IS NULL) THEN MAPBI_PROCESSO_MAPEAMENTO_TF.PRG_MAPBI ELSE TPFBI_TIPO_FINAN_HIST__ALIAS_.PRG_TPFBI END ASC, CASE WHEN (PTHBI_PROCESSO_TIPO_FINAN_HIST.IDC_ALT_TPF_PTHBI IS NULL) THEN MAPBI_PROCESSO_MAPEAMENTO_TF.SUB_PRG_MAPBI ELSE TPFBI_TIPO_FINAN_HIST__ALIAS_.SUB_PRG_TPFBI END ASC, CASE WHEN (PTHBI_PROCESSO_TIPO_FINAN_HIST.IDC_ALT_TPF_PTHBI IS NULL) THEN MAPBI_PROCESSO_MAPEAMENTO_TF.CHM_MAPBI ELSE TPFBI_TIPO_FINAN_HIST__ALIAS_.CHM_TPFBI END ASC, CASE WHEN (PTHBI_PROCESSO_TIPO_FINAN_HIST.IDC_ALT_TPF_PTHBI IS NULL) THEN MAPBI_PROCESSO_MAPEAMENTO_TF.CLS_OBJ_MAPBI ELSE TPFBI_TIPO_FINAN_HIST__ALIAS_.CLS_OBJ_TPFBI END ASC, CASE PROB.IDC_BLS_OCM_PROBI WHEN 'S' THEN 'Sim' WHEN 'N' THEN 'Não' ELSE NULL END ASC, PROB.SIT_PROBI ASC, PROB.COO_PROBI ASC, to_char(trunc(PROB.DAT_AUT_PROBI, 'dd'), 'dd/MM/yyyy') ASC, to_char(trunc(PROB.DAT_DPC_PROBI, 'dd'), 'dd/MM/yyyy') ASC, to_char(PTHBI_PROCESSO_TIPO_FINAN_HIST.DAT_INI_TPF_PTHBI, 'dd/MM/yyyy') ASC, to_char(PTHBI_PROCESSO_TIPO_FINAN_HIST.DAT_TER_TPF_PTHBI, 'dd/MM/yyyy') ASC, T5.NOM_PESBI ASC, T5.SEX_PESBI ASC, PROB.TIT_PROBI ASC, PROB.CPL_TIT_PROBI ASC, T6.NOM_PESBI ASC, T6.SEX_PESBI ASC, GARBI_GDE_AREA.NOM_GDE_ARE_GARBI ASC, AREBI_AREA.NOM_ARE_AREBI ASC, SARBI_SUB_AREA.NOM_SUB_ARE_SARBI ASC, UNIBI_UNIDADE.NOM_UNIBI ASC, ITCBI_INSTITUICAO.NOM_ITCBI ASC, UNIBI_UNIDADE.CID_UNIBI ASC, T.NUM_PROBI ASC, T13.NOM_TPFBI ASC, PTHBI_PROCESSO_TIPO_FINAN_HIST.DAT_INI_TPF_PTHBI ASC, PROB.OGM_PROBI ASC, PROB.LST_PAL_CHV_PROBI ASC, PROB.CID_PROBI ASC, PROB.UF_PROBI ASC, PROB.PA_PROBI ASC, PROB.LST_OUT_ITC_PROBI ASC, PROB.OUT_PTC_PROBI ASC
| | +---> JOIN HASH [LeftOuter] [Cost: 133K, Rows: 416K] (PATH ID: 2)
| | |      Join Cond: (PROB.ID_PRO_PAI_PROBI = T.ID_PROBI)
| | |      Materialize at Input: PROB.ID_PRO_PAI_PROBI, T.ID_PROBI, T.NUM_PROBI, T.TPFBI_ID_TPFBI
| | |      Materialize at Output: PROB.NUM_PROBI, PROB.TMT_PROBI, PROB.OGM_PROBI, PROB.SIT_PROBI, PROB.TIT_PROBI, PROB.CPL_TIT_PROBI, PROB.DAT_AUT_PROBI, PROB.DAT_DPC_PROBI, PROB.LST_OUT_ITC_PROBI, PROB.LST_PAL_CHV_PROBI, PROB.IDC_BLS_OCM_PROBI, PROB.PA_PROBI, PROB.OUT_PTC_PROBI, PROB.CID_PROBI, PROB.UF_PROBI, PROB.COO_PROBI
| | | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 90K, Rows: 416K] (PATH ID: 3)
| | | |      Join Cond: (PTHBI_PROCESSO_TIPO_FINAN_HIST.PROBI_ID_PROBI = PVABI_PROCESSO_VALOR_ANO.PROBI_ID_PROBI)
| | | |      Join Filter: ((PTHBI_PROCESSO_TIPO_FINAN_HIST.TPFBI_ID_TPFBI = PVABI_PROCESSO_VALOR_ANO.TPFBI_ID_TPFBI) OR (PTHBI_PROCESSO_TIPO_FINAN_HIST.IDC_ALT_TPF_PTHBI IS NULL))
| | | | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 71K, Rows: 305K] (PATH ID: 4)
| | | | |      Join Cond: (PTHBI_PROCESSO_TIPO_FINAN_HIST.TPFBI_ID_TPFBI = TPFBI_TIPO_FINAN_HIST__ALIAS_.ID_TPFBI)
| | | | | +-- Outer -> JOIN HASH [Cost: 61K, Rows: 305K] (PATH ID: 5)
| | | | | |      Join Cond: (PROB.ID_PROBI = MAPBI_PROCESSO_MAPEAMENTO_TF.PROBI_ID_PROBI)
| | | | | |      Materialize at Input: PROB.ID_PROBI
| | | | | | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 36K, Rows: 305K] (PATH ID: 6)
| | | | | | |      Join Cond: (PROB.ID_PROBI = PTHBI_PROCESSO_TIPO_FINAN_HIST.PROBI_ID_PROBI)
| | | | | | |      Materialize at Input: PROB.ID_PROBI
| | | | | | |      Runtime Filter: (SIP1(HashJoin): PROB.ID_PROBI)
| | | | | | | +-- Outer -> JOIN HASH [Cost: 20K, Rows: 305K] (PATH ID: 7)
| | | | | | | |      Join Cond: (T6.ID_PESBI = PROB.PESBI_ID_RSP_PESBI)
| | | | | | | |      Materialize at Input: PROB.PESBI_ID_RSP_PESBI
| | | | | | | | +-- Outer -> JOIN HASH [Cost: 12K, Rows: 305K] (PATH ID: 8)
| | | | | | | | |      Join Cond: (T5.ID_PESBI = PROB.PESBI_ID_BEN_PESBI)
| | | | | | | | |      Materialize at Input: PROB.PESBI_ID_BEN_PESBI
| | | | | | | | | +-- Outer -> JOIN HASH [Cost: 5K, Rows: 305K] (PATH ID: 9)
| | | | | | | | | |      Join Cond: (UNIBI_UNIDADE.ID_UNIBI = PROB.UNIBI_ID_UNIBI)
| | | | | | | | | |      Materialize at Input: PROB.UNIBI_ID_UNIBI, UNIBI_UNIDADE.ITCBI_ID_ITCBI, UNIBI_UNIDADE.ID_UNIBI, UNIBI_UNIDADE.NOM_UNIBI, UNIBI_UNIDADE.CID_UNIBI
| | | | | | | | | | +-- Outer -> JOIN HASH [Cost: 2K, Rows: 305K] (PATH ID: 10)
| | | | | | | | | | |      Join Cond: (PROB.SARBI_ID_SARBI = SARBI_SUB_AREA.ID_SUB_ARE_SARBI)
| | | | | | | | | | |      Materialize at Input: SARBI_SUB_AREA.ID_SUB_ARE_SARBI, SARBI_SUB_AREA.NOM_SUB_ARE_SARBI, SARBI_SUB_AREA.AREBI_ID_ARE_AREBI
| | | | | | | | | | | +-- Outer -> STORAGE ACCESS for PROB [Cost: 2K, Rows: 305K] (PATH ID: 11)
| | | | | | | | | | | |      Projection: INFO.PROBI_PROCESSO_DBD_1_rep_info_info_node0001
| | | | | | | | | | | |      Materialize: PROB.SARBI_ID_SARBI
| | | | | | | | | | | |      Filter: (PROB.SIT_PROBI = ANY (ARRAY['Cancelado', 'Concedido', 'Denegado']))
| | | | | | | | | | | |      Filter: ((trunc(PROB.DAT_AUT_PROBI, 'dd') >= '01/01/2007 00:00:00'::timestamp) AND (trunc(PROB.DAT_AUT_PROBI, 'dd') <= '01/01/2012 00:00:00'::timestamp))
| | | | | | | | | | | |      Runtime Filters: (SIP5(HashJoin): PROB.SARBI_ID_SARBI), (SIP4(HashJoin): PROB.UNIBI_ID_UNIBI), (SIP3(HashJoin): PROB.PESBI_ID_BEN_PESBI), (SIP2(HashJoin): PROB.PESBI_ID_RSP_PESBI)
| | | | | | | | | | | +-- Inner -> JOIN HASH [Cost: 119, Rows: 417] (PATH ID: 12)
| | | | | | | | | | | |      Join Cond: (AREBI_AREA.ID_ARE_AREBI = SARBI_SUB_AREA.AREBI_ID_ARE_AREBI)
| | | | | | | | | | | |      Materialize at Input: AREBI_AREA.GARBI_ID_GDE_ARE_GARBI, AREBI_AREA.ID_ARE_AREBI, AREBI_AREA.NOM_ARE_AREBI
| | | | | | | | | | | | +-- Outer -> STORAGE ACCESS for SARBI_SUB_AREA [Cost: 17, Rows: 417] (PATH ID: 13)
| | | | | | | | | | | | |      Projection: INFO.SARBI_SUB_AREA_DBD_29_rep_full_full_v1_node0001
| | | | | | | | | | | | |      Materialize: SARBI_SUB_AREA.AREBI_ID_ARE_AREBI
| | | | | | | | | | | | |      Runtime Filter: (SIP6(HashJoin): SARBI_SUB_AREA.AREBI_ID_ARE_AREBI)
| | | | | | | | | | | | +-- Inner -> JOIN MERGEJOIN(inputs presorted) [Cost: 52, Rows: 77 (9 RLE)] (PATH ID: 14)
| | | | | | | | | | | | |      Join Cond: (AREBI_AREA.GARBI_ID_GDE_ARE_GARBI = GARBI_GDE_AREA.ID_GDE_ARE_GARBI)
| | | | | | | | | | | | | +-- Outer -> STORAGE ACCESS for AREBI_AREA [Cost: 17, Rows: 77 (9 RLE)] (PATH ID: 15)
| | | | | | | | | | | | | |      Projection: INFO.AREBI_AREA_DBD_3_rep_full_full_v1_node0001
| | | | | | | | | | | | | |      Materialize: AREBI_AREA.GARBI_ID_GDE_ARE_GARBI
| | | | | | | | | | | | | |      Runtime Filter: (SIP7(MergeJoin): AREBI_AREA.GARBI_ID_GDE_ARE_GARBI)
| | | | | | | | | | | | | +-- Inner -> STORAGE ACCESS for GARBI_GDE_AREA [Cost: 34, Rows: 9] (PATH ID: 16)
| | | | | | | | | | | | | |      Projection: INFO.GARBI_GDE_AREA_DBD_13_rep_full_full_v1_node0001
| | | | | | | | | | | | | |      Materialize: GARBI_GDE_AREA.ID_GDE_ARE_GARBI, GARBI_GDE_AREA.NOM_GDE_ARE_GARBI
| | | | | | | | | | +-- Inner -> JOIN HASH [Cost: 60, Rows: 8K (747 RLE)] (PATH ID: 17)
| | | | | | | | | | |      Join Cond: (ITCBI_INSTITUICAO.ID_ITCBI = UNIBI_UNIDADE.ITCBI_ID_ITCBI)
| | | | | | | | | | | +-- Outer -> STORAGE ACCESS for UNIBI_UNIDADE [Cost: 17, Rows: 8K (747 RLE)] (PATH ID: 18)
| | | | | | | | | | | |      Projection: INFO.UNIBI_UNIDADE_DBD_2_rep_info_info_node0001
| | | | | | | | | | | |      Materialize: UNIBI_UNIDADE.ITCBI_ID_ITCBI
| | | | | | | | | | | |      Runtime Filter: (SIP8(HashJoin): UNIBI_UNIDADE.ITCBI_ID_ITCBI)
| | | | | | | | | | | +-- Inner -> STORAGE ACCESS for ITCBI_INSTITUICAO [Cost: 36, Rows: 769] (PATH ID: 19)
| | | | | | | | | | | |      Projection: INFO.ITCBI_INSTITUICAO_DBD_16_rep_full_full_v1_node0001
| | | | | | | | | | | |      Materialize: ITCBI_INSTITUICAO.ID_ITCBI, ITCBI_INSTITUICAO.NOM_ITCBI
| | | | | | | | | +-- Inner -> STORAGE ACCESS for T5 [Cost: 2K, Rows: 164K] (PATH ID: 20)
| | | | | | | | | |      Projection: INFO.PESBI_PESSOA_DBD_23_rep_full_full_v1_node0001
| | | | | | | | | |      Materialize: T5.SEX_PESBI, T5.ID_PESBI, T5.NOM_PESBI
| | | | | | | | +-- Inner -> STORAGE ACCESS for T6 [Cost: 2K, Rows: 164K] (PATH ID: 21)
| | | | | | | | |      Projection: INFO.PESBI_PESSOA_DBD_23_rep_full_full_v1_node0001
| | | | | | | | |      Materialize: T6.SEX_PESBI, T6.ID_PESBI, T6.NOM_PESBI
| | | | | | | +-- Inner -> STORAGE ACCESS for PTHBI_PROCESSO_TIPO_FINAN_HIST [Cost: 9K, Rows: 305K] (PATH ID: 22)
| | | | | | | |      Projection: INFO.PTHBI_PROCESSO_TIPO_FINAN_HIST_DBD_26_rep_full_full_v1_node0001
| | | | | | | |      Materialize: PTHBI_PROCESSO_TIPO_FINAN_HIST.IDC_ALT_TPF_PTHBI, PTHBI_PROCESSO_TIPO_FINAN_HIST.TPFBI_ID_TPFBI, PTHBI_PROCESSO_TIPO_FINAN_HIST.ID_PTHBI, PTHBI_PROCESSO_TIPO_FINAN_HIST.PROBI_ID_PROBI, PTHBI_PROCESSO_TIPO_FINAN_HIST.DAT_INI_TPF_PTHBI, PTHBI_PROCESSO_TIPO_FINAN_HIST.DAT_TER_TPF_PTHBI, PTHBI_PROCESSO_TIPO_FINAN_HIST.VAL_CON_TPF_PTHBI, PTHBI_PROCESSO_TIPO_FINAN_HIST.VAL_DSB_TPF_PTHBI
| | | | | | +-- Inner -> STORAGE ACCESS for MAPBI_PROCESSO_MAPEAMENTO_TF [Cost: 5K, Rows: 305K] (PATH ID: 23)
| | | | | | |      Projection: INFO.MAPBI_PROCESSO_MAPEAMENTO_TF_DBD_18_rep_full_full_v1_node0001
| | | | | | |      Materialize: MAPBI_PROCESSO_MAPEAMENTO_TF.CLS_OBJ_MAPBI, MAPBI_PROCESSO_MAPEAMENTO_TF.CHM_MAPBI, MAPBI_PROCESSO_MAPEAMENTO_TF.CAT_ITM_MAPBI, MAPBI_PROCESSO_MAPEAMENTO_TF.PRG_MAPBI, MAPBI_PROCESSO_MAPEAMENTO_TF.PROBI_ID_PROBI, MAPBI_PROCESSO_MAPEAMENTO_TF.ITM_MAPBI, MAPBI_PROCESSO_MAPEAMENTO_TF.SUB_PRG_MAPBI
| | | | | +-- Inner -> STORAGE ACCESS for TPFBI_TIPO_FINAN_HIST__ALIAS_ [Cost: 136, Rows: 119] (PATH ID: 24)
| | | | | |      Projection: INFO.TPFBI_TIPO_FINAN_DBD_39_rep_full_full_v1_node0001
| | | | | |      Materialize: TPFBI_TIPO_FINAN_HIST__ALIAS_.ID_TPFBI, TPFBI_TIPO_FINAN_HIST__ALIAS_.NOM_TPFBI, TPFBI_TIPO_FINAN_HIST__ALIAS_.ITM_TPFBI, TPFBI_TIPO_FINAN_HIST__ALIAS_.CAT_ITM_TPFBI, TPFBI_TIPO_FINAN_HIST__ALIAS_.PRG_TPFBI, TPFBI_TIPO_FINAN_HIST__ALIAS_.SUB_PRG_TPFBI, TPFBI_TIPO_FINAN_HIST__ALIAS_.CHM_TPFBI, TPFBI_TIPO_FINAN_HIST__ALIAS_.CLS_OBJ_TPFBI
| | | | +-- Inner -> STORAGE ACCESS for PVABI_PROCESSO_VALOR_ANO [Cost: 2K, Rows: 416K] (PATH ID: 25)
| | | | |      Projection: INFO.PVABI_PROCESSO_VALOR_ANO_DBD_27_rep_full_full_v1_node0001
| | | | |      Materialize: PVABI_PROCESSO_VALOR_ANO.ANO_PVABI, PVABI_PROCESSO_VALOR_ANO.TPFBI_ID_TPFBI, PVABI_PROCESSO_VALOR_ANO.PROBI_ID_PROBI, PVABI_PROCESSO_VALOR_ANO.VAL_DSB_CTB_PVABI
| | | +-- Inner -> JOIN HASH [Cost: 594, Rows: 305K] (PATH ID: 26)
| | | |      Join Cond: (T.TPFBI_ID_TPFBI = T13.ID_TPFBI)
| | | | +-- Outer -> STORAGE ACCESS for T [Cost: 394, Rows: 305K] (PATH ID: 27)
| | | | |      Projection: INFO.PROBI_PROCESSO_DBD_24_rep_full_full_v1_node0001
| | | | |      Materialize: T.TPFBI_ID_TPFBI
| | | | |      Runtime Filter: (SIP9(HashJoin): T.TPFBI_ID_TPFBI)
| | | | +-- Inner -> STORAGE ACCESS for T13 [Cost: 34, Rows: 119] (PATH ID: 28)
| | | | |      Projection: INFO.TPFBI_TIPO_FINAN_DBD_39_rep_full_full_v1_node0001
| | | | |      Materialize: T13.ID_TPFBI, T13.NOM_TPFBI


trying so hard !!!

Post Reply

Return to “Vertica Performance Tuning”