Oracle SQL Performance Optimization series (7)

24.Use EXPLAIN PLANAnalyze SQLStatement

 

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.

The nested loop is a few operations that do not follow the above rules. The correct execution path is to check the operations that provide data to the nested loop. the operation with the smallest operation number will be processed first.

 

Press:

 

Through practice,I still feel that SQLPLUS is usedSet trace inEasy to use.

Example:

 

SQL> list

1 SELECT *

2 FROM dept, emp

 3 * WHERE emp. deptno = dept. deptno

SQL> set autotrace traceonly/* traceonlyThe execution result may not be displayed */

SQL>/

14 rows selected.

Execution Plan

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

0 select statement Optimizer = CHOOSE

1 0 NESTED LOOPS

2 1 table access (FULL) OF 'emp'

3 1 table access (by index rowid) OF 'dept'

4 3 INDEX (unique scan) OF 'pk _ DEPT '(UNIQUE)

 

Statistics

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

0 recursive cballs

2 db block gets

30 consistent gets

0 physical reads

0 redo size

2598 bytes sent via SQL * Net to client

503 bytes encoded ed via SQL * Net from client

2 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

 

Through the above analysis,The actual execution steps are as follows:

1.Table access (FULL) OF 'emp'

2.INDEX (unique scan) OF 'pk _ DEPT '(UNIQUE)

3.Table access (by index rowid) OF 'dept'

4.Nested loops (JOINING 1 AND 3)