Wait event latch* generalizado

[Róli]

Olá!

Histórico de tempo de execução do processo:

Tempo 1: 4h de execução:
Inicialmente, o processo (um dos principais processos da enmpresa), formado por vários SQLs, demnadava 4h de tempo de execução.

Tempo 2:
Em uma análise de Fks não indexadas, foi criado os índices para as Fks onde o tempo de execução passou a ser 1:10h.
Em uma solicitação de análise de performance do processo, em uma noite, foram feitas as seguintes ações:
1. Reconstruidos grupos de redos do banco, o tamanho de cada membro de 125M passou a ser 250M.
2. Implementado coleta de estatísticas full diária do schema do processo (o restante dos schemas, a coleta é incremental na semana e full final de semana).
3. Realizado rebuild dos índices do schema do processo.
4. Baixado o banco.
5. Colocado no ar o banco.

No dia seguinte, o processo foi executado em 15 minutos

Tempo 3:
Nos dias seguintes, o processo voltou a ser executado em 1:10h
Foi mantido a coleta de estatísticas full diária do schema e até feito rebuild ds índices, mas não teve melhora no processo.

Tempo atual: Continua 1:10h de execução.

Hoje, em um acompanhamento de execução do processo, os eventos de espera constantes gerados foram:
latch: shared pool
latch: library cache
latch: session allocation

Mas, cada SQL desse evento, não demora mais que 2 minutos, mas, os SQLs são executados muitas e muitas vezes no banco durante o processo.

Ao ler algumas postagens, verifiquei o seguinte:
O parâmetro cursor_sharing está como EXACT.
Os SQLs dos eventos de espera, possuem variáveis bind, mas também batante valores fixos que mudam.

Algumas verificações:

Registros de eventos:

SID EVENT SECONDS_IN_WAIT SQL_TEXT
==== ============================ =============== ======================================================================
516 latch: session allocation 1 UPDATE /*+ INDEX(idf idf_pk) */ COR_IDF IDF SET IND_LAN_FISCAL_I
516 latch: session allocation 1 CMS = 'N', IND_LAN_FISCAL_IPI = 'N', IND_LAN_FISCAL_STF = 'N', I
516 latch: session allocation 1 ND_LAN_FISCAL_STT = 'N' WHERE CODIGO_DO_SITE = :B3 AND DOF_SEQUE
516 latch: session allocation 1 NCE = :B2 AND IDF_NUM LIKE :B1

