如何确定->提高 Oracle SP 的性能
我们有很多 SP，其中许多需要很长时间才能执行。这些 SP 相当长，通过 SP 找出哪个查询花费很长时间是很痛苦的。
找出哪个查询花费最多时间的最佳方法是什么？这样我们就可以专注于该查询，而不是花时间进行研究。
目前我正在使用 PL/SQL Developer 来执行 SP
We have a lot of SP's and many of them take forever to execute. These SP's are pretty long and its a pain to go through the SP to find out which query is taking long.
Whats the best way to find out which query is taking the most time? so that we can just concentrate on that query rather than spending time in research.
Currently I am using PL/SQL developer to execute the SP
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
解释计划很容易获得——它提供了对效率的洞察，这将转化为时间/资源。
如果您想真正深入了解存储过程，请查看使用 tkprof 运行跟踪。这是关于它的文章。
The explain plan is readily accessible - it provides insight into the efficiency, which will translate into time/resources.
If you want real insight into stored procedures, look at running a trace using
tkprof. Here's an article on it.如果您能够执行查询，那么我建议您熟悉 DBMS_PROFLIER 功能。 Oracle PL/SQL Profiler 是一个可以帮助提供有关 PL/SQL 代码运行时行为的统计信息的工具。该工具在执行时收集有关 PL/SQL 源代码的信息并将该信息存储在表中。执行完成后，您可以报告存储的分析器数据以确定：
执行的
每行代码的执行时间
执行至完成
在分析期间执行）
通过为 Profiler 运行汇总此信息，您可以确定哪些源代码行或 SQL 语句可以从调整中获益最多。
有关 DBMS_PROFILER 包的 Oracle 文档位于此处 10克。
Metalink Doc 243755.1 有一个脚本可以为您的分析运行生成漂亮的 HTML 输出。
PROFSUM.SQL 是另一个报告收集的数据的脚本，可使用
If you are able to execute the queries then I would suggest getting familiar with the DBMS_PROFLIER functionality. The Oracle PL/SQL Profiler is a tool that can help provide statistics about the run-time behavior of PL/SQL code. The tool gathers information about PL/SQL source code as it executes and stores that information in tables. Once the execution is complete you can report on the stored profiler data to determine:
executed
execution time of each line of code
execute to completion
executed during the analysis period)
One this information is summarized for a Profiler run you can determine which lines of source code or SQL statements can benefit the most from tuning.
Oracle documentation on the DBMS_PROFILER package is here for 10g.
Metalink Doc 243755.1 has a script to produce pretty HTML output for your profiling runs.
PROFSUM.SQL is another script that reports on the gathered data, it is available here.
我做了很多此类工作，我发现拥有一个好的日志记录工具是正确的选择。问题总是出在一个或多个 SQL DML 语句上——尤其是 UPDATES 和 DELETES。如果您创建一个简单的日志记录过程，该过程使用时间戳列写入表（使用 AUTONOMOUS_TRANSACTION），并将对此的调用包装在所有 SQL 周围，您将很快确定您的时间都花在了哪里。我开发了一个成熟而复杂的包，专门用于检测像这样的 PL/SQL 代码以及实时监控生产代码 - 如果您想要一份副本，请回复我。
可悲的是，大多数开发人员似乎认为 DBMS_OUTPUT 可以解决问题 - 可以，但不是很有效......或者很漂亮。
其他 Oracle 跟踪工具肯定也有帮助，但使用起来有点困难。
最后 - 使用企业管理器并分析一段时间内的快照。查看前 10 个 SQL 查询 - 如果您愿意，可以使用 SQL Advisor。
希望有帮助...
I do a lot of this kind of work and I find that having a good logging tool is the way to go. Invariably the problem will be an one or more SQL DML statements - especially UPDATES and DELETES. If you create a simple logging procedure that writes to a table (using AUTONOMOUS_TRANSACTION) with a timestamp column and wrap calls to this around all SQL you will very quickly identify where your time is going. I have developed a mature and sophisticated package specifically to instrument PL/SQL code like this and for real time monitoring of production code - get back to me if you would like a copy.
Sadly most developers seem to think the DBMS_OUTPUT will solve the problem - can do, but not very efficient ... or pretty.
Other Oracle tracing tools are definitely a help too but a little more difficult to use.
Finally - use Enterprise manager and analyze a snapshot across a period of time. Look at the top 10 SQL queries - and if you like use SQL advisor.
Hope that helps ...