dbms_xplan.display_cursor不能正常顯示處理
測試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中把這行去掉吧。
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 不能正常顯示WINDOWS隱藏檔案Windows
- 用Compute Shader處理影像資料後在安卓機上不能正常顯示渲染紋理安卓
- 帝國CMS安裝介面不能正常顯示
- jive 安裝上了,可是中文介面不能正常顯示。
- 子元素浮動不能正常顯示的解決辦法
- 如何處理不能正常開啟 workflow mailer service 問題AI
- 蘋果稽核App被拒——iPhone 應用在 iPad 上不能正常顯示蘋果APPiPhoneiPad
- Jdon在resin 2.16下部分功能不正常(account.jsp不能正常顯示)JS
- win10電腦顯示器螢幕不能鋪滿怎麼辦 win10電腦顯示不能鋪滿螢幕處理方法Win10
- tiff不正常顯示問題
- Mysql輸出中文顯示亂碼處理MySql
- plsql顯示韓文亂碼的處理方法SQL
- Android 介面顯示與邏輯處理Android
- SAP Cloud Application Programming bookshop 例子 Vue頁面不能正常顯示的原因分析CloudAPPVue
- 水庫斷面水位中--水勢顯示效果處理--日期處理 SQLSQL
- monitor_dg_例行巡檢,顯示正常或不正常
- 顯示網路圖片變形的處理
- 字元顯示亂碼問題處理辦法字元
- win10程式圖示顯示不正常怎麼辦_win10系統圖示顯示不正常的解決方法Win10
- win10快捷圖示顯示不正常怎麼辦 win10開機快捷圖示顯示不正常修復方法Win10
- win10檢測不到第二個顯示器怎麼處理_win10第二個顯示器不能識別解決方法Win10
- win10 無法獲取圖示怎麼解決 win10系統圖示不能正常顯示怎麼辦Win10
- Homestead 開啟顯示如下介面,該如何處理?
- 系統中資料顯示進行脫敏處理
- 處理crs_stat -t顯示host列為空
- xshell終端顯示亂碼的處理方法
- flash在jsp中不能顯示?JS
- iPhone中XML處理以及網路上的圖片顯示iPhoneXML
- recycleview聊天介面的從底部開始顯示的處理View
- bc搭建資料夾顯示特殊html字元處理方法HTML字元
- awr報告中顯示enq: TM - contention 處理方法ENQ
- Android圖片處理:識別影象方向並顯示Android
- 控制皮膚中的java無法正常顯示Java
- Html中連續半形空格的正常顯示HTML
- Python 影像處理 OpenCV (2):畫素處理與 Numpy 操作以及 Matplotlib 顯示影像PythonOpenCV
- App在iOS 11下真機顯示正常模擬器上顯示有問題APPiOS
- win10圖示錯誤顯示怎麼改回來_win10電腦圖示顯示錯誤處理方法Win10
- win7桌面圖示顯示不正常解決Win7