OK, se com a 2ª opção (cursor, imagino que com external table e bulk/forall) já te satisfez ok, ficamos contentes de ter ajudado... Para complemento de conhecimento, para uma próxima vez, se vc julgar conveniente, manda um case com os CREATEs (com CONSTRAINTS inclusive), uns INSERTs pra popular as tabelas, o texto do MERGE e as msgs de erro, que a gente pode palpitar em cima...
[]s Chiappa --- Em oracle_br@yahoogrupos.com.br, Alexandre Brum <alexandre_b...@...> escreveu > > Obrigado Chiappa e Wilson > > Tentei usar o merge. Aconteceram alguns erros. Então criei um índice na tabela temporária A e fiz dois cursores. Um para update e outro para inserts. O primeiro usando exists para pegar apenas os registros que estão na tabela A e B e o segundo com a opção not exists, para pegar apenas os registros q estão na tabela A. Desta forma o tempo de execução da procedure reduziu de 2h para 3min. > > Muito obrigado. > > > Fique com Deus. > Um grande abraço. > > Att. > Alexandre Brum > > --- Em sex, 20/2/09, jlchiappa <jlchia...@...> escreveu: > > De: jlchiappa <jlchia...@...> > Assunto: Re: Res: [oracle_br] Tuning de Aplicação > Para: oracle_br@yahoogrupos.com.br > Data: Sexta-feira, 20 de Fevereiro de 2009, 12:21 > > > > > > > Alexandre, seguem algumas obs gerais , vamos ver se te ajudo : > > 1) a regra de ouro para manipulações de dados, principalmente em > grandes volumes, cargas, etc, é SIMPLES : tente AO MÁXIMO fazer tudo > num único SQL, usando os recursos todos do SQL, apenas SE realmente > não conseguir aí sim tente um cursor PL/SQL, se ainda assim não > conseguir aí sim ir pra C ou java. Isso visando principalmente à > performance, já que (como já falado em msgs anteriores, blablabla) há > a questão de context switch, o CBO é *** COMPLETAMENTE *** incapaz de > otimizar SQLs separados (como o SELECT num cirsor e os SQLs dentro > dele), etc, etc, etc... No seu caso vc quer fazer um UPSERT (UPdate se > existe, inSERT se não), já existe ** SIM ** no 9i um comando SQL > nativo pra isso, é , como outros colegas já indicaram, o MERGE, > consulte o manual "SQL Reference" do 9i que ele EXISTE SIM no 9i. > Obviamente, o MERGE trabalha com tabelas, então não sei se ficou claro > mas pra isso vc teria que usar o EXTERNAL TABLE, aí o teu > arquivo-texto funcionaria como se fosse uma tabela, podendo ser lida > via SELECT, participar de um MERGE, de um JOIN, normal.... > O problema pra isso na 9i é que lá vc não tem o recurso de DML Error > Logging 9que este sim só veio no 10g), então se o merge 9i em qualquer > linha falhar (veio um número errado no arquivo-texto, uma data, enfim, > qquer problema causador de exception) no 9i o merge falha > integralmente e vc não tem um log das falhas, só a mensagem de erro... > Outra coisa é que o MERGE *** exige *** constraint de PK/UK presente, > e ** proíbe ** UPDATEs na chave... > Então a coisa é , SE isso é contornável/aceitá vel OK, vá de MERGE num > SQL só que deve ser mais performático, senão continue nas opções abaixo. > > 2) se vc não puder usar o MERGE por causa das limitações dele no 9i, > voltando um pouco a recomendação de usar SQLs para processar, outra > opção poderia ser (** sempre ** tendo o arquivo-texto como external > table) , E SE os INSERTs forem maioria esmagadora, fazer : > > a. inserir quem tá na tabela-external / arq.texto e não existe na > tab-destino com : > > INSERT /*+ APPEND */ INTO tabelasdestino (SELECT ... FROM > tabeladestino a, externalapontandopr oarqtexto b > WHERE a.chavedatabdestino = > b.campodatabelaexto rigem (+) > AND b.campodatabelaexto rigem is null); > > b. botar numa GTT para localizar rapidamente quem será UPDATE : > > INSERT /*+ APPEND */ INTO GTT (SELECT ... FROM tabeladestino a, > externalapontandopr oarqtexto b > WHERE a.chavedatabdestino = > b.campodatabelaexto rigem (+) > AND b.campodatabelaexto rigem is null); > > c. cursor (*** OBVIAMENTE *** com BULK COLLECT, FORALL, etc, etc) > lendo as linhas da GTT e fazendo os UPDATEs, com COMMIT só no final, etc. > > 3) já falamos algumas vezes, mas vale o reforço : ao usar o hint de > NOLOGGING num UPDATE sabe o que vc obtém ?? Pontas dos dedos mais > gastas do que o necessário, pois vc acabou de digitar algo IMPOSSÌVEL, > o banco só pode mesmo ignorar o que vc indicou... A questão é que o > NOLOGGING funciona introduzindo dados em blocos vazios, situados além > do limite dos blocos usados (daí o APPEND do hint), e *** OBVIAMENTE > *** um UPDATE vai mexer numa linha dum bloco que JÁ EXISTE, um bloco > potencialmente "cheio", já com outras linhas, NÂO TEM COMO fazer > append-mode com UPDATE por causa disso... > > 4) pro INSERT ... VALUES (valor1, valor2...) que vc usa vc não obterá > *** COISA ALGUMA ***, nada, niente, nill, null com o HINT de APPEND, > nem com as tabelas em modo de nolog : como já dito anteriormente, o > APPEND / NOLOG funciona se vc está inserindo múltiplas linhas, que > podem portanto ser dirigidas para blocos virgens/vazios acima da lista > de blocos usados - EVIDENTEMENTE um INSERT... VALUES está inserindo UM > REGISTRO APENAS, ele NÃO VAI movimentar múltiplos blocos (por > definição um registro SEMPRE está alocado prum bloco só, falando de > tabelas heap comuns), então não tem como se fazer APPEND-mode. ... O > fato é, tanto pro teu UPDATE (por definição) quanto pro teu INSERT > (pois ele operam em uma linha só, ao invés de fazer INSERT ... (SELECT > multiplaslinhas) vale aí o bordão de um vilão da minha infância ; > ZEEEROOOO!!! Blz ? > > 5) finalmente, se por qquer motivo não der pra fazer nada do acima, ao > menos altere a sua rotina para NÂO ficar lendo e processamento uma > linha por vez do arquivo-texto (a external table seria ótima pra isso) > e processe com BULK COLLECT e FORALL o cursor resultante. > > []s > > Chiappa > > --- Em oracle...@yahoogrup os.com.br, Alexandre Brum > <alexandre_brum@ ...> escreveu > > > > Bom dia Wilson > > > > O Oracle que estou usando é 9i. Acredito que o comando MERGE está > disponível a partir da 10g. Estranhamente esta procedure gastou apenas > 7 min para rodar no meu micro, mas no servidor está gastando quase 2h. > > > > Configuração do meu micro: > > > > Intel Core(TM)2 Duo > > E4600 2.40 GHz > > 1.20 GHz, 1,98 GB de Ram > > > > > > Configuração do Servidor: > > > > > > HP DL 580 > > 4 Processadores Intel Xeon 2.8 GHz > > 4 HD´s 146 GB > > 4 GB de RAM > > Conexão com um Storage e 100 GB de Volume. > > > > Obrigado. > > > > > > Fique com Deus. > > Um grande abraço. > > > > Att. > > Alexandre Brum > > > > --- Em qui, 19/2/09, Wilson Teixeira <wilsonteixeira2000 @...> escreveu: > > > > De: Wilson Teixeira <wilsonteixeira2000 @...> > > Assunto: Res: [oracle_br] Tuning de Aplicação > > Para: oracle...@yahoogrup os.com.br > > Data: Quinta-feira, 19 de Fevereiro de 2009, 16:13 > > > > > > > > > > > > > > Alexandre, > > > > você pode tentar realizar esta operacao utilizando o comando MERGE. > > > > ____________ _________ _________ __ > > De: Alexandre Brum <alexandre_brum@ yahoo.com. br> > > Para: oracle...@yahoogrup os.com.br > > Enviadas: Quinta-feira, 19 de Fevereiro de 2009 11:45:51 > > Assunto: [oracle_br] Tuning de Aplicação > > > > Prezados (as) > > Tenho uma procedure que atualiza dados de uma tabela "A" para a > tabela "B". A tabela "A" não possui nenhum índice. > > Foi carregada através do loader. A procedure procura primeiro > inserir o registro da tabela "A" na tabela "B", caso o > > registro exista na tabela "B", então ocorre uma excecption e através > do WHEN DUP_VAL_ON_INDEX a procecure executa um > > Update na tabela "B" com os dados da tabela "A". > > Esse processo está demorando em média 2h. Estou procurando uma > forma de reduzir esse tempo. Se alguém tiver uma sugestão, > > agradeço antecipadamente. Alguns dados importantes sobre esse cenário: > > Tabela "A": 1.343.656 de registros; > > Não possui índices; > > > > Tabela "B": 1.953.678 de registros; > > Índices: > > PK_PRAZO_ACC - Coluna ID_PRAZO_ACC > > UK_PRAZO_ACC - Coluna ID_LINHA_ORDEM_ COMPRA > > > > Na cláusula WHERE do Update o índice UK_PRAZO_ACC > está sendo utilizado; > > No comando Insert está sendo utilizado: > > /*+ APPEND */ > > NOLOGGING > > > > No comando Update está sendo utilizado: > > NOLOGGING > > > > Microsoft Windows Server 2003 > > Service Pack for Microsoft Windows Server 2003 2 > > Oracle9i Database Enterprise/Standard Edition for Windows 9.2.0. 1 > > Patch Upgrade for Windows Server 2003 (32-bit) - requires 9.2.01 > installation OUI220180 > > Patch Set # 9203WIN2K3 > > Patch # p3095277_9204_ WINNT > > > > Abaixo segue a procedure na íntegra: > > > > CREATE OR REPLACE PROCEDURE ECARGA.AI_PRAZO_ ACC IS > > -- > > CURSOR C_TAB IS > > SELECT /*+ FIRST_ROWS(100000) */ > > Prazo_Entrega > > ,MAX(Dt_Criacao) AS Dt_Criacao > > ,Dt_Atualizacao > > ,NR_Contrato > > ,NR_Ordem_Compra > > ,ID_Ordem_Compra > > ,ID_Linha_Ordem_ compra > > ,ID_Organizacao > > ,NR_LINHA_ORDEM_ COMPRA > > FROM A_PRAZO_ACC > > GROUP BY > > Prazo_Entrega > > ,Dt_Atualizacao > > ,NR_Contrato > > ,NR_Ordem_Compra > > ,ID_Ordem_Compra > > ,ID_Linha_Ordem_ compra > > ,ID_Organizacao > > ,NR_LINHA_ORDEM_ COMPRA > > ; > > -- > > V_CONT NUMBER := 0; -- CONTADOR GERAL DE REGISTROS > > -- > > TYPE FILE_TYPE IS RECORD ( ID BINARY_INTEGER) ; > > -- > > ARQ UTL_FILE.FILE_ TYPE; -- ARQUIVO QUE LOGA OS ERROS > > -- > > V_LOCAL_ERRO VARCHAR2(6); > > -- > > V_DADOS_ERRO VARCHAR2(4000) ; > > -- > > procedure log_erro(P_LOCAL_ ERRO varchar2, P_DADOS_ERRO varchar2) is > > -- > > ARQ1 UTL_FILE.FILE_ TYPE; -- ARQUIVO QUE LOGA OS ERROS > > -- > > begin > > -- > > IF NOT UTL_FILE.IS_ OPEN(ARQ1) THEN > > -- > > ARQ1 := UTL_FILE.FOPEN( 'C:\DADOS\ ARQUIVOS_ GERADOS_DO_ > ORACLE\CARGA\ ERRO_PROC' , 'AI_PRAZO_ACC. TXT','W', 5000); > > -- > > END IF; > > -- > > UTL_FILE.PUT_ LINE(ARQ1, P_LOCAL_ERRO| |' '||P_DADOS_ERRO| |' > '||SQLERRM); > > -- > > end; > > -- > > BEGIN > > -- > > FOR R_TAB IN C_TAB LOOP > > -- > > V_DADOS_ERRO := TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') > ||'ID_Linha_ Ordem_compra= ' || R_TAB.ID_Linha_ Ordem_compra; > > -- > > BEGIN > > -- > > V_LOCAL_ERRO := 'INSERT'; > > -- > > INSERT /*+ APPEND */ INTO PRAZO_ACC NOLOGGING > > (ID_PRAZO_ACC > > ,Prazo_Entrega > > ,Dt_Criacao > > ,Dt_Atualizacao > > ,NR_Contrato > > ,NR_Ordem_Compra > > ,ID_Ordem_Compra > > ,ID_Linha_Ordem_ compra > > ,ID_Organizacao > > ,NR_linha_Ordem_ Compra > > ) > > VALUES > > (SEQ_PRAZO_ACC. NEXTVAL > > ,R_TAB.Prazo_ Entrega > > ,R_TAB.Dt_Criacao > > ,R_TAB.Dt_Atualizac ao > > ,R_TAB.NR_Contrato > > ,R_TAB.NR_Ordem_ Compra > > ,R_TAB.ID_Ordem_ Compra > > ,R_TAB.ID_Linha_ Ordem_compra > > ,R_TAB.ID_Organizac ao > > ,R_TAB.NR_linha_ Ordem_Compra > > ); > > -- > > V_CONT := V_CONT + 1; > > -- > > IF V_CONT = 100000 THEN > > -- > > V_CONT := 0; > > -- > > COMMIT; > > -- > > END IF; > > -- > > EXCEPTION > > -- > > WHEN DUP_VAL_ON_INDEX THEN > > -- > > BEGIN > > -- > > V_LOCAL_ERRO := 'UPDATE'; > > -- > > UPDATE PRAZO_ACC NOLOGGING > > SET Prazo_Entrega = R_TAB.Prazo_ Entrega > > , Dt_Criacao = R_TAB.Dt_Criacao > > , Dt_Atualizacao = R_TAB.Dt_Atualizaca o > > , NR_Contrato = R_TAB.NR_Contrato > > , NR_Ordem_Compra = R_TAB.NR_Ordem_ Compra > > , ID_Ordem_Compra = R_TAB.ID_Ordem_ Compra > > , ID_Linha_Ordem_ compra = R_TAB.ID_Linha_ > Ordem_compra > > , ID_Organizacao = R_TAB.ID_Organizaca o > > , NR_linha_Ordem_ Compra = R_TAB.NR_linha_ > Ordem_Compra > > WHERE ID_Linha_Ordem_ compra = R_TAB.ID_Linha_ > Ordem_compra; > > -- > > if sql%notfound then > > log_erro(V_LOCAL_ ERRO, V_DADOS_ERRO| |' '||SQLERRM); > > end if; > > -- > > V_CONT := V_CONT + 1; > > -- > > IF V_CONT = 100000 THEN > > -- > > V_CONT := 0; > > -- > > COMMIT; > > -- > > END IF; > > -- > > EXCEPTION > > -- > > WHEN OTHERS THEN > > -- > > IF NOT UTL_FILE.IS_ OPEN(ARQ) THEN > > -- > > ARQ := UTL_FILE.FOPEN( 'C:\DADOS\ ARQUIVOS_ > GERADOS_DO_ ORACLE\CARGA\ ERRO_PROC' , 'AI_PRAZO_ACC. TXT','W', 5000); > > -- > > END IF; > > -- > > UTL_FILE.PUT_ LINE(ARQ, V_LOCAL_ERRO| |' > '||V_DADOS_ERRO| |' '||SQLERRM); > > -- > > END; > > -- > > WHEN OTHERS THEN > > -- > > IF NOT UTL_FILE.IS_ OPEN(ARQ) THEN > > -- > > ARQ := UTL_FILE.FOPEN( 'C:\DADOS\ ARQUIVOS_ GERADOS_DO_ > ORACLE\CARGA\ ERRO_PROC' , 'AI_PRAZO_ACC. TXT','W', 5000); > > -- > > END IF; > > -- > > UTL_FILE.PUT_ LINE(ARQ, V_LOCAL_ERRO| |' '||V_DADOS_ERRO| |' > '||SQLERRM); > > -- > > END; > > -- > > END LOOP; > > -- > > IF UTL_FILE.IS_ OPEN(ARQ) THEN > > -- > > UTL_FILE.FCLOSE( ARQ); > > -- > > END IF; > > -- > > COMMIT; > > -- > > EXCEPTION > > -- > > WHEN UTL_FILE.INVALID_ PATH OR UTL_FILE.INVALID_ MODE OR > UTL_FILE.INVALID_ OPERATION OR UTL_FILE.INVALID_ FILEHANDLE OR > UTL_FILE.WRITE_ ERROR THEN > > -- > > IF UTL_FILE.IS_ OPEN(ARQ) THEN > > -- > > UTL_FILE.PUT_ LINE(ARQ, 'UTL_FILE' ||' '||V_DADOS_ERRO| |' > '||SQLERRM); > > -- > > UTL_FILE.FCLOSE( ARQ); > > -- > > ELSE > > -- > > RAISE_APPLICATION_ ERROR(-20001, SQLERRM); > > -- > > END IF; > > -- > > COMMIT; > > -- > > WHEN OTHERS THEN > > -- > > IF NOT UTL_FILE.IS_ OPEN(ARQ) THEN > > -- > > UTL_FILE.PUT_ LINE(ARQ, 'OTHERS'| |' '||V_DADOS_ERRO| |' > '||SQLERRM); > > -- > > UTL_FILE.FCLOSE( ARQ); > > -- > > ELSE > > -- > > RAISE_APPLICATION_ ERROR(-20001, SQLERRM); > > -- > > END IF; > > -- > > COMMIT; > > -- > > END; > > / > > > > Fique com Deus. > > Um grande abraço. > > > > Att. > > Alexandre Brum > > > > Veja quais são os assuntos do momento no Yahoo! +Buscados > > http://br.maisbusca dos.yahoo. com > > > > [As partes desta mensagem que não continham texto foram removidas] > > > > Veja quais são os assuntos do momento no Yahoo! +Buscados > > http://br.maisbusca dos.yahoo. com > > > > [As partes desta mensagem que não continham texto foram removidas] > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Veja quais são os assuntos do momento no Yahoo! +Buscados > > http://br.maisbusca dos.yahoo. com > > > > [As partes desta mensagem que não continham texto foram removidas] > > > > > > > > > > > > > > > > > > > > Veja quais são os assuntos do momento no Yahoo! +Buscados > http://br.maisbuscados.yahoo.com > > [As partes desta mensagem que não continham texto foram removidas] >
