José Luís Na correria, não consegui ver direito o script do seu novo processo...
Mas tenho dois comentários: - Sobre recriar os índices: certamente, em atualizações *muito grandes*, é mais rápido "recriar os índices" depois (do que deixar que eles sejam atualizados durante o processo). Obs.: Para isso, talvez haja comandos melhores do que a dupla "drop index/create index" acho que vale a pena pesquisar algumas opções de "alter index". ** IMPORTANTE **: Isso já é verdade no caso de um INSERT-SELECT comum. No caso de um INSERT-APPEND, se não me engano, é "pré-requisito" não ter índices habilitados e nem triggers ativos. - Dê uma olhada na package DBMS_STATS. É o metodo recomendado, atualmente, para coletar estatísticas (ao invés do ANALYZE... este ainda funciona, mas a package tem vantagens e é mais compatível com as necessidades do otimizador nas versões mais recentes). Boa sorte! [ ] André Em 29 de março de 2010 12:24, Jose Luis Ramos <[email protected]>escreveu: > 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 > > > [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
