Oracle SQL with AutoTRACE analysis of the implementation plan

2010-09-06  来源:本站原创  分类:Database  人气:150 


* Environment: windowsXP + Oracle10gR2
* AutoTRACE is to analyze the SQL execution plan, the efficiency of a very simple and convenient tool
* /

AUTOTRACE is a function of SQL * Plus automatically generates a trace for the SQL statement execution plan and provide a statement dealing with the statistics.

SQL * Plus AUTOTRACE SQL Trace can be used to replace the use, AUTOTRACE advantage is you do not set the trace file format, and it will automatically display the execution plan for the SQL statement. However, AUTOTRACE analysis and implementation of the statement; but only of EXPLAIN PLAN statement.

Use AUTOTRACE not produce trace file.

SQLPLUS of AutoTrace is to analyze the SQL execution plan, the efficiency of a very simple and convenient tool, in most cases is a very useful tool. Using AutoTrace tool provides SQL execution plan and the implementation of the state can be optimized SQL for us to provide optimized based on the time and the optimization of the apparent effect of contrast.


For example:
SET AUTOT [RACE] OFF stop AutoTrace
SET AUTOT [RACE] ON open AutoTrace, show AUTOTRACE execution plan and statistics and results of SQL execution
SET AUTOT [RACE] TRACEONLY open AutoTrace, show only AUTOTRACE information
SET AUTOT [RACE] ON EXPLAIN open AutoTrace, show only AUTOTRACE implementation plan

SET AUTOT [RACE] ON STATISTICS open AutoTrace, statistics show only AUTOTRACE

Interpretation of results
physical reads physical time - the process of implementation of SQL, the data read from the hard disk block number
redo size redo a few - the process of implementation of SQL to generate the size of the redo log
bytes set via sql * net to client via sql * net send to the client the number of bytes
bytes received via sql * net from client through sql * net client to accept the number of bytes
sorts (memory) in memory of the sort occurred
sorts (disk) can not occur in memory sort, need hard drive to help
the number of records the results of rows processed

AutoTrace optimized Note

1. Can be implemented by setting the timing to the time used SQL, but not only this time as the only measure of SQL execution efficiency. This time will include some time AUTOTRACE consumption, so this time and not just SQL execution time. This time with the SQL execution time there is some error, but in SQL is relatively simple especially when.

2. Should determine the level of SQL efficiency through the implementation of the logic inside the SQL implementation of the state of the number of logical read read = (db block gets + consistent gets)

AutoTrace is to optimize ORACLE tool in the most basic tools, although more limited functionality, but enough to meet our daily needs.

In Oracle9i the need to run $ ORACLE_HOME \ RDBMS \ ADMIN \ utlxplan.sql script generates plan_table table;
In Oracle10g the PLAN_TABLE no longer need to create, Oracle adds a default dictionary table PLAN_TABLE $, and then create a public synonym on PLAN_TABLE $ for users

About Autotrace several common options:
SET AUTOTRACE OFF ---------------- AUTOTRACE not generate the report, which is the default mode
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE shows only the optimizer execution path report
SET AUTOTRACE ON STATISTICS - show only the implementation of statistical information
SET AUTOTRACE ON ----------------- include the implementation of plans and statistical information
SET AUTOTRACE TRACEONLY ------ with set autotrace on, but does not show query output

1 where the use of index
SQL> set timing on
SQL> set autotrace on

Before the index is not used: full table scan took 4.46 seconds
SQL> select count (*) from test where wner = 'RISENET';


Elapsed time: 00: 00: 04.46

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

Index has been created.

Elapsed time: 00: 00: 04.57

After using the index: 0.01 seconds

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


Elapsed time: 00: 00: 00.01

2 When using count (*) using the full table scan, you can create a primary key, so you can use to index
SQL> select count (*) from test;

205 880

Elapsed time: 00: 00: 02.09

Implementation 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;

Table has been changed.

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


Elapsed time: 00: 00: 00.04

The circumstances under which the index does not work:
1, the type does not match

2, the conditions column contains the function but did not create a function index

3, composite index of leading column is not a query

4, CBO mode, select a disproportionately large number of rows, the optimizer to take a full table scan

5, CBO mode table is no analysis of the growth of the table clear, the optimizer to take a full table scan