dbms_xplan.display_cursor 无显示的处理

在测试SQL 运行时统计特性时发现使用dbms_xplan.display_cursor 是出下面的错误

SQL>SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'iostats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID  9babjv8yq8ru3, child number 0 BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0       Please verify value of SQL_ID and CHILD_NUMBER;       It could also be that the plan is no longer in cursor cache (check v$sql_plan) 8 rows selected.

造成错误原因是SQLPLUS的环境变量 serveroutput 设置为了ON

SQL>show serveroutput serveroutput ON SIZE 10000 FORMAT WORD_WRAPPED SQL>set serveroutput OFF --关闭serveroutput

关闭后你应该可以看到

SQL>select /*+  gather_plan_statistics*/ count(*) from scott.DEPT;     COUNT(*) ----------          4 1 row selected. Elapsed: 00:00:00.00 SQL>SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'iostats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID  0hgxnuw37sbrx, child number 0 ------------------------------------- select /*+  gather_plan_statistics*/ count(*) from scott.DEPT Plan hash value: 3051237957 -------------------------------------------------------------------------------------- | Id  | Operation        | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT |         |      1 |        |      1 |00:00:00.01 |       1 | |   1 |  SORT AGGREGATE  |         |      1 |      1 |      1 |00:00:00.01 |       1 | |   2 |   INDEX FULL SCAN| PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       1 | -------------------------------------------------------------------------------------- 14 rows selected. Elapsed: 00:00:00.02

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

转载于:http://blog.itpub.net/22782597/viewspace-621097/