Oracle存储过程慢操作定位方法及Profiler运行报错解决方案

现有Profiler工具启动卡点排查 #

之前使用启用了「Gather SQL Statistics」功能的Profiler时，反复在文件路径校验环节失败，可以先按以下逻辑定位根因：

• 这类需要指定存储路径的Profiler，本质依赖Oracle数据库服务端的可写目录权限，而非客户端本地的存储路径
• 常见失败触发点：
  • 当前执行账号没有创建、读写数据库DIRECTORY对象的权限
  • 错误输入了客户端本地的文件路径，没有传入数据库服务器节点上真实存在的可写物理目录
  • 数据库实例的运行账号对指定的服务器路径没有操作系统级别的读写权限
• 快速修复步骤：
  1. 用有管理员权限的账号在数据库服务器上确认目标可写路径，创建对应目录对象

     CREATE OR REPLACE DIRECTORY PROFILER_TRACE_DIR AS '/opt/oracle/writable_trace_path';
     

  2. 给执行存储过程的业务账号授予对应目录的读写权限

     GRANT READ, WRITE ON DIRECTORY PROFILER_TRACE_DIR TO 你的业务执行账号;
     

  3. 在Profiler的路径配置环节填入对应的数据库目录对象名PROFILER_TRACE_DIR，重新启动流程即可跳过路径校验失败的问题

分层定位耗时最长环节的实操方案 #

第一步：粗粒度拆解包内各存储过程的整体占比 #

不需要一开始就开启细粒度追踪，先缩小排查范围，优先拿到四个存储过程的各自总耗时：

• 优先选不需要客户端本地文件依赖的Hierarchical Profiler（即DBMS_HPROF内置能力），直接将调用树的统计数据落到数据库端，快速拉出每个存储过程的调用次数、累计耗时、占整个流程的比例
• 如果快速搭建临时能力，可以在存储包内部嵌轻量计时逻辑，用自定义临时表记录每个存储过程的入口、出口时间差，示例逻辑：

  -- 可作为包的私有方法内置，无额外依赖
  PROCEDURE log_step_cost(p_step_name VARCHAR2, p_start_time TIMESTAMP DEFAULT NULL) IS
  BEGIN
    IF p_start_time IS NULL THEN
      INSERT INTO pkg_trace_log(step_name, start_ts) VALUES (p_step_name, SYSTIMESTAMP);
    ELSE
      UPDATE pkg_trace_log 
      SET end_ts = SYSTIMESTAMP, 
          cost_ms = ROUND(
            (EXTRACT(DAY FROM (SYSTIMESTAMP - p_start_time))*86400 
            + EXTRACT(HOUR FROM (SYSTIMESTAMP - p_start_time))*3600 
            + EXTRACT(MINUTE FROM (SYSTIMESTAMP - p_start_time))*60 
            + EXTRACT(SECOND FROM (SYSTIMESTAMP - p_start_time)))*1000, 2
          )
      WHERE step_name = p_step_name AND end_ts IS NULL;
    END IF;
    COMMIT;
  END;
  

• 拿到结果后，优先聚焦耗时占比超过70%的单个存储过程，避免无差别全流程追踪带来的性能损耗

第二步：定位单个存储过程内部的最长耗时操作 #

锁定目标存储过程后，用以下两种可靠方式拆解内部环节：

• 启用会话级10046 Trace事件，拿到全量执行细节
  • 在执行目标存储过程前，为当前会话开启带等待事件、绑定变量的Trace能力：

    ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
    -- 执行目标存储过程，例如：DAT_REPORT_V2.AUX_TABLES;
    ALTER SESSION SET EVENTS '10046 trace name context off';
    

  • 将生成的服务端trace文件用tkprof工具格式化后，可直接看到每条SQL的执行耗时、物理读、等待事件，精准定位拖慢流程的具体操作
• 长流程场景下用AWR自动抓取会话等待数据
  • 如果整个存储过程执行时长超过数分钟，不需要额外埋点，直接在执行过程中生成一次AWR快照，执行完成后查询DBA_HIST_ACTIVE_SESS_HISTORY视图，拉取执行期间所有非空闲状态的会话记录，统计不同SQL、PL/SQL行号的等待时长占比，占比最高的即为耗时最长的操作环节

第三步：优化效果验证 #

• 定位到最长耗时操作后，将对应SQL单独抽取，通过EXPLAIN PLAN FOR查看执行计划，排查是否存在全表扫描、索引失效、表关联顺序错误等问题
• 优化完成后重跑带计时逻辑的流程，对比前后的环节耗时差值，即可确认优化效果

内容的提问来源于stack exchange，提问作者Javi Torre