今天在测试过程中，偶然发现在Oracle10g Release 2中，Autotrace的功能已经被极大加强和改变。
这是一个很有意思的小的变化，关于autotrace的使用，请参考:
启用AutoTrace接下来让我们先来看一下什么地方发生了改变:
SQL> set linesize 120
SQL> set autotrace on
SQL> select count(*) from v$session
2 /
COUNT(*)
----------
21
Execution Plan
----------------------------------------------------------
Plan hash value: 2376410614
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 65 | | |
| 2 | NESTED LOOPS | | 1 | 65 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$KSUSE | 1 | 52 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 13 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
BITAND("S"."KSUSEFLG",1)<>0)
4 - filter("S"."KSUSEOPC"="E"."INDX")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
|
注意，此时autotrace的输出被良好格式化，并给出关于执行计划部分的简要注释。
其实这里并没有带来新的东西，从Oracle9i开始，Oracle提供了一个新的工具dbms_xplan用以格式化和查看SQL的执行计划。其原理是通过对plan_table的查询和格式化提供更友好的用户输出。
dbms_xplan的调用的语法类似:
select * from table(dbms_xplan.display(format=>'BASIC'))
使用 TABLE() 操作符，或者 CAST 操作。
具体用法可以参考Oracle官方文档。
实际上从Oracle9i开始我们就经常使用如下方式调用dbms_xplan:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> explain plan for
2 select count(*) from dual;
Explained.
SQL> @?/rdbms/admin/utlxplp;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | TABLE ACCESS FULL | DUAL | | | |
--------------------------------------------------------------------
Note: rule based optimization
10 rows selected.
|
utlxplp.sql脚本中正是调用了dbms_xplan:
