如何获取PL/SQL包中各存储过程与函数的执行耗时

下面是几种实用的方法，可根据你的需求选择：

1. 手动添加计时逻辑（简单直接） #

直接在每个存储过程/函数的首尾加入时间记录代码，计算执行耗时。适合小范围测试，无需额外工具。

示例代码：

CREATE OR REPLACE PACKAGE BODY my_package IS
    PROCEDURE my_proc IS
        l_start NUMBER;
        l_elapsed NUMBER;
    BEGIN
        -- 记录开始时间（DBMS_UTILITY.GET_TIME返回百分之一秒为单位的数值）
        l_start := DBMS_UTILITY.GET_TIME;
        
        -- 你的业务逻辑
        NULL;
        
        -- 计算并输出耗时（转换为秒）
        l_elapsed := (DBMS_UTILITY.GET_TIME - l_start) / 100;
        DBMS_OUTPUT.PUT_LINE('my_proc 执行耗时: ' || l_elapsed || ' 秒');
        -- 也可以将结果插入自定义日志表持久化
        -- INSERT INTO exec_log (proc_name, elapsed_sec) VALUES ('my_proc', l_elapsed);
    END my_proc;

    FUNCTION my_func RETURN NUMBER IS
        l_start TIMESTAMP;
        l_elapsed INTERVAL DAY TO SECOND;
        l_result NUMBER;
    BEGIN
        l_start := SYSTIMESTAMP;
        
        -- 你的函数逻辑
        l_result := 100 * 200;
        
        l_elapsed := SYSTIMESTAMP - l_start;
        DBMS_OUTPUT.PUT_LINE('my_func 执行耗时: ' || l_elapsed);
        RETURN l_result;
    END my_func;
END my_package;

2. 使用PL/SQL Profiler（无需修改包代码） #

Oracle自带的PL/SQL剖析器可以收集程序单元的执行统计信息，包括每个存储过程/函数的总执行时间、调用次数等，无需修改原有包代码。

步骤示例：

-- 1. 启动剖析器
DECLARE
    l_run_id NUMBER;
BEGIN
    DBMS_PROFILER.START_PROFILER('包执行性能分析', l_run_id);
    -- 调用包内的程序单元
    my_package.my_proc;
    DBMS_OUTPUT.PUT_LINE('my_func 返回值: ' || my_package.my_func);
    DBMS_PROFILER.STOP_PROFILER;
END;
/

-- 2. 查询执行时间统计
SELECT
    pu.unit_name AS 程序单元名称,
    SUM(pd.total_time) / 1000000000 AS 总执行时间_秒
FROM
    PLSQL_PROFILER_RUNS pr
JOIN
    PLSQL_PROFILER_UNITS pu ON pr.runid = pu.runid
JOIN
    PLSQL_PROFILER_DATA pd ON pu.runid = pd.runid AND pu.unit_number = pd.unit_number
WHERE
    pr.run_comment = '包执行性能分析'
GROUP BY
    pu.unit_name;

注意：需要拥有EXECUTE ON DBMS_PROFILER权限，且相关分析视图（PLSQL_PROFILER_*）需存在。

3. 使用SQL Trace + TKPROF（结合SQL语句分析） #

通过SQL Trace生成跟踪文件，再用TKPROF工具分析，可同时获取PL/SQL单元和内部SQL语句的执行时间，适合深度性能调优。

步骤示例：

-- 1. 启用SQL Trace
ALTER SESSION SET SQL_TRACE=TRUE;

-- 2. 执行包内程序单元
EXEC my_package.my_proc;
SELECT my_package.my_func FROM DUAL;

-- 3. 关闭SQL Trace
ALTER SESSION SET SQL_TRACE=FALSE;

然后找到Oracle生成的跟踪文件（默认在user_dump_dest目录下），用TKPROF工具解析：

tkprof your_trace_file.trc output_report.txt explain=your_username/your_password

在生成的output_report.txt中，可找到每个PL/SQL单元的执行耗时、调用次数等详细信息。

4. 使用DBMS_HPROF（分层剖析，可视化报告） #

Oracle 11g及以上的分层剖析器，能生成带调用栈的性能报告，支持输出HTML格式，直观展示程序单元的调用关系和执行时间占比。

步骤示例：

-- 1. 创建存放报告的目录（需替换为实际路径）
CREATE DIRECTORY hprof_dir AS '/opt/oracle/hprof_reports';
GRANT READ, WRITE ON DIRECTORY hprof_dir TO your_user;

-- 2. 启动分层剖析
DECLARE
    l_run_id NUMBER;
BEGIN
    DBMS_HPROF.START_PROFILING('HPROF_DIR', 'package_perf.trc');
    my_package.my_proc;
    DBMS_OUTPUT.PUT_LINE('my_func 返回值: ' || my_package.my_func);
    DBMS_HPROF.STOP_PROFILING;
END;
/

-- 3. 生成HTML报告
DECLARE
    l_report CLOB;
BEGIN
    l_report := DBMS_HPROF.ANALYZE('HPROF_DIR', 'package_perf.trc', 'HPROF_DIR', 'package_perf_report.html');
END;
/

打开生成的HTML报告，可清晰看到每个存储过程/函数的执行耗时、调用层级等信息。

内容的提问来源于stack exchange，提问作者Golu Singh