Oracle Execution Plan--oracle How to enable execution plan

Autotrace is an sql*plus feature that automatically tracks the generation of an execution plan for SQL statements and provides statistics about the processing of the statement. Sql*plus Autotrace can be used instead of SQL Trace, the benefit of autotrace is that you do not have to format the trace file, and it automatically displays the execution plan for the SQL statement. However, Autotrace parses and executes statements, while explain plan parses only statements. Using autotrace does not produce trace files.

1 Enable Autotrace

1.1 with SYS user, run Utlxplan.sql

Sqlplus/as SYSDBA

sql> @ $ORACLE _home/rdbms/admin/utlxplan.sql

1.2 Creating Plustrace roles by executing plustrce.sql scripts

sql> @ $ORACLE _home/sqlplus/admin/plustrce.sql

1.3 Licensing

Grant the Plustrace role to the current user (a user without DBA role), or grant plustrace to public;

2 Setting the Autotrace command

Serial number	Command	Explain
1	SET AUTOTRACE OFF	This is the default value, which is to turn off Autotrace
2	SET AUTOTRACE on	Generating result sets and interpreting plans and listing statistics
3	SET AUTOTRACE on EXPLAIN	Show result set and interpret schedule do not show statistics
4	SET AUTOTRACE traceonly	Show interpreting schedules and statistics, although executing the statement but you will not see the result set
5	SET AUTOTRACE traceonly STATISTICS	Show Statistics only

Eg:set AUTOTRACE on, set timing on, alter session SET Time_statistics=true;

The following is for reference only

3 Autotrace The meaning of the columns of the implementation plan

Serial number	Column Name	Explain
1	Id_plus_exp	Line number of each step
2	Parent_id_plus_exp	The level number of the parent for each step
3	Plan_plus_exp	Every step of the actual
4	Object_node_plus_exp	Dblink or parallel queries are used

4 Autotrace Statistics Common column interpretation

Serial number	Column Name	Explain
1	DB Block gets	The number of blocks read from the buffer cache
2	Consistent gets	Number of block of undo data read from buffer cache
3	Physical Reads	The number of blocks read from disk
4	Redo Size	The size of the redo generated by DML
5	Sorts (memory)	The sort amount that is executed in memory
6	Sorts (disk)	The amount of sorting performed on the disk