CURSOR_SHARING_FORCE 힌트

Oracle 23ai(23.4)에 CURSOR_SHARING_FORCE 힌트가 추가되었습니다. CURSOR_SHARING_FORCE 힌트를 사용하면 구문 레벨에서 Cursor Sharing 기능을 Force로 설정할 수 있습니다.

-- 1
SELECT name, inverse, target_level, version
  FROM v$sql_hint
 WHERE name IN ('CURSOR_SHARING_EXACT', 'CURSOR_SHARING_FORCE');

NAME                 INVERSE              TARGET_LEVEL VERSION
-------------------- -------------------- ------------ -------
CURSOR_SHARING_EXACT CURSOR_SHARING_FORCE            1 9.0.0
CURSOR_SHARING_FORCE CURSOR_SHARING_EXACT            1 23.1.0

2 rows selected.

 

Cursor Sharing 기능은 cursor_sharing 파라미터로 설정하며 기본값은 EXACT입니다.

-- 2-1
SELECT name, value, isses_modifiable, description
  FROM v$parameter
 WHERE name = 'cursor_sharing';

NAME           VALUE DEFAULT_VALUE ISSES_MODIFIABLE DESCRIPTION
-------------- ----- ------------- ---------------- -------------------
cursor_sharing EXACT EXACT         TRUE             cursor sharing mode

1 row selected.

-- 2-2
SELECT name, ordinal, value, isdefault
  FROM v$parameter_valid_values
 WHERE name = 'cursor_sharing';

NAME           ORDINAL VALUE   ISDEFAULT
-------------- ------- ------- ---------
cursor_sharing       1 FORCE   FALSE
cursor_sharing       2 EXACT   TRUE
cursor_sharing       3 SIMILAR FALSE

3 rows selected.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 3
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER);

 

아래와 같이 CURSOR_SHARING_FORCE 힌트를 사용하여 리터럴 값이 다른 2개의 쿼리를 수행하겠습니다.

-- 4-1
SELECT /*+ CURSOR_SHARING_FORCE */ * FROM t1 WHERE c1 = 1;

-- 4-2
SELECT /*+ CURSOR_SHARING_FORCE */ * FROM t1 WHERE c1 = 2;

 

V$SQL 뷰를 조회하면 앞서 수행한 쿼리의 커서가 공유된 것을 확인할 수 있습니다.

-- 5
SELECT sql_text, loads, executions
  FROM v$sql
 WHERE sql_text LIKE 'SELECT /*+ CURSOR_SHARING_FORCE */%';

SQL_TEXT                                                           LOADS EXECUTIONS
------------------------------------------------------------------ ----- ----------
SELECT /*+ CURSOR_SHARING_FORCE */ * FROM t1 WHERE c1 = :"SYS_B_0"     1          2

1 row selected.