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> 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> 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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-622540/，如需轉載，請註明出處，否則將追究法律責任。

oracle dbms_profiles分析pl/sql

相關文章