如何生成整个存储过程的解释计划 [英] how to generate explain plan for entire stored procedure

问题描述

我通常在sqlplus中使用以下内容生成解释计划:

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?

有没有一种方法可以为整个存储过程生成解释计划? SP没有输入/输出参数.

Is there a way to generate explain plan for the entire stored procedure? The SP has no input/output parameters.

推荐答案

您生成的内容正确地称为执行计划". 解释计划"是用于生成和查看执行计划的命令，与示例中的AUTOTRACE TRACEONLY一样.

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.

根据定义，执行计划是针对单个SQL语句的. PL/SQL块没有执行计划.如果它包含一个或多个SQL语句，则每个语句都有一个执行计划.

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.

一种选择是从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.

另一个选择是激活SQL跟踪，然后运行该过程.这将在服务器上生成一个跟踪文件，其中包含会话中执行的所有语句的执行计划.跟踪的格式相当原始，因此通常最容易使用Oracle的TKPROF工具对其进行格式化.还有各种第三方工具也可以处理这些跟踪文件.

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.

这篇关于如何生成整个存储过程的解释计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！