【OceanBase 诊断调优】—— 如何在 OceanBase 数据库 Oracle 模式中定位存储过程内的慢 SQL

OceanBase 数据库 Oracle 模式中由于存储过程内 SQL 较多，存储过程执行慢时，定位具体慢的 SQL 较困难，OceanBase 数据库 V4.2.2 及之后版本，可以通过本文档方法查询存储过程内 SQL 执行时间，可用于定位存储过程内的慢 SQL。

定位 OceanBase 数据库 Oracle 模式中定位存储过程内的慢 SQL

测试示例。

1. 创建测试表 test。

   obclient [SYS]> CREATE TABLE test(n1 INT, d1 DATE);
   Query OK, 0 rows affected (0.183 sec)
   

2. 创建存储过程。

   obclient [SYS]> create or replace PACKAGE pkg_test_plsql IS
       PROCEDURE test
        (
          pn_OperatorID IN INT
        );
      END pkg_test_plsql;
      /
   Query OK, 0 rows affected (0.072 sec)
   
   obclient [SYS]> create or replace PACKAGE BODY pkg_test_plsql IS
       PROCEDURE test
        (
          pn_OperatorID IN INT
        ) IS
        ln_OperatorID INT;
        BEGIN
          ln_OperatorID := pn_OperatorID;
          dbms_lock.sleep(3);
          INSERT INTO test(n1) VALUES(1);
          INSERT INTO test(n1) VALUES(2);
          COMMIT;
          SELECT count(1) INTO ln_OperatorID FROM test;
        END test;
      END pkg_test_plsql;
      /
    Query OK, 0 rows affected (0.131 sec)
   

3. 在会话级别设置 nls_date_format 对应的日期格式。

   bclient [SYS]> ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
   Query OK, 0 rows affected (0.001 sec)
   

4. 获取系统时间。

   obclient [SYS]> SELECT sysdate from dual;
   

   输出结果如下：

   +---------------------+
   | SYSDATE             |
   +---------------------+
   | 2024-10-10 10:09:21 |
   +---------------------+
   1 row in set (0.001 sec)
   

5. 调用存储过程。

   obclient [SYS]> call pkg_test_plsql.test(1);
   Query OK, 0 rows affected (3.199 sec)
   

6. 查看 TRACE_ID。

   obclient [SYS]> SELECT last_trace_id() FROM dual;
   

   输出结果如下：

   +-----------------------------------+
   | LAST_TRACE_ID()                   |
   +-----------------------------------+
   | YB42AC1E87E1-000623EE413F689E-0-0 |
   +-----------------------------------+
   1 row in set (0.002 sec)
   

7. 根据 TRACE_ID 查询存储过程内 SQL 执行时间。

   obclient [SYS]> SELECT
        to_char(to_date('1970-01-01', 'yyyy-mm-dd') + (request_time / 1000000 / 86400) + to_number(substr(tz_offset(sessiontimezone), 1, 3)) / 24, 'YYYYMMDD HH24:MI:SS') request_time_s,
        t.trace_id,
        t.request_type,
        t.pl_trace_id,
        to_char(t.query_sql) query_sql,
        t.elapsed_time/1000000 elapsed_time_s FROM gv$ob_sql_audit t WHERE
        user_name = 'SYS'
        AND t.pl_trace_id = 'YB42AC1E87E1-000623EE413F689E-0-0'
        AND query_sql NOT LIKE '%explain%'
        AND query_sql NOT LIKE '%request_time%' ORDER BY request_time DESC;
   

   输出结果如下：

   +-------------------+-----------------------------------+--------------+-----------------------------------+-------------------------------------------------+----------------+
   | REQUEST_TIME_S    | TRACE_ID                          | REQUEST_TYPE | PL_TRACE_ID                       | QUERY_SQL                                       | ELAPSED_TIME_S |
   +-------------------+-----------------------------------+--------------+-----------------------------------+-------------------------------------------------+----------------+
   | 20241010 10:09:25 | YB42AC1E87E1-000623EE413F68A1-0-0 |           11 | YB42AC1E87E1-000623EE413F689E-0-0 | select count(1) AS "COUNT(1)" from "SYS"."TEST" |        .003413 |
   | 20241010 10:09:25 | YB42AC1E87E1-000623EE413F68A0-0-0 |           11 | YB42AC1E87E1-000623EE413F689E-0-0 | insert into "SYS"."TEST"("N1") values(2)        |        .000102 |
   | 20241010 10:09:25 | YB42AC1E87E1-000623EE413F689F-0-0 |           11 | YB42AC1E87E1-000623EE413F689E-0-0 | insert into "SYS"."TEST"("N1") values(1)        |        .011947 |
   | 20241010 10:09:22 | YB42AC1E87E1-000623EE413F689E-0-0 |            2 | YB42AC1E87E1-000623EE413F689E-0-0 | call pkg_test_plsql.test(1)                     |       3.198526 |
   +-------------------+-----------------------------------+--------------+-----------------------------------+-------------------------------------------------+----------------+
   4 rows in set (0.049 sec)
   

   上述输出结果中 REQUEST_TYPE = 11 代表 PL 内 SQL。

   注意

   正常 SQL 走 SQL 引擎才会被记录到，如上测试示例中 dbms_lock.sleep 不会记录。