오라클 성능 고도화 원리와 해법 I (2009년)
1. 커서(Cursor)란?
- 커서(Cursor)는 상황에 따라 여러 가지 의미로 사용되고 있으며 크게 아래의 3가지 의미를 말함
- 공유 커서(shared cursor)
- 세션 커서(session cursor)
- 애플리케이션 커서(application cursor)
1) 공유 커서(shared cursor)
- JAVA, VB, Pro*C, PL/SQL등에서 SQL을 수행하면 Server Process는 해당 SQL이 Library Cache에 공유되어
있는지를 먼저 확인하고 없으면 최적화를 통한 실행계획을 생성, 존재하면 Library Cache에 공유되어
있는 Cursor를 재활용하는데 이 때 Cursor를 공유 커서(shared cursor)라 부름
2) 세션 커서(session cursor)
- Library Cache에 공유되어 있는 커서를 실행할때는 PGA 영역에 'Private SQL Area' 메모리 영역을 할당하며
이는 'Persisten Area', 'Runtime Area'로 나뉜다. - Persisten Area : 바인드 변수 등을 저장함. 실행이 종료된 후 커서가 닫힐 때 해제됨
- Runtime Area : SELECT문은 모든 레코드를 Fetch 완료하거나 실행을 취소할 때 해제되지만
Insert, Update, Delete는 실행이 종료됨가 동시에 해제됨
- 이 때, 'Shared SQL Area(Library Cache)'를 읽어 커서를 실행하는데 필요한 정보들을 Private SQL Area에 담고,
shared cursor를 가리키는 포인터를 유지하며, 커서의 상태(open, bound, execute, close 등) 정보도 관리함 - 이런 과정들을 "커서를 오픈한다."라고 함
- 이를 JAVA의 Class와 비교한다면 'Shared SQL Area(Library Cache)'는 Class이고 'Private SQL Area'는
Class를 이용한 객체(Object)이다.
3) 애플리케이션 커서(application cursor)
- PGA에 있는 커서를 핸들링하려면 JAVA, VB, Pro*C, PL/SQL 같은 클라이언트 애플리케이션에도 리소스를 할당해야
하는데, 이 또한 커서라는 용어를 사용함
2. 커서 공유
- 오라클에서 "커서를 공유한다."라고 함은, 라이브러리 캐시의 공유 커서를 말함.
- 라이브버리 캐시에 공유돼 있는 커서의 수행 통계를 v$sql을 통해 조회해 볼 수 있음.
-- 1. 오라클 버전 확인
SELECT *
FROM V$VERSION
;
BANNER
-------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
PL/SQL Release 10.2.0.3.0 - Production
-- 2. 테이블 생성
DROP TABLE EMP;
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
COMMIT;
-- 3. 라이브러리 캐시 비움
ALTER SYSTEM FLUSH SHARED_POOL;
-- 4. 1회 조회
SELECT /* CURSOR_TEST */
EMPNO,
ENAME,
JOB,
SAL,
DEPTNO
FROM EMP
WHERE EMPNO = 7788
;
EMPNO ENAME JOB SAL DEPTNO
---------- -------------------- ------------------ ---------- ----------
7788 SCOTT ANALYST 3000 20
1 row selected.
-- 5. V$SQL 조회
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
-------------------------- ----------- ---------- ---------- ------------- ----------
6gyd34836phnb 1 1 1 0 0
1 row selected.
-- 6. 2회 조회
SELECT /* CURSOR_TEST */
EMPNO,
ENAME,
JOB,
SAL,
DEPTNO
FROM EMP
WHERE EMPNO = 7788
;
EMPNO ENAME JOB SAL DEPTNO
---------- -------------------- ------------------ ---------- ----------
7788 SCOTT ANALYST 3000 20
1 row selected.
-- 7. 3회 조회
SELECT /* CURSOR_TEST */
EMPNO,
ENAME,
JOB,
SAL,
DEPTNO
FROM EMP
WHERE EMPNO = 7788
;
EMPNO ENAME JOB SAL DEPTNO
---------- -------------------- ------------------ ---------- ----------
7788 SCOTT ANALYST 3000 20
1 row selected.
-- 8. V$SQL 조회
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
-------------------------- ----------- ---------- ---------- ------------- ----------
6gyd34836phnb 3 1 3 0 0
1 row selected.
| PARSE_CALLS | 라이브러리 캐시에서 SQL 커서를 찾으려는 요청 횟수 |
| LOADS | 하드파싱을 거친 SQL 실행계획을 라이브러리 캐시에 적재한 횟수 |
| EXECUTIONS | SQL을 수행한 횟수 |
| INVALIDATIONS | 커서가 무효화된 횟수. 커서가 참조하고 있는 오브젝트에 중요한 변화가 일어났음을 의미함. |
- 위의 결과는 SHARED Pool을 모두 비우고 테스트를 시작했으므로 SQL을 첫 번째 수행할 때 하드파싱이 일어난 것이며,
공유된 하나의 커서를 세번 재사용했음을 알 수 있다.
- 지금까지는 같은 세션에서 커서 공유 테스트를 해 보았고, 다른 세션에서도 공유가 되는지 테스트를 해봄
-- 1. 새로 연결
conn user/password@instance
Connected.
-- 2. 4회 조회
SELECT /* CURSOR_TEST */
EMPNO,
ENAME,
JOB,
SAL,
DEPTNO
FROM EMP
WHERE EMPNO = 7788
;
EMPNO ENAME JOB SAL DEPTNO
---------- -------------------- ------------------ ---------- ----------
7788 SCOTT ANALYST 3000 20
1 row selected.
-- 3. V$SQL 조회
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
-------------------------- ----------- ---------- ---------- ------------- ----------
6gyd34836phnb 4 1 4 0 0
- 이렇게 다른 세션에서도 커서를 공유할 수 있지만 아래의 이유로 커서 공유를 할 수 없을 수 있다.
- 커서가 참조하고 있던 오브젝트에 컬럼이 추가/삭제되는 경우
- 커서가 참조하고 있던 오브젝트에 새로운 인덱스가 만들어지는 경우
- 커서가 참조하고 있던 오브젝트의 통계를 새로 수집하는 경우
- 통계정보 생성 이후 커서 공유 테스트
-- 1. 통계정보 생성
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,
'EMP',
CASCADE => TRUE,
NO_INVALIDATE => FALSE
);
END;
/
PL/SQL procedure successfully completed.
-- 2. 5회 조회
SELECT /* CURSOR_TEST */
EMPNO,
ENAME,
JOB,
SAL,
DEPTNO
FROM EMP
WHERE EMPNO = 7788
;
EMPNO ENAME JOB SAL DEPTNO
---------- -------------------- ------------------ ---------- ----------
7788 SCOTT ANALYST 3000 20
1 row selected.
;
-- 3. V$SQL 조회
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
-------------------------- ----------- ---------- ---------- ------------- ----------
6gyd34836phnb 1 2 1 1 1
1 row selected.
- 커서가 공유되려면 커서를 식별하는 키 값이 같아야 되는데, 이 식별자는 'SQL 문장'임(V$SQL.SQL_FULLTEXT)
3. Child 커서를 공유하지 못하는 경우
- SQL 문장이 100% 동일한데도 SQL 커서를 공유하지 못하고 별도의 SQL 커서를 생성해야 할 때가 있음
만약 EMP 테이블이 SCOTT과 HR 스키마에 각각 생성되어 있다면 SCOTT과 HR로 로긴할 때 다른 테이블을
액세스해야 하고 실행계획도 달라져야 하며 이 때, Child Cursor를 생성함 - 영구적으로 보관되는 Stored Object(테이블, 인덱스, 함수, 프로시저, 패키지 등)는 이름으로 식별이
가능하므로 Child 오브젝트가 필요없지만, SQL 커서는 Transient Object는 전체 문자열이 LCO를
식별하는 식별자이므로 이에 따라 Child 커서가 생성될 수 있다. - V$SQLAREA는 Parent 커서 정보를 보여주고, V$SQL은 Child 커서를 보여준다.
(V$SQLAREA는 V$SQL을 GROUP BY 한 결과임) - 아래는 옵티마이저 모드를 변경하여 테스트 한 결과임
-- 1. 옵티마이저 모드 ALL_ROWS 활성화
ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS';
-- 2. EMP 테이블 조회
SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
1 row selected.
-- 3. 옵티마이저 모드 FIRST_ROWS 활성화
ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS';
-- 4. EMP 테이블 조회
SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
1 row selected.
-- 5. V$SQL 조회
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_MODE ADDRESS HASH_VALUE PARSING_USER_ID
-------------------------- ------------ -------------------- ---------------- ---------- ---------------
4ttqgu8uu8fus 0 ALL_ROWS 070000010BC93AB8 899955544 44
4ttqgu8uu8fus 1 FIRST_ROWS 070000010BC93AB8 899955544 44
-- 6. V$SQLAREA 조회
SELECT SQL_ID,
VERSION_COUNT,
OPTIMIZER_MODE,
ADDRESS,
HASH_VALUE
FROM V$SQLAREA
WHERE SQL_TEXT LIKE '%SELECT * FROM EMP%'
AND SQL_TEXT NOT LIKE '%V$SQL%'
;
SQL_ID VERSION_COUNT OPTIMIZER_MODE ADDRESS HASH_VALUE
-------------------------- ------------- -------------------- ---------------- ----------
4ttqgu8uu8fus 2 FIRST_ROWS 070000010BC93AB8 899955544
- SQL 하나당 여러 개의 Child 커서를 갖는 것은 바람직한 현상은 아님.
VERSION_COUNT 수치가 높은 SQL일수록 커서를 탐색하는 데 더 많은 시간을 소비하므로 라이브러리 캐시 래치에
대한 경합 발생 가능성을 증가시킴.
- 하나의 SQL 문장에 여러 개 Child 커서를 갖게 되는 이유
- SQL에서 참조하는 오브젝트명이 같지만 SQL을 실행한 사용자에 따라 다른 오브젝트를 가리킬 때
- 참조 오브젝트가 변경돼 커서가 무효화되면 이후 그 커서를 처음 사용하려는 세션에 의해 다시
하드파싱돼야 하는데, 특정 세션이 아직 기존 커서를 사용중(pin)일 때 - 옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를 때
- 입력된 바인드 값의 길이가 크게 다를 ?
- NLS 파라미터를 다르게 설정했을 때
- SQL 트레이스를 활성화했을 때
- 아래 테스트는 입력된 바인드 값이 32, 128, 2000 바이트를 넘을 때마다 새로운 Child 커서가 생성됨을 보여줌
-- 1. 테이블 생성
CREATE TABLE T(C VARCHAR2(4000));
-- 2. 라이브러리 캐시 비움
ALTER SYSTEM FLUSH SHARED_POOL;
-- 3. BYTE 생성 이미지
WITH TAB AS (SELECT LPAD('A', LEVEL, '0') VAL
FROM DUAL
CONNECT BY LEVEL <= 4000)
SELECT VAL,
LENGTHB(VAL) BITE
FROM TAB
;
A 1
0A 2
00A 3
000A 4
0000A 5
00000A 6
........
-- 4. PL/SQL 스크립트
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 -- 새 Child 커서가 생길 때마다
DBMS_OUTPUT.PUT_LINE(c);
l_prev_child_cnt := l_child_cnt;
END IF;
END LOOP;
END;
/
SQL_ID --> f6qwn8zavty07
1
33
129
2001
-- 5. V$SQL_SHARED_CURSOR 조회
SELECT CHILD_NUMBER,
BIND_MISMATCH
FROM V$SQL_SHARED_CURSOR
WHERE SQL_ID = 'f6qwn8zavty07'
ORDER BY CHILD_NUMBER
;
CHILD_NUMBER BI
------------ --
0 N
1 Y
2 Y
3 Y
4 rows selected.
4. Parent 커서를 공유하지 못하는 경우
- 이번에는 의미적으로 같고 실행환경이 같음에도 불구하고 커서를 공유하지 못해 Parant 커서 자체가
여러 개 생성되는 경우임
1) 공백문자 또는 줄바꿈
SELECT * FROM CUSTOMER;
SELECT * FROM CUSTOMER;
2) 대소문자 구분
SELECT * FROM CUSTOMER;
SELECT * FROM Customer;
3) 테이블 Owner 명시
SELECT * FROM CUSTOMER;
SELECT * FROM HR.CUSTOMER;
4) 주석(Comment)
SELECT * FROM CUSTOMER;
SELECT /* 주석문 */ * FROM CUSTOMER;
5) 옵티마이저 힌트 사용
SELECT * FROM CUSTOMER;
SELECT /*+ ALL_ROWS */ * FROM CUSTOMER;
6) 조건절 비교 값
SELECT * FROM CUSTOMER WHERE CUST_ID = '0000001';
SELECT * FROM CUSTOMER WHERE CUST_ID = '0000002';
- 이 중 가장 라이브러리 캐시 효율과 직접적으로 관련된 예는 6)번 예제임
- 이와 같이 조건절에 바인드 변수를 사용하지 않고 서로 다른 Literal 값으로 문자열을 사용하게 되면
이는 시스템을 장애 상황으로 몰고 갈 수 있다.
문서에 대하여
- 최초작성자 : ~xsoft
- 최초작성일 : 2009년 12월 05일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.*
"코어 오라클 데이터베이스 스터디 모임" 에서 2009년에 "오라클 성능 고도화 원리와 해법 I " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3035
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.