SQL 안의 PL/SQL 호출 최적화: Context Switch·Recursive SQL·Set-Based 변환

핵심 요약

SQL 문장에서 PL/SQL 함수를 호출하면, 함수가 그대로 실행되는 경우 SQL Runtime이 행을 처리하는 도중 PL/SQL Runtime을 호출하고 반환값을 다시 SQL 처리에 사용합니다.

SQL Row Source 처리
→ PL/SQL 함수 진입
→ 계산 또는 함수 내부 SQL 수행
→ 반환값 전달
→ SQL Row Source 처리 계속

이 경계가 후보 행마다 반복되면 함수 본문이 짧아도 CPU와 호출 고정비가 누적됩니다. 함수 안에서 다시 SQL을 실행하면 별도 Cursor의 Parse·Execute·Fetch와 Logical I/O까지 반복될 수 있습니다.

행별 함수 평가
→ SQL↔PL/SQL Runtime 전환
→ 함수 내부 SQL 실행
→ 반복 Buffer Gets·Cursor Call

튜닝의 우선순위는 다음과 같습니다.

1. 결과 의미를 먼저 고정한다
2. JOIN·CASE·사전 집계·분석 함수로 행별 호출을 제거한다
3. Oracle 26ai에서는 SQL Transpiler 또는 SQL Macro 적용 가능성을 확인한다
4. 남은 함수 호출에 PRAGMA UDF를 검토한다
5. 반복 입력·변경 빈도·검색 패턴에 맞을 때만 RESULT_CACHE나 Function-Based Index를 사용한다
6. 실제 Plan·Trace·PLSQL_EXEC_TIME·내부 SQL 통계로 전후를 비교한다

PRAGMA UDF, DETERMINISTIC, RESULT_CACHE, Function-Based Index는 서로 다른 문제를 해결합니다. 어떤 기능도 잘못된 집합 처리, 과도한 후보 행, 함수 내부 반복 SQL을 자동으로 모두 제거하지 않습니다.

학습 목표

• SQL Runtime과 PL/SQL Runtime 사이 전환 비용을 설명한다.
• SELECT 목록·WHERE·JOIN 조건에서 함수 평가 규모를 판단한다.
• 최종 반환 행 수와 함수 호출 횟수가 같다고 단정할 수 없는 이유를 설명한다.
• 함수 내부 SQL의 반복 Execute·Buffer Gets를 Trace와 SQL 통계로 찾는다.
• 행별 함수를 JOIN·CASE·사전 집계·분석 함수로 변환한다.
• SQL에서 호출되는 PL/SQL 함수의 부작용 제한을 설명한다.
• Oracle 26ai SQL Transpiler와 SQL Macro의 역할·적용 범위를 설명한다.
• PRAGMA UDF, DETERMINISTIC, RESULT_CACHE의 의미를 구분한다.
• Function-Based Index가 해결하는 Access 비용과 남기는 실행·DML 비용을 구분한다.
• V$SQLSTATS, Actual Plan, SQL Trace, PL/SQL Hierarchical Profiler를 조합해 검증한다.

1. SQL 안의 PL/SQL 함수는 어디에서 평가되는가

사용자 정의 함수는 SELECT 목록, Predicate, Join 조건, 정렬·그룹 표현식 등에 나타날 수 있습니다.

SELECT customer_id,
       get_customer_grade(customer_id) AS grade
FROM   customer;

SELECT customer_id
FROM   customer
WHERE  get_customer_grade(customer_id) = 'VIP';

SELECT o.order_id,
       c.customer_id
FROM   orders o
JOIN   customer c
  ON   get_customer_group(o.customer_id) = c.group_code;

함수가 표시된 위치와 실제 평가 시점은 항상 같지 않습니다. Optimizer는 Predicate Pushdown, View Merging, Join Order 변경과 같은 Transformation을 수행할 수 있기 때문입니다.

최종 반환 행 100건
≠ 함수 호출이 반드시 100회

