通过dbms_profiler分析脚本执行效率

最新推荐文章于 2023-07-21 14:54:00 发布

cuihuang1302

最新推荐文章于 2023-07-21 14:54:00 发布

阅读量120

点赞数

文章标签：数据库

定位存储过程中哪一段代码费时，一直是个麻烦事情，DBMS_PROFILER包的出现可以解决这个难题，它甚至可以定位每行代码的运行时间!

首先，以dba身份登录命令窗口，执行profload.sql和proftab.sql脚本：

$ sqlplus / as sysdba

SQL> @?/rdbms/admin/profload

程序包已创建。

授权成功。

同义词已创建。

库已创建。

程序包体已创建。

Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.

PL/SQL 过程已成功完成。

SQL> @?/rdbms/admin/proftab
drop table plsql_profiler_data cascade constraints
*
第1行出现错误:
ORA-00942: 表或视图不存在

drop table plsql_profiler_units cascade constraints
*
第1行出现错误:
ORA-00942: 表或视图不存在

drop table plsql_profiler_runs cascade constraints
*
第1行出现错误:
ORA-00942: 表或视图不存在

drop sequence plsql_profiler_runnumber
*
第1行出现错误:
ORA-02289: 序列不存在

表已创建。

注释已创建。

表已创建。

注释已创建。

表已创建。

注释已创建。

序列已创建。

SQL>

然后使用以下语句，创建SYS对象相关对应的synonym：

create public synonym plsql_profiler_runs for sys.plsql_profiler_runs;
create public synonym plsql_profiler_data for sys.plsql_profiler_data;
create public synonym plsql_profiler_units for sys.plsql_profiler_units;
create public synonym plsql_profiler_runnumber for sys.plsql_profiler_runnumber;

最后对以上对象，进行授权：

grant select on plsql_profiler_runnumber to public;
grant select,insert,update,delete on plsql_profiler_data to public;
grant select,insert,update,delete on plsql_profiler_runs to public;
grant select,insert,update,delete on plsql_profiler_units to public;

当一切准备就绪，我们就可以正式开始使用DBMS_PROFILER来为我们服务了。

运行一段类似如下的脚本：

declare
err number;
begin
err := dbms_profiler.start_profiler('test_free');//这里的test_free只是一个标识作用，对后面查询该执行效率有用，所以每次运行，最好都改变一次，便于定位对应的执行效率结果
dbms_output.put_line(err);//没作用，完全可以不要
。。。// 我们需要测试性能的procedures或者functions等PLSQL代码块
err := dbms_profiler.stop_profiler();
dbms_output.put_line(err);//没作用，完全可以不要
end;

然后通过以下语句，查看每行SQL的执行效率：

SELECT C.LINE#, C.TOTAL_OCCUR, C.TOTAL_TIME, C.MIN_TIME, C.MAX_TIME
FROM PLSQL_PROFILER_RUNS A, PLSQL_PROFILER_UNITS B, PLSQL_PROFILER_DATA C
WHERE A.RUN_COMMENT = 'test_free' --上面执行时，任意指定的一个标识字符串
   AND B.UNIT_OWNER = 'MS' --连接数据库的Oracle的用户
   AND A.RUNID = B.RUNID
   AND A.RUNID = C.RUNID
   AND B.UNIT_NUMBER = C.UNIT_NUMBER;

获得类似如下结果：

     LINE# TOTAL_OCCUR TOTAL_TIME   MIN_TIME   MAX_TIME
---------- ----------- ---------- ---------- ----------
         1           1      44000      44000      44000
         4           1       1000       1000       1000
         5           1       1000       1000       1000
         6           1       1000       1000       1000
         9           1       1000       1000       1000
        10           1       2000       2000       2000
        11           1          0          0          0
        12           1       3000       3000       3000
        13           1       2000       2000       2000
        14           1       2000       2000       2000
        15           1       1000       1000       1000
        20           1       1000       1000       1000
        21           1       1000       1000       1000
        22           1       2000       2000       2000
        23           1       1000       1000       1000
        25           1       1000       1000       1000
        28           1       1000       1000       1000
        31           1       1000       1000       1000
        52           1          0          0          0
        55           1     314000     314000     314000