'cursor'에 해당되는 글 2건

  1. 2014.03.23 04 커서공유
  2. 2010.04.01 ORA-01000: maximum open cursors exceeded" 조사
ORACLE/ADMIN2014. 3. 23. 22:45

04 커서공유

반응형

04 커서공유


(1) 커서란?

공유 커서 (shared cursor) : 라이브러리 캐시에 공유돼 있는 Shared SQL Area
세션 커서 (session cursor) : Private SQL AREA에 저장된 커서
애플리케이션 커서 (application cursor) : 세션 커서를 가리키는 핸들

그림 7

라이브러리 캐시에 공유돼 있는 커서를 실행할 때는 우선 PGA 영역에 메모리를 할당
Private SQL area 라고 하는데 ， Persistent Area 와 Runtime Area로 나뉜다.
Shared SQL Area를 읽어 커서를 실행하는 데 필요한 정보들을 Private SQL Area에 담고，
공유 커서를 가리키는 포인터를 유지한다.그리고 커서의 상태정보도 관리한다.
커서 를 실행하기 위한 이런 준비과정을 "커서를 오픈한다" 고 표현하고，
PGA에 저장된 커서 정보(즉,파싱된 SQL문과 문장을 수행하능데 필요한 기타 정보)를 또
한 '커서' 라고부른다.

PGA에 있는 커서를 핸들링하려면 JAVA, VB, Pro*C, PL!SQL 같은 클라이언트 애플리케이션에도
리소스를 할당

(2) 커서 공유

######################
커서 공유 테스트
######################

-- sys유저 접속
SQL> grant select_catalog_role to scott;
Statement Processed.

SQL> alter system flush shared_pool;
Statement Processed.

-- SCOTT유저 접속
SQL> select /* cursor test */ empno, ename, job, sal, deptno 
from emp 
where empno = 7788;

EMPNO ENAME      JOB       SAL       DEPTNO 
----- ---------- --------- --------- ------ 
 7788 SCOTT      ANALYST        1000     20

1 rows selected.

SQL> select sql_id, parse_calls, loads, executions, invalidations 
      , decode(sign(invalidations), 1, (loads-invalidations),0) reloads 
from v$sql 
where sql_text like '%cursor test%' 
and sql_text not like '%v$sql%';

SQL_ID        PARSE_CALLS LOADS     EXECUTIONS INVALIDATIONS RELOADS   
------------- ----------- --------- ---------- ------------- --------- 
896pksq7c53f2           1         1          1             0         0

1 rows selected.

SQL> select /* cursor test */ empno, ename, job, sal, deptno 
from emp 
where empno = 7788;

EMPNO ENAME      JOB       SAL       DEPTNO 
----- ---------- --------- --------- ------ 
 7788 SCOTT      ANALYST        1000     20

1 rows selected.

SQL> select /* cursor test */ empno, ename, job, sal, deptno 
from emp 
where empno = 7788;

EMPNO ENAME      JOB       SAL       DEPTNO 
----- ---------- --------- --------- ------ 
 7788 SCOTT      ANALYST        1000     20

1 rows selected.

SQL> select sql_id, parse_calls, loads, executions, invalidations 
      , decode(sign(invalidations), 1, (loads-invalidations),0) reloads 
from v$sql 
where sql_text like '%cursor test%' 
and sql_text not like '%v$sql%';

SQL_ID        PARSE_CALLS LOADS     EXECUTIONS INVALIDATIONS RELOADS   
------------- ----------- --------- ---------- ------------- --------- 
896pksq7c53f2           3         1          3             0         0

1 rows selected.
  • parse_calls: 라이브러리 캐시에서 SQL 커서를찾으려는요청 횟수
  • loads: 하드파싱을 거친 SQL 실행계획을 라이브러리 캐시에 적재한 횟수
  • executions : SQL을 수행한 횟수
  • invalidations : 커서가 무효화된 횟수. 커서가 참조하고 있는 오브젝트에 중요한 변
    화가일어났음을의미함
#####################################
다른세션 접속후 커서공유 테스트
#####################################

SQL> select /* cursor test */ empno, ename, job, sal, deptno 
from emp 
where empno = 7788;

EMPNO ENAME      JOB       SAL       DEPTNO 
----- ---------- --------- --------- ------ 
 7788 SCOTT      ANALYST        1000     20

1 rows selected.


SQL> select /* cursor test */ empno, ename, job, sal, deptno 
from emp 
where empno = 7788;

EMPNO ENAME      JOB       SAL       DEPTNO 
----- ---------- --------- --------- ------ 
 7788 SCOTT      ANALYST        1000     20

1 rows selected.

SQL> select sql_id, parse_calls, loads, executions, invalidations 
      , decode(sign(invalidations), 1, (loads-invalidations),0) reloads 
from v$sql 
where sql_text like '%cursor test%' 
and sql_text not like '%v$sql%';

SQL_ID        PARSE_CALLS LOADS     EXECUTIONS INVALIDATIONS RELOADS   
------------- ----------- --------- ---------- ------------- --------- 
896pksq7c53f2           5         1          5             0         0

1 rows selected.

다른 세션에서 같은 SQL을 수행할 때도 이전 세션에서 적재한 커서를 재사용했음을
알수있다.

###################################
통계 재생성후 커서공유 테스트
###################################

SQL> execute DBMS_STATS.GATHER_TABLE_STATS  
(ownname => USER, tabname => 'EMP' 
,no_invalidate => FALSE 
);

PL/SQL executed.
SQL Execution Time > 00:00:05.367

SQL> select /* cursor test */ empno, ename, job, sal, deptno 
from emp 
where empno = 7788;

EMPNO ENAME      JOB       SAL       DEPTNO 
----- ---------- --------- --------- ------ 
 7788 SCOTT      ANALYST        1000     20

1 rows selected.

SQL Execution Time > 00:00:00.062
Total Elapsed Time > 00:00:00.062

