Identificando e resolvendo os Eventos: Cursor: Pin S e Cursor: Pin S wait on X

Por Victor Armbrust
Postado en janeiro 2013

Gerenciamento otimizado de memória, monitoramento de processos, performance satisfatória em queries e processos administrativos, backup em tempo recorde, relatórios executados em minutos, planos de execução extremamente performáticos, enfimm garantir performance em um Banco de Dados Oracle é sempre uma das muitas tarefas de um DBA Oracle.

No Oracle Database existem diversos eventos que ocorrem enquanto o Banco de Dados está em execução. Alguns deles podem ou não impactar o tempo de resposta de processos, gerar contenções ou concorrências ou ainda algum tipo de "hang"/ travamento no ambiente. O importante é entender o funcionamento destes eventos no Banco de Dados e encontrar assim a melhor maneira para resolver problemas de performance.

O que são Eventos de Espera (Wait Events)?

Em algum determinado momento, processos do Oracle estão ocupados executando alguma instrução específica ou aguardando que algo específico aconteça. Por exemplo, a execução de um cálculo qualquer dentro de bloco PL/SQL significa um determinado processo pode estar utilizando CPU, o que significa que este processo pode estar apenas em "busy" não necessariamente em "wait", apenas em execução. Por outro lado, um processo como o LGWR pode estar aguardando que o Sistema Operacional confirme que uma informação do Log Buffer foi gravado com sucesso da memória para os online redo log files em disco, neste caso o processo LGWR estaria em "wait" não em "busy".

Na maioria das vezes, o próprio Oracle encontra a solução ou tratativa correta para cada evento de espera, sendo assim, a monitoração preventiva de eventos que ocorrem no banco de dados pode ajudar a identificar eventos com longa espera ou travados. Porém, em alguns casos, podem ocorrer eventos de espera no Banco de Dados e consequentemente a intervenção do DBA será necessária,

Afinal, o que é o evento: Cursor: Pin S wait on X?

Em alguns casos onde aplicações utilizam um alto número de cursores abertos simultaneamente, uma sessão pode requisitar o acesso a uma área de memória para utilização de um cursor (shared mutex pin) em um objeto que já está sendo utilizado por outro cursor em modo exclusivo (exclusive mutex pin).

O Conceito de "mutex" foi implementado no Oracle Database 10gR2, com o objetivo de proteger dados (ou recursos) de acesso concorrente. (Mutual Exclusion). Em outras palavras: Toda vez que o Oracle obtém de um mutex, seu valor é incrementado. Toda vez que o mutex é liberado, seu valor é decrementado.

O evento "Cursor: Pin S wait on X" tem ligação direta com o evento "Cursor Pin S". Veja abaixo:

Cursor: Pin S : Este é um evento de espera que acontece quando uma sessão precisa de um Mutex específico para um cursos específico (em modo Share). Neste caso, o Oracle atualiza contadores internos para obter o mutex desejado.

É importante lembrar que o acesso aos mutexes/contadores não são concorrentes, ou seja, se outras sessões tentarem obter um mutex que já esteja em uso, então o evento "cursor: pin s" irá ocorrer.

Cursor: Pin S wait on X: Este é um evento de espera que ocorre quando o Oracle não consegue obter um mutex em modo compartilhado (share) pois existe outra sessão utilizando o mesmo mutex em modo exclusivo (exclusive X).

Resumo:

Cursor: Pin S : Ocorre quando o Oracle precisa de um mutex específico para um cursor específico, mas não consegue obter pois não consegue atualizar seus contadores internos (Não porque outra sessão está utilizando o mutex referenciado em modo exclusivo)

Cursor: Pin S wait on X : Ocorre quando o Oracle precisa de um mutex específico em modo Share(S) para um cursor específico, porém outra sessão está usando o mesmo mutex em modo exclusivo(X).

Onde encontrar informações sobre eventos de espera?

Informações sobre eventos de espera podem ser encontradas em 3 (três) views:

  • V$SESSION_WAIT mostra os eventos para cada sessão que estavam em "wait" ou estão em "wait"
  • V$SYSTEM_EVENT mostra o número total de vezes que todas as sessões aguardaram por eventos dessa view.
  • V$SESSION_EVENT similar a view V$SYSTEM_EVENT, mas mostra cada evento de wait para sessões atuais.

