Menu de navegação

[eversonpizaParticipante]

Olá amigos,

Tenho um banco que esta apresentando muita contenção em ‘library cache lock’, esse evento representa mais de 90% do eventos do banco.

O problema é que não consigo encontrar a consulta que esta esperando por esse evento, olhando na v$session sempre aparece o mesmo sql_id, mas esse sql_id não existe na v$sql (sempre os selects abaixo), alguém tem alguma dica do que posso fazer?

SQL> select sql_id, count(*)
2 from gv$session
3 where EVENT ='library cache lock'
4 group by sql_id;

SQL_ID COUNT(*)
------------- ----------
4gd6b1r53yt88 49
47

SQL> select *
2 from gv$sql
3 where sql_id = '4gd6b1r53yt88';

no rows selected

A versão do oracle é 10.2.0.3 com RAC, o SO é Linux.

Obrigado,
Everson[/code]

[vieriParticipante]

Vc não conseguiu pegar porque ela já não está mais ativa.

tente na GV$open_cursor.

Aonde vc se baseou para dizer que 90% dos waits da base são de concurrency library cache lock ?

qual tamanho da sua shared_pool / SGA ?

[eversonpizaParticipante]

Olá vieri,

Olhei na gv$open_cursor e ele trouxe como SQL_TEXT o valor ‘table_1_ff_14f_0_0_0′, vi em outro forum que o código do meio ’14f’ é o código hexadecimal do object_id, com isso cheguei no objeto SYS.KOTTD$.

Chegar até ai não ajudou muito, tb vi em outros forum que pode ser alguma coisa relacionada a campos CLOB, como criamos um nova funcionalidade a algum tempo que usa esse tipo de campo, vamos parar ela para ver se o problema é resolvido.

Everson

[eversonpizaParticipante]

Sobre como eu cheguei a conclusão que estou tento muito contenção desse tipo, eu peguei essa informação em vários locais, inclusive no AWR, abaixo segue um select que fiz na gv$active_session_history

SELECT h.event
,h.wait_class
,count(*) total
,sum(h.wait_time + h.time_waited)/1000000 total_wait_time
FROM
gv$active_session_history h,
v$event_name e
WHERE h.sample_time BETWEEN sysdate - (1/24) AND sysdate
AND h.event_id = e.event_id
GROUP BY h.event, h.wait_class
ORDER BY total_wait_time DESC;

                                                                 TOT_WAIT

EVENT WAIT_CLASS TOT (s)

---

library cache lock Concurrency 81,523 39,751.58
log file sync Commit 1,054 444.64
row cache lock Concurrency 830 388.63
cr request retry Other 572 348.75
gc buffer busy Cluster 788 324.69
gc cr block busy Cluster 351 51.85
gc current block busy Cluster 401 43.62
enq: TX - row lock contention Application 54 19.96
gcs drm freeze in enter server mode Other 47 18.89
gc cr block 3-way Cluster 351 15.05
gc current block 3-way Cluster 318 11.47
gc remaster Cluster 16 11.16
db file sequential read User I/O 998 8.07
gc current grant busy Cluster 118 6.00
gc cr block 2-way Cluster 315 4.51
gc current block 2-way Cluster 376 4.28
library cache pin Concurrency 15 3.44
enq: TX - index contention Concurrency 30 3.29
db file scattered read User I/O 317 2.89
gc cr block congested Cluster 18 1.75
gc cr grant 2-way Cluster 42 1.26
gc current block congested Cluster 12 0.88
DFS lock handle Other 14 0.84
...


[vieriParticipante]

Da uma olhada como está o comportamento da SGA.

select * from gv$sga_dynamic_components;

com a inclusão dos CLOB vc com certeza está usando mais SGA do que antes.

[eversonpizaParticipante]

Vieri,

Tiramos do ar a aplicação que utilizava o clob que mencionei no outro post, mas nada mudou, continua com muita contenção.

Oq devo analisar na gv$sga_dynamic_components?
Segue o conteudo dela:
select * from gv$sga_dynamic_components where current_size > 0 order by 1,2;

I	COMPONENT	CURRENT_SIZE	MIN_SIZE	MAX_SIZE	USER_SPECIFIED_SIZE	OPER_COUNT	LAST_OPER_TYP	LAST_OPER	LAST_OPER_TIME	GRANULE_SIZE
1	DEFAULT buff	8472494080	8472494080	0	0	1	GROW	DEFERRED	06/10/2009 01:32	16777216
	er cache

1|java pool | 67108864| 67108864| 0| 0| 1|SHRINK |DEFERRED |06/10/2009 01:32| 16777216
1|large pool | 50331648| 50331648| 0| 0| 0|STATIC | | | 16777216
1|shared pool | 12868124672| 12868124672| 0| 0| 0|STATIC | | | 16777216
2|DEFAULT buff| 8489271296| 8489271296| 0| 0| 1|GROW |DEFERRED |04/10/2009 21:57| 16777216
|er cache | | | | | | | | |

