# DBE_PROFILER
plprofiler提供了一个接口，用于分析存储过程的应用程序，可以收集、存储以及删除执行存储过程所涉及的相关数据。
#### 概述
在PL/SQL函数和存储过程中查找性能问题存在一定困难。在系统或扩展视图中，仅可查看从客户端发送的查询。在调用存储过程的情况下，仅调用最外层的存储过程。plprofiler扩展可用于快速识别最耗时的存储过程，再向下查看其中单个语句的耗时情况。
DBE_PROFILER工具的原理为在执行DBE_PROFILER.PL_START_PLPROFILING后，系统会初始化内存以准备记录数据，然后对后续执行的存储过程进行打桩，在关键点记录详细信息，并将这些信息记录在内存中，等待事务提交之后将内存中的数据写入相关系统表中，表信息请参见[相关系统表介绍]。
使用此工具，可以在会话中先创建存储过程及相关表信息，然后调用plprofiler接口对存储过程进行profiling，再执行存储过程。此时存储过程分析数据已生成，可以通过查询系统表去获取数据。
可以通过接口DBE_PROFILER.PL_CLEAR_PROFILING去删除某次或所有profiling后储存在系统表里的数据。
在事务中执行DBE_PROFILER工具时须注意，在事务执行阶段，由于事务未提交，所以内存中的数据不会写入系统表中，只有在执行commit后才会将数据写入系统表。同理，执行rollback后，会将所有数据进行清理。
#### 权限介绍
普通用户访问DBE_PROFILER.PL_PROFILING_FUNCTIONS、 DBE_PROFILER.PL_PROFILING_DETAILS、DBE_PROFILER.PL_PROFILING_CALLGRAPH、DBE_PROFILER.PL_PROFILING_TRACKINFO系统表时，仅具有查询记录权限。
#### 接口介绍
高级功能包DBE_PROFILER支持的接口请参见[表1]。
 表1DBE_PROFILER 
| 接口名称                                                                                                                       | 描述                            |
|:---|:---|
| [DBE_PROFILER.PL_START_PROFILING] | 允许用户对目标存储过程进行profiling。        |
| [DBE_PROFILER.PL_CLEAR_PROFILING]                                            | 删除某次或所有profiling后储存在系统表里的数据。 |
   
- DBE_PROFILER.PL_START_PROFILING
  允许用户对目标存储过程进行profiling。
  DBE_PROFILER.PL_START_PROFILING原型为：
  ```
  DBE_PROFILER.PL_START_PROFILING (run_id varchar2(64)) return void;
  ```
  表2DBE_PROFILER.PL_START_PROFILING接口参数说明 
  | 参数         | 描述                                                                                      |
  |:---|:---|
  | run_id | 表示此次profiling的执行编号，系统会将用户的session_id和执行编号拼接为形如sessionid_runid的字符串写入表中，作为本次profiling的唯一标识。 |
     
  
- DBE_PROFILER.PL_CLEAR_PROFILING
  删除某次或所有profiling后储存在系统表里的数据。
  DBE_PROFILER.PL_CLEAR_PROFILING原型为：
  ```
  DBE_PROFILER.PL_CLEAR_PROFILING (run_id varchar2) return void;
  ```
  表3DBE_PROFILER.PL_CLEAR_PROFILING接口参数说明 
  | 参数      | 描述                                                   |
  |:---|:---|
  | run_id | 非空值时，表示删除某次profiling数据的唯一标识；为空值时，表示删除所有profiling后的数据。 |
     
  
 
 #### 相关系统表介绍
表4总览 
| 表名                                                                                      | 描述                     |
|:---|:---|
| [DBE_PROFILER.PL_PROFILING_FUNCTIONS]   | 此次profiling涉及的存储过程。   |
| [DBE_PROFILER.PL_PROFILING_DETAILS]    | 存储过程内每条语句的执行时间细节。       |
| [DBE_PROFILER.PL_PROFILING_CALLGRAPH]   | 调用栈信息和对应每个存储过程执行的整体时间。 |
| [DBE_PROFILER.PL_PROFILING_TRACKINFO] | 存储过程每个阶段的执行时间。        |
   
