Identificar um vazamento de cursor no Oracle

Descrição

Este artigo descreve como identificar qual cursor do Oracle não está sendo fechado ou reutilizado em cada execução que leva ao seguinte erro do Oracle:

"ORA-01000: maximum open cursors exceeded."

É importante ser capaz de diagnosticar qual cursor está sendo 'vazado' (não fechado) para identificar qual parte do aplicativo é responsável por gerenciar o cursor, Oracle, ArcGIS ou o desenvolvedor do aplicativo.

Solução ou Solução Provisória

As instruções fornecidas demonstram como identificar cursores que não estão sendo fechados ou reutilizados por um aplicativo para uma determinada sessão do Oracle.

1. Identifique a sessão por seu nome de usuário Oracle para recuperar o valor sid. Conecte-se à instância Oracle como SYS ou SYSTEM ou a um usuário com privilégios de DBA usando SQL*Plus. Execute a seguinte instrução SQL para obter o valor sid da sessão:

SQL> SELECT sid FROM v$session WHERE username = 'TOM';

       SID
----------
       135

O valor sid é usado na próxima etapa para identificar a sessão a ser investigada.

2. Liste os endereços da instrução SQL da sessão, que têm mais de uma referência ativa. Usando o valor sid da instrução anterior, execute uma consulta na visualização v$open_cursor.

SQL> SELECT COUNT(*), address
  2  FROM v$open_cursor
  3  WHERE sid = 135
  4  GROUP BY address HAVING COUNT(address) > 1 ORDER BY COUNT(*);

  COUNT(*) ADDRESS
---------- --------
         2 35E6083C
         2 35B77834
         2 35E686B4
         3 35F97908

O resultado lista cada cursor, que foi aberto pela sessão mais de uma vez em ordem decrescente.

3. Obtenha a instrução SQL real para cada endereço retornado na etapa 2. Isso pode ser feito consultando a visualização v$sql usando o valor do endereço.

SQL> SELECT sql_fulltext
  2  FROM v$sql
  3  WHERE address = '35F97908';

SQL_FULLTEXT
-------------------------------------------------------------------------------
SELECT  OBJECTID, SEG_ID, SYMBOL, PIPE_SIZE, SLOPE, US_INV,
DS_INV, ACC_NO, SEW_NO, MATERIAL, SEW_SHAPE, HEIGHT, WIDTH,
INST_YEAR, TV_LAST, DRAIN_AREA, MEAS_REF, SEP_COMB, PUB_PRI,
QUEST,  SEG_TYPE, SSAD_LEN, V__43.st_SHAPE$, V__43.st_len$, 
V__43.st_points, V__43.st_numpts, V__43.st_entity, V__43.st_minx,
V__43.st_miny, V__43.st_maxx,V__43.st_maxy, V__43.st_area$,
V__43.st_len$, V__43.st_rowid FROM
(SELECT b.OBJECTID, b.SEG_ID, b.SYMBOL, b.PIPE_SIZE, b.SLOPE, b.US_INV,
b.DS_INV, b.ACC_NO, b.SEW_NO, b.MATERIAL, b.SEW_SHAPE,
b.HEIGHT, b.WIDTH, b.INST_YEAR, b.TV_LAST, b.DRAIN_AREA,
b.MEAS_REF, b.SEP_COMB, b.PUB_PRI, b.QUEST, b.SEG_TYPE,
b.SSAD_LEN, 1 st_SHAPE$, b.SHAPE.points as st_points,
b.SHAPE.numpts as st_numpts, b.SHAPE.entity as st_entity, b.SHAPE.minx as st_minx, 
b.SHAPE.miny as st_miny, b.SHAPE.maxx as st_maxx, b.SHAPE.maxy as st_maxy,
b.SHAPE.area as st_area$, b.SHAPE.len as
st_len$, b.rowid as st_rowid FROM TOM.sewers b
WHERE SDE.ST_EnvIntersects(b.SHAPE,:1,:2,:3,:4) = 1
AND b.OBJECTID NOT IN (SELECT /*+ HASH_AJ */ SDE_DELETES_ROW_ID
FROM TOM.D43 WHERE DELETED_AT IN (SELECT l.lineage_id
FROM SDE.state_lineages l WHERE l.lineage_name = :lineage_name1 AND l.lineage_id <= :state_id1)
AND SDE_STATE_ID = 0) UNION ALL SELECT a.OBJECTID, a.SEG_ID, a.SYMBOL, a.PIPE_SIZE, a.SLOPE,
a.US_INV, a.DS_INV, a.ACC_NO, a.SEW_NO, a.MATERIAL, a.SEW_SHAPE,
a.HEIGHT, a.WIDTH, a.INST_YEAR, a.TV_LAST, a.DRAIN_AREA,
a.MEAS_REF, a.SEP_COMB, a.PUB_PRI, a.QUEST, a.SEG_TYPE, a.SSAD_LEN, 2
st_SHAPE$ ,a.SHAPE.points as st_points, a.SHAPE.numpts as st_numpts,
a.SHAPE.entity as st_entity, a.SHAPE.minx as st_minx, a.SHAPE.miny as st_miny,
a.SHAPE.maxx as st_maxx, a.SHAPE.maxy as st_maxy, a.SHAPE.area as st_area$,
a.SHAPE.len as st_len$, a.rowid as st_rowid FROM TOM.A43 a, SDE.state_lineages SL
WHERE SDE.ST_EnvIntersects(a.SHAPE,:5,:6,:7,:8) = 1 
AND (a.OBJECTID, a.SDE_STATE_ID) NOT IN (SELECT /*+ HASH_AJ */ SDE_DELETES_ROW_ID, SDE_STATE_ID
FROM TOM.D43 WHERE DELETED_AT IN (SELECT  l.lineage_id FROM
SDE.state_lineages l WHERE l.lineage_name = :lineage_name2 AND l.lineage_id <= :state_id2)
AND SDE_STATE_ID > 0) AND a.SDE_STATE_ID = SL.lineage_id AND SL.lineage_name
= :lineage_name3 AND SL.lineage_id <= :state_id3) V__43

O resultado, nesse cenário, é uma consulta espacial versionada. Com base no que o aplicativo está fazendo, possivelmente ArcObjects personalizados, há três referências a essa instrução SQL. O aplicativo está abrindo três referências explícitas ou o aplicativo ArcGIS não está reutilizando a instrução SQL inicial.
Se for necessária mais assistência para identificar a causa do problema, entre em contato com o Suporte Técnico da ESRI.