ORACLE SQL Performance Optimization Series (vii)

oracle| Performance | optimization
24. Analyze SQL statements with explain plan



EXPLAIN Plan is a good tool for analyzing SQL statements, and it can even parse statements without executing SQL. By analyzing, we can see how Oracle joins tables, how they scan tables (index scans or full table scans), and the index names that are used.

You need to read the results of the analysis from the inside out, from top to bottom. The results of the EXPLAIN plan analysis are arranged in indented format, and the most internal operations will be interpreted first, and if two operations are in the same layer, the minimum operator number will be executed first.

The NESTED loop is a handful of operations that are not handled in accordance with the above rules, and the correct execution path is to check the operations that provide data to the NESTED loop, where the smallest operator number will be processed first.



Translator by:



In practice, it is more convenient to use the Set TRACE function in Sqlplus.

Example:



Sql> List

1 SELECT *

2 from Dept, EMP

3* WHERE Emp.deptno = Dept.deptno

Sql> set Autotrace traceonly/*traceonly can not display execution results * *

Sql>/

Rows selected.

Execution Plan

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

0 SELECT STATEMENT Optimizer=choose

1 0 NESTED LOOPS

2 1 TABLE ACCESS (full) ' EMP '

3 1 TABLE ACCESS (by INDEX ROWID) of ' DEPT '

4 3 INDEX (unique SCAN) of ' pk_dept ' (unique)



Statistics

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

0 Recursive calls

2 db block gets

Consistent gets

0 physical Reads

0 Redo Size

2598 Bytes sent via sql*net to client

503 Bytes received via sql*net from client

2 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

Rows processed



Through the above analysis, you can draw the actual implementation steps are:

1. TABLE ACCESS (Full) ' EMP '

2. INDEX (unique SCAN) of ' pk_dept ' (unique)

3. TABLE ACCESS (by INDEX ROWID) of ' DEPT '

4. NESTED LOOPS (joining 1 and 3)





Note: Many third-party tools such as Toad and Oracle's own tools such as OMS SQL Analyze provide extremely convenient explain plan tools. Friends who may like graphical interfaces can choose them.



adjourned