oracle使用profiler分析语句执行效率

转自 http://blog.itpub.net/29371470/viewspace-1064297/

 

1、前言
     Profiler是ORACLE PL/SQL 的一个调试优化跟踪方案，使用profiler可以跟语句执行过程中消耗的时间等。在日常开发过程中，我们可能会常常遇到一些sql语句、存储过程在使用过程中，消耗过多的资源，为了细化了某条具体的语句，我们可以使用profiler。这样大大的减少了我们寻找问题语句的时间，提高效率，在优化的过程中，profiler起到一个很好的效果

2、profiler的一些包的介绍

--proftab.sql介绍
在当前用户建表及序列等，具体如下；
plsql_profiler_runs  - profiler运行信息                    
plsql_profiler_units - profiler每个单元信息                
plsql_profiler_data  - profiler每个单元的详细数据          
plsql_profiler_runnumber 用来生成profiler唯一运行编号的序列

--profload.sql介绍
创建数据库服务包，包括启动、停止等，具体如下：
sys.dbms_profiler包
此包包括以下主要的函数过程    
start_profiler           --启动函数
stop_profiler            --停止函数
pause_profiler
resume_profiler
flush_data
internal_version_check 
get_version
rollup_unit
rollup_run

--具体创建过程
SQL> @$ORACLE_HOME/rdbms/admin/proftab.sql;
drop table plsql_profiler_data cascade constraints
ERROR at line 1:
ORA-00942: table or view does not exist

drop table plsql_profiler_units cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist

drop table plsql_profiler_runs cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist

drop sequence plsql_profiler_runnumber
              *
ERROR at line 1:
ORA-02289: sequence does not exist

 

Table created.

Comment created.

Table created.

Comment created.

Table created.

Comment created.

Sequence created.

在创建的过程中，会检测这些对象是否存在，先进行删除，后面补建成功

SQL> @$ORACLE_HOME/rdbms/admin/profload.sql;

Package created.

Grant succeeded.

Synonym created.

Library created.

Package body created.

Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.

PL/SQL procedure successfully completed.

3、创建我们需要的测试对象

在本存储过程中，我们只进行两条语句的分析处理，具体如下：

SQL> CREATE OR REPLACE PROCEDURE mytest_proc
  2  is
  3    v_counts1 number;
  4    v_counts2 number;
  5    v_counts3 number;
  6  
  7  begin
  8    select count(*) into v_counts1 from dba_tables;
  9    select count(*) into v_counts2 from dba_objects;
 10    select count(*) into v_counts3 from dba_segments;
 11  end;
 12  /

4、开始测试profiler的使用
--针对上面建立的存储过程，对里面的语句进行分析
SQL> begin
  2  DBMS_PROFILER.START_PROFILER('my profiler test');
  3  mytest_proc ;
  4  DBMS_PROFILER.STOP_PROFILER;
  5  end;
  6  /

PL/SQL procedure successfully completed.

--查询刚才分析出来的结果：
SQL> select d.line#,
  2         u.unit_name,
  3         d.total_time,
  4         d.total_occur,
  5         d.max_time,
  6         s.text
  7    from sys.plsql_profiler_data d, sys.all_source s, sys.plsql_profiler_units u
  8   where u.runid = d.runid
  9     and d.unit_number = u.unit_number
 10     and d.total_occur <> 0
 11     and s.type(+) = u.unit_type
 12     and s.owner(+) = u.unit_owner
 13     and s.name(+) = u.unit_name
 14     and d.line# = nvl(s.line, d.line#) and u.unit_name = 'MYTEST_PROC'
 15   order by u.unit_number, d.line#
 16  ;
 
LINE# UNIT_NAME       TOTAL_TIME TOTAL_OCCUR   MAX_TIME TEXT
----- --------------- ---------- ----------- ---------- ----------------------------------------------------
    1 MYTEST_PROC           2006           1       2006 PROCEDURE mytest_proc
    8 MYTEST_PROC      412905914           1  412905914   select count(*) into v_counts1 from dba_tables;
    9 MYTEST_PROC      102775576           1  102775576   select count(*) into v_counts2 from dba_objects;
   10 MYTEST_PROC      222756652           1  222756652   select count(*) into v_counts3 from dba_segments;
   12 MYTEST_PROC           5015           1       5015 end;
   
字段说明：
unit_time     --表示执行的过程、语句、函数等
total_time    --总共运行的时间，单位非常小，万亿分之一秒
total_occur   --执行的次数，用总时间除次数，可得到每次运行的时间
max_time      --代码运行的最最长时间，如有游标循环等
runid         --运行号，这个是自动产生的
Avg_time      --平均运行时间
min_time      --最小运行时间