oracle 如何为整个存储过程生成解释计划
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4510491/
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 generate explain plan for entire stored procedure
提问by learn_plsql
I usually generate explain plans using the following in sqlplus:
我通常在 sqlplus 中使用以下内容生成解释计划：
SET AUTOTRACE ON
SET TIMING ON
SET TRIMSPOOL ON
SET LINES 200
SPOOL filename.txt
SET AUTOTRACE TRACEONLY;
{query goes here}
SPOOL OFF
SET AUTOTRACE OFF
But what If I want to generate explain plan for a stored procedure?
但是如果我想为存储过程生成解释计划怎么办？
Is there a way to generate explain plan for the entire stored procedure? The SP has no input/output parameters.
有没有办法为整个存储过程生成解释计划？SP 没有输入/输出参数。
回答by Dave Costa
What you are generating is correctly called an "execution plan". "Explain plan" is a command used to generate and view an execution plan, much as AUTOTRACE TRACEONLY does in your example.
您生成的内容正确地称为“执行计划”。“解释计划”是用于生成和查看执行计划的命令，与示例中的 AUTOTRACE TRACEONLY 非常相似。
By definition, an execution plan is for a single SQL statement. A PL/SQL block does not have an execution plan. If it contains one or more SQL statements, then each of those will have an execution plan.
根据定义，执行计划是针对单个 SQL 语句的。PL/SQL 块没有执行计划。如果它包含一个或多个 SQL 语句，那么每个语句都会有一个执行计划。
One option is to manually extract the SQL statements from the PL/SQL code and use the process you've already shown.
一种选择是从 PL/SQL 代码中手动提取 SQL 语句并使用您已经展示的过程。
Another option is to active SQL tracing then run the procedure. This will produce a trace file on the server that contains the execution plans for all statements executed in the session. The trace is in fairly raw form so it is generally easiest to format it using Oracle's TKPROF tool; there are also various third-party tools that process these trace files as well.
另一种选择是激活 SQL 跟踪，然后运行该过程。这将在服务器上生成一个跟踪文件，其中包含会话中执行的所有语句的执行计划。跟踪是相当原始的形式，因此通常最容易使用 Oracle 的 TKPROF 工具对其进行格式化；还有各种第三方工具也可以处理这些跟踪文件。
回答by Panky031
Hi I have done like below for the stored procedure:
SET AUTOTRACE ON
SET TIMING ON
SET TRIMSPOOL ON
SET LINES 200
SPOOL filename.txt
SET AUTOTRACE TRACEONLY;
@your stored procedure path
SPOOL OFF
SET AUTOTRACE OFF
And got the below statistics:
Statistics
-----------------------------------------------------------
6 CPU used by this session
8 CPU used when call started
53 DB time
6 Requests to/from client
188416 cell physical IO interconnect bytes
237 consistent gets
112 consistent gets - examination
237 consistent gets from cache
110 consistent gets from cache (fastpath)
2043 db block gets
1 db block gets direct
2042 db block gets from cache
567 db block gets from cache (fastpath)
27 enqueue releases
27 enqueue requests
4 messages sent
31 non-idle wait count
19 non-idle wait time
44 opened cursors cumulative
2 opened cursors current
22 physical read total IO requests
180224 physical read total bytes
1 physical write total IO requests
8192 physical write total bytes
1 pinned cursors current
461 recursive calls
4 recursive cpu usage
2280 session logical reads
1572864 session pga memory
19 user I/O wait time
9 user calls
1 user commits
No Errors.
Autotrace Disabled