WHERE 함수는 Filter 이전 후보 100만 건에 평가될 수 있고, 반대로 표현식이 SQL로 변환되거나 Index에서 값을 얻으면 예상보다 적게 실행될 수 있습니다. 따라서 SQL Text만 보고 정확한 호출 횟수를 고정하지 않습니다.

호출 규모를 판단할 때 확인할 것

• 함수가 적용되는 Row Source의 Starts와 A-Rows
• Predicate 적용 전후의 Actual Row 수
• 함수 내부 SQL의 EXECUTIONS와 BUFFER_GETS
• Parent SQL의 전체 Fetch 여부
• 같은 입력 Key의 반복 정도와 NDV
• SQL Transpiler·Function-Based Index 사용 여부

A-Rows는 함수 평가 후보의 규모를 추정하는 근거이지, 그 자체가 항상 정확한 함수 호출 Counter는 아닙니다.

2. Runtime 전환 비용이 누적되는 구조

Oracle의 PL/SQL Engine은 Procedural Statement를 실행하고 SQL Statement는 SQL Engine으로 보냅니다. 반대로 SQL에서 PL/SQL 함수를 호출하면 SQL 처리 흐름이 PL/SQL Runtime을 호출해야 합니다.

SQL Runtime
→ Parameter 전달
→ PL/SQL Runtime 실행
→ Return Value 전달
→ SQL Runtime 복귀

한 번의 전환은 짧을 수 있지만 후보 행 수가 커지면 누적됩니다.

함수 호출 1회 비용이 작더라도
후보 1,000,000행 × 행별 호출
→ CPU·Elapsed Time이 크게 증가할 수 있음

고정된 마이크로초 값을 일반 공식처럼 사용하지 않습니다. 실제 비용은 함수 본문, Compile Mode, SQL Transpiler, CPU, Cache, 내부 SQL, 데이터 분포에 따라 달라집니다.

3. 함수 내부 SQL은 반복 Cursor 작업과 I/O를 만든다

다음 함수는 고객 한 명의 최근 주문일을 조회합니다.

CREATE OR REPLACE FUNCTION get_last_order_date (
    p_customer_id IN NUMBER
) RETURN DATE
IS
    l_order_date DATE;
BEGIN
    SELECT MAX(order_date)
    INTO   l_order_date
    FROM   orders
    WHERE  customer_id = p_customer_id;

    RETURN l_order_date;
END;
/

SELECT c.customer_id,
       get_last_order_date(c.customer_id) AS last_order_date
FROM   customer c;

고객 후보가 100만 건이면 함수 내부 SQL도 매우 많이 실행될 수 있습니다.

CUSTOMER Row Source
→ 고객별 Function Call
→ 고객별 ORDERS Index Probe 또는 Scan
→ 별도 SQL Cursor의 Execute·Fetch·Buffer Gets 반복

SQL Trace에서는 함수 내부 SQL을 Parent SQL과 별도 Statement·호출 깊이로 확인할 수 있습니다. TKPROF의 Recursive SQL이라는 용어는 Oracle이 상위 SQL을 처리하기 위해 추가로 실행한 SQL을 가리키므로, 모든 함수 내부 SQL을 단순히 같은 의미의 내부 Recursive SQL이라고 단정하지 않습니다. 중요한 것은 별도 SQL Text의 실행 횟수와 자원 사용량을 Parent 부하와 함께 합산하는 것입니다.

4. 첫 번째 해법: JOIN과 사전 집계

행별 최근 주문일 함수는 고객별 집계를 한 번 수행하고 JOIN할 수 있습니다.

SELECT c.customer_id,
       o.last_order_date
FROM   customer c
LEFT JOIN (
    SELECT customer_id,
           MAX(order_date) AS last_order_date
    FROM   orders
    GROUP BY customer_id
) o
  ON o.customer_id = c.customer_id;

변경 전
고객 N건 × ORDERS 반복 Probe

변경 후
ORDERS 집합 집계 1회
→ CUSTOMER와 JOIN

