Get execution plan EXPLANPLAN

Generally, there are four ways to obtain an execution plan: 1. Execute the explain Plan and query the result output table. 2. query the dynamic performance view, which displays the execution plan cached in the database cache (sometimes the result is not found because the execution plan is no longer in the database cache ). 3. query AWR or Statspack tables. 4. Start the execution plan tracking function (setautotracetra)

Generally, there are four ways to obtain the execution plan: 1. Execute the explain plan and query the result output table. 2. query the dynamic performance view, which displays the execution plan cached in the database cache (sometimes the result is not found because the execution plan is no longer in the database cache ). 3. query AWR or Statspack tables. 4. Start the set autotrace tra

Generally, there are four ways to obtain the execution plan: 1. Execute the explain plan and query the result output table. 2. query the dynamic performance view, which displays the execution plan cached in the database cache (sometimes the result is not found because the execution plan is no longer in the database cache ). 3. query AWR or Statspack tables. 4. Start the tracing function (set autotrace trace explain) that provides the execution plan ). The fourth method is easy to use ~ This article describes the first method.

First, the principle of explainplan is to analyze an SQL statement and store the execution plan and related information of the statement into the plan table. You can view the explain plan syntax in the online documentation,

Here, the string is used to differentiate multiple execution plans in a schedule. The table after into is the "Schedule ". The general method of use is to explain plan for "SQL text"; then select * fromtable (dbms_xplan.display); sometimes this happens, explain plan for insert into test select * from test; at this time, no transaction changes, because the explain Plan is a DML statement, it will not be implicitly committed to the current transaction, just insert several records to the schedule.

We will save the execution plan and related information to the schedule. The default schedule is a table under sys, but there is a common synonym for plan_table. When you want to use a private schedule, you can run utlxplan. SQL script.

When a variable is bound to an SQL statement, bind the variable to explain for instead of using several constants to bind the variable for analysis. There are also two problems with variable binding in explain. First, the bound variable is of the varchar2 type by default. Therefore, an explicit type conversion is available in the execution plan access. Second, bind peeking cannot be used.