Explain Plan for Oracle SQL Statement Analysis

Explain plan is a good tool for analyzing SQL statements. It can even analyze statements without executing SQL statements. through analysis, we can know how Oracle connects to the table, how to scan the table (index scan or full table scan), and the index name used.

You need to interpret the analysis results in the order from inside to outside. the results of the explain plan Analysis are arranged in indent format, and the most internal operations will be first interpreted. If the two operations are on the same layer, the operations with the minimum operation number will be executed first.

1. Install

Run the creation script as sys. The script is located at $ ORACLE_HOME/rdbms/admin/utlxplan. SQL.

The execution method is cp $ ORACLE_HOME/rdbms/admin/utlxplan. SQL/home/utlxplan. SQL.

Run @/home/utlxplan. SQL in the database.

After execution, a plan table is created in the database to store the analysis information.

2. Use

Syntax:

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: The real SQL statement to be analyzed

For example:

Explain plan set statement_id = 't_ test' for select * from T;

Analysis:

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;

Grant all on plan_table to public; # authorize all users

To use autotrace in SQL * plus. Perform the following operations:

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;

The method is the same as ultxplain. SQL.

Authorization: grant the plustrace permission to the user who needs autotrace

Grant plustruce to public;

Start and close autotrace:

Set autotrace on/off;

Show only execution plans

Set autotrace on explain;

Show statistics only:

Set autotrace on statistics;

Display the execution plan and block the execution results;

Set autotrace on traceonly;

Display only the execution plan and block the execution result:

Set autotrace on traceonly explain;