그러나 집합 변환이 항상 물리 작업량을 줄이는 것은 아닙니다. Outer 고객이 매우 적고 ORDERS(customer_id, order_date) Index가 선택적이면 소수의 Probe가 전체 ORDERS 집계보다 저렴할 수 있습니다.

두 방식을 비교하는 기준

• Outer 후보 행 수와 고객 Key NDV
• 함수 내부 SQL 1회당 Buffer Gets
• 전체 ORDERS 집계 입력량
• Index의 선두 Column·정렬 방향·Clustering 상태
• 첫 행 응답과 전체 처리량 중 어느 목표가 중요한지
• NULL, 중복, 주문이 없는 고객의 보존 여부

집합 SQL은 호출 제거뿐 아니라 결과 의미와 전체 작업량을 함께 검증합니다.

5. CASE·Lookup JOIN·분석 함수로 변환

5.1 단순 분기 함수는 CASE

SELECT student_id,
       CASE
         WHEN score >= 90 THEN 'A'
         WHEN score >= 80 THEN 'B'
         WHEN score >= 70 THEN 'C'
         ELSE 'D'
       END AS grade
FROM   student_score;

5.2 Code 조회 함수는 Lookup JOIN

SELECT o.order_id,
       s.status_name,
       s.status_group
FROM   orders o
LEFT JOIN order_status_code s
  ON s.status_code = o.status_code;

같은 Code Key로 이름·그룹·정렬순서를 각각 함수 호출하는 것보다 한 번 JOIN해 여러 값을 가져오는 편이 유리할 수 있습니다.

5.3 이전 값·누적값은 분석 함수

