How to enable the execution plan in Oracle

1. Enable autotrace

1.1 run utlxplan. SQL as a sys user

Sqlplus/As sysdba

SQL> @ $ ORACLE_HOME/rdbms/admin/utlxplan. SQL

1.2 execute the plustrce. SQL script to create the plustrace role

SQL> @ $ ORACLE_HOME/sqlplus/admin/plustrce. SQL

1.3 authorization

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

2. Set the autotrace command

Serial number	Command	Explanation
1	Set autotrace off	This is the default value, that is, disable autotrace.
2	Set autotrace on	Generate result set and explain Plan and list statistics
3	Set autotrace on explain	Show result set and explain Plan not show statistics
4	Set autotrace traceonly	Displays explain plans and statistics. Even if you execute this statement, you cannot 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;

3 Meanings of columns in the autotrace execution plan

Serial number	Column name	Explanation
1	Id_plus_exp	The row number of each step.
2	Parent_id_plus_exp	Level Number of the parent in each step
3	Plan_plus_exp	Actual steps
4	Object_node_plus_exp	Dblink or parallel query

4Autotrace statistics common column explanations

Serial number	Column name	Explanation
1	Db block gets	Number of blocks read from buffer cache
2	Consistent gets	Number of Undo data blocks read from buffer cache
3	Physical reads	Number of blocks read from the disk
4	Redo size	Size of the redo generated by DML
5	Sorts (memory)	Sorting amount executed in memory
6	Sorts (Disk)	Number of orders executed on the disk