13518219792
一、描述
在查看执行计划测试的过程中遇到使用dbms_xplan.display无法查看执行计划，发现是因为之前在测试的过程中打开了set autot on，关闭后(set autot off) ，执行计划可以正常显示。
成都创新互联专注于天台网站建设服务及定制，我们拥有丰富的企业做网站经验。 热诚为您提供天台营销型网站建设，天台网站制作、天台网页设计、天台网站官网定制、小程序开发服务，打造天台网络公司原创品牌,更为您提供天台网站排名全网营销落地服务。
二、操作过程
SQL>
SQL> explain plan for select count(*) from tt;
Explained.
Elapsed: 00:00:00.00
SQL> set lines 200 pages 2000
SQL> select * from table(dbms_xplan.display());
9 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
14 recursive calls
12 db block gets
55 consistent gets
0 physical reads
0 redo size
1124 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
##上面无法显示执行计划，很奇怪！！！
##关闭autot ，再次查看执行计划OK！！！
SQL> set autot off
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3133740314
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21556 (1)| 00:04:19 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TT | 2762K| 21556 (1)| 00:04:19 |
-------------------------------------------------------------------
9 rows selected.
Elapsed: 00:00:00.02
##解释一下执行计划，首先是做了全表扫描这个很容易理解，sort aggregage 是因为做了count(*) 聚合。
SQL> 