SQL> select /* cursor test */ empno, ename, job, sal, deptno 
from emp 
where empno = 7788;

EMPNO ENAME      JOB       SAL       DEPTNO 
----- ---------- --------- --------- ------ 
 7788 SCOTT      ANALYST        1000     20

1 rows selected.

SQL Execution Time > 00:00:00.062
Total Elapsed Time > 00:00:00.062

-------------------------[Start Time: 2014/03/04 18:39:12]-------------------------
SQL> select sql_id, parse_calls, loads, executions, invalidations 
      , decode(sign(invalidations), 1, (loads-invalidations),0) reloads 
from v$sql 
where sql_text like '%cursor test%' 
and sql_text not like '%v$sql%';

SQL_ID        PARSE_CALLS LOADS     EXECUTIONS INVALIDATIONS RELOADS   
------------- ----------- --------- ---------- ------------- --------- 
896pksq7c53f2           2         2          2             1         1

1 rows selected.

SQL Execution Time > 00:00:00.031
Total Elapsed Time > 00:00:00.047

쿼리를 다시 수행한 후에 v$sql을 다시 조회해 보면， 적재 횟수가 아래처럼 2로 증가한
것을볼수있다.

라이브러리 캐시에 있는 커서틀이 여러 세션에 의해 공유되면서 반복 재사용되는 것
공유된 커서를 사용할 때는 최적화 및 Row-Source Generation 단계를 생략하고
곧바로 실행 단계로 넘어가므로 보다 효율적이고 빠르게 SQL을 수행한다

커서가 공유되려면 커서를 식별하는 키 값이 같아야 함
-> 라이브러리 캐시에서 커서를 식별하기 위해 시용되는 키 값은 'SQL 문장 그 자체'
-> SQL문을 구성하는 전체 문자열이 이름 역할을 한다는 뜻
-> SQL_ID와 SQL FULLTEXT는 1:1로 대응
SQL문 중간에 작은 공백문자 하나만 추가하더라도 서로 다른 SQL 문장으로 인식해 새로운
SQL_ID를발급받게 된다. 즉 커서가 공유되지 않는다.

(3) Child 커서를 공유하지 못하는 경우

8QL마다 하나의 Parent 커서를 가지며， Child 커서는 여러 개일 수 있다.
실제 수행에 필요한 정보는 Child 커서에 담기므로 적어도 한 개의 Child 커서를 갖는다.
v$sqlarea는 Parent 커서 정보를 보여주고36) ' v$sql은 Child 커서 정보를 보여준다.
아래는 SC0TT과 HR 스키마 각각에 EMP 테이블을 만들고 각 계정으로 로그인 해서
select * from emp 쿼리를 수행한 후에 v$sqlarea와 v$sql을 쿼리했을 때의 결과를 보
이고 있다.

##########################
자식커서의 공유 테스트
##########################

C:\WINDOWS\system32>sqlplus "/as sysdba"


SQL> conn /as sysdba
연결되었습니다.
SQL> create table hr.emp as select * from scott.emp;

테이블이 생성되었습니다.

SQL> alter system flush shared_pool;

시스템이 변경되었습니다.

SQL> conn scott/tiger
연결되었습니다.
SQL> select * from emp where empno=7698;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30

SQL> conn hr/hr
연결되었습니다.
SQL> select * from emp where empno=7698;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30


== v$sqlarea는 부모커서를 확인
SQL>  conn /as sysdba
연결되었습니다.
SQL> select sql_id, version_count, optimizer_mode, address, hash_value
  2  from v$sqlarea
  3  where sql_text like 'select * from emp where%'
  4  and sql_text  not like 'v$sql';

SQL_ID        VERSION_COUNT OPTIMIZER_ ADDRESS  HASH_VALUE
------------- ------------- ---------- -------- ----------
5tq0u8y4zd3ta             2 ALL_ROWS   3D9C5BFC 2314637098


== v$sql는 자식커서를 확인
SQL> select sql_id, child_number, optimizer_mode, address, hash_value, parsing_user_id
  2  from v$sql
  3  where sql_text like 'select * from emp where%'
  4   and sql_text  not like 'v$sql';

SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS  HASH_VALUE PARSING_USER_ID
------------- ------------ ---------- -------- ---------- ---------------
5tq0u8y4zd3ta            0 ALL_ROWS   3D9C5BFC 2314637098              84
5tq0u8y4zd3ta            1 ALL_ROWS   3D9C5BFC 2314637098              85


== v$sqlarea 에 동일문장의 버전이 몇개인지 볼수 있다.
select * from v$sqlarea order by version_count desc;

하나의 SQL문장이 여러개 Child 커서를 갖게 되는 대표적인 이유
1. SQL에서 참조하는 오브젝트명이 같지만 SQL을 실행한 사용자에 따라 다른 오브젝트를 가리킬때
2. 참조 오브젝트가 변경돼 커서가 무효화되면 이후 그 커서를 처음 사용하려는 세션에
의해 다시 하드파싱돼야 하는데 특정 세션이 아직 기존 커서를 사용 중(Pin)일 때
3. 옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를 때
4. 입력된 바인드 값의 길이가 크게 다를 때
5. NLS 파라미터를 다르게 설정했을 때
6. SQL 트레이스를활성화했을때

해당 딕셔너리를 참조해라. ====> V$SQL_SHARED_CURSOR

SQL> conn scott/tiger
연결되었습니다.
SQL> conn /as sysdba
연결되었습니다.
SQL> alter system flush shared_pool;

시스템이 변경되었습니다.

SQL> conn scott/tiger
연결되었습니다.
SQL> alter session set optimizer_mode=first_rows;

세션이 변경되었습니다.

SQL> select * from emp where empno=7698;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30

SQL> alter session set optimizer_mode=all_rows;

세션이 변경되었습니다.

