oracle运行 profiles,oracle dbms_profiles分析pl/sql

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