Melhorar performance

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 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?

Selecionar tudo

              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_%'));
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

Bom dia cleberz,

tenta comparar mais colunas

Selecionar tudo

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.
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

Melhorou não.
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

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

Selecionar tudo

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.
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

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?
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

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

Selecionar tudo

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;
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

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
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

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.
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

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

Se é índice mesmo, como resolvo? uso um hint?
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

Não sei se um hint resolveria...

Vou fazer uns testes aqui.
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

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

Selecionar tudo

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;
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

Bom...

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

Selecionar tudo

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;
/
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

ok. vou testar essa.
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

só não esquece de alterar o nome do dblink, deixei o meu :?
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

e eu esqueci de tirar um ;

Selecionar tudo

and table_name like v_table_name||'%';
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

testando ainda, aguenta aí brother.
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 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?
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

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
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

Acho que consegui alguma coisa, tenta assim

Selecionar tudo

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;
/
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

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

Agora to testando esse seu bloco.
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

Meu amigo, demorou 13.000 segundos. com parallel fica mais rapido.
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

beleza, pelo menos você já diminuiu bastante desde o início
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

Sim, obrigado brother. Mas ainda to tentandodominuir mais. Valeu!
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 1 visitante