Melhorar performance

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Mensagemem Qui, 31 Ago 2017 8:42 am

Bom dia,

Estou fazendo o select abaixo e gostaria de melhorar a performnce, pois estou utilizando-o em uma package e esta demorando um absurdo. alguém tem uma dica para eu melhorar a performance do select abaixo?

SELECT TABLE_NAME, COLUMN_NAME
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'USER_1'
AND CONSTRAINT_TYPE = 'P'
AND TABLE_NAME LIKE ('TABLE_CUST_%'));
cleberz

Mensagemem Qui, 31 Ago 2017 9:19 am

Bom dia cleberz,

tenta comparar mais colunas

SELECT TABLE_NAME, COLUMN_NAME
FROM ALL_CONS_COLUMNS
WHERE (owner, table_name, CONSTRAINT_NAME) IN (SELECT owner, table_name, CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'USER_1'
AND CONSTRAINT_TYPE = 'P'
AND TABLE_NAME LIKE ('TABLE_CUST_%'));
pode ser que melhore.
spernega
Localização: São Paulo - SP

Mensagemem Qui, 31 Ago 2017 1:29 pm

Melhorou não.
cleberz

Mensagemem Qui, 31 Ago 2017 1:38 pm

o select abaixo é um cursor que gera uma query. a execução do bloco abaixo é que demoro muuuuuuito, absurdamente, 12 horas para 22 tabelas.

declare
queryresult number(38) := 0;
cursor C_1 is
SELECT TABLE_NAME, COLUMN_NAME
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'USER1'
AND CONSTRAINT_TYPE = 'P'
AND TABLE_NAME LIKE ('TABLE_CUST%'));

begin
for rec in c_1 loop

execute immediate 'SELECT COUNT(1) FROM USER1.'||rec.TABLE_NAME||' a WHERE NOT EXISTS (SELECT 1 FROM USER1.'||TABLE_NAME||'@DB_LINK b WHERE a.'||rec.COLUMN_NAME||' = b.'||rec.COLUMN_NAME) into queryresult;

end loop;
end;
Editado pela última vez por cleberz em Qui, 31 Ago 2017 1:39 pm, em um total de 1 vez.
cleberz

Mensagemem Qui, 31 Ago 2017 1:39 pm

Será que o gargalo está nesta query mesmo?

Estou executando na minha base e retorna direitinho, sem demora.

Quer postas a package pra gente dar uma olhada?
spernega
Localização: São Paulo - SP

Mensagemem Qui, 31 Ago 2017 1:40 pm

o select abaixo é um cursor que gera uma query. a execução do bloco abaixo é que demoro muuuuuuito, absurdamente, 12 horas para 22 tabelas.

declare
queryresult number(38) := 0;
cursor C_1 is
SELECT TABLE_NAME, COLUMN_NAME
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'USER1'
AND CONSTRAINT_TYPE = 'P'
AND TABLE_NAME LIKE ('TABLE_CUST%'));

begin
for rec in c_1 loop

execute immediate 'SELECT COUNT(1) FROM USER1.'||rec.TABLE_NAME||' a WHERE NOT EXISTS (SELECT 1 FROM USER1.'||TABLE_NAME||'@DB_LINK b WHERE a.'||rec.COLUMN_NAME||' = b.'||rec.COLUMN_NAME) into queryresult;

end loop;
end;
cleberz

Mensagemem Qui, 31 Ago 2017 1:42 pm

Vou testar com o que você mandou, mas por via das dúvidas, execute só a Query pra ver se está muito lenta mesmo.
spernega
Localização: São Paulo - SP

Mensagemem Qui, 31 Ago 2017 1:51 pm

O select no execute immediate que está sendo executado pode não estar usando nenhum índice, além do LINK poder estar lento.

Mas eu aposto na primeira hipótese, sem índice.

Você está tentando comparar os registros faltantes na base remota?

Se as suas PKs forem compostas por mais de uma coluna, quase certeza que é índice mesmo.
spernega
Localização: São Paulo - SP

Mensagemem Qui, 31 Ago 2017 1:55 pm

Você está tentando comparar os registros faltantes na base remota? Sim

Se é índice mesmo, como resolvo? uso um hint?
cleberz

Mensagemem Qui, 31 Ago 2017 2:01 pm

Não sei se um hint resolveria...

Vou fazer uns testes aqui.
spernega
Localização: São Paulo - SP

Mensagemem Qui, 31 Ago 2017 2:11 pm

Veja o hint que coloquei no segundo select do execute immediate.
Melhorou muito:

execute immediate 'SELECT COUNT(1) FROM USER1.'||rec.TABLE_NAME||' a WHERE NOT EXISTS (SELECT /*+ parallel */ 1 FROM USER1.'||TABLE_NAME||'@DB_LINK b WHERE a.'||rec.COLUMN_NAME||' = b.'||rec.COLUMN_NAME) into queryresult;
cleberz

Mensagemem Qui, 31 Ago 2017 2:27 pm

Bom...

Tentei de outro jeito, veja se ajuda um pouco mais.
Usei o minus ao invés not exists, mais fácil de montar.

