Oracle get execution plan

I. background

When we question the slow execution of an SQL statement, and then try to improve the possibility. We often need to check the execution plan corresponding to this SQL statement. This article describes how to obtain the execution plan in Oracle.

Ii. Obtaining Method

Oracle provides four methods for obtaining execution plans:

1. Execute the SQL statement explain plan and then query the result output table (common method)

2. query a dynamic performance View

3. query the automatic workload database or statspack table

4. Start the tracking function that provides the execution plan (for example, start SQL _trace)

Iii. SQL statement EXPLAIN PLAN (method 1)

This command uses an SQL statement as the input to obtain the execution plan of this SQL statement and output the result to the schedule. Syntax:

Explain plan for select count (*) from user_objects/* obtain the execution plan through parsing */

Select * from table (dbms_xplan.display)/* query the schedule to display the execution plan */

If you are viewing the execution plan of the variable-bound SQL statement, the SQL statement must be in the form of a variable, for example:

Explain plan for select * from emp where empno =: p_value.

However, there is a problem because the command explain plan cannot use the Bind Variable for a preview, that is, the explain plan shows the execution plan, which may not be the real execution plan. That is to say, if a variable is bound, the execution plan generated by the explain plan is unreliable (of course, this is absolutely reliable and actually reliable)

1 2 Next Page