在9iR2及以前的Oracle版本中,可以使用列格式化命令对EXPLAIN PLAN的输出结果进行格式化。这个方法在10g中已经无效,实验参考如下。
1.与AUTOTRACE相关的列名大体有6个,关于AUTOTRACE和这6列的描述信息,可以参考官方文档
1)参考链接:
Oracle官方文档将这部分的解释放到了“Tuning SQL*Plus”部分里,大家可以在下面这个Oracle 10gR2的官方文档中得到参考信息。
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch8.htm#i1037182
2)摘录一段话:
Execution Plan Output in Earlier Databases
Execution Plan output from Oracle Database 9i Release 2 (9.2) or earlier is different.
上面的话说明了:9iR2及之前版本的Oracle数据库中可以使用列格式化命令对输出进行干预。在之后的版本中无法使用这样的方法进行格式化了。
3)再摘录整理一下文档中关于旧版本中6列信息的描述,如果您使用的还是9或更早的Oracle,可以参考使用:
(1)ID_PLUS_EXP
Shows the line number of each execution step.
(2)PARENT_ID_PLUS_EXP
Shows the relationship between each step and its parent. This column is useful for large reports.
(3)PLAN_PLUS_EXP
Shows each step of the report.
(4)OBJECT_NODE_PLUS_EXP
Shows database links or parallel query servers used.
(5)OTHER_TAG_PLUS_EXP
Describes the function of the SQL statement in the OTHER_PLUS_EXP column.
(6)OTHER_PLUS_EXP
Shows the text of the query for the parallel server or remote database.
2.上面提到的6列信息默认的格式是什么,怎么查看?
其实在Oracle提供的默认初始化SQL*Plus文件glogin.sql中记录了详细的信息,即使在10gR2的环境中,该设置伊然存在着。
ora10g@secDB /home/oracle$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2004, Oracle Corporation. 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
--
-- 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
COLUMN units_col_plus_show_sga FORMAT a15
-- 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 SHOW RECYCLEBIN
COLUMN origname_plus_show_recyc FORMAT a16 HEADING 'ORIGINAL NAME'
COLUMN objectname_plus_show_recyc FORMAT a30 HEADING 'RECYCLEBIN NAME'
COLUMN objtype_plus_show_recyc FORMAT a12 HEADING 'OBJECT TYPE'
COLUMN droptime_plus_show_recyc FORMAT a19 HEADING 'DROP TIME'
-- Defaults for SET AUTOTRACE EXPLAIN report
-- These column definitions are only used when SQL*Plus
-- is connected to Oracle 9.2 or earlier.
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
-- Default for XQUERY
COLUMN result_plus_xquery HEADING 'Result Sequence'
3.稍微的再解释一下上面的6列信息
1)id_plus_exp和parent_id_plus_exp控制EXPLAIN PLAN输出中头两列
2)plan_plus_exp是执行计划本身的文字描述
3)object_node_plus_exp、other_tag_plus_exp和other_plus_exp这最后三个设置控制并行查询计划显示的输出信息
4.测试一下这些参数在10g中已经不生效
我们使用列格式化命令使这些列均不显示,看一下效果。结果显然,没有影响。
sec@ora10g> COLUMN id_plus_exp noprint
sec@ora10g> COLUMN parent_id_plus_exp noprint
sec@ora10g> COLUMN plan_plus_exp noprint
sec@ora10g> COLUMN object_node_plus_exp noprint
sec@ora10g> COLUMN other_tag_plus_exp noprint
sec@ora10g> COLUMN other_plus_exp noprint
sec@ora10g> explain plan for select * from cat;
SQL> select * from table(dbms_xplan.display);
Explained.
sec@ora10g>
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3442307057
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 662 | 49650 | 37 (3)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL | OBJ$ | 1405 | 102K| 37 (3)| 00:00:01 |
|* 3 | TABLE ACCESS CLUSTER| TAB$ | 1 | 12 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=6 OR
"O"."TYPE#"=2 AND NOT EXISTS (SELECT /*+ */ 0 FROM "SYS"."TAB$" "T"
WHERE "T"."OBJ#"=:B1 AND (BITAND("T"."PROPERTY",512)=512 OR
BITAND("T"."PROPERTY",8192)=8192)))
2 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."LINKNAME" IS
NULL)
3 - filter(BITAND("T"."PROPERTY",512)=512 OR
BITAND("T"."PROPERTY",8192)=8192)
4 - access("T"."OBJ#"=:B1)
24 rows selected.
-- The End --
1.与AUTOTRACE相关的列名大体有6个,关于AUTOTRACE和这6列的描述信息,可以参考官方文档
1)参考链接:
Oracle官方文档将这部分的解释放到了“Tuning SQL*Plus”部分里,大家可以在下面这个Oracle 10gR2的官方文档中得到参考信息。
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch8.htm#i1037182
2)摘录一段话:
Execution Plan Output in Earlier Databases
Execution Plan output from Oracle Database 9i Release 2 (9.2) or earlier is different.
上面的话说明了:9iR2及之前版本的Oracle数据库中可以使用列格式化命令对输出进行干预。在之后的版本中无法使用这样的方法进行格式化了。
3)再摘录整理一下文档中关于旧版本中6列信息的描述,如果您使用的还是9或更早的Oracle,可以参考使用:
(1)ID_PLUS_EXP
Shows the line number of each execution step.
(2)PARENT_ID_PLUS_EXP
Shows the relationship between each step and its parent. This column is useful for large reports.
(3)PLAN_PLUS_EXP
Shows each step of the report.
(4)OBJECT_NODE_PLUS_EXP
Shows database links or parallel query servers used.
(5)OTHER_TAG_PLUS_EXP
Describes the function of the SQL statement in the OTHER_PLUS_EXP column.
(6)OTHER_PLUS_EXP
Shows the text of the query for the parallel server or remote database.
2.上面提到的6列信息默认的格式是什么,怎么查看?
其实在Oracle提供的默认初始化SQL*Plus文件glogin.sql中记录了详细的信息,即使在10gR2的环境中,该设置伊然存在着。
ora10g@secDB /home/oracle$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2004, Oracle Corporation. 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
--
-- 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
COLUMN units_col_plus_show_sga FORMAT a15
-- 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 SHOW RECYCLEBIN
COLUMN origname_plus_show_recyc FORMAT a16 HEADING 'ORIGINAL NAME'
COLUMN objectname_plus_show_recyc FORMAT a30 HEADING 'RECYCLEBIN NAME'
COLUMN objtype_plus_show_recyc FORMAT a12 HEADING 'OBJECT TYPE'
COLUMN droptime_plus_show_recyc FORMAT a19 HEADING 'DROP TIME'
-- Defaults for SET AUTOTRACE EXPLAIN report
-- These column definitions are only used when SQL*Plus
-- is connected to Oracle 9.2 or earlier.
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
-- Default for XQUERY
COLUMN result_plus_xquery HEADING 'Result Sequence'
3.稍微的再解释一下上面的6列信息
1)id_plus_exp和parent_id_plus_exp控制EXPLAIN PLAN输出中头两列
2)plan_plus_exp是执行计划本身的文字描述
3)object_node_plus_exp、other_tag_plus_exp和other_plus_exp这最后三个设置控制并行查询计划显示的输出信息
4.测试一下这些参数在10g中已经不生效
我们使用列格式化命令使这些列均不显示,看一下效果。结果显然,没有影响。
sec@ora10g> COLUMN id_plus_exp noprint
sec@ora10g> COLUMN parent_id_plus_exp noprint
sec@ora10g> COLUMN plan_plus_exp noprint
sec@ora10g> COLUMN object_node_plus_exp noprint
sec@ora10g> COLUMN other_tag_plus_exp noprint
sec@ora10g> COLUMN other_plus_exp noprint
sec@ora10g> explain plan for select * from cat;
SQL> select * from table(dbms_xplan.display);
Explained.
sec@ora10g>
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3442307057
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 662 | 49650 | 37 (3)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL | OBJ$ | 1405 | 102K| 37 (3)| 00:00:01 |
|* 3 | TABLE ACCESS CLUSTER| TAB$ | 1 | 12 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=6 OR
"O"."TYPE#"=2 AND NOT EXISTS (SELECT /*+ */ 0 FROM "SYS"."TAB$" "T"
WHERE "T"."OBJ#"=:B1 AND (BITAND("T"."PROPERTY",512)=512 OR
BITAND("T"."PROPERTY",8192)=8192)))
2 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."LINKNAME" IS
NULL)
3 - filter(BITAND("T"."PROPERTY",512)=512 OR
BITAND("T"."PROPERTY",8192)=8192)
4 - access("T"."OBJ#"=:B1)
24 rows selected.
-- The End --
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-616926/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-616926/