Exemplo de sessões em wait e do evento Cursor: Pin S wait on X:

 SID  SERIAL# EVENT                   		WAIT 	  STATUS     MACHINE  
 ---- ------- -------------------------------	------  ---------- ---------
 3257     101 db file scattered read      	0       ACTIVE     servidor1
 3258      99 db file read by other session 	2,555   ACTIVE     servidor1
 3259     111 PX Deq: read by other session 	33,555  ACTIVE     servidor1
 3260     110 PX Deq: Execution Msg       	0 	ACTIVE     servidor1
 3261     102 PX Deq: Execution Msg      	0 	ACTIVE     servidor1
 3262     114 db file scattered read      	0 	ACTIVE     servidor1
 3289      86 db file scattered read      	0 	ACTIVE     servidor1
 3267     169 db file scattered read      	0 	ACTIVE     servidor1
 3268     104 db file Cursor Pin S Wait on X	1,321   ACTIVE     servidor1
 3269      96 PX Deq: Cursor Pin S Wait on X	1,321   ACTIVE     servidor1
 3270      10 PX Deq: Execute Reply       	0 	ACTIVE     servidor1
 3279     121 PX Deq: Execution Msg       	0 	ACTIVE     servidor1
 3265     317 PX Deq: Execution Msg       	0 	ACTIVE     servidor1
 3291     307 PX Deq: i/o slave wait      	2,531	ACTIVE     servidor1
 3292     455 PX Deq: i/o slave wait       	2,531   ACTIVE     servidor1
 3298     322 PX Deq: i/o slave wait       	2,531   ACTIVE     servidor1

Mais informações sobre sessões em espera pelo evento Cursor: Pin S wait on X

Para verificar mais informações sobre sessões em espera por cursores, pode-se utilizar os scripts a seguir:

- Verificar sessões que estão bloqueando outras sessões

select p2raw, p2/power(16,8) blocking_sid, p1 mutex_id, sid blocked_sid
   from v$session
  where event like 'cursor:%'
    and state='WAITING'
/

- Verificar histórico de sessões que aguardaram pelo evento

select requesting_session, blocking_session, sleep_timestamp, mutex_type from v$mutex_sleep_history
/

- Identificar sessões que estão aguardando

select * from v$mutex_sleep
/

- Identificar sessões com maior tempo de Banco de Dados (Load) por espera em eventos de Cursor Pin%

select EVENT, WAIT_TIME_MILLI as "WAIT(MS)", WAIT_COUNT, 
round((WAIT_TIME_MILLI*100)/(select sum(WAIT_TIME_MILLI) from 
v$event_histogram),3) as "TOTAL DB WAIT(%)"
from  v$event_histogram
where WAIT_TIME_MILLI > 512
and event like '%cursor%'
group by EVENT, WAIT_TIME_MILLI, WAIT_COUNT
order by WAIT_TIME_MILLI desc
/

- Identificar SQLID com evento de espera em Cursor Pin%

select count(*), sql_id,sql_child_number,session_state,blocking_session_status,event,wait_class
  from DBA_HIST_ACTIVE_SESS_HISTORY 
  where snap_id between 18085 and 18086
  and event like '%cursor: pin S%'
  group by sql_id,sql_child_number,session_state,blocking_session_status,event,wait_class
/ 

Verificando informações sobre Cursores e Sessões com espera em Cursor Pin

Uma vez identificadas as sessões que estão com espera nesse evento, pode-se obter mais informações ou coletas sobre as mesmas. Veja a seguir:

- Identificar consumo pelo SQLID que está em espera por evento Cursor Pin%

select sql_id,optimizer_cost o_cost,optimizer_mode o_mode ,SHARABLE_MEM mem ,
        version_count ver_c,fetches_total fetc_t ,END_OF_FETCH_COUNT_TOTAL cnt_total,
        EXECUTIONS_TOTAL exe ,PARSE_CALLS_TOTAL pars,DISK_READS_TOTAL disk_t,
        BUFFER_GETS_TOTAL buffer_t ,ROWS_PROCESSED_TOTAL rows_t ,cpu_time_total cpu_t,
        round(elapsed_time_total/1000000) elapsed_s ,iowait_total
from dba_hist_sqlstat
where snap_id between 18085 and 18086
and sql_id in ('<NUMERO_DO_SQLID_1>','<NUMERO_DO_SQLID_2>')
/

- Coletar número de cursores abertos no Banco de Dados, e cursores em utilização

