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的性能问题.这个工具最好用在开发时期,用来调整基于各种应用的情景的代码，它也能用很好的调整已在使用中的例程并且采取显而易见的时间去执行。总之，这个工具可以给每一行代码给予性能统计，它可以帮助我们评估和调整到一个出色的水平,当检查SQL语句的性能问题时，PL/SQL代码不应该忽略，相反应该调整到最佳的结果.

上一页  [1] [2] [3]