oracle view the actual execution plan

Occasionally, the execution plan that oracle sees is not a real one.
To see if it is a real execution plan is to see if the SQL is actually executed. If it is not executed, then the execution plan may be inaccurate due to the direct error between statistics and practice, or for other reasons.

10046 captures the implementation plan
The execution plan obtained by explain plan, dbms_xplan and AutoTrace may not be the real execution plan.

1.10046

The 10046 event captures the actual execution plan of oracle sql

Use 10046 to view the execution plan method
Open:
SQL> alter session set events '10046 trace name context forever, level 12';
or
SQL> oradebug event 10046 trace name context forever, level 12
Close:
SQL> alter session set events '10046 trace name context off';
or
SQL> oradebug event 10046 trace name context off
SQL> select * from v$mystat where rownum=1;
SQL> SELECT tracefile FROM v$process WHERE addr IN (SELECT paddr FROM v$session WHERE sid='xxx');

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.
SQL> select * from test;
....

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL> select * from v$mystat where rownum=1;

       SID STATISTIC#      VALUE
---------- ---------- ----------
         1          0          0

SQL> SELECT tracefile FROM v$process WHERE addr IN (SELECT paddr FROM v$session WHERE sid='1');

TRACEFILE
--------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2905.trc

tkprof conversion format

tkprof /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2905.trc orcl_ora_2905.trc

View the execution plan in the trc file

Sort options: default
----Default sort

----Description of parameters
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

----SQL ID Already hash value
SQL ID: c99yw1xkb4f1u
Plan Hash: 1357081020

----Executing SQL
select * 
from
 test


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          3          0           1


----Optimizer accesses data units by default all_rows
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91  


----Here you can see the actual resource consumption:
cr Logical reading consumed at each step
pr Physics Reading
pw Physical Writing
time Fat time
card Functions actually returned
size Data Size Returned

//As you can see here, the test returns a row of data after three logical reads and no disk reads.
//Execution Plan Execution Step Operation TABLE ACCESS FULL TEST Full Table Scanning

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL TEST (cr=3 pr=0 pw=0 time=0 us cost=3 size=181 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  SQL*Net message from client                     2        7.76          7.77
********************************************************************************

SQL ID: 06nvwn223659v
Plan Hash: 0
alter session set events '10046 trace name context off'
......

2.explain plan
The target sql is not actually executed, so the execution plan obtained is inaccurate
If there are bound variables, the resulting execution plan can be seen as a semi-finished product, sometimes inaccurate.

3.dbms_xplan
select * from table(dbms_xplan.display)
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced'))
select * from table(dbms_xplan.display_awr('sql_id'))

The execution plan obtained by select * from table(dbms_xplan.display) may be inaccurate because it captures the execution result of explain plan
The remaining three available implementation plans are accurate
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced'))
select * from table(dbms_xplan.display_awr('sql_id'))

4.autotrace

Set AutoTrace and set autotrace traceonly target sql have been executed to see resource consumption, but the execution plan may still be inaccurate because the actual source is explain plan
When the set autotrace traceonly explain parse is used to perform the select operation, sql does not actually execute. Acquired execution plans may be inaccurate

SQL> set autotrace traceonly explain;
SQL> select count(*) from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST |     1 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------

SQL> set autotrace off;
SQL> select a.SQL_TEXT,a.EXECUTIONS from v$sqlarea a where a.SQL_TEXT like 'select count(*)%';

SQL_TEXT                                                                         EXECUTIONS
-------------------------------------------------------------------------------- ----------
select count(*) from test                                                                 0

EXECUTIONS is 0, indicating no real execution.