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
<*> 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
