ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 10053诊断事件

oracle 10053诊断事件

原创 Linux操作系统 作者:foxmile 时间:2008-01-25 15:32:08 0 删除 编辑
本文参考王海《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 顺序。

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/79499/viewspace-160114/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 关于绑定变量
请登录后发表评论 登录
全部评论

注册时间:2007-12-10

  • 博文量
    106
  • 访问量
    197865