SELECT account_id,
       transaction_time,
       amount,
       SUM(amount) OVER (
           PARTITION BY account_id
           ORDER BY transaction_time, transaction_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_balance
FROM   account_transaction;

6. 집합 변환에서 반드시 보존할 의미

함수를 SQL로 바꿀 때 속도만 비교하면 잘못된 결과를 만들 수 있습니다.

확인 항목

1. NULL 의미: 주문이 없는 고객이 NULL로 남아야 하는가?
2. 중복 의미: Lookup Table의 Key가 실제로 유일한가?
3. 동점 의미: 최신 시각이 같은 행 중 어떤 행을 선택하는가?
4. 보안·Session Context: 함수가 VPD 또는 SYS_CONTEXT에 의존하는가?
5. 예외 처리: 함수가 NO_DATA_FOUND를 특정 기본값으로 바꾸는가?
6. Data Type·NLS: 암시적 변환이나 NLS 형식이 결과에 영향을 주는가?
7. 첫 행 응답: 전체 사전 집계가 소수 Probe보다 늦게 첫 행을 반환하지 않는가?

변환 전후 결과를 MINUS, Count, Hash Aggregate, 경계값 Test로 비교하고 성능은 전체 Fetch 조건에서 측정합니다.

7. SQL에서 호출되는 PL/SQL 함수의 부작용 제한

SQL Statement가 호출하는 Stored Function과 그 하위 Subprogram은 Side Effect를 제한받습니다.

• SELECT 또는 Parallel DML에서 호출된 함수는 Database Table을 변경할 수 없습니다.
• INSERT·UPDATE·DELETE·MERGE가 호출한 함수는 그 DML이 변경하는 Table을 Query하거나 변경할 수 없습니다. 위반하면 Mutating Table 오류가 발생할 수 있습니다.
• 일반 SQL에서 호출된 함수는 Transaction Control, Session Control, System Control, DDL을 실행할 수 없습니다. Autonomous Transaction 지정은 별도 예외지만, 조회 함수 안에서 부작용을 만들면 정합성·재시도·성능 위험이 커집니다.

따라서 SQL에서 사용하는 함수는 가능한 한 입력만으로 값을 계산하고 부작용이 없도록 설계합니다. 함수 내부 Commit이나 Logging DML로 호출 횟수를 세는 방식은 운영 코드의 의미와 성능을 바꿀 수 있으므로 Controlled Test 외에는 사용하지 않습니다.

8. Oracle 26ai SQL Transpiler

Oracle AI Database 26ai는 SQL 안에서 호출되는 PL/SQL 함수를 가능한 경우 의미적으로 같은 SQL 표현식으로 자동 변환하는 SQL Transpiler를 제공합니다.

변환 전
SQL Runtime → PL/SQL Runtime → SQL Runtime

변환 후
PL/SQL 함수 호출을 SQL 표현식으로 대체
→ SQL Runtime 안에서 평가

활성화

ALTER SESSION SET SQL_TRANSPILER = ON;

Oracle AI Database 26ai Reference에서 SQL_TRANSPILER의 기본값은 OFF입니다. 활성화된 경우에만 변환 가능한 함수를 자동으로 Transpile합니다.

변환 가능한 대표 요소

• SQL Scalar Type 기반 Parameter·Local Variable
• 단순 변수 대입과 Return
• SQL 표현식으로 변환 가능한 계산
• SQL CASE 표현식
• Package 안에 정의된 함수 중 지원되는 형태

변환되지 않는 대표 요소

• 함수 내부 Embedded SQL, Cursor, Dynamic SQL
• Package State 의존
• 다른 PL/SQL 함수 호출과 재귀 호출
• Loop, GOTO, RAISE 같은 Control Flow
• Autonomous Transaction과 Transaction 처리
• PL/SQL Collection·Record·Object 등 미지원 Type

변환이 불가능하면 기존 PL/SQL Runtime 호출로 Fallback합니다.

실제 변환 여부 확인

Actual 또는 Display Plan의 Predicate Information에서 함수명이 SQL 표현식으로 대체됐는지 확인합니다.

변환됨: TO_CHAR(...), CASE ... 같은 SQL 표현식 표시
변환 안 됨: GET_GRADE(column) 같은 함수 호출이 그대로 표시

Feature 활성화만 보고 전환 비용이 사라졌다고 단정하지 않습니다.

9. SQL Macro는 명시적 SQL 표현식 생성 대안

Scalar SQL Macro는 함수 호출 위치에 사용할 SQL 표현식 Text를 생성합니다. 실행 시 반환 Text가 SQL 표현식으로 확장되므로 일반 PL/SQL 함수의 행별 Runtime 호출을 피할 수 있습니다.

CREATE OR REPLACE FUNCTION grade_expr(p_score NUMBER)
RETURN VARCHAR2
SQL_MACRO(SCALAR)
IS
BEGIN
  RETURN q'{
    CASE
      WHEN p_score >= 90 THEN 'A'
      WHEN p_score >= 80 THEN 'B'
      ELSE 'C'
    END
  }';
END;
/

SQL Macro는 RESULT_CACHE, PARALLEL_ENABLE, PIPELINED와 함께 선언할 수 없고, Scalar Macro는 Table Argument를 받을 수 없습니다. Macro가 생성한 SQL의 의미·권한·Plan을 별도로 검증해야 합니다.

26ai에서는 단순 PL/SQL 함수가 SQL Transpiler로 자동 변환될 수 있으므로, SQL Macro와 일반 함수 중 유지보수성과 지원되는 문법을 비교합니다.

10. PRAGMA UDF의 역할과 한계

CREATE OR REPLACE FUNCTION grade_name (
    p_score IN NUMBER
) RETURN VARCHAR2
IS
    PRAGMA UDF;
BEGIN
    RETURN CASE
             WHEN p_score >= 90 THEN 'A'
             WHEN p_score >= 80 THEN 'B'
             ELSE 'C'
           END;
END;
/

PRAGMA UDF는 해당 PL/SQL Unit이 주로 SQL Statement에서 사용되는 User-Defined Function임을 Compiler에 알려 성능이 개선될 가능성을 제공합니다.

다음을 자동으로 해결하지 않습니다.

• 후보 행 수
• 함수 내부 SQL의 반복 I/O
• 비선택적인 Access Path
• 복잡한 계산 자체
• 동일 입력 결과 Cache
• Side Effect나 잘못된 결과 의미

