如何轻松分析Oracle软件包的执行是否存在性能问题? [英] How can I easily analyze an Oracle package's execution for performance issues?

问题描述

我在11g R2 DB中有一个pl/sql程序包，该程序包中包含许多相关的过程&功能.我执行一个顶层函数，它完成了很多工作.当前正在处理<每秒10行.我想提高性能，但是我不确定要关注哪个例程.

I have a pl/sql package in an 11g R2 DB that has a fair number of related procedures & functions. I execute a top level function and it does a whole lot of stuff. It is currently processing < 10 rows per second. I would like to improve the performance, but I am not sure which of the routines to focus on.

对于Oracle的"PL/SQL层次分析器(DBMS_HPROF)"来说，这似乎是一项好工作一点点击界面.我找不到这个.

This seems like a good job for Oracle's "PL/SQL hierarchical profiler (DBMS_HPROF)" Being the lazy person that I am I was hoping that either SQLDeveloper v3.2.20.09 or Enterprise Manager would be able do what I want with a point & click interface. I cannot find this.

是否有一种简便"的方法来分析过程/程序包中的实际PL/SQL代码?

Is there an "easy" way to analyze the actual PL/SQL code in a procedure/package?

我使用企业管理器的顶部活动"部分优化了程序包中的查询，查看了花费很长时间的所有查询.现在，我只显示一个"Execute PL/SQL"，而我至少需要将其分解为以下步骤:被调用的函数，以及它们占用的时间百分比.

I have optimized the queries in the package using the "Top Activity" section of Enterprise Manager, looking at all of the queries that were taking a long time. Now all I have is a single "Execute PL/SQL" showing up, and I need to break that down into at least the procedures & functions that are called, and what percentage of the time they are taking.

推荐答案

The PL/SQL Hierarchical Profiler, documented here, is actually very easy to run, assuming that you have the necessary privileges.

基本上，您只需要运行这样的PL/SQL块:

Basically you just need to run a PL/SQL block like this:

begin
   dbms_hprof.start_profiling('PLSHPROF_DIR', 'test.trc');
   your_top_level_procedure;
   dbms_hprof.stop_profiling;
end;

plshprof实用程序将从原始探查器输出文件(在上面的示例中为test.trc)生成HTML报告.

The plshprof utility will generate HTML reports from the raw profiler output file (test.trc in the example above).

这篇关于如何轻松分析Oracle软件包的执行是否存在性能问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！