Performance INSERT com select

Tuning de Banco, Tuning de SQL, Ferramentas de tuning
Responder
Avatar do usuário
gpereira
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 61
Registrado em: Sáb, 10 Dez 2011 10:56 am
Gustavo Antonio Pereira
OCA Oracle Database 10g Administrator Certified Associate

Pessoal, como vão?

A tempos, iniciei um projeto de ETL que encontra-se em fase de conclusão.

Ultimo processo de carga full, envolve o JOIN de 5 tabelas gigantes. Decidi então fazer joins por fases.

Ultima fase, envolve um join de duas tabelas que não entendo por que esta demorando absurdamente, outras fases envolveram tabelas com maior massa de dados e não demoraram mais de 4 horas.

Este processo esta a quese 24 horas em execução e não terminou.

Segue dados:

DBM_3CARTAO - Mais de 200 milhões de linhas

CUSTOMER_CARD - Em torno de 90 milhoes de linhas

Selecionar tudo

INSERT INTO DBM.TMP_DBM_4CLIENTE

(
ID_TRANSACAO,
ID_DETALHE_TRANSACAO,
NR_TICKET,
DT_TRANSACAO,
ID_LOJA,
ID_CLIENTE,
NR_CARTAO,
NR_AGRUPAMENTO,
FL_TROCA,
ID_SKU,
VL_preço,
VL_DESCONTO,
VL_DESC_ASSOCIADO,
VL_REMARCACAO,
QT_PRODUTO
)

select

ST.ID_TRANSACAO,
ST.ID_DETALHE_TRANSACAO,
ST.NR_TICKET,
ST.DT_TRANSACAO,
ST.ID_LOJA,
NVL(CA.CUSTOMERPROSPECTID,-2),
NVL(ST.NR_CARTAO,-2),
NVL(CA.ACCOUNTGROUPNBR,-2),
ST.FL_TROCA,
ST.ID_SKU,
ST.VL_preço,
ST.VL_DESCONTO,
ST.VL_DESC_ASSOCIADO,
ST.VL_REMARCACAO,
ST.QT_PRODUTO

from DBM.DBM_3CARTAO st,
      CUSTOMER_CARD CA
where ST.NR_CARTAO = CA.CARDNBR (+) ;
Pontos importantes:

-- Join é feito por campo vachar2 que possui 19 caracteres (isso pode ocasionar lentidao no join)

-- Indice esta criado na tabela CUSTOMER_CARD no campo CARDNBR,porem executando "plano de execuçao", ainda sim parece estar executando full scan (não sei informar se estatisticas estao atualizadas, não administro o banco aqui)
Sem título.jpg

-- Verificado e não existem locks

-- Verificado gerenciador da maquina e esta dentro do esperado, não realizando swaap e processamento nos conformes.

-- Estou utilizando "paralelismo 2", utilizei desta mesma forma nos outros processamentos e surtiu efeito.

Duvidas

Voces julgam esta demora aceitavel? Caso negativo, o que pode ser feito para melhoria?

NVL aplicado pode impactar nesta demora?

Pode ser algo relacionado ao paralelismo?

Repito, processos anteriores tiveram maiores massas de dados, porem não demorou isso tudo.
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Oi Gpereira,

Não teria como lhe dar um diagnóstico completo para o seu caso.

Só queria então fazer algumas sugestões, mais para chamar outros foristas (mais experientes em perfomance) para esta discussão.

Seu insert faz a carga de 200 milhoes de linhas, pois você está fazendo um OUTER-JOIN entre DBM_3CARTAO e CUSTOMER_CARD. Será que isso não leva tempo para ser processado?

Se você tentasse o seguinte:

1) Transformar a tabela TMP_DBM_4CLIENTE em NO LOGGING;
2) Alterar a estrutura da tabela de destino (TMP_DBM_4CLIENTE), adicionando a coluna NR_CARTAO;
3) Criar um indice em TMP_DBM_4CLIENTE por NR_CARTAO;
4) Desativar este indice na carga (SKIP UNUSED INDEXES);
5) Fazer INSERT /*+ APPEND */ dos dados de DBM_3CARTAO em TMP_DBM_4CLIENTE; (cronometre aqui o tempo para ele fazer este insert sem o outer join .. comparando este tempo com o processamento completo, você tem como avaliar o quando o JOIN está aumentando este tempo);
6) Fazer rebuild do indice;
7) Executar um CURSOR + COLLECTION, para fazer update em TMP_DBM_4CLIENTE das colunas NVL(CA.CUSTOMERPROSPECTID,-2) e NVL(CA.ACCOUNTGROUPNBR,-2). Você faria um comando FORALL .. UPDATE..;

Será que você teria algum ganho de performance?

Não creio que o NVL esteja impactando no seu caso, mas você pode fazer um teste retirando esta função do seu insert e avaliando se houve alguma ganho significativo.

Creio que os foristas podem lhe apresentar outras sugestões;

Abraços,

Sergio Coutinho


Será que teria algum





DBM_3CARTAO - Mais de 200 milhões de linhas

CUSTOMER_CARD - Em torno de 90 milhoes de linhas
Avatar do usuário
fbifabio
Moderador
Moderador
Mensagens: 199
Registrado em: Ter, 22 Fev 2011 1:51 pm
Localização: São Paulo - SP
Contato:
Fábio Prado
www.fabioprado.net

gpereira ,

O stcoutinho deu ótima resposta. Para complementar seguem abaixo 3 dicas de sql tuning e que tenho certeza que te ajudarão a otimizar a performance dessa query:

1- Crie a tabela DBM.TMP_DBM_4CLIENTE como GTT (global temporary table)
2- Otimize o SELECT criando por exemplo um índice na FK do relacionamento e/ou substituindo o outer join por 2 instruções ligadas por UNION com INNER JOIN
3- Faça inserção com carga direta usando o hint APPEND (pesquise as restrições deste comando).

Obs.: Dei apenas 3 dicas, mas muito mais coisas poderiam ser realizadas com maior tempo de dedicação analisando a instrução SQL e o ambiente em que ela está sendo executada.

[]s
Responder
  • Informação
  • Quem está online

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