SQL> select * from emp where empno=7698;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30


SQL> conn /as sysdba
연결되었습니다.
SQL> select sql_id, version_count, optimizer_mode, address, hash_value
  2  from v$sqlarea
  3  where sql_text like 'select * from emp where%'
  4  and sql_text  not like 'v$sql';

SQL_ID        VERSION_COUNT OPTIMIZER_ ADDRESS  HASH_VALUE
------------- ------------- ---------- -------- ----------
5tq0u8y4zd3ta             2 ALL_ROWS   3D9C5BFC 2314637098



SQL> select child_number, child_address, optimizer_mode_mismatch, optimizer_mismatch
  2  from V$SQL_SHARED_CURSOR
  3  where SQL_ID = '5tq0u8y4zd3ta'
  4  and ADDRESS  = '3D9C5BFC';

CHILD_NUMBER CHILD_AD O O
------------ -------- - -
           0 37DF4684 N N
           1 37FF5A94 Y N


SQL> select *
  2  from V$SQL_SHARED_CURSOR
  3  where SQL_ID = '5tq0u8y4zd3ta'
  4  and ADDRESS  = '3D9C5BFC';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U
 T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B
------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
5tq0u8y4zd3ta 3D9C5BFC 37DF4684            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N
 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
5tq0u8y4zd3ta 3D9C5BFC 37FF5A94            1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N
 N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N

Version Count 수치가 높은 SQL 일수록 커서를 탐색하는 데 더 많은 시간을 소비하므
로 library cache 래치에 대한 경합 발생 가능성을 증가시킨다.

v$sql_shared_cursor 다이나믹 뷰는 새로운 Child 커서가 왜 기존 Child 커서와 공유
못하는지 확인

(4) Parent 커서를 공유하지 못하는 경우

1. 공백 문자 또는 줄바꿈
SELECT * FROM CUSTOMER;
SELECT *   FROM CUSTOMER;
2 . 대소문자구분
SELECT * FROM CUSTOMER;
SELECT * FROM Customer;
3. 태이블 Owner 명시
SELECT * FROM CUSTOMER;
SELECT * FROM HR.CUSTOMER;
4. 주석 (Comment)
SELECT * FROM CUSTOMER;
SELECT /* 주석문 */ * FROM CUSTOMER;
5. 용티마이져 힌트 사용
SELECT * FROM CUSTOMER;
SELECT /*+ all_rows */ * FROM CUSTOMER;
6. 조건 절 비교값
SELECT * FROM CUSTOMER WHERE CUST_ID = '0000001';
SELECT * FROM CUSTOMER WHERE CUST_ID = '0000002';
이 외에도 더 다양한 케이스가 있을 것이다. 1. 2. 4번은 그 실행계획이 100% 같다.

그럼에도 문자열을 조금 다르게 기술했다는 이유 때문에 서로 다른 SQL로서 각각
하드파싱을 일으카고 서로 다른 공간을 차지하면서 Shared Pool을 낭비하게 된다. 이런
비효율을 줄이고 공유 가능한 형태로 SQL을 작성하려면 개발 초기에 SQL 작성 표준을
정해 이를 준수하도록 해야 한다.

5번은 의도적으로 실행계획을 달리 가져가려는 것이므로 논외로 하고 라이브러리 캐시
효율과 직접 관련이 큰 것은 6번 같은 패턴이다. 즉 조건절에 바인드 변수를 사용하지 않
고 서로 다른 Literal 값으로 문자열을 대체하는 경우다.
만약 OLTP성 업무를 처리하는 애플리케이션에서 6번과 같은 패턴으로 SQL을 개발한
다변 결코 좋은 성능을 보장받을 수 없다. 개별 쿼리 성능으로 보면 잘 느끼지 못하지만
동시 트랜잭션이 몰리는 peak 시간대에 시스템을 장애 상황으로 몰고 가는 주범이다.

==================================================================================================================
공유풀은 힙(heap)으로 불리는 OS메모리 공간으로 구성됨.
힙(heap)은 헤더와 하나이상의 메모리익스텐트로으로 구성됨.
메모리익스텐트는 지속적으로 할당과 반납이 반복되며 여러개 작은조각으로 나누어짐.
메모리 영역이 작은단위로 나누어 지는 것을 공유풀단편화(FRAGMENTATION)이라 함.
단편화로 인해 쪼개진 영역은 청크라고 불리며 프리리스트에 의해 관리됨.
한번 사용된 청크는 다시 프리리스트에 등록되기 전까지 공유풀 LRU리스트에서 관리됨
LRU알고리즘을 이용하여 청크의 재사용률을 높이기 위해서임.

공유풀에 메모리 할당과정
1. 새로운 SQL에 대해 파스 수행하려면 힙영역 새로운 빈공간 할당받아야함.
2. 프리리스트에서 필요한 크기의 프리청크 서칭
3. 프리리스트 서칭후 있으면 힙영역 할당. 없으면 LRU리스트 사용가능 청크를 서칭
4. LRU리스트에도 없으면 4031에러 발생후 SQL파스 실패

예> SQL파스를 위해 256바이트가 필요할�
1. 쉐어드풀 래치획득후 프리리스트로 부터 256바이트의 청크를 검색.

  • 이과정에서 래치를 획득하지 못하면 latch:shared pool 대기 이벤트를 발생시키며 획득가능할때까지 대기.
    2. 256바이트의 프리청크를 찾았다면 해당 청크를 익스텐트에 할당.
  • 이과정에서 256바이트의 프리청크가 없어서 찾지 못했다면 더큰크기의 프리청크를 검색
    3. 400바이트의 프리청크를 찾았다면 필요한 크기의 256바이트와 144바이트의 크기로 쪼갬.
    256바이트 청크를 익스텐트에 할당. 144바이트의 청크는 프리리스트에 등록
  • 이과정에서 256바이트 보다 더 큰 프리청크를 찾지 못했다면 공유풀 LRU리스트로 부터 핀이 해제된 청크중 256바이트 보다 큰 청크를 검색 후 프리리스트 등록
    4. 공유풀LRU리스트 검색시 필요한 크기의 청크를 확인 못할시 4031에러와 SQL파스 실패

