Pipelined Functions & Performance

This topic covers Oracle's highest-leverage PL/SQL performance features: pipelined table functions, result caching, NOCOPY, native compilation, and profiling.

Pipelined Table Functions

A regular table function computes the entire result set, stores it in memory, then returns it. A pipelined function produces rows incrementally with PIPE ROW — the caller starts receiving rows immediately, and memory usage stays flat.

Regular Table Function (Materializes Everything)

CREATE TYPE t_emp_row AS OBJECT (
    employee_id  NUMBER,
    full_name    VARCHAR2(100),
    salary       NUMBER,
    dept_name    VARCHAR2(100)
);
/

CREATE TYPE t_emp_tab AS TABLE OF t_emp_row;
/

-- Regular table function — all rows built in memory before returning
CREATE OR REPLACE FUNCTION get_dept_employees_bulk(p_dept_id IN NUMBER)
RETURN t_emp_tab IS
    v_result  t_emp_tab := t_emp_tab();
BEGIN
    SELECT t_emp_row(e.employee_id,
                     e.first_name || ' ' || e.last_name,
                     e.salary,
                     d.department_name)
    BULK COLLECT INTO v_result
    FROM   employees   e
    JOIN   departments d ON d.department_id = e.department_id
    WHERE  e.department_id = p_dept_id;

    RETURN v_result;  -- return entire collection at once
END get_dept_employees_bulk;
/

Pipelined Table Function (Streams Rows)

CREATE OR REPLACE FUNCTION get_dept_employees_piped(p_dept_id IN NUMBER)
RETURN t_emp_tab PIPELINED IS    -- PIPELINED keyword
BEGIN
    FOR r IN (SELECT e.employee_id,
                     e.first_name || ' ' || e.last_name AS full_name,
                     e.salary,
                     d.department_name
              FROM   employees   e
              JOIN   departments d ON d.department_id = e.department_id
              WHERE  e.department_id = p_dept_id
              ORDER BY e.salary DESC)
    LOOP
        -- PIPE ROW sends one row immediately to the caller
        PIPE ROW(t_emp_row(r.employee_id, r.full_name, r.salary, r.department_name));
    END LOOP;

    RETURN;  -- RETURN with no value signals end of rows
END get_dept_employees_piped;
/

-- Use in SQL — exactly like a table
SELECT *
FROM   TABLE(get_dept_employees_piped(80))
WHERE  salary > 12000
ORDER BY salary DESC;

-- JOIN with other tables
SELECT p.full_name, p.salary, j.job_title
FROM   TABLE(get_dept_employees_piped(80)) p
JOIN   employees e ON e.employee_id = p.employee_id
JOIN   jobs      j ON j.job_id      = e.job_id;

Why Pipelined Functions Are Faster

RESULT_CACHE

RESULT_CACHE stores function results in the SGA. Subsequent calls with the same arguments return the cached value without re-executing:

CREATE OR REPLACE FUNCTION dept_salary_total(p_dept_id IN NUMBER)
RETURN NUMBER
RESULT_CACHE RELIES_ON (employees)   -- auto-invalidate if employees changes
IS
    v_total  NUMBER;
BEGIN
    SELECT SUM(salary) INTO v_total
    FROM   employees
    WHERE  department_id = p_dept_id;

    RETURN NVL(v_total, 0);
END dept_salary_total;
/

-- First call: executes the query
SELECT dept_salary_total(80) FROM dual;

-- Second call (same session or different session): returns cached value
SELECT dept_salary_total(80) FROM dual;

-- After any DML on EMPLOYEES, the cache is automatically invalidated
UPDATE employees SET salary = salary + 1 WHERE employee_id = 100;

Monitor cache hits:

SELECT name, cache_hits, cache_misses, invalidations
FROM   v$result_cache_objects
WHERE  name LIKE '%DEPT_SALARY_TOTAL%';

DETERMINISTIC Functions

Mark a function DETERMINISTIC when the same inputs always produce the same output. This enables:

