Menu de navegação

[Andrei RubinoParticipante]

Olá Pessoal,
Estou criando um cursor que faz um update em uma tabela para atualizar uma Situação.
Existe algum modo de tunar esse update para ser mais rápido ?

Obrigado !

Segue o código para explicação.


DECLARE
CURSOR C1 IS
SELECT /*+ PARALLEL(4) */
NR_MATRICULA_UNIDADE,
DT_MES_ANO_REF
FROM MOVTO_SERVICO_FATURA_AUX
GROUP BY NR_MATRICULA_UNIDADE, DT_MES_ANO_REF
ORDER BY DT_MES_ANO_REF,NR_MATRICULA_UNIDADE;

TYPE TR1 IS TABLE OF C1%ROWTYPE INDEX BY PLS_INTEGER;
R1 TR1;

v_MAX_NR_DIFER NUMBER;

BEGIN

OPEN C1;
LOOP

FETCH C1 BULK COLLECT INTO R1 LIMIT 3000;
EXIT WHEN R1.COUNT = 0;
COMMIT;

FOR I IN 1..R1.COUNT LOOP
BEGIN

SELECT MAX(NR_DIFER_ORI)
INTO v_MAX_NR_DIFER
FROM MOVTO_SERVICO_FATURA_AUX
WHERE NR_MATRICULA_UNIDADE = R1(I).NR_MATRICULA_UNIDADE
AND DT_MES_ANO_REF = R1(I).DT_MES_ANO_REF;

UPDATE MOVTO_SERVICO_FATURA_AUX
SET ID_SITUACAO_MOVTO = 'F',
ID_INSERIR = 'S'
WHERE NR_MATRICULA_UNIDADE = R1(I).NR_MATRICULA_UNIDADE
AND DT_MES_ANO_REF = R1(I).DT_MES_ANO_REF
AND NR_DIFER_ORI = v_MAX_NR_DIFER;

EXCEPTION

WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000,'SQL ERROR: '|| SQLERRM ||' SQL CODE: '|| SQLCODE);
END;

END LOOP;

END LOOP;

COMMIT;
END;
/


[rmanParticipante]

@Andrei Rubino

Quantos registros serão atualizados?

Se não for absurdo, creio que um simples UPDATE sem envolver PL/SQL poderá ter mais performance.


UPDATE MOVTO_SERVICO_FATURA_AUX
SET ID_SITUACAO_MOVTO = 'F', ID_INSERIR = 'S'
WHERE (NR_MATRICULA_UNIDADE, DT_MES_ANO_REF, NR_DIFER_ORI) IN
(SELECT NR_MATRICULA_UNIDADE, DT_MES_ANO_REF, MAX(NR_DIFER_ORI)
FROM MOVTO_SERVICO_FATURA_AUX
GROUP BY NR_MATRICULA_UNIDADE, DT_MES_ANO_REF)


[Andrei RubinoParticipante]

@rman

Serão cerca de 20 milhões de registros.
Acredito que não será viável direto o UPDATE.Concorda ?

Obrigado !

[rmanParticipante]

@Andrei Rubino

Realmente com esses números não é viável fazer numa tacada só. Quando vi o GROUP BY no CURSOR achei estranho, creio que você deve utilizar a tabela pai, não a filha. Remova o ORDER BY também.

Creio que assim fica melhor:


DECLARE
CURSOR C1 IS
SELECT /*+ PARALLEL(4) */
NR_MATRICULA_UNIDADE, DT_MES_ANO_REF
FROM MOVTO_SERVICO_FATURA_AUX
GROUP BY NR_MATRICULA_UNIDADE, DT_MES_ANO_REF
ORDER BY DT_MES_ANO_REF, NR_MATRICULA_UNIDADE;

TYPE TR1 IS TABLE OF C1%ROWTYPE INDEX BY PLS_INTEGER;
R1 TR1;

v_MAX_NR_DIFER NUMBER;

BEGIN

OPEN C1;
LOOP

FETCH C1 BULK COLLECT
  INTO R1 LIMIT 3000;
EXIT WHEN R1.COUNT = 0;
COMMIT;

FOR I IN 1 .. R1.COUNT LOOP
  BEGIN

    UPDATE MOVTO_SERVICO_FATURA_AUX
       SET ID_SITUACAO_MOVTO = 'F', ID_INSERIR = 'S'
     WHERE (NR_MATRICULA_UNIDADE, DT_MES_ANO_REF, NR_DIFER_ORI) IN
           (SELECT NR_MATRICULA_UNIDADE,
                   DT_MES_ANO_REF,
                   MAX(NR_DIFER_ORI)
              FROM MOVTO_SERVICO_FATURA_AUX
             WHERE NR_MATRICULA_UNIDADE = R1(I)
            .NR_MATRICULA_UNIDADE
               AND DT_MES_ANO_REF = R1(I).DT_MES_ANO_REF
             GROUP BY NR_MATRICULA_UNIDADE, DT_MES_ANO_REF)

     EXCEPTION

     WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20000, 'SQL ERROR: ' || SQLERRM || ' SQL CODE: ' || SQLCODE);
  END;

END LOOP;

END LOOP;

COMMIT;
END;
/