26ai에서 SQL Transpiler가 함수 전체를 SQL로 변환하면 Runtime 전환 자체를 제거할 수 있지만, Transpile 불가 또는 비활성 상태에서는 PRAGMA UDF가 남은 함수 호출 비용을 줄이는 보조 수단이 될 수 있습니다. 적용 전후 PLSQL_EXEC_TIME, CPU, Elapsed Time을 같은 부하로 비교합니다.

11. DETERMINISTIC은 검증된 Cache가 아니라 개발자 계약이다

DETERMINISTIC은 같은 입력값이면 같은 결과를 반환하고 Side Effect가 없다는 속성을 개발자가 선언하는 것입니다.

CREATE OR REPLACE FUNCTION normalize_code(p_code VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC
IS
BEGIN
  RETURN UPPER(TRIM(p_code));
END;
/

반드시 기억할 점

• Oracle이 함수가 실제로 결정적인지 완전히 검증해 주는 기능이 아닙니다.
• 계약을 위반하면 실행 결과와 호출 효과가 Undefined 상태이며, 오류 없이 잘못된 결과가 나올 수 있습니다.
• 현재 시각, Sequence, Package Variable, Session별 Context, 변경 가능한 Table 값에 의존하는 함수에는 부적합합니다.
• User-Defined Function을 Function-Based Index, Virtual Column, Query Rewrite 대상 Materialized View에 사용할 때 필요합니다.
• 함수 정의를 변경하면 의존 Function-Based Index와 Materialized View를 수동으로 재구축해야 합니다.
• DETERMINISTIC은 입력별 결과를 Server Result Cache에 저장한다는 뜻이 아닙니다.

12. RESULT_CACHE는 입력별 결과 저장 기능이다

CREATE OR REPLACE FUNCTION get_config_value(p_name VARCHAR2)
RETURN VARCHAR2
RESULT_CACHE
IS
    l_value VARCHAR2(4000);
BEGIN
    SELECT config_value
    INTO   l_value
    FROM   app_config
    WHERE  config_name = p_name;

    RETURN l_value;
END;
/

Oracle은 Result-Cached Function 실행 중 조회한 Table·View Dependency를 추적하고, 관련 데이터의 변경이 Commit되면 Cache된 결과를 Invalid 처리합니다.

적합한 조건

• 같은 입력이 여러 실행·Session에서 반복됨
• 계산 또는 조회 비용이 큼
• 결과 크기가 작음
• 의존 데이터 변경이 드묾
• Session별 NLS·Time Zone·Application Context에 결과가 좌우되지 않음

주의점

• 입력값 종류가 매우 많으면 Cache Object 관리 비용이 커질 수 있습니다.
• 의존 Table 변경이 잦으면 Invalidation이 반복됩니다.
• 의존 Table에 Uncommitted DML을 수행 중인 Session은 자기 변경을 보기 위해 Cache를 우회할 수 있습니다.
• Cache Hit 여부와 함수 본문 실행 횟수를 애플리케이션이 전제로 삼아서는 안 됩니다.
• 26ai의 실행 Threshold 설정에 따라 처음부터 즉시 Cache되지 않을 수 있습니다.

먼저 행별 호출을 제거한 뒤, 남은 반복 계산에 한해 Result Cache를 검토합니다.

13. Function-Based Index는 표현식 Access Path다

CREATE INDEX customer_upper_name_ix
ON customer (UPPER(customer_name));

Function-Based Index는 함수 또는 표현식 값을 미리 계산해 Index Key로 저장합니다.

SELECT customer_id, customer_name
FROM   customer
WHERE  UPPER(customer_name) = :name;

Optimizer는 표현식 Predicate에 대해 해당 Index 사용을 검토할 수 있습니다.

해결할 수 있는 비용

• 표현식 Predicate 때문에 일반 Column Index를 직접 사용하기 어려운 문제
• 선택적인 표현식 조건의 Table Full Scan 또는 넓은 Scan

남는 비용

• INSERT·UPDATE 시 표현식 계산과 Index 유지
• Undo·Redo·Storage
• Index에 없는 Column의 Table Access
• SELECT 목록에 별도로 작성한 함수 호출
• 함수 변경 후 Index Invalid 상태·재활성화·재구축 관리

User-Defined Function 기반 Index는 함수가 DETERMINISTIC으로 선언되어야 합니다. 그러나 선언만으로 함수의 정확성이 보장되는 것은 아니므로 실제 결정성·권한·NLS·Session 의존성을 설계자가 검증해야 합니다.

14. 실행계획·통계·Trace로 비용을 확인한다

14.1 V$SQLSTATS

SELECT sql_id,
       plan_hash_value,
       executions,
       rows_processed,
       cpu_time,
       elapsed_time,
       plsql_exec_time,
       buffer_gets,
       disk_reads,
       parse_calls,
       SUBSTR(sql_text, 1, 120) AS sql_text
FROM   v$sqlstats
WHERE  plsql_exec_time > 0
ORDER BY plsql_exec_time DESC
FETCH FIRST 30 ROWS ONLY;

PLSQL_EXEC_TIME은 해당 SQL에 귀속된 PL/SQL 실행시간의 누적값이며 단위는 Microsecond입니다. 이것만으로 함수 호출 횟수를 역산하지 않습니다. 함수별 정확한 호출 수가 필요하면 Controlled Test의 Instrumentation, 함수 내부 SQL의 Executions, SQL Trace, Profiler를 함께 사용합니다.

14.2 Actual Plan

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id  => :sql_id,
    format  => 'ALLSTATS LAST +PREDICATE +ALIAS'
  )
);

