Oracle - Child Cursor

Posted 05 20, 2012 13:50, Filed under: DataBase/Oracle


# Child 커서

SQL 하다당 여러 개의 Child 커서를 갖는 것이 바람직한 현상은 아니다.
AWR 리포트에서 Version Count가 20개 이상인 SQL 목록을 보여주는 이유도 그 때문이다.
아래는 Version Count가 가장 높은 SQL문을 찾는 쿼리다.
SUNSHINY@ORACLE11> select substr(sql_text, 1, 30) as SQL_TEXT, 
      SQL_ID               , 
      VERSION_COUNT        ,
      USERS_OPENING        
  from v$sqlarea
  order by version_count desc
; 

SQL_TEXT                   SQL_ID                         VERSION_COUNT 
---------------------------------------------------------------- ------------- ----------------
select value$ from sys.props$  9p6bq1v54k13j            4       
select * from emp                       a2dk8bdn0ujx7          3        
SELECT PARAMETER_VALUE      08bqjmf8490s2            3      
 SELECT source,        (case w   6wm3n4d7bnddg          2       
SELECT advisor_id FROM         gdn3ysuyssf82              2      
 SELECT sum(case when         47mm81hm9sggy         2       
SELECT OWNER FROM            d8mayxqw0wnpv          2       
SELECT a.name task_             5s34t44u10q4g             2        
update seq$ set                     4m7m0t6fjcs5x              2        
select /*+ index(idl_               cvn54b7yz0s8u              2       
SELECT owner#, property      c8gnrhxma4tas              2        
SELECT dbin.instance_           69k5bhm12sz98            2         
 SELECT nvl(e1,0) as e1,        18c2yb5aj919t               2         
select obj#,type#,ctime,      3nkd3g3ju5ph1               2       


SUNSHINY@ORACLE11>desc v$sqlarea
이름                             널 유형             
------------------------------ - -------------- 
SQL_TEXT                         VARCHAR2(1000) 
SQL_FULLTEXT                     CLOB           
SQL_ID                           VARCHAR2(13)   
SHARABLE_MEM                     NUMBER         
PERSISTENT_MEM                   NUMBER         
RUNTIME_MEM                      NUMBER         
SORTS                            NUMBER         
VERSION_COUNT                    NUMBER         
LOADED_VERSIONS                  NUMBER         
OPEN_VERSIONS                    NUMBER         
USERS_OPENING                    NUMBER         
FETCHES                          NUMBER         
EXECUTIONS                       NUMBER         
PX_SERVERS_EXECUTIONS            NUMBER         
END_OF_FETCH_COUNT               NUMBER         
USERS_EXECUTING                  NUMBER         
LOADS                            NUMBER         
FIRST_LOAD_TIME                  VARCHAR2(19)   
INVALIDATIONS                    NUMBER         
PARSE_CALLS                      NUMBER         
DISK_READS                       NUMBER         
DIRECT_WRITES                    NUMBER         
BUFFER_GETS                      NUMBER         
APPLICATION_WAIT_TIME            NUMBER         
CONCURRENCY_WAIT_TIME            NUMBER         
CLUSTER_WAIT_TIME                NUMBER         
USER_IO_WAIT_TIME                NUMBER         
PLSQL_EXEC_TIME                  NUMBER         
JAVA_EXEC_TIME                   NUMBER         
ROWS_PROCESSED                   NUMBER         
COMMAND_TYPE                     NUMBER         
OPTIMIZER_MODE                   VARCHAR2(10)   
OPTIMIZER_COST                   NUMBER         
OPTIMIZER_ENV                    RAW(2000 BYTE) 
OPTIMIZER_ENV_HASH_VALUE         NUMBER         
PARSING_USER_ID                  NUMBER         
PARSING_SCHEMA_ID                NUMBER         
PARSING_SCHEMA_NAME              VARCHAR2(30)   
KEPT_VERSIONS                    NUMBER         
ADDRESS                          RAW(8 BYTE)    
HASH_VALUE                       NUMBER         
OLD_HASH_VALUE                   NUMBER         
PLAN_HASH_VALUE                  NUMBER         
MODULE                           VARCHAR2(64)   
MODULE_HASH                      NUMBER         
ACTION                           VARCHAR2(64)   
ACTION_HASH                      NUMBER         
SERIALIZABLE_ABORTS              NUMBER         
OUTLINE_CATEGORY                 VARCHAR2(64)   
CPU_TIME                         NUMBER         
ELAPSED_TIME                     NUMBER         
OUTLINE_SID                      VARCHAR2(40)   
LAST_ACTIVE_CHILD_ADDRESS        RAW(8 BYTE)    
REMOTE                           VARCHAR2(1)    
OBJECT_STATUS                    VARCHAR2(19)   
LITERAL_HASH_VALUE               NUMBER         
LAST_LOAD_TIME                   DATE           
IS_OBSOLETE                      VARCHAR2(1)    
IS_BIND_SENSITIVE                VARCHAR2(1)    
IS_BIND_AWARE                    VARCHAR2(1)    
CHILD_LATCH                      NUMBER         
SQL_PROFILE                      VARCHAR2(64)   
SQL_PATCH                        VARCHAR2(30)   
SQL_PLAN_BASELINE                VARCHAR2(30)   
PROGRAM_ID                       NUMBER         
PROGRAM_LINE#                    NUMBER         
EXACT_MATCHING_SIGNATURE         NUMBER         
FORCE_MATCHING_SIGNATURE         NUMBER         
LAST_ACTIVE_TIME                 DATE           
BIND_DATA                        RAW(2000 BYTE) 
TYPECHECK_MEM                    NUMBER         
IO_CELL_OFFLOAD_ELIGIBLE_BYTES   NUMBER         
IO_INTERCONNECT_BYTES            NUMBER         
PHYSICAL_READ_REQUESTS           NUMBER         
PHYSICAL_READ_BYTES              NUMBER         
PHYSICAL_WRITE_REQUESTS          NUMBER         
PHYSICAL_WRITE_BYTES             NUMBER         
OPTIMIZED_PHY_READ_REQUESTS      NUMBER         
LOCKED_TOTAL                     NUMBER         
PINNED_TOTAL                     NUMBER         
IO_CELL_UNCOMPRESSED_BYTES       NUMBER         
IO_CELL_OFFLOAD_RETURNED_BYTES   NUMBER         


Version Count 수치가 높은 SQL 일수록 커서를 탐색하는 데 더 많은 시간을 소비하므로 library cache 래치에 대한 경합 발생 가능성을 증가시킨다.
예를 들어, ASP(Application Service Provider) 서비스를 하면서 테이블을 하나로 통합하지 않고 각 고객사마다 별도의 사용자 계정을 발급하는 형식으로 설계했다고 가정하자.
그러면 각 사용자 계정마다 같은 테이블을 생성해 두고, 로그인 할 때 각자의 계정을 사용함으로써 '같은 SQL문을 사용'하면서도 각기 자신의 테이블을 액세스하도록 구현할 것이다.
같은 SQL문을 사용하므로 Parent 커서는 공유하겠지만 Child 커서는 개별적으로 생성될 것이고, 모든 SQL에 대해 많은 Child 커서를 갖는 구조이므로 당연히 라이브러리 캐시 효율은 나빠진다.

하나의 SQL 문장이 여러 개 Child 커서를 갖게 되는 이유는 여러 가지가 있다.

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

v$sql_shared_cursor 다이나믹 뷰는 새로운 Child 커서가 왜 기존 Child 커서와 공유되지 못했는지 이유를 설명해 준다.
SUNSHINY@ORACLE11>desc v$sql_shared_cursor;
이름                            널 유형           
----------------------------- - ------------ 
SQL_ID                          VARCHAR2(13) 
ADDRESS                         RAW(8 BYTE)  
CHILD_ADDRESS                   RAW(8 BYTE)  
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)  


같은 쿼리를 두 번 수행하는데, 한번은 옵티마이저 모드를 FIRST_ROWS로, 다른 한 번은 ALL_ROWS로 설정한 상태에서 수행하고 v$sql_shared_cursor를 조회해 보았다.
SUNSHINY@ORACLE11> alter system flush shared_pool;

System altered.

SUNSHINY@ORACLE11> alter session set optimizer_mode = first_rows;

Session altered.

SUNSHINY@ORACLE11> select * from emp;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30
      7521 WARD       SALESMAN          7698 22-FEB-81       1250        500       30
      7566 JONES      MANAGER          7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN          7698 28-SEP-81       1250       1400       30
      7698 BLAKE      MANAGER          7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER          7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN          7698 08-SEP-81       1500      0       30
      7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
      7900 JAMES      CLERK          7698 03-DEC-81        950            30
      7902 FORD       ANALYST          7566 03-DEC-81       3000            20
      7934 MILLER     CLERK          7782 23-JAN-82       1300            10

14 rows selected.

SUNSHINY@ORACLE11> alter session set optimizer_mode = all_rows;

Session altered.

SUNSHINY@ORACLE11> select * from emp;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30
      7521 WARD       SALESMAN          7698 22-FEB-81       1250        500       30
      7566 JONES      MANAGER          7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN          7698 28-SEP-81       1250       1400       30
      7698 BLAKE      MANAGER          7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER          7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN          7698 08-SEP-81       1500      0       30
      7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
      7900 JAMES      CLERK          7698 03-DEC-81        950            30
      7902 FORD       ANALYST          7566 03-DEC-81       3000            20
      7934 MILLER     CLERK          7782 23-JAN-82       1300            10

14 rows selected.

SUNSHINY@ORACLE11> 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%'
;   

SQL_ID          CHILD_NUMBER    OPTIMIZER_ ADDRESS    HASH_VALUE PARSING_USER_ID
------------- ------------ ---------- ---------------- ---------- ---------------
a2dk8bdn0ujx7         0         FIRST_ROWS 00000000CF959598        1745700775           85
a2dk8bdn0ujx7         1         ALL_ROWS   00000000CF959598         1745700775           85

SUNSHINY@ORACLE11> select child_number, 
       child_address, 
       optimizer_mode_mismatch,
       optimizer_mismatch
  from v$sql_shared_cursor
  where sql_id = 'a2dk8bdn0ujx7'
  and address = '00000000CF959598'
; 

CHILD_NUMBER            CHILD_ADDRESS   OP  OP
------------ ---------------- - ------------------
       0            00000000CF943058     N     N
       1            00000000CF757258     Y     N


위 쿼리 결과를 통해 두 번째 Child 커서가 새로 생성된 이유가 옵티마이저 모드 불일치(optimizer_mode_mismatch) 때문인 것을 알 수 있다.
옵티마이저 모드를 제외한 다른 옵티마이저 관련 파라미터 불일치가 원인일 때는 optimizer_mismatch 컬럼에 표시하며, 10gR1까지는 이를 구분하지 않고 optimizer_mismatch 하나로 보여준다.



참고 : 오라클 성능 고도화 원리와 해법 I



※ 위 내용은, 여러 자료를 참고하거나 제가 주관적으로 정리한 것입니다.
   잘못된 정보나 보완이 필요한 부분을, 댓글 또는 메일로 보내주시면 많은 도움이 되겠습니다.
05 20, 2012 13:50 05 20, 2012 13:50


Trackback URL : http://develop.sunshiny.co.kr/trackback/765

Leave a comment

« Previous : 1 : ... 220 : 221 : 222 : 223 : 224 : 225 : 226 : 227 : 228 : ... 648 : Next »

Recent Posts

  1. HDFS - Python Encoding 오류 처리
  2. HP - Vertica ROS Container 관련 오류...
  3. HDFS - Hive 실행시 System Time 오류
  4. HP - Vertica 사용자 쿼리 이력 테이블...
  5. Client에서 HDFS 환경의 데이터 처리시...

Recent Comments

  1. Generally I do not read post on bl... 레기읏룸 셔츠룸 차이. 레깅스룸 부엉이 01 24,
  2. Wonderful site. A lot of useful in... /427 01 23,
  3. 안녕하세요^^ 배그핵
  4. 안녕하세요^^ 도움이 되셨다니, 저... sunshiny
  5. 정말 큰 도움이 되었습니다.. 감사합... 사랑은

Recent Trackbacks

  1. important link important link 25 01
  2. invoice printing and mailing services invoice printing and mailing services 25 01
  3. my review here my review here 25 01
  4. relocation services london relocation services london 25 01
  5. get redirected here get redirected here 24 01

Calendar

«   01 2020   »
      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31  

Bookmarks

  1. 위키피디아
  2. MysqlKorea
  3. 오라클 클럽
  4. API - Java
  5. Apache Hadoop API
  6. Apache Software Foundation
  7. HDFS 생태계 솔루션
  8. DNSBL - Spam Database Lookup
  9. Ready System
  10. Solaris Freeware
  11. Linux-Site
  12. 윈디하나의 솔라나라

Site Stats

TOTAL 2823642 HIT
TODAY 6 HIT
YESTERDAY 501 HIT