SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> show user
USER is "SYS"
SQL> desc dbms_profiler
FUNCTION FLUSH_DATA RETURNS BINARY_INTEGER
PROCEDURE FLUSH_DATA
PROCEDURE GET_VERSION
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MAJOR                          BINARY_INTEGER          OUT
 MINOR                          BINARY_INTEGER          OUT
FUNCTION INTERNAL_VERSION_CHECK RETURNS BINARY_INTEGER
FUNCTION PAUSE_PROFILER RETURNS BINARY_INTEGER
PROCEDURE PAUSE_PROFILER
FUNCTION RESUME_PROFILER RETURNS BINARY_INTEGER
PROCEDURE RESUME_PROFILER
PROCEDURE ROLLUP_RUN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RUN_NUMBER                     NUMBER                  IN
PROCEDURE ROLLUP_UNIT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RUN_NUMBER                     NUMBER                  IN
 UNIT                           NUMBER                  IN
FUNCTION START_PROFILER RETURNS BINARY_INTEGER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RUN_COMMENT                    VARCHAR2                IN     DEFAULT
 RUN_COMMENT1                   VARCHAR2                IN     DEFAULT
 RUN_NUMBER                     BINARY_INTEGER          OUT
PROCEDURE START_PROFILER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RUN_COMMENT                    VARCHAR2                IN     DEFAULT
 RUN_COMMENT1                   VARCHAR2                IN     DEFAULT
 RUN_NUMBER                     BINARY_INTEGER          OUT
FUNCTION START_PROFILER RETURNS BINARY_INTEGER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RUN_COMMENT                    VARCHAR2                IN     DEFAULT
 RUN_COMMENT1                   VARCHAR2                IN     DEFAULT
PROCEDURE START_PROFILER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RUN_COMMENT                    VARCHAR2                IN     DEFAULT
 RUN_COMMENT1                   VARCHAR2                IN     DEFAULT
FUNCTION STOP_PROFILER RETURNS BINARY_INTEGER
PROCEDURE STOP_PROFILER

SQL> conn / as sysdba
Connected.
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.~~~~~~~~~~~~~~~~~
 
SQL> CREATE PUBLIC SYNONYM plsql_profiler_runs FOR plsql_profiler_runs;   创建 同义词       
CREATE PUBLIC SYNONYM plsql_profiler_units FOR plsql_profiler_units;
CREATE PUBLIC SYNONYM plsql_profiler_data FOR plsql_profiler_data;
CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR plsql_profiler_runnumber;

Synonym created.

SQL>
Synonym created.

SQL>
Synonym created.

SQL>
Synonym created.

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_runs TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_units TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_data TO PUBLIC;
GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;

Grant succeeded.

SQL>
Grant succeeded.

SQL>
Grant succeeded.

SQL>
Grant succeeded.

SQL> conn xh/a831115
Connected.
SQL> create table t1 (a int,b int);

Table created.

SQL>  create or replace procedure  pr_t1 is
  2     begin
  3    for i in 1..1000 loop
  4    insert into t1 values(i,i+1);
  5    end loop;
  6  commit;
  7   end ;
  8  /

Procedure created.

SQL> execute pr_t1;

PL/SQL procedure successfully completed.

SQL> select count(*) from t1;

  COUNT(*)
----------
      1000

SQL> conn xh/a831115
Connected.
SQL> SELECT dbms_profiler.start_profiler AS status from dual;

    STATUS
----------
         0

SQL> execute pr_t1;

PL/SQL procedure successfully completed.

SQL> SELECT dbms_profiler.stop_profiler() AS status,plsql_profiler_runnumber.currval AS runid FROM dual;

    STATUS      RUNID
---------- ----------
         0          1

