1st_커서 공유 (by jbh1827) [2012.11.16]
커서 공유
1. 커서란
{*}커서(Cursor)*
상황에 따라 여러 가지 의미로 사용되고 있으며
하나의 의미로 표현되지 않고 아래에서 설명하고 있는 3종류의 커서를 일컫는 말
(1)공유커서(shared cursor)
- JAVA, VB, Pro*C, PL/SQL 등 에서 SQL을 수행하면 서버 프로세스는 해당 SQL이 라이브러리 캐시에 공유돼 있는지를 먼저 확인한다. 없으면 최적화 과정을 통해 실행계획을 만들고， 라이브러리 캐시에 공유한다. 그렇게 라이브러리 캐시에 공유돼 있는 Shared SQL Area를 '커서'공유커서(shared cursor) 라고 부른다.
(2)세션커서(session cursor)
- 라이브러리 캐시에 공유돼 있는 공유커서(shared cursor)를 실행하기 위해 PGA 영역에 메모리를 할당(Private SQL Area)하며,커서를 실행하기 위한 준비과정(영역에 메모리를 할당 등)을 '커서가 오픈한다'라고 표현하며, PGA에 메모리를 할당하여 저장한 커서 정보(즉, 파싱된 SQL문과 문장을 수행하는데 필요한 기타 정보)를 '커서' 즉 세션커서라고 부른다.
- Private SQL Area:Private SQL Area는 Persistent Area와 Runtime Area 로 나뉘며, Shared SQL Area를 읽어 커서를 실행하는 데 필요한 정보들을 담는 역할과, 포인터를 유지하는 역할, 커서의 상태정보를 관리하는 역할을 한다.
- Persistent Area: 바인드 변수 등을 저장, 실행 종료된 후 커서가 닫힐 때 해제
- Runtime Area: select문만 fetch완료하거나 실행을 취소할 때 해제, insert, update, delete는 실행 종료 됨과 동시에 해제됨
- 이를 쉬운 예를 들어 자바와 비교를 한다면 'Shared SQL Area(Library Cache)'는 Class이고 'Private SQL Area'는 Class를 이용한 객체(Object)이다.
Cursor c = new Cursor();
(3)어플리케이션 커서(application cursor)
- PGA에 있는 커서를 핸들링하기 위해 다양한 클라이언트 애플리케이션 또한 리소스를 할당해야 하는데, 이 또한 '커서' 즉 애플리케이션 커서라고 한다.
2. 커서공유
오라클에서 "커서를 공유한다"는 표현은 라이브러리 캐시의 공유 커서(shared cursor)를 말함.
라이브러리 캐시에 공유돼 있는 커서의 수행 통계를 v$sql을 통해 조회 가능
SCOTT@orcl > ALTER SYSTEM FLUSH SHARED_POOL;
시스템이 변경되었습니다.
SCOTT@orcl > select /* cursor_test */*
2 from emp
3 where empno ='7788';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
SCOTT@orcl > 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
------------- ----------- ---------- ---------- ------------- ----------
3fu5pr5zu4t1h 1 1 1 0 0
| parse_calls | 라이브러리 캐시에서 SQL 커서를 찾으려고 요청한 횟수. |
|---|---|
| loads | 하드파싱을 거친 SQL 실행계획을 라이브러리 캐시에 적재한 횟수. |
| executions | SQL을 수행한 횟수. |
| invalidations | 커서가 무효화된 횟수. 커서가 참조하고 있는 오브젝트에 중요한 변화가 일어났음을 의미함. |
SCOTT@orcl > select /* cursor_test */*
2 from emp
3 where empno ='7788';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
SCOTT@orcl > select /* cursor_test */*
2 from emp
3 where empno ='7788';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
SCOTT@orcl > select /* cursor_test */*
2 from emp
3 where empno ='7788';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
SCOTT@orcl > 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%'
6 ;
SQL_ID PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS RELOADS
------------- ----------- ---------- ---------- ------------- ----------
3fu5pr5zu4t1h 4 1 4 0 0
SCOTT@orcl > disconnect
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options에서 분리되었습니다.
@ > conn scott/tiger
연결되었습니다.
SCOTT@orcl >
SCOTT@orcl > select /* cursor_test */*
2 from emp
3 where empno ='7788'
4 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
SCOTT@orcl > 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
------------- ----------- ---------- ---------- ------------- ----------
3fu5pr5zu4t1h 5 1 5 0 0
//;을 붙여서 실행을 하게 되면 동일하지 않은 쿼리로 분석을 하게 된다.
SCOTT@orcl > select /* cursor_test */*
2 from emp
3 where empno ='7788';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
SCOTT@orcl > 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
------------- ----------- ---------- ---------- ------------- ----------
3fu5pr5zu4t1h 5 1 5 0 0
1qnr0wvwvv2wk 1 1 1 0 0
- 공유 커서 무효화되는 경우
- 참조하고 있는 오브젝트에 컬럼이 추가/삭제되는 경우
- 새로운 인덱스 생성
- 오브젝트 통계를 새로 수집
- DDL문이 수행되는 경우
SCOTT@orcl > BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS
3 ( ownname => USER
4 , tabname => 'EMP'
5 , no_invalidate => FALSE
6 );
7 END;
8 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SCOTT@orcl > select /* cursor_test */*
2 from emp
3 where empno ='7788'
4 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
SCOTT@orcl > 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
------------- ----------- ---------- ---------- ------------- ----------
3fu5pr5zu4t1h 1 2 1 1 1
3. Child 커서를 공유하지 못하는 경우
{*}Child 커서란?*
SQL문장이 100% 동일한대도 동일쿼리를 실행하는 사용자 스키마가 다르다면 각각의 다른 테이블을 액세스하게 되며 실행계획 또한 달라진다.
이럴 때 오라클은 하나의 Parent커서에 여러개의 Child 커서를 가지게 된다.
- child 커서는 전체 문자열 그대로가 LCO인 Transient Object에만 child 커서를 생성하며,
Stored Object는 유일하게 식별가능한 이름이 부여되므로 child오브젝트를사용할 필요가 없다. - -V$SQLAREA는 Parent 커서 정보를 보여주고, V$SQL은 Child 커서를 보여준다.
- Child 커서가 생성되는 경우
- SQL에서 참조하는 오브젝트명이 같지만 SQL을 실행한 사용자에 따라 다른 오브젝트를 가리킬 때
- 참조 오브젝트가 변경돼 커서가 무효화되면 이후 그 커서를 처음 사용하려는 세션에 의해 다시 하드파싱돼야 하는데, 특정 세션이 아직 기존 커서를 사용중(pin)일 때
- 옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를 때
- 입력된 바인드 값의 길이가 크게 다를 ?
- NLS 파라미터를 다르게 설정했을 때
- SQL 트레이스를 활성화했을 때
SCOTT@orcl > conn sys/manager as sysdba;
연결되었습니다.
SYS@orcl AS SYSDBA> select /* cursor_test */* from emp
2 where empno ='7788';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
SYS@orcl AS SYSDBA> select sql_id , version_count, optimizer_mode, address, hash_value
2 from v$sqlarea
3 where sql_text like '%/* cursor_test */%'
4 and sql_text not like '%v$sql%' ;
SQL_ID VERSION_COUNT OPTIMIZER_ ADDRESS HASH_VALUE
------------- ------------- ---------- -------- ----------
1auaf2z18m8ks 1 ALL_ROWS 2E734D4C 3263799896
SYS@orcl AS SYSDBA> select sql_id , child_number, optimizer_mode, address, hash_value
2 from v$sql
3 where sql_text like '%/* cursor_test */%'
4 and sql_text not like '%v$sql%';
SQL_ID CHILD_NUMBER OPTIMIZER_ ADDRESS HASH_VALUE
------------- ------------ ---------- -------- ----------
1auaf2z18m8ks 0 ALL_ROWS 2E734D4C 3263799896
--다시 다른 계정으로 들어가서 테이블 같이 쿼리 실행
SCOTT@orcl > select /* cursor_test */* from emp
2 where empno ='7788';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
SCOTT@orcl > select sql_id , version_count, optimizer_mode, address, hash_value
2 from v$sqlarea
3 where sql_text like '%/* cursor_test */%'
4 and sql_text not like '%v$sql%' ;
SQL_ID VERSION_COUNT OPTIMIZER_ ADDRESS HASH_VALUE
------------- ------------- ---------- -------- ----------
1auaf2z18m8ks 2 ALL_ROWS 2E734D4C 3263799896
1auaf2z18m8ks 2 ALL_ROWS 2E734D4C 3263799896
SCOTT@orcl > select sql_id , child_number, optimizer_mode, address, hash_value
2 from v$sql
3 where sql_text like '%/* cursor_test */%'
4 and sql_text not like '%v$sql%';
SQL_ID CHILD_NUMBER OPTIMIZER_ ADDRESS HASH_VALUE
------------- ------------ ---------- -------- ----------
1auaf2z18m8ks 0 ALL_ROWS 2E734D4C 3263799896
1auaf2z18m8ks 1 ALL_ROWS 2E734D4C 3263799896
- v$sqlarea는 Parent커서 정보를 보여주고, v$sql은 Child커서 정보를 보여준다. Version_Count 수치가 높은 SQL 일수록 커서를 탐색하는 많은 시간이 소비되므로 Library cache 래치에 대한 경합이 발생가능성이 증가한다.
- 같은 SQL문을 사용하면 Parent 커서는 공유하겠지만, Child 커서는 개별적으로 생성 되고, 모든 SQL문에 대해 많은 Child 커서를 갖는 구조이므로 라이브러리 캐시 효율은 나빠진다.
SCOTT@orcl > ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS';
세션이 변경되었습니다.
SCOTT@orcl > select /* cursor_test */* from emp
2 where empno ='7788';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
SCOTT@orcl > ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS';
세션이 변경되었습니다.
SCOTT@orcl > select /* cursor_test */* from emp
2 where empno ='7788';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
SCOTT@orcl > select sql_id , version_count, optimizer_mode, address, hash_value
2 from v$sqlarea
3 where sql_text like '%/* cursor_test */%'
4 and sql_text not like '%v$sql%' ;
SQL_ID VERSION_COUNT OPTIMIZER_ ADDRESS HASH_VALUE
------------- ------------- ---------- -------- ----------
1auaf2z18m8ks 3 ALL_ROWS 2E734D4C 3263799896
1auaf2z18m8ks 3 FIRST_ROWS 2E734D4C 3263799896
SCOTT@orcl > select sql_id , child_number, optimizer_mode, address, hash_value,
parsing_user_id
2 from v$sql
3 where sql_text like '%/* cursor_test */%'
4 and sql_text not like '%v$sql%';
SQL_ID CHILD_NUMBER OPTIMIZER_ ADDRESS HASH_VALUE PARSING_USER_ID
------------- ------------ ---------- -------- ---------- ---------------
1auaf2z18m8ks 0 ALL_ROWS 2E734D4C 3263799896 0
1auaf2z18m8ks 1 ALL_ROWS 2E734D4C 3263799896 54
1auaf2z18m8ks 2 FIRST_ROWS 2E734D4C 3263799896 54
- 해당 테스트는 옵티마이저 모드 변경 때문에 child 커서가 생성됨을 알 수 있다.
.
- v$sql_shared_cursor 다이나믹 뷰는 새로운 Child 커서가 왜 기존 커서와 공유되지 못했는지 이유를 설명해준다.
SCOTT@orcl > select child_number, child_address, optimizer_mode_mismatch, optimizer_mismatch
2 from v$sql_shared_cursor
3 where sql_id ='1auaf2z18m8ks'
4 and address ='2E734D4C';
CHILD_NUMBER CHILD_AD O O
------------ -------- - -
0 2CA5CF88 N N
1 2E72158C N N
2 2E5B62EC Y N
- 해당 테스트는 입력된 바인드 값이 32, 128, 2000 바이트를 넘을 때마다 새로운 Child 커서가 생성됨을 보여줌
SCOTT@orcl > 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 LOOP
11 l_bind_value := LPAD('A', c, '0');
12
13 SELECT COUNT(*)
14 INTO l_cnt
15 FROM T
16 WHERE C = l_bind_value;
17
18
19 IF c = 1 THEN
20 SELECT PREV_SQL_ID
21 INTO l_sql_id
22 FROM V$SESSION
23 WHERE SID = USERENV('SID')
24 AND USERNAME IS NOT NULL
25 AND PREV_HASH_VALUE <> 0;
26
27 DBMS_OUTPUT.PUT_LINE('SQL_ID --> ' || l_sql_id);
28 END IF;
29
30 SELECT COUNT(*)
31 INTO l_child_cnt
32 FROM V$SQL
33 WHERE SQL_ID = l_sql_id;
34
35 IF l_prev_child_cnt < l_child_cnt THEN
36 DBMS_OUTPUT.PUT_LINE(c);
37
38 l_prev_child_cnt := l_child_cnt;
39 END IF;
40 END LOOP;
41 END;
42 /
SQL_ID --> f6qwn8zavty07
l
33
129
2001
PL/SQL 처리가 정상적으로 완료되었습니다.
SCOTT@orcl > SELECT CHILD_NUMBER,
2 BIND_MISMATCH
3 FROM V$SQL_SHARED_CURSOR
4 WHERE SQL_ID = 'f6qwn8zavty07'
5 ORDER BY CHILD_NUMBER
6 ;
CHILD_NUMBER B
------------ -
0 N
1 Y
2 Y
3 Y
4. 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';
- 이 중 가장 라이브러리 캐시 효율과 직접적으로 관련된 예는 (6)번 예제임
- 이와 같이 조건절에 바인드 변수를 사용하지 않고 서로 다른 Literal 값으로 문자열을 사용하게 되면 이는 시스템을 장애 상황으로 몰고 갈 수 있다.