oracle 如何在Oracle中查找存储过程的开销并对其进行优化
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14767842/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find the cost of a stored procedure in Oracle and optimize it
提问by ramesh
Can anybody let me know if there is any way to find out cost of a stored procedure in Oracle? If no direct way is there, I would like to know any substitutes.
The way I found the cost is doing an auto trace of all the queries used in the stored procedure and then estimate the proc cost according to the frequency of the queries execution.
In addition to that I would like suggestions to optimize my stored procedure especially the query given below.
Logic of the procedure:
Below is the dynamic sql query used as a cursor in my stored procedure. This cursor is opened and fetched inside a loop. I fetch the info and put them in a varray, count the data and then insert it to a table.
My objective is to find out the cost of the proc as well as optimize the sp.
有人可以让我知道是否有任何方法可以找出 Oracle 中存储过程的成本吗？如果没有直接的方法，我想知道任何替代品。
我发现成本的方法是对存储过程中使用的所有查询进行自动跟踪，然后根据查询执行的频率估计过程成本。
除此之外，我想建议优化我的存储过程，尤其是下面给出的查询。
过程逻辑：
下面是在我的存储过程中用作游标的动态 sql 查询。该游标在循环内打开和获取。我获取信息并将它们放入一个变量中，计算数据，然后将其插入到表中。
我的目标是找出 proc 的成本并优化 sp。
SELECT DISTINCT acct_no
FROM raw
WHERE 1=1
AND code = ''' || code ||
''' AND qty < 0
AND acct_no
IN (SELECT acct_no FROM ' || table_name || ' WHERE counter =
(SELECT MAX(counter) FROM ' || table_name || '))
回答by Vincent Malgrat
One of the best tool in analyzing SQL and PLSQL performance is the native SQL trace.
分析 SQL 和 PLSQL 性能的最佳工具之一是本机SQL 跟踪。
enable tracing in your session:
SQL> alter session set SQL_TRACE=TRUE; Session alteredRun your procedure
Exit your session
Navigate to your server
udumpdirectory and find your trace file (usually the latest)
在您的会话中启用跟踪：
SQL> alter session set SQL_TRACE=TRUE; Session altered运行你的程序
退出会话
导航到您的服务器
udump目录并找到您的跟踪文件（通常是最新的）
This will produce a file containing a list of all statements with lots of information, including the number of times each was executed, its query plan and statistics. This is more detailed and precise than manually running the plan for each select.
这将生成一个文件，其中包含所有语句的列表，其中包含大量信息，包括每个语句的执行次数、查询计划和统计信息。这比为每个选择手动运行计划更加详细和精确。
If you want to optimize performance on a procedure, you would usually sort the trace file by the time taken to execute (with sort=EXEELA) or fetch SQL and try to optimize the queries that make the most work.
如果要优化过程的性能，通常会按执行（使用sort=EXEELA）或获取 SQL所需的时间对跟踪文件进行排序，并尝试优化最能发挥作用的查询。
You can also make the trace file log wait events by using the following commandat step 1:
您还可以在步骤 1 中使用以下命令使跟踪文件记录等待事件：
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
回答by APC
The way to find out the cost (in execution of time) for a stored procedure is to employ a profiler. 11g introduced the Hierarchical Profiler which is highly neat. Find out more.
找出存储过程的成本（执行时间）的方法是使用分析器。11g 引入了高度整洁的分层分析器。 了解更多。
Prior to 11g there was only the DBMS_PROFILER, which is good enough, especially if your stored procedure doesn't use objects in other schemas. Find out more.
在 11g 之前，只有 DBMS_PROFILER，这已经足够了，尤其是当您的存储过程不使用其他模式中的对象时。 了解更多。
Trace is good for identifying poorly performing SQL. Profilers are good for identifying the cost of the PL/SQL elements of a stored proc. If your proc has some expensive computation elements which don't read or write to tables then that won't show up in SQL trace.
跟踪有助于识别性能不佳的 SQL。探查器非常适合识别存储过程的 PL/SQL 元素的成本。如果您的 proc 有一些不读取或写入表的昂贵计算元素，那么它不会显示在 SQL 跟踪中。
Likewise if you have a well-tuned SQL statement but use it badly ia profiler run is likely to be more help than trace. An example of what I mean is repeatedly executing the same SELECT statement inside a Cursor loop: I know that's not quite what you're doing but it's close enough.
同样，如果您有一个经过良好调整的 SQL 语句，但使用不当，则分析器运行可能比跟踪更有帮助。我的意思的一个例子是在 Cursor 循环内重复执行相同的 SELECT 语句：我知道这不是你正在做的，但它已经足够接近了。
Apparently the hierarchical profiler DBMS_HPROF is installed by default in 11g but a DBA has to grant some privileges to developers who want to use it. Find out more.
显然，分层分析器 DBMS_HPROF 是默认安装在 11g 中的，但是 DBA 必须向想要使用它的开发人员授予一些权限。 了解更多。
To install the DBMS_PROFILER in 10g (or earlier) a DBA has to run this script:
要在 10g（或更早版本）中安装 DBMS_PROFILER，DBA 必须运行以下脚本：
$ORACLE_HOME/rdbms/admin/proftab.sql
Be sure to get the reporting infrastructure as well:
一定要获得报告基础设施：
$ORACLE_HOME/plsql/demo/profsum.sql
(The name or location of this script may vary in earlier versions).
（此脚本的名称或位置在早期版本中可能有所不同）。
回答by haki
The easy way is to execute the procedure and then query v$sql. if you want a little tip to make your life easier (not just for packages) add a blank comment to the query inside the procedure, something like
简单的方法是执行该过程，然后查询 v$sql。如果你想要一个小技巧让你的生活更轻松（不仅仅是包）在过程中的查询中添加一个空白注释，比如
select /* BIG DADDY */ * from dual;
and then query v$sql as follows
然后查询 v$sql 如下
select * from v$sql where sql_text like '%BIG DADDY%';
the best way is definitely the way @Vincent Malgratsuggested.
最好的方式绝对是@Vincent Malgrat建议的方式。
good luck.
祝你好运。
