(1) 커서란?
라이브러리 캐시에 공유돼 있는 커서를 실행할 때는 우선 PGA 영역에 메모리를 할당
Private SQL area' 라고 하는데 ， Persistent Area 와 Runtime Area로 나뉜다.
Shared SQL Area를 읽어 커서를 실행하는 데 필요한 정보들을 Private SQL Area에 담고，
공유 커서를 가리키는 포인터를 유지한다.그리고 커서의 상태정보도 관리한다.
커서 를 실행하기 위한 이런 준비과정을 "커서를 오픈한다" 고 표현하고，
PGA에 저장된 커서 정보(즉,파싱된 SQL문과 문장을 수행하능데 필요한 기타 정보)를 또
한 '커서' 라고부른다.
PGA에 있는 커서를 핸들링하려면 JAVA, VB, Pro*C, PL!SQL 같은 클
라이언트 애플리케이션에도 리소스를 할당
(2) 커서 공유
TEST
– sys유저 접속
SQL> grant select_catalog_role to scott;
Statement Processed.
SQL> alter system flush shared_pool;
Statement Processed.
– SCOTT유저 접속
SQL> select /* cursor test */ empno, ename, job, sal, deptno
from emp
where empno = 7788;
EMPNO ENAME JOB SAL DEPTNO
1 rows selected.
SQL Execution Time > 00:00:00.093
Total Elapsed Time > 00:00:00.109
SQL_ID PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS RELOADS
1 rows selected.
SQL Execution Time > 00:00:00.031
Total Elapsed Time > 00:00:00.047
EMPNO ENAME JOB SAL DEPTNO
1 rows selected.
SQL Execution Time > 00:00:00.000
Total Elapsed Time > 00:00:00.015
EMPNO ENAME JOB SAL DEPTNO
1 rows selected.
SQL Execution Time > 00:00:00.016
Total Elapsed Time > 00:00:00.032
SQL_ID PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS RELOADS
1 rows selected.
SQL Execution Time > 00:00:00.015
Total Elapsed Time > 00:00:00.031
TEST (다른세션 접속후 커서공유 테스트)
EMPNO ENAME JOB SAL DEPTNO
1 rows selected.
SQL Execution Time > 00:00:00.031
Total Elapsed Time > 00:00:00.047
EMPNO ENAME JOB SAL DEPTNO
1 rows selected.
SQL Execution Time > 00:00:00.031
Total Elapsed Time > 00:00:00.031
SQL_ID PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS RELOADS
1 rows selected.
SQL Execution Time > 00:00:00.015
Total Elapsed Time > 00:00:00.031
다른 세션에서 같은 SQL을 수행할 때도 이전 세션에서 적재한 커서를 재사용했음을
알수있다.
TEST (통계 재생성후 커서공유 테스트)
SQL> execute DBMS_STATS.GATHER_TABLE_STATS
(ownname => USER, tabname => 'EMP'
,no_invalidate => FALSE
);
PL/SQL executed.
SQL Execution Time > 00:00:05.367
SQL> select /* cursor test */ empno, ename, job, sal, deptno
from emp
where empno = 7788;
EMPNO ENAME JOB SAL DEPTNO
1 rows selected.
SQL Execution Time > 00:00:00.062
Total Elapsed Time > 00:00:00.062
SQL> select /* cursor test */ empno, ename, job, sal, deptno
from emp
where empno = 7788;
EMPNO ENAME JOB SAL DEPTNO
1 rows selected.
SQL Execution Time > 00:00:00.062
Total Elapsed Time > 00:00:00.062
SQL_ID PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS RELOADS
1 rows selected.
SQL Execution Time > 00:00:00.031
Total Elapsed Time > 00:00:00.047
쿼리를 다시 수행한 후에 v$sql을 다시 조회해 보면， 적재 횟수가 아래처럼 2로 증가한
것을볼수있다.
라이브러리 캐시에 있는 커서틀이 여러 세션에 의해 공유되면서 반복 재사용되는 것
공유된 커서를 사용할 때는 최적화 및 Row-Source Generation 단계를 생략하고
곧바로 실행 단계로 넘어가므로 보다 효율적이고 빠르게 SQL을 수행한다
커서가 공유되려면 커서를 식별하는 키 값이 같아야 함
-> 라이브러리 캐시에서 커서를 식별하기 위해 시용되는 키 값은 'SQL 문장 그 자체'
-> SQL문을 구성하는 전체 문자열이 이름 역할을 한다는 뜻
-> SQL_ID와 SQL FULLTEXT는 1:1로 대응
SQL문 중간에 작은 공백문자 하나만 추가하더라도 서로 다른 SQL 문장으로 인식해 새로운
SQL_ID를발급받게 된다. 즉 커서가공유되지 않는다.
(3) Child 커서를 공유하지 못하는 경우
8QL마다 하나의 Parent 커서를 가지며， Child 커서는 여러 개일 수 있다.
실제 수행에 필요한 정보는 Child 커서에 담기므로 적어도 한 개의 Child 커서를 갖는다.
v$sqlarea는 Parent 커서 정보를 보여주고36) ' v$sql은 Child 커서 정보를 보여준다.
아래는 8C0TT과 HR 스키마 각각에 EMP 테이블을 만들고 각 계정으로 로그인 해서
select * from emp 쿼리를 수행한 후에 v$sqlarea와 v$sql을 쿼리했을 때의 결과를 보
이고 있다.
TEST
p397
Version Count 수치가 높은 SQL 일수록 커서를 탐색하는 데 더 많은 시간을 소비하므
로 library cache 래치에 대한 경합 발생 가능성을 증가시킨다.
v$sql_shared_ cursor 다이나믹 뷰는 새로운 Child 커서가 왜 기존 Child 커서와 공유
못하는지 확인
TEST (옵티마이저 옵션으로 인한 미스매치)
TEST (바인드 값의 사이즈에 따른 미스매치)
(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. 주석 (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' ;
이 외에도 더 다양한 케이스가 있을 것이다. 1. 2. 4번은 그 실행계획이 100% 같다.
그럼에도 문자열을 조금 다르게 기술했다는 이유 때문에 서로 다른 SQL로서 각각
하드파싱을 일으카고 서로 다른 공간을 차지하면서 Shared Pool을 낭비하게 된다. 이런
비효율을 줄이고 공유 가능한 형태로 SQL을 작성하려면 개발 초기에 SQL 작성 표준을
정해 이를 준수하도록 해야 한다.
5 번은 의도적으로 실행계획을 달리 가져가려는 것이므로 논외로 하고 라이브러리 캐시
효율과 직접 관련이 큰 것은 6번 같은 패턴이다. 즉 조건절에 바인드 변수를 사용하지 않
고 서로 다른 Literal 값으로 문자열을 대체하는 경우다.
만약 OLTP성 업무를 처리하는 애플리케이션에서 6번과 같은 패턴으로 SQL을 개발한
다변 결코 좋은 성능을 보장받을 수 없다. 개별 쿼리 성능으로 보면 잘 느끼지 못하지만
동시 트랜잭션이 몰리는 peak 시간대에 시스템을 장애 상황으로 몰고 가는 주범이다.
==================================================================================================================
공유풀은 힙(heap)으로 불리는 OS메모리 공간으로 구성됨.
힙(heap)은 헤더와 하나이상의 메모리익스텐트로으로 구성됨.
메모리익스텐트는 지속적으로 할당과 반납이 반복되며 여러개 작은조각으로 나누어짐.
메모리 영역이 작은단위로 나누어 지는 것을 공유풀단편화(FRAGMENTATION)이라 함.
단편화로 인해 쪼개진 영역은 청크라고 불리며 프리리스트에 의해 관리됨.
한번 사용된 청크는 다시 프리리스트에 등록되기 전까지 공유풀 LRU리스트에서 관리됨
LRU알고리즘을 이용하여 청크의 재사용률을 높이기 위해서임.
공유풀에 메모리 할당과정
1. 새로운 SQL에 대해 파스 수행하려면 힙영역 새로운 빈공간 할당받아야함.
2. 프리리스트에서 필요한 크기의 프리청크 서칭
3. 프리리스트 서칭후 있으면 힙영역 할당. 없으면 LRU리스트 사용가능 청크를 서칭
4. LRU리스트에도 없으면 4031에러 발생후 SQL파스 실패
예> SQL파스를 위해 256바이트가 필요할떄
1. 쉐어드풀 래치획득후 프리리스트로 부터 256바이트의 청크를 검색.
프리리스트에서 프리청크를 검색후 할당받기까지 모든단계에서 shared pool 래치를 할당 받아야 한다.
청크의 할당 해제가 빈번히 발생 한다면 청크는 더욱 작게 쪼개져 관리되어야 할 청크의 수가 증가 한다.
프리리스트를 검색하는 시간을 증가시키고 쉐어드풀 레치에 대한 경합을 증가시켜 성능저하를 유발 하며
4031에러를 발생시킨다.
==================================================================================================================