2|java pool | 67108864| 67108864| 0| 0| 1|SHRINK |DEFERRED |04/10/2009 21:57| 16777216
2|large pool | 33554432| 33554432| 0| 0| 0|STATIC | | | 16777216
2|shared pool | 12868124672| 12868124672| 0| 0| 0|STATIC | | | 16777216
3|DEFAULT buff| 8472494080| 8472494080| 0| 0| 1|GROW |DEFERRED |04/10/2009 19:40| 16777216
|er cache | | | | | | | | |

3|java pool | 67108864| 67108864| 0| 0| 1|SHRINK |DEFERRED |04/10/2009 19:40| 16777216
3|large pool | 50331648| 50331648| 0| 0| 0|STATIC | | | 16777216
3|shared pool | 12868124672| 12868124672| 0| 0| 0|STATIC | | | 16777216
4|DEFAULT buff| 8506048512| 8506048512| 0| 0| 1|GROW |DEFERRED |12/11/2009 20:11| 16777216
|er cache | | | | | | | | |

4|java pool | 67108864| 67108864| 0| 0| 1|SHRINK |DEFERRED |12/11/2009 20:11| 16777216
4|large pool | 16777216| 16777216| 0| 0| 0|STATIC | | | 16777216
4|shared pool | 12868124672| 12868124672| 0| 0| 0|STATIC | | | 16777216


Obrigado,
Everson

[vieriParticipante]

Sua SGA está bem configurada 12g pra shared_pool e 8G para o buffer cache em cada um dos 4 nodes do RAC.

Tente colocar um trace no SID que está gerando a concorrência,
temos que entender oque este sid está fazendo?

Ele está gerando concorrência nos 4nodes ?

faça aquele select dos Wait’s em cada nó na v$ ao invês da gv$
para ver em qual está com mais incidencia.

A partir disso verifique os serviços como estão configurados,
se está tudo balanceado direitinho …

[Ricardo Portilho ProniParticipante]

Oi.

Se a Wait de library está alta, e o gerenciamento automático de memória deixou sua Shared com 12G, certamente você tem SQLs muito executados sem Bind, como um loop provavelmente.
Você não precisa achar o SQL que está esperando por esta Wait, você tem que achar os SQLs que estão causandoe sta Wait.

Este SQL vai dizer quais os SQLs parecidos mais repetidos. Coloque o resultado aqui pra gente.

SELECT SUBSTR(SQL_TEXT, 1, 100) SQL_TEXT, COUNT(SQL_TEXT) QTD FROM V$SQL HAVING COUNT(SQL_TEXT) > 100 GROUP BY SUBSTR(SQL_TEXT, 1, 100) ORDER BY QTD;

[vieriParticipante]

E ai Everson o grande Portilho
nos deu um excelente norte.

Realmente não é um bom sinal o Gerenciamento automático pedir
12G de shared pool.

Poste o resultado da query ai pra gente.

[]s

[eversonpizaParticipante]

Rodei o select e ele trouxe apenas 11 linhas a menor com 118 repetições e a maior com 12000, média de umas 800 repetições.

Só que nenhuma das consultas apresentadas é novas, todas já existiam no sistema.

Outra informação, o CURSOR_SHARING esta como FORCE.

[Ricardo Portilho ProniParticipante]

O CURSOR_SHARING=FORCE permitiu você sobreviver até hoje com estes SQLs não compartilhados.
Sugiro colocar BINDs pelo menos no de 12000 repetições, certamente seu ambiente irá melhorar.

[eversonpizaParticipante]

Fazendo alguns testes aqui percebi uma situação onde ele sempre gera essa contenção, é na conexão com o sqlplus em modo silent (-s) usando TWO_TASK do RAC, sem o ‘-s’ vai numa boa, ou se usar um two_task apontando para um determinado nó tb vai na boa, mesmo em modo silent.

Para fazer o teste usei um usuário que ninguém estava usando, e em outra shell vi que a conexão estava esperando por ‘library cache lock’.

[vieriParticipante]

Mas ela estava esperando…

Para resolver o problema vc tem que encontrar a
sessão que gerá a espera.

Seu ambiente está com a shared_pool e o dicionário de dados no gargalo.

Pegue a query que executou 12mil vezes e taca BIND nelas.
inclusive nas outras.

Leva fé, já tive que rodar a empresa implorando para todos usarem Binds, em um e-commerce que parava de 1 em 1 hora por time out
de Latch.

[vieriParticipante]

O fato delas já existirem não isenta elas de culpa,
seu sistema já era um vulcão prestes a explodir, quando chegou no seu limite começou a aparecer diretamente esss concorrência.

[Autor]

Posts