declare
--
v_owner varchar2(40) := 'USER1';
v_link varchar2(40) := '@dblink_siv001';
v_table_name varchar2(40) := 'TABLE_CUST';
queryresult number(38) := 0;
--
cursor c is
select constraint_name, table_name from all_constraints
where owner = v_owner
and table_name like v_table_name||'%';
and constraint_type = 'P';
c_r c%rowtype;
--
cursor C1 is
select column_name, count(*) over (partition by table_name) qt_colu
from all_cons_columns
where owner = v_owner
and table_name = c_r.table_name
and constraint_name = c_r.constraint_name
order by position ;
c1_r c1%rowtype;
--
v_comando varchar2(2000);
v_colunas varchar2(2000);
v_contador number := 0;
--
begin
open c;
loop
fetch c into c_r;
exit when c%notfound;
--
v_Comando := null;
v_contador := 1;
v_colunas := null;
open c1;
loop
fetch c1 into c1_r;
exit when c1%notfound;
--
if v_contador = c1_r.qt_colu then
v_colunas := v_colunas||c1_r.column_name;
else
v_colunas := v_colunas||c1_r.column_name||',';
end if;
v_contador := nvl(v_contador,0) + 1;
--
end loop;
close c1;
--
v_Comando := 'Select count(1) from ( ';
v_comando := v_Comando||'Select '||v_Colunas||' from '||c_r.table_name||' minus '||'Select '||v_Colunas||' from '||c_r.table_name||v_link||')';
--v_comando := v_Comando||'Select '||v_Colunas||' from '||c_r.table_name||v_link||' minus '||'Select '||v_Colunas||' from '||c_r.table_name||')';
--
dbms_output.put_line(v_Comando);
execute immediate v_Comando into queryresult;
dbms_output.put_line(queryresult);
end loop;
close c;
end;
/
spernega
Localização: São Paulo - SP

Mensagemem Qui, 31 Ago 2017 2:30 pm

ok. vou testar essa.
cleberz

Mensagemem Qui, 31 Ago 2017 2:31 pm

só não esquece de alterar o nome do dblink, deixei o meu :?
spernega
Localização: São Paulo - SP

Mensagemem Qui, 31 Ago 2017 2:44 pm

e eu esqueci de tirar um ;

and table_name like v_table_name||'%';
spernega
Localização: São Paulo - SP

Mensagemem Qui, 31 Ago 2017 5:26 pm

testando ainda, aguenta aí brother.
cleberz

Mensagemem Sex, 01 Set 2017 9:23 am

Brother, da forma como você colocou esta demorando 11.000 segundos e com o hint /*+ parallel */ que coloquei esta demorando 9.000 segundos, um pouco mais rapido. Ainda assim não tem como a gente melhorar?
cleberz

Mensagemem Sex, 01 Set 2017 9:42 am

Tenta fazer usando o not exist, não tenho certeza se um é mais rápido que o outro, mas não custa tentar.

Enquanto isso vou pensando por aqui.
spernega
Localização: São Paulo - SP

Mensagemem Sex, 01 Set 2017 10:08 am

Acho que consegui alguma coisa, tenta assim

declare
--
v_owner varchar2(40) := 'USER_1';
v_link varchar2(40) := '@DB_LINK';
v_table_name varchar2(40) := 'TABLE_CUST_';
--
queryresult number(38) := 0;
v_qt_regi_base_loca number(38) := 0;
v_qt_regi_base_remo number(38) := 0;
--
cursor c is
select constraint_name, table_name from all_constraints
where owner = v_owner
and table_name like v_table_name||'%'
and constraint_type = 'P';
c_r c%rowtype;
--
cursor C1 is
select column_name, count(*) over (partition by table_name) qt_colu
from all_cons_columns
where owner = v_owner
and table_name = c_r.table_name
and constraint_name = c_r.constraint_name
order by position ;
c1_r c1%rowtype;
--
v_comando varchar2(2000);
v_colunas varchar2(2000);
v_contador number := 0;
--
begin
open c;
loop
fetch c into c_r;
exit when c%notfound;
--
-- pesquisa a quantidade de registros na base local
--
v_qt_regi_base_loca := 0;
execute immediate 'select count(1) from '||c_r.table_name into v_qt_regi_base_loca;
--
v_contador := 1;
v_colunas := null;
v_Comando := 'Select count(1) from '||c_r.table_name||' a where exists (select 1 from '||c_r.table_name||v_link||' b ';
open c1;
loop
fetch c1 into c1_r;
exit when c1%notfound;
--
if v_contador = 1 then
v_Comando := v_Comando||' where ';
else
v_Comando := v_Comando||' and ';
end if;
--
v_Comando := v_Comando||' a.'||c1_r.column_name||' = b.'||c1_r.column_name;
v_contador := nvl(v_contador,0) + 1;
--
end loop;
close c1;
--
v_Comando := v_Comando||')';
--
dbms_output.put_line(v_Comando);
execute immediate v_Comando into v_qt_regi_base_remo;
--
dbms_output.put_line('Local :'||nvl(v_qt_regi_base_loca,0));
dbms_output.put_line('Remota :'||nvl(v_qt_regi_base_Remo,0));
dbms_output.put_line( nvl(v_qt_regi_base_loca,0) - nvl(v_qt_regi_base_remo,0) );
end loop;
close c;
end;
/
spernega
Localização: São Paulo - SP

Mensagemem Sex, 01 Set 2017 3:23 pm

Com o hint /*+ PARALLEL (5) */ consegui derrubar de 9.000 segundos para 6.000 segundos.

Agora to testando esse seu bloco.
cleberz


Próximo

Voltar para SQL

Quem está online

Usuários navegando neste fórum: Nenhum usuário registrado e 4 visitantes