Summary of ORACLE EXPLAIN plan

In ORACLE database, you need to SQLStatement to optimize the words need to know its implementation plan, and accordingly to adjust. There are several ways to get Oracle's execution plan, and here's a summary

1, the use of explain

    Oracle RDBMS executes every SQL statement, and must be evaluated by the Oracle Optimizer. So, understanding how the optimizer selects (searching) the path and how the index is used is a great help for optimizing SQL statements. Explain can be used to quickly and easily find out how the query data in a given SQL statement gets the search path (which we often call access path). So we choose the best query method to achieve maximum optimization effect.
1.1 , Ann   Pack                                                                                                                              

To use explain, you first have to execute the appropriate script to create a Explain_plan table.

The specific script executes as follows:

$ORACLE _home/rdbms/admin/utlxplan.sql (UNIX) The script generates a table this program creates a table named Plan_table.

1.2. Use
General usage Syntax:
Explain plan [SET statement_id [=] < string literal >] [into < table_name >]
for < sql_statement >

which
STATEMENT_ID: is a unique string that distinguishes the current execution plan from other execution plans that are stored in the same plan.
TABLE_NAME: is the plan table name, which is structured as shown before, and you can set this name arbitrarily.
Sql_statement: is the real SQL statement.

Like what:

Sql>explain plan set statement_id= ' t_test ' for select * from T_test;

Sql>

Explained

Execute the following statement to query the execution plan

Sql>select a.operation,options,object_name,object_type,id,parent_id
2 from Plan_table A
3 WHERE statement_id= ' t_test '
4 ORDER by Id;
You can also use this sentence to select * from table (dbms_xplan.display); You can list all the data in the plan_table.

2, the use of Autotrace method

2.1, installation

Run script with sys user Ultxplan.sql

The script to build this table is: (UNIX: $ORACLE _home/rdbms/admin, Windows:%oracle_home%/rdbms/admin) ultxplan.sql.

Sql> connect sys/sys@colm2 as SYSDBA;

Sql> @c:/oracle/ora92/rdbms/admin/utlxplan.sql;

sql> create public synonym plan_table a synonym for plan_table;--

Sql> Grant all in plan_table to public;--authorize all users

to be in Database to create a role plustrace, run the script plustrce.sql with the SYS user, and this script is created in the directory (UNIX: $ORACLE _home/sqlplus/admin, Windows :%oracle_home%/sqlplus/admin);

Sql> @c:/oracle/ora92/sqlplus/admin/plustrce.sql;

Then the character Plustrace is granted to the user who needs autotrace;

Sql>grant plustrace to public;

After the above steps are set, you can use Autotrace in Sql*plus.

2, 2 use

It's easy to use, just use a single command.

Sql>set autotrace on;

*autotrace function can only be used in Sql*plus

Some other ways to use it:

2.2.1, the total execution time of the statement in Sqlplus

Sql> set timing on;

2.2.2, show only execution plans-(results are executed at the same time)

Sql>set Autotrace on explain

Like what:

Sql> Select COUNT (*) from test;

COUNT (*)

-------------

4

Execution Plan

----------------------------

0 SELECT statement ptimitzer=choose (cost=3 card=1)

1 0 Sort (aggregate)

2 1 partition range (All)

3 2 Table access (full) ' T_test ' (cost=3 card=900)

2.2.3, display only statistical information---(results are executed simultaneously)

Sql>set Autotrace on statistics;

(Note: For SYS users, statistics will be 0)

2.2.4, showing execution plans, shielding execution results--(but the statement actually performs

Sql> set autotrace on traceonly;

(Note: With set autotrace on; Just don't show the results, show plans and statistics)

2.2.5, only show execution plan, mask all other results-(statement or execution)

Sql>set autotrace on traceonly explain;

It works well for explain plan, which simply looks at the big table.

2.2.6, closing

Sql>set autotrace off;

Summary: The automatic display function under Sqlplus, in view of the execution plan, its statements will be executed. Especially when executing update/delete statements, be aware that Oracle executes scripts to display execution plans at the same time, even with set autotrace on traceonly explain;

This is the time to recommend using explain plan for view or Pl/sql and other third-party tools

3, the third tool to see the implementation plan

If you use the Select query in Pl/sql to display the execution plan, you only need the SQL Windows window to enter the SQL statement you want to query, and then select the key F5 or the menu tools D D>explain The menu key allows you to view the execution plan for the statement in the Execution Plan window.
The execution plan information to execute the statement can be viewed in the Toad statement by selecting the Explain Plantab page below in the execution of the current SQL window.

4, limit explain real only limit is that the user cannot explain other user's table, view, index or other type, the user must be the owner of all interpreted things, if not the owner and only select permission, explain will return an error.