Menu de navegação

[FernandaAcParticipante]

Bom dia! Gostaria de saber se podem me ajudar com um problema que estou enfrentando.

Estou tendo problema de estouro de memória (erro “ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)”) na abertura de um cursor de uma query simples, porém que retorna um grande volume de dados (já chegou a 1.400.000 de registros). Essa rotina gera diariamente um arquivo txt utilizando o UTL_FILE, carregado com base no resultado do cursor. Já chegou a processar com sucesso arquivos com até 600.000 registros, porém, quando o volume aumenta temos o problema do estouro.

Quando o estouro apareceu, fiz uma alteração na lógica da rotina, implementando o Bulk Collect e o limite de 5000 registros.

Analisando detalhadamente o log de erro do verifiquei o seguinte:

No Log eu informo em que passo da execução estamos e o detalhe da linha carregada no arquivo (REGISTRO):
-20006-Erro execucao SP_GERA_ARQUIVO: cEventos_Pri – ORA-ERRO: -4030-ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)–REGISTRO: -LINHA: 1-Arquivo: EVENTO_PRMCNL_AMAZON_20130719.txt-Dir: /ctmux06/nframz/export-Usuario: OPS$CTMUX06

Análise>
O erro ocorre no momento de abrir o cursor cEventos_Pri.
Ainda não chegou a carregar nenhuma linha no arquivo.
O arquivo é salvo a cada 10.000 linhas. como no log temos que estava ainda na “LINHA” “1”, e com o “REGISTRO” em branco, temos como saber que não é o tamanho do arquivo. Provavelmente é a quantidade de linhas do próprio cursor. Mas gostaria de entender ainda porque o Bulk Collect para a abertura deste cursor não resolveu o problema.

LOOP
FETCH p_cursor BULK COLLECT
INTO V_COD_LOJA ,
V_COD_PRDUTO ,
V_COD_EVENTO ,
V_DSC_EVENTO ,
V_DAT_INICIO_EVENTO ,
V_DAT_FIM_EVENTO ,
V_COD_TIPO_EVENTO ,
V_DSC_TIPO_EVENTO
LIMIT 5000;

Pesquisando sobre o estouro da memória e o uso do bulk collect verifiquei que uma das possibilidades do estouro é o tamanho do limite do Bulk Collect que pode estar muito grande. Eu configurei o limite em 5000 registros, pois no outro sistema em que atuo este limite é normal.

O bulk collect aloca o resultado do cursor na memória PGA, limitando o número de registros de acordo com o volume indicado.

Antes da implementação da utilização do comando de Bulk Collect a rotina já estava apresentando o estouro de memória, o que descarta a hipótese do próprio bulk collect estar causando o problema.

Penso em criar um parâmetro genérico com o número de registros a ser usado neste limite. Com isso, posso ir ajustando de acordo com os resultados.

O que ainda não consegui compreender é que já processamos com sucesso volumes de até 600.000 registros, utilizando este BULK COLLECT. Como estamos alocando somente de 5000 em 5000 registros, por que um dia com 1.000.000 de registros estaria estourando a memória PGA? Pela lógica, o uso de memória não deveria ser maior neste caso. O estouro está ocorrendo logo na primeira abertura do cursor e não após carregamento parcial do arquivo.

Por este motivo, ainda não tenho certeza se reduzindo este limite estarei solucionando o problema de estouro da memória PGA.

O que mais posso avaliar?

Obrigada!
Abs,
Fernanda

Rotina detalhada:


CREATE OR REPLACE PACKAGE BODY PRI.PG_EXPORT_EVENTO_PRMCNL_NFR IS

PROCEDURE SP_EXPORT_EVENTO_PRMCNL_NFR
( P_DATA IN DATE
, P_DIRETORIO IN VARCHAR2)
IS

cEventos_Pri sys_refcursor;
cEventos_Prom_Flex sys_refcursor;

v_linha NUMBER := 0;
arqDiretorio VARCHAR2(255) := '';
strArquivo VARCHAR2(50) := '';
sLastPosition VARCHAR2(500) DEFAULT '';
strRegistro VARCHAR2(32700) := '';

v_retorno VARCHAR2(2000);

-- ativar debug em arquivo txt:
v_Handle Utl_File.File_Type;
--

