(1) 커서란?

• 공유 커서(shared cursor) 
• 라이브러리 캐시에 공유돼 있는 Shared SQL Area
• java,vb,pro*c,pl/sql 등에서 sql 수행시 서버 프로세서는 라이브러리캐시에 공유되어있는지 확인 없으면 최적화과정을 통해 실행계획을 재생성 라이브러리 캐시에 공유
•  세션 커서(session cursor)  
• Private SQL Area (라이브러리 캐시에 공유된 커서를 실행할때 우선 PGA영역에 메모리 할당) 에 저장된 커서
• 커서를 오픈하면 라이브러리 캐시에 공유되 있는 커서를 인스턴스화 함(커서오픈) 으로써 PGA에 커서를 위한 메모리공간(Persistent Area, Runtime Area)을 할당하고, 실제 데이터 추출을 시작할 수있도록 준비작업을 함
• 애플리케이션 커서 (application cursor) 
• 세션 커서를 가리키는 핸들
• 클라이언트 애플리케이션에 리소스를 할당하는 것

(2) 커서 공유

• v$sql: 라이브러리 캐시에 공유 돼 있는 커서의 수행통계 조회

같은 세션

SQL> alter system flush shared_pool;

System altered.

SQL> select /* cursor_test */ empno, ename, job, sal, deptno

  2  from emp

  3  where empno = 7788;

     EMPNO ENAME      JOB              SAL     DEPTNO

---------- ---------- --------- ---------- ----------

      7788 SCOTT      ANALYST         3000         20

SQL> select sql_id,parse_calls,loads,executions,invalidations,

  2  decode(sign(invalidations), 1,(loads-invalidations), 0) reloads

  3  from v$sql

  4  where sql_text like '%cursor_test%'

  5  and sql_text not like '%v$sql%';

SQL_ID        PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS

------------- ----------- ---------- ---------- ------------- ----------

1nv36gy8vycf5           1          1          1             0          0

SQL> select /* cursor_test */ empno, ename, job, sal, deptno

  2  from emp

  3  where empno = 7788;

     EMPNO ENAME      JOB              SAL     DEPTNO

---------- ---------- --------- ---------- ----------

      7788 SCOTT      ANALYST         3000         20

SQL> select /* cursor_test */ empno, ename, job, sal, deptno

  2  from emp

  3  where empno = 7788;

     EMPNO ENAME      JOB              SAL     DEPTNO

---------- ---------- --------- ---------- ----------

      7788 SCOTT      ANALYST         3000         20

SQL> select sql_id,parse_calls,loads,executions,invalidations,

  2  decode(sign(invalidations), 1,(loads-invalidations), 0) reloads

  3  from v$sql

  4  where sql_text like '%cursor_test%'

  5  and sql_text not like '%v$sql%';

SQL_ID        PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS

------------- ----------- ---------- ---------- ------------- ----------

1nv36gy8vycf5           3          1          3             0          0

SQL>

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

다른 세션끼리 같은 커서를 공유하는지 테스트

SQL> disconnect

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn scott as sysdba

Enter password:

Connected.

SQL> select /* cursor_test */ empno, ename, job, sal, deptno

  2  from emp

  3  where empno = 7788;

     EMPNO ENAME      JOB              SAL     DEPTNO

---------- ---------- --------- ---------- ----------

      7788 SCOTT      ANALYST         3000         20

SQL> select sql_id,parse_calls,loads,executions,invalidations,

  2  decode(sign(invalidations), 1,(loads-invalidations), 0) reloads

  3  from v$sql

  4  where sql_text like '%cursor_test%'

  5  and sql_text not like '%v$sql%';

SQL_ID        PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS

------------- ----------- ---------- ---------- ------------- ----------

1nv36gy8vycf5           4          1          4             0          0

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

• 무효화
• 커서가 첨조하고 있던 오브젝트에 컬럼이 추가/삭제 되거나 새로운 인덱스 만들어질때
• 오브젝트 통계를 새로 수집하는등 DDL 문 수행될때

SQL> begin

        dbms_stats.gather_table_stats

        (ownname        => USER

        ,tabname        => 'EMP'

        ,no_invalidate  => FALSE

        );

end;

  2    3    4    5    6    7    8

  9  /

PL/SQL procedure successfully completed.

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%';  2    3    4    5

SQL_ID        PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS

------------- ----------- ---------- ---------- ------------- ----------

1nv36gy8vycf5           4          1          4             1          0

SQL> select /* cursor_test */ empno, ename, job, sal, deptno

from emp

where empno = 7788;  2    3

     EMPNO ENAME      JOB              SAL     DEPTNO

---------- ---------- --------- ---------- ----------

      7788 SCOTT      ANALYST         3000         20

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%';  2    3    4    5