SQL>  COL line FORMAT 9,999 HEADING LINE#
SQL>  COL total_occur FORMAT 9,999,999 HEADING EXEC#
SQL> COL time FORMAT 9,990.9 HEADING TIME%
SQL>  COL text FORMAT A100 HEADING CODE
SQL>  SELECT s.line,
  2   round(ratio_to_report(p.total_time) OVER ()*100,1) AS time,
  3   total_occur,
  4   s.text
  5   FROM all_source s,
  6   (SELECT u.unit_owner, u.unit_name, u.unit_type,
  7   d.line#, d.total_time, d.total_occur
  8   FROM plsql_profiler_units u, plsql_profiler_data d
  9   WHERE u.runid = &runid
 10   AND d.runid = u.runid
 11   AND d.unit_number = u.unit_number) p
 12   WHERE s.owner = p.unit_owner (+)
 13   AND s.name = p.unit_name (+)
 14   AND s.type = p.unit_type (+)
 15   AND s.line = p.line# (+)
 16   AND s.owner = '&owner'
 17   AND s.name = '&name'
 18   ORDER BY s.line;
Enter value for runid: 1
old   9:  WHERE u.runid = &runid
new   9:  WHERE u.runid = 1
Enter value for owner: xh
old  16:  AND s.owner = '&owner'
new  16:  AND s.owner = 'xh'
Enter value for name: pr_t1
old  17:  AND s.name = '&name'
new  17:  AND s.name = 'pr_t1'

 LINE#    TIME%      EXEC# CODE
------ -------- ---------- ----------------------------------------
     1      0.0          0 procedure  pr_t1 is                                      
     2                        begin
     3      3.9      1,001   for i in 1..1000 loop                           可以看到执行了很好的分析
     4     93.8      1,000   insert into t1 values(i,i+1);
     5                       end loop;
     6      2.3          1 commit;
     7      0.0          1  end ;

7 rows selected.

plsql_profiler_runs表：包含哪些刨析会话已经运行

runid
 NUMBER PRIMARY KEY
 Unique run identifier from plsql_profiler_runnumber
 
related_run
 NUMBER
 Runid of related run (for client/server correlation)
 
run_owner
 VARCHAR2(32),
 User who started run
 
run_date
 DATE
 Start time of run
 
run_comment
 VARCHAR2(2047)
 User provided comment for this run
 
run_total_time
 NUMBER
 Elapsed time for this run in nanoseconds
 
run_system_info
 VARCHAR2(2047)
 Currently unused
 
run_comment1
 VARCHAR2(2047)
 Additional comment
 
spare1
 VARCHAR2(256)
 Unused
 

plsql_profiler_units:提供每次运行所执行的单元
runid
 NUMBER
 Primary key, references plsql_profiler_runs,
 
unit_number
 NUMBER
 Primary key, internally generated library unit #
 
unit_type
 VARCHAR2(32)
 Library unit type
 
unit_owner
 VARCHAR2(32)
 Library unit owner name
 
unit_name
 VARCHAR2(32)
 Library unit name timestamp on library unit
 
unit_timestamp
 DATE
 In the future will be used to detect changes to unit between runs
 
total_time
 NUMBER
 Total time spent in this unit in nanoseconds. The profiler does not set this field, but it is provided for the convenience of analysis tools.
 
spare1
 NUMBER
 Unused
 
spare2 NUMBER Unused
 

PLSQL_PROFILER_DATA :含前面分析数据
runid
 NUMBER
 Primary key, unique (generated) run identifier
 
unit_number
 NUMBER
 Primary key, internally generated library unit number
 
line#
 NUMBER
 Primary key, not null, line number in unit
 
total_occur
 NUMBER
 Number of times line was executed
 
total_time
 NUMBER
 Total time spent executing line in nanoseconds
 
min_time
 NUMBER
 Minimum execution time for this line in nanoseconds
 
max_time
 NUMBER
 Maximum execution time for this line in nanoseconds
 
spare1
 NUMBER
 Unused
 
spare2
 NUMBER
 Unused
 
spare3
 NUMBER
 Unused
 
spare4
 NUMBER
 Unused