오라클 성능 고도화 원리와 해법 I (2012년)
목록
커서 공유 0 0 99,999+

by 구루비스터디 커서공유 [2018.03.20]

  1. 커서공유
    1. 커서?


커서공유

커서?

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



SQL> alter system flush shared_pool;

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

select /* cursor_test */*
from bsh_test1
where col1 ='1'

      COL1       COL2 COL3     COL4 COL5
---------- ---------- -------- ---- ----
         1       6316 20120105 1111 AAAA

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
------------- ----------- ---------- ---------- ------------- ----------
dfnbgv83nxhrx           1          1          1             0          0


-- 두번더 실행

SQL> select /* cursor_test */*
  2  from bsh_test1
  3  where col1 ='1';

      COL1       COL2 COL3     COL4 COL5
---------- ---------- -------- ---- ----
         1       6316 20120105 1111 AAAA

SQL> select /* cursor_test */*
  2  from bsh_test1
  3  where col1 ='1';

      COL1       COL2 COL3     COL4 COL5
---------- ---------- -------- ---- ----
         1       6316 20120105 1111 AAAA

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
------------- ----------- ---------- ---------- ------------- ----------
2dc55sdgg8wnw           3          1          3             0          0

-- 보너스부록(from 절 앞 bsh_test1 , where 앞 col1 앞에 의 스페이스를 주고 처리를하였을때 동일하지않은 쿼리로 분석)


SQL> select /* cursor_test */*
  2  from bsh_test1
  3  where col1 ='1';

SQL> select /* cursor_test */*
  2  from bsh_test1
  3  where col1 ='1';

SQL> select /* cursor_test */*
  2  from  bsh_test1
  3  where  col1 ='1';

SQL> select /* cursor_test */*
  2  from  bsh_test1
  3  where  col1 ='1';

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
------------- ----------- ---------- ---------- ------------- ----------
2dc55sdgg8wnw           2          2          2             0          0
7h49ssmgxm4u3           2          2          2             0          0

--DLL , 통계정보 수집 으로 인한경우 해당커서는 무효화됨

SQL> begin
  2  dbms_stats.gather_table_stats
  3  ( ownname => user, tabname => 'bsh_test1', no_invalidate => false );  <-- invalidate 값이 1올라감
  3  ( ownname => user, tabname => 'bsh_test1', no_invalidate => true );  <-- invalidate 값이 동일
  4  end;
  5  /

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


SQL> select /* cursor_test */*
  2  from  bsh_test1
  3  where  col1 ='1';

      COL1       COL2 COL3     COL4 COL5
---------- ---------- -------- ---- ----
         1       6316 20120105 1111 AAAA

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




-- child_cursor를 가지는경우

SQL> create table bshman_t
  2  (
  3  col1 varchar2(10)
  4  );

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

SQL> insert into bshman_t (col1) values('a');

1 개의 행이 만들어졌습니다.

SQL> show user;
USER은 "BSHMAN"입니다
SQL> conn /as sysdba
연결되었습니다.
SQL> create table bshman_t
  2  (
  3  col1 varchar2(10)
  4  );

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

SQL> insert into bshman_t (col1) values('a');

1 개의 행이 만들어졌습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> select /*bshman_t*/* from bshman_t
  2  where col1='a';

SQL> select sql_id , version_count, optimizer_mode, address, hash_value
  2  from v$sqlarea
  3  where sql_text like '%/*bshman_t*/%'
  4  and sql_text not like '%v$sql%'  ;

SQL_ID        VERSION_COUNT OPTIMIZER_ ADDRESS  HASH_VALUE
------------- ------------- ---------- -------- ----------
cvvaug1h81fuz             1 ALL_ROWS   33DE1F7C 1619049311


SQL> select sql_id , child_number, optimizer_mode, address, hash_value
  2  from v$sql
  3  where sql_text like '%/*bshman_t*/%'
  4  and sql_text not like '%v$sql%';

SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS  HASH_VALUE
------------- ------------ ---------- -------- ----------
cvvaug1h81fuz            0 ALL_ROWS   33DE1F7C 1619049311

--sysdba 계정으로 변경하여 조회함

SQL> select /*bshman_t*/* from bshman_t
  2  where col1='a';

SQL> select sql_id , version_count, optimizer_mode, address, hash_value
  2  from v$sqlarea
  3  where sql_text like '%/*bshman_t*/%'
  4  and sql_text not like '%v$sql%'  ;

