Oracle利用 DBMS_PROFILER 包来分析PL/SQL瓶颈
Oracle利用 DBMS_PROFILER 包来分析PL/SQL瓶颈
在 Oracle 10g 版本之前，默认情况下不会安装 DBMS_PROFILER 包，此次演示版本为11.2.0.4.0，默认已经安装。
安装 DBMS_PROFILER 包方式如下：
sqlplus / as sysdba desc dbms_profiler --不存在则通过下面的方式创建 @?/rdbms/admin/profload.sql
另外，DBMS_PROFILER 包收集的分析信息存储在多个表中，而这些表默认情况下不会创建，需要在使用 DBMS_PROFIER 包之前在所有数据库版本中显式创建。
方法是运行位于 $ORACLE_HOME/rdbms/admin 文件夹中的脚本“proftab.sql”。这些表可以在普通用户或 SYS 用户中创建，如果在 SYS 中创建，则需要向想要使用 DBMS_PROFILER 包的用户授予适当的权限。
运行“proftab.sql”创建以下对象：
- PLSQL_PROFILER_RUNS - PL/SQL 探查器的运行特定信息。
- PLSQL_PROFILER_UNITS - 有关运行中每个库单元的信息。
- PLSQL_PROFILER_DATA - 所有分析器运行的累积数据。
- 序列 PLSQL_PROFILER_RUNNUMBER
这里使用 SYS 用户创建并授权：
sqlplus / as sysdba @?/rdbms/admin/proftab.sql GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC; GRANT SELECT,INSERT,UPDATE,DELETE ON sys.plsql_profiler_data TO PUBLIC; GRANT SELECT,INSERT,UPDATE,DELETE ON sys.plsql_profiler_units TO PUBLIC; GRANT SELECT,INSERT,UPDATE,DELETE ON sys.plsql_profiler_runs TO PUBLIC; CREATE PUBLIC SYNONYM sys.plsql_profiler_runnumber FOR plsql_profiler_runnumber; CREATE PUBLIC SYNONYM sys.plsql_profiler_runs FOR plsql_profiler_runs; CREATE PUBLIC SYNONYM sys.plsql_profiler_units FOR plsql_profiler_units; CREATE PUBLIC SYNONYM sys.plsql_profiler_data FOR plsql_profiler_data;
https://www.cnblogs.com/PiscesCanon/p/17684336.html
这样所有的普通用户都有权限操作以上涉及的4个对象。
接下来使用普通用户ZKM进行测试，创建测试表和存储过程如下：
CREATE TABLE zkm (id int); CREATE OR REPLACE procedure proc_insert_zkm as begin for x in 1..10000 loop insert into zkm values(x); commit; end loop; end proc_insert_zkm; /
开始测试：
1. SET SERVEROUTPUT ON DECLARE v_run_id PLS_INTEGER; v_ret_value PLS_INTEGER; BEGIN v_ret_value := DBMS_PROFILER.START_PROFILER('TEST PROFILER','SAMPLE PROFILER',v_run_id); DBMS_OUTPUT.PUT_LINE ('The run_number is : ' || v_run_id); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error while starting the profiler :' || SQLERRM); END; / 2. EXEC proc_insert_zkm; --你需要调试的SQL、存储过程，匿名块等 3. BEGIN DBMS_PROFILER.STOP_PROFILER; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error while stopping the profiler :' || SQLERRM); END; /
同个会话执行：
---step 1 10:54:50 ZKM@zkmdb(26)> SET SERVEROUTPUT ON 10:54:57 ZKM@zkmdb(26)> DECLARE 10:54:57 2 v_run_id PLS_INTEGER; 10:54:57 3 v_ret_value PLS_INTEGER; 10:54:57 4 BEGIN 10:54:57 5 v_ret_value := DBMS_PROFILER.START_PROFILER('TEST PROFILER','SAMPLE PROFILER',v_run_id); 10:54:57 6 DBMS_OUTPUT.PUT_LINE ('The run_number is : ' || v_run_id); 10:54:57 7 EXCEPTION WHEN OTHERS THEN 10:54:57 8 DBMS_OUTPUT.PUT_LINE('Error while starting the profiler :' || SQLERRM); 10:54:57 9 END; 10:54:57 10 / The run_number is : 4 PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 ---step 2 10:55:00 ZKM@zkmdb(26)> EXEC proc_insert_zkm; PL/SQL procedure successfully completed. Elapsed: 00:00:00.96 ---step 3 10:55:11 ZKM@zkmdb(26)> BEGIN 10:55:14 2 DBMS_PROFILER.STOP_PROFILER; 10:55:14 3 EXCEPTION WHEN OTHERS THEN 10:55:14 4 DBMS_OUTPUT.PUT_LINE('Error while stopping the profiler :' || SQLERRM); 10:55:14 5 END; 10:55:14 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.01
可以查询视图PLSQL_PROFILER_RUNS来查看执行过的DBMS_PROFILER来调试的信息：
10:59:53 SYS@zkmdb(503)> COL RUN_COMMENT FOR A25 11:00:08 SYS@zkmdb(503)> SELECT RUNID, RUN_DATE, RUN_COMMENT FROM PLSQL_PROFILER_RUNS; RUNID RUN_DATE RUN_COMMENT ---------- ------------------- ------------------------- 1 2023-09-07 10:37:38 TEST PROFILER 2 2023-09-07 10:38:15 TEST PROFILER 3 2023-09-07 10:46:52 TEST PROFILER 4 2023-09-07 10:47:59 TEST PROFILER 5 2023-09-07 10:54:20 TEST PROFILER Elapsed: 00:00:00.00
通过上边step 1步骤获得的run_number为4，结合一下脚本可以查询到我们需要的信息：
COLUMN UNIT_NAME FORMAT A35 COLUMN OCCURRED FORMAT 999999 COLUMN LINE FORMAT 9999 COLUMN TOT_TIME FORMAT 999.999999 COLUMN TEXT FORMAT A46 SELECT P.UNIT_NAME, P.OCCURRED, P.TOT_TIME, P.MIN_TIME, P.MAX_TIME, P.LINE# LINE, SUBSTR(S.TEXT, 1,75) TEXT FROM (SELECT U.UNIT_NAME, D.TOTAL_OCCUR OCCURRED, (D.TOTAL_TIME/1E9) TOT_TIME, D.MIN_TIME/1E9 MIN_TIME, D.MAX_TIME/1E9 MAX_TIME, D.LINE# FROM PLSQL_PROFILER_UNITS U, PLSQL_PROFILER_DATA D WHERE D.RUNID=U.RUNID AND D.UNIT_NUMBER = U.UNIT_NUMBER AND D.TOTAL_OCCUR >0 AND U.RUNID= 4) P,USER_SOURCE S WHERE P.UNIT_NAME = S.NAME(+) AND P.LINE# = S.LINE (+) ORDER BY P.LINE#;
10:59:27 ZKM@zkmdb(26)> COLUMN UNIT_NAME FORMAT A35 11:03:00 ZKM@zkmdb(26)> COLUMN OCCURRED FORMAT 999999 11:03:00 ZKM@zkmdb(26)> COLUMN LINE FORMAT 9999 11:03:00 ZKM@zkmdb(26)> COLUMN TOT_TIME FORMAT 999.999999 11:03:00 ZKM@zkmdb(26)> COLUMN TEXT FORMAT A46 11:03:01 ZKM@zkmdb(26)> SELECT P.UNIT_NAME, P.OCCURRED, P.TOT_TIME, P.MIN_TIME, P.MAX_TIME, P.LINE# LINE, SUBSTR(S.TEXT, 1,75) TEXT 11:03:01 2 FROM (SELECT U.UNIT_NAME, D.TOTAL_OCCUR OCCURRED, (D.TOTAL_TIME/1E9) TOT_TIME, D.MIN_TIME/1E9 MIN_TIME, D.MAX_TIME/1E9 MAX_TIME, 11:03:01 3 D.LINE# FROM PLSQL_PROFILER_UNITS U, PLSQL_PROFILER_DATA D 11:03:01 4 WHERE D.RUNID=U.RUNID AND D.UNIT_NUMBER = U.UNIT_NUMBER AND D.TOTAL_OCCUR >0 11:03:01 5 AND U.RUNID= 4) P,USER_SOURCE S 11:03:01 6 WHERE P.UNIT_NAME = S.NAME(+) AND P.LINE# = S.LINE (+) 11:03:01 7 ORDER BY P.LINE#; UNIT_NAME OCCURRED TOT_TIME MIN_TIME MAX_TIME LINE TEXT ----------------------------------- -------- ----------- ---------- ---------- ----- ---------------------------------------------- <anonymous> 3 .000028 .000001 .000024 1 <anonymous> 2 .000086 .000003 .000038 1 <anonymous> 2 .000116 .000004 .00004 1 <anonymous> 2 .000065 0 .000063 1 <anonymous> 2 .000094 .000004 .000041 1 <anonymous> 1 .000004 .000004 .000004 1 <anonymous> 2 .000063 .000001 .000062 2 PROC_INSERT_ZKM 10001 .001925 0 .000016 4 for x in 1..10000 <anonymous> 1 .000037 .000037 .000037 5 <anonymous> 1 .000043 .000043 .000043 6 <anonymous> 1 .000041 .000041 .000041 6 PROC_INSERT_ZKM 10000 .571903 .000049 .001052005 6 insert into zkm values(x); PROC_INSERT_ZKM 10000 .383340 .000033 .000887004 7 commit; PROC_INSERT_ZKM 1 .000002 .000002 .000002 9 end proc_insert_zkm; <anonymous> 1 .000001 .000001 .000001 9 <anonymous> 1 .000001 .000001 .000001 11 16 rows selected. Elapsed: 00:00:00.06
OCCURRED：当前line被执行的次数
TOT_TIME：执行line总共消耗时间，单位s
MIN_TIME：该line在执行了occurred次中某一次的最短时间，单位s
MAX_TIME：该line在执行了occurred次中某一次的最长时间，单位s
PS：可自行修改脚本调整时间单位，PLSQL_PROFILER_DATA.MAX_TIME单位是纳秒。
参考：
https://cloud.tencent.com/developer/article/1431556?from=15425
Using DBMS_PROFILER (文档 ID 97270.1)
防偷防爬：https://www.cnblogs.com/PiscesCanon/p/17684336.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_PROFILER.html
