How Does Oracle view the real SQL Execution Plan?

Knowledge Popularization:

1 normally, we use the explain plan for, set autotrace, and utlxplan methods to view the execution plans. They are all the execution plans generated by optimizer and do not fully conform to the execution paths of Oracle internal SQL statements.

2 v $ parent cursor placed in sqlarea, and SQL _id and address of SQL

3 v $ SQL corresponds to the SQL sub-cursor in v $ sqlarea. address and child_number represent the unique sub-cursor.

How to query the actual SQL Execution Plan of SQL statements in the database:

1 set autotrace on

If the statement returns a large number of rows, the results will be very large, which is a difficult choice in sqlplus.

Set autotrace traceonly -- the correct execution plan cannot be displayed each time, causing misleading SQL optimization.

2 querying v $ SQL _plan is the actual execution path, but it is difficult to read.

3. Use the following statement to query the actual SQL Execution path and format the execution path output.

Set linesize 400

Set pagesize 60

SELECT * from table (SELECT DBMS_XPLAN.DISPLAY_CURSOR ('a8tumy29tf0za ', '0') from dual );

-- The first one is SQL _id.

-- The second is child_number.