오라클 19c cursor_sharing 테스트

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0

 

방법 : 오라클 19c cursor_sharing 테스트

cursor_sharing 파라미터를 테스트 해봄

cursor_sharing은 일시적으로 바인드변수에 대한 하드파싱 문제를 해결할 수 있지만 완전한 해결책은 아님
force 이면 컬럼 히스토그램을 사용하지 못하므로 고정된 실행계획을 사용한다고함

 

cursor_sharing 파라미터 옵션 설명
exact(기본값) : 확히 일치하는 텍스트가 있는 명령문만 동일한 커서를 공유함
force : 기존 커서를 공유하거나 커서 계획이 최적이 아닌 경우 새 커서를 만들 수 있음
similar : Oracle 11gR2 이후부터 더 이상 사용되지 않는 값

 

 

리터럴 쿼리 실행

SQL>
select * from emp where ename = 'LITERAL1';
select * from emp where ename = 'LITERAL2';

 

 

shared pool에서 리터럴 쿼리 확인

SQL> 
col sql_text for a60
select sql_text,
       executions
from   v$sqlarea
where  instr(sql_text, 'select * from emp where ename') > 0
and    instr(sql_text, 'sql_text') = 0
order by sql_text;
SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
select * from emp where ename = 'LITERAL1'                            1
select * from emp where ename = 'LITERAL2'                            1

 

 

세션레벨로 cursor_sharing 파라미터 force로 변경

SQL> alter session set cursor_sharing=force;
 
Session altered.

 

 

새로운 값으로 리터럴 쿼리 실행

SQL>
select * from emp where ename = 'LITERAL3';
select * from emp where ename = 'LITERAL4';

 

 

shared pool에서 리터럴 쿼리 재확인

SQL>
col sql_text for a60
select sql_text,
       executions
from   v$sqlarea
where  instr(sql_text, 'select * from emp where ename') > 0
and    instr(sql_text, 'sql_text') = 0
order by sql_text;
 
SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
select * from emp where ename = 'LITERAL1'                            1
select * from emp where ename = 'LITERAL2'                            1
select * from emp where ename = :"SYS_B_0"                            2

리터럴 쿼리가 :"SYS_B_0"으로 자동으로 바인드 변수처럼 변함

 

 

참조 : https://oracle-base.com/articles/9i/cursor_sharing
http://bysql.net/w201002/5943
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/CURSOR_SHARING.html