확인 항목:

• 함수 Predicate가 적용되는 Operation
• Starts, A-Rows, Buffers, A-Time
• SQL Transpiler에 의해 함수명이 SQL 표현식으로 바뀌었는지
• Function-Based Index가 실제 Access Path에 사용됐는지

14.3 SQL Trace와 TKPROF

• Parent SQL의 Parse·Execute·Fetch·Rows
• 함수 내부 SQL의 별도 Executions·Rows·Disk·Query·Current
• 호출 깊이와 Recursive Call을 포함한 총 Resource
• 전체 Fetch 여부와 Client Fetch 패턴

Trace 자체의 Overhead가 있으므로 대상 Module·Action·Session을 제한해 수집합니다.

14.4 PL/SQL Hierarchical Profiler

DBMS_HPROF는 Subprogram Invocation별로 PL/SQL과 SQL 실행시간을 구분해 함수 내부의 실제 병목을 찾는 데 유용합니다. Parent SQL 통계와 결합해 Runtime 전환, 함수 자체 계산, 함수 내부 SQL 중 어느 부분이 지배적인지 판단합니다.

15. 적용 순서

1. 결과 의미 고정
   NULL·중복·동점·NLS·Context·예외 처리 확인

2. 호출 위치와 규모 확인
   Plan의 A-Rows·Starts, 전체 Fetch, 내부 SQL Executions 측정

3. 집합 SQL 변환
   CASE·JOIN·GROUP BY·Analytic·EXISTS 적용

4. 물리 작업량 비교
   반복 Index Probe와 전체 집계의 Buffer Gets·첫 행·전체 처리량 비교

5. 26ai Runtime 전환 제거 검토
   SQL_TRANSPILER 활성·Eligibility·Plan 확인
   필요하면 Scalar SQL Macro 검토

6. 남은 PL/SQL 호출 최적화
   PRAGMA UDF 적용 전후 PLSQL_EXEC_TIME·CPU 비교

7. 목적별 보조 기능 적용
   반복 입력·저변경: RESULT_CACHE
   표현식 검색 Access: Function-Based Index
   결정성 계약: DETERMINISTIC

8. 회귀 검증
   결과 일치, DML 비용, Invalidation, Concurrency, Plan 안정성 확인

혼동하기 쉬운 판단