V_SESSION_USERNAME VARCHAR2(25);
v_cod_erro NUMBER;
v_msg_erro VARCHAR2(2000);
exc Exception;

BEGIN

DBMS_APPLICATION_INFO.SET_MODULE( 'PG_EXPORT_EVENTO_PRMCNL_NFR' , 'SP_EXPORT_EVENTO_PRMCNL_NFR');
DBMS_APPLICATION_INFO.set_client_info( P_DATA);

-- Obtem o usuario que esta acessando o sistema
BEGIN
SELECT SUBSTR(S.USERNAME,1,25)
INTO V_SESSION_USERNAME
FROM V$SESSION S
WHERE SID IN (SELECT DISTINCT SID FROM V$MYSTAT);
EXCEPTION
WHEN OTHERS THEN
V_SESSION_USERNAME :='USUARIO NAO IDENTIFICADO';
END;

sLastPosition:='POSICAO(VERIFICA DIRETORIO)';

--Diretorio
if upper(P_DIRETORIO) = 'H' then --Homologacao
arqDiretorio := 'c:tempcomercialnfr';
else
arqDiretorio := P_DIRETORIO;
end if;

-- PK de promocoes (produto + loja + dat inicio) -- produto nao pode estar em duas promocoes ao mesmo tempo
-- Amazon nao possui codigo do evento, devera ser gerado internamente no nfr
sLastPosition:='POSICAO(MONTAGEM STRING SQL)';
strArquivo := 'EVENTO_PRMCNL_AMAZON_' || to_char(P_DATA,'RRRRMMDD') || '.txt';

sLastPosition:='POSICAO(LOG INICIO INTERFACE)';

