当前位置：首页 > article >正文

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

article 2024/12/2 14:39:52

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

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

测试示例。

创建测试表 test。

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

创建存储过程。

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)

在会话级别设置 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)

获取系统时间。

obclient [SYS]> SELECT sysdate from dual;

输出结果如下：

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

调用存储过程。

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

查看 TRACE_ID。

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

输出结果如下：

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

根据 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。