前两天同事碰到一个问题,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
--------------------------------------------------------------------------------
| 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