PLSQL_PROFILE 分析procedure執行過程.
需要執行兩個包.

@?/rdbms/admin/profload.sql
@?/rdbms/admin/proftab.sql
需要執行這兩個指令碼.

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

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

建立如下三個表。
select * from plsql_profiler_runs;
select * from plsql_profiler_units;
select * from plsql_profiler_data;

SQL> create public synonym plsql_profiler_runs for sys.plsql_profiler_runs;

Synonym created.

SQL> create public synonym plsql_profiler_units for sys.plsql_profiler_units;

Synonym created.

SQL> create public synonym plsql_profiler_data for sys.plsql_profiler_data;

Synonym created.

SQL> create public synonym plsql_profiler_runnumber for sys.plsql_profiler_runnumber;

Synonym created.

SQL> grant select on plsql_profiler_runnumber to public;

Grant succeeded.

SQL> grant select,insert,update,delete on plsql_profiler_data to public;

Grant succeeded.

SQL> grant select,insert,update,delete on plsql_profiler_units to public;

Grant succeeded.

SQL> grant select,insert,update,delete on plsql_profiler_runs to public;

Grant succeeded.

create or replace procedure scott.test
as
cnt int;
begin
for i in 1..1000 loop
select count(*) into cnt from ind3 where id=i*i;
end loop;
end;

SQL> declare
2 i_result binary_integer;
3 begin
4 i_result:=dbms_profiler.start_profiler(run_comment=>'test:'||sysdate);
5 test();
6 i_result:=dbms_profiler.stop_profiler;
7 end;
8 /

PL/SQL 過程已成功完成。

SQL> set linesize 200
SQL> alter session set nls_date_format='yyyy/mm/dd'
2 ;

會話已更改。

SQL> col runid format 99999
SQL> col run_comment format a50
SQL> col run_total_time format 99999999999999999
SQL> select runid,run_date,run_comment,run_total_time from plsql_profiler_runs;

RUNID RUN_DATE RUN_COMMENT RUN_TOTAL_TIME
------ ---------- -------------------------------------------------- ------------------
1 2016/01/21 test:21-1月 -16 755444827692

SQL> select * from plsql_profiler_data
2 ;

RUNID UNIT_NUMBER LINE# TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME SPARE1 SPARE2 SPARE3 SPARE4
------ ----------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 1 1 0 0 0 0
1 1 4 0 0 0 0
1 1 5 2 451235974 641598 450594376
1 1 6 1 3078387 3078387 3078387
1 1 7 0 0 0 0
1 2 1 0 3441532 3441532 3441532
1 2 5 1001 137038947 61593 1090716
1 2 6 1000 6.1426E+11 18370238 6.0140E+10
1 2 8 1 1970989 1970989 1970989

已選擇9行。

和all_source連線查詢對應的SQL消耗的時間。
SQL> select al.owner,al.name,al.line,al.text,pl.total_time from all_source al,plsql_profiler_data pl where pl.line#=al.line
2 and al.name='TEST' and pl.runid=2;

OWNER NAME LINE TEXT TOTAL_TIME
---------- ---------- ---- ---------------------------------------------------------------------- ----------------
SCOTT TEST 1 procedure test 0
SCOTT TEST 4 begin 0
SCOTT TEST 5 dbms_profiler.start_profiler(run_comment=>'1111'|| sysdate); 0
SCOTT TEST 6 for i in 1..1000 loop 76841643
SCOTT TEST 7 select count(*) into cnt from ind3 where id=i*i; 20983585353
SCOTT TEST 8 end loop; 0
SCOTT TEST 9 dbms_profiler.stop_profiler; 2717809
SCOTT TEST 10 end; 0

已選擇8行。