如何分析Oracle存储过程的端到端性能 [英] How to profile end to end performance of Oracle stored procedure

问题描述

我想知道从外部进程访问数据库并说执行该操作到数据库返回该进程并说继续"的时间，我的存储过程要花多长时间.

I want to know how long my stored procedure is taking to execute from the time an external process hits the database and says execute this to the time the database returns back to the process and says here ya go.

是否有一种简单易行的方法来完成这项非常基本的任务?

Is there a simple easy way to do this very basic task?

当然，对于存储过程执行期间发生的事情以及执行每个任务(插入，plsql字符串操作等)花费了多少存储过程时间的报告，这是一个好处，但是我真的只是想简单地做些事情且易于使用. (免费)

Of course a report of what is happening during the stored procedure execution and how much of the stored procedure's time is spent doing each task (inserts, plsql string manipulation etc.) would be a bonus, but I really just want something simple and easy to use. (And free)

推荐答案

如果您使用的是Oracle 11g，则应查看层次分析器DBMS_HPROF.该工具将为您提供PL/SQL程序中所有节点的经过时间.顾名思义，它对于调查调用程序的程序特别有用.它还标识了不同于函数调用的SQL语句的计时. 了解详情.

If you're using Oracle 11g you should have a look at the hierarchical profiler, DBMS_HPROF. This is a tool which will give you elapsed timings for all the nodes in a PL/SQL program. As the name suggests, it is especially useful for investigating programs which call programs which call programs. It also identifies timing for SQL statements distinct from function calls. Find out more.

它是标准11g部署的一部分，因此是免费的.好吧，一旦您支付了许可证费用，它就是免费的:)

It is part of the standard 11g deployment, and hence is free. Well, once you've paid for your license it's free :)

默认情况下，不授予DBMS_HPROF软件包的默认权限.因此，您需要获得具有SYSDBA访问权限的人才能看到您的权限.分析还需要创建一些表.这些东西都不应该成为问题，但我知道有时是这样.

By default rights on the DBMS_HPROF package are not granted to any one. So, you'll need to get somebody with SYSDBA access to see you right. The analysis also requires the creation of some tables. Neither of these things should be an issue but I know sometimes they are.

A，您使用的是较早版本.因此，您只需使用DBMS_PROFILER(自9i开始就存在).这基本上适用于单个程序.它的主要缺点是我们只能在拥有CREATE特权的程序上使用它(即，除非我们具有CREATE ANY PROCEDURE特权(通常意味着成为DBA)，否则仅是我们架构中的程序).同样，为了分析嵌入式SQL语句，我们需要使用DBMS_TRACE包. 了解更多信息.

Alas, you're on an earlier version. So that leaves you with just DBMS_PROFILER, which has been around since 9i. This basically works well for a single program. Its main drawback is that we can only use it on programs for which we have the CREATE privilege (i.e. just programs in our schema unless we have the CREATE ANY PROCEDURE privilge, which usually means being a DBA). Also, for profiling embedded SQL statements we need to use the DBMS_TRACE package. Find out more.

这篇关于如何分析Oracle存储过程的端到端性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！