Oracle PL/SQL 内存优化详解
Oracle PL/SQL 内存优化详解
适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07
1. 概述
PL/SQL 内存优化技巧[1]：
详细见：Oracle PL/SQL 性能优化详解、Oracle PL/SQL 集合性能优化详解。
2. PGA 管理
2.1 自动
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 4G;
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 8G;2.2 监控
SELECT name, value, unit FROM v$pgastat;
-- 超过限制
SELECT sql_id, operation, actual_mem_used, max_mem_used
FROM v$sql_memory_workarea
WHERE actual_mem_used > 100*1024*1024;详细见：Oracle PGA 调优。
3. 集合内存
3.1 LIMIT
DECLARE
CURSOR c IS SELECT * FROM employees;
TYPE emp_tab IS TABLE OF c%ROWTYPE;
v_emp emp_tab;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO v_emp LIMIT 1000;
EXIT WHEN v_emp.COUNT = 0;
-- 处理
-- 释放
v_emp.DELETE;
END LOOP;
CLOSE c;
END;
/详细见：Oracle BULK COLLECT 与 FORALL 详解。
3.2 选择类型
- Associative Array：内存最省
- Nested Table：中
- VARRAY：固定3.3 TRIM / DELETE
-- 释放
v_emp.TRIM(v_emp.COUNT);
v_emp.DELETE;4. SERIALLY_REUSABLE
4.1 基本
CREATE OR REPLACE PACKAGE temp_pkg AS
PRAGMA SERIALLY_REUSABLE;
v_data CLOB;
PROCEDURE process(p_data CLOB);
END;
/
CREATE OR REPLACE PACKAGE BODY temp_pkg AS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE process(p_data CLOB) IS
BEGIN
v_data := p_data;
-- 处理
END;
END;
/4.2 优势
- 包状态工作区在 SGA
- 调用后释放
- 内存优化
- 大数据4.3 限制
- 状态不保留
- 不适合缓存5. 字符串
5.1 VARCHAR2
-- 32767 字节（PL/SQL）
-- 4000 字节（SQL 12c-）
-- 32767 字节（SQL 12c+ MAX_STRING_SIZE=EXTENDED）5.2 CLOB
-- 大字符串
DECLARE
v_text CLOB;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_text, TRUE);
DBMS_LOB.WRITEAPPEND(v_text, 100, '...');
-- 处理
DBMS_LOB.FREETEMPORARY(v_text);
END;
/5.3 释放
DBMS_LOB.FREETEMPORARY(v_lob);6. 缓存
6.1 RESULT_CACHE
CREATE OR REPLACE FUNCTION get_dept_name(p_id NUMBER) RETURN VARCHAR2
RESULT_CACHE RELIES_ON (departments)
IS
v_name VARCHAR2(100);
BEGIN
SELECT name INTO v_name FROM departments WHERE id = p_id;
RETURN v_name;
END;
/6.2 包级缓存
CREATE OR REPLACE PACKAGE cache_pkg AS
TYPE emp_cache IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
v_cache emp_cache;
END;
/6.3 选择
- 函数结果：RESULT_CACHE
- 会话级：包级缓存
- 共享：RESULT_CACHE详细见：Oracle PL/SQL 性能优化详解。
7. Native Compilation
7.1 启用
ALTER SYSTEM SET PLSQL_CODE_TYPE = NATIVE;
-- 或会话
ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE;7.2 重新编译
ALTER PACKAGE emp_pkg COMPILE;
-- 或
EXEC DBMS_RECOMPILE.RECOMP_SERIAL('SCOTT');7.3 优势
- CPU 密集
- 计算
- 循环
- 性能提升详细见：Oracle PL/SQL 性能优化详解。
8. INLINE
8.1 基本
CREATE OR REPLACE PROCEDURE caller IS
PRAGMA INLINE(small_proc, 'YES');
BEGIN
small_proc;
END;
/8.2 自动
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;
-- 自动 INLINE8.3 优势
- 小函数
- 减少调用开销
- 性能9. 优化级别
9.1 PLSQL_OPTIMIZE_LEVEL
0：无优化
1：基本
2：默认（推荐）
3：激进（INLINE）ALTER SYSTEM SET PLSQL_OPTIMIZE_LEVEL = 2;10. 共享池
10.1 代码共享
- 编译代码在共享池
- 多会话共享
- 减少内存10.2 监控
SELECT namespace, gets, gethits, pins, pinhits
FROM v$librarycache
WHERE namespace IN ('SQL AREA', 'PL/SQL AREA');
-- 失效对象
SELECT * FROM v$db_object_cache WHERE type LIKE 'PACKAGE%' AND kept = 'NO';10.3 DBMS_SHARED_POOL
EXEC DBMS_SHARED_POOL.KEEP('EMP_PKG', 'P');
-- 钉在共享池详细见：Oracle SGA 调优。
11. 临时 LOB
11.1 临时
DECLARE
v_lob CLOB;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_lob, TRUE, DBMS_LOB.CALL);
-- 处理
DBMS_LOB.FREETEMPORARY(v_lob);
END;
/11.2 监控
SELECT * FROM v$temporary_lobs;11.3 释放
-- 必须显式释放
DBMS_LOB.FREETEMPORARY(v_lob);12. 内存泄漏
12.1 原因
- 集合未释放
- 临时 LOB 未释放
- 包状态累积12.2 检测
SELECT sid, serial#, program, pga_used_mem, pga_alloc_mem
FROM v$session
ORDER BY pga_alloc_mem DESC;12.3 修复
-- 重置包状态
EXEC DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.REINITIALIZE);
-- 释放
EXEC DBMS_SESSION.FREE_UNUSED_USER_MEMORY;13. 应用场景
13.1 批量处理
- BULK COLLECT
- LIMIT
- 释放13.2 缓存
- RESULT_CACHE
- 包级缓存
- 频繁查询13.3 大数据
- SERIALLY_REUSABLE
- 临时 LOB
- 流式处理13.4 计算密集
- Native
- INLINE
- 优化级别14. 常见坑与排错
14.1 OOM
- 大集合
- LIMIT
- 释放14.2 泄漏
- LOB 未释放
- 集合未清空14.3 共享池
- pin
- 失效15. 最佳实践
- PGA 自动：管理
- LIMIT：批量
- 释放：完成
- SERIALLY_REUSABLE：临时
- RESULT_CACHE：函数
- Native：计算
- INLINE：小函数
- 优化级别：2+
- 监控：PGA
- 测试：内存
16. 参考资料
[1] Oracle Database PL/SQL Language Reference 19c, “Tuning PL/SQL Performance” https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-optimization-and-tuning.html