CURSOR_SHARING 파라미터
비슷한 조건 문을 가지는 문장에 대해 SQL문을 공유하기 위해서는 이전 강좌 처럼 바인드 변수를 이용 할 수 있습니다. 이전 강좌에서 상수 값을 다르게 주게 되는 경우 서로 다른 SQL로 인식을 하여 하드 파싱(처음 보는 SQL문으로 인식하여 일일이 파싱)을 한다고 하였다.
Oracle 8.1.6에서 소개된 Cursor_Sharing 변수는 각각의 문장들에 대해 bind 변수로 처리하지 않게 되더라도 내부적으로 바인드 변수로 처리하여 각각의 Cursor에 대해 공유가 가능 하도록 했다. 실제 이 기능은 Bind 변수를 쓰는 것 보다는 빠르지 않지만 Literal SQL문을 이용하는 것보다 20~30% 성능 향상이 있는 것으로 검증 되었으며 오라클12C에서 사용가능한 두 파라미터는 EXACT, FORCE 이다. (SIMILAR는 Deprecated됨)
Oracle 8.1.6에서 소개된 Cursor_Sharing 변수는 각각의 문장들에 대해 bind 변수로 처리하지 않게 되더라도 내부적으로 바인드 변수로 처리하여 각각의 Cursor에 대해 공유가 가능 하도록 했다. 실제 이 기능은 Bind 변수를 쓰는 것 보다는 빠르지 않지만 Literal SQL문을 이용하는 것보다 20~30% 성능 향상이 있는 것으로 검증 되었으며 오라클12C에서 사용가능한 두 파라미터는 EXACT, FORCE 이다. (SIMILAR는 Deprecated됨)
Oracle11g에서 Cursor_Sharing 파라미터의 기본값은 EXACT인데 기본값이 아닌 경우 SQL문장이 바인드 변수보다 리터럴 값을 사용 하였다면 시스템에서 생성한 바인드 변수(:SYS_B_0)로 리터럴 값을 자동 변형 한다.
Cursor_Sharing 값이 기본값(EXACT)이 아닌 경우 즉 FORCE, SIMILAR인 경우 아래와 같은 절차로 SQL구문을 파싱한다.
“select * from emp where deptno = 20” 이라는 SQL문장이 실행되었다고 가정 한다면
1. Shared Pool 안에서 공백, 대소문자까지 완벽하게 동일한 SQL 문장이 있는지 검사한다. 만약 동일한 SQL문장이 있으면 파싱트리 및 실행계획을 공유해서 사용하고 4번으로, 없다면 다음 2번을 수행한다.
2. Shared Pool에서 유사한 SQL문장을 찾는다. (예를들면 where절의 상수값만 다른 경우), 만약 유사한 SQL문장이 없다면 Hard Parsing을 수행하여 파싱트리, 실행계획을 만들고 4번으로, 이미 있다면 다음 3번을 수행한다.
3. 남은 과정은 실행 한 SQL문장(where deptno = 20)의 실행 계획이, 상수값만 다른 상태로 존재하는 SQL문장(where deptno = 10)의 실행계획을 공유할 수 있는지를 확인하는 것인데 만약 공유 불가능하다면 Hard Parsing을 적용하여 새로운 실행계획을 만들고 공유가능 하다면 실행계획을 공유한 다음 4번을 진행한다.
4. SQL Area에 SQL문장 및 파싱트리, 실행계획등을 공유시킨다.
5. 실행 계획대로 SQL문을 실행한다.
1. CURSOR_SHARING = EXACT
SQL문장이 모두 동일해야만 Soft Parsing이 가능하다.(공백, 대소문자, where절의 상수까지도 같아야 한다), 소프트 파싱이란? SQL구문을 파싱 하는 단계에서 이미 만들어져 있는 파싱 트리와 실행 계획 등을 재사용하는 것이다.
SQL>conn / as sysdba
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> alter session set cursor_sharing = exact;
세션이 변경되었습니다.
SQL> select dname from scott.dept where deptno = 10;
DNAME
--------------
ACCOUNTING
SQL> select dname from scott.dept where deptno = 40;
DNAME
--------------
OPERATIONS
SQL> select substr(sql_text,1,50) "SQL", count(*),
2 sum(executions) "총 실행 횟수"
3 from v$sqlarea
4 where sql_text like '%dept%'
5 group by substr(sql_text,1,50)
6 having count(*) > 0
7 order by 2;
SQL COUNT(*) 총 실행 횟수
---------- --------------------------------------------------
select dname from scott.dept where deptno = 10 1 1
select dname from scott.dept where deptno = 40 1 1
파싱을 2번한 것을 알 수 있다.
2. CURSOR_SHARING = SIMILAR
Oracle12C에서 deprecated 되었으며 FORCE를 대신해서 쓰면 된다.
SQL문은 동일 해야 하며 조건에 정의된 바인드 변수의 값이 다르더라도 하나의 SQL문으로 간주하여 Soft Parsing 한다. 이 값은 결국 다른 상수 값을 사용하더라도 하나의 SQL문으로 인식하므로, 3번(FORCE)과 같은 결과를 나타낸다.
SQL>conn / as sysdba
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> alter session set cursor_sharing = similar;
세션이 변경되었습니다.
SQL> select dname from scott.dept where deptno = 10;
DNAME
--------------
ACCOUNTING
SQL> select dname from scott.dept where deptno = 40;
DNAME
--------------
OPERATIONS
SQL> select substr(sql_text,1,50) "SQL", count(*),
2 sum(executions) "총 실행 횟수"
3 from v$sqlarea
4 where sql_text like '%dept%'
5 group by substr(sql_text,1,50)
6 having count(*) > 0
7 order by 2;
SQL COUNT(*) 총 실행 횟수
---------- --------------------------------------------------
select dname from scott.dept where deptno = :" 1 2
파싱이 한번만 일어남을 알수 있다…
3. CURSOR_SHARING = FORCE
WHERE 조건절에 정의된 상수가 다르더라도 Soft Parsiing 한다.
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> alter session set cursor_sharing = force;
세션이 변경되었습니다.
SQL> select dname from scott.dept where deptno = 10;
DNAME
--------------
ACCOUNTING
SQL> select dname from scott.dept where deptno = 40;
DNAME
--------------
OPERATIONS
SQL> conn / as sysdba
연결되었습니다.
SQL> select substr(sql_text,1,40) "SQL", count(*),
2 sum(executions) "총 실행 횟수"
3 from v$sqlarea
4 where sql_text like '%dept%'
5 group by substr(sql_text,1,40)
6 having count(*) > 0
7 order by 2;
SQL COUNT(*) 총 실행 횟수
---------- --------------------------------------------------
select dname from dept where deptno = :" 1 2
파싱이 한번만 일어남을 알수 있다.
댓글 없음:
댓글 쓰기