SQL_ID        PARSE_CALLS      LOADS EXECUTIONS INVALIDATIONS    RELOADS

------------- ----------- ---------- ---------- ------------- ----------

1nv36gy8vycf5           1          2          1             1          1

SQL>

• sql 문 중간에 작은 공백문자 하나만 추가하더라도 다른 sql문장으로 인식-> 커서 공유되지 않음
• 10g 부터 pl/sql 로 작성된 sql에 주석이 있다면 컴파일 과정에서 없애버리거나 수문자를 대문자로 바꾸기도함

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

• sql 문장에 100% 동일 해도 sql 커서를 공유하지 못하고 별도의 sql 커서를 생성할때도 있음
• Stored Object
• 테이블, 인덱스 ,함수, 프로시져, 패키지
• Transient Object
• 인스턴스가 떠있는 동안에만 존재하는 object

• child 커서
• 전체 문자열은 같지만 다른 방식으로 실행해야 하거나 파싱 스키마에 따라 다른 오브젝트를 참조하는 상황에서 필요

• v$sqlarea
• parent 커서 정보를 보여줌 
• v$sql을 group by 해서 집계
• v$sql
• child 커서 정보를 보여줌

SQL> conn hr/hr

SQL> select * from emp;

14 rows selected.

SQL> conn scott/tiger

SQL> select * from emp;

14 rows selected.

SQL> select sql_id,version_count,optimizer_mode,address,hash_value

from v$sqlarea

where sql_text like '%select * from emp%'

and sql_text not like '%v$sql%';

  2    3    4

SQL_ID        VERSION_COUNT OPTIMIZER_ ADDRESS  HASH_VALUE

------------- ------------- ---------- -------- ----------

a2dk8bdn0ujx7             2 ALL_ROWS   493E5928 1745700775

SQL> select sql_id,child_number,optimizer_mode,address,hash_value,parsing_user_id

from v$sql

where sql_text like '%select * from emp%'

and sql_text not like '%v$sql%';

  2    3    4

SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS  HASH_VALUE PARSING_USER_ID

------------- ------------ ---------- -------- ---------- ---------------

a2dk8bdn0ujx7            0 ALL_ROWS   493E5928 1745700775               0

a2dk8bdn0ujx7            1 ALL_ROWS   493E5928 1745700775              84

SQL>

• sql 하나당 여러개의 child 커서를 갖는 것은 바람직한 현상이 아님
• awr 리포트 에서 version count 가 20 이상인 sql목록을 따라 보여줌
• version count 수치가 높은 sql일수록 커서를 탐색하는데 더 많은 시간을 소비
• library cache 래치에 대한 경합 발생 가능성이 높음

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

• v$sql_shared_cursor
• 새로운 child 커서가 왜 기존 child 커서와 공유 되지 못한이유를 보여줌