SQL_ID        VERSION_COUNT OPTIMIZER_ ADDRESS  HASH_VALUE
------------- ------------- ---------- -------- ----------
cvvaug1h81fuz             2 ALL_ROWS   33DE1F7C 1619049311

SQL> select sql_id , child_number, optimizer_mode, address, hash_value
  2  from v$sql
  3  where sql_text like '%/*bshman_t*/%'
  4  and sql_text not like '%v$sql%'  ;

SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS  HASH_VALUE
------------- ------------ ---------- -------- ----------
cvvaug1h81fuz            0 ALL_ROWS   33DE1F7C 1619049311
cvvaug1h81fuz            1 ALL_ROWS   33DE1F7C 1619049311

-- 부록(modual은 왜변하지않는가...)

SQL> select /*bshman_t*/* from bshman_t
  2  where col1='a';


SQL> select sql_id, child_number, optimizer_mode, address, hash_value, parsing_u
ser_id ,module from v$sql
  2  where sql_text like '%/*bshman_t*/%'
  3  and sql_text not like '%*v$sql*%';

SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS  HASH_VALUE PARSING_USER_ID MODULE
--------------------------------------------------------------------------------
cvvaug1h81fuz            1 ALL_ROWS   2FB85A2C 1619049311               0encore

SQL> conn bshman/.....
연결되었습니다.
SQL> select /*bshman_t*/* from bshman_t
  2  where col1='a';


SQL> select sql_id, child_number, optimizer_mode, address, hash_value, parsing_u
ser_id ,module from v$sql
  2  where sql_text like '%/*bshman_t*/%'
  3  and sql_text not like '%*v$sql*%';

SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS  HASH_VALUE PARSING_USER_ID MODULE
------------- ------------ ---------- -------- ---------- --------------- ---------------
cvvaug1h81fuz            1 ALL_ROWS   2FB85A2C 1619049311              73 encore
  • Version_Count 수치가 높은 SQL 일수록 커서를 탐색하는 많은 시간이 소비되므로 Library cache 래치에 대한 경합이 발생가능성이 증가한다.
  • 같은 SQL문을 사용하면 Parent 커서는 공유하겠지만, Child 커서는 개별적으로 생성 되고,모든 SQL문에 대해 많은 Child 커서를 갖는 구조이므로 라이브러리 캐시 효율은 나빠진다.


  • 동일한 SQL 이 Child 커서를 갖게 되는 이유


1) SQL 에서 참조하는 오브젝트명이 같지만 SQL을 실행한 사용자에 따라 다른 오브젝트를 가리킬 때

 : 상단테스트로 확인


2) 참조 오브젝트가 변경돼 커서가 무효화되면 이후 그 커서를 처음 사용하려는 세션에 의해 다시 하드파싱돼야 하는데, 특정 세션이 아직 기존 커서를 사용 중(pin) 일 때

SQL> alter session set session_cached_cursors = 10;

세션이 변경되었습니다.

SQL> alter system flush shared_pool;

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

SQL> select /* cursor_test */*
  2  from  bsh_test1
  3  where  col1 ='1';

      COL1       COL2 COL3     COL4 COL5
---------- ---------- -------- ---- ----
         1       6316 20120105 1111 AAAA

SQL> begin
  2  dbms_stats.gather_table_stats
  3  ( ownname => user, tabname => 'bsh_test1', no_invalidate => false );
  4  end;
  5  /

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

SQL>
SQL> select sql_id , parse_calls, loads, executions, invalidations,
  2  decode(sign(invalidations), 1, (loads-invalidations), 0) reloads,users_open
ing, users_executing
  3  from v$sql
  4  where sql_text like '%/* cursor_test */%'
  5  and sql_text not like '%*v$sql*%';

선택된 레코드가 없습니다.

SQL> select /* cursor_test */*
  2  from  bsh_test1
  3  where  col1 ='1';

      COL1       COL2 COL3     COL4 COL5
---------- ---------- -------- ---- ----
         1       6316 20120105 1111 AAAA

SQL> select sql_id , parse_calls, loads, executions, invalidations,
  2  decode(sign(invalidations), 1, (loads-invalidations), 0) reloads,users_open
ing, users_executing
  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 USERS_OPENING USERS_EXECUTING
------------- ----------- ---------- ---------- ------------- ---------- ------------- ---------------
7h49ssmgxm4u3           1          2          1             1          1            0               0

-- 무효화처리가되어서 쿼리를 3번이상실행시켜

SQL> select /* cursor_test */*
  2  from  bsh_test1
  3  where  col1 ='1';

      COL1       COL2 COL3     COL4 COL5
