dbms_profiler

从oracle8i开始oracle提供dbms_profiler包来收集pl/sql块执行情况,通过分析收集的数据进而找到pl/sql的瓶颈......

dbms_profiler安装:
1. @%oracle_home%rdbmsadminprofload as sys
2. @%oracle_home%rdbmsadminproftab as user
3. @%oracle_home%plsqldemoprofrep as user

统计数据:
@%oracle_home%plsqldemoprofsum

SQL> delete from plsql_profiler_data;

6 rows deleted.

SQL> delete from plsql_profiler_units;

2 rows deleted.

SQL> delete from plsql_profiler_runs;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> declare
2 v_count integer:=0;
3 begin
4 dbms_profiler.start_profiler('test');
5 for i in 1..100 loop
6 if mod(i,2)=0 then
7 v_count:=v_count+i;
8 end if;
9 end loop;
10 dbms_profiler.stop_profiler();
11 end;
12 /

PL/SQL procedure successfully completed.

SQL> @%oracle_home%plsqldemoprofsum
SP2-0310: unable to open file "profrep.sql"

1 row updated.

PL/SQL procedure successfully completed.

GRAND_TOTA
----------
.09

RUNID RUN_COMMENT SECONDS
----- ----------- ----------
3 test .543014545

RUNID RUN_COMMENT UNIT_OWNER UNIT_NAME SECONDS PERCEN
----- ----------- ----------- -------------- --------- ------
3 test .09 17.4

UNIT_OWNER UNIT_NAME SECONDS PERCENTAG
----------- -------------- --------- ---------
.09 100.00

no rows selected

no rows selected

no rows selected

PL/SQL procedure successfully completed.

no rows selected

no rows selected

LINES_EXECUTED
--------------
0

LINES_PRESENT
-------------
0

=================================trace info=================================
===========================Results for run #3 made on 21-AUG-06 15:33:07 =========================
(test) Run total time: .54 seconds
Unit #1: . - Total time: .09 seconds
============================================================================

PL/SQL procedure successfully completed.

================== Profiler report - all runs rolled up ===================
Unit .:
============================================================================

PL/SQL procedure successfully completed.

Tom给出了一个改进的profsum:

set echo off
set linesize 5000
set trimspool on
set serveroutput on
set termout off

column owner format a11
column unit_name format a14
column text format a21 word_wrapped
column runid format 9999
column secs format 999.99
column hsecs format 999.99
column grand_total format 9999.99
column run_comment format a11 word_wrapped
column line# format 99999
column pct format 999.9
column unit_owner format a11

spool profsum.out

/* Clean out rollup results, and recreate */
update plsql_profiler_units set total_time = 0;

execute prof_report_utilities.rollup_all_runs;

prompt =
prompt =
prompt ====================
prompt Total time
select grand_total/1000000000 as grand_total
from plsql_profiler_grand_total;

prompt =
prompt =
prompt ====================
prompt Total time spent on each run
select runid,
substr(run_comment,1, 30) as run_comment,
run_total_time/1000000000 as secs
from (select a.runid, sum(a.total_time) run_total_time, b.run_comment
from plsql_profiler_units a, plsql_profiler_runs b
where a.runid = b.runid group by a.runid, b.run_comment )
where run_total_time > 0
order by runid asc;

prompt =
prompt =
prompt ====================
prompt Percentage of time in each module, for each run separately

select p1.runid,
substr(p2.run_comment, 1, 20) as run_comment,
p1.unit_owner,
decode(p1.unit_name, '', '',
substr(p1.unit_name,1, 20)) as unit_name,
p1.total_time/1000000000 as secs,
TO_CHAR(100*p1.total_time/p2.run_total_time, '999.9') as percentage
from plsql_profiler_units p1,
(select a.runid, sum(a.total_time) run_total_time, b.run_comment
from plsql_profiler_units a, plsql_profiler_runs b
where a.runid = b.runid group by a.runid, b.run_comment ) p2
where p1.runid=p2.runid
and p1.total_time > 0
and p2.run_total_time > 0
and (p1.total_time/p2.run_total_time) >= .01
order by p1.runid asc, p1.total_time desc;

