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