개요
Oracle 23ai(23.8)에 Dynamic Statistics for PL/SQL Functions 기능이 추가되었습니다. 이 기능은 PL/SQL 함수에 대한 동적 통계를 수집합니다. 이 글에서는 스칼라 타입을 반환하는 PL/SQL 함수를 스칼라 함수, 컬렉션 타입을 반환하는 PL/SQL 함수를 테이블 함수로 지칭합니다.
plsql_function_dynamic_stats 파라미터로 PL/SQL 함수에 대한 동적 통계 수집의 동작을 설정할 수 있습니다. 기본값은 PREFERENCE로 dynamic_stats 설정에 따라 동적 통계를 수집합니다.
-- 1-1
SELECT name, value, isses_modifiable, issys_modifiable
FROM v$parameter
WHERE name = 'plsql_function_dynamic_stats';
NAME VALUE ISSES_MODIFIABLE ISSYS_MODIFIABLE
---------------------------- ---------- ---------------- ----------------
plsql_function_dynamic_stats PREFERENCE TRUE FALSE
1 row selected.
-- 1-2
SELECT ordinal, value, isdefault
FROM v$parameter_valid_values
WHERE name = 'plsql_function_dynamic_stats';
ORDINAL VALUE ISDEFAULT
------- ---------- ---------
1 CHOOSE FALSE -- 옵티마이저가 동적 통계의 수집 여부를 선택 (현재는 스칼라 함수에 대해서만 동적 통계를 수집)
2 PREFERENCE TRUE -- dynamic_stats 설정에 따라 동적 통계를 수집 (기본값)
3 OFF FALSE -- 모든 함수에 대한 동적 통계의 수집를 비활성화
4 ON FALSE -- 모든 함수에 대한 동적 통계의 수집를 활성화
4 rows selected.
DBMS_STATS 패키지에 PL/SQL 함수에 대한 동적 통계 수집과 관련된 서브프로그램이 추가되었습니다. SET_GLOBAL_PLSQL_PREFS 프로시저로 전역 수준의 dynamic_stats, SET_PLSQL_PREFS 프로시저로 함수 수준의 dynamic_stats를 설정할 수 있습니다. 전역 수준의 dynamic_stats의 기본값은 CHOOSE로 옵티마이저가 동적 통계의 수집 여부를 선택합니다. 현재는 스칼라 함수에 대한 동적 통계만 수집합니다. PL/SQL 함수에 대한 동적 통계의 수집 여부는 plsql_function_dynamic_stats 파라미터, 함수 수준의 dynamic_stats, 전역 수준의 dynamic_stats의 우선 순위로 결정됩니다.
-- 2-1
DBMS_STATS.SET_GLOBAL_PLSQL_PREFS (
pname IN VARCHAR2 -- dynamic_stats
, pvalue IN VARCHAR2 -- ON, OFF, CHOOSE, NULL
);
-- 2-2
DBMS_STATS.RESET_GLOBAL_PLSQL_PREF_DEFAULTS
-- 2-3
DBMS_STATS.SET_PLSQL_PREFS (
ownname IN VARCHAR2
, package_name IN VARCHAR2
, function_name IN VARCHAR2
, pname IN VARCHAR2 -- dynamic_stats
, pvalue IN VARCHAR2 -- ON, OFF, CHOOSE, NULL
);
-- 2-4
DBMS_STATS.GET_PLSQL_PREFS (
pname IN VARCHAR2
, ownname IN VARCHAR2 DEFAULT NULL
, package_name IN VARCHAR2 DEFAULT NULL
, function_name IN VARCHAR2 DEFAULT NULL
)
RETURN VARCHAR2;
-- 2-5
DBMS_STATS.DELETE_PLSQL_PREFS (
ownname IN VARCHAR2
, package_name IN VARCHAR2
, function_name IN VARCHAR2
, pname IN VARCHAR2
);
-- 2-6
SELECT DBMS_STATS.GET_PLSQL_PREFS ('dynamic_stats') AS dynamic_stats;
DYNAMIC_STATS
-------------
CHOOSE
1 row selected.
스칼라 함수
스칼라 함수의 동작을 테스트하기 위해 아래와 같이 테이블과 함수를 생성하겠습니다. 오브젝트 통계가 존재하면 스칼라 함수에 대한 동적 통계가 수집되지 않으므로 CTAS 문에 NO_GATHER_OPTIMIZER_STATISTICS 힌트를 사용해야 합니다.
-- 3-1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1) AS
SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */
b.*
FROM XMLTABLE ('1 to 15' COLUMNS c1 NUMBER PATH '.') a
, XMLTABLE ('1 to 15' COLUMNS c1 NUMBER PATH '.') b
WHERE b.c1 >= a.c1;
-- 3-2
DROP FUNCTION f1;
CREATE OR REPLACE FUNCTION f1 (i_value IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN i_value;
END;
/
아래와 같이 optimizer_dynamic_sampling 파라미터를 2, _optim_peek_user_binds 파라미터를 TRUE로 설정하겠습니다.
-- 4
ALTER SESSION SET optimizer_dynamic_sampling = 2;
ALTER SESSION SET "_optim_peek_user_binds" = TRUE;
아래 예제는 스칼라 함수에 대한 동적 통계가 수집된 실행 계획을 보여줍니다. 예상 로우 수와 실제 로우 수가 동일한 것을 볼 수 있습니다.
-- 5-1
SELECT * FROM t1 WHERE c1 = f1 (15);
-----------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 15 | 4 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 15 | 15 | 4 |
-----------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
-- 5-2
VAR b1 NUMBER = 15
SELECT * FROM t1 WHERE c1 = f1 (:b1);
-----------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 15 | 4 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 15 | 15 | 4 |
-----------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 15
-- 5-3
SELECT * FROM t1 WHERE c1 = f1 (c1);
-----------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 120 | 5 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 120 | 120 | 5 |
-----------------------------------------------------------------------
plsql_function_dynamic_stats 파라미터를 OFF로 설정하거나 0 레벨의 DYNAMIC_SAMPLING 힌트를 사용하면 스칼라 함수에 대한 동적 통계가 수집되지 않습니다. 6-3번 예제는 서브 쿼리가 Execute Call에서 실행되므로 함수에 대한 동적 통계가 수집되지 않습니다.
-- 6-1
SELECT /*+ OPT_PARAM('PLSQL_FUNCTION_DYNAMIC_STATS' 'OFF') */ * FROM t1 WHERE c1 = f1 (15);
-----------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 15 | 4 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 3 | 15 | 4 |
-----------------------------------------------------------------------
-- 6-2
SELECT /*+ DYNAMIC_SAMPLING(0) */ * FROM t1 WHERE c1 = f1 (15);
-----------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 15 | 4 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 3 | 15 | 4 |
-----------------------------------------------------------------------
-- 6-3
SELECT * FROM t1 WHERE c1 = (SELECT f1 (15) FROM DUAL);
-----------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 15 | 4 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 1 | 15 | 4 |
| 2 | FAST DUAL | | 1 | 1 | 1 | 0 |
-----------------------------------------------------------------------
테스트를 위해 아래와 같이 t1 테이블의 오브젝트 통계를 수집하겠습니다.
-- 7
EXEC DBMS_STATS.GATHER_TABLE_STATS (NULL, 'T1', no_invalidate => FALSE);
아래 예제는 스칼라 함수에 대한 동적 통계가 수집되지 않은 실행 계획을 보여줍니다. E-Rows가 8(= 120 / 15)이므로 스칼라 함수에 대한 동적 통계가 수집되지 않는 것을 알 수 있습니다.
-- 8
SELECT * FROM t1 WHERE c1 = f1 (15);
-----------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 15 | 3 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 8 | 15 | 3 |
-----------------------------------------------------------------------
plsql_function_dynamic_stats 파라미터를 ON으로 설정하거나 2 레벨의 DYNAMIC_SAMPLING 힌트를 사용해도 스칼라 함수에 대한 동적 통계가 수집되지 않습니다.
-- 9-1
SELECT /*+ OPT_PARAM('PLSQL_FUNCTION_DYNAMIC_STATS' 'ON') */ * FROM t1 WHERE c1 = f1 (15);
-----------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 15 | 3 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 8 | 15 | 3 |
-----------------------------------------------------------------------
-- 9-2
SELECT /*+ DYNAMIC_SAMPLING(2) */ * FROM t1 WHERE c1 = f1 (15);
-----------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 15 | 3 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 8 | 15 | 3 |
-----------------------------------------------------------------------
11 레벨의 DYNAMIC_SAMPLING 힌트를 사용하면 스칼라 함수에 대한 동적 통계가 수집되는 것으로 보입니다.
-- 10
SELECT /*+ DYNAMIC_SAMPLING(11) */ * FROM t1 WHERE c1 = f1 (15);
-----------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 15 | 3 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 15 | 15 | 3 |
-----------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
테이블 함수
테이블 함수에 대한 동적 통계를 테스트하기 위해 아래와 같이 함수를 생성하겠습니다. f2 함수는 테이블 함수, f3 함수는 파이프라인 테이블 함수입니다.
-- 11-1
CREATE OR REPLACE TYPE tnt_number FORCE AS TABLE OF NUMBER;
/
-- 11-2
DROP FUNCTION f2;
CREATE OR REPLACE FUNCTION f2 (i_value IN NUMBER)
RETURN tnt_number
IS
v1 tnt_number := tnt_number ();
BEGIN
FOR i IN 1 .. i_value LOOP
v1.EXTEND;
v1(v1.LAST) := i;
END LOOP;
RETURN v1;
END;
/
-- 11-3
DROP FUNCTION f3;
CREATE OR REPLACE FUNCTION f3 (i_value IN NUMBER)
RETURN tnt_number PIPELINED
IS
BEGIN
FOR i IN 1 .. i_value LOOP
PIPE ROW (i);
END LOOP;
END;
/
테이블 함수에 대한 동적 통계를 수집하기 위해 함수 수준의 dynamic_stats를 ON으로 설정하겠습니다. *_PROCEDURES 뷰의 dynamic_sampling_on, dynamic_sampling_off, dynamic_sampling_choose 칼럼에서 dynamic_stats 설정을 확인할 수 있습니다.
-- 12-1
EXEC DBMS_STATS.SET_PLSQL_PREFS (NULL, NULL, 'F2', 'dynamic_stats', 'ON');
EXEC DBMS_STATS.SET_PLSQL_PREFS (NULL, NULL, 'F3', 'dynamic_stats', 'ON');
-- 12-2
SELECT object_name, dynamic_sampling_on, dynamic_sampling_off, dynamic_sampling_choose
FROM user_procedures
WHERE object_name IN ('F1', 'F2', 'F3');
OBJECT_NAME DYNAMIC_SAMPLING_ON DYNAMIC_SAMPLING_OFF DYNAMIC_SAMPLING_CHOOSE
----------- ------------------- -------------------- -----------------------
F1 NO NO NO
F2 YES NO NO
F3 YES NO NO
3 rows selected.
아래 예제는 테이블 함수에 대한 동적 통계가 수집된 실행 계획을 보여줍니다. E-Rows가 A-Rows와 동일한 것을 볼 수 있습니다.
-- 13-1
SELECT * FROM f2 (100);
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| F2 | 1 | 100 | 100 |
-----------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
-- 13-2
VAR b1 NUMBER = 100
SELECT * FROM f2 (:b1);
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| F2 | 1 | 100 | 100 |
-----------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 100
-- 13-3
SELECT * FROM f3 (100);
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| F3 | 1 | 100 | 100 |
-----------------------------------------------------------------------------
plsql_function_dynamic_stats 파라미터를 OFF로 설정하거나 0 레벨의 DYNAMIC_SAMPLING 힌트를 사용하면 테이블 함수에 대한 동적 통계가 수집되지 않습니다.
-- 14-1
SELECT /*+ OPT_PARAM('PLSQL_FUNCTION_DYNAMIC_STATS' 'OFF') */ * FROM f2 (100);
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| F2 | 1 | 8168 | 100 |
-----------------------------------------------------------------------------
-- 14-2
SELECT /*+ DYNAMIC_SAMPLING(0) */ * FROM f2 (100);
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| F2 | 1 | 8168 | 100 |
-----------------------------------------------------------------------------
테스트를 위해 f2, f3 함수에 대한 dynamic_stats 설정을 기본값으로 설정하겠습니다.
-- 15-1
EXEC DBMS_STATS.SET_PLSQL_PREFS (NULL, NULL, 'F2', 'dynamic_stats', NULL);
EXEC DBMS_STATS.SET_PLSQL_PREFS (NULL, NULL, 'F3', 'dynamic_stats', NULL);
-- 15-2
SELECT object_name, dynamic_sampling_on, dynamic_sampling_off, dynamic_sampling_choose
FROM user_procedures
WHERE object_name IN ('F1', 'F2', 'F3');
OBJECT_NAME DYNAMIC_SAMPLING_ON DYNAMIC_SAMPLING_OFF DYNAMIC_SAMPLING_CHOOSE
----------- ------------------- -------------------- -----------------------
F1 NO NO NO
F2 NO NO NO
F3 NO NO NO
3 rows selected.
아래 예제는 테이블 함수에 대한 동적 통계가 수집되지 않은 실행 계획을 보여줍니다. E-Rows가 8168(기본값)이므로 테이블 함수에 대한 동적 통계가 수집되지 않는 것을 알 수 있습니다.
-- 16
SELECT * FROM f2 (100);
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| F2 | 1 | 8168 | 100 |
-----------------------------------------------------------------------------
plsql_function_dynamic_stats 파라미터를 ON으로 설정하거나 2 레벨의 DYNAMIC_SAMPLING 힌트를 사용하면 함수에 대한 동적 통계가 수집됩니다. 참고로 23.8 이전 버전에서도 2 레벨의 DYNAMIC_SAMPLING 힌트를 사용하면 테이블 함수에 대한 동적 통계가 수집됩니다. (optimizer_dynamic_sampling 파라미터 설정이 아닌 DYNAMIC_SAMPLING 힌트를 사용해야 함)
-- 17-1
SELECT /*+ OPT_PARAM('PLSQL_FUNCTION_DYNAMIC_STATS' 'ON') */ * FROM f2 (100);
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| F2 | 1 | 100 | 100 |
-----------------------------------------------------------------------------
-- 17-2
SELECT /*+ DYNAMIC_SAMPLING(2) */ * FROM f2 (100);
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| F2 | 1 | 100 | 100 |
-----------------------------------------------------------------------------
아울러 23.8 이전 버전에서 6708183 Fix Control을 활성화해도 테이블 함수에 대한 동적 통계가 수집됩니다.
-- 18-1
SELECT bugno, value, sql_feature, description, optimizer_feature_enable
FROM v$system_fix_control
WHERE bugno = 6708183;
BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE
------- ----- ------------------------------ ----------------------------------------- ------------------------
6708183 0 QKSFM_DYNAMIC_SAMPLING_6708183 allow dynamic sampling on table functions
1 row selected.
-- 18-2
SELECT /*+ OPT_PARAM('_fix_control' '6708183:1') */ * FROM f2 (100);
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| F2 | 1 | 100 | 100 |
-----------------------------------------------------------------------------
관련 링크