在9iR2及以前的Oracle版本中，可以使用列格式化命令對EXPLAIN PLAN的輸出結果進行格式化。這個方法在10g中已經無效，實驗參考如下。

1.與AUTOTRACE相關的列名大體有6個，關於AUTOTRACE和這6列的描述資訊，可以參考官方文件
1）參考連結：
Oracle官方文件將這部分的解釋放到了“Tuning SQL*Plus”部分裡，大家可以在下面這個Oracle 10gR2的官方文件中得到參考資訊。

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 --

【執行計劃】格式化EXPLAIN PLAN的輸出結果

相關文章