set autot traceonly与explain plan for的一点小区别

最新推荐文章于 2021-04-04 10:09:40 发布

clg10051

最新推荐文章于 2021-04-04 10:09:40 发布

阅读量381

点赞数

文章标签：数据库

结论

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1873304/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1873304/