开博了。其实之前有的，只是不在pub上，最近实在忍受不了msn共享空间了。

oracle 10053诊断事件

上一篇 / 下一篇 2008-01-25 15:32:08 / 个人分类：读书笔记

本文参考王海《ORACLE 诊断事件及深入解析10053 事件》概念来自于该文档
一、概念
Oracle 为RDBMS 提供了多种的诊断工具，诊断事件(Event)是其中一种常用、好用的
方法，它使DBA 可以方便的转储数据库各种结构及跟踪特定事件的发生.
1、通常格式如下：
EVENT="<事件名称><动作><跟踪项目><范围限定>"

2、 Event 分类
诊断事件大体上可以分为四类：
a．转储类事件：它们主要用于转储Oracle 的一些结构，例如转储一下控制文件、数
据文件头等内容。
b．捕捉类事件：它们用于捕捉一些Error 事件的发生，例如捕捉一下ORA-04031 发
生时一些Rdbms 信息，以判断是Bug 还是其它原因引起的这方面的问题。
c．改变执行途径类事件：它们用于改主一些Oracle 内部代码的执行途径，例如设置
10269 将会使Smon 进程不去合并那些Free 的空间。
d．跟踪类事件：这们用于获取一些跟踪信息以用于Sql 调优等方面，最典型的便是
10046 了，将会对Sql 进行跟踪。

3、设置诊断事件
a、init.ora
EVENT="\
10231 trace name context forever, level 10:\
10232 trace name context forever, level 10"
可以设置多个也可以设置一个
b、session/system
Alter session/system set events ‘immediate trace name controlf level 10;

