oracle 解释存储过程中的查询计划
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/122736/
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
Explain Plan for Query in a Stored Procedure
提问by Mark Roddy
I have a stored procedure that consists of a single select query used to insert into another table based on some minor math that is done to the arguments in the procedure. Can I generate the plan used for this query by referencing the procedure somehow, or do I have to copy and paste the query and create bind variables for the input parameters?
我有一个存储过程，它包含一个单一的选择查询，用于根据对过程中的参数进行的一些次要数学运算插入到另一个表中。我可以通过以某种方式引用过程来生成用于此查询的计划，还是必须复制和粘贴查询并为输入参数创建绑定变量？
采纳答案by Mike McAllister
Use SQL Trace and TKPROF. For example, open SQL*Plus, and then issue the following code:-
使用SQL 跟踪和 TKPROF。例如，打开 SQL*Plus，然后发出以下代码：-
alter session set tracefile_identifier = 'something-unique'
alter session set sql_trace = true;
alter session set events '10046 trace name context forever, level 8';
select 'right-before-my-sp' from dual;
exec your_stored_procedure
alter session set sql_trace = false;
Once this has been done, go look in your database's UDUMP directory for a TRC file with "something-unique" in the filename. Format this TRC file with TKPROF, and then open the formatted file and search for the string "right-before-my-sp". The SQL command issued by your stored procedure should be shortly after this section, and immediately under that SQL statement will be the plan for the SQL statement.
完成此操作后，请在数据库的 UDUMP 目录中查找文件名中带有“something-unique”的 TRC 文件。用 TKPROF 格式化这个 TRC 文件，然后打开格式化的文件并搜索字符串“right-before-my-sp”。您的存储过程发出的 SQL 命令应该在此部分之后不久，并且紧接在该 SQL 语句下的是 SQL 语句的计划。
Edit:For the purposes of full disclosure, I should thank all those who gave me answers on this threadlast week that helped me learn how to do this.
编辑：为了充分披露，我应该感谢上周在这个线程上给我答案的所有那些帮助我学习如何做到这一点的人。
回答by Jasmine
From what I understand, this was done on purpose. The idea is that individual queries within the procedure are considered separately by the optimizer, so EXPLAIN PLAN doesn't make sense against a stored proc, which could contain multiple queries/statements.
据我了解，这是故意的。这个想法是优化器单独考虑过程中的各个查询，因此 EXPLAIN PLAN 对存储过程没有意义，它可能包含多个查询/语句。
The current answer is NO, you can't run it against a proc, and you must run it against the individual statements themselves. Tricky when you have variables and calculations, but that's the way it is.
当前的答案是否定的，您不能针对 proc 运行它，而必须针对单个语句本身运行它。当您有变量和计算时会很棘手，但这就是它的方式。
回答by David Aldridge
Many tools, such as Toad or SQL Developer, will prompt you for the bind variable values when you execute an explain plan. You would have to do so manually in SQL*Plus or other tools.
许多工具，例如 Toad 或 SQL Developer，会在您执行解释计划时提示您输入绑定变量值。您必须在 SQL*Plus 或其他工具中手动执行此操作。
You could also turn on SQL tracing and execute the stored procedure, then retrieve the explain plan from the trace file.
您还可以打开 SQL 跟踪并执行存储过程，然后从跟踪文件中检索解释计划。
Be careful that you do not just retrieve the explain plan for the SELECT statement. The presence of the INSERT clause can change the optimizer goal from first rows to all rows.
请注意不要只检索 SELECT 语句的解释计划。INSERT 子句的存在可以将优化器目标从第一行更改为所有行。
