Meaning of the output content of Oracle AutoTrace Optimization

SQLPLUS AutoTrace is a simple and convenient tool for analyzing SQL Execution plans and execution efficiency. In most cases, it is also a very useful tool. Use AutoT

SQLPLUS AutoTrace is a simple and convenient tool for analyzing SQL Execution plans and execution efficiency. In most cases, it is also a very useful tool. Use AutoT

SQLPLUS AutoTrace is a simple and convenient tool for analyzing SQL Execution plans and execution efficiency. In most cases, it is also a very useful tool. The SQL Execution Plan and execution status provided by the AutoTrace tool can provide the basis for optimization and the obvious comparison of the optimization results.

Enter the AUTOTRACE command in SQLPLUS and the SQL statement to be optimized to obtain the SQL Execution Plan and execution status information.

SQL> set timing on // enable time display
SQL> set autot traceonly // only displays trace results, not SQL Execution results
SQL> select * from ac01 where aac001 = '000000 ′;

Used time: 00: 00: 00.62

Execution Plan
--------------------
0 select statement Optimizer = CHOOSE
1 0 table access (by index rowid) OF 'ac01 ′
2 1 INDEX (unique scan) OF 'pk _ ac01' (UNIQUE)
Statistics
--------------------
0 recursive cballs
0 db block gets
3 consistent gets
2 physical reads
0 redo size
1875 bytes sent via SQL * Net to client
424 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Usage: set autot [RACE] {OFF | ON | TRACE [ONLY]} [EXP [LAIN] [STAT [ISTICS]
Example:
Set autot [RACE] OFF stop AutoTrace
Set autot [RACE] ON enables AutoTrace to display AUTOTRACE information and SQL Execution results
Set autot [RACE] TRACEONLY enables AutoTrace and only displays AUTOTRACE Information
Set autot [RACE] on explain enable AutoTrace. Only the autotrace explain information is displayed.
Set autot [RACE] on statistics to enable AutoTrace. Only STATISTICS information of AUTOTRACE is displayed.

Iii. Analysis of execution plans
Execution Plan
--------------------
0 select statement Optimizer = CHOOSE
1 0 table access (by index rowid) OF 'ac01 ′
2 1 INDEX (unique scan) OF 'pk _ ac01' (UNIQUE)
--------------------

This is the SQL select * from ac01 where aac001 = '000000'; Execution Plan.
The execution process is as follows:
1) INDEX (unique scan) OF 'pk _ ac01' (UNIQUE)
2) table access (by index rowid) OF 'ac01 ′
An execution plan is a tree structure. The execution of the plan starts from the leaf node until the root node. Therefore, on different layers, the lower the layer is executed first (larger in the first column of numbers); on different layers, the more left, the more executed (smaller in the second column ).
By analyzing this implementation plan, you can know the following points:
A) This is a SELECT statement.
B) The current optimizer mode used by the database system is CHOOSE.
C) during execution, find the ROWID of the corresponding record through the unique index PK_AC01 on the AC01 table, and then access the AC01 table directly through the indexed ROWID to find the corresponding record.
This is a relatively simple SQL statement, so the execution plan is relatively simple and does not involve too many connections and indexes.