4、DBMS_SYSTEM.SET_EV过程
a．过和定义如下
DBMS_SYSTEM.SET_EV(
SI Binary_integer,
SE Binary_integer,
EV Binary_integer,
LE Binary_integer,
NM Binary_integer);
SI: 即v$session 中的sid
SE:即v$session 中的serial#
EV:要设置的事件
LE:要设置事件的级别
NM:名称
b．举个例子，以10046 为例
SQL> EXECUTE SYS.DBMS_SYSTEM.SET_EV(sid,serial#,10046,12,'')；
王海讲的很细。我主要是想测试一下10053事件，以及该事件的准备工作，就不细谈了，前面只是基本的概念。其中需要注意的是：
要实现跟踪必须满足两个条件：sql 语句必须被hardparse 并且必须使用CBO 优化器模式

二、准备工作
1、设置优化器及启用关闭该事件

C:\Documents and Settings\zero>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 1月 25 14:34:28 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> conn test/1
已连接。
SQL>

SQL> alter session set events '10053 trace name context forever ,level 1'
2 /

会话已更改。

SQL> alter session set optimizer_mode = all_rows;

会话已更改。

SQL> select count(1) from ele_enterprise ee ,ele_budget_subject bs,ele_payoff_kind pk,ele_manage_branch mb;

COUNT(1)
----------
139853952

SQL> alter session set events '10053 trace name context off';

会话已更改。
此时我本地的udump已经生成了一个trc文件“zero_ora_2128.trc”

2、文件逐步分析
a、语句

*** 2008-01-25 14:20:21.000
QUERY
select count(1) from ele_enterprise ee ,ele_budget_subject bs,ele_payoff_kind pk,ele_manage_branch mb
=====================
这个是我在该事件启用的时候的一个sql查询语句，写的很烂，肯定会硬解析，我在上一步设置了cbo的优化模式，保证了该语句能够被10053事件记录

b、优化器信息

记载了所有影响成本计算的参数
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
OPTIMIZER_FEATURES_ENABLE = 9.2.0
OPTIMIZER_MODE/GOAL = All_Rows
_OPTIMIZER_PERCENT_PARALLEL = 101
HASH_AREA_SIZE = 1048576
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 0
SORT_AREA_SIZE = 524288
OPTIMIZER_SEARCH_LIMIT = 5
PARTITION_VIEW_ENABLED = FALSE
_ALWAYS_STAR_TRANSFORMATION = FALSE
_B_TREE_BITMAP_PLANS = TRUE
STAR_TRANSFORMATION_ENABLED = FALSE
_COMPLEX_VIEW_MERGING = TRUE
_PUSH_JOIN_PREDICATE = TRUE
PARALLEL_BROADCAST_ENABLED = TRUE
OPTIMIZER_MAX_PERMUTATIONS = 2000
OPTIMIZER_INDEX_CACHING = 0
_SYSTEM_INDEX_CACHING = 0
OPTIMIZER_INDEX_COST_ADJ = 100
OPTIMIZER_DYNAMIC_SAMPLING = 1
_OPTIMIZER_DYN_SMP_BLKS = 32
QUERY_REWRITE_ENABLED = FALSE
QUERY_REWRITE_INTEGRITY = ENFORCED
_INDEX_JOIN_ENABLED = TRUE
_SORT_ELIMINATION_COST_RATIO = 0
_OR_EXPAND_NVL_PREDICATE = TRUE
_NEW_INITIAL_JOIN_ORDERS = TRUE
ALWAYS_ANTI_JOIN = CHOOSE
ALWAYS_SEMI_JOIN = CHOOSE
_OPTIMIZER_MODE_FORCE = TRUE
_OPTIMIZER_UNDO_CHANGES = FALSE
_UNNEST_SUBQUERY = TRUE
_PUSH_JOIN_UNION_VIEW = TRUE
_FAST_FULL_SCAN_ENABLED = TRUE
_OPTIM_ENHANCE_NNULL_DETECTION = TRUE
_ORDERED_NESTED_LOOP = TRUE
_NESTED_LOOP_FUDGE = 100
_NO_OR_EXPANSION = FALSE
_QUERY_COST_REWRITE = TRUE
QUERY_REWRITE_EXPRESSION = TRUE
_IMPROVED_ROW_LENGTH_ENABLED = TRUE
_USE_NOSEGMENT_INDEXES = FALSE
_ENABLE_TYPE_DEP_SELECTIVITY = TRUE
_IMPROVED_OUTERJOIN_CARD = TRUE
_OPTIMIZER_ADJUST_FOR_NULLS = TRUE
_OPTIMIZER_CHOOSE_PERMUTATION = 0
_USE_COLUMN_STATS_FOR_FUNCTION = TRUE
_SUBQUERY_PRUNING_ENABLED = TRUE
_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50
_SUBQUERY_PRUNING_COST_FACTOR = 20
_LIKE_WITH_BIND_AS_EQUALITY = FALSE
_TABLE_SCAN_COST_PLUS_ONE = TRUE
_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
_OPTIMIZER_COST_MODEL = CHOOSE
_GSETS_ALWAYS_USE_TEMPTABLES = FALSE
DB_FILE_MULTIBLOCK_READ_COUNT = 16
_NEW_SORT_COST_ESTIMATE = TRUE
_GS_ANTI_SEMI_JOIN_ALLOWED = TRUE
_CPU_TO_IO = 0
_PRED_MOVE_AROUND = TRUE

c、基本统计信息
下一部分是所有表和索引的基本统计信息
基本统计信息包括
表:
Trace label dba_tables column
CDN NUM_ROWS 表记录数
NBLKS BLOCKS 高水位以下的block 数

TABLE_SCAN_CST 全表扫描的I/O 成本
AVG_ROW_LEN AVG_ROW_LEN 平均行长
索引:
Trace label dba_indexes column
Index#, col# 索引号及表列号
LVLS BLEVEL BTREE 索引高度
#LB LEAF_BLOCKS 索引叶块数
#DK DISTINCT_KEYS 不重复索引关键字
LB/K AVG_LEAF_BLOCKS_PER_KEY 叶块/关键字
DB/K AVG_DATA_BLOCKS_PER_KEY 数据块/关键字
CLUF CLUSTERING_FACTOR 索引聚合因子

***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: ELE_MANAGE_BRANCH   Alias: MB
TOTAL :: CDN: 16 NBLKS: 5 AVG_ROW_LEN: 287
-- Index stats
INDEX NAME: ELE_MANAGE_BRANCH_N1 COL#: 1
    TOTAL :: LVLS: 0   #LB: 1 #DK: 1 LB/K: 1 DB/K: 1 CLUF: 1
INDEX NAME: ELE_MANAGE_BRANCH_N2 COL#: 3
    TOTAL :: LVLS: 0   #LB: 1 #DK: 16 LB/K: 1 DB/K: 1 CLUF: 1
INDEX NAME: ELE_MANAGE_BRANCH_PK COL#: 2
    TOTAL :: LVLS: 0   #LB: 1 #DK: 16 LB/K: 1 DB/K: 1 CLUF: 1
***********************
Table stats    Table: ELE_PAYOFF_KIND   Alias: PK
TOTAL :: CDN: 6 NBLKS: 5 AVG_ROW_LEN: 293
-- Index stats
INDEX NAME: ELE_PAYOFF_KIND_N1 COL#: 1
    TOTAL :: LVLS: 0   #LB: 1 #DK: 1 LB/K: 1 DB/K: 1 CLUF: 1
INDEX NAME: ELE_PAYOFF_KIND_N2 COL#: 3
    TOTAL :: LVLS: 0   #LB: 1 #DK: 6 LB/K: 1 DB/K: 1 CLUF: 1
INDEX NAME: ELE_PAYOFF_KIND_PK COL#: 2
    TOTAL :: LVLS: 0   #LB: 1 #DK: 6 LB/K: 1 DB/K: 1 CLUF: 1
***********************
Table stats    Table: ELE_BUDGET_SUBJECT   Alias: BS
TOTAL :: CDN: 1451 NBLKS: 58 AVG_ROW_LEN: 312
-- Index stats
INDEX NAME: ELE_BUDGET_SUBJECT_N1 COL#: 1
    TOTAL :: LVLS: 1   #LB: 4 #DK: 1 LB/K: 4 DB/K: 58 CLUF: 58
INDEX NAME: ELE_BUDGET_SUBJECT_N2 COL#: 3
    TOTAL :: LVLS: 1   #LB: 4 #DK: 1451 LB/K: 1 DB/K: 1 CLUF: 1333
INDEX NAME: ELE_BUDGET_SUBJECT_PK COL#: 2
    TOTAL :: LVLS: 1   #LB: 10 #DK: 1451 LB/K: 1 DB/K: 1 CLUF: 1423
***********************
Table stats    Table: ELE_ENTERPRISE   Alias: EE
TOTAL :: CDN: 832 NBLKS: 43 AVG_ROW_LEN: 358
-- Index stats
INDEX NAME: ELE_ENTERPRISE_N1 COL#: 1
    TOTAL :: LVLS: 1   #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 800
INDEX NAME: ELE_ENTERPRISE_N2 COL#: 3
    TOTAL :: LVLS: 1   #LB: 3 #DK: 832 LB/K: 1 DB/K: 1 CLUF: 382
INDEX NAME: ELE_ENTERPRISE_PK COL#: 2
    TOTAL :: LVLS: 1   #LB: 6 #DK: 832 LB/K: 1 DB/K: 1 CLUF: 814
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
TABLE: ELE_ENTERPRISE     ORIG CDN: 832 ROUNDED CDN: 832 CMPTD CDN: 832
Access path: tsc Resc: 6 Resp: 6
Access path: index (iff)
      Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
      RSC_CPU: 0   RSC_IO: 4
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 4 Resp: 4
Access path: index (iff)
      Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
      RSC_CPU: 0   RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (no sta/stp keys)
      Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
      RSC_CPU: 0   RSC_IO: 26
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
      Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
      RSC_CPU: 0   RSC_IO: 7
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
      Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
      RSC_CPU: 0   RSC_IO: 26
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
      Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
      RSC_CPU: 0   RSC_IO: 7
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
BEST_CST: 2.00 PATH: 23 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
TABLE: ELE_BUDGET_SUBJECT     ORIG CDN: 1451 ROUNDED CDN: 1451 CMPTD CDN: 1451
Access path: tsc Resc: 7 Resp: 7
Access path: index (iff)
      Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
      RSC_CPU: 0   RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (iff)
      Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
      RSC_CPU: 0   RSC_IO: 3
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 3 Resp: 3
Access path: index (no sta/stp keys)
      Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
      RSC_CPU: 0   RSC_IO: 5
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
      Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
      RSC_CPU: 0   RSC_IO: 11
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
      Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
      RSC_CPU: 0   RSC_IO: 5
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
      Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
      RSC_CPU: 0   RSC_IO: 11
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
BEST_CST: 2.00 PATH: 23 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
TABLE: ELE_PAYOFF_KIND     ORIG CDN: 6 ROUNDED CDN: 6 CMPTD CDN: 6
Access path: tsc Resc: 2 Resp: 2
Access path: index (iff)
      Index: ELE_PAYOFF_KIND_N1
TABLE: ELE_PAYOFF_KIND
      RSC_CPU: 0   RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (iff)
      Index: ELE_PAYOFF_KIND_PK
TABLE: ELE_PAYOFF_KIND
      RSC_CPU: 0   RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (no sta/stp keys)
      Index: ELE_PAYOFF_KIND_N1
TABLE: ELE_PAYOFF_KIND
      RSC_CPU: 0   RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
      Index: ELE_PAYOFF_KIND_PK
TABLE: ELE_PAYOFF_KIND
      RSC_CPU: 0   RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
      Index: ELE_PAYOFF_KIND_N1
TABLE: ELE_PAYOFF_KIND
      RSC_CPU: 0   RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
      Index: ELE_PAYOFF_KIND_PK
TABLE: ELE_PAYOFF_KIND
      RSC_CPU: 0   RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
BEST_CST: 1.00 PATH: 4 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
TABLE: ELE_MANAGE_BRANCH     ORIG CDN: 16 ROUNDED CDN: 16 CMPTD CDN: 16
Access path: tsc Resc: 2 Resp: 2
Access path: index (iff)
      Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
      RSC_CPU: 0   RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (iff)
      Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
      RSC_CPU: 0   RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Access path: iff Resc: 2 Resp: 2
Access path: index (no sta/stp keys)
      Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
      RSC_CPU: 0   RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
      Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
      RSC_CPU: 0   RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
      Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
      RSC_CPU: 0   RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
      Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
      RSC_CPU: 0   RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
BEST_CST: 1.00 PATH: 4 Degree: 1

d、综合计划：
这一部分开始是10053 最大的一部分，在这里CBO 会评估各种JOIN 方式及顺序的成
本。

Join order[1]: ELE_PAYOFF_KIND [PK] ELE_MANAGE_BRANCH [MB] ELE_ENTERPRISE [EE] ELE_BUDGET_SUBJECT [BS]
Now joining: ELE_MANAGE_BRANCH [MB] *******
NL Join
Outer table: cost: 1 cdn: 6 rcz: 0 resp: 1
Inner table: ELE_MANAGE_BRANCH
    Access path: tsc Resc: 2
    Join: Resc: 13 Resp: 13
Access path: index (iff)
      Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
      RSC_CPU: 0   RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_MANAGE_BRANCH
    Access path: iff Resc: 2
    Join: Resc: 13 Resp: 13
Access path: index (iff)
      Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
      RSC_CPU: 0   RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_MANAGE_BRANCH
    Access path: iff Resc: 2
    Join: Resc: 13 Resp: 13
Access path: index (no sta/stp keys)
      Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
      RSC_CPU: 0   RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
    Join: resc: 7 resp: 7
Access path: index (no sta/stp keys)
      Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
      RSC_CPU: 0   RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
    Join: resc: 7 resp: 7
Access path: index (no sta/stp keys)
      Index: ELE_MANAGE_BRANCH_N1
TABLE: ELE_MANAGE_BRANCH
      RSC_CPU: 0   RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
      Index: ELE_MANAGE_BRANCH_PK
TABLE: ELE_MANAGE_BRANCH
      RSC_CPU: 0   RSC_IO: 1
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join cardinality: 96 = outer (6) * inner (16) * sel (1.0000e+000) [flag=0]
Best NL cost: 7 resp: 7
Join result: cost: 7 cdn: 96 rcz: 0
Now joining: ELE_ENTERPRISE [EE] *******
NL Join
Outer table: cost: 7 cdn: 96 rcz: 0 resp: 7
Inner table: ELE_ENTERPRISE
    Access path: tsc Resc: 6
    Join: Resc: 583 Resp: 583
Access path: index (iff)
      Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
      RSC_CPU: 0   RSC_IO: 4
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_ENTERPRISE
    Access path: iff Resc: 4
    Join: Resc: 391 Resp: 391
Access path: index (iff)
      Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
      RSC_CPU: 0   RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_ENTERPRISE
    Access path: iff Resc: 2
    Join: Resc: 199 Resp: 199
Access path: index (no sta/stp keys)
      Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
      RSC_CPU: 0   RSC_IO: 26
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
    Join: resc: 2503 resp: 2503
Access path: index (no sta/stp keys)
      Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
      RSC_CPU: 0   RSC_IO: 7
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
    Join: resc: 679 resp: 679
Access path: index (no sta/stp keys)
      Index: ELE_ENTERPRISE_N1
TABLE: ELE_ENTERPRISE
      RSC_CPU: 0   RSC_IO: 26
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
      Index: ELE_ENTERPRISE_PK
TABLE: ELE_ENTERPRISE
      RSC_CPU: 0   RSC_IO: 7
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join cardinality: 79872 = outer (96) * inner (832) * sel (1.0000e+000) [flag=0]
Best NL cost: 199 resp: 199
Join result: cost: 199 cdn: 79872 rcz: 0
Now joining: ELE_BUDGET_SUBJECT [BS] *******
NL Join
Outer table: cost: 199 cdn: 79872 rcz: 0 resp: 199
Inner table: ELE_BUDGET_SUBJECT
    Access path: tsc Resc: 7
    Join: Resc: 559303 Resp: 559303
Access path: index (iff)
      Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
      RSC_CPU: 0   RSC_IO: 2
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_BUDGET_SUBJECT
    Access path: iff Resc: 2
    Join: Resc: 159943 Resp: 159943
Access path: index (iff)
      Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
      RSC_CPU: 0   RSC_IO: 3
IX_SEL: 0.0000e+000 TB_SEL: 1.0000e+000
Inner table: ELE_BUDGET_SUBJECT
    Access path: iff Resc: 3
    Join: Resc: 239815 Resp: 239815
Access path: index (no sta/stp keys)
      Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
      RSC_CPU: 0   RSC_IO: 5
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
    Join: resc: 399559 resp: 399559
Access path: index (no sta/stp keys)
      Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
      RSC_CPU: 0   RSC_IO: 11
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
    Join: resc: 878791 resp: 878791
Access path: index (no sta/stp keys)
      Index: ELE_BUDGET_SUBJECT_N1
TABLE: ELE_BUDGET_SUBJECT
      RSC_CPU: 0   RSC_IO: 5
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
      Index: ELE_BUDGET_SUBJECT_PK
TABLE: ELE_BUDGET_SUBJECT
      RSC_CPU: 0   RSC_IO: 11
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Join cardinality: 115894272 = outer (79872) * inner (1451) * sel (1.0000e+000) [flag=0]
Best NL cost: 159943 resp: 159943
Join result: cost: 159943 cdn: 115894272 rcz: 0
Best so far: TABLE#: 0 CST:          1 CDN:          6 BYTES:          0
Best so far: TABLE#: 1 CST:          7 CDN:         96 BYTES:          0
Best so far: TABLE#: 2 CST:        199 CDN:      79872 BYTES:          0
Best so far: TABLE#: 3 CST:     159943 CDN: 115894272 BYTES:          0

第1 行为JOIN 方式
第2 行为驱动表的成本，行数，行大小。这里的行数为16，平均行长原本为20，但
是因为DEPT 表包含(DEPTNO, DEPT, and LOC)3 列但仅有DEPTNO,DEPT 等2 列需
要被join，所以计算后平均行长为16，所以在这里也被称为low row size.
第3 行到16 行通过NL JOIN 的成本计算公式，计算出几种不同join 方法的成本。

所以在这里HA JOIN 会被选做最优化的执行路径，SQL 语句将会最终走HA JOIN.
多重JOIN:
如果出现大于两个表进行JOIN 的情况，那么会有更多的join 顺序被考虑，4 个表join
的话会有24 种join 顺序，5 个表的话会有120 个join 顺序，n 个表会有n!个join 顺
序。由于估算每种join 顺序都会耗费cpu，所以oracle 用一个初始化参数
optimizer_max_permutations 来限制最大计算join 顺序。

个人理解，在做多表的时候，每个表的组合越来越多，本部分的信息就更多。