448 latch: library cache 1 UPDATE cor_dof dof SET dof.ctrl_obs_calculo = 'N', dof.ano_mes_e
448 latch: library cache 1 missao = '', dof.cfop_codigo = '5.117', dof.cobranca_loc_codigo
448 latch: library cache 1 = '002', dof.cobranca_pfj_codigo = '020004-2', dof.cpag_codigo =
448 latch: library cache 1 '', dof.ctrl_conteudo = 'I', dof.ctrl_dt_inclusao = to_date('11
448 latch: library cache 1 /12/2013 09:18:23','DD/MM/YYYY hh24:mi:ss'), dof.ctrl_dt_ult_alt
448 latch: library cache 1 eracao = to_date('11/12/2013 09:18:24','DD/MM/YYYY hh24:mi:ss'),
448 latch: library cache 1 dof.ctrl_origem_registro = 'D', dof.ctrl_processamento = '0', d
448 latch: library cache 1 of.ctrl_situacao_dof = 'N', dof.destinatario_loc_codigo = '002',
448 latch: library cache 1 dof.destinatario_pfj_codigo = '020004-2', dof.dest_princ_est_co
448 latch: library cache 1 digo = '020004-2', dof.dh_emissao = '', dof.dof_import_numero =
448 latch: library cache 1 'oracle.xml.jaxp.JXSAXParser@1235797', dof.dt_conversao_moeda_na

************************************* Somente um tipo de evento é gerado por vez, nunca dois tipos de evento latch são gerados juntos.


LATCH TYPE IMPACT SLEEP RATE WAITS HOLDING LEVEL
===================================== =========== ========== ============= =====
shared pool 2822915 0.32% 0 7
library cache 1505697 0.03% 0 5
session allocation 205108 0.03% 0 5
cache buffers chains 32756 0.00% 0 1
user lock 13723 0.06% 0 3
row cache objects 9737 0.00% 0 4
kks stats 9145 0.03% 0 0
process allocation 8951 0.08% 0 1
enqueues 5741 0.00% 0 5
library cache lock 5451 0.00% 0 6
shared pool simulator 3670 0.00% 0 8
active service list 1946 0.01% 0 0


SELECT name, to_char(gets), misses, sleeps,
immediate_gets, immediate_misses
FROM v$latch
WHERE name = 'cache buffers chains';

NAME TO_CHAR(GETS) MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
--------------------- ----------------- -------- ---------- -------------- ----------------
cache buffers chains 57771019439 723868 32330 1453352238 1903


SELECT *
FROM v$sysstat
WHERE name like 'parse count%';

STATISTIC# NAME CLASS VALUE STAT_ID
========== ======================= ======= ========== ==========
338 parse count (total) 64 552823465 63887964
339 parse count (hard) 64 6472753 143509059
340 parse count (failures) 64 36707 1118776443


dbprod SQL> SELECT SUBSTR(sql_text, 1, 60) sql,
count(*) copies
FROM v$sqlarea
GROUP BY substr(sql_text, 1, 60)
HAVING count(*) > 3
ORDER BY count(*)
/ 2 3 4 5 6 7

SQL COPIES
------------------------------------------------------------------------ ----------
Select /*+ INDEX(cor_classificacao_mercadoria CFMERC_UK) */ 4901
t400frel.sg 79283
...
..
511 rows selected.


Como resolver esses tipos de evento latch sendo que só acontecem para esse processo?

Desde já, muito obrigada!

[portilho]

Oi !
Certamente o problema é a falta de variáveis Bind. Foram milhares de execução para (quase) o mesmo SQL.
Até de acordo com a sequência de fatos faz sentido, pois na primeira execução foi rápido, mas na segunda, quando a Library Cache já estava cheia, passou a demorar.
Cuidado, não altere o parâmetro CURSOR_SHARING sem testar antes, pois ele pode ter efeitos colaterais, associados com o conteúdo do próprio SQL (por exemplo, sustr).
Veja abaixo como o nome da coluna retornada muda. A aplicação pode ter algum impacto com isso:

SQL> ALTER SESSION SET CURSOR_SHARING=EXACT;

Session altered.

SQL> SELECT SUBSTR(OBJECT_NAME, 1, 2) FROM DBA_OBJECTS A WHERE ROWNUM = 1;

SUBSTR
------
IC

SQL> ALTER SESSION SET CURSOR_SHARING=FORCE;

Session altered.

SQL> SELECT SUBSTR(OBJECT_NAME, 1, 2) FROM DBA_OBJECTS B WHERE ROWNUM = 1;

SUBSTR(OBJECT_NAME,1,2)
--------------------------------------------------------------------------------
IC

SQL>

[Róli]

Olá,

Entendi, obrigada!

Minha dúvida é em relação a uma constatação do fornecedor dessa aplicação, informaram que o processo no banco deles, roda em 7 minutos. Importaram o schema do cliente que atendo e rodaram o processo. Nesse caso, mesmo tendo muitos literais em SQL executados muitas e muitas vezes, tiverem o tempo bom.
Temos alguma ação a fazer com a Library Cache? (Não temos como aumentar SGA, já está no limite de memória disponível do servidor)

Muito obrigada

[portilho]

Treino é treino, jogo é jogo.
A menor das diferenças no ambiente pode alterar o tempo completamente.
Só dá para saber qual o motivo da diferença com Traces 10046 dos dois ambientes.

Aumentar a Shared Pool não irá te ajudar. As Latchs são as maiores consumidoras de tempo do processo? Tem um tkprof?