分类: Oracle
2008-05-07 17:31:25
环境的创建
默认安装或数据库的创建状态下,DBMS_PROFILER包不会自动安装,请DBA用profload.sql脚本创建它.用一个权限较大的或一个单独的用户,创建存储统计信息的表。如果
用如SYS用户创建,则给其它用户授予DML
创建表的如下:
PLSQL_PROFILER_RUNS表:PL/SQL配置的运行细节.
PLSQL_PROFILER_UNITS表:运行中每一个库单元的信息.
PLSQL_PROFILER_DATA表:
PLSQL_PROFILER_RUNNUMBER序列提供了RUNID
运行和解释配置数据
ORACLE提供了三个表来统计,填充RUNID。有许多第三方的工具可以提供自定义的基于这些数据的报告,ORACLE提供profrep.sql脚本评估数据(在
-----------------------------------------------------------
Script: call_profiler.sql
-----------------------------------------------------------
set head off
set pages 0
select decode(dbms_profiler.start_profiler, '0', 'Profiler started', 'Profiler error')
from dual;
--< place your routine in the below block >--
declare
l_status varchar2(200);
begin
am_perf_chk(2, l_status);
dbms_output.put_line(l_status);
end;
/
select decode(dbms_profiler.stop_profiler, '0', 'Profiler stopped', 'Profiler error')
from dual;
select decode(dbms_profiler.flush_data, '0', 'Profiler flushed', 'Profiler error')
from dual;
select 'runid:' || plsql_profiler_runnumber.currval
from dual;
set head on
set pages 200
-----------------------------------------------------------
Script: evaluate_profiler_results.sql
-----------------------------------------------------------
undef runid
undef owner
undef name
set verify off
select s.line "Line", p.total_occur "Occur", p.total_time "Msec", s.text "Text"
from all_source s, (select u.unit_owner, u.unit_name, u.unit_type, d.line#,
d.total_occur, d.total_time/1000000 total_time
from plsql_profiler_data d, plsql_profiler_units u
where u.runid = &&runid
and u.runid = d.runid
and u.unit_number = d.unit_number) p
where s.owner = p.unit_owner (+)
and s.name = p.unit_name (+)
and s.type = p.unit_type (+)
and s.line = p.line# (+)
and s.name = upper('&&name')
and s.owner = upper('&&owner')
order by s.line;
select exec.cnt/total.cnt * 100 "Code% coverage"
from (select count(1) cnt
from plsql_profiler_data d, plsql_profiler_units u
where d.runid = &&runid
and u.runid = d.runid
and u.unit_number = d.unit_number
and u.unit_name = upper('&&name')
and u.unit_owner = upper('&&owner')) total,
(select count(1) cnt
from plsql_profiler_data d, plsql_profiler_units u
where d.runid = &&runid
and u.runid = d.runid
and u.unit_number = d.unit_number
and u.unit_name = upper('&&name')
and u.unit_owner = upper('&&owner')
and d.total_occur > 0) exec;
undef runid
undef owner
undef name
结论
DBMS_PROFILER是非常强大的工具,其一就是可以识别PL/SQL的性能问题.这个工具最好用在开发时期,