How to use Sqlplus to parse SQL statements (Query execution plan tracking)

Method One: Autotrace

1, connect sys/password as SYSDBA, run under SYS user $oracle_home/sqlplus/admin/plustrce.sql
This paragraph SQLThe actual contents are as follows:
Set echo on
Drop role Plustrace;
Create role Plustrace;
Grant SELECT on V_$sesstat to Plustrace;
Grant SELECT on V_$statname to Plustrace;
Grant SELECT on V_$mystat to Plustrace;
Grant Plustrace to DBA with admin option;
Set echo off

This generates the Plustrace role, and then gives the role to the general user under the SYS user
Sql> Grant Plustrace to user name;

2, and then run $oracle_home/rdbms/admin/utlxplan.sql under the current user sql>, it creates a plan_table that stores the results of the parsed SQL statement.

The following statements are actually executed:

CREATE TABLE Plan_table (
statement_id VARCHAR2 (30),
Timestamp date,
Remarks VARCHAR2 (80),
Operation Varchar2 (30),
Options VARCHAR2 (30),
Object_node VARCHAR2 (128),
Object_owner VARCHAR2 (30),
object_name VARCHAR2 (30),
Object_instance Numeric,
Object_type VARCHAR2 (30),
Optimizer VARCHAR2 (255),
Search_columns number,
ID Numeric,
parent_id Numeric,
Position Numeric,
Cost Numeric,
Cardinality numeric,
Bytes Numeric,
Other_tag varchar2 (255),
Partition_start varchar2 (255),
Partition_stop varchar2 (255),
partition_id Numeric,
Other Long,
Distribution VARCHAR2 (30));

The 3,sql/plus window runs the following command

Set time on; (Description: Open time display) (optional)
Set autotrace on; (Description: Turn on automatic analysis statistics and display the results of the SQL statement)
Set Autotrace traceonly; (Description: Turn on automatic analysis statistics, do not display the results of the SQL statement running)

4, next you run the SQL statement that needs to look at the execution plan and see its analysis statistics. In general, our SQL statements should avoid full table scans of large tables.

5, turn off the above features, run the following command in the Sql/plus window

Set time off; (Description: Turn off time display)
Set Autotrace off;

Attached: The associated set autotrace command:

SET AUTOTRACE OFF
No AUTOTRACE report is generated.

SET AUTOTRACE on EXPLAIN
Shows only the optimizer execution path.

SET AUTOTRACE on

STATISTICS shows only the SQL statement execution STATISTICS.

SET AUTOTRACE on
Includes both the optimizer execution path and the SQL statement execution statistics.

SET AUTOTRACE traceonly
Like SET AUTOTRACE on, but suppresses the printing of the user's query output, if any.

Note: If the set autotrace on statement is executed, the following query, insert, UPDATE, and DELETE statements will display the execution plan and useful statistics until the set Autotrace off statement is executed.

Method Two: EXPLAIN PLAN for SQL

1, executed under Sqlplus: EXPLAIN PLAN for SQL statement, after execution will prompt has been explained.

2, then execute the following query, query out the execution plan

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

Example:

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

explained.

Sql> select * FROM table (dbms_xplan.display); Note: In addition to the above, there is a very useful function Dbms_xplan.display_awr (sql_id, Null,null, ' all '), this can be combined with the sql_id of the SQL statement in the AWR report to get the execution plan of the history statement, and then through the view Dba_hist_sqlbind find the binding variable value for the statement.

How to use Sqlplus to parse SQL statements (Query execution plan tracking)