[오라클 커서공유]CURSOR_SHARING 파라미터

[오라클 커서공유]CURSOR_SHARING 파라미터

CURSOR_SHARING 파라미터

비슷한 조건 문을 가지는 문장에 대해 SQL문을 공유하기 위해서는 이전 강좌 처럼 바인드 변수를 이용 할 수 있습니다. 이전 강좌에서 상수 값을 다르게 주게 되는 경우 서로 다른 SQL로 인식을 하여 하드 파싱(처음 보는 SQL문으로 인식하여 일일이 파싱)을 한다고 하였다.

 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문장이 실행되었다고 가정 한다면

<!--[if !supportLists]-->1.     <!--[endif]-->Shared Pool 안에서 공백, 대소문자까지 완벽하게 동일한 SQL 문장이 있는지 검사한다. 만약 동일한 SQL문장이 있으면 파싱트리 및 실행계획을 공유해서 사용하고 4번으로, 없다면 다음 2번을 수행한다.

<!--[if !supportLists]-->2.     <!--[endif]-->Shared Pool에서 유사한 SQL문장을 찾는다. (예를들면 where절의 상수값만 다른 경우), 만약 유사한 SQL문장이 없다면 Hard Parsing을 수행하여 파싱트리, 실행계획을 만들고 4번으로, 이미 있다면 다음 3번을 수행한다.

<!--[if !supportLists]-->3.     <!--[endif]-->남은 과정은 실행 한 SQL문장(where deptno = 20)의 실행 계획이, 상수값만 다른 상태로 존재하는 SQL문장(where deptno = 10)의 실행계획을 공유할 수 있는지를 확인하는 것인데 만약 공유 불가능하다면 Hard Parsing을 적용하여 새로운 실행계획을 만들고 공유가능 하다면 실행계획을 공유한 다음 4번을 진행한다.

<!--[if !supportLists]-->4.     <!--[endif]-->SQL Area에 SQL문장 및 파싱트리, 실행계획등을 공유시킨다.

<!--[if !supportLists]-->5.     <!--[endif]-->실행 계획대로 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

파싱이 한번만 일어남을 알수 있다.