[Oracle]查看SQL的执行计划

SQL SET AUTOTRACE ON SQL SELECT SYSDATE FROM DUAL; SYSDATE -------------- 26-9月-12 执行计划 ---------------------------------------------------------- ERROR:anuncaughterror in function displayhashappened;pleasecontactOracle support Please

1. SQL> SET AUTOTRACE ON
2. SQL> SELECT SYSDATE FROM DUAL;
4. SYSDATE
5. --------------
6. 26-9月 -12
9. 执行计划
10. ----------------------------------------------------------
11. ERROR: an uncaught error in function display has happened; please contact Oracle
12. support
14. Please provide also a DMP file of the used plan table PLAN_TABLE
15. ORA-00904: "OTHER_TAG": 标识符无效
18. 统计信息
19. ----------------------------------------------------------
20. 0 recursive calls
21. 0 db block gets
22. 0 consistent gets
23. 0 physical reads
24. 0 redo size
25. 347 bytes sent via SQL*Net to client
26. 338 bytes received via SQL*Net from client
27. 2 SQL*Net roundtrips to/from client
28. 0 sorts (memory)
29. 0 sorts (disk)
30. 1 rows processed

解决方法：

Need to recreate or upgrade the plan table, it is still the 8i one.
Please drop the table and run

SQL> drop table PLAN_TABLE ;

表已删除。

SQL> @C:\oracle\product\10.2.0\client_2\RDBMS\ADMIN\utlxplan.sql

表已创建。

1. SQL> SET AUTOTRACE ON
2. SQL> SELECT SYSDATE FROM DUAL;
4. SYSDATE
5. --------------
6. 26-9月 -12
9. 执行计划
10. ----------------------------------------------------------
11. Plan hash value: 1388734953
13. -----------------------------------------------------------------
14. | Id | Operation | Name | Rows | Cost (%CPU)| Time |
15. -----------------------------------------------------------------
16. | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
17. | 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
18. -----------------------------------------------------------------
21. 统计信息
22. ----------------------------------------------------------
23. 0 recursive calls
24. 0 db block gets
25. 0 consistent gets
26. 0 physical reads
27. 0 redo size
28. 347 bytes sent via SQL*Net to client
29. 338 bytes received via SQL*Net from client
30. 2 SQL*Net roundtrips to/from client
31. 0 sorts (memory)
32. 0 sorts (disk)
33. 1 rows processed
35. SQL>

ps：查看执行计划，也可以是使用 [Oracle]如何查看SQL的执行计划 - DBMS_XPLAN Package