BEGIN
-- 396 -- Início da Interface (pri.p_prmtro_mensagem)
INSERT INTO L_OCORR_INTERF_PRIC(COD_OCORR_INTERF,DAT_PROC,COD_INTERF,IND_TIPO_REGIST,DSC_RESULT_INTERF,DSC_ERRO_BANCO,NOM_ARQUIV,MSG_COD,PARAM_LIST,DAT_ATUALIZACAO)
VALUES (pri.sq_ocorr_interf_pric.nextval, SYSDATE, 'EXPNFRPR','I','OK',NULL,strArquivo,396,'Data: ' || P_DATA || ',Dir:' ||P_DIRETORIO ||',Usuario: ' ||V_SESSION_USERNAME,SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_cod_erro := -20000;
v_msg_erro := 'Erro na inclusao do log de execucao OK inicio: ' || SQLCODE || '-' ||SQLERRM || '-' ||'Data: ' || P_DATA || ',Dir:' ||P_DIRETORIO ||',Usuario: ' ||V_SESSION_USERNAME;
-- Se ocorrer erro no log nao deve parar o processamento, executar a exportacao do arquivo e logar erro somente no final
END;

sLastPosition:='POSICAO(ABRE ARQUIVO)';

-- Inicio da geracao do arquivo txt
v_Handle := Utl_File.fopen(arqDiretorio, strArquivo, 'w');

-- Cabecalho
sLastPosition:='POSICAO(Cria o cabecalho)';
strRegistro := 'Cod Loja;Cod Produto;Cod Evento;Dsc Evento;Data Inicio;Data Fim;Cod Tipo Evento;Dsc Tipo Evento; Linha';

sLastPosition:='POSICAO(GRAVA REGISTRO cabecalho)';

Utl_File.Put_Line (v_Handle, strRegistro);

sLastPosition:='POSICAO(ABRE CURSOR cEventos_Pri)';

-- Extracao de promocao (Origem: Maestro / Amazon)
open cEventos_Pri for
SELECT AP.COD_UNDADE_PRECO || ';' AS COD_LOJA
, AP.COD_PRDUTO || ';' AS COD_PRDUTO
, 0 || ';' AS COD_EVENTO
, I.DSC_TIPO_PRMCAO || ';' AS DSC_EVENTO
, TO_CHAR(TO_DATE(PPA.DAT_INICIO_PRMCAO,'DD/MM/RRRR'),'DD/MM/RRRR') || ';' AS DAT_INICIO_EVENTO
, TO_CHAR(TO_DATE(PPA.DAT_FIM_PRMCAO,'DD/MM/RRRR'),'DD/MM/RRRR') || ';' AS DAT_FIM_EVENTO
, I.COD_IDNTFC_PRMCAO || ';' AS COD_TIPO_EVENTO
, I.DSC_TIPO_PRMCAO || ';' AS DSC_TIPO_EVENTO
FROM I_ATUAL_PRECO_OUT AP, PRI.B_PRDUTO_PRMCAO_ATIVA PPA,PRI.B_IDNTFC_PRMOCAO I
WHERE AP.COD_UNDADE_PRECO = PPA.COD_UNDADE
AND AP.COD_PRDUTO = PPA.COD_PRDUTO
AND PPA.COD_IDNTFC_PRMCAO = I.COD_IDNTFC_PRMCAO
AND AP.VAL_VENDA_CENCRG = PPA.VAL_PRECO_PRMCAO
AND TRUNC(AP.DAT_FIM_PRMCAO) = TRUNC(PPA.DAT_FIM_PRMCAO)
AND TRUNC(PPA.DAT_INICIO_PRMCAO) >= TO_DATE(P_DATA,'DD/MM/RRRR')
AND TRUNC(PPA.DAT_INICIO_PRMCAO) <= TO_DATE(P_DATA+1,'DD/MM/RRRR') AND PPA.COD_IDNTFC_PRMCAO <> 483;

sLastPosition:='POSICAO(SP_CARREGA_ARQUIVO cEventos_Pri)';

v_linha := v_linha +1;

SP_CARREGA_ARQUIVO(p_cursor => cEventos_Pri
, p_nome_cursor => 'cEventos_Pri'
, p_linha => v_linha
, p_handle => v_Handle
, x_retorno => v_retorno);

IF v_retorno is not null THEN
v_cod_erro := -20006;
v_msg_erro := 'Erro execucao SP_GERA_ARQUIVO: cEventos_Pri - '|| v_retorno|| chr(13) || '-Arquivo: ' || strArquivo || '-Dir: ' || arqDiretorio||'-Usuario: ' ||V_SESSION_USERNAME;
RAISE exc;
END IF;

sLastPosition:='POSICAO(ABRE CURSOR cEventos_Prom_Flex)';

-- Extracao de promocao flexivel (Origem: Gold)
open cEventos_Prom_Flex for
SELECT PF.COD_LOJA || ';' AS COD_LOJA
, TO_NUMBER(PF.COD_PRODUTO) || ';' AS COD_PRDUTO
, 0 || ';' AS COD_EVENTO
, IP.DSC_TIPO_PRMCAO || ';' AS DSC_EVENTO
, TO_CHAR(to_date(PF.DAT_INICIO_PROMOCAO,'DD/MM/RRRR'),'DD/MM/RRRR') || ';' AS DAT_INICIO_EVENTO
, TO_CHAR(to_date(PF.DAT_FIM_PROMOCAO,'DD/MM/RRRR'),'DD/MM/RRRR') || ';' AS DAT_FIM_EVENTO
, 483 || ';' AS COD_TIPO_EVENTO
, IP.DSC_TIPO_PRMCAO || ';' AS DSC_TIPO_EVENTO
FROM VE_PRMCAO_FLEXVL PF
INNER JOIN PRI.B_IDNTFC_PRMOCAO IP
ON COD_IDNTFC_PRMCAO = 483
WHERE PF.DAT_FIM_PROMOCAO IS NOT NULL
AND TRUNC(PF.DAT_INICIO_PROMOCAO) >= TO_DATE(P_DATA,'DD/MM/RRRR')
AND TRUNC(PF.DAT_INICIO_PROMOCAO) <= TO_DATE(P_DATA+1,'DD/MM/RRRR'); sLastPosition:='POSICAO(SP_CARREGA_ARQUIVO cEventos_Prom_Flex)'; SP_CARREGA_ARQUIVO(p_cursor => cEventos_Prom_Flex
, p_nome_cursor => 'cEventos_Prom_Flex'
, p_linha => v_linha
, p_handle => v_Handle
, x_retorno => v_retorno);

IF v_retorno is not null THEN
v_cod_erro := -20006;
v_msg_erro := 'Erro execucao SP_GERA_ARQUIVO: cEventos_Prom_Flex - '|| v_retorno|| chr(13) || '-Arquivo: ' || strArquivo || '-Dir: ' || arqDiretorio||'-Usuario: ' ||V_SESSION_USERNAME;
RAISE exc;
END IF;

sLastPosition:='POSICAO(FECHA ARQUIVO)';
-- Fim - fecha arquivo
Utl_File.fclose_all;
--

sLastPosition:='POSICAO(LOG FIM INTERFACE)';

v_linha := v_linha-1;

BEGIN
-- 374 -- Fim da interface (pri.p_prmtro_mensagem)
INSERT INTO L_OCORR_INTERF_PRIC(COD_OCORR_INTERF,DAT_PROC,COD_INTERF,IND_TIPO_REGIST,DSC_RESULT_INTERF,DSC_ERRO_BANCO,NOM_ARQUIV,MSG_COD,PARAM_LIST,DAT_ATUALIZACAO)
VALUES (pri.sq_ocorr_interf_pric.nextval, SYSDATE, 'EXPNFRPR','I','OK',NULL,strArquivo || ': ' || v_linha || ' registros exportados',374,'Data: ' || P_DATA || ',Dir:' ||P_DIRETORIO ||',Usuario: ' ||V_SESSION_USERNAME,SYSDATE);
COMMIT;

EXCEPTION
WHEN OTHERS THEN
v_cod_erro := -20000;
v_msg_erro := v_msg_erro || chr(13) || 'Erro na inclusao do log de execucao OK fim: ' || SQLCODE || '-' ||SQLERRM || '-' ||'Data: ' || P_DATA || ',Dir:' ||P_DIRETORIO ||',Usuario: ' ||V_SESSION_USERNAME;
RAISE exc;
END;

EXCEPTION
WHEN OTHERS THEN

IF v_msg_erro is null THEN
IF sLastPosition='POSICAO(ABRE CURSOR)' THEN
v_cod_erro := -20004;
v_msg_erro := 'ORA-ERRO: ' || SQLCODE || '-' || SQLERRM||'-'||sLastPosition || '-Arquivo: ' || strArquivo || '-Dir: ' || arqDiretorio||'-Usuario: ' ||V_SESSION_USERNAME;
ELSE
v_cod_erro := -20005;
v_msg_erro := 'ORA-ERRO: ' || SQLCODE || '-' || SQLERRM||'-'||sLastPosition || '-REGISTRO: ' || strRegistro || '-LINHA: ' || v_linha || '-Arquivo: ' || strArquivo || '-Dir: ' || arqDiretorio||'-Usuario: ' ||V_SESSION_USERNAME;
END IF;
END IF;

-- 364 -- Erro na Interface.
BEGIN
INSERT INTO L_OCORR_INTERF_PRIC(COD_OCORR_INTERF,DAT_PROC,COD_INTERF,IND_TIPO_REGIST,DSC_RESULT_INTERF,DSC_ERRO_BANCO,NOM_ARQUIV,MSG_COD,PARAM_LIST,DAT_ATUALIZACAO)
VALUES (pri.sq_ocorr_interf_pric.nextval, SYSDATE, 'EXPNFRPR','I','ERRO',substr(v_cod_erro || '-' || v_msg_erro,1,512),strArquivo || ': ' || v_linha || ' registros exportados',364,'Data: ' || P_DATA || ',Dir:' ||P_DIRETORIO ||',Usuario: ' ||V_SESSION_USERNAME,SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_msg_erro := v_msg_erro || chr(13) || 'Erro na inclusao do log de execucao ERRO fim: ' || SQLCODE || '-' ||SQLERRM;
END;

RAISE_APPLICATION_ERROR(v_cod_erro,v_msg_erro, true);

END SP_EXPORT_EVENTO_PRMCNL_NFR;

PROCEDURE SP_CARREGA_ARQUIVO( p_cursor IN sys_refcursor
, p_nome_cursor IN VARCHAR2
, p_linha IN OUT NUMBER
, p_handle IN OUT Utl_File.File_Type
, x_retorno IN OUT VARCHAR2) IS

-- Campos que deverao ser extraidos para o arquivo - inicio
TYPE T_COD_LOJA IS TABLE OF VARCHAR2(9); -- NUMBER(8); -- PK
TYPE T_COD_PRDUTO IS TABLE OF VARCHAR2(9); -- NUMBER(8); -- PK
TYPE T_COD_EVENTO IS TABLE OF VARCHAR2(9); -- NUMBER(8); -- PK
TYPE T_DSC_EVENTO IS TABLE OF VARCHAR2(151); -- VARCHAR2(150);
TYPE T_DAT_INICIO_EVENTO IS TABLE OF VARCHAR2(11); -- DATE;
TYPE T_DAT_FIM_EVENTO IS TABLE OF VARCHAR2(11); -- DATE;
TYPE T_COD_TIPO_EVENTO IS TABLE OF VARCHAR2(9); -- NUMBER(8);
TYPE T_DSC_TIPO_EVENTO IS TABLE OF VARCHAR2(151); -- VARCHAR2(150);

V_COD_LOJA T_COD_LOJA;
V_COD_PRDUTO T_COD_PRDUTO;
V_COD_EVENTO T_COD_EVENTO;
V_DSC_EVENTO T_DSC_EVENTO;
V_DAT_INICIO_EVENTO T_DAT_INICIO_EVENTO;
V_DAT_FIM_EVENTO T_DAT_FIM_EVENTO;
V_COD_TIPO_EVENTO T_COD_TIPO_EVENTO;
V_DSC_TIPO_EVENTO T_DSC_TIPO_EVENTO;
-- OBS: atualmente no Amazon, a descricao do evento eh a descricao do tipo do evento (ex: tabloide)
-- O layout do arquivo foi desenvolvido com ambos os campos, de forma que no momento em que for criada
-- a interface com o sistema maestro, essa descricao deverah ser a descricao do evento ex: 'tabloide de pascoa')
-- Campos que deverao ser extraidos para o arquivo - fim

sLastPosition VARCHAR2(500) DEFAULT '';
strRegistro VARCHAR2(32700) := '';

BEGIN

x_retorno := NULL;

LOOP
FETCH p_cursor BULK COLLECT
INTO V_COD_LOJA ,
V_COD_PRDUTO ,
V_COD_EVENTO ,
V_DSC_EVENTO ,
V_DAT_INICIO_EVENTO ,
V_DAT_FIM_EVENTO ,
V_COD_TIPO_EVENTO ,
V_DSC_TIPO_EVENTO
LIMIT 5000;

FOR V_INDEX IN 1..V_COD_LOJA.COUNT LOOP

-- Detalhe
BEGIN
sLastPosition:='POSICAO(MONTA REGISTRO) ' || p_nome_cursor;
strRegistro := V_COD_LOJA(V_INDEX)
|| V_COD_PRDUTO(V_INDEX)
|| V_COD_EVENTO(V_INDEX)
|| V_DSC_EVENTO(V_INDEX)
|| V_DAT_INICIO_EVENTO(V_INDEX)
|| V_DAT_FIM_EVENTO(V_INDEX)
|| V_COD_TIPO_EVENTO(V_INDEX)
|| V_DSC_TIPO_EVENTO(V_INDEX)
|| p_linha;

sLastPosition:='POSICAO(GRAVA REGISTRO) ' || p_nome_cursor;
Utl_File.Put_Line (p_Handle, strRegistro);
END;

p_linha := p_linha + 1;

-- A cada 10000 linhas grava o arquivo e libera memoria flush
IF mod(p_linha,10000) = 0 THEN
Utl_File.FFlush(p_Handle);
END IF;

END LOOP;

IF V_COD_LOJA.COUNT > 0 THEN
V_COD_LOJA.TRIM(V_COD_LOJA.COUNT);
END IF;
EXIT WHEN p_cursor%NOTFOUND;

END LOOP;

CLOSE p_cursor;

EXCEPTION
WHEN OTHERS THEN
x_retorno := 'ORA-ERRO: ' || SQLCODE ||'-'|| SQLERRM||'-'||sLastPosition || '-REGISTRO: ' || strRegistro || '-LINHA: ' || p_linha ;
END SP_CARREGA_ARQUIVO;

END PG_EXPORT_EVENTO_PRMCNL_NFR;


[rmanParticipante]

@FernandaAc

Pelo que deu pra entender o problema é falta de recurso, no caso, PGA.

Qual versão do Oracle Database e o Sistema operacional? Como está os parametros de memória?

[FernandaAcParticipante]

rman, obrigada pelo retorno!

A versão do Oracle é 10G. (10.2.0.3.0)

Dados de memória da v$parameter:

pga_aggregate_target = 8G
sort_area_size = 65536 (porem já removi o ORDER BY da query para reduzir uso da area de sort)
hash_area_size = 131072
sga_max_size = 8G
shared_pool_size = DEFAULT

Tem mais algum parametro que posso extrair? Não conheço muito da parte física do Oracle.

Obrigada!
Abs
Fernanda

[FernandaAcParticipante]

Complementando: o servidor do BD é UNIX.

[rmanParticipante]

@FernandaAc

Teria um servidor de homologação para testar algumas mudanças de parametro? Não é interessante fazer isso direto no servidor de produção.

O problema acontece em horarios de pico, ou seja, varias conexões abertas, ou com apenas 1 conexão já estoura? Pergunto isso, por que a conexão pode ser dedicada ou compartilhada, verifique para mim que tipo de conexão é sendo utilizada. Na conexão dedicada cada conexão terá usa própria PGA, e na compartilhada existe um reaproveitamento de memória entre as conexões abertas.

O sistema é WEB? Está sendo utilizado pool de conexão?

Posta pra mim o sga_target.

Pode fazer um teste, diminuir o sga_target e aumentar o pga_aggregate_target. Faça testes gradativos para chegar na proporção ideal, a diminuição do sga_target pode impactar em modo geral a desempenho do sistema.

Quanto de memória RAM tem a máquina? A máquina é dedicada a Banco de Dados?

[FernandaAcParticipante]

rman,
Seguem respostas:

Teria um servidor de homologação para testar algumas mudanças de parametro? Não é interessante fazer isso direto no servidor de produção.
Temos um servidor de homologação, mas que não apresenta as mesmas configurações de produção, infelizmente. O erro não ocorre em homologação, o volume de dados da base também é menor. 🙁
Estas mudanças precisam de restart de banco, certo?

O problema acontece em horarios de pico, ou seja, varias conexões abertas, ou com apenas 1 conexão já estoura?
Havia concorrência. Então nós mudamos o horário da execução buscando o horário em que não há outros processos em execução. Está rodando às 5h da manhã. Porém mesmo no horário de menor uso da base, ainda temos tido o mesmo problema.

Para avaliar os processos concorrentes eu pedi a execução da query abaixo (executarão 5 vezes, com intervalo de 3 em 3 minutos, no horário de processamento da rotina).
Porém, esta madrugada não conseguiram executar. Estou elaborando um script bat e tentarei configurar uma tarefa na minha máquina para exportar este resultado
sem depender de terceiros:
SELECT a.osuser,
a.status,
a.inst_id,
NVL(a.username, ‘(oracle)’) AS username,
a.module,
a.program,
b.value as pga_memory,
a.*
FROM gv$session a, gv$sesstat b, gv$statname c
WHERE a.sid = b.sid
AND a.inst_id = b.inst_id
AND b.statistic# = c.statistic#
AND b.inst_id = c.inst_id
AND c.name = ‘session pga memory’
AND a.program IS NOT NULL
ORDER BY b.value DESC;

Acha que podemos extrair mais alguma informação de memória que pode ser importante no momento da execução?

Pergunto isso, por que a conexão pode ser dedicada ou compartilhada, verifique para mim que tipo de conexão é sendo utilizada.
Na conexão dedicada cada conexão terá usa própria PGA, e na compartilhada existe um reaproveitamento de memória entre as conexões abertas.
Tenho quase certeza de que é compartilhada. Tenho como confirmar através de alguma tabela do BD? Não tenho acesso às configurações do servidor direto no UNIX.

O sistema é WEB? Está sendo utilizado pool de conexão?
Essa rotina especificamente é executada diretamente no servidor de banco de dados, através de um sistema de agendamento de jobs (Control-M), que executa um shell script que inicia a rotina utilizando o usuário do sistema operacional.
Há outros processos WEB e Access que utilizam a base de dados, porém durante o dia, não no horário em que estamos executando o job.

Posta pra mim o sga_target.
sga_target = 8Gb (8388608000)

Pode fazer um teste, diminuir o sga_target e aumentar o pga_aggregate_target. Faça testes gradativos para chegar na proporção ideal, a diminuição do sga_target pode impactar em modo geral a desempenho do sistema.
Vou ver se conseguimos avaliar algo assim em homologação. O risco é muito grande, pois há processos diários e críticos para o cliente rodando neste mesmo servidor. 🙁

Quanto de memória RAM tem a máquina? A máquina é dedicada a Banco de Dados?
A máquina é dedicada a este banco de dados.
Vou tentar saber a quantidade de memória RAM.

Obrigada!
Abs
Fernanda

[MANOELRBParticipante]

Sugestão!

Os cursores cEventos_Pri e cEventos_Prom_Flex estão sendo abertos por dias:

     AND TRUNC(PPA.DAT_INICIO_PRMCAO) >= TO_DATE(P_DATA,'DD/MM/RRRR')
     AND TRUNC(PPA.DAT_INICIO_PRMCAO) = TO_DATE(P_DATA,'DD/MM/RRRR')
  AND TRUNC(PF.DAT_INICIO_PROMOCAO) <= TO_DATE(P_DATA+1,'DD/MM/RRRR');

Será que ao invés de você abrir ele por dia você fazer um laço que processa cada hora do dia diminuindo a quantidade de registros passadas de uma unica vez não melhora o processo?

Att,

Manoel Jr.

[FernandaAcParticipante]

Manoel,

Obrigada pela sugestão!

Acho até que funcionará sim se fizermos o cursor executar mais de uma vez, com um loop externo e uma quebra por hora ou outra.

A questão é que se estou com um bulk collect alocando somente de 5000 em 5000 registros, se eu reduzir a query filtrando de hora em hora ou qualquer outro tipo de quebra, será que resolverá? Na prática acho que sim, mas não entendo porque deveria haver diferença se estou usando limite. 🙂

Teoricamente: O limite do cursor não deveria ser suficiente? Se não tiver memória para o limite de 5000, terá memória para um cursor que traga menos registros, mesmo ocupando o mesmo limite de 5000?

Se filtrar por hora, teremos pelo menos 48 execuções, com média de 32.000 registros em cada (se os registros fossem distribuídos de forma igual através do dia, mas infelizmente não são, os eventos são criados em massa para varias lojas ao mesmo tempo).

Outra possibilidade que quase implantamos foi realizar a query uma vez para cada cod_loja, mas não foi possível, porque meu gerente achou que degradaria a performance, executar a query 300 vezes, filtrando uma loja por vez, então implantamos a melhoria somente incluindo o bulk collect, removendo o union (essa query, na primeira versão sem o bulk era executada toda de uma só vez) e removendo um order by por 5 campos que estava sendo realizado.

Essa quebra por loja é minha próxima tentativa mapeada, mas mais para “gambearra” rs, do que solução que me orgulharia, pois até então não consegui entender o motivo do bulk collect não estar restringindo o uso da memória da forma como eu imaginei que faria.

[b]
Outra coisa importante[/b] que esqueci de citar anteriormente: a mesma query, executada diretamente no PLSQL Developer funcionou sem causar o estouro de memória.

Dúvida: será a forma de implementação da rotina que está fazendo com que o bulk collect não funcione corretamente? Ou sobrecarregando a memória de forma diferente da execução da query fora da rotina? Tem alguma forma diferente que eu poderia aplicar o bulk collect?

[Sergio WilliansMestre]

Já vi isso acontecer algumas vezes, e posso afirmar que em 99% delas o problema era justamente o mesmo que o rman está desconfiando. Falta de recursos na PGA.

Uma vez li um artigo do Steven Feurstein onde ele era categórico em afirmar que “Não existe almoço grátis !”, bulk collect geralmente dá ganho de performance mas em contrapartida adora consumir memória. Inclusive, há um artigo dele mesmo, bem interessante, onde mostra algumas técnicas de utilização de bulk collect sem monopolizar toda a PGA. 🙂

http://www.oracle.com/technetwork/issue … 09862.html

Espero que seja de alguma valia.

[rmanParticipante]

@FernandaAc

Os parametros SGA_TARGET e PGA_AGGREGATE_TARGET são dinamicos, não será necessário reiniciar a instancia. Diminua primeiro o SGA_TARGET e aumente o PGA_AGGREGATE_TARGET em seguida. O ideal é ter um base de homologação o mais próximo possível do ambiente de produção, verifique se é possível restaurar um backup físico pelo RMAN. Se não puder homologar a mudança dos parametros não faça na produção, é melhor procurar outra solução.

Tinha quase certeza que você ia responder que o modo de conexão era dedicada. A conexão compartilhada poderia ser uma solução, caso existam um número muito alto de conexões abertas simuntaneas. Pelo dbca, em configure database option é possível definir o modo de conexão, creio que é necessário alterar o tnsnames.ora do client também.

Bom, a falta de recurso é o problema principal. A forma como o BULK COLLECT com o LIMIT foi empregado está correta, mas mesmo assim a PGA não é suficiente.

Fazer um upgrade de memória do servidor e redistribuir PGA e SGA, ou redistribuir a memória que existe.

[FernandaAcParticipante]

Pessoal, muito obrigada pela ajuda de todos, estou tentando postar resposta desde ontem, mas tive problema de conexão, só estou conseguindo responder agora.

@rman, eu estava enganada e sua quase certeza estava certa! A conexão nessa base é do tipo Dedicada.
Aqui as configurações não seguem um padrão, rs, mas consegui respostas que mostram claramente que sua solução é a ideal. O problema é mexer em produção em um sistema cuja prioridade da nossa exportação é baixíssima perto dos demais processos que rodam nele.

Respostas atualizadas:

• Tipo de conexão: Dedicada ou Compartilhada? (na conexão dedicada cada conexão terá usa própria PGA, e na compartilhada existe um reaproveitamento de memória entre as conexões abertas).
  RESPOSTA: Dedicada, cada processo banco posssui um processo servidor.

• Quanto de memória RAM tem a máquina?
  RESPOSTA: Esta lpar possui 17GBs de RAM, o banco possui alocado para ele 3,2 GB de SGA e 800M de PGA

• A máquina é dedicada somente ao Banco de Dados do Amazon?
  RESPOSTA: Não, existem outras aplicação que são executadas nesta maquina junto ao banco.

Apesar de termos 8G de PGA parametrizados na base, somente 800M são alocados para o sistema onde estamos rodando a exportação, isso é possível? 😮

Assim, realmente fica complicado. 😛

Muito obrigada pelos esclarecimentos!

Abs
Fernanda

[rmanParticipante]

@FernandaAc

Caso for implentar a mudança de conexão dedicada para compartilhada lembre-se que é necessário alterar o tnsnames.ora. A mudança pode ser feita via dbca. Segue um exemplo da mudança no tnsnames.ora:

SHARED CONNECTION TO TEST DATABASE

TEST_SHARED =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = alpha2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = test)
)
)