프리리스트에서 프리청크를 검색후 할당받기까지 모든단계에서 shared pool 래치를 할당 받아야 한다.
청크의 할당 해제가 빈번히 발생 한다면 청크는 더욱 작게 쪼개져 관리되어야 할 청크의 수가 증가 한다.
프리리스트를 검색하는 시간을 증가시키고 쉐어드풀 레치에 대한 경합을 증가시켜 성능저하를 유발 하며
4031에러를 발생시킨다.
==================================================================================================================

05 바인드 변수의 중요성

바인드 변수 사용에 따른 효과는 아주 분명
커서를 많이 생성하지 않고 하나를 반복 재사용하므로 메모리 사용량과 파싱 소요 시간을 불인다
궁극적으로 시스템전반의 메모리와 CPU사용률을 낮춰 데이터베이스 성능과 확장성을 높이는데 기여
특히 동시 사용자 접속이 많을 때는 그 영향력이 절대적이다

#################################
바인드 변수 사용의 필요성 테스트
#################################

SQL> create table t as select * from dba_objects;

테이블이 생성되었습니다.

SQL> update t set object_id = rownum;

72471 행이 갱신되었습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> create unique index t_idx on t (object_id);

인덱스가 생성되었습니다.

SQL> analyze table t compute statistics;

테이블이 분석되었습니다.



set timing on 
declare 
  type rc is ref cursor; 
  l_rc rc;
  l_object_name t.object_name%type;
begin 
  for i in 1..20000
  loop
   open l_rc for
    'select /* TEST1 */ object_name
     from t
     where object_id = :x' using i;
   fetch l_rc into l_object_name;
   close l_rc;
  end loop;
end;
/
SQL> set timing on
SQL> declare
  2    type rc is ref cursor;
  3    l_rc rc;
  4    l_object_name t.object_name%type;
  5  begin
  6    for i in 1..20000
  7    loop
  8     open l_rc for
  9      'select /* TEST1 */ object_name
 10       from t
 11       where object_id = :x' using i;
 12     fetch l_rc into l_object_name;
 13     close l_rc;
 14    end loop;
 15  end;
 16  /

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:00.79
SQL>

SQL> col sql_text format a50
SQL> select sql_text,loads,parse_calls,executions,fetches
  2  from v$sql
  3  where sql_text like '%TEST1%'
  4  and sql_text not like '%v$sql%'
  5  and sql_text not like 'declare%';

SQL_TEXT                                                LOADS PARSE_CALLS EXECUTIONS    FETCHES
-------------------------------------------------- ---------- ----------- ---------- ----------
select /* TEST1 */ object_name      from t      wh          1       20000      20000      20000
ere object_id = :x


경   과: 00:00:00.03


declare 
  type rc is ref cursor; 
  l_rc rc;
  l_object_name t.object_name%type;
begin 
  for i in 1..20000
  loop
   open l_rc for
    'select /* TEST2 */ object_name
     from t
     where object_id = '||i ;
   fetch l_rc into l_object_name;
   close l_rc;
  end loop;
end;
/

SQL> declare
  2    type rc is ref cursor;
  3    l_rc rc;
  4    l_object_name t.object_name%type;
  5  begin
  6    for i in 1..20000
  7    loop
  8     open l_rc for
  9      'select /* TEST2 */ object_name
 10       from t
 11       where object_id = '||i ;
 12     fetch l_rc into l_object_name;
 13     close l_rc;
 14    end loop;
 15  end;
 16  /

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:07.54
SQL>


select sql_text,loads,parse_calls,executions,fetches
from v$sql
where sql_text like '%TEST2%'
and sql_text not like '%v$sql%'
and sql_text not like 'declare%';


select /* TEST2 */ object_name      from t      wh          1           1          1          1
ere object_id = 19110

select /* TEST2 */ object_name      from t      wh          1           1          1          1
ere object_id = 18922

select /* TEST2 */ object_name      from t      wh          1           1          1          1
ere object_id = 18476

select /* TEST2 */ object_name      from t      wh          1           1          1          1
ere object_id = 19948

select /* TEST2 */ object_name      from t      wh          1           1          1          1
ere object_id = 19051


2091 개의 행이 선택되었습니다.

경   과: 00:00:01.25

바인드 변수 사용원칙을 잘 지커지 않으면 라이브러리캐시 경합때문에 시스템 정상가동이
어려운 상황에 직면할 수 있다. cursor_sharing 파라미터를 변경 긴급처방

06 바인드 변수의 부작용과 해법

바인드 변수를 사용하면 최초 수행할 때 최적화를 거친 실행계획을 캐시에 적재하고，
실행시점에는 그것을 그대로 가져와 값을 다르게 바인딩하면서 반복 재사용하게 된다.
여기서， 변수를 바인딩하는 시점이 (최적화 시점보다 나중인) 실행시점이라는 사실을 아
는 것이 중요하다. 즉， SQL을 최적화하는 시점에 조건절 컬럼의 데이터 분포도를 활용하
지 못하는 문제점을 갖는다.

(1) 바인드 변수 Peeking

오라클은 9i부터 바인드 변수 Peeking 기능을 도입
SQL이 첫 번째 수행되면서 하드파싱될 때 함께 딸려 온 바인드 변수 값을 살짝 훔쳐 보고
그 값에 대한 컬럼 분포를 이용해 실행계획을 결정
문제점
잘못 수립된 실행계획 때문에 느린 애플리케이션도 문제지만 시스템 운영자 입장에서는
자주 실행계획이 바뀌어 어제와 오늘의 수행속도가 급격히 달라지는 현상

