Some summary of Oracle execution plan and statistical information

[Date: 2011-08-05]
Source: Linux Community wangshengfeng1986211
[Font: Big Small]

2010-07-01 15:03

1. SET AUTOTRACE on EXPLAIN
(Set AUTOT on exp)
The Sqlplus command, which displays the execution plan while executing the SQL statement, sets exp (lain) to show only the execution plan without displaying statistics:
2. Sql>explain Plan for select "";
Sql>select * FROM table (dbms_xplan.display);

After the set autotrace on explain statement is executed, the next query, insert, UPDATE, and DELETE statements will show the execution plan until the "set Autotrace off" is executed. Statement. If set autotrace on is on, some useful statistics are displayed in addition to the execution plan.

Execute explain plan for to display only the execution plan, and then execute the following query

Sql> select * FROM table (dbms_xplan.display);

Such as:

Sql> explain plan for SELECT * from emp where deptno= ' 20 ';

explained.

Sql> select * FROM table (dbms_xplan.display);

Plan_table_output
--------------------------------------------------------------------------------
Plan Hash value:3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 |       SELECT STATEMENT |      |    5 |      150 | 3 (0) | 00:00:01 |
|* 1 | TABLE ACCESS full|      EMP |    5 |      150 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

Plan_table_output
--------------------------------------------------------------------------------

1-filter ("DEPTNO" =20)

Rows selected.

3, Sql>exec dbms_stats.delete_table_stats (USER, ' table ');(Delete the statistics of the table)

Sql>exec dbms_stats.gather_table_stats (USER, ' table ',method_opt=> ' for all COLUMNS SIZE 100 ') (Collect table statistics)

4. Several common options for autotrace

Set Autotrace off----------------does not generate a autotrace report, which is the default mode
Set autotrace on explain------autotrace only show optimizer execution path report
Set Autotrace on statistics--Displays only execution statistics
Set autotrace on-----------------contains execution plans and statistics
Set Autotrace traceonly------The same set autotrace on, but does not display the query output

(1). Set autotrace on explain; --Show execution plan only
Sql> set autotrace on explain;
Sql>

Select COUNT (*) from dba_objects;
COUNT (*)
----------
31820
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=choose
1 0 SORT (AGGREGATE)
2 1 VIEW of ' dba_objects '
3 2 Union-all
4 3 FILTER
5 4 TABLE ACCESS (by INDEX ROWID) of ' obj$ '
6 5 NESTED LOOPS
7 6 TABLE ACCESS (full) of ' user$ '
8 6 INDEX (RANGE SCAN) of ' i_obj2 ' (UNIQUE)
9 4 TABLE ACCESS (by INDEX ROWID) of ' ind$ '
9 INDEX (unique SCAN) of ' i_ind1 ' (unique)
3 NESTED LOOPS
TABLE ACCESS (full) of ' user$ '
INDEX (RANGE SCAN) of ' I_link1 ' (non-unique)
(2). Set Autotrace on statistics;--only display statistics
Sql> set autotrace on statistics;
Sql> Select COUNT (*) from dba_objects;
COUNT (*)
----------
31820
Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
25754 consistent gets
0 physical Reads
0 Redo Size
383 Bytes sent via sql*net to client
503 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed
(3). Set Autotrace traceonly;--with set autotrace on just do not show query output
Sql> set Autotrace traceonly;
Sql> Select COUNT (*) from dba_objects;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=choose
1 0 SORT (AGGREGATE)
2 1 VIEW of ' dba_objects '
3 2 Union-all
4 3 FILTER
5 4 TABLE ACCESS (by INDEX ROWID) of ' obj$ '
6 5 NESTED LOOPS
7 6 TABLE ACCESS (full) of ' user$ '
8 6 INDEX (RANGE SCAN) of ' i_obj2 ' (UNIQUE)
9 4 TABLE ACCESS (by INDEX ROWID) of ' ind$ '
9 INDEX (unique SCAN) of ' i_ind1 ' (unique)
3 NESTED LOOPS
TABLE ACCESS (full) of ' user$ '
INDEX (RANGE SCAN) of ' I_link1 ' (non-unique)
Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
25754 consistent gets
0 physical Reads
0 Redo Size
383 Bytes sent via sql*net to client
503 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed
(4). Set Autotrace traceonly explain;--a more practical option to show only the execution plan, but with set autotrace on explain, which is useful for viewing only the explain plan of a large table, compared to not executing the statement.
Sql> set Autotrace traceonly explain;
Sql> select * from Dba_objects;
Time used: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=choose
1 0 VIEW of ' dba_objects '
2 1 Union-all
3 2 FILTER
4 3 TABLE ACCESS (by INDEX ROWID) of ' obj$ '
5 4 NESTED LOOPS
6 5 TABLE ACCESS (full) of ' user$ '
7 5 INDEX (RANGE SCAN) of ' i_obj2 ' (UNIQUE)
8 3 TABLE ACCESS (by INDEX ROWID) of ' ind$ '
9 8 INDEX (unique SCAN) of ' i_ind1 ' (unique)
2 TABLE ACCESS (by INDEX ROWID) of ' link$ '
One to ten NESTED LOOPS
TABLE ACCESS (full) of ' user$ '
INDEX (RANGE SCAN) of ' I_link1 ' (non-unique)

5, analyze

Analyze table Hr.employees Compute (estimate) statistics; (compute collects statistics for each row of data, which is time consuming; estimate collect statistics for a subset of data rows)

Select T.owner,t.table_name,t.tablespace_name,t.blocks,t.empty_blocks,t.avg_space
From Dba_tables t
where t.owner= ' HR ';

Some summary of Oracle execution plan and statistical information