Oracle uses explain plan to analyze SQL statement instance Parsing

OracleUseEXPLAIN PLANThe Method for Analyzing SQL statements is what we will introduce in this article. We know that the 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. The most internal operation is interpreted first. If the two operations are on the same layer, the operation 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.

Through practice, it is easier 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>/
  
  
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, we can see that the actual execution steps are:

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)

Note:Currently, many third-party tools, such as TOAD and ORACLE tools, such as oms SQL Analyze, provide extremely convenient EXPLAIN PLAN tools. Users who like the graphic interface may choose them.

This article describes how to use explain plan to analyze SQL statements in Oracle!