cursor_sharing 파라메터를 변경으로 바인드변수 처럼 경합 줄이기

Cursor_Sharing

바인드 변수 사용 원칙을 지키지 않아 라이브러리 캐시 경합이 일어날 경우 경합을 일시적으로 해결하기 위하여 cursor_sharing 파라메터를 변경할 수 있다.

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

 

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

 

** 다량의 데이터 insert / update / delete 작업시 활용하면  커서공유에 의하여 파싱 및 경합 (Hard Parsing을 수행하여 파싱트리, 실행계획 수립작업 pass 효과)

 아래는 update 문 304회 실행한 결과에 대한 v$SQL 확인 내용.