Oracle SQL 学习笔记30 - 分析、跟踪和调试plsq

分析跟踪和调试plsql的一般方法

• 利用数据字典获取代码信息
• 利用DBMS_UTILITY获取运行堆栈和错误堆栈信息
• 利用PLSQL_DEBUG对代码运行进行跟踪
• 利用DBMS_PROFILER对代码运行进行分析
• 利用DBMS_OUTPUT手动调试
• 构建中间临时表进行调试
• 设置调试代码开关参数
• 利用图形界面工具，如sqldeveloper
• 标准化应用异常的定义

找到代码信息

analyze_trace_debug.JPG

使用数据字典视图

• ALL_ARGUMENTS
• ALL_OBJETS
• ALL_SOURCE
• ALL_PROCEDURES
• ALL_DEPENDENCIES

实例

找到代码中所有CHAR的实例

SELECT  NAME,  LINE,  text
FROM  user_source
WHERE  INSRT(UPPER(text),  '  CHAR')  >  0
        OR  INSTR  (UPPER(text),  '  CHAR(')  >  0
        OR  INSTR  (UPPER(text),  '  CHAR  (')  >  0;  

使用ALL_ARGUMENTS

通过查询ALL_ARGUMENTS视图，可以找到存储过程和函数的参数信息。

SELECT  object_name,  argument_name,  in_out,  position,  data_type
FROM  all_arguments
WHERE  package_name  =  'ORDERS_APP_PKG';

使用数据库提供的包

• DBMS_DESCRIBE
• DBMS_UTILITY

使用DBMS_UTILITY.FORMAT_CALL_STATCK

此函数返回当前调用栈的格式化文本字符串，可以用于找到正在执行的代码行。

EXECUTE  third_one
------------ PL/SQL  Call Stack------------
object handle    line number    object name

找到错误信息

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE函数用于展示异常抛出点的调用堆栈，如果没有错误，那么就返回一个Null字符串。

• 实例1

CREATE  OR  REPLACE  PROCEDURE  top_with_logging  IS
  -- NOTE: SQLERRM  in principle gives the same info
  --  as format_error_stack.
  -- But SQLERRM is subject to some length limits,
  -- while format_error_stack is not
BEGIN
  P5();  -- This procedure, in turn, calls others,
         -- building a stack.  P0  contains the exception.