---------- ---------- -------- ---- ----
         1       6316 20120105 1111 AAAA

SQL> select /* cursor_test */*
  2  from  bsh_test1
  3  where  col1 ='1';

      COL1       COL2 COL3     COL4 COL5
---------- ---------- -------- ---- ----
         1       6316 20120105 1111 AAAA

SQL> select /* cursor_test */*
  2  from  bsh_test1
  3  where  col1 ='1';

      COL1       COL2 COL3     COL4 COL5
---------- ---------- -------- ---- ----
         1       6316 20120105 1111 AAAA

SQL> select sql_id , parse_calls, loads, executions, invalidations,
  2  decode(sign(invalidations), 1, (loads-invalidations), 0) reloads,users_open
ing, users_executing
  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 USERS_OPENING USERS_EXECUTING
------------- ----------- ---------- ---------- ------------- ---------- ------------- ---------------
7h49ssmgxm4u3           4          2          4             1          1            1               0

SQL> begin
  2  dbms_stats.gather_table_stats
  3  ( ownname => user, tabname => 'bsh_test1', no_invalidate => false );
  4  end;
  5  /

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

SQL> select /* cursor_test */*
  2  from  bsh_test1
  3  where  col1 ='1';

      COL1       COL2 COL3     COL4 COL5
---------- ---------- -------- ---- ----
         1       6316 20120105 1111 AAAA

SQL> select sql_id , parse_calls, loads, executions, invalidations,
  2  decode(sign(invalidations), 1, (loads-invalidations), 0) reloads,users_open
ing, users_executing
  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 USERS_OPENING USERS_EXECUTING
------------- ----------- ---------- ---------- ------------- ---------- ------------- ---------------
7h49ssmgxm4u3           1          3          1             2          1            0               0


SQL> select /* cursor_test */*
  2  from  bsh_test1
  3  where  col1 ='1';

      COL1       COL2 COL3     COL4 COL5
---------- ---------- -------- ---- ----
         1       6316 20120105 1111 AAAA

SQL> select /* cursor_test */*
  2  from  bsh_test1
  3  where  col1 ='1';

      COL1       COL2 COL3     COL4 COL5
---------- ---------- -------- ---- ----
         1       6316 20120105 1111 AAAA

SQL> select /* cursor_test */*
  2  from  bsh_test1
  3  where  col1 ='1';

      COL1       COL2 COL3     COL4 COL5
---------- ---------- -------- ---- ----
         1       6316 20120105 1111 AAAA

SQL> select sql_id , parse_calls, loads, executions, invalidations,
  2  decode(sign(invalidations), 1, (loads-invalidations), 0) reloads,users_open
ing, users_executing
  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 USERS_OPENING USERS_EXECUTING
------------- ----------- ---------- ---------- ------------- ---------- ------------- ---------------
7h49ssmgxm4u3           4          3          4             2          1            1               0


SQL> disconnect
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options에서 분리되었습니다.
SQL> conn bshman

연결되었습니다.
SQL> select /* cursor_test */*
  2  from  bsh_test1
  3  where  col1 ='1';

      COL1       COL2 COL3     COL4 COL5
---------- ---------- -------- ---- ----
         1       6316 20120105 1111 AAAA

SQL> select sql_id , parse_calls, loads, executions, invalidations,
  2  decode(sign(invalidations), 1, (loads-invalidations), 0) reloads,users_open
ing, users_executing
  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 USERS_OPENING USERS_EXECUTING
------------- ----------- ---------- ---------- ------------- ---------- ------------- ---------------
7h49ssmgxm4u3           5          3          5             2          1            1               0

- child 커서를 가져야되는데 안가지네...


3) 옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를 때

SQL> select /*bshman_t*/* from bshman_t
  2  where col1='a';

SQL> select sql_id , child_number, optimizer_mode, address, hash_value
  2  from v$sql
  3  where sql_text like '%/*bshman_t*/%'
  4  and sql_text not like '%v$sql%';

SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS  HASH_VALUE
------------- ------------ ---------- -------- ----------
cvvaug1h81fuz            0 FIRST_ROWS 33DE1F7C 1619049311

SQL> alter session set optimizer_mode ='ALL_ROWS';

세션이 변경되었습니다.

SQL> select /*bshman_t*/* from bshman_t
  2  where col1='a';


SQL> select sql_id , child_number, optimizer_mode, address, hash_value
  2  from v$sql
  3  where sql_text like '%/*bshman_t*/%'
  4  and sql_text not like '%v$sql%';

SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS  HASH_VALUE
------------- ------------ ---------- -------- ----------
cvvaug1h81fuz            0 FIRST_ROWS 33DE1F7C 1619049311
cvvaug1h81fuz            1 ALL_ROWS   33DE1F7C 1619049311

SQL> select child_number, child_address, optimizer_mode_mismatch, optimizer_mismatch
  2  from v$sql_shared_cursor
  3  where sql_id ='cvvaug1h81fuz'
  4  and address ='33DE1F7C';

CHILD_NUMBER CHILD_AD O O
------------ -------- - -
           0 33DD6ACC N N
           1 2F95133C Y N


4) 입력된 바인트 값의 길이가 크게 다를 때

SQL> CREATE TABLE t(c VARCHAR2(4000));

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

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

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

  DECLARE
        l_cnt            NUMBER;
        l_child_cnt      NUMBER;
        l_prev_child_cnt NUMBER;
        l_bind_value     VARCHAR2(4000);
        l_sql_id         VARCHAR2(13);
    BEGIN
        l_prev_child_cnt := 0;

        FOR c IN 1..4000
        LOOP
            l_bind_value := LPAD('A', c, '0');

            SELECT COUNT(*)
              INTO l_cnt
              FROM t
             WHERE c = l_bind_value
            ;

            -- 맨 처음에만 sql_id를 찾아 출력
            IF c = 1 THEN
                SELECT prev_sql_id
                  INTO l_sql_id
                  FROM v$session
                 WHERE sid = USERENV('SID')
                   AND username IS NOT NULL
                   AND prev_hash_value <> 0
                ;

                DBMS_OUTPUT.PUT_LINE('SQL_ID --> ' || l_sql_id);
            END IF;

            SELECT COUNT(*)
              INTO l_child_cnt
              FROM v$sql
             WHERE sql_id = l_sql_id
            ;

            IF l_prev_child_cnt < l_child_cnt THEN
                DBMS_OUTPUT.PUT_LINE(c);
                l_prev_child_cnt := l_child_cnt;
            END IF;
        END LOOP;
    END;
    /
SQL_ID --> f6qwn8zavty07
1

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

SQL> select child_number, bind_mismatch
  2  from v$sql_shared_cursor
  3  where sql_id ='f6qwn8zavty07'
  4  order by child_number;

CHILD_NUMBER B
------------ -
           0 N
           1 Y
           2 Y


5) NLS 파라미터를 다르게 설정했을 때

SQL> update sys.props$ set value$='AMERICAN_AMERICA.KO16KSC5601' where name='NL_LANGUAGE';

1 행이 갱신되었습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> select /*bshman_t*/* from bshman_t
  2  where col1='a';


SQL> select sql_id , child_number, optimizer_mode, address, hash_value
  2  from v$sql
  3  where sql_text like '%/*bshman_t*/%'
  4  and sql_text not like '%v$sql%';

SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS  HASH_VALUE
------------- ------------ ---------- -------- ----------
cvvaug1h81fuz            0 ALL_ROWS   2FB04720 1619049311
cvvaug1h81fuz            1 ALL_ROWS   2FB04720 1619049311


6) SQL 트레이스를 활성화 했을때

SQL> alter system set sql_trace = true;

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

SQL> select /*bshman_t*/* from bshman_t
  2  where col1='a';

SQL> select sql_id , child_number, optimizer_mode, address, hash_value
  2  from v$sql
  3  where sql_text like '%/*bshman_t*/%'
  4  and sql_text not like '%v$sql%';

SQL_ID        CHILD_NUMBER OPTIMIZER_ ADDRESS  HASH_VALUE
------------- ------------ ---------- -------- ----------
cvvaug1h81fuz            0 ALL_ROWS   2FB04720 1619049311
cvvaug1h81fuz            1 ALL_ROWS   2FB04720 1619049311


h3.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. 주석(Commnet)


SELECT * FROM CUSTOMER;
SELECT /*주석*/*   FROM CUSTOMER;


5. 옵티마지어흰트


SELECT * FROM CUSTOMER;
SELECT /*+ all_rows */*   FROM CUSTOMER;


6. 조건절 비교값


SELECT * FROM CUSTOMER WHERE CUST_ID = '000001';
SELECT * FROM CUSTOMER WHERE CUST_ID = '000002';
  • v$sql_shared_cursor 다이나믹뷰는 새로운 Child 커서가 왜 기존 Child 커서와 공유되지못한지 보여준다
  • 책 267 p 참조
코어 오라클 데이터베이스 스터디 모임 에서 2012년에 오라클 성능 고도화 원리와 해법 I 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3099

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입