오픈커서 문제
오라클 데이터베이스는 Oracle Identity Manager를 통해 여러 SQL문을 실행합니다.
Oracle Database에서 모든 SQL문은 실행을 위한 메모리를 특정 영역에 할당 받게 됩니다. Oracle PL/SQL을 사용하면 이 영역의 이름을 지정할 수 있습니다. 이 개인용 SQL영역을 컨텍스트 영역 또는 커서라고 합니다. 커서의 메모리는 공유풀에서 할당됩니다. 공유풀은 Oracle Database의 필수 메모리 구성요소로 특히 라이브러리 캐시에서 사용됩니다.
Renegade 세션이 라이브러리 캐시를 채우거나 수백만개의 SQL구문 분석 요청으로 CPU를 점유하지 못하게 커서의 수를 제한하도록 데이터베이스에 OPEN_CURSORS 변수를 설정해야 합니다. (레니게이드 세션에 대한 적절한 표현을 찾지 못했습니다. 배신자라고 하는데 사용자의 요청을 응답하지 않았다고 배신자라는 표현은 좀...)
OPEN_CURSORS 매개변수는 각 세션이 세션 당 열 수 있는 최대 커서 수를 설정합니다. 예를 들어 OPEN_CURSORS의 값이 1000으로 설정되면 각 세션은 한 번에 최대 1000개의 커서를 열 수 있습니다.
때로는 데이터베이스의 커서 수가 최대 한계를 초과하여 다음과 같은 오류가 발생합니다.
----------------------------------------------------------------
java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded ORA-00604: error occurred at recursive SQL level 1
% SFO 에서는 Open Cursor Alert(count) 메시지가 발생합니다.
----------------------------------------------------------------
해결방법
1. 데이터베이스의 SYS 스키마에 로그인하거나 DBA권한이 잇는 스키마로 로그인 하십시오.
2. 다음 SQL을 사용하여 오류를 발생시키는 세션을 찾으십시오.
----------------------------------------------------------------
select
a.value
, s.username
, s.sid
, s.serial#
from
v$sesstat a
, v$statname b
, v$session s
where
a.statistic# = b.statistic#
and s.sid=a.sid
and b.name = 'opened cursors current'
and s.username is not null;
-- 개인적으로 order by value desc 를 추가하고 싶습니다.
----------------------------------------------------------------
출력은 모든 세션의 세부 사항을 표시합니다. 최대 세션 ID를 볼 수 있습니다.
3. 열린 커서를 최대로 만드는 쿼리를 표시하려면 다음 SQL문을 실행하십시오.
----------------------------------------------------------------
select
sid
,sql_text
, count(*) as "OPEN CURSORS"
, USER_NAME
from
v$open_cursor
where
sid in ($SID);
--$SID 부분을 첫번째에서 문제가 있다고 판단되는 SID를 대입하면 됩니다.
----------------------------------------------------------------
최대 커서를 열고 후속 커서를 정상적으로 닫지않은 상위 쿼리가 표시됩니다.
일부 코드가 SQL 쿼리 위에서 실행중인 경우 코드에 액세스 할 수 있는 경우 Java 문, 결과 집합 또는 연결이 제대로 닫혔는지 확인하십시오. 코드가 연결을 닫지 않으면 열려있는 모든 연결을 올바르게 닫아 코드의 메모리 누수를 저장하고 데이터베이스 메모리를 저장하십시오.
----------------------------------------------------------------
max(a.value) as highest_open_cur,
p.value as max_open_cur
FROM
v$sesstat a
, v$statname b
, v$parameter p
WHERE
a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;
JAVA를 통해 호출한 경우 SESSION의 CLOSE를 하거나 PL_SQL의 경우 COMMIT을 통한 세션의 종료, 혹은 커서를 닫는 구문을 사용해야 하는 것 같습니다.
4. OPEN_CURSORS 매개변수의 값을 충분히 높게 설정했는지 확인하려면 다음과 같이 최대 커서에 대한 환경 설정을 v$sesstat를 조회해 확인하세요.
----------------------------------------------------------------
SELECT max(a.value) as highest_open_cur,
p.value as max_open_cur
FROM
v$sesstat a
, v$statname b
, v$parameter p
WHERE
a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;
----------------------------------------------------------------
세션이 한계에 가깝게 실행중인 경우 OPEN_CURSORS 매개변수의 값을 늘리세요.
오라클 트러블 슈팅 가이드에 나온 이야기는 이정도이지만, 실제 운영 시스템에 작성된 PL / SQL에 보면 대게 LOOP 문내에서 커서를 열기만 하고 닫지 않는 케이스가 가장 많습니다. 그리고 대게 암시적 커서를 사용하여 루프 되는 데이터의 총량을 확인하지 않고 사용하는 경우가 있습니다. 제가 사용하는 환경에서는 OPEN_CURSORS 매개변수가 대게 1000이기에 이 부분을 확인해 수정시키는 중 입니다.
또한 JAVA에서 반복적으로 SQL 수행 하는 프로그램에서 COMMIT이나 세션에 대한 클로즈 처리가 없이 지속적으로 SQL을 전송하는 프로그램도 자주 있습니다. 이런 부분을 확인해보면 될 것 같습니다. (전자정부에서는 대게 기본값으로 자동 커밋처리됩니다. )
댓글 없음:
댓글 쓰기