select      user_process username,
       "Recursive Calls",
       "Opened Cursors",
       "Current Cursors"
    from  (
       select  nvl(ss.USERNAME,'ORACLE PROC')||'('||se.sid||') ' user_process,
                       sum(decode(NAME,'recursive calls',value)) "Recursive Calls",
                       sum(decode(NAME,'opened cursors cumulative',value)) "Opened Cursors",
                       sum(decode(NAME,'opened cursors current',value)) "Current Cursors"
      from    v\$session ss,
                v\$sesstat se,
                 v\$statname sn
      where   se.STATISTIC# = sn.STATISTIC#
      and     (NAME  like '%opened cursors current%'
      or       NAME  like '%recursive calls%'
      or       NAME  like '%opened cursors cumulative%')
      and     se.SID = ss.SID
      and     ss.USERNAME is not null
      group   by nvl(ss.USERNAME,'ORACLE PROC')||'('||se.SID||') '
   )
   orasnap_user_cursors
   order      by USER_PROCESS,"Recursive Calls"
/

Resolvendo eventos Cursor Pin S e Cursor Pin S wait on X

Resolver este evento não é uma tarefa que envolve apenas um comando ou apenas um parâmetro. Deve ser feita uma verificação completa do Banco de Dados (Através dos scripts acima demonstrados) juntamente com análise de parâmetros e workload do sistema.

Existem algumas situações onde são identificados Bugs ou correções para problemas específicos, como por exemplos nos Notes abaixo do MOS (My Oracle Support):

  • Bug 9694101 - Hang / deadlock between "cursor: pin S wait on X" and "library cache lock" involving dictionary objects [ID 9694101.8]
  • "Cursor: Pin S Wait On X" Contention Mutex Sleep Reason Primarily ' kkslce [KKSCHLPIN2]' [ID 1268724.1]
  • Bug 7234778 - Unnecessary "cursor: pin S wait on X" waits [ID 7234778.8]

entre outros..

A seguir alguns itens que podem ser verificados para minimizar ou solucionar os eventos de espera Cursor: Pin S e Cursor: Pin S wait on X

- Desabilitar a utilização de Mutex

Como citado acima, esse recurso foi implementado na versão 10gR2, porém pode ser desabilitado no Oracle Database. De qualquer forma MUITA ATENÇÃO ao executar esta alteração, pois o comportamento do Banco de Dados pode se alterar durante a execução de cursores.
**P.S: Essa alteração é recomendada na versão 10gR2 para o HP-UX, conforme Note do MOS: (216205.1). Na maioria das plataformas não existe essa necessidade.

_kks_use_mutex_pin=FALSE   

alter system set "_kks_use_mutex_pin"=false scope=spfile;
shutdown immediate
startup
/

- Executar um FLUSH na Buffer Cache

Ao executar um FLUSH na Database Buffer Cache todos os blocos de objetos alocados em memória serão descarregados, sendo assim, possíveis situações de HOT BLOCKS em memória serão limpos. Isso pode ajudar na liberação de novos mutexes

alter system flush buffer_cache;

- Validar gerenciamento de Cursores

Existe uma relação com os eventos de espera em Cursor Pin S com os parâmetros open_cursors e session_cached_cursors. É muito importante garantir que os mesmos estejam corretamente definidos.

open_cursors : Número máximo de Cursores abertos simultaneamente no Banco de Dados.
session_cached_cursors : Número máximo de Cursores que podem ser gerenciados em cache por sessão.

alter system set open_cursors=1000 scope=both;
alter system set session_cached_cursors=50 scope=both;

- Validar código da aplicação

É importante ainda lembrar que se muitos cursores são abertos ao mesmo tempo para solucionar uma mesma tarefa em um mesmo objeto, o evento de espera Cursor Pin S poderá ocorrer. Sendo assim o código de sua aplicação deve ser otimizado utilizando cursores em momentos estratégicos e de maneira coerente com a real necessidade do aplicativo.

Conclusões

Os eventos de espera são constantes em um Banco de Dados Oracle e devem ser interpretados não como problemáticos porém como pontos de monitoramento e atenção, visando manter o sistema com performance satisfatória e comportamento adequado.
Neste artigo pudemos observar o comportamento e funcionamento de eventos de espera em Curores, bem como maneiras de solucionar estes eventos em caso de longa espera. É bastante importante manter o banco de dados sempre atualizado em níveis de PSU e CPU corretos para minimizar possíveis BUGS além de utilizar corretamente os recursos que o Oracle Databas oferece para o desenvolvimento de suas aplicações.




Victor Armbrust é DBA há 10 anos, especialista em Banco de Dados Oracle e Bacharel em Ciências da Computação. Com sólidos conhecimentos em Banco de Dados e Sistemas operacionais, possui certificações OCP 10g/11g, OCE 11g Performance Tuning, OCE 10g RAC, Exadata Implementation Specialist, OCA Mysql 5, LPIC-3, OCA Solaris 10, Data Warehouse Implementation Specialist entre outras.
Instrutor Oracle University e Consultor de Banco de Dados na Oracle Advanced Customer Support Services.