커서공유 (by manon94) [2010.04.05]
04 커서공유
(1) 커서란?
- 공유커서(Shared Cursor)
- 라이브러리 캐시에 공유되어 있는 Shared SQL Area
- SQL을 실행하는데 필요한 루틴(routine)을 정의한 것
- Java의 클레스
- 세션커서(Session Cursor)
- Private SQL Area에 저장되는 커서
- 라이브러리 캐시에 공유되어 있는 공유커서를 인스턴스화하기 위해 PGA 에 메모리공간을 할당
- 실제 대이터를 추출하기 위한 준비작업
- Java의 클레스를 상속받은 객체
- 애플리케이션 커서(Application Cursor)
- 세션커서를 가리키는 핸들
- 세션커서를 핸들링하기 위한 애플리케이션 리소스 할당
그림 4-7
(2) 커서공유
- 공유커서
- 라이브러리캐시에 공유되어 있는 커서
- v$sql을 통해 확인
{section}
{column:width=50}
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
시스템이 변경되었습니다.
{column}
{column:width=50}{column}
{section}
{section}
{column:width=50}
SQL> SELECT /* cursor_test */
2 empno, ename, job, sal, deptno
3 FROM emp
4 WHERE empno = 7369
5 ;
EMPNO ENAME JOB SAL DEPTNO
---------- -------------------- ------------------ ---------- ----------
7369 SMITH CLERK 800 20
SQL> SELECT sql_id
2 , parse_calls
3 , loads
4 , executions
5 , invalidations
6 , DECODE(SIGN(invalidations), 1, (loads - invalidations), 0) reloads
7 FROM v$sql
8 WHERE sql_text LIKE '%cursor_test%'
9 AND sql_text NOT LIKE '%V$SQL%'
10 ;
SQL_ID PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS RELOADS
-------------------------- ----------- ---------- ---------- ------------- ----------
9v6cbm9canuj8 1 1 1 0 0
SQL> SELECT /* cursor_test */
2 empno, ename, job, sal, deptno
3 FROM emp
4 WHERE empno = 7369
5 ;
EMPNO ENAME JOB SAL DEPTNO
---------- -------------------- ------------------ ---------- ----------
7369 SMITH CLERK 800 20
SQL> SELECT /* cursor_test */
2 empno, ename, job, sal, deptno
3 FROM emp
4 WHERE empno = 7369
5 ;
EMPNO ENAME JOB SAL DEPTNO
---------- -------------------- ------------------ ---------- ----------
7369 SMITH CLERK 800 20
SQL> SELECT sql_id
2 , parse_calls
3 , loads
4 , executions
5 , invalidations
6 , DECODE(SIGN(invalidations), 1, (loads - invalidations), 0) reloads
7 FROM v$sql
8 WHERE sql_text LIKE '%cursor_test%'
9 AND sql_text NOT LIKE '%V$SQL%'
10 ;
SQL_ID PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS RELOADS
-------------------------- ----------- ---------- ---------- ------------- ----------
9v6cbm9canuj8 3 1 3 0 0
{column}
{column:width=50}{column}
{section}
- v$sql 조회항목의 의미
- parse_call : 라이브러리 캐시에서 SQL을 찾으려는 요청횟수
- loads : 하드파싱을 거친 SQL 실행계획을 라이브러리 캐시에 적재한 횟수
- executions : SQL을 수행한 횟수
- reloads : 커서가 무효화된 횟수. 커서가 참조하고 있는 오브젝트에 변화가 일어났음을 의미.
- v$sql 조회결과의 해석
- 같은 SQL이 3번 수행되어 한번의 하드파싱이 일어나 공유커서에 저장되었으며
- 이는 저장된 공유커서가 재사용되었음을 의미한다.
- 다른 세션으로 접속하여 테스트
{section}
{column:width=50}
SQL> disconn
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production에서 분리되었습니다.
SQL> conn scott/tiger
연결되었습니다.
SQL> SELECT /* cursor_test */
2 empno, ename, job, sal, deptno
3 FROM emp
4 WHERE empno = 7369
5 ;
EMPNO ENAME JOB SAL DEPTNO
---------- -------------------- ------------------ ---------- ----------
7369 SMITH CLERK 800 20
SQL> SELECT sql_id
2 , parse_calls
3 , loads
4 , executions
5 , invalidations
6 , DECODE(SIGN(invalidations), 1, (loads - invalidations), 0) reloads
7 FROM v$sql
8 WHERE sql_text LIKE '%cursor_test%'
9 AND sql_text NOT LIKE '%V$SQL%'
10 ;
SQL_ID PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS RELOADS
-------------------------- ----------- ---------- ---------- ------------- ----------
9v6cbm9canuj8 4 1 4 0 0
{column}
{column:width=50}{column}
{section}
- 조회 결과
- 다른 세션에서도 공유된 커서가 재사용되었음을 알 수 있다.
- 커서가 재사용되지 않는 경우는? 공유커서의 무효화
- 커서에서 참조하는 오브젝트의 변화(DDL 등, DML은 무관)
- 컬럼의 추가/삭제/변경 : 이전의 커서가 잘못된 컬럼참조를 할수 있으므로
- 인덱스의 추가 : 이전에 수립된 실행계획이 최적이 아닐 수 있으므로
- 통계정보 생성 : 마찬가지로
- 통계정보 수집 테스트
{section}
{column:width=50}
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS
3 ( ownname => USER
4 , tabname => 'EMP'
5 , no_invalidate => FALSE
6 );
7 END;
8 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT sql_id
2 , parse_calls
3 , loads
4 , executions
5 , invalidations
6 , DECODE(SIGN(invalidations), 1, (loads - invalidations), 0) reloads
7 FROM v$sql
8 WHERE sql_text LIKE '%cursor_test%'
9 AND sql_text NOT LIKE '%V$SQL%'
10 ;
선택된 레코드가 없습니다.
SQL> SELECT /* cursor_test */
2 empno, ename, job, sal, deptno
3 FROM emp
4 WHERE empno = 7369
5 ;
EMPNO ENAME JOB SAL DEPTNO
---------- -------------------- ------------------ ---------- ----------
7369 SMITH CLERK 800 20
SQL> SELECT sql_id
2 , parse_calls
3 , loads
4 , executions
5 , invalidations
6 , DECODE(SIGN(invalidations), 1, (loads - invalidations), 0) reloads
7 FROM v$sql
8 WHERE sql_text LIKE '%cursor_test%'
9 AND sql_text NOT LIKE '%V$SQL%'
10 ;
SQL_ID PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS RELOADS
-------------------------- ----------- ---------- ---------- ------------- ----------
9v6cbm9canuj8 1 2 1 1 1
{column}
{column:width=50}{column}
{section}
- 커서 공유의 키(식별자)
- 커서가 공유되려면 쿼리를 식별할 수 있는 키가 있어야 하는데 이 키는 쿼리문장(sql_fulltext) 그 자체이다.
- 10g 부터는 sql_id 가 식별자의 역할을 할 수 있다.
(3) Child 커서를 공유하지 못하는 경우
- Child 커서란?
- 100% 동일한 SQL 문장인데도 불구하고 공유되지 않는 경우 Child 커서를 생성하게 된다.
- 테스트
{section}
{column:width=50}
SQL> SELECT * FROM emp;
SQL> SELECT sql_id
2 , version_count
3 , optimizer_mode
4 , address
5 , hash_value
6 FROM v$sqlarea
7 WHERE sql_text LIKE '%SELECT * FROM emp%'
8 AND sql_text NOT LIKE '%V$SQL%'
9 ;
SQL_ID VERSION_COUNT OPTIMIZER_MODE ADDRESS HASH_VALUE
------------- ------------- -------------- -------- ----------
4u5074pdutxzs 2 ALL_ROWS 2FF85AF4 1538062328
SQL> SELECT sql_id
2 , child_number
3 , optimizer_mode
4 , address
5 , hash_value
6 , parsing_user_id
7 FROM v$sql
8 WHERE sql_text LIKE '%SELECT * FROM emp%'
9 AND sql_text NOT LIKE '%V$SQL%'
10 ;
SQL_ID CHILD_NUMBER OPTIMIZER_MODE ADDRESS HASH_VALUE PARSING_USER_ID
------------- ------------ -------------- -------- ---------- ---------------
4u5074pdutxzs 0 ALL_ROWS 2FF85AF4 1538062328 33
4u5074pdutxzs 1 ALL_ROWS 2FF85AF4 1538062328 36
SQL> SELECT * FROM dual;
SQL> SELECT sql_id
2 , version_count
3 , optimizer_mode
4 , address
5 , hash_value
6 FROM v$sqlarea
7 WHERE sql_text LIKE '%SELECT * FROM dual%'
8 AND sql_text NOT LIKE '%V$SQL%'
9 ;
SQL_ID VERSION_COUNT OPTIMIZER_MODE ADDRESS HASH_VALUE
------------- ------------- -------------- -------- ----------
3vjxpmhhzngu4 2 ALL_ROWS 2FF5EDA4 570048324
SQL> SELECT sql_id
2 , child_number
3 , optimizer_mode
4 , address
5 , hash_value
6 , parsing_user_id
7 FROM v$sql
8 WHERE sql_text LIKE '%SELECT * FROM dual%'
9 AND sql_text NOT LIKE '%V$SQL%'
10 ;
SQL_ID CHILD_NUMBER OPTIMIZER_MODE ADDRESS HASH_VALUE PARSING_USER_ID
------------- ------------ -------------- -------- ---------- ---------------
3vjxpmhhzngu4 1 ALL_ROWS 2FF5EDA4 570048324 36
{column}
{column:width=50}{column}
{section}
- 동일한 SQL 문장이 Child커서를 갖게 되는 이유
- 오브젝트명은 같지만 유저에 따라 다른 오브젝트를 가리킬때
- 오브젝트 변경으로 무효화된 커서를 처음 사용하려 했으나 특정세션이 아직 기존 커서를 사용중(pin)일때
- 옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를때
- 입력된 바인드값의 길이가 크게 다를 때
- NLS 파라미터가 다를때
- SQL트레이스를 활성화시켰을 때
- v$sql_shared_cursor
- 새로운 Child커서가 기존커서와 공유되지 못한 이유를 설명해준다
- 267페이지 Desc 참조
- 옵티마이저 모드 변경 테스트
{section}
{column:width=50}
SQL> ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS';
SQL> SELECT * FROM emp;
SQL> SELECT sql_id
2 , child_number
3 , child_address
4 , optimizer_mode_mismatch
5 , optimizer_mismatch
6 , auth_check_mismatch
7 , translation_mismatch
8 FROM v$sql_shared_cursor
9 WHERE sql_id = '4u5074pdutxzs'
10 ;
SQL_ID CHILD_NUMBER CHILD_AD OP OP AU TR
-------------------------- ------------ -------- -- -- -- --
4u5074pdutxzs 0 335BFBEC N N N N
4u5074pdutxzs 1 2FFC2AB8 N N Y Y
4u5074pdutxzs 2 2FFCE6B4 Y N N N
{column}
{column:width=50}{column}
{section}
- 입력된 바인드변수값이 다를때 테스트
{section}
{column:width=50}
SQL> CREATE TABLE t(c VARCHAR2(4000));
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> DECLARE
2 l_cnt NUMBER;
3 l_child_cnt NUMBER;
4 l_prev_child_cnt NUMBER;
5 l_bind_value VARCHAR2(4000);
6 l_sql_id VARCHAR2(13);
7 BEGIN
8 l_prev_child_cnt := 0;
9
10 FOR c IN 1..4000
11 LOOP
12 l_bind_value := LPAD('A', c, '0');
13
14 SELECT COUNT(*)
15 INTO l_cnt
16 FROM t
17 WHERE c = l_bind_value
18 ;
19
20 -- 맨 처음에만 sql_id를 찾아 출력
21 IF c = 1 THEN
22 SELECT prev_sql_id
23 INTO l_sql_id
24 FROM v$session
25 WHERE sid = USERENV('SID')
26 AND username IS NOT NULL
27 AND prev_hash_value <> 0
28 ;
29
30 DBMS_OUTPUT.PUT_LINE('SQL_ID --> ' || l_sql_id);
31 END IF;
32
33 SELECT COUNT(*)
34 INTO l_child_cnt
35 FROM v$sql
36 WHERE sql_id = l_sql_id
37 ;
38
39 IF l_prev_child_cnt < l_child_cnt THEN -- 새 Child 커서가 생길 때마다
40 DBMS_OUTPUT.PUT_LINE(c);
41 l_prev_child_cnt := l_child_cnt;
42 END IF;
43 END LOOP;
44 END;
45 /
SQL_ID --> f6qwn8zavty07
1
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
{column}
{column:width=50}{column}
{section}
- 테스트 결과 책과 다르게 나옴
- 1, 33, 129, 2001
- 테스트 결과 책과 다르게 나옴
(4) Parent 커서를 공유하지 못하는 경우
- 의미적으로는 같은 Sql구문이지만 문자열에 차이가 있는 경우
- 공백문자 또는 줄바꿈
- 대소문자 구분
- 테이블 Owner 명시
- 주석
- 옵티마이져 힌트 : first_rows, all_rows
- 조건절의 비교값
- 기타 등등
- 커서 공유 실패
- 1,2,3,4번은 같은 실행계획이지만 문자열을 조금 다르게 기술함으로 인해
서로 다른 sql이 되어 각각 하드 파싱을 일으키며 Shared Pool공간을 낭비 - 커서 공유 실패를 예방하려면 Sql 작성 표준을 정해 이를 준수하도록 해야 한다.
(예) 오라클 예약어는 대문자로 사용자 객체명은 소문자로 통일
한줄에 쭈욱 나열하지 말고 의미가 달라지는 구문은 줄바꿈 - 5번은 의도적으로 실행계획을 달리 가져가고자 함
- 6번은 리터럴값을 문자열로 처리해서 sql 실행
반복 수행문안에서 사용되거나 자주 사용되는 OLTP성 업무의 쿼리라면 치명적일 수 있음
바인드 변수를 사용하여 해결.
- 1,2,3,4번은 같은 실행계획이지만 문자열을 조금 다르게 기술함으로 인해