(2) 적응적 커서 공유(Adaptive Cursor Sharing)

오라클 11g도입된 이 기능이 작동하려면 기본적으로 조건절 컬럼에 히스토그램이 생성돼 있어야 한다.
그 상태에서，옵티마이저가 바인드 변수 값에 따라 실행계획을 달리 가져갈 필요가 있다고 판단면
SQL커서에 대해서 이 기능이 활성화
문제점
새로운 실행계획을 만들지 않는다
기존 커서의 실행계획을 그대로 사용해보고 성능이 나쁘다고 판단되면 모드를 전환

(3) 입력 값에 따라 SQL 분리

인텍스 액세스 경로(access Path)로서 중요하고 조건절 컬럼의 데이터 분포가 균일하지 않
은 상황에서 바인드 변수 시용에 따른 부작용을 피하려면 바인딩 되는 값에 따라 실행계
획을 아래와 같이 분리하는 방안을 고려해야 한다.

select /*+ full(a) */ *
from 아파트매물 a
where :city in ('서울','경기')
and 도시 = :city
union all
select /*+ index(a) */ *
from 아파트매물 a
where :city not in ('서울','경기')
and 도시 = :city;

문제점
union all을 이용해 SQL을 길게 작성하면 Parse 단계의 CPU 사용률을 높임.
-라이브러리 캐시는 문장을 저장함
-Syntax를 체크함
-파싱트리를 만들어 Semantic 체크도함
union all을 이용해 SQL을 길게 작성하면 Execute단계에서도 CPU 사용률을 높임.
-I/O를 일으키지 않을 뿐 실제실행은 일어나기 때문

(4) 예외적으로，Literal 상수값 사용

게다가 배치 프로그램이나 정보계 시스템에서 수행되는 SQL은 대부분 Long
Running 쿼리이므로 파싱 소요시간이 쿼리 총 소요시간에서 차지하는 비중에 매우 낮
고, 사용빈도도낮아하드파싱에 의한라이브러리 캐시 부하를크게 염려할펼요가없다.

07 세션 커서 캐싱

세션 커서 (session cursor)란
Shared Pool에 위치한 공유 커서 (shared cursor)를 실행하려고 PGA로 인스턴스화한 것

쿼리를 수행한 후에 커서를 닫으면,
세션 커서를 위해 할당된 메모리는 물론 공유 커서를 가리키는 포인터까지바로 해제,
그 다음에 같은 SQL을 수행하면 커서를 오픈하기 위한 라이브러리 캐시 탐색작업을 다시 해야 함.

세션 커서 캐싱(Session Cursor Cache)란
오라클은 자주 수행하는 SQL에 대한 세션 커서를 세션 커서 캐시 (Session cursor Cache)에 저장

커서를 닫는 순간 해당 커서의 Parse Call 횟수를 확인해 보고 그 값이 3보다 크거나 같으면,
세션 커서를 세션 커서 캐시로 옮긴다. 세션 커서 캐시에는 SQL 텍스트와 함께 공유 커서를
가리키는 포인터를 저장
커서는 닫힌 상태지만 공유 커서에 대한 참조를 유지하기 때문에 다음 수행 시 더 빨리
커서를 오픈(자주 수행되는 SQL문에 의해 발생하는 라이브러리 캐시 부히를 경감)

즉，
SQL문을 파싱해서 구문을 분석하고,
라이브러리 캐시에서 커서를 찾는 과정에서 소모되는 CPU 사용량을 줄일 수 있음은 물론，
소프트 파싱 과정에 발생하는 래치 요청 횟수를 감소시키는 효과

세션 커서 캐시 내에서도 LRU 알고리즘을 시용함으로써 새로운 엔트리를 위한 공간이 필요할 때마다
기존 세션 커서 중 사용 빈도가 낮은것부터 밀어낸다.

session_cached_cursors는 얼마나 많은 세션 커서를 캐싱할지를 지정하는 파라미터
로서 ， 이 값을 O보다 크게 설정하면 Parse Call이 발생할 때마다 라이브러리 캐시를 탐색
하기 전에 세션 커서 캐시를 먼저 살펴본다.

  • users_opening: 공유 커서를 참조하고 있는 세션 커서의 수를 보여준다. 수행을 마
    쳐 커서 를 닫았더라도 참조를 버리지 않은 채 세션 커서 캐시로 옮겨진다면 여기에
    집계된다 .
  • users_executing : 해당 SQL을 현재 실행 중인， 즉 커서가 열려있는 세션 커서의
    수를 보여준다. DML일 때는 수행을 마칠 때 커서가 자동으로 닫히지만 select문은
    EOF(End of Fetch)에 도달했을 때 커서가 닫힌다
SQL> alter session set session_cached_cursors = 10;

세션이 변경되었습니다.

SQL> alter system flush shared_pool;

시스템이 변경되었습니다.

SQL> select * from scott.emp where empno=7788 ;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7788 SCOTT      ANALYST         7566 87/04/19       3000
        20


SQL> select parse_calls, users_opening, users_executing from v$sql
  2  where sql_text = 'select * from scott.emp where empno=7788 ';

PARSE_CALLS USERS_OPENING USERS_EXECUTING
----------- ------------- ---------------
          1             0               0

SQL> select * from scott.emp where empno=7788 ;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7788 SCOTT      ANALYST         7566 87/04/19       3000
        20


SQL> select parse_calls, users_opening, users_executing from v$sql
  2  where sql_text = 'select * from scott.emp where empno=7788 ';

PARSE_CALLS USERS_OPENING USERS_EXECUTING
----------- ------------- ---------------
          2             0               0

SQL> select * from scott.emp where empno=7788 ;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7788 SCOTT      ANALYST         7566 87/04/19       3000
        20


SQL> select parse_calls, users_opening, users_executing from v$sql
  2  where sql_text = 'select * from scott.emp where empno=7788 ';

