如何为整个存储过程生成解释计划
我通常在 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
但是如果我想为存储过程生成解释计划怎么办？
有没有办法为整个存储过程生成解释计划？ SP 没有输入/输出参数。
I usually generate explain plans using the following in 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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您生成的内容正确地称为“执行计划”。 “解释计划”是用于生成和查看执行计划的命令，就像示例中的 AUTOTRACE TRACEONLY 所做的那样。
根据定义，执行计划是针对单个 SQL 语句的。 PL/SQL 块没有执行计划。如果它包含一个或多个 SQL 语句，那么每个语句都会有一个执行计划。
一种选择是手动从 PL/SQL 代码中提取 SQL 语句并使用您已经展示的过程。
另一种选择是主动 SQL 跟踪，然后运行该过程。这将在服务器上生成一个跟踪文件，其中包含会话中执行的所有语句的执行计划。跟踪是相当原始的形式，因此通常最容易使用 Oracle 的 TKPROF 工具对其进行格式化；还有各种第三方工具也可以处理这些跟踪文件。
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.
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.
One option is to manually extract the SQL statements from the PL/SQL code and use the process you've already shown.
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.