Oracle AutoTRACE Usage Analysis + instance

* Environment: windowsXP + Oracle10gR2
* AutoTRACE is a simple and convenient tool for analyzing SQL Execution plans and execution efficiency.
*/

AUTOTRACE is an SQL * Plus function that automatically generates an execution plan for an SQL statement and provides statistics related to the processing of the statement.

SQL * Plus AUTOTRACE can be used to replace SQL Trace. The advantage of AUTOTRACE is that you do not have to set the Tracing file format, and it will automatically display the execution plan for SQL statements. However, AUTOTRACE analyzes and executes statements, while explain plan only analyzes statements.

Using AUTOTRACE does not generate trace files.

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.

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.

Result explanation
Physical reads physical read-number of data blocks read from the hard disk during SQL Execution
Redo size redo count-the size of the redo log generated during SQL Execution
Bytes set via SQL * net to the number of bytes that the client sends to the client through SQL * net
Bytes received ed via SQL * net from client the number of bytes received by the client through SQL * net
Sorting of sorts (memory) in memory
Sorts (disks) cannot be sorted in memory. Hard Disks are required for help.
Number of records of rows processed results

Considerations for AutoTrace Optimization

1. You can set timing to get the time used to execute the SQL statement. However, you cannot set timing as the only measurement of SQL Execution efficiency. This time will include some time consumption for AUTOTRACE, so this time is not just the time for SQL Execution. This time may be slightly different from the SQL Execution time, especially when SQL is relatively simple.

2. Determine the SQL efficiency by executing the number of logical reads in the SQL Execution status
Logical READ = (db block gets + consistent gets)
Summary

AutoTrace is the most basic tool in ORACLE optimization tools. Although it has limited functions, it is sufficient to meet the needs of our daily work.

Run the $ ORACLE_HOME \ RDBMS \ ADMIN \ utlxplan. SQL script in Oracle9i to generate the plan_table table;
In Oracle10g, PLAN_TABLE no longer needs to be created. By default, Oracle adds a dictionary table PLAN_TABLE $, and creates a public synonym Based on PLAN_TABLE $ for users to use.

Description of several common Autotrace options:
Set autotrace off ---------------- do not generate AUTOTRACE Report, which is the default mode
Set autotrace on explain ------ AUTOTRACE only displays the optimizer execution path report
Set autotrace on statistics -- display only execution STATISTICS
Set autotrace on ----------------- contains the execution plan and statistics
Set autotrace traceonly ------ same as set autotrace on, but no query output is displayed

1. Use an index in the where clause
SQL> set timing on
SQL> set autotrace on

Before using indexes: full table scan takes 4.46 seconds
SQL> select count (*) from test where wner = 'risenet ';

COUNT (*)
----------
1350

Used time: 00: 00: 04.46

SQL> create index test_owner_index
2 on test (owner );

The index has been created.

Used time: 00: 00: 04.57

SQL> select count (*) from test where wner = 'risenet ';

COUNT (*)
----------
1350

Used time: 00: 00: 00.01

After using the index: 0.01 seconds

2 when count (*) is used for full table scan, you can create a primary key to use the index.
SQL> select count (*) from test;

COUNT (*)
----------
205880

Used time: 00: 00: 02.09

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time |
-------------------------------------------------------------------
| 0 | select statement | 1 | 4109 (1) | 00:00:50 |
| 1 | sort aggregate | 1 |
| 2 | table access full | TEST | 102K | 4109 (1) | 00:00:50 |
-------------------------------------------------------------------

SQL> alter table mzl
2 add primary key (object_id)
3 using index;

The table has been changed.

Used time: 00: 00: 00.53
SQL> select count (*) from mzl;

COUNT (*)
----------
51473

Used time: 00: 00: 00.04

Under what circumstances does the index not work:
1. Type Mismatch

2. When a condition column contains a function but no function index is created

3. The leading column in the composite index is not used as the query condition.

4. The number of rows selected in CBO mode is too large. The optimizer performs full table scan.

5. The following table in the CBO mode is not analyzed, and the table growth is obvious. The optimizer performs full table scan.