如何确保PL/SQL程序（含计算密集型存储过程）以优化级别2及以上编译？

问题1：如何确保PL/SQL程序在编译时已开启优化功能？ #

PL/SQL的编译优化由PLSQL_OPTIMIZE_LEVEL参数控制，取值范围是0-3（Oracle 12c及以后支持3级），其中级别2及以上就属于启用了优化（默认值就是2）。要确认你的程序是否已开启优化，有两个核心方法：

• 检查已编译对象的实际优化级别：直接查询数据字典视图，就能看到每个PL/SQL对象编译时使用的级别：

  SELECT name, type, optimize_level
  FROM user_plsql_object_settings
  WHERE name = 'YOUR_PROCEDURE_NAME' -- 替换成你的程序名
    AND type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE'); -- 根据对象类型调整
  

  如果返回的optimize_level是2或3，说明这个对象编译时已经开启了优化。

• 验证当前环境的默认优化级别：如果想确认会话或系统的默认设置，用这两个查询：

  -- 查看系统级默认配置
  SELECT value
  FROM v$parameter
  WHERE name = 'plsql_optimize_level';
  
  -- 查看当前会话的有效配置
  SELECT value
  FROM v$parameter_session
  WHERE name = 'plsql_optimize_level';
  

  只要参数值≥2，那么默认编译的所有PL/SQL程序都会自动开启优化。

问题2：如何确保计算密集型存储过程始终以2级及以上优化级别编译？ #

虽然默认是2级，但架不住有人误改会话或系统参数，导致计算密集型存储过程编译时用了更低级别（性能会大幅下降）。要彻底解决这个问题，推荐这几个靠谱方案：

• 显式在创建/替换时指定优化级别（最推荐）：不管当前环境参数是什么，直接在存储过程的定义里强制指定优化级别，这是最稳妥的办法：

  CREATE OR REPLACE PROCEDURE cpu_hungry_proc
  AUTHID DEFINER -- 根据你的权限需求选DEFINER或CURRENT_USER
  PLSQL_OPTIMIZE_LEVEL = 2 -- 12c+可以用3级，优化更激进
  IS
    -- 变量声明
    v_total NUMBER := 0;
  BEGIN
    -- 你的计算密集型逻辑
    FOR i IN 1..1000000 LOOP
      v_total := v_total + i;
    END LOOP;
  END cpu_hungry_proc;
  /
  

  每次编译这个存储过程时，都会严格使用你指定的级别，完全不受环境参数影响。

• 自动化检查+重新编译脚本：可以写个脚本定期扫描关键对象，发现优化级别不够就自动重新编译。比如这个PL/SQL块：

  DECLARE
    CURSOR c_suboptimal_objs IS
      SELECT name, type
      FROM user_plsql_object_settings
      WHERE optimize_level < 2
        AND name = 'CPU_HUNGRY_PROC' -- 或者批量筛选多个对象
        AND type = 'PROCEDURE';
    v_compile_sql VARCHAR2(300);
  BEGIN
    FOR rec IN c_suboptimal_objs LOOP
      v_compile_sql := 'ALTER ' || rec.type || ' ' || rec.name || ' COMPILE PLSQL_OPTIMIZE_LEVEL = 2';
      EXECUTE IMMEDIATE v_compile_sql;
      DBMS_OUTPUT.PUT_LINE('Recompiled ' || rec.type || ' ' || rec.name || ' with optimize level 2');
    END LOOP;
  END;
  /
  

  把这个脚本做成定时任务（用DBMS_SCHEDULER），就能自动维护关键对象的优化级别。

• 锁定系统级参数（谨慎使用）：如果你的数据库环境不允许任何人修改优化级别参数，可以用DBA权限把PLSQL_OPTIMIZE_LEVEL设为2并锁定：

  -- 永久生效（重启后保持）
  ALTER SYSTEM SET plsql_optimize_level = 2 SCOPE=SPFILE;
  -- 立即生效
  ALTER SYSTEM SET plsql_optimize_level = 2;
  -- 锁定参数，防止被修改（部分Oracle版本支持）
  ALTER SYSTEM SET plsql_optimize_level = 2 SCOPE=SPFILE LOCK;
  

  注意这个操作会影响整个数据库的所有PL/SQL编译，所以一定要确认所有应用都兼容这个设置后再操作。

内容的提问来源于stack exchange，提问作者Steven Feuerstein