1. Function-based indexes
2. Query optimization (call once per unique argument, not once per row)

CREATE OR REPLACE FUNCTION tax_rate(p_country IN VARCHAR2)
RETURN NUMBER DETERMINISTIC IS
BEGIN
    RETURN CASE p_country
               WHEN 'US'  THEN 0.21
               WHEN 'UK'  THEN 0.19
               WHEN 'DE'  THEN 0.15
               ELSE             0.25
           END;
END tax_rate;
/

-- Function-based index using a deterministic function
CREATE INDEX idx_emp_tax ON employees (tax_rate(country_code));

NOCOPY Hint for OUT/IN OUT Collections

When you pass a large collection as an OUT or IN OUT parameter, Oracle copies the entire collection. NOCOPY passes by reference instead:

CREATE OR REPLACE PROCEDURE process_salaries(
    p_salaries IN OUT NOCOPY t_salary_list,   -- pass by reference, no copy
    p_raise_pct IN NUMBER
) IS
BEGIN
    FOR i IN 1..p_salaries.COUNT LOOP
        p_salaries(i) := ROUND(p_salaries(i) * (1 + p_raise_pct / 100), 2);
    END LOOP;
END process_salaries;
/

Native Compilation (PLSQL_CODE_TYPE = NATIVE)

By default, PL/SQL compiles to interpreted bytecode. Native compilation produces machine code — typically 10–30% faster for compute-intensive code:

-- Enable native compilation for one subprogram
ALTER PROCEDURE process_salaries COMPILE PLSQL_CODE_TYPE=NATIVE;

-- Check compilation type
SELECT object_name, plsql_code_type
FROM   user_plsql_object_settings
WHERE  object_name = 'PROCESS_SALARIES';

Enable schema-wide (requires DBA):

-- In init.ora or spfile (session-level for testing)
ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE;

Optimization Level

-- Level 2 (default): includes advanced optimizations
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;

-- Level 3: includes inlining of small procedures/functions — can give 10–20% boost
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;

-- Verify
SELECT plsql_optimize_level FROM user_plsql_object_settings
WHERE object_name = 'MY_PROC';

Profiling with DBMS_HPROF

Hierarchical profiler — shows which functions consume the most time:

-- Setup (once)
-- @$ORACLE_HOME/rdbms/admin/dbmshprf.sql
-- DBMS_HPROF.CREATE_TABLES;

-- Start profiling
DBMS_HPROF.START_PROFILING(
    location  => 'PROFILE_DIR',  -- Oracle directory object
    filename  => 'my_run.trc'
);

-- Run the code you want to profile
BEGIN
    -- ... your PL/SQL code ...
    NULL;
END;
/

-- Stop profiling
DBMS_HPROF.STOP_PROFILING;

-- Analyze and load results
DECLARE
    v_run_id NUMBER;
BEGIN
    v_run_id := DBMS_HPROF.ANALYZE(
        location => 'PROFILE_DIR',
        filename => 'my_run.trc'
    );
    DBMS_OUTPUT.PUT_LINE('Run ID: ' || v_run_id);
END;
/

-- Query results
SELECT runnumber, owner, module, function, subtree_elapsed_time, calls
FROM   dbmshp_function_info
WHERE  runnumber = 1
ORDER BY subtree_elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

Performance Checklist

Summary

• Pipelined functions stream rows with PIPE ROW — constant memory, immediate delivery, SQL filter push-down.
• RESULT_CACHE stores results in the SGA; auto-invalidated when dependent tables change.
• DETERMINISTIC enables function-based indexes and query optimization.
• NOCOPY passes large collections by reference — significant speedup for large IN OUT parameters.
• Native compilation (PLSQL_CODE_TYPE=NATIVE) produces machine code — best for compute-heavy code.
• DBMS_HPROF profiles PL/SQL call hierarchies to identify hotspots.
• Start with BULK COLLECT / FORALL — that's where the biggest gains always are.