Explain plan Oracle optimization tools installation and use __oracle

In an Oracle database, the need to optimize the SQL statement requires knowledge of its execution plan, which is tailored accordingly. 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, installation

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 for plan_table; --Establish synonyms

Sql> Grant all on plan_table to public; --Authorize all users

To create a role plustrace in the database, run the script plustrce.sql with the SYS user, which 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;

Of course, we can also generate an execution plan for all of the SQL statements running in a running session, which requires tracking the session, producing a trace file, and then formatting the file with a tkprof program, which is useful because it contains additional information, Individual resource conditions (such as CPU, DISK, elapsed, etc.) that are consumed by each phase of the SQL statement (such as Parse, execute, Fetch).

3. Generate execution plan with Dbms_system stored procedure
Because using the Dbms_system stored procedure, you can track the SQL statements emitted by another session and record the execution plan used, and provide other information that is useful for performance tuning. Because the way it is used is somewhat different from the above 2 ways, it is described separately in the appendix. This approach is one of the more useful ways to adjust SQL, in some cases it is not.

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

4, 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? The D>explain Plan Menu key allows you to view the execution plans 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.

5. Limit

The real only limitation of the

Explain is that users cannot interpret other users ' tables, views, indexes, or other types, that the user must be the owner of all the things being interpreted, and that explain returns an error if it is not the owner but only the SELECT permission.