dbms_xplan.display_cursor不能正常顯示處理

dbinsight發表於2013-10-22
測試db環境今天無法正常顯示dbms_xplan.display_cursor的結果,報錯如下:
SQL> select count(*) from t1;

  COUNT(*)
----------
     49999

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

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.

昨天還是OK的。
不過我昨天修改了$ORACLE_HOME/sqlplus/admin/glogin.sql,新增了幾個專案:
[oracle@ora11g ~]$ cd $ORACLE_HOME/sqlplus/admin
[oracle@ora11g admin]$ cat glogin.sql
--
-- Copyright (c) 1988, 2011, Oracle and/or its affiliates.
-- All rights reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script. is automatically run

SET LINESIZE 300
SET PAGES 500
SET LONG 999999
set serveroutput on format wrapped

column NAME format a30
column OWNER format a15
column VALUE format a50

惟有一些SET和COLUMN的命令,看起來都還是算正常,唯一可能出問題的在於set serveroutput這裡。
關閉serveroutput,重新嘗試:
SQL> set serveroutput off
SQL> select count(*) from t1;

  COUNT(*)
----------
     49999

SQL>  select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5bc0v4my7dvr5, child number 0
-------------------------------------
select count(*) from t1

Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |    65 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 49999 |    65   (0)| 00:00:01 |
-------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T1@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]


39 rows selected.

蛋疼,問題果然在這裡。
從glogin.sql中把這行去掉吧。


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29154652/viewspace-774816/,如需轉載,請註明出處,否則將追究法律責任。

相關文章