Hi List,
I have posted a subjetc on the admin list but I thought that it might fit
better on this list as follow:
Hi List,
As I said before, I'm not a DBA " yet" , but I'm learning ... and I
already have a PostgreSQL running, so I have to ask some help...
I got a SQL as folows :
SELECT /*+ */
ftnfco00.estado_cliente ,
ftcofi00.grupo_faturamento ,
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.qtde_duzias,0)), '+', NVL(ftnfpr00.qtde_duzias,0), 0) ) ,
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL(ftnfpr00.vlr_liquido,0)), '+',
NVL(ftnfpr00.vlr_liquido,0), 0) ) ,
ftprod00.tipo_cadastro||ftprod00.codigo_produto||'||'||gsames00.ano_mes ,
ftprod00.descricao_produto||'||'||gsames00.descricao ,
DIVIDE( SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.vlr_liquido,0)), '+', NVL(ftnfpr00.vlr_liquido,0), 0)
*ftnfpr00.margem_comercial ),
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.vlr_liquido,0)), '+', NVL(ftnfpr00.vlr_liquido,0), 0)) ) ,
SUM( DECODE( ftcofi00.nf_prodgratis, 'S', NVL(ftnfpr00.qtde_duzias,0), 0 ) ) ,
SUM( DECODE( ftcofi00.nf_prodgratis, 'S', NVL(ftnfpr00.vlr_liquido,0), 0 ) )
FROM
ftprod00 ,
ftnfco00 ,
ftcgma00 ,
ftcgca00 ,
ftspro00 ,
ftclcr00 ,
gsames00 ,
ftcofi00 ,
ftrepr00 ,
gsesta00 ,
ftsupv00 ,
ftgrep00 ,
ftclgr00 ,
ftband00 ,
fttcli00 ,
ftredc00 ,
ftnfpr00
WHERE
ftnfco00.emp = 909 AND
ftnfpr00.fil IN ('101') AND
ftnfco00.situacao_nf = 'N' AND
TO_CHAR(ftnfco00.data_emissao,'YYYYMM') >= '200208' AND
TO_CHAR(ftnfco00.data_emissao,'YYYYMM') <= '200304' AND
ftcofi00.grupo_faturamento >= '01' AND
(ftcofi00.atual_fatura IN ('+','-') OR ftcofi00.nf_prodgratis = 'S') AND
ftcgma00.emp = ftprod00.emp AND
ftcgma00.fil = ftprod00.fil AND
ftcgma00.codigo = ftprod00.cla_marca AND
ftcgca00.emp = ftprod00.emp AND
ftcgca00.fil = ftprod00.fil AND
ftcgca00.codigo = ftprod00.cla_categoria AND
ftspro00.emp = ftprod00.emp AND
ftspro00.fil = ftprod00.fil AND
ftspro00.codigo = ftprod00.situacao AND
ftclcr00.emp = ftnfco00.emp AND
ftclcr00.fil = ftnfco00.empfil AND
ftclcr00.tipo_cadastro = ftnfco00.tipo_cad_clicre AND
ftclcr00.codigo = ftnfco00.cod_cliente AND
gsames00.ano_mes = TO_CHAR(ftnfco00.data_emissao,'YYYYMM') AND
ftcofi00.emp = ftnfco00.emp AND
ftcofi00.fil = ftnfco00.empfil AND
ftcofi00.codigo_fiscal = ftnfco00.cod_fiscal AND
ftrepr00.emp = ftnfco00.emp AND
ftrepr00.fil = ftnfco00.empfil AND
ftrepr00.codigo_repr = ftnfco00.cod_repres AND
gsesta00.estado_sigla = ftnfco00.estado_cliente AND
ftsupv00.emp = ftrepr00.emp AND
ftsupv00.fil = ftrepr00.fil AND
ftsupv00.codigo_supervisor = ftrepr00.codigo_supervisor AND
ftgrep00.emp = ftrepr00.emp AND
ftgrep00.fil = ftrepr00.fil AND
ftgrep00.codigo_grupo_rep = ftrepr00.codigo_grupo_rep AND
ftclgr00.emp = ftclcr00.emp AND
ftclgr00.fil = ftclcr00.fil AND
ftclgr00.codigo = ftclcr00.codigo_grupo_cliente AND
ftband00.emp = ftclcr00.emp AND
ftband00.fil = ftclcr00.fil AND
ftband00.codigo = ftclcr00.bandeira_cliente AND
fttcli00.emp = ftclcr00.emp AND
fttcli00.fil = ftclcr00.fil AND
fttcli00.cod_tipocliente = ftclcr00.codigo_tipo_cliente AND
ftredc00.emp = ftclcr00.emp AND
ftredc00.fil = ftclcr00.fil AND
ftredc00.tipo_contribuinte = ftclcr00.tipo_contribuinte AND
ftredc00.codigo_rede = ftclcr00.codigo_rede AND
gsesta00.estado_sigla = ftclcr00.emp_estado AND
ftnfco00.emp = ftnfpr00.emp AND
ftnfco00.fil = ftnfpr00.fil AND
ftnfco00.nota_fiscal = ftnfpr00.nota_fiscal AND
ftnfco00.serie = ftnfpr00.serie AND
ftnfco00.data_emissao = ftnfpr00.data_emissao AND
ftprod00.emp = ftnfpr00.emp AND
ftprod00.fil = ftnfpr00.empfil AND
ftprod00.tipo_cadastro = ftnfpr00.tipo_cad_promat AND
ftprod00.codigo_produto= ftnfpr00.cod_produto
GROUP BY
ftnfco00.estado_cliente ,
ftcofi00.grupo_faturamento ,
ftprod00.tipo_cadastro||ftprod00.codigo_produto||'||'||gsames00.ano_mes ,
ftprod00.descricao_produto||'||'||gsames00.descricao
I have created the decode, NVL and DIVIDE functions.... the problem is that the
where condition makes this query to slow ( about 4 min ) and the same query in
my Oracle database takes less than 40 seconds. I have tried to isolate the
problem taking off some fields and I left justa the two first fields in the
query ( ftnfco00.estado_cliente , ftcofi00.grupo_faturamento ) and it still
taking almost 4 min to return. Does anyone have a hint to give me to make it
faster ?
Atached goes a explain analyze return os this query.
Atenciosamente,
Rhaoni Chiu Pereira
Sist�mica Computadores
Visite-nos na Web: http://sistemica.info
Fone/Fax : +55 51 3328 1122
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=7971.65..7971.68 rows=1 width=833) (actual
time=1501529.16..1508066.32 rows=1755 loops=1)
-> Group (cost=7971.65..7971.66 rows=1 width=833) (actual
time=1501495.07..1502517.99 rows=24111 loops=1)
-> Sort (cost=7971.65..7971.65 rows=1 width=833) (actual
time=1501495.03..1501563.87 rows=24111 loops=1)
Sort Key: ftnfco00.estado_cliente, ftcofi00.grupo_faturamento,
((((ftprod00.tipo_cadastro)::text || (ftprod00.codigo_produto)::text) || '||'::text)
|| (gsames00.ano_mes)::text), ((ftprod00.descricao_produto || '||'::character varying)
|| gsames00.descricao)
-> Nested Loop (cost=7913.31..7971.64 rows=1 width=833) (actual
time=1382281.24..1495748.79 rows=24111loops=1)
Join Filter: ("inner".estado_sigla = "outer".estado_cliente)
-> Nested Loop (cost=7913.31..7969.90 rows=1 width=827) (actual
time=1382271.41..1490702.11 rows=24111 loops=1)
Join Filter: (("outer".emp = "inner".emp) AND ("inner".fil
= "outer".empfil) AND ("inner".codigo = "outer".bandeira_cliente))
-> Nested Loop (cost=7913.31..7968.87 rows=1 width=795)
(actual time=1382179.80..1486492.69 rows=24111 loops=1)
Join Filter: (("inner".emp = "outer".emp) AND
("outer".codigo = "inner".cla_categoria)AND ("outer".codigo = "inner".cla_marca) AND
("outer".codigo = "inner".situacao))
-> Nested Loop (cost=7913.31..7954.34 rows=3
width=705) (actual time=1381632.57..1464651.32 rows=241110 loops=1)
Join Filter: (("outer".emp = "inner".emp) AND
("inner".fil = "outer".empfil)) -> Nested Loop
(cost=7913.31..7941.84 rows=2 width=665) (actual time=1381444.87..1408531.29
rows=241110 loops=1)
Join Filter: (("inner".emp = "outer".emp)
AND ("outer".fil = "inner".empfil) AND("outer".codigo_grupo_rep =
"inner".codigo_grupo_rep))
-> Seq Scan on ftgrep00
(cost=0.00..1.01 rows=1 width=32) (actual time=28.97..28.97 rows=1 loops=1)
-> Materialize (cost=7940.79..7940.79
rows=2 width=633) (actual time=1381415.51..1385445.39 rows=241110 loops=1)
-> Nested Loop
(cost=7913.31..7940.79 rows=2 width=633) (actual time=1348400.96..1379090.93
rows=241110 loops=1)
Join Filter: (("inner".emp =
"outer".emp) AND ("outer".fil = "inner".empfil) AND ("outer".codigo_supervisor =
"inner".codigo_supervisor))
-> Seq Scan on ftsupv00
(cost=0.00..1.01 rows=1 width=32) (actual time=13.92..13.94 rows=1 loops=1)
-> Materialize
(cost=7939.75..7939.75 rows=2 width=601) (actual time=1348386.88..1357853.77
rows=241110 loops=1)
-> Nested Loop
(cost=7913.31..7939.75 rows=2 width=601) (actual time=238548.84..1344339.33
rows=241110 loops=1)
Join Filter:
(("inner".ano_mes)::text = to_char("outer".data_emissao, 'YYYYMM'::text))
-> Merge Join
(cost=7913.31..7913.45 rows=2 width=576)(actual time=238524.31..275724.44 rows=241110
loops=1)
Merge Cond:
(("outer".empfil = "inner".fil) AND ("outer".codigo_tipo_cliente =
"inner".cod_tipocliente) AND ("outer".emp = "inner".emp))
-> Sort
(cost=7911.79..7911.80 rows=2 width=543)(actual time=238523.48..254284.13 rows=241110
loops=1)
Sort
Key: ftclcr00.fil, ftclcr00.codigo_tipo_cliente, ftnfpr00.emp
->
Nested Loop (cost=7851.29..7911.78 rows=2 width=543) (actual time=80005.55..144755.84
rows=241110 loops=1)
Join Filter: (("inner".emp_estado = "outer".estado_cliente) AND ("outer".codigo =
"inner".codigo_grupo_cliente))
-> Nested Loop (cost=7851.29..7853.22 rows=10 width=449) (actual
time=79960.48..94206.17 rows=241110 loops=1)
Join Filter: (("inner".emp = "outer".emp) AND ("outer".fil = "inner".empfil))
-> Seq Scan on ftclgr00 (cost=0.00..1.01 rows=1 width=32) (actual
time=12.21..12.21 rows=1 loops=1)
-> Materialize (cost=7852.06..7852.06 rows=10 width=417) (actual
time=79948.16..82051.01 rows=241110 loops=1)
-> Merge Join (cost=7851.29..7852.06 rows=10 width=417) (actual
time=65536.22..78515.30 rows=241110 loops=1)
Merge Cond: (("outer".empfil = "inner".fil) AND ("outer".cod_repres =
"inner".codigo_repr) AND ("outer".emp = "inner".emp))
-> Sort (cost=7840.51..7840.54 rows=10 width=364) (actual time=64203.33..65522.80
rows=241110 loops=1)
Sort Key: ftnfco00.empfil, ftnfco00.cod_repres, ftnfpr00.emp
-> Nested Loop
(cost=7569.51..7840.35 rows=10 width=364) (actual time=2094.38..31469.51 rows=241110
loops=1)
Join Filter: (("outer".emp = "inner".emp) AND ("outer".empfil = "inner".fil))
-> Nested Loop (cost=7569.51..7839.10 rows=1 width=337) (actual
time=2094.00..21568.08 rows=24111 loops=1)
Join Filter: (("outer".emp = "inner".emp) AND ("outer".empfil = "inner".fil))
-> Nested Loop (cost=7569.51..7838.07 rows=1 width=305) (actual
time=2086.59..20456.24 rows=24111 loops=1)
Join Filter: (("outer".emp = "inner".emp) AND ("outer".empfil = "inner".fil))
-> Nested Loop (cost=7569.51..7837.05 rows=1 width=273) (actual
time=2078.21..19368.42 rows=24111 loops=1)
-> Merge Join (cost=7569.51..7572.07 rows=44 width=161) (actual
time=1991.00..2262.54 rows=7718 loops=1)
Merge Cond: (("outer".cod_fiscal = "inner".codigo_fiscal) AND
("outer".empfil = "inner".fil) AND ("outer".emp = "inner".emp))
-> Sort (cost=7520.71..7521.45 rows=295 width=109) (actual
time=1958.40..1994.38 rows=9339 loops=1)
Sort Key: ftnfco00.cod_fiscal, ftnfco00.empfil, ftnfco00.emp
-> Seq Scan on ftnfco00 (cost=0.00..7508.60 rows=295 width=109)
(actual time=14.50..1581.81 rows=9339 loops=1)
Filter: ((emp = 909::numeric) AND (situacao_nf =
'N'::character varying) AND (to_char(data_emissao, 'YYYYMM'::text) >= '200209'::text)
AND (to_char(data_emissao, 'YYYYMM'::text) <= '200304'::text))
-> Sort (cost=48.80..48.98 rows=72 width=52) (actual
time=31.86..43.69 rows=7765 loops=1)
Sort Key: ftcofi00.codigo_fiscal, ftcofi00.fil, ftcofi00.emp
-> Seq Scan on ftcofi00 (cost=0.00..46.58 rows=72 width=52)
(actual time=7.62..31.07 rows=75 loops=1)
Filter: ((grupo_faturamento >= '01'::character varying) AND
((atual_fatura = '+'::character varying) OR (atual_fatura = '-'::character varying) OR
(nf_prodgratis = 'S'::character varying)))
-> Index Scan using ftnfpr00_pkey on ftnfpr00 (cost=0.00..5.95 rows=1
width=112) (actual time=1.41..2.14 rows=3 loops=7718)
Index Cond: (("outer".emp = ftnfpr00.emp) AND ("outer".fil =
ftnfpr00.fil) AND (ftnfpr00.fil = 101::numeric) AND ("outer".nota_fiscal =
ftnfpr00.nota_fiscal) AND ("outer".serie = ftnfpr00.serie) AND ("outer".data_emissao =
ftnfpr00.data_emissao))
-> Seq Scan on ftcgca00 (cost=0.00..1.01 rows=1 width=32) (actual time=0.01..0.01
rows=1 loops=24111)
-> Seq Scan on ftcgma00 (cost=0.00..1.01 rows=1 width=32) (actual time=0.01..0.01
rows=1 loops=24111)
-> Seq Scan on ftspro00 (cost=0.00..1.10 rows=10 width=27) (actual time=0.00..0.04
rows=10 loops=24111)
-> Sort (cost=10.78..11.05 rows=109 width=53) (actual time=1332.54..1510.69
rows=241175 loops=1)
Sort Key: ftrepr00.fil, ftrepr00.codigo_repr, ftrepr00.emp
-> Seq Scan on ftrepr00 (cost=0.00..7.09 rows=109 width=53) (actual
time=406.63..1331.33 rows=109 loops=1)
-> Index Scan using ftclcr01 on ftclcr00 (cost=0.00..5.83 rows=1 width=94) (actual
time=0.13..0.14 rows=1 loops=241110)
Index Cond: ((ftclcr00.emp = "outer".emp) AND (ftclcr00.fil = "outer".empfil) AND
(ftclcr00.tipo_cadastro = "outer".tipo_cad_clicre) AND (ftclcr00.codigo = "ou
ter".cod_cliente))
-> Sort
(cost=1.52..1.56 rows=17 width=33) (actual time=0.61..225.43 rows=240986 loops=1)
Sort Key: fttcli00.fil, fttcli00.cod_tipocliente, fttcli00.emp
-> Seq Scan on fttcli00 (cost=0.00..1.17 rows=17 width=33) (actual time=0.12..0.17
rows=17 loops=1)
-> Seq Scan on
gsames00 (cost=0.00..6.72 rows=372 width=25) (actual time=0.00..1.28 rows=372
loops=241110)
-> Index Scan using ftredc00_pkey on ftredc00
(cost=0.00..5.83 rows=1 width=40) (actual time=0.12..0.12 rows=1 loops=241110)
Index Cond: ((ftredc00.emp = "outer".emp)
AND (ftredc00.fil = "outer".fil) AND (f
tredc00.tipo_contribuinte = "outer".tipo_contribuinte) AND (ftredc00.codigo_rede =
"outer".codigo_rede))
-> Index Scan using ftprod00_pkey on ftprod00
(cost=0.00..5.74 rows=1 width=90) (actual time=0.06..0.06 rows=1 loops=241110)
Index Cond: ((ftprod00.emp = "outer".emp) AND
(ftprod00.fil = "outer".empfil) AND (ftprod00.tipo_cadastro = "outer".tipo_cad_promat)
AND (ftprod00.codigo_produto = "outer".cod_produto))
-> Seq Scan on ftband00 (cost=0.00..1.01 rows=1 width=32)
(actual time=0.01..0.01 rows=1 loops=24111)
-> Seq Scan on gsesta00 (cost=0.00..1.33 rows=33 width=6)
(actual time=0.01..0.08 rows=33 loops=24111) Total runtime: 1508259.91 msec
(74 rows)
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
