4._커서_공유

조회 수 13619 추천 수 0 2013.09.05 09:42:10
남송휘 *.73.93.2

(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) 
    • 세션 커서를 가리키는 핸들
    • 클라이언트 애플리케이션에 리소스를 할당하는 것

dd.gif



(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 작성 표준을 정해 이를 준수





  • 오라클 고도화 원리와 해법 1 (bysql.net 2012년 1차 스터디)
  • 작성자:  남송휘
  • 최초작성일: 2012년 4월 26 일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^