PARSE_CALLS USERS_OPENING USERS_EXECUTING
----------- ------------- ---------------
          3             1               0

세번 일어나는 순간 users_opening값이 0에서 1로 바뀐 것 확인
세번째 수행되기 전까지는 users_opening값이 0인것
커서를 닫자마자 공유커서에 대한 참조까지 곧바로 해제

users_opening에서 'open' 의 의미가 실제 커서가 열려 있음을 의미하는 것이 아님을
기억할 필요가 있다. 커서는 닫힌 상태지만 공유 커서에 대한 참조를 유지하기 때문에 다
음수행 시 더 빨리 커서를오픈할수있는것이다.

create table t(x number);

select a.name, b.value  
from   v$statname a, v$mystat b 
where  a.name in ('session cursor cache hits','parse count (total)')
and    b.statistic# = a.statistic#; 


alter session set session_cached_cursors=0;

declare 
   i number; 
 begin 
   for i in 1..10000 
    loop 
    execute immediate 'insert into t values('||mod(i,100)||')'; 
    end loop; 
 
 commit; 
end; 
/  


select a.name, b.value  
from   v$statname a, v$mystat b 
where  a.name in ('session cursor cache hits','parse count (total)')
and    b.statistic# = a.statistic#; 

alter session set session_cached_cursors=100;

declare 
   i number; 
 begin 
   for i in 1..10000 
    loop 
    execute immediate 'insert into t values('||mod(i,100)||')'; 
    end loop; 
 
 commit; 
end; 
/  


select a.name, b.value  
from   v$statname a, v$mystat b 
where  a.name in ('session cursor cache hits','parse count (total)')
and    b.statistic# = a.statistic#; 

출처: http://wiki.gurubee.net/pages/viewpage.action?pageId=28115145


반응형
저작자표시 비영리

'ORACLE > ADMIN' 카테고리의 다른 글

서로 다른 오라클 버전의 클라이언트 / 서버 간의 상호 운용성 지원 매트릭스 (문서 ID 1556542.1)  (0) 2014.07.05
오라클 7/8/8i/9i/10g/11g에서 오라클 블록 손상 처리 (문서 ID 1534333.1)  (0) 2014.07.02
[ORACLE]Listener.log 관리  (0) 2013.08.06
psu 패치  (0) 2013.07.25
shrink 대상 선정 및 shrink작업  (1) 2013.06.03
Posted by [PineTree]
ORACLE/TroubleShooting2010. 4. 1. 15:10

ORA-01000: maximum open cursors exceeded" 조사

반응형
문제 설명
Oracle은OPEN_CURSORS매개변수를 사용하여 세션이 동시에 취할 수 있는 최대 열린 커서 개수를 지정합니다. 최대 개수를 초과하면 Oracle은ORA-01000오류를 보고합니다. 이 오류가 WebLogic Server로 전송되면SQLException이 발생합니다.

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

이 패턴은 WebLogic Server를 사용할 때 오류를 발생시키는 원인과 해결 방법에 대해 설명합니다.

문제 해결
다음 항목을 모두 수행해야 하는 것은 아닙니다. 어떤 경우에는 다음 중 일부만 수행하여도 해결할 수 있습니다.

항목 바로가기

진단 조회
다음 SQL 조회는ORA-01000문제를 진단하는 데 유용합니다. 이런 조회를 실행하려면 데이터베이스에 관리자로 로그인하거나 데이터베이스 관리자가 사용자에게v$뷰에서 SELECT 명령문을 실행할 수 있는 권한을 승인해야 합니다.

1. 데이터베이스의 OPEN_CURSORS 매개변수 값을 확인합니다.
Oracle 은OPEN_CURSORS초기 화 매개변수를init.ora에 사용하여 세션이 동시에 취할 수 있는 최대 커서 개수를 지정합니다. 디폴트값은 50이지만, WebLogic Server와 같은 시스템에는 너무 작습니다. 다음 조회를 사용하여 데이터베이스에서OPEN_CURSORS매개변수 값을 찾을 수 있습니다.
 
SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     1000

 

OPEN_CURSORS값을 충분히 큰 값으로 설정하여 응용 프로그램에서 열린 커서가 부족하지 않도록 해야 합니다. 이 수는 응용 프로그램에 따라 다릅니다. 세션이OPEN_CURSORS에 지정된 커서 개수만큼 열지 않는 경우 이 값을 실제 필요한 값보다 크게 설정해도 오버헤드가 추가되지 않습니다.

2. 열린 커서의 수를 확인합니다.
아래 조회는 사용자 'SCOTT'가 각 세션에 대해 연 커서 개수를 내림차순으로 표시합니다.
 
SQL> select o.sid, osuser, machine, count(*) num_curs
  2  from v$open_cursor o, v$session s
  3  where user_name = 'SCOTT' and o.sid=s.sid
  4  group by o.sid, osuser, machine
  5 order by  num_curs desc;       SID OSUSER               MACHINE                                              NUM_CURS
---------- ---------------- ------------------------------------------------- ----------
       217                                m1                                                           1000
        96                                 m2                                                            10
       411                                m3                                                             10
        50                                test                                                              9

WebLogic Server에서 커넥션 풀을 사용할 때 커넥션을 커넥션 풀에서 가져온 경우 이 조회의user_name은 커넥션을 생성하는데 사용한user_name이 어야 합니다. 조회 결과는 시스템 이름도 출력합니다. 조회 결과를 통해 열린 커서의 개수가 큰SID와 WebLogic Server를 실행하는 시스템 이름을 식별할 수 있습니다.

v$open_cursor는dbms_sql.open_cursor()를 사용하여 연 동적 커서인PARSED및NOT CLOSED를 세션에 대해 추적할 수 있습니다. 구문 분석 하지 않은 열린 동적 커서는 추적하지 않습니다. 응용 프로그램에서 동적 커서의 사용은 흔하지 않습니다. 이 패턴은 동적 커서가 사용되지 않는 것으로 간주합니다.

