꿈꾸는 개발자, DBA 커뮤니티 구루비

h1.커서공유
h3.커서?

공유커서(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을 실행한 사용자에 따라 다른 오브젝트를 가리킬 때
{code:sql} : 상단테스트로 확인 {code}

2) 참조 오브젝트가 변경돼 커서가 무효화되면 이후 그 커서를 처음 사용하려는 세션에 의해 다시 하드파싱돼야 하는데, 특정 세션이 아직 기존 커서를 사용 중(pin) 일 때
{code:sql} 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) 옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를 때||
|{code:sql}
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) 입력된 바인트 값의 길이가 크게 다를 때
{code:sql} 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©;
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 파라미터를 다르게 설정했을 때 ||
|{code:sql}
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 트레이스를 활성화 했을때
{code: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. 공백 문자 또는 줄바꿈 ||
|{code:sql}

SELECT * FROM CUSTOMER;
SELECT *   FROM CUSTOMER;

2. 대소문자 구분
{code:sql}

SELECT * FROM CUSTOMER;
SELECT * FROM Customer;



|| 3. 테이블 Owner명시 ||
|{code:sql}

SELECT * FROM CUSTOMER;
SELECT *   FROM HR.CUSTOMER;

4. 주석(Commnet)
{code:sql}

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



|| 5. 옵티마지어흰트 ||
|{code:sql}

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

6. 조건절 비교값
{code:sql}

SELECT * FROM CUSTOMER WHERE CUST_ID = '000001';
SELECT * FROM CUSTOMER WHERE CUST_ID = '000002';



- v$sql_shared_cursor 다이나믹뷰는 새로운 Child 커서가 왜 기존 Child 커서와 공유되지못한지 보여준다
책 267 p 참조

커서 공유 (by bshman) [2012.05.02]