DEDICATED CONNECTION TO TEST DATABASE

TEST_DEDICATED =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = alpha2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)


Está confuso as informações de memória, anteriormente você disse que sga_target = 8G, sga_max_size = 8G pga_aggregate_target = 8G. Não bate com 3,2 GB de SGA e 800 MB de PGA.

Se a máquina possui 17 GB você pode aumentar a pga_aggregate_target.

[Regis AraujoParticipante]

Opa..!

Boa tarde a Todos..!!!

Acrescente a entrada abaixo antes do EXIT WHEN p_cursor%NOTFOUND;


V_COD_LOJA.DELETE;
V_COD_PRDUTO.DELETE;
V_COD_EVENTO.DELETE;
V_DSC_EVENTO.DELETE;
V_DAT_INICIO_EVENTO.DELETE;
V_DAT_FIM_EVENTO.DELETE;
V_COD_TIPO_EVENTO.DELETE;
V_DSC_TIPO_EVENTO.DELETE;


Você precisa limpar as informações da memória, o buck collect é muito mais aproveitado com FORALL ao invês de for loop;

Espero que ajude..!

Abraços..!

[FernandaAcParticipante]

@Thunder_Catz

Muito obrigada pela sugestão, gostei muito e vou incluir como medida corretiva.

Porém, como o erro acontece logo na primeira abertura do cursor, não chega a executar o trecho em que eu colocarei estes deletes. Com isso, acredito que ainda terei que fazer a alteração no filtro do cursor para que retorne menos dados, independente do bulk…

@rman
Infelizmente acho que não teremos aprovação para alterar os parâmetros de memória do banco, mas manterei como meta importante equalizarmos o ambiente de homologação com produção, para permitir análise deste tipo de problema!

Obrigada a todos 🙂

Abs
Fernanda

[Autor]

Posts