EXCEPTION
  WHEN  OTHERS  THEN
    log_errors(  'Error_Stack...'  ||  CHR(10)  ||
              DBMS_UTILITY.FORMAT_ERROR_STACK());
    log_errors(  'Error_Backtrace...'  ||  CHR(10)  ||
              DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
    DBMS_OUTPUT.PUT_LINE('--------')
END  top_with_logging;
/

• 实例2

CREATE  OR  REPLACE  PROCEDURE  log_errors(  i_buff  IN  VARCHAR2  )  IS
  g_start_pos  PLS_INTEGER  :=  1
  g_end_pos  PLS_INTEGER;
FUNCTION  output_one_line  RETURN  BOOLEAN  IS
BEGIN
  g_end_pos  :=  INSTR(  i_buff,  CHR(10),  g_start_pos  );
  CASE  g_end_pos  >  0
    WHEN  TRUE  THEN
      DBMS_OUTPUT.PUT_LINE(  SUBSTR(  i_buff,  g_start_pos,  
        g_end_pos - g_start_pos));
      g_start_pos  :=  g_end_pos+1;
      RETURN  TRUE
    WHEN  FALSE  THEN
      DBMS_OUTPUT.PUT_LINE(SUBSTR(i_buff,  g_start_pos,
      (LENGTH(i_buff) - g_start_pos) + 1 ));
      RETURN  FALSE;
    END  CASE;
  END  output_one_line;
BEGIN
  WHILE  output_one_line()  LOOP  NULL;
  END  LOOP;
END  log_errors;

跟踪PL/SQL执行

通过DBMS_TRACE包，我们可以跟踪PL/SQL的执行，从而更好的理解程序的执行路径。

trace.JPG

DBMS_TRACE包

DBMS_TRACE包包括：

• set_plsql_trace(trace_level Integer)
• clear_plsql_trace
• plsql_trace_version

set_plsql_trace

使用set_plsql_trace，先指定trace level以确定如何跟踪调用，异常，SQL和代码。支持以下trace_level：

trace_all_calls
trace_enabled_calls
trace_all_sql
trace_enabled_sql
trace_all_exceptions
trace_enabled_exceptions
trace_all_lines
trace_enabled_lines
trace_stop
trace_pause
trace_resume

跟踪PL/SQL的步骤

trace_plsql.JPG

• 步骤1 启动指定的子程序
  有两种方法启动指定的子程序：

1. 在编译的时候打开debug option

ALTER  SESSION  SET  PLSQL_DEBUG=true;
CREATE  OR  REPLACE...

2. 在重新编译的时候打开debug option

ALTER  [PROCEDURE  |  FUNCTION  |  PACKAGE]  <subprogram-name>  COMPILE  DEBUG  [BODY];

• 步骤2和3，指定trace level并开始跟踪

EXECUTE  DBMS_TRACE.SET_PLSQL_TRACE
(tracelevel1  +  tracelevel2 ...)

EXECUTE  my_program

• 步骤4，关闭跟踪

EXECUTE  DBMS_TRACE.CLEAR_PLSQL_TRACE

• 检查跟踪信息
  跟踪信息主要包括：

1. 对调用的跟踪信息会输出程序单元的类型，名称和栈的深度
2. 对异常的跟踪信息输出行号

plsql_trace_runs 和 plsql_trace_events

跟踪信息被输出到以下两个数据字典视图中：plsql_trace_runs 和 plsql_trace_events。
可以执行tracetab.sql脚本来创建数组字典视图。需要权限来查看视图中的跟踪信息。

• 实例

SELECT  proc_name,  proc_line,  event_proc_name,  event_comment
FROM  sys.plsql_trace_events
WHERE  event_proc_name  =  'P5'
OR  PROC_NAME  =  'P5';

Profiling Pl/SQL 应用

可以用Profiling去评估性能和识别需要改进的区域。
Profiling使得我们可以数出每一行执行的次数，决定每一行的执行时间，访问储存在数据库表中的信息——这些信息可以以任意粒度查看。

DBMS_PROFILER可以用来识别性能瓶颈：

profile.JPG

DBMS_PROFILER包

DBMS_PROFILER包主要包括以下部分

• START_PROFILER
• STOP_PROFILER
• FLUSH_DATA
• PAUSE_PROFILER
• RESUME_PROFILER
• GET_VERSION
• INTERNAL_VERSION_CHCEK

Profiling PL/SQL的步骤

profile_step.JPG

• 实例1

CREATE  OR  REPLACE  PROCEDURE  my_profiler
(p_comment1  IN  VARCHAR2,  p_comment2  IN  VARCHAR2)
IS
  v_return_code  NUMBER;
BEGIN
-- start  the profiler
  v_return_code  :=  DBMS_PROFILER.START_PROFILER(p_comment1,  p_comment1)
  dbms_output.put_line('Result  from  START:  '  ||  v_return_code);

-- now run a program...
  query_code_pkg.find_text_in_code('customers');

-- flush the collected data  to the dictionary  tables
  v_return_code  :=  DBMS_PROFILER.FLUSH_DATA;
  dbms_output.put_line('Result  from  FLUSH:  '  ||  v_return_code);

-- stop profiling
  v_return_code  :=  DBMS_PROFILER.STOP_PROFILER
  dbms_output.put_line('Result  from  STOP:  '  ||  v_return_code);
END;
/

• 实例2

EXECUTE  my_profiler('Benchmark:  1',  'This is the first run!')

SELECT  runid,  run_owner,  run_date,  run_comment,  run_commend1,  run_total_time
FROM  plsql_profiler_runs;

• 实例3，查看runid和unit_number

SELECT  runid,  unit_number,  unit_type,  unit_owner,  unit_name
FROM  plsql_profiler_units  inner  JOIN  plsql_profiler_runs
USING  (  runid  );

• 实例4，使用runid和unit_number来查看每行的执行时间

SELECT  line#,  total_occur,  total_time,  min_time,  max_time
FROM  plsql_profiler_data
WHERE  runid  =  1  AND  unit_number  =  2;

其他常用调试方法

• 利用DBMS_OUTPUT手动调试
• 构建中间临时表进行调试
• 设置调试代码开关参数
• 利用图形界面工具，如sqldeveloper
• 标准化应用异常的定义