【Oracle】-【sqlplus相關】-serveroutput引數

前兩天同事碰到一個問題，10g版本下執行：

select /*+ no_index(t1 idx_t1) */ * from t1 where n=3;
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

提示：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9m7787camwh4m, child number 0
begin :id := sys.dbms_transaction.local_transaction_id; end;
NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, 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_p

8 rows selected

查了資料說是serveroutput需要設定為off，才能用dbms_xplan列印執行計劃。

但嘗試用了set serveroutput off後沒有任何反應，而且也看不了serveroutput的任何設定值，很是奇怪。

分析：

1、以上語句是在PLSQL Developer的command windows中用的。PLSQL Developer是第三方的Oracle客戶端工具，可能無法列印出這個dbms_xplan的執行計劃。

2、serveroutput是sqlplus的語句或配置引數，不是資料庫或sql的語句或配置引數。

3、set serveroutput on語句作用是開啟oracle自帶的輸出方法dbms_output，在執行以後，使用dbms_output方法可以輸出資訊。所以不能在sql window中使用，該命令不是寫在pl/sql中寫的,是在sql/plus中執行。SQL WINDOW裡面可以不加set serveroutput on就可以使用了。

資料顯示：

1. sql> set serveroutput on

然後,在sqlplus中

2. sql> store set $ORACLE_HOME/sqlplus/admin/glogin.sql replace

完成儲存設定.

可以通過如下檢視設定效果：

SQL> show serveroutput;serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

說明設定成功。

glogin.sql如下：

--
-- Copyright (c) Oracle Corporation 1988, 2000. All Rights Reserved.
--
-- NAME
-- glogin.sql
--
-- DESCRIPTION
-- SQL*Plus global login startup file.
--
-- Add any sqlplus commands here that are to be executed when a user
-- starts SQL*Plus on your system
--
-- USAGE
-- This script. is automatically run when SQL*Plus starts
--

-- For backward compatibility
SET PAGESIZE 14
SET SQLPLUSCOMPATIBILITY 8.1.7

-- Used by Trusted Oracle
COLUMN ROWLABEL FORMAT A15

-- Used for the SHOW ERRORS command
COLUMN LINE/COL FORMAT A8
COLUMN ERROR FORMAT A65 WORD_WRAPPED

-- Used for the SHOW SGA command
COLUMN name_col_plus_show_sga FORMAT a24

-- Defaults for SHOW PARAMETERS
COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME
COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

-- Defaults for SET AUTOTRACE EXPLAIN report
COLUMN id_plus_exp FORMAT 990 HEADING i
COLUMN parent_id_plus_exp FORMAT 990 HEADING p
COLUMN plan_plus_exp FORMAT a60
COLUMN object_node_plus_exp FORMAT a8
COLUMN other_tag_plus_exp FORMAT a29
COLUMN other_plus_exp FORMAT a44

基本都是格式化的內容。

當然也可以繞過PLSQL Developer的限制，按照http://www.itpub.net/thread-1749679-1-1.html的講解：

SQL> select /* test *//*+ gather_plan_statistics */ * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2550.00 10

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5100.00 10

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1987/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER CLERK 7782 1982/1/23 1400.00 10

14 rows selected

SQL> select sql_id, child_number, sql_text from v$sql where sql_text like '%test%';

SQL_ID CHILD_NUMBER SQL_TEXT

------------- ------------ --------------------------------------------------------------------------------

0200bsvkhc917 0 select wrm.last_ash_sample_id from WRM$_DATABASE_INSTANCE wrm, (select

5ms1dhxbadq64 0 update wrm$_snapshot set status = 0, flush_elapsed = greatest((cast

2hqz4n3062jhq 0 select /* test *//*+ gather_plan_statistics */ * from emp

cptm0vuy03d5g 0 select sql_id, child_number, sql_text from v$sql where sql_text like '%test%'

SQL> select * from table(dbms_xplan.display_cursor('2hqz4n3062jhq', 0, 'allstats last'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID 2hqz4n3062jhq, child number 0

-------------------------------------

select /* test *//*+ gather_plan_statistics */ * from emp

Plan hash value: 3956160932

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 |

--------------------------------------------------------------------------------

12 rows selected

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

【Oracle】-【sqlplus相關】-serveroutput引數

相關文章