[Andrei RubinoParticipante]

@RMAN

Obrigado, vou testar dessa maneira !

[rmanParticipante]

@Andrei Rubino

Agora que reparei, o COMMIT deve estar dentro do FOR, se não é a mesma coisa do que fazer numa tacada só. :blink:

[Fábio PradoParticipante]

As tabelas do cursor e update são as mesmas, portanto, não relacione-as através da PK, faça o relacionamento através do rowid, utilizando a cláusula CURRENT OF, como descrevo no artigo http://www.fabioprado.net/2011/05/plsql-mais-rapido-quando-usar-cursores.html.

O tempo irá melhorar monstruosamente!

[]s

Fábio Prado

[Andrei RubinoParticipante]

@RMAN

Me confirme se está certo meu conhecimento.
Eu tenho outro LOOP fora do FOR, então o R1.COUNT recebe 3000 sempre,
e quando o I do FOR chega ao valor de 3000 ele sai do FOR e vai para o
FETCH C1 BULK COLLECT INTO R1 LIMIT 3000 realizando o COMMIT a cada 3000 registros , não é ?

@fbifabio e @rman
Acredito que deveria ter dado mais informações no primeiro post.
os campos NR_MATRICULA_UNIDADE, DT_MES_ANO_REF não são uma PK.
A PK é composta por NR_MATRICULA_UNIDADE, DT_MES_ANO_REF,NR_DIFER_ORI e um Numero Sequencial.

Todos os registros possuem uma situação que é a ‘C’.
e a minha intenção é atualizar a situação para ‘F’
apenas para os registros com o maior NR_DIFER_ORI.

Então pelo rowid acredito que se tornaria mais lento o update, vistou que o update seria de registro em registro. Correto ?

Eu estava fazendo algo redundante tbm,
removi o SELECT MAX de dentro do loop e estou carregando ele direto no cursor,
assim deixando apenas o UPDATE dentro do loop.

Obrigado pelas dicas Senhores!!!

DECLARE
CURSOR C1 IS
SELECT /*+ PARALLEL(4) */
NR_MATRICULA_UNIDADE,
DT_MES_ANO_REF,
MAX(NR_DIFER_ORI) NR_DIFER_ORI
FROM MOVTO_SERVICO_FATURA_AUX
GROUP BY NR_MATRICULA_UNIDADE, DT_MES_ANO_REF,NR_DIFER_ORI
ORDER BY DT_MES_ANO_REF,NR_MATRICULA_UNIDADE;

TYPE TR1 IS TABLE OF C1%ROWTYPE INDEX BY PLS_INTEGER;
R1 TR1;

v_MAX_NR_DIFER NUMBER;

BEGIN

OPEN C1;
LOOP

    FETCH C1 BULK COLLECT INTO R1 LIMIT 3000;
    EXIT WHEN R1.COUNT = 0;
    COMMIT;

    FOR I IN 1..R1.COUNT LOOP
      BEGIN

     UPDATE MOVTO_SERVICO_FATURA_AUX
        SET ID_SITUACAO_MOVTO = 'F'
      WHERE NR_MATRICULA_UNIDADE = R1(I).NR_MATRICULA_UNIDADE
        AND DT_MES_ANO_REF       = R1(I).DT_MES_ANO_REF
        AND NR_DIFER_ORI     = R1(I).NR_DIFER_ORI;  

      EXCEPTION

      WHEN  OTHERS THEN
                 RAISE_APPLICATION_ERROR(-20000,'SQL ERROR: '|| SQLERRM ||' SQL CODE: '|| SQLCODE);
      END;

END LOOP;

END LOOP;

COMMIT;
END;
/


[rmanParticipante]

@Andrei Rubino

Realmente, analisando melhor a posição do COMMIT está ok, está commitando de 3000 em 3000.

Agora sim o GROUP BY no cursor faz sentido. 🙂

Remova o ORDER BY do cursor, neste caso a ordem não é importante, e a ordenação é um custo extra.

[Andrei RubinoParticipante]

Verdade, Obrigado rman !

[Fábio PradoParticipante]

Agora também entendi. A dica do @rman foi perfeita, vc não precisa do ORDER BY.

Outra coisa que vc poderia testar é tirar o hint PARALLEL. Vc esta recuperando apenas 3 mil linhas por vez e armazenando-as em uma collection. Se recuperar 3 mil linhas é uma operação rápida, paralelismo não irá te ajudar. Paralelismo é indicado em query longas. A sua query sem o LIMIT deve ser longa, mas como vc está recuperando pequenas quantidades por vez, vale a pena testar sem o hint, OK?

[]s

Fábio Prado

[Andrei RubinoParticipante]

Ok, vou testar também.
Mas já estou descartando esse update tenho que fazer de outra forma.
Deixei rodando cerca de 15h e ele não saiu do primeiro loop para efetuar algum commit.Está muito lento.

Estou testando um cursor com selects e sub selects com utl_file para gerar um arquivo de dados para ser importado os dados com a situação já correta.

Obrigado !

[rmanParticipante]

@Andrei

Opa, se a ideia é gerar um arquivo de dados, gere no formato do sql loader. Vai ser um tapa pra carregar os dados. :ohmy:

[Autor]

Posts