Olá, segui a recomendação de mais de uma pessoa e dexei de usar PL/SQL e
utilizei INSERT FROM SELECT com APPEND.
Ficou da forma abaixo. Voces acham que dropar e recriar os índices onde faço
INSERT pode ajudar ? Mas quando as tabelas estiverem muito grandes não vai
demorar para recriar os índices ?
O processo funciona assim: como tenho 32 arquivos texto (um para cada site
migrado), para cada um rodo o SQL*LAODER e o SCRIPT abaixo. Depois dropo as
tabelas temporárias (de carga e as outras duas). Além disso tenho quatro
tipos diferentes de aplicação que estão sendo migradas, ou seja, tenho 4
INSERTS diferentes, mas são análogos, só mudo um pouco a estrutura das
tabelas SERV* (tenho a SERV_PORTABILIDADE, SERV_PPECAP, SERV_CCTCTPE e
SERV_DESTBARR). No caso da portabilidade, que é a migração com mais volume
de dados estou levando 10 horas para migrar 77.286.592 registros. Será que é
razoável ? Alguém vê mais algum problema ?
Para quem perguntou a regra da tabela de LOG é assim: quando ja existe o
mesmo NTL, MAS alguns dos campos que testei no INSERT são diferentes (ai tem
coisa errada) ai então insiro na tabela de logs.
=======================================================================
analyze table CARGA_PORTABILIDADE compute statistics
/
INSERT /*+ append */ INTO TEMP_PORTAB_NOT_NTL
select SERV_PORTABILIDADE_SEQ.NEXTVAL, 0, CP.DATA_CRIACAO,
CP.ULTIMA_ATUALIZACAO, CP.ULTIMA_OPERACAO,
CP.NTL, CP.OPC, CP.rn1, CP.cnl, CP.NUF, CP.TPB, CP.CPT, CP.EIP,
CP.NUE, CP.TBR, CP.CRE, CP.SIGLA_ER, CP.OBJ_SAIDA
FROM CARGA_PORTABILIDADE CP
WHERE NOT EXISTS (SELECT SP.NTL
FROM SERV_PORTABILIDADE SP, CARGA_PORTABILIDADE CP
WHERE CP.NTL = SP.NTL)
/
commit;
analyze table TEMP_PORTAB_NOT_NTL compute statistics
/
INSERT /*+ append */ INTO TEMP_PORTAB_EXIST_NTL
select SP.ID, SP.EXCLUIDO, SP.DATA_CRIACAO, SP.ULTIMA_ATUALIZACAO,
SP.ULTIMA_OPERACAO,
SP.NTL, SP.OPC, SP.rn1, SP.cnl, SP.NUF, SP.TPB, SP.CPT, SP.EIP,
SP.NUE, SP.TBR, SP.CRE,
CP.NTL, CP.OPC, CP.rn1, CP.cnl, CP.NUF, CP.TPB, CP.CPT, CP.EIP,
CP.NUE, CP.TBR, CP.CRE,
CP.SIGLA_ER, CP.OBJ_SAIDA
FROM CARGA_PORTABILIDADE CP, SERV_PORTABILIDADE SP
WHERE CP.NTL = SP.NTL
/
commit;
analyze table TEMP_PORTAB_EXIST_NTL compute statistics
/
INSERT /*+ APPEND */ INTO SERV_PORTABILIDADE
SELECT TPN.ID, TPN.EXCLUIDO, TPN.DATA_CRIACAO, TPN.ULTIMA_ATUALIZACAO,
TPN.ULTIMA_OPERACAO,
TPN.NTL, TPN.OPC, TPN.rn1, TPN.CNL, TPN.NUF, TPN.TPB, TPN.CPT,
TPN.EIP, TPN.NUE, TPN.TBR, TPN.CRE
FROM TEMP_PORTAB_NOT_NTL TPN
/
commit;
analyze table SERV_PORTABILIDADE compute statistics
/
INSERT /*+ APPEND */ INTO SERV_PORTABILIDADE_CRIADO
SELECT TPN.ID, TPN.SIGLA_ER, TPN.OBJ_SAIDA, TPN.DATA_CRIACAO,
TPN.ULTIMA_ATUALIZACAO, TPN.ULTIMA_OPERACAO
FROM TEMP_PORTAB_NOT_NTL TPN
/
commit;
analyze table SERV_PORTABILIDADE_CRIADO compute statistics
/
INSERT /*+ append */ INTO SERV_PORTABILIDADE_CRIADO
SELECT ID, SIGLA_ER_CP, OBJ_SAIDA_CP, DATA_CRIACAO, ULTIMA_ATUALIZACAO,
ULTIMA_OPERACAO
FROM TEMP_PORTAB_EXIST_NTL
/
commit;
analyze table SERV_PORTABILIDADE_CRIADO compute statistics
/
INSERT /*+ append */ INTO SERV_PORTABILIDADE_LOG
SELECT ID, EXCLUIDO, DATA_CRIACAO, ULTIMA_ATUALIZACAO, ULTIMA_OPERACAO,
NTL_SP, OPC_SP, RN1_SP, CNL_SP, NUF_SP, TPB_SP, CPT_SP, EIP_SP,
NUE_SP, TBR_SP, CRE_SP,
NTL_CP, OPC_CP, RN1_CP, CNL_CP, NUF_CP, TPB_CP, CPT_CP, EIP_CP,
NUE_CP, TBR_CP, CRE_CP,
SIGLA_ER_CP, OBJ_SAIDA_CP
FROM TEMP_PORTAB_EXIST_NTL
WHERE ((OPC_SP <> OPC_CP OR (OPC_SP IS NULL AND OPC_CP IS NOT NULL) OR
(OPC_SP IS NOT NULL AND OPC_CP IS NULL)) OR
(RN1_SP <> RN1_CP OR (RN1_SP IS NULL AND RN1_CP IS NOT NULL) OR
(RN1_SP IS NOT NULL AND RN1_CP IS NULL)) OR
(CNL_SP <> CNL_CP OR (CNL_SP IS NULL AND CNL_CP IS NOT NULL) OR
(CNL_SP IS NOT NULL AND CNL_CP IS NULL)) OR
(NUF_SP <> NUF_CP OR (NUF_SP IS NULL AND NUF_CP IS NOT NULL) OR
(NUF_SP IS NOT NULL AND NUF_CP IS NULL)) OR
(TPB_SP <> TPB_CP OR (TPB_SP IS NULL AND TPB_CP IS NOT NULL) OR
(TPB_SP IS NOT NULL AND TPB_CP IS NULL)) OR
(CPT_SP <> CPT_CP OR (CPT_SP IS NULL AND CPT_CP IS NOT NULL) OR
(CPT_SP IS NOT NULL AND CPT_CP IS NULL)) OR
(EIP_SP <> EIP_CP OR (EIP_SP IS NULL AND EIP_CP IS NOT NULL) OR
(EIP_SP IS NOT NULL AND EIP_CP IS NULL)) OR
(NUE_SP <> NUE_CP OR (NUE_SP IS NULL AND NUE_CP IS NOT NULL) OR
(NUE_SP IS NOT NULL AND NUE_CP IS NULL)) OR
(TBR_SP <> TBR_CP OR (TBR_SP IS NULL AND TBR_CP IS NOT NULL) OR
(TBR_SP IS NOT NULL AND TBR_CP IS NULL)) OR
(CRE_SP <> CRE_CP OR (CRE_SP IS NULL AND CRE_CP IS NOT NULL) OR
(CRE_SP IS NOT NULL AND CRE_CP IS NULL)))
/
commit;
analyze table SERV_PORTABILIDADE_LOG compute statistics
/
TRUNCATE TABLE TEMP_PORTAB_EXIST_NTL
/
TRUNCATE TABLE TEMP_PORTAB_NOT_NTL
/
TRUNCATE TABLE CARGA_PORTABILIDADE
/
Em 28 de março de 2010 15:57, Andre Santos <[email protected]>escreveu:
> José Luís
>
> A primeira coisa que eu faria é NÃO coletar estatísticas de 5 em 5 MINUTOS.
> Isso vai onerar desnecessariamente o servidor (ainda mais que é para todas
> as tabelas do SCHEMA...).
> A coleta de estatísticas é muito importante, mas deve ser feita nos
> momentos
> certos, quando for realmente necessária e/ou conveniente.
>
> Outro detalhe sobre isso é que, no **único SELECT**, estão sendo usados
> HINTS...
> Então uma coisa contradiz a outra: se os hints são importantes nesse caso,
> não é necessária a preocupação com as estatísticas.
> Porém, normalmente, é o contrário: se o SGBD está bem configurado em com
> estatísticas em ordem, o otimizador já gera o melhor plano de execução e
> não precisamos utilizar os "hints".
>
> Mais duas observações:
> - o COMMIT após o LOOP mais interno não é uma boa prática no Oracle. Às
> vezes é necessário para contornar um problema de área de rollback (undo)
> pequena... Mas o desejável (se a capacidade do servidor permitir), é fazer
> o
> COMMIT só no final da transação.
> - o tratamento de erro com WHEN OTHERS THEN NULL, em geral, também não é
> uma boa prática (pode ocultar alguma problema significativo).
> _____
>
> Sobre a tarefa, confirme se eu entendi direito:
>
> 1. Inicia com as tabelas de destino vazias (truncadas).
> 2. Consulta uma tabela de destino (SERV_PORTABILIDADE_TMP), para ver se já
> existe o valor de NTL a ser carregado.
> 3.a. Se não existir, então insere nas tabelas de destino
> (SERV_PORTABILIDADE_TMP e SERV_PORTABILIDADE_CRIADO_TMP).
> 3.b1. Se já existir, então insere apenas na tabela
> SERV_PORTABILIDADE_CRIADO_TMP, porém com o "identificador" definido na
> tabela de destino SERV_PORTABILIDADE_TMP.
> 3.b2. Se algumas colunas tiverem valores diferentes, então também insere
> numa tabela de log (SERV_PORTABILIDADE_LOG).
>
> Fiquei com algumas dúvidas sobre as regras de negócio... Qual o critério
> para ser inserido só na SERV_PORTABILIDADE_TMP (já que não há um ORDER BY
> para determinar uma ordem de prioridade)? E o motivo de inserir na tabela
> de
> "log" se todos os registros já estão na SERV_PORTABILIDADE_CRIADO_TMP?
> _____
>
> De qualquer forma, eu tentaria um processo diferente.
> Muitas vezes, uma solução baseada em SQL pode ter resultados melhores que
> uma rotina procedural (PL/SQL)...
> Faria uns testes com INSERT's em modo APPEND ("direct-path load"), que são
> muito rápidos.
>
> -- Preparação
> 1. Coleta de estatísticas para a tabela de origem: CARGA_PORTABILIDADE.
> 2. Truncate nas tabelas de destino: SERV_PORTABILIDADE_TMP e
> SERV_PORTABILIDADE_CRIADO_TMP.
>
> -- Alimentar a tabela de destino SERV_PORTABILIDADE_TMP,
> -- que terá o identificador (ID) definido pela sequence
> SERV_PORTABILIDADE_SEQ_TMP,
> -- através de INSERT em modo APPEND.
> -- OBS.1: Para isso, seria importante definir um critério para "unicidade"
> na seleção (ou prioridade de carga).
> -- OBS.2: Para funcionamento do hint APPEND, a tabela de destino deve estar
> sem índices habilitados (e triggers também).
> 3. INSERT /*+APPEND */ INTO serv_portabilidade_tmp SELECT ... FROM
> carga_portabilidade WHERE ...;
>
> 4. Recriar os índices para a tabela de destino recém-alimentada
> SERV_PORTABILIDADE_TMP (aqui já há um COMMIT implícito).
> 5. Coletar as estatísticas para esta tabela e seus índices.
>
> -- Alimentar a segunda tabela de destino SERV_PORTABILIDADE_CRIADO_TMP,
> também com um INSERT-APPEND,
> -- fazendo um join (por NTL) de CARGA_PORTABILIDADE e
> SERV_PORTABILIDADE_TMP
> (esta apenas para obter o tal identificador).
> -- OBS.: O join provavelmente funcionaria bem com um índice UNIQUE em
> SERV_PORTABILIDADE_TMP, por ID e NTL.
> 6. INSERT /*+APPEND */ INTO serv_portabilidade_criado_tmp (ID, ...) SELECT
> s.ID, c... FROM carga_portabilidade c,
> serv_portabilidade_tmp s WHERE s.NTL = c.NTL;
>
> 7. Recriar os índices para a tabela de destino recém-alimentada
> SERV_PORTABILIDADE_CRIADO_TMP.
> 8. Coletar as estatísticas para esta tabela e seus índices.
>
> -- Alimentar a tabela de log (sempre com INSERT-APPEND), talvez com um
> SELECT DISTINCT da SERV_PORTABILIDADE_CRIADO_TMP (ou algo que faça mais
> sentido, conforme a lógica da aplicação...).
> 9. INSERT /*+APPEND */ INTO serv_portabilidade_log SELECT DISTINCT ... FROM
> serv_portabilidade_criado_tmp;
>
> 10. Se a tabela de log tiver índices, recriá-los.
> 11. Coletar as estatísticas da tabela de log (provavelmente com estimativa
> em 10%)
> _____
>
> É isso! Espero que as idéias ajudem.
>
> [ ]
>
> André
>
>
> Em 19 de março de 2010 12:41, Jose Luis Ramos
> <[email protected]>escreveu:
>
> >
> >
> > Pessoal, preciso de uma ajuda em um PL/SQL que, segundo meus cálculosvai
> > demorar 3 dias para rodar. É inviável. O processo é uma migração de dados
> > entre dois sistemas. Eu carrego primeiramente a tabela
> CARGA_PORTABILIDADE
> > (através do SQL*LOADER) e depois em cima desta tabela eu executo um
> PL/SQL
> > para preencher duas outras tabelas pertencentes ao sistema para o qual os
> > dados estão sendo migrados (SERV_PORTABILIDADE_TMP E
> > SERV_PORTABILIDADE_CRIADO_TMP). Ja fiz alguns ajustes que melhorarm um
> > pouco, mas não muito. Alterei os seguintes parâmetros:
> >
> > CUROR_SHARING=SIMILAR
> > OPTIMIZER_INDEX_CACHING=70
> > OPTIMIZER_INDEX_COST_ADJ=30
> > PGA_AGGREGATE_TARGET=5g
> > OPTIMIZER_MODE=ALL_ROWSS (esta o default)
> > CURSOR_SPACE_FOR_TIME=TRUE
> > SESSION_CACHED_CURSOR=500
> >
> > além disse coloquei os índices em tablespace separada das tabelas e criei
> > um
> > JOB que recalcula as estatísticas para as tabelas deste SCHEMA para rodar
> > de
> > 5 em 5 minutos. O Servisor é um HP que tem 60GB de RAM , dois
> processadores
> > dual core e 4 discos de 300GB em RAID 0+1.
> >
> > O código do PL/SQL qu executo está abaixo. O SQL*LOADER não é o gargalo.
> > Roda em alguns minutos. O total de registros que devem ser processados
> pelo
> > PL/SQL é de 4milhões de linhas. Se alguém puder me dar alguma dica eu
> > agradeço muito. Abraços.
> >
> > CREATE OR REPLACE procedure PROC_PORTABILIDADE
> > is
> > CURSOR C_CARGA IS
> > SELECT * FROM CARGA_PORTABILIDADE;
> > CURSOR C_PORT IS
> > SELECT * FROM SERV_PORTABILIDADE_TMP;
> > v_count NUMBER;
> > sql1 varchar2(255) := 11;
> > REG_CARGA C_CARGA%ROWTYPE;
> > REG_PORT C_PORT%ROWTYPE;
> > t1 varchar2(1600) := '';
> > t2 varchar2(1600) := '';
> > BEGIN
> > sql1 := 'TRUNCATE TABLE SERV_PORTABILIDADE_TMP';
> > execute immediate sql1;
> > sql1:= 'TRUNCATE TABLE SERV_PORTABILIDADE_CRIADO_TMP';
> > execute immediate sql1;
> > OPEN C_CARGA;
> > LOOP
> > FETCH C_CARGA INTO REG_CARGA;
> > EXIT WHEN C_CARGA%NOTFOUND;
> > OPEN C_PORT;
> > LOOP
> > FETCH C_PORT INTO REG_PORT;
> > V_COUNT := 0;
> > SELECT /*+ index(carga_portabilidade IDX_NTL_CARGA_PORTAB_TMP)
> > index(SERV_PORTABILIDADE_TMP IDX_NTL_SERV_PORTAB_TMP */
> > COUNT(1) INTO V_COUNT FROM SERV_PORTABILIDADE_TMP
> > WHERE REG_CARGA.NTL = SERV_PORTABILIDADE_TMP.NTL;
> > IF (REG_CARGA.NTL IS NOT NULL) AND (V_COUNT IS NULL OR V_COUNT = 0)
> > THEN
> > INSERT INTO SERV_PORTABILIDADE_TMP
> > VALUES(SERV_PORTABILIDADE_SEQ_TMP.NEXTVAL, 0,
> > REG_CARGA.DATA_CRIACAO, REG_CARGA.ULTIMA_ATUALIZACAO,
> > REG_CARGA.ULTIMA_OPERACAO,
> > REG_CARGA.NTL, REG_CARGA.OPC, REG_CARGA.rn1,
> > REG_CARGA.cnl, REG_CARGA.NUF, REG_CARGA.TPB, REG_CARGA.CPT,
> REG_CARGA.EIP,
> > REG_CARGA.NUE, REG_CARGA.TBR, REG_CARGA.CRE);
> > INSERT INTO SERV_PORTABILIDADE_CRIADO_TMP
> > VALUES(SERV_PORTABILIDADE_SEQ_TMP.CURRVAL, REG_CARGA.SIGLA_ER,
> > REG_CARGA.OBJ_SAIDA, REG_CARGA.DATA_CRIACAO,
> > REG_CARGA.ULTIMA_ATUALIZACAO,
> > REG_CARGA.ULTIMA_OPERACAO);
> > ELSE IF REG_CARGA.NTL = REG_PORT.NTL THEN
> > INSERT INTO SERV_PORTABILIDADE_CRIADO_TMP
> > VALUES(REG_PORT.ID <http://reg_port.id/>, REG_CARGA.SIGLA_ER,
> > REG_CARGA.OBJ_SAIDA,
> > REG_PORT.DATA_CRIACAO, REG_PORT.ULTIMA_ATUALIZACAO,
> > REG_PORT.ULTIMA_OPERACAO);
> > t1 := REG_CARGA.Opc|| REG_CARGA.Rn1|| REG_CARGA.Cnl||
> > REG_CARGA.Nuf|| REG_CARGA.Tpb|| REG_CARGA.Cpt|| REG_CARGA.Eip||
> > REG_CARGA.Nue|| REG_CARG
> > A.TBR|| REG_CARGA.Cre;
> > t2 := REG_PORT.Opc || REG_PORT.Rn1 || REG_PORT.Cnl ||
> > REG_PORT.Nuf || REG_PORT.Tpb || REG_PORT.Cpt || REG_PORT.Eip||
> > REG_PORT.Nue|| REG_PORT.T
> > BR|| REG_PORT.Cre;
> > IF t1 <> t2 then
> > INSERT INTO SERV_PORTABILIDADE_LOG
> > VALUES(REG_PORT.ID <http://reg_port.id/>, REG_CARGA.NTL, REG_CARGA.OPC,
> > REG_CARGA.rn1, REG_CARGA.cnl, REG_CARGA.NUF, REG_CARGA.TPB,
> REG_CARGA.CPT,
> > REG_CARGA.EIP, REG_CARGA.NUE, REG_CARGA.TBR,
> > REG_CARGA.CRE, REG_CARGA.SIGLA_ER, REG_CARGA.OBJ_SAIDA);
> > END IF;
> > END IF;
> > END IF;
> > EXIT WHEN C_PORT%NOTFOUND;
> > END LOOP;
> > COMMIT;
> > CLOSE C_PORT;
> > END LOOP;
> > CLOSE C_CARGA;
> > EXCEPTION
> > WHEN DUP_VAL_ON_INDEX THEN
> > NULL;
> > WHEN OTHERS THEN
> > NULL;
> > END;
> > /
> >
> > --
> > Jose Luis Ramos Jr
> > Campinas - SP
> > Oracle OCP DBA 8i, 9i,10g
> > Fone: 19-37056793
> >
> > [As partes desta mensagem que não continham texto foram removidas]
> >
> >
> >
>
>
> [As partes desta mensagem que não continham texto foram removidas]
>
>
>
> ------------------------------------
>
>
> --------------------------------------------------------------------------------------------------------------------------
> >Atenção! As mensagens do grupo ORACLE_BR são de acesso público e de
> inteira responsabilidade de seus remetentes.
> Acesse: http://www.mail-archive.com/[email protected]/
>
> --------------------------------------------------------------------------------------------------------------------------
> >Apostilas » Dicas e Exemplos » Função » Mundo Oracle » Package » Procedure
> » Scripts » Tutoriais - O GRUPO ORACLE_BR TEM SEU PROPRIO ESPAÇO! VISITE:
> http://www.oraclebr.com.br/
> ------------------------------------------------------------------------------------------------------------------------
> Links do Yahoo! Grupos
>
>
>
--
Jose Luis Ramos Jr
Campinas - SP
Oracle OCP DBA 8i, 9i,10g
Fone: 19-37056793
[As partes desta mensagem que não continham texto foram removidas]
------------------------------------
--------------------------------------------------------------------------------------------------------------------------
>Atenção! As mensagens do grupo ORACLE_BR são de acesso público e de inteira
>responsabilidade de seus remetentes.
Acesse: http://www.mail-archive.com/[email protected]/
--------------------------------------------------------------------------------------------------------------------------
>Apostilas » Dicas e Exemplos » Função » Mundo Oracle » Package » Procedure »
>Scripts » Tutoriais - O GRUPO ORACLE_BR TEM SEU PROPRIO ESPAÇO! VISITE:
>http://www.oraclebr.com.br/
------------------------------------------------------------------------------------------------------------------------
Links do Yahoo! Grupos
<*> Para visitar o site do seu grupo na web, acesse:
http://br.groups.yahoo.com/group/oracle_br/
<*> Para sair deste grupo, envie um e-mail para:
[email protected]
<*> O uso que você faz do Yahoo! Grupos está sujeito aos:
http://br.yahoo.com/info/utos.html