- DBE_PROFILER.PL_PROFILING_FUNCTIONS
  系统表信息参见[表5]。
   表5DBE_PROFILER.PL_PROFILING_FUNCTIONS 
  | 列            | 类型                     | 描述                               |
  |:---|:---|:---|
  | run_id    | VARCHAR2(80) NOT NULL | 由session_id和执行编号拼接而成的唯一标识。        |
  | funcoid   | BIGINT NOT NULL       | 存储过程id。                            |
  | schema    | TEXT NOT NULL           | 模式名称。                              |
  | funcname  | TEXT NOT NULL           | 存储过程的名称。                            |
  | total_occur | BIGINT                   | 存储过程执行的总次数。                           |
  | init_time  | BIGINT                    | 存储过程执行开始前，变量赋值阶段的时间。               |
  | end_time     | BIGINT                    | 存储过程执行结束后，处理返回值阶段的时间。               |
  | total_time  | BIGINT                  | 存储过程执行的总时间，不包含init_time和end_time。 |
     
  
- DBE_PROFILER.PL_PROFILING_DETAILS
  系统表信息参见[表6]。
   表6DBE_PROFILER.PL_PROFILING_DETAILS 
  | 列             | 类型                      | 描述                                                                                                          |
  |:---|:---|:---|
  | run_id       | VARCHAR2(80) NOT NULL | 由session_id和执行编号拼接而成的唯一标识。                                                                                |
  | funcoid       | BIGINT NOT NULL         | 存储过程id。                                                                                                    |
  | line#      | INTEGER NOT NULL        | 行号。                                                                                                          |
  | source       | TEXT                    | 存储过程执行语句。                                                                                                    |
  | cmd_type      | TEXT                    | 语句类型，在EXECSQL和PERFORM语句时根据是否是函数调用语句在类型后添加_FUNCCALL后缀。如：EXECSQL或EXECSQL_FUNCCALL、PERFORM或PERFORM_FUNCCALL。  |
  | total_occur  | BIGINT                  | 语句的执行次数。                                                                                                    |
  | total_time | BIGINT                 | 语句的总执行时间，对于BODY类语句显示本身的执行时间，不包含里层语句执行时间。BODY类语句如下：BLOCK、IF、FORI、WHILE、LOOP、FORS、FORC、FOREACH_A、DYNFORS和CASE。 |
  | min_time     | BIGINT                 | 语句的最短执行时间。                                                                                                    |
  | max_time     | BIGINT                    | 语句的最长执行时间。                                                                                                |
     
  
- DBE_PROFILER.PL_PROFILING_CALLGRAPH
  系统表信息参见[表7]。
   表7DBE_PROFILER.PL_PROFILING_CALLGRAPH 
  | 列         | 类型                    | 描述                         |
  |:---|:---|:---|
  | run_id     | VARCHAR2(80) NOT NULL | 由session_id和执行编号拼接而成的唯一标识。 |
  | stack    | TEXT\[\] NOT NULL   | 调用栈。                        |
  | self_time | BIGINT                | 存储过程执行时间。                  |
     
  
- DBE_PROFILER.PL_PROFILING_TRACKINFO
  系统表信息参见[表8]。
   表8DBE_PROFILER.PL_PROFILING_TRACKINFO 
  | 列             | 类型                  | 描述                           |
  |:---|:---|:---|
  | run_id       | VARCHAR2(80) NOT NULL | 由session_id和执行编号拼接而成的唯一标识。 |
  | funcoid     | BIGINT NOT NULL      | 存储过程id。                      |
  | step_name   | TEXT NOT NULL       | 执行阶段名称。                     |
  | loops_count | INTEGER              | 执行阶段的总次数。                  |
  | max_time      | BIGINT               | 执行阶段的最长执行时间。                 |
  | min_time    | BIGINT               | 执行阶段的最短执行时间。              |
  | avg_time    | BIGINT              | 执行阶段的平均执行时间。               |
  | total_time   | BIGINT                | 执行阶段的总执行时间。                 |
     
  
 