3. 커서에 대해 실행 중인 SQL을 확인합니다.
위의 조회 결과에서 식별한 SID를 취하고 다음 조회를 실행합니다.

SQL> select q.sql_text
  2  from v$open_cursor o, v$sql q
  3  where q.hash_value=o.hash_value and o.sid = 217;

SQL_TEXT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select * from empdemo where empid='212'
select * from empdemo where empid='321'
select * from empdemo where empid='947'
select * from empdemo where empid='527'
...

 

결과는 커넥션 상에서 실행 중인 조회를 표시합니다. 이를 기준으로 열린 커서의 출처를 역추적할 수 있습니다.

페 이지 맨 위

일 반적인 원인과 문제 해결
다음 단계는 문제의 원인을 파악하고 가능한 해결 방법을 모색하는 절차입니다.

코드 연습
이 문제의 가장 일반적인 원인은 JDBC Object가 정상적으로 닫히지 않은 경우입니다. 모든 JDBC Object가 정상적으로 닫혔는지 확인하기 위해 응용 프로그램 코드에서 역추적하려면진 단 조회에 타사의 조회 결과를 사용합니다. 모든 JDBC Object가 정상적인 상태나 예외 조건에서 닫히도록 하려면finally블록에서 Connections, Statements 및 ResultSets 같은 JDBC Object를 명시적으로 닫는 것이 좋습니다. 다음은 일반적인 예제입니다.
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try {
    conn = getConnection(); //Method getConnection will return a JDBC Connection
    stmt = conn.createStatement();
    rs = stmt.executeQuery("select * from empdemo");
    // do work
} catch (Exception e) {
    // handle any exceptions
} finally {
    try {
        if(rs != null)
            rs.close();
    } catch (SQLException rse) {}
    try {
        if(stmt != null)
            stmt.close();
    } catch (SQLException sse) {}
    try {
        if(conn != null)
            conn.close();
    } catch (SQLException cse) {}
}

 

JDBC Object를 버리는 코딩 습관을 피하십시오. 다음 연습은 각 루프 반복에서 새 Connection, Statement 및 ResultSet를 얻지만 각 반복에 대해 JDBC Object를 닫지는 않습니다. 그러므로 JDBC Object leak이 발생합니다.

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String[] queries = new String[10];
//Define queries

try {
    for(int i = 0; i < 10; i++) {
        conn = getConnection();
        stmt = conn.createStatement();
        rs = stmt.executeQuery(queries[i]);
        // do work
    }
} catch (Exception e) {
    // handle any exceptions
} finally {
    try {
        if(rs != null) 
            rs.close();
    } catch (SQLException rse) {}
    try {
        if(stmt != null) 
            stmt.close();
    } catch (SQLException sse) {}
    try {
        if(conn != null) 
            conn.close();
    } catch (SQLException cse) {}
}


Connection을 닫을 때 Statement와 ResultSet를 닫아야 하지만 JDBC 사양에 따라 하나의 Connection Object에 여러 Statement를 작성한 경우에는 사용한 직후 Statement와 ResultSet를 명시적으로 닫는 것이 좋습니다. Statement와 ResultSet를 명시적으로 즉시 닫지 않으면 커서가 누적되어 Connection이 닫히기 전에 데이터베이스에 허용된 최대 개수를 초과할 수 있습니다. 예를 들어, 아래 코드 부분에서는finally블록에 서 Connection을 닫을 때 ResultSet와 Statement도 닫아야 합니다. 그러나 이 코드 부분은 하나의 Connection에 여러 Statement와 ResultSet를 생성합니다. 루프가 끝나기 전에 "maximum open cursors exceeded(열린 최대 커서 개수 초과)" 문제가 발생했을 수 있습니다.


Connection conn = null;

try{
    conn = getConnection();

    for(int i = 0; i < NUM_STMT; i++) {
        Statement stmt = null;
        ResultSet rs = null;
 
         stmt = conn.createStatement();
         rs = stmt.executeQuery(/*some query*/);
        //do work
    }
} catch(SQLException e) {
     // handle any exceptions
} finally {
    try{
        if(conn != null)
            conn.close();
    } catch(SQLException ignor) {}
}


페 이지 맨 위

명령문 캐시
성능 향상을 위해 WebLogic Server는 커넥션 풀을 사용할 때 prepared statements와 callable statements를 캐시하는 기능을 제공합니다. WebLogic Server가 prepared statements나 callable statements를 캐시할 때 많은 경우에 DBMS는 열린 각 명령문에 대해 커서를 유지합니다. 그러므로 명령문 캐시 기능은 "maximum open cursors exceeded(열린 최대 커서 개수 초과)" 문제의 원인이 될 수 있습니다. 명령문 캐시 크기 속성은 커넥션 풀의 각 인스턴스에서 각 커넥션에 대해 캐시할 prepared statements와 callable statements의 전체 개수를 결정합니다. 명령문을 너무 많이 캐시하면 데이터베이스 서버의 열린 커서 개수가 최대 개수를 초과할 수 있습니다.

WebLogic Server에서 디폴트 명령문 캐시 크기는 버전마다 다를 수 있습니다. 예를 들면 다음과 같습니다. 

