[1] 커서의 종류

• 처음 SQLP 1회독 했을 때 긴가민가 어물쩡 넘어가다 시험 직전까지 어물쩡했던 부분이 바로
  커서의 종류 부분이었다. 글로만 읽으면 이 얘기가 저 얘기같고 저 얘기가 이 얘기 같은 부분이라 그림으로 조금 정리해보았다.
  

(1) 공유커서

• 라이브러리 캐시에 공유되어 있는 공유 SQL 영역(Shared SQL Area)이다.
• SQL을 실행하는데 필요한 루틴을 정의한 것이다.
• Java의 클래스 형태이다.

(2) 세션커서

• Private SQL Area에 저장되는 커서이다.
• 라이브러리 캐시에 공유되어 있는 공유커서를 인스턴스화하기 위해 PGA에 메모리공간을 할당한다.
• 세션커서를 할당하는 것은 서버 프로세스가 실제 데이터를 추출하기 위한 준비작업이다.
• Java의 클래스를 상속받은 객체이다.

(3) 애플리케이션 커서

• 세션커서를 가리키는 핸들이다.
• 세션커서를 핸들링하기 위한 애플리케이션 리소스를 할당한다.

[2] 커서공유

(1) 공유커서

공유커서는 v$sql을 통해 확인할 수 있다.

커서 공유의 키(식별자)

• 커서가 공유되려면 쿼리를 식별할 수 있는 키가 있어야 하는데 이 키는 쿼리문장 그 자체이다.
• 10g 부터는 sql_id 가 식별자의 역할을 할 수 있다.

  ✅ $sql 조회항목

  • parse_call : 라이브러리 캐시에서 SQL을 찾으려는 요청횟수
  • loads : 하드파싱을 거친 SQL 실행계획을 라이브러리 캐시에 적재한 횟수
  • executions : SQL을 수행한 횟수
  • reloads : 커서가 무효화된 횟수로, 커서가 참조하고 있는 오브젝트에 변화가 일어났음을 의미한다.

✅ 커서가 재사용되지 않는 경우 (공유커서의 무효화)

• 커서에서 참조하는 오브젝트의 변화 (DDL 등, DML은 무관하다.)
• 컬럼의 추가/삭제/변경 : 이전의 커서가 잘못된 컬럼참조를 할 수 있기 때문이다.
• 인덱스의 추가 : 이전에 수립된 실행계획이 최적이 아닐 수 있기 때문이다.
• 통계정보 생성 : 마찬가지로 이전에 수립된 실행계획이 최적이 아닐 수 있기 때문이다.

[3] child 커서를 공유하지 못하는 경우

(1) Child 커서는 언제 생성될까?

• 👉 100% 동일한 SQL 문장인데도 불구하고 공유되지 않는 경우 Child 커서가 생성된다.

(2) 동일한 SQL 문장이 Child 커서를 갖게 되는 이유

• 오브젝트명은 같지만 유저에 따라 다른 오브젝트를 가리킬때
• 오브젝트 변경으로 무효화된 커서를 처음 사용하려 했으나 특정세션이 아직 기존 커서를 사용중(pin)일때
• "옵티마이저 모드"를 비롯해 옵티마이저 관련 파라미터가 다를때
• 입력된 바인드값의 길이가 크게 다를 때
• NLS 파라미터가 다를때
• SQL트레이스를 활성화시켰을 때

(3) child 커서를 공유하지 못한 이유 확인

• v$sql_shared_cursor 뷰를 통해 새로운 Child 커서가 기존 커서와 공유되지 못한 이유를 알 수 있다.

[4] Parent 커서를 공유하지 못하는 경우

(1) 의미적으로는 같은 Sql구문이지만 문자열에 차이가 있는 경우

• 공백문자 또는 줄바꿈
• 대소문자 구분
• 테이블 Owner 명시
• 주석
• 옵티마이져 힌트 : first_rows, all_rows
• 조건절의 비교값
• 기타 등등

(2) 커서 공유 실패 예방하기

• 동일한 쿼리가 서로 다른 sql이 되어 각각 하드 파싱을 일으키며 Shared Pool공간을 낭비하게 된다.
  따라서 커서 공유 실패를 예방하려면 Sql 작성 표준을 정해 이를 준수하도록 한다.

  ✅ sql 작성 표준 예시

  • 오라클 예약어는 대문자로 , 사용자 객체명은 소문자로 통일한다.
  • 한줄에 쭈욱 나열하지 말고 의미가 달라지는 구문은 줄바꿈을 한다.
  • 반복 수행문안에서 사용되거나 자주 사용되는 OLTP성 업무의 쿼리라면 치명적 바인드 변수를 사용한다.