解释存储过程中的查询计划

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?

我有一个存储过程,包含一个单一的选择查询,用于根据对过程中的参数执行的一些次要数学插入到另一个表中。我可以通过某种方式引用过程来生成用于此查询的计划,还是必须复制并粘贴查询并为输入参数创建绑定变量?

3 个解决方案

#1

Use SQL Trace and TKPROF. For example, open SQL*Plus, and then issue the following code:-

使用SQL Trace和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 thread last week that helped me learn how to do this.

编辑:为了充分披露,我应该感谢所有那些在上周给我答案的人帮助我学习如何做到这一点。

#2

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对存储的proc没有意义,它可能包含多个查询/语句。

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.

当前答案为NO,您不能针对proc运行它,并且您必须针对各个语句本身运行它。你有变量和计算时很棘手,但事实就是如此。

#3

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子句的存在可以将优化器目标从第一行更改为所有行。

#1