사용자 정의 함수는 사용 위치에 따라 실행 계획에 Rowsource 실행 통계가 표시될 수도 있고 표시되지 않을 수도 있습니다.
테스트를 위해 아래와 같이 t1 테이블과 f1 함수를 생성하겠습니다.
-- 1-1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1) AS SELECT ROWNUM FROM XMLTABLE ('1 to 10000');
-- 1-2
CREATE OR REPLACE FUNCTION f1 (i_v1 IN NUMBER)
RETURN NUMBER
IS
v1 NUMBER;
BEGIN
SELECT COUNT (*) INTO v1 FROM t1;
RETURN v1;
END;
/
아래 2-2번, 2-4번 쿼리는 실행 계획에 함수의 Rowsource 실행 통계가 표시되고, 2-1번, 2-3번 쿼리는 실행 계획에 함수의 Rowsource 실행 통계가 표시되지 않습니다.
-- 2-1
SELECT f1 (c1) FROM t1;
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10000 |00:00:00.01 | 118 |
| 1 | TABLE ACCESS FULL| T1 | 1 | 10000 |00:00:00.01 | 118 |
---------------------------------------------------------------------------
-- 2-2
SELECT f1 (c1) FROM t1 ORDER BY c1;
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10000 |00:00:00.64 | 180K|
| 1 | SORT ORDER BY | | 1 | 10000 |00:00:00.64 | 180K| -- !
| 2 | TABLE ACCESS FULL| T1 | 1 | 10000 |00:00:00.01 | 18 |
----------------------------------------------------------------------------
-- 2-3
SELECT (SELECT f1 (c1) FROM DUAL) FROM t1;
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10000 |00:00:00.01 | 119 |
| 1 | FAST DUAL | | 10000 | 10000 |00:00:00.01 | 0 |
| 2 | TABLE ACCESS FULL| T1 | 1 | 10000 |00:00:00.01 | 119 |
---------------------------------------------------------------------------
-- 2-4
SELECT * FROM (SELECT /*+ NO_MERGE */ f1 (c1) FROM t1);
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10000 |00:00:00.75 | 180K|
| 1 | VIEW | | 1 | 10000 |00:00:00.75 | 80K| -- !
| 2 | TABLE ACCESS FULL| T1 | 1 | 10000 |00:00:00.01 | 118 |
----------------------------------------------------------------------------
V$SQL 뷰를 조회하면 쿼리의 실행 통계가 저장된 것을 확인할 수 있습니다.
-- 3
SELECT sql_id, sql_text, executions, buffer_gets, elapsed_time
FROM v$sql
WHERE sql_id IN ('75s1qa8xtsag1', '04nfjzkadk516', '08mkc5mbwm7c7', '8xk2v37bp5ccu');
SQL_ID SQL_TEXT EXECUTIONS BUFFER_GETS ELAPSED_TIME
------------- ------------------------------------------------------ ---------- ----------- ------------
75s1qa8xtsag1 SELECT f1 (c1) FROM t1 1 181327 835807
04nfjzkadk516 SELECT f1 (c1) FROM t1 ORDER BY c1 1 180018 630512
08mkc5mbwm7c7 SELECT (SELECT f1 (c1) FROM DUAL) FROM t1 1 180119 768192
8xk2v37bp5ccu SELECT * FROM (SELECT /*+ NO_MERGE */ f1 (c1) FROM t1) 1 180118 751655
4 행이 선택되었습니다.