column secs form 9.99
prompt =
prompt =
prompt ====================
prompt Percentage of time in each module, summarized across runs
select p1.unit_owner,
decode(p1.unit_name, '', '', substr(p1.unit_name,1, 25)) as
unit_name,
p1.total_time/1000000000 as secs,
TO_CHAR(100*p1.total_time/p2.grand_total, '99999.99') as percentage
from plsql_profiler_units_cross_run p1,
plsql_profiler_grand_total p2
order by p1.total_time DESC;

prompt =
prompt =
prompt ====================
prompt Lines taking more than 1% of the total time, each run separate
select p1.runid as runid,
p1.total_time/10000000 as Hsecs,
p1.total_time/p4.grand_total*100 as pct,
substr(p2.unit_owner, 1, 20) as owner,
decode(p2.unit_name, '', '', substr(p2.unit_name,1, 20)) as
unit_name,
p1.line#,
( select p3.text
from all_source p3
where p3.owner = p2.unit_owner and
p3.line = p1.line# and
p3.name=p2.unit_name and
p3.type not in ( 'PACKAGE', 'TYPE' )) text
from plsql_profiler_data p1,
plsql_profiler_units p2,
plsql_profiler_grand_total p4
where (p1.total_time >= p4.grand_total/100)
AND p1.runID = p2.runid
and p2.unit_number=p1.unit_number
order by p1.total_time desc;

prompt =
prompt =
prompt ====================
prompt Most popular lines (more than 1%), summarize across all runs
select p1.total_time/10000000 as hsecs,
p1.total_time/p4.grand_total*100 as pct,
substr(p1.unit_owner, 1, 20) as unit_owner,
decode(p1.unit_name, '', '',
substr(p1.unit_name,1, 20)) as unit_name,
p1.line#,
( select p3.text from all_source p3
where (p3.line = p1.line#) and
(p3.owner = p1.unit_owner) AND
(p3.name = p1.unit_name) and
(p3.type not in ( 'PACKAGE', 'TYPE' ) ) ) text
from plsql_profiler_lines_cross_run p1,
plsql_profiler_grand_total p4
where (p1.total_time >= p4.grand_total/100)
order by p1.total_time desc;

execute prof_report_utilities.rollup_all_runs;

prompt =
prompt =
prompt ====================
prompt Number of lines actually executed in different units (by unit_name)

select p1.unit_owner,
p1.unit_name,
count( decode( p1.total_occur, 0, null, 0)) as lines_executed ,
count(p1.line#) as lines_present,
count( decode( p1.total_occur, 0, null, 0))/count(p1.line#) *100
as pct
from plsql_profiler_lines_cross_run p1
where (p1.unit_type in ( 'PACKAGE BODY', 'TYPE BODY',
'PROCEDURE', 'FUNCTION' ) )
group by p1.unit_owner, p1.unit_name;

prompt =
prompt =
prompt ====================
prompt Number of lines actually executed for all units
select count(p1.line#) as lines_executed
from plsql_profiler_lines_cross_run p1
where (p1.unit_type in ( 'PACKAGE BODY', 'TYPE BODY',
'PROCEDURE', 'FUNCTION' ) )
AND p1.total_occur > 0;

prompt =
prompt =
prompt ====================
prompt Total number of lines in all units
select count(p1.line#) as lines_present
from plsql_profiler_lines_cross_run p1
where (p1.unit_type in ( 'PACKAGE BODY', 'TYPE BODY',
'PROCEDURE', 'FUNCTION' ) );

spool off
set termout on
edit profsum.out
set linesize 131

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45259/viewspace-134884/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/45259/viewspace-134884/