""maximum open cursors exceeded(열린 최대 커서 개수 초과)" 문제가 명령문 캐시와 관련이 있는지 확인하려면 명령문 캐시 크기를 0으로 설정하여 이 기능을 해제하거나 캐시 크기를 줄여 오류가 계속 발생하는지 확인할 수 있습니다. 캐시 크기를 줄여도 문제가 발생하지 않으면 커넥션 풀의 원래 명령문 캐시 크기가 너무 크거나 DBMS의 최대 열린 커서 개수 제한이 너무 적은 것이므로, 이 중 하나의 값을 조정해야 할 수 있습니다. 커넥션에 열린 커서의 개수가 계속 증가하다가 명령문 캐시 크기를 0으로 설정했을 때 이런 동작이 나타나지 않으면 커서 leak 문제일 수 있습니다. 사용 중인 JDBC 드라이버가 원인이거나 WebLogic Server 버그일 수도 있습니다. 다른 JDBC 드라이버를 사용해 보십시오. 다른 JDBC 드라이버를 사용할 때도 동일한 문제가 발생하면 기술 지원 엔지니어가 이 문제를 세부적으로 조사하여 WebLogic Server 버그인지 확인할 수 있도록 BEA에 보고하십시오.

페 이지 맨 위

데이터베이스 드라이버
""maximum open cursors exceeded(열린 최대 커서 개수 초과)" 문제는 JDBC 드라이버가 원인일 수 있습니다. 드라이버가 문제인지 WebLogic 커넥션 풀이 문제인지 파악하기 위해 재현 가능한 테스트 케이스가 있으면 다음을 시도해 볼 수 있습니다.

1. 드라이버에서 커넥션을 직접 가져옵니다.
테스트 케이스에서 JDBC 커넥션은 드라이버에서 직접 가져오고 WebLogic 커넥션 풀은 우회합니다. 커넥션을 닫지 말고 배열이나 일부 다른 구조에 열어 두고 커서 leak 문제가 계속 발생하는지 확인합니다. 커넥션을 닫지 않는 이유는 커넥션 풀을 사용할 때의 동작을 시뮬레이션하기 위해서 입니다. 커넥션 풀을 사용할 때connection.close()는 커넥션을 실제로 닫지는 않으나 대신에 커넥션을 풀로 반환합니다.

2. 다른 JDBC 드라이버로 시도합니다.
타사의 JDBC 드라이버나 드라이버의 업데이트 버전으로 시도하여 문제가 계속 발생하는지 확인합니다. 메타 데이터를 사용하여 올바른 드라이버가 사용되었는지 확인할 수 있습니다. 샘플 코드는 다음과 같습니다.

Connection conn = getConnection();
DatabaseMetaData dmd = conn.getMetaData();
System.out.println("JDBC Driver Name is " + dmd.getDriverName()); 
System.out.println("JDBC Driver Version is " + dmd.getDriverVersion());

3. XA 드라이버 버그.
Oracle XA 드라이버를 사용 중이고 데이터베이스에 "SELECT count (*) FROM SYS.DBA_PENDING_TRANSACTIONS" 같은 쿼리가 많은 경우 Oracle XA 드라이버에 커서 leak 문제가 발생할 수 있습니다. 이 문제는 MetaLink Case 3151681에 설명되어 있으며 버전 10.1.0.2에서 수정되었습니다.
또한 XA 드라이버를 사용할 때http://e-docs.bea.com/wls/docs81/jta/thirdpartytx.html#1075181에 설명된 대로 데이터베이스 서버에 XA 사용을 설정해야 합니다. 즉,grant select on dba_pending_transactions to public명 령을 실행해야 합니다.

JDBC 드라이버가 문제이고 이 드라이버를 사용해야 할 경우 커서 leak 문제의 해결 방법은 WebLogic 커넥션을 가끔씩 재설정하거나 커넥션 풀을 축소하는 것입니다. 재설정하는 방법이나 커넥션 풀을 축소하는 방법은 WebLogic 설명서를 참조하십시오. 버전 8.1의 경우http://e-docs.bea.com/wls/docs81/ConsoleHelp/domain_jdbcconnectionpool_control.html에 설명되어 있습니다.


페 이지 맨 위

알려진 문제
사용하고 있는 WLS 버전의 릴리스 정보를 주기적으로 검토하여 서비스 팩에서 알려진 문제나 해결된 문제를 확인하고 ORA-01000 / 커서 Leak 관련 문제를 검색할 수 있습니다.다음을 참조하십시오.
특별 정보의 경우, 각 버전의 서비스 팩 릴리스 정보에서 해결된 것으로 표시된 다음 CR를 참고하십시오. 

검색하면 릴리스 정보뿐 아니라추 가 도움말에서 언급된 기타 지원 솔루션 및 CR 관련 정보도 알 수 있습니다. 계약 고객은http://support.bea.com/에 로그인한 다음 Browse 포틀릿에서 Solutions 및 Bug Central을 검색하여 제품 버전별로 사용 가능한 최신 CR을 찾을 수 있습니다.

페 이지 맨 위

추 가 도움말이 필요하십니까?
패턴대로 작업했지만 추가 도움말이 필요한 경우 다음과 같이 할 수 있습니다.
  1. http://support.bea.com/의 AskBEA에서"ORA-01000: maximum open cursors exceeded" 등으로 문제를조회하여 게시된 다른 해결 방법을 찾아봅니다. 계약 지원 고객: 제공되는 CR 관련 정보에 액세스할 수 있는 권한으로 로그온합니다
  2. http://forums.bea.com에 서 BEA 뉴스그룹에 보다 자세한 내용을 질문합니다.
이렇게 해도 문제를 해결할 수 없는 경우 유효한 유지 보수 계약이 되어 있다면http://support.bea.com/에 로그인하여 지원요청할 수 있습니다.
반응형
저작자표시 비영리

'ORACLE > TroubleShooting' 카테고리의 다른 글

ORA-01031 오류 해결방법  (0) 2010.08.12
Oracle 패치셋 업그레이드 후 ORA-01092 발생할 경우  (0) 2010.05.26
WARNING: Subscription for node down event still pending  (0) 2009.06.09
DATABASE CRASH WITH SGA_TARGET 문서 ID: 747812.1  (0) 2009.03.03
RAC 환경에서 ORA-29740 문제 진단  (0) 2009.03.02
Posted by [PineTree]

티스토리툴바