#### 约束
- DBE_PROFILER.PL_START_PROFILING不支持在存储过程内使用，在存储过程内调用不生效。
- DBE_PROFILER.PL_START_PROFILING不支持事务失败记录统计信息到系统表。
- DBE_PROFILER.PL_START_PROFILING入参runid长度限制为64字节，超长将会报错。
- DBE_PROFILER.PL_START_PROFILING入参如果和profiler系统表内的runid重复将会报错。
- DBE_PROFILER不支持记录匿名块语句、PACKAGE的初始化语句和嵌套子程序内的语句。
- DBE_PROFILER不支持记录自治事务中语句。
- DBE_PROFILER最多可以记录嵌套层数不超过500层的存储过程，但是由于系统表DBE_PROFILER.PL_PROFILING_CALLGRAPH中stack列受索引长度最大值的影响，以及存储过程嵌套深度受GUC参数max_stack_depth的影响，实际最多可记录的存储过程嵌套层数会小于500层。
 
#### 示例
```
-- step1 创建表和存储过程。
gaussdb=# DROP TABLE IF EXISTS t1;
gaussdb=# CREATE TABLE t1 (i int);
gaussdb=# CREATE OR REPLACE PROCEDURE p1() AS
sql_stmt varchar2(200);
result number;
BEGIN
    for i in 1..1000 loop
      insert into t1 values(1);
    end loop;
    sql_stmt := 'select count(*) from t1';
    EXECUTE IMMEDIATE sql_stmt into result;
END;
/
gaussdb=# CREATE OR REPLACE PROCEDURE p2() AS
BEGIN
   p1();
END;
/
gaussdb=# CREATE OR REPLACE PROCEDURE p3() AS
BEGIN
    p2();
END;
/
-- step2 调用plprofiler接口对存储过程进行profiling。
gaussdb=# SELECT dbe_profiler.pl_start_profiling('123');
gaussdb=# CALL p3();
-- step3 查询相关profiling信息。
-- 查询DBE_PROFILER.PL_PROFILING_FUNCTIONS表查看此次profiling涉及的存储过程。
gaussdb=# SELECT * FROM dbe_profiler.pl_profiling_functions ORDER BY run_id, funcoid;
 run_id | funcoid | schema | funcname | total_occur | init_time | end_time | total_time
--------+---------+--------+----------+-------------+-----------+----------+------------
 11_123 |   16736 | public | p1()     |           1 |         8 |        3 |      31429
 11_123 |   16737 | public | p2()     |           1 |         9 |        1 |      32015
 11_123 |   16738 | public | p3()     |           1 |        20 |        2 |      32597
(3 rows)
-- 查询DBE_PROFILER.PL_PROFILING_DETAILS表查看存储过程内每条语句的执行时间细节。
gaussdb=# SELECT * FROM dbe_profiler.pl_profiling_details WHERE funcoid = 16736 ORDER BY run_id, funcoid, line#;
 run_id | funcoid | line# |                   source                    |  cmd_type  | total_occur | total_time | max_time | min_time
--------+---------+-------+---------------------------------------------+------------+-------------+------------+----------+----------
 11_123 |   16736 |     1 |  DECLARE                                    |            |           0 |          0 |        0 |        0
 11_123 |   16736 |     2 | sql_stmt varchar2(200);                     |            |           0 |          0 |        0 |        0
 11_123 |   16736 |     3 | result number;                              |            |           0 |          0 |        0 |        0
 11_123 |   16736 |     4 | BEGIN                                       |            |           0 |          0 |        0 |        0
 11_123 |   16736 |     5 |     for i in 1..1000 loop                   | FORI       |           1 |        659 |      659 |      659
 11_123 |   16736 |     6 |       insert into t1 values(1);             | EXECSQL    |        1000 |      29274 |     2282 |       23
 11_123 |   16736 |     7 |     end loop;                               |            |           0 |          0 |        0 |        0
 11_123 |   16736 |     8 |     sql_stmt := 'select count(*) from t1';  | ASSIGN     |           1 |        297 |      297 |      297
 11_123 |   16736 |     9 |     EXECUTE IMMEDIATE sql_stmt into result; | DYNEXECUTE |           1 |       1190 |     1190 |     1190
 11_123 |   16736 |    10 | END                                         |            |           0 |          0 |        0 |        0
(10 rows)
-- 查询DBE_PROFILER.PL_PROFILING_CALLGRAPH表查看调用栈信息和对应每个存储过程执行的整体时间（total_time、self_time对应调用栈栈顶存储过程的总时间和自身执行时间）。
gaussdb=# SELECT * FROM dbe_profiler.pl_profiling_callgraph ORDER BY run_id, stack;
 run_id |                                   stack                                   | self_time
--------+---------------------------------------------------------------------------+-----------
 11_123 | {"public.p3() oid=16738"}                                                 |       582
 11_123 | {"public.p3() oid=16738","public.p2() oid=16737"}                         |       586
 11_123 | {"public.p3() oid=16738","public.p2() oid=16737","public.p1() oid=16736"} |     31429
(3 rows)
-- 查询DBE_PROFILER.PL_PROFILING_TRACKINFO表查看存储过程每个阶段的执行时间。
gaussdb=# SELECT step_name, loops_count FROM dbe_profiler.pl_profiling_trackinfo WHERE funcoid=16736;
  step_name   | loops_count
--------------+-------------
 cleanup      |           1
 compile      |           1
 exec_context |           1
 exec_cursor  |           1
 execute      |           1
 finsh        |           0
 init         |           1
 package      |           1
 spictx       |           1
(9 rows)
-- step4 删除系统表数据。
gaussdb=# SELECT dbe_profiler.pl_clear_profiling('');
gaussdb=# SELECT step_name, loops_count FROM dbe_profiler.pl_profiling_trackinfo WHERE funcoid=16736;
 step_name | loops_count 
-----------+-------------
(0 rows)
gaussdb=# DROP TABLE t1;
-- step5 profiling包含自治事务的存储过程。
-- 建表。
gaussdb=# CREATE TABLE t2(a int, b int);
-- 创建包含自治事务的存储过程。
gaussdb=# CREATE OR REPLACE PROCEDURE autonomous(a int, b int)  AS 
DECLARE 
num3 int := a;
num4 int := b;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert into t2 values(num3, num4); 
dbe_output.print_line('just use call.');
END;
/
-- 创建调用自治事务存储过程的普通存储过程。
gaussdb=# CREATE OR REPLACE PROCEDURE autonomous_1(a int, b int)  AS 
DECLARE 
BEGIN
dbe_output.print_line('just no use call.');
insert into t2 values(666, 666);
autonomous(a,b);
END;
/
gaussdb=# SELECT dbe_profiler.pl_start_profiling ('100');
gaussdb=# CALL autonomous(11,22);
-- 查询表信息。
gaussdb=# SELECT * FROM dbe_profiler.pl_profiling_functions ORDER BY run_id, funcoid;
 run_id | funcoid | schema | funcname | total_occur | init_time | end_time | total_time
--------+---------+--------+----------+-------------+-----------+----------+------------
(0 rows)
gaussdb=# SELECT * FROM dbe_profiler.pl_profiling_details ORDER BY run_id, funcoid, line#;
 run_id | funcoid | line# | source | cmd_type | total_occur | total_time | max_time | min_time 
--------+---------+-------+--------+----------+-------------+------------+----------+----------
(0 rows)
gaussdb=# SELECT * FROM dbe_profiler.pl_profiling_callgraph ORDER BY run_id, stack;
 run_id | stack | self_time 
--------+-------+-----------
(0 rows)
gaussdb=# SELECT * FROM dbe_profiler.pl_profiling_trackinfo ORDER BY run_id, funcoid;
 run_id | funcoid | step_name | loops_count | max_time | min_time | avg_time | total_time 
--------+---------+-----------+-------------+----------+----------+----------+------------
(0 rows)
gaussdb=# SELECT dbe_profiler.pl_start_profiling ('101');
gaussdb=# CALL autonomous_1(11,22);
gaussdb=# SELECT * FROM dbe_profiler.pl_profiling_functions ORDER BY run_id, funcoid;
 run_id | funcoid |   schema   |    funcname    | total_occur | init_time | end_time | total_time
--------+---------+------------+----------------+-------------+-----------+----------+------------
 11_101 |   16030 | dbe_output | print_line()   |           1 |        11 |       13 |        222
 11_101 |   16765 | public     | autonomous_1() |           1 |        19 |        1 |       9004
(2 rows)
gaussdb=# SELECT * FROM dbe_profiler.pl_profiling_details ORDER BY run_id, funcoid, line#;
 run_id | funcoid | line# |                   source                    |     cmd_type     | total_occur | total_time | max_time | min_time
--------+---------+-------+---------------------------------------------+------------------+-------------+------------+----------+----------
 11_101 |   16030 |     1 |                                             |                  |           0 |          0 |        0 |        0
 11_101 |   16030 |     2 | BEGIN                                       |                  |           0 |          0 |        0 |        0
 11_101 |   16030 |     3 |     PKG_UTIL.io_print(format, true);        | PERFORM_FUNCCALL |           1 |        218 |      218 |      218
 11_101 |   16030 |     4 | END;                                        |                  |           0 |          0 |        0 |        0
 11_101 |   16030 |     5 |                                             |                  |           0 |          0 |        0 |        0
 11_101 |   16765 |     1 |                                             |                  |           0 |          0 |        0 |        0
 11_101 |   16765 |     2 | DECLARE                                     |                  |           0 |          0 |        0 |        0
 11_101 |   16765 |     3 | BEGIN                                       |                  |           0 |          0 |        0 |        0
 11_101 |   16765 |     4 | dbe_output.print_line('just no use call.'); | PERFORM_FUNCCALL |           1 |        797 |      797 |      797
 11_101 |   16765 |     5 | insert into t2 values(666, 666);            | EXECSQL          |           1 |        524 |      524 |      524
 11_101 |   16765 |     6 | autonomous(a,b);                            | PERFORM_FUNCCALL |           1 |       7669 |     7669 |     7669
 11_101 |   16765 |     7 | END                                         |                  |           0 |          0 |        0 |        0
(12 rows)
gaussdb=# SELECT * FROM dbe_profiler.pl_profiling_callgraph ORDER BY run_id, stack;
 run_id |                                  stack                                  | self_time
--------+-------------------------------------------------------------------------+-----------
 11_101 | {"public.autonomous_1() oid=16765"}                                     |      8782
 11_101 | {"public.autonomous_1() oid=16765","dbe_output.print_line() oid=16030"} |       222
(2 rows)
gaussdb=# SELECT * FROM dbe_profiler.pl_profiling_trackinfo ORDER BY run_id, funcoid;
 run_id | funcoid |  step_name   | loops_count | max_time | min_time | avg_time | total_time
--------+---------+--------------+-------------+----------+----------+----------+------------
 11_101 |   16030 | cleanup      |           1 |        1 |        1 |        1 |          1
 11_101 |   16030 | compile      |           1 |       40 |       40 |       40 |         40
 11_101 |   16030 | exec_context |           1 |        3 |        3 |        3 |          3
 11_101 |   16030 | exec_cursor  |           1 |        2 |        2 |        2 |          2
 11_101 |   16030 | execute      |           1 |      287 |      287 |      287 |        287
 11_101 |   16030 | finsh        |           0 |        0 |        0 |        0 |          0
 11_101 |   16030 | init         |           1 |        0 |        0 |        0 |          0
 11_101 |   16030 | package      |           1 |        2 |        2 |        2 |          2
 11_101 |   16030 | spictx       |           1 |        1 |        1 |        1 |          1
 11_101 |   16765 | cleanup      |           1 |        6 |        6 |        6 |          6
 11_101 |   16765 | compile      |           1 |      284 |      284 |      284 |        284
 11_101 |   16765 | exec_context |           1 |        2 |        2 |        2 |          2
 11_101 |   16765 | exec_cursor  |           1 |        2 |        2 |        2 |          2
 11_101 |   16765 | execute      |           1 |     9045 |     9045 |     9045 |       9045
 11_101 |   16765 | finsh        |           0 |        0 |        0 |        0 |          0
 11_101 |   16765 | init         |           1 |        0 |        0 |        0 |          0
 11_101 |   16765 | package      |           1 |        4 |        4 |        4 |          4
 11_101 |   16765 | spictx       |           1 |        0 |        0 |        0 |          0
(18 rows)
gaussdb=# SELECT dbe_profiler.pl_clear_profiling('');
gaussdb=# SELECT * FROM dbe_profiler.pl_profiling_functions;
 run_id | funcoid | schema | funcname | total_occur | init_time | end_time | total_time
--------+---------+--------+----------+-------------+-----------+----------+------------
(0 rows)
gaussdb=# DROP TABLE t2;
```
