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方法可以输出信息。所以不能在plsql/sql的SQL WINDOW中使用，而是用在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的限制，指定sql_id即可：

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

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

| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffe

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

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

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

12 rows selected

原文地址：http://www.2cto.com/database/201305/214430.html

总结：

可以在脚本文件login.sql中加入set serveroutput onsize 1000000,这样每次启动SQLPLUS时都会自动开启SERVEROUTPUT；

注意：

在存储过程中，如果加入dbms_output.enable(1000000)，则是设置整个过程的全部输出缓冲区大小，不能控制是否在客户端显示输出信息。