Practical application skills of Oracle EXPLAIN PLAN

The following articles mainly summarize the practical application skills of the Oracle explain plan. If you need to optimize the SQL statements in the Oracle database, we need to know the relevant plans to be executed, so as to make targeted adjustments. There are several methods to obtain the Oracle execution plan, which will be summarized below.

1. Use of EXPLAIN

Every SQL statement executed by the OracleRDBMS must be evaluated by the Oracle optimizer. Therefore, it is helpful to know how the optimizer selects (Search) paths and how indexes are used. Explain can be used to quickly and conveniently find out how the query data in a given SQL statement is obtained, that is, the search Path (which is usually called the Access Path ). So that we can select the optimal query method to achieve the maximum optimization effect.

1.1 installation

To use EXPLAIN, first execute the corresponding script to create the Explain_plan table.

The specific script execution is 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 Syntax:

 
 

  
  
Oracle explain PLAN [ SET STATEMENT_ID [=] < string literal > ] [ INTO < table_name > ]  
  
  
FOR < sql_statement > 
 
 

Where:

STATEMENT_ID: A unique string that distinguishes the current execution PLAN from other execution plans stored in the same PLAN.

TABLE_NAME: the name of the plan table. Its structure is shown in the preceding figure. You can set this name as needed.

SQL _STATEMENT: a real SQL statement.

For example:

 
 

  
  
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

3 WHERE STATEMENT_ID = 't_ Test'

4 order by Id;

You can also use this sentence

 
 

  
  
select * from table(dbms_xplan.display); 
 
 

You can list all the data in PLAN_TABLE.

2. Usage of AUTOTRACE

2.1 installation

Run the script ultxplan. SQL using sys.

The script for creating 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;  
 
 

Create Synonym

 
 

  
  
SQL> grant all on plan_table to public; 
 
 

Authorize all users

Create a role plustrace in the database and run the script plustrce with the sys user. SQL to create this role. The script is in the directory UNIX: $ ORACLE_HOME/sqlplus/admin, Windows: % ORACLE_HOME % \ sqlplus \ admin;

 
 

  
  
SQL> @C:\oracle\ora92\sqlplus\admin\plustrce.sql; 
 
 

Then, grant the role plustrace to the user who needs autotrace;

 
 

  
  
SQL>grant plustrace to public; 
 
 

After setting the preceding steps, you can use autotrace in SQL * plus.

2. Use

It is very convenient to use. You only need to use one command.

 
 

  
  
SQL>SET AUTOTRACE ON； 
 
 

* The autotrace function can only be used in SQL * PLUS.

Other usage methods:

2.2.1 obtain the total statement execution time in SQLPLUS

 
 

  
  
SQL> set timing on; 
 
 

2.2.2. display only the execution plan-the execution statement will be executed at the same time to get the result)

SQL> set autotrace on explain

For example:

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) of 't_ test' (cost = 3 card = 900)

2.2.3. Only statistics are displayed. The result is displayed when the statement is executed)

 
 

  
  
SQL>set autotrace on statistics; 
 
 

Note: For SYS users, the statistical information will be 0)

2.2.4 display the execution plan and block the execution results-but the statement is actually executed

 
 

  
  
SQL> set autotrace on traceonly; 
 
 

Note: The same as set autotrace on; only the results are not displayed, and the plans and statistics are displayed)

2.2.5 only display the execution plan and block all other results -- (the statement is still executed)

 
 

  
  
SQL>set autotrace on traceonly explain; 
 
 

It is very useful for Oracle Explain plans that only view large tables.

2.2.6. Disable

 
 

  
  
SQL>set autotrace off; 
 
 

Conclusion: The automatic display function of SQLPLUS still executes the statements in the execution plan. Especially when executing the UPDATE/DELETE statement, please note that ORACLE first executes the script and displays the execution plan, even if you use

 
 

  
  
set autotrace on traceonly explain; 
 
 

In this case, we recommend that you use the explain plan for or third-party tools such as PL/SQL.

3. view the execution plan using the third tool

If you select to query the statement in PL/SQL to display the execution plan, you only need to enter the SQL statement to query in the SQL WINDOWS window, and then select press F5 or in the menu TOOLS? D? D> click the Explain Plan menu to view the execution Plan of the statement in the execution Plan window.

In the TOAD statement, select the Oracle Explain PlanTAB at the bottom of the current SQL window to view the execution plan of the statement to be executed.

4. Restrictions

The only limit of explain is that users cannot interpret tables, views, indexes, or other types of other users. Users must be the owner of all interpreted items, if the select permission is not the owner and only the select permission is required, an error is returned in "explain.