set autot traceonly與explain plan for的一點小區別

結論

1,set autot traceonly檢視測試SQL的執行計劃，會真正去執行對應的測試SQL，並且會產生explan plan set statement_id的取測試SQL的SQL
2,explan plan for也可以檢視測試SQL的執行計劃，但它不會真正去執行對應的測試sql
3,我分析得出上述結論的依據是檢視v$sql
4,set autot traceonly如果執行多次測試sql,會生成多個不同子游標sql,但sql_id相同,但是child_number不同
5,基於上述結論，如果要檢視執行計劃，一定要用set autot traceonly,這會獲取一個真正執行過的執行計劃的統計資訊與指標

測試

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


BANNER
------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


SQL> set autot traceonly


SQL> select * from t_test_physical;


10 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 1051875242


-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |    10 |   130 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T_TEST_PHYSICAL |    10 |   130 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        645  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed


---可見set autot traceonly作用同explain plan for一樣，且每次產生的子游標不同，會產生多個子遊標版本
SQL> select sql_id,child_number,sql_text from v$sql where lower(sql_text) like '%select * from t_test_physical%' order by 1,2;


SQL_ID                     CHILD_NUMBER SQL_TEXT
-------------------------- ------------ --------------------------------------------------
0a23w2j4gbfgj                         0 EXPLAIN PLAN SET STATEMENT_ID='PLUS33316409' FOR s
                                        elect * from t_test_physical


0a23w2j4gbfgj                         1 EXPLAIN PLAN SET STATEMENT_ID='PLUS33316409' FOR s
                                        elect * from t_test_physical


0a23w2j4gbfgj                         2 EXPLAIN PLAN SET STATEMENT_ID='PLUS33316409' FOR s
                                        elect * from t_test_physical


0a23w2j4gbfgj                         3 EXPLAIN PLAN SET STATEMENT_ID='PLUS33316409' FOR s
                                        elect * from t_test_physical


SQL_ID                     CHILD_NUMBER SQL_TEXT
-------------------------- ------------ --------------------------------------------------


0a23w2j4gbfgj                         4 EXPLAIN PLAN SET STATEMENT_ID='PLUS33316409' FOR s
                                        elect * from t_test_physical


597tfvph509y5                         0 select sql_id,child_number,sql_text from v$sql whe
                                        re lower(sql_text) like '%select * from t_test_phy
                                        sical%'


65crh2jad201p                         0 select sql_id,sql_text from v$sql where lower(sql_
                                        text) like '%select * from t_test_physical%'




SQL_ID                     CHILD_NUMBER SQL_TEXT
-------------------------- ------------ --------------------------------------------------
aay3tk4j538mu                         0 select sql_id,child_number,sql_text from v$sql whe
                                        re lower(sql_text) like '%select * from t_test_phy
                                        sical%' order by 1,2


bckrm5s2n5vmu                         0 select * from t_test_physical


9 rows selected.




SQL> startup force
ORACLE instance started.


Total System Global Area  572100608 bytes
Fixed Size                  2215304 bytes
Variable Size             511705720 bytes
Database Buffers           50331648 bytes
Redo Buffers                7847936 bytes
Database mounted.
Database opened




SQL> set autot traceonly
SQL> set linesize 300
SQL> select * from t_test_physical;


10 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 1051875242


-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |    10 |   130 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T_TEST_PHYSICAL |    10 |   130 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)




Statistics
----------------------------------------------------------
        237  recursive calls
          0  db block gets
         44  consistent gets
          8  physical reads
          0  redo size
        645  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
         10  rows processed


SQL> 


---可見set autot traceonly實際會把測試SQL執行一次，並且產生SQL explain plan for sql
SQL> select sql_id,child_number,sql_text from v$sql where lower(sql_text) like '%select * from t_test_physical%' order by 1,2;


SQL_ID                     CHILD_NUMBER SQL_TEXT
-------------------------- ------------ --------------------------------------------------
4ctjrqauu1731                         0 EXPLAIN PLAN SET STATEMENT_ID='PLUS33326409' FOR s
                                        elect * from t_test_physical


aay3tk4j538mu                         0 select sql_id,child_number,sql_text from v$sql whe
                                        re lower(sql_text) like '%select * from t_test_phy
                                        sical%' order by 1,2


bckrm5s2n5vmu                         0 select * from t_test_physical  








----測試下explain plan for
SQL> startup force
ORACLE instance started.


Total System Global Area  572100608 bytes
Fixed Size                  2215304 bytes
Variable Size             511705720 bytes
Database Buffers           50331648 bytes
Redo Buffers                7847936 bytes
Database mounted.
Database opened.


SQL> set linesize 300
SQL> explain plan for select * from t_test_physical;


Explained.


SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1051875242


-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |    10 |   130 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T_TEST_PHYSICAL |    10 |   130 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------


Note
-----


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)


12 rows selected.


---可見explan plan for沒有真正執行測試的sql
SQL> select sql_id,child_number,sql_text from v$sql where lower(sql_text) like '%select * from t_test_physical%' order by 1,2;


SQL_ID                     CHILD_NUMBER SQL_TEXT
-------------------------- ------------ --------------------------------------------------
9z8a9qhx8w4du                         0 explain plan for select * from t_test_physical
aay3tk4j538mu                         0 select sql_id,child_number,sql_text from v$sql whe
                                        re lower(sql_text) like '%select * from t_test_phy
                                        sical%' order by 1,2