SQL> desc v$sql_shared_cursor;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 SQL_ID                                             VARCHAR2(13)

 ADDRESS                                            RAW(4)

 CHILD_ADDRESS                                      RAW(4)

 CHILD_NUMBER                                       NUMBER

 UNBOUND_CURSOR                                     VARCHAR2(1)

 SQL_TYPE_MISMATCH                                  VARCHAR2(1)

 OPTIMIZER_MISMATCH                                 VARCHAR2(1)

 OUTLINE_MISMATCH                                   VARCHAR2(1)

 STATS_ROW_MISMATCH                                 VARCHAR2(1)

 LITERAL_MISMATCH                                   VARCHAR2(1)

 FORCE_HARD_PARSE                                   VARCHAR2(1)

 EXPLAIN_PLAN_CURSOR                                VARCHAR2(1)

 BUFFERED_DML_MISMATCH                              VARCHAR2(1)

 PDML_ENV_MISMATCH                                  VARCHAR2(1)

 INST_DRTLD_MISMATCH                                VARCHAR2(1)

 SLAVE_QC_MISMATCH                                  VARCHAR2(1)

 TYPECHECK_MISMATCH                                 VARCHAR2(1)

 AUTH_CHECK_MISMATCH                                VARCHAR2(1)

 BIND_MISMATCH                                      VARCHAR2(1)

 DESCRIBE_MISMATCH                                  VARCHAR2(1)

 LANGUAGE_MISMATCH                                  VARCHAR2(1)

 TRANSLATION_MISMATCH                               VARCHAR2(1)

 BIND_EQUIV_FAILURE                                 VARCHAR2(1)

 INSUFF_PRIVS                                       VARCHAR2(1)

 INSUFF_PRIVS_REM                                   VARCHAR2(1)

 REMOTE_TRANS_MISMATCH                              VARCHAR2(1)

 LOGMINER_SESSION_MISMATCH                          VARCHAR2(1)

 INCOMP_LTRL_MISMATCH                               VARCHAR2(1)

 OVERLAP_TIME_MISMATCH                              VARCHAR2(1)

 EDITION_MISMATCH                                   VARCHAR2(1)

 MV_QUERY_GEN_MISMATCH                              VARCHAR2(1)

 USER_BIND_PEEK_MISMATCH                            VARCHAR2(1)

 TYPCHK_DEP_MISMATCH                                VARCHAR2(1)

 NO_TRIGGER_MISMATCH                                VARCHAR2(1)

 FLASHBACK_CURSOR                                   VARCHAR2(1)

 ANYDATA_TRANSFORMATION                             VARCHAR2(1)

 INCOMPLETE_CURSOR                                  VARCHAR2(1)

 TOP_LEVEL_RPI_CURSOR                               VARCHAR2(1)

 DIFFERENT_LONG_LENGTH                              VARCHAR2(1)

 LOGICAL_STANDBY_APPLY                              VARCHAR2(1)

 DIFF_CALL_DURN                                     VARCHAR2(1)

 BIND_UACS_DIFF                                     VARCHAR2(1)

 PLSQL_CMP_SWITCHS_DIFF                             VARCHAR2(1)

 CURSOR_PARTS_MISMATCH                              VARCHAR2(1)

 STB_OBJECT_MISMATCH                                VARCHAR2(1)

 CROSSEDITION_TRIGGER_MISMATCH                      VARCHAR2(1)

 PQ_SLAVE_MISMATCH                                  VARCHAR2(1)

 TOP_LEVEL_DDL_MISMATCH                             VARCHAR2(1)

 MULTI_PX_MISMATCH                                  VARCHAR2(1)

 BIND_PEEKED_PQ_MISMATCH                            VARCHAR2(1)

 MV_REWRITE_MISMATCH                                VARCHAR2(1)

 ROLL_INVALID_MISMATCH                              VARCHAR2(1)

 OPTIMIZER_MODE_MISMATCH                            VARCHAR2(1)

 PX_MISMATCH                                        VARCHAR2(1)

 MV_STALEOBJ_MISMATCH                               VARCHAR2(1)

 FLASHBACK_TABLE_MISMATCH                           VARCHAR2(1)

 LITREP_COMP_MISMATCH                               VARCHAR2(1)

 PLSQL_DEBUG                                        VARCHAR2(1)

 LOAD_OPTIMIZER_STATS                               VARCHAR2(1)

 ACL_MISMATCH                                       VARCHAR2(1)

 FLASHBACK_ARCHIVE_MISMATCH                         VARCHAR2(1)

 LOCK_USER_SCHEMA_FAILED                            VARCHAR2(1)

 REMOTE_MAPPING_MISMATCH                            VARCHAR2(1)

 LOAD_RUNTIME_HEAP_FAILED                           VARCHAR2(1)

 HASH_MATCH_FAILED                                  VARCHAR2(1)

 PURGED_CURSOR                                      VARCHAR2(1)

 BIND_LENGTH_UPGRADEABLE                            VARCHAR2(1)

SQL>

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set optimizer_mode=first_rows;

Session altered.

SQL> select * from emp;

14 rows selected.

SQL> alter session set optimizer_mode=all_rows;

Session altered.

SQL> select * from emp;

14 rows selected.

SQL>  select sql_id,child_number,optimizer_mode,address,hash_value,parsing_user_id

from v$sql

where sql_text like '%select * from emp%'

and sql_text not like '%v$sql%';  2    3    4

SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS  HASH_VALUE PARSING_USER_ID

------------- ------------ ---------- -------- ---------- ---------------

a2dk8bdn0ujx7            0 FIRST_ROWS 44B9A5C0 1745700775               0

a2dk8bdn0ujx7            1 ALL_ROWS   44B9A5C0 1745700775               0

SQL>

SQL> select child_number,child_address, optimizer_mode_mismatch, optimizer_mismatch

from v$sql_shared_cursor

where sql_id='a2dk8bdn0ujx7'

and address='44B9A5C0';  2    3    4

CHILD_NUMBER CHILD_AD O O

------------ -------- - -

           0 44B36D3C N N

           1 490744D0 Y N

SQL>

• optimizer_mode_mismatch 
• 두번째 child 커서는 옵티마이저 모드 불일치 때문에 생성 

• optimizer_mismatch
• 옵티마이저 모드를 제외한 다른 옵티마이저 관련 파라미트 불일치
• 10gr1 -> 구분하지 않고 optimizer_mismatch 하나로 보여줌

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

• 의미적으로 같고 실행환경이 같은데도 커서를 공유하지 못해 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’;

• 서로 다른 sql로 각각 하드파싱을 일으키면 서로 다른 공간을 차지하면서 shared pool 을낭비
• 개발 초기에 sql 작성 표준을 정해 이를 준수