1,--如果是第一次使用，需要检查DBMS_PROFILER 包是否安装。

#sqlplus APPS/ --注意用户
sql>DESC DBMS_PROFILER;
.
2,如果DBMS_PROFILER没有安装，使用SYS 用来执行如下命令来安装该包。
SQL> conn / as sysdba
Connected.
SQL> CREATE USER profiler IDENTIFIED BY profiler DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON

USERS;

User created.

SQL> GRANT CONNECT, CREATE TABLE, CREATE SEQUENCE TO profiler;

Grant succeeded.
SQL> GRANT CONNECT, CREATE TABLE, CREATE SEQUENCE TO profiler;

Grant succeeded

#sqlplus SYS/ --注意用户

SQL> START ?/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.

GRANT SELECT ON profiler.plsql_profiler_runnumber TO PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE ON profiler.plsql_profiler_runs TO PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE ON profiler.plsql_profiler_units TO PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE ON profiler.plsql_profiler_data TO PUBLIC;•用 SYS 身份登录

数据库，为表和序列创建 public 同义词
CREATE PUBLIC SYNONYM plsq_profiler_runnumber FOR profiler.plsql_profiler_runnumber;

CREATE PUBLIC SYNONYM plsql_profiler_runs FOR profiler.plsql_profiler_runs;

CREATE PUBLIC SYNONYM plsql_profiler_units FOR profiler.plsql_profiler_units;

CREATE PUBLIC SYNONYM plsql_profiler_data FOR profiler.plsql_profiler_data;
(二)  使用 DBMS_PROFILER 收集信息:

begin

  -- Call the procedure

  dbms_profiler.start_profiler('profiler');

   FOR i IN 1..10000 LOOP

    EXECUTE IMMEDIATE 'INSERT INTO t VALUES('|| TO_CHAR(i) ||')';

  END LOOP;

  dbms_profiler.stop_profiler;

end;

可以通过下面SQL查询RUNID:

SELECT runid, run_owner, run_date, run_comment FROM plsql_profiler_runs WHERE

run_comment='profiler';

SQL> desc plsql_profiler_data
 Name                                     Null?    Type
 ---------------------------------------- -------- ---------------------------
 RUNID                                    NOT NULL NUMBER
 UNIT_NUMBER                              NOT NULL NUMBER
 LINE#                                    NOT NULL NUMBER
 TOTAL_OCCUR                                       NUMBER
 TOTAL_TIME                                        NUMBER
 MIN_TIME                                          NUMBER
 MAX_TIME                                          NUMBER
 SPARE1                                            NUMBER
 SPARE2                                            NUMBER
 SPARE3                                            NUMBER
 SPARE4                                            NUMBER

SQL> BEGIN
  2    DBMS_PROFILER.START_PROFILER('only test system procedure');
  3  system.ora$_sys_rep_auth;  //測試存儲過程
  4  DBMS_PROFILER.STOP_PROFILER;
  5   end;
  6  /

PL/SQL procedure successfully completed.

SQL> START profiler.sql

Enter value for 1: 1

也可以用以下SQL进行查询

引用

column unit_name format a15 truncate;

column occured format 999999 ;

column line# format 99999 ;

column tot_time format 999.999999 ;

select p.unit_name, p.occured, p.tot_time, p.line# line,

       substr(s.text, 1,75) text

  from

       (select u.unit_name, d.TOTAL_OCCUR occured,

               (d.TOTAL_TIME/1000000000) tot_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= &RUN_ID) p, 

       user_source s

where p.unit_name = s.name(+) and  p.line# = s.line (+)

order by p.unit_name, p.line#;

5、跟踪完成后，可在当前目录找到profiler_.htm的l一html文件，里面存放了PLSQL各模块的执行效率

。

6、如果要查看PL/SQL的源代码，可以profgsrc.sql脚本查看，执行该脚本后会在当前目录生成存放源代码的

sql文件

引用

SQL> @profgsrc.sql system.ora$_sys_rep_auth

Usage:

sqlplus apps/

SQL> START profgsrc.sql

NAME                           TYPE

------------------------------ ------------