获取SQL执行计划的方式有很多，但是某些时候获取的SQL执行计划并不是准确的，只有在SQL真实执行之后获取到的SQL PLAN才是真实准确的，其他方式(如，explain plan)获取到的执行计划都有可能因为绑定绑定变量和当时SQL执行环境的影响而导致获取到的执行计划可能不准确。对于AUTOTRACE开关，当执行SET AUTOT ON和SET AUTOT TRACE命令此时的SQL是实际执行过的，所以此时获取到的执行计划是准确的。而SET AUTOT TRACE EXP命令时稍有不同，当执行的是SELECT语句时SQL语句并不会实际执行，此时获取到的执行计划和直接使用explain plan命令获取的结果是一致的，但是对于DML命令则会实际执行该SQL。
 下面看一个例子，说明SET AUTOT TRACE EXP并不会真实的执行SELECT语句，以此为例来介绍如何确定SQL是否真实执行。  首先，我们直接执行SQL，可以在V$SQLAREA里看到EXECUTIONS的值为1。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
SQL> SELECT PCT_USED,PCT_FREE FROM TEST_ENV.TB_TABLE_LIST WHERE TABLE_NAME='DBMS_SQLPATCH_STATE';
PCT_USED PCT_FREE ---------- ---------- 40 10
SQL> SELECT EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT PCT_USED,PCT_FREE FROM TEST_%';
EXECUTIONS ---------- 1
SQL> ALTER SYSTEM FLUSH SHARED_POOL;--该操作为了方便后面查询，生产环境勿执行
系统已更改。
 打开SET AUTOT TRACE EXP，后重复上述SQL，可以看到EXECUTIONS的值为0，表明SQL并没有真实执行。
SQL> select a.table_name,a.pct_used,b.column_name from test_env.tb_table_list a,test_env.tb_column_list b where a.owner=b.owner and a.table_name=b.table_name and b.table_name='DBMS_SQLPATCH_STATE';
select a.table_name,a.pct_used,b.column_name from test_env.tb_table_list a,test_env.tb_column_list b where a.owner=b.owner and a.table_name=b.table_name and b.table_name='DBMS_SQLPATCH_STATE'
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ Disk file operations I/O 2 0.00 0.00 SQL*Net message to client 3 0.00 0.00 db file sequential read 2 0.01 0.01 db file scattered read 36 0.01 0.04 SQL*Net message from client 3 6.98 7.06 PGA memory operation 1 0.00 0.00
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 6 0.00 0.00 SQL*Net message from client 6 80.00 104.95 db file sequential read 2 0.01 0.01 PGA memory operation 1 0.00 0.00 Disk file operations I/O 5 0.00 0.00 db file scattered read 36 0.01 0.04
Misses in library cache during parse: 2 Misses in library cache during execute: 15
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 10 0.00 0.04 PGA memory operation 7 0.00 0.00 Disk file operations I/O 1 0.00 0.00
1 user SQL statements in session. 19 internal SQL statements in session. 20 SQL statements in session. ******************************************************************************** Trace file: C:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_2548.trc Trace file compatibility: 12.2.0.0 Sort options: default
1 session in tracefile. 1 user SQL statements in trace file. 19 internal SQL statements in trace file. 20 SQL statements in trace file. 20 unique SQL statements in trace file. 2176 lines in trace file. 0 elapsed seconds in trace file.