ORACLE 诊断事件及深入解析 10053事件


ORACLE 诊断事件及深入解析 10053 事件 文档来源: http://www.itpub.net/323537,1.html 发表者: wanghai,liyongdong 整理者: vongates 说明:其中深入解析 10053 事件部分来自 wanghai 在 cnoug 作品 原文连接:http://www.cnoug.org/viewthread.php?tid=44302 Oracle 为 RDBMS 提供了多种的诊断工具,诊断事件(Event)是其中一种常用、好用的 方法,它使 DBA 可以方便的转储数据库各种结构及跟踪特定事件的发生. 一、Event 的通常格式及分类 1、 通常格式如下: EVENT="<事件名称><动作><跟踪项目><范围限定>" 2、 Event 分类 诊断事件大体上可以分为四类: a. 转储类事件:它们主要用于转储 Oracle 的一些结构,例如转储一下控制文件、数 据文件头等内容。 b. 捕捉类事件:它们用于捕捉一些 Error 事件的发生,例如捕捉一下 ORA-04031 发 生时一些 Rdbms 信息,以判断是 Bug 还是其它原因引起的这方面的问题。 c. 改变执行途径类事件:它们用于改主一些 Oracle 内部代码的执行途径,例如设置 10269 将会使 Smon 进程不去合并那些 Free 的空间。 d. 跟踪类事件:这们用于获取一些跟踪信息以用于 Sql 调优等方面,最典型的便是 10046 了,将会对 Sql 进行跟踪。 3、 说明: a. 如果 immediate 放在第一个说明是无条件事件,即命令发出即转储到跟踪文件。 b. trace name 位于第二、三项,除它们外的其它限定词是供 Oracle 内部开发组用的。 c. level 通常位于 1-10 之间(10046 有时用到 12),10 意味着转储事件所有的信息。 例如当转储控制文件时,level1 表示转储控制文件头,而 level 10 表明转储控制文件 全部内容。 d. 转储所生成的 trace 文件在 user_dump_dest 初始化参数指定的位置。 二、说一说设置的问题了 可以在 init.ora 中设置所需的事件,这将对所有会话期打开的会话进行跟踪,也可以 用 alter session set event 等方法设置事件跟踪,这将打开正在进行会话的事件跟踪。 1、 在 init.ora 中设置跟踪事件的方法 a. 语法 EVENT=”event 语法|,level n|:event 语法|,level n|…” b. 举例 event=”10231 trace name context forever,level 10’ c. 可以这样设置多个事件: EVENT="\ 10231 trace name context forever, level 10:\ 10232 trace name context forever, level 10" 2、 通过 Alter session/system set events 这种方法 举个例子大家就明白了 Example: Alter session set events ‘immediate trace name controlf level 10’; Alter session set events ‘immediate trace name blockdump level 112511416’; (*) 在 oracle8x 及之上的版本也有这样的语句: Alter system dump datafile 13 block 15;实现的功能与(*)是类似的。 3、 使用 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,''); 4、 使用 Oradebug 来设置诊断事件的方法 同样举个例子大家就明白了: a. 找到 spid SQL>select username, sid, serial#, paddr from v$session where username='qiuyb'; USERNAME SID SERIAL# PADDR -------------------------------------------------------- HRB3 265 910 C000000084435AD8 SQL>SELECT ADDR,PID,SPID FROM V$PROCESS WHERE ADDR= C000000084435AD8'; ADDR PID SPID ------------------------------------------ C000000084435AD8 91 4835 b. 设置事件,以 10046 为例 sqlplus /nolog SQL>connect / as sysdba; SQL>oradebug setospid 4835 SQL>oradebug unlimit --不限制转储文件的大小 SQL> oradebug event 10046 trace name context forever,level 12 --设置事件进行 sql 跟踪 SQL> oradebug event 10046 trace name context off --关闭跟踪 注意不要用 oradug 去跟踪 oracle 的 smon,pmon 等几个进程,操作不当可能会杀掉这 几个后台进和引起宕库。 三、你可能的问题 1、 我如何知道在系统中设置了哪些 event? 回答: a. 如果你的事件是在 init.ora 中设置的可以用 SQL>show parameter event; 来查看 b. Michael R.Ault 给的 SQL serveroutput on size 1000000 declare event_level number; begin for i in 10000..10999 loop sys.dbms_system.read_ev(i,event_level); if (event_level > 0) then dbms_output.put_line('Event '||to_char(i)||' set at level '|| to_char(event_level)); end if; end loop; end; / 2、 在 oracle9i 中使用 spfile 的那种如何设置诊断事件呢? 回答: 简单,Alter system 命令就可以完成 alter system set event='10046 trace name context forever, level 12' scope=spfile; 重启一下就生效了。 3、 坏了,我的 9i 设置完诊断事件,起不来了,报 ORA-02194 错怎么办? 回答: 那你一定是在使用 Alter system 时把某一项写错了,比如把 context 写成了 conetxt 了, 可以做如下的解决: a.由 spfile 生成 pfile SQL>create pfile from spfile; File created. b.编辑 pfile 以修正错误 Change... *.event='10046 trace name conetxt forever, level 12' -to- *.event='10046 trace name context forever, level 12' c.用 pfile 启动 SQL>startup pfile=/..... d.重新生成 SPFILE. SQL>create spfile from pfile; File created. 不同事件 level 有不同的意思 例如:10046 level 1:跟踪 sql 语句,包括解析、执行、提取、提交和回滚等。 level 4:包括变量的详细信息 level 8:包括等待事件 level 12:包括绑定变量与等待事件 我们可以通过下面的语句在 SQLPLUS 中来得到可用 Oracle 诊断事件列表 SET SERVEROUTPUT ON DECLARE err_msg VARCHAR2(120); BEGIN dbms_output.enable (1000000); FOR err_num IN 10000..10999 LOOP err_msg := SQLERRM (-err_num); IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN dbms_output.put_line (err_msg); END IF; END LOOP; END; / -------------------------------------------------------------- 通常事件不需要 Oracle Support ,例如: 1、分析数据文件块,转储数据文件 n 的块 m alter system dump datafile n block m 2、分析日志文件 alter system dump logfile logfilename; 3、分析控制文件的内容 alter session set events 'immediate trace name CONTROLF level 10' 4、分析所有数据文件头 alter session set events 'immediate trace name FILE_HDRS level 10' 5、分析日志文件头 alter session set events 'immediate trace name REDOHDR level 10' 6、分析系统状态,最好每 10 分钟一次,做三次对比 alter session set events 'immediate trace name SYSTEMSTATE level 10' 7、分析进程状态 alter session set events 'immediate trace name PROCESSSTATE level 10' 8、分析 Library Cache 的详细情况 alter session set events 'immediate trace name library_cache level 10' 深入解析 10053 事件 你是否想知道一句 sql 语句如何执行,它是否走索引,是否采用不同得驱动表,是否 用 nestloop join,hash join…..?这一切对你是否很神秘呢?或许你会说 execution plan 能 看到这些东西,但是你是否清楚 execution plan 是如何得到?这篇文章就是给出了隐 藏在 execution plan 底下的具体实现。 10053 事件 10053 事件是 oracle 提供的用于跟踪 sql 语句成本计算的内部事件,它能记载 CBO 模 式下 oracle 优化器如何计算 sql 成本,生成相应的执行计划。 如何设置 10053 事件 设置本 session 的 10053 开启: Alter session set events’10053 trace name context forever[,level {1/2}]’; 关闭: Alter session set events’10053 trace name context off’; 设置其他 session 的 10053 开启: SYS.DBMS_SYSTEM.SET_EV (, , 10053, {1|2}, '') 关闭: SYS.DBMS_SYSTEM.SET_EV (, , 10053,0, '') 跟其他跟踪事件不同,10053 提供了两个跟踪级别,但是级别 2 的跟踪信息比级别 1 少(其他跟踪事件如 10046 跟踪级别越高信息越多),跟踪信息将被记 录到 user_dump_dest 目录底下。注意,要实现跟踪必须满足两个条件:sql 语句必须被 hard parse 并且必须使用 CBO 优化器模式。如果 sql 语句已经被 parse 过,那么 10053 不生 成跟踪信息。如果你使用 RULE 优化器,那么 10053 也不会生成跟踪信息。 跟踪内容 跟踪文件包括 6 部分: Sql 语句 优化器相关参数 基本统计信息 基本表访问成本 综合计划 特殊功能的成本重计算 这篇文章将会涉及到前 4 项和一部分第 5 项的内容,我们将会用以下语句作为例子: select dname, ename from emp, dept where emp.deptno = dept.deptno and ename = :b1 sql 语句: 这部分是整个跟踪文件里最容易理解的部分,包括了所执行的 sql 语句,如果你采用 RULE 模式优化器,那么除了这一部分外将不会有多余信息出现在跟踪文件里。 优化器相关参数: 记载了所有影响成本计算的参数 *************************************** PARAMETERS USED BY THE OPTIMIZER ******************************** OPTIMIZER_FEATURES_ENABLE = 8.1.6 OPTIMIZER_MODE/GOAL = Choose OPTIMIZER_PERCENT_PARALLEL = 0 HASH_AREA_SIZE = 131072 HASH_JOIN_ENABLED = TRUE HASH_MULTIBLOCK_IO_COUNT = 0 OPTIMIZER_SEARCH_LIMIT = 5 PARTITION_VIEW_ENABLED = FALSE _ALWAYS_STAR_TRANSFORMATION = FALSE _B_TREE_BITMAP_PLANS = FALSE STAR_TRANSFORMATION_ENABLED = FALSE _COMPLEX_VIEW_MERGING = FALSE _PUSH_JOIN_PREDICATE = FALSE PARALLEL_BROADCAST_ENABLED = FALSE OPTIMIZER_MAX_PERMUTATIONS = 80000 OPTIMIZER_INDEX_CACHING = 0 OPTIMIZER_INDEX_COST_ADJ = 100 QUERY_REWRITE_ENABLED = TRUE QUERY_REWRITE_INTEGRITY = ENFORCED _INDEX_JOIN_ENABLED = FALSE _SORT_ELIMINATION_COST_RATIO = 0 _OR_EXPAND_NVL_PREDICATE = FALSE _NEW_INITIAL_JOIN_ORDERS = FALSE _OPTIMIZER_MODE_FORCE = TRUE _OPTIMIZER_UNDO_CHANGES = FALSE _UNNEST_SUBQUERY = FALSE _PUSH_JOIN_UNION_VIEW = FALSE _FAST_FULL_SCAN_ENABLED = TRUE _OPTIM_ENHANCE_NNULL_DETECTION = TRUE _ORDERED_NESTED_LOOP = FALSE _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 = FALSE _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 = FALSE _SORTMERGE_INEQUALITY_JOIN_OFF = FALSE _DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE _ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE DB_FILE_MULTIBLOCK_READ_COUNT = 32 SORT_AREA_SIZE = 131072 基本统计信息: 下一部分是所有表和索引的基本统计信息 基本统计信息包括 表: 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: DEPT Alias: DEPT TOTAL :: CDN: 16 NBLKS: 1 TABLE_SCAN_CST: 1 AVG_ROW_LEN: 20 -- Index stats INDEX#: 23577 COL#: 1 TOTAL :: LVLS: 0 #LB: 1 #DK: 16 LB/K: 1 DB/K: 1 CLUF: 1 *********************** Table stats Table: EMP Alias: EMP TOTAL :: CDN: 7213 NBLKS: 85 TABLE_SCAN_CST: 6 AVG_ROW_LEN: 36 -- Index stats INDEX#: 23574 COL#: 1 TOTAL :: LVLS: 1 #LB: 35 #DK: 7213 LB/K: 1 DB/K: 1 CLUF: 4125 INDEX#: 23575 COL#: 2 TOTAL :: LVLS: 1 #LB: 48 #DK: 42 LB/K: 1 DB/K: 36 CLUF: 1534 INDEX#: 23576 COL#: 8 TOTAL :: LVLS: 1 #LB: 46 #DK: 12 LB/K: 3 DB/K: 34 CLUF: 418 *************************************** 基本表访问成本: 这里开始 CBO 将会计算单表访问的成本 单表访问路径 SINGLE TABLE ACCESS PATH ........................................................................................................................................ .1 Column: ENAME Col#: 2 Table: EMP Alias: EMP.....................................................................2 NDV: 42 NULLS: 0 DENS: 2.3810e-002 ...........................................................................3 TABLE: EMP ORIG CDN: 7213 CMPTD CDN: 172 ........................................................................................4 Access path: tsc Resc: 6 Resp: 6............................................................................................................5 Access path: index (equal) ...............................................................................................................................6 INDEX#: 23575 TABLE: EMP ...........................................................................................................................7 CST: 39 IXSEL: 0.0000e+000 TBSEL: 2.3810e-002.......................................................................8 BEST_CST: 6.00 PATH: 2 Degree: 1..............................................................................................................9 我们看一下上面是什么意思。首先 CBO 列出了 ename 列的统计信息(第 2,3 行),这些 统计信息来自 dba_tab_columns。 列的统计信息和 dba_tab_columns 中对应的列名如下 Trace label dba_tables column NDV NUM_DISTINCT 列的不重复值数 NULLS NUM_NULLS 列的空行数 DENS DENSITY 列密度,没有直方图的情况下= 1/NDV LO LOW_VALUE 列的最小值 (只对数字列) HI HIGH_VALUE 列的最大值 (只对数字列) 第 4 行出现了表的行数 ORIG CDN 和计算过的行数 CMPTD CDN (computed cardinality). 计算公司如下, CMPTD CDN = ORIG CDN * FF 在这里 FF 表示过滤因子(Filter Factor)。我们稍后再来看 FF 是什么及如何计算的。 第 5 行表示了全表扫描的成本。 这里的成本是 62, 是由 NBLKS 和 db_file_multi_block_read_count 初始化参数计算出来的。. 第 6-8 行是索引访问的成本。 第 9 行是总结了以上信息并选出了最优的访问路径为全表扫描,成本为 6。 表扫描成本 让我们来看一下全表扫描成本(tsc)是如何计算的 这里有其他两个大表的基本统计信 息。 TOTAL :: CDN: 115630 NBLKS: 4339 TABLE_SCAN_CST: 265 AVG_ROW_LEN: 272 TOTAL :: CDN: 454503 NBLKS: 8975 TABLE_SCAN_CST: 548 AVG_ROW_LEN: 151 你可能曾经看到过全表扫描成本= 访问的块数目/ db_file_multi_block_read_count. 看 起来这个等式很有意义因为 oracle 在做全表扫描时每个 I/O 请求将会读取 db_file_multi_block_read_count 个块。但 是,我们计算以上统计信息得到 NBLKS / TABLE_SCAN_CST = 4339 / 265 = 16.373 ≠ db_file_multi_block_read_count(这里的值是 32,可以看前面参数那一页) 另外一个表为 NBLKS / TABLE_SCAN_CST = 8975 / 548 = 16.377 全表扫描成本和 db_file_multi_block_read_count CBO 将会根据 NBLKS 和 db_file_multiblock_read_count 来估计全表扫描成本,但是 db_file_multiblock_read_count 通常会被打上折扣。实际上我们可以认为等式会是 TABLE_SCAN_CST = NBLKS / k 我们来看一下 k 和 db_file_multiblock_read_count 究竟有什么规律可寻。我们来做一 个实验,使用不同的 db_file_multiblock_read_count 值 4, 6,8, 12,16, 24,32 来测试。 过滤因子(FF) 为了理解索引访问成本我们需要了解一下过滤因子。 过滤因子是一个介于 0 和 1 之 间的数字,反映了记录的可选择性。如果一个列有 10 种不同的值,我们需要查询等 于其中某一个值的记录时,如果这 10 种值平均分 布的话,你将得到 1/10 的行数。 如果没有直方图,过滤因子为 FF = 1/NDV = density 再来看一下过滤因子和查询条件的关系 不使用绑定变量的情况: predicate Filter factor c1 = value 1/c1.num_distinct4 c1 like value 1/c1.num_distinct c1 > value (Hi - value) / (Hi - Lo) c1 >= value (Hi - value) / (Hi - Lo) + c1 < value (value - Lo) / (Hi - Lo) c1 <= value (value - Lo) / (Hi - Lo) + c1 between val1 and val2 (val2 – val1) / (Hi - Lo) + 使用绑定变量的情况(8i): predicate Filter factor col1 = :b1 col1.density col1 {like | > | >= | < | <=} :b1 {5.0000e-02 | col1. col1 between :b1 and :b2 5.0000e-02 * 5.0000e- 包含 and 和 or 的情况: predicate Filter factor predicate 1 and predicate 2 FF1 * FF2 predicate 1 or predicate 2 FF1 + FF2 – FF1 * FF2 索引访问成本: 现在我们知道了聚合因子的概念,我们再来看一看索引访问的成本 SINGLE TABLE ACCESS PATH ........................................................................................................................................ .1 Column: ENAME Col#: 2 Table: EMP Alias: EMP.....................................................................2 NDV: 42 NULLS: 0 DENS: 2.3810e-002 ...........................................................................3 TABLE: EMP ORIG CDN: 7213 CMPTD CDN: 172 ........................................................................................4 Access path: tsc Resc: 6 Resp: 6............................................................................................................5 Access path: index (equal) ...............................................................................................................................6 INDEX#: 23575 TABLE: EMP ...........................................................................................................................7 CST: 39 IXSEL: 0.0000e+000 TBSEL: 2.3810e-002.......................................................................8 BEST_CST: 6.00 PATH: 2 Degree: 1..............................................................................................................9 我们来看 6-8 行,这里表示了索引访问的成本。第 6 行表示这里采取索引 equal 的方 法来访问,再来回忆一下索引的基本统计信息 INDEX#: 23575 COL#: 2 TOTAL :: LVLS: 1 #LB: 48 #DK: 42 LB/K: 1 DB/K: 36 CLUF: 1534 根据索引成本计算公式 blevel + FF*leaf_blocks + FF*clustering_factor 1 + 2.3810e-002-2*48 + 2.3810e-002-2*1534 = 1 + 1.1429 + 36.5245 = 38.6674 这里的 FF 就等于 TBSEL=DENS=2.3810e-002,由于我们的查询条件为 ename = :b1 所 以得出 FF 为 ENAME 列的 DENS, 其实索引访问方式的成本计算公式 • Unique scan blevel+1 • Fast full scan leaf_blocks / k ( k = 1.6765x0.6581 ) • Index-only blevel + FF*leaf_blocks 让我们用别的例子证明一下索引成本计算,语句为 select … from tbl a where a.col#1 = :b1 and a.col#12 = :b2 and a.col#8 = :b3 索引和列的基本统计数据如下 INDEX# COL# LVLS #LB #DK LB/K DB/K CLUF 8417 27,1 1 13100 66500 1 22 1469200 8418 1,12,7 2 19000 74700 1 15 1176500 8419 3,1,4,2 2 31000 49700 1 2 118000 15755 1,12,8 1 12600 18800 1 30 1890275 8416 1,2,33,4,5,6 2 25800 1890300 1 1 83900 Col#: 1 NDV: 10 NULLS: 0 DENS: 1.0000e-001-1 Col#: 12 NDV: 8 NULLS: 0 DENS: 1.2500e-001 Col#: 8 NDV: 33 NULLS: 0 DENS: 3.0303e-001 Access path: index (scan)...................................................................................................................................1 INDEX#: 8418 CST: 14947 IXSEL: 1.2500e-002 TBSEL: 1.2500e-002 ........................................2 Access path: index (equal) ...............................................................................................................................3 INDEX#: 15755 CST: 7209 IXSEL: 0.0000e+000 TBSEL: 3.7879e-003 ......................................4 Access path: index (scan) .................................................................................................................................5 INDEX#: 8416 CST: 10972 IXSEL: 1.0000e-001 TBSEL: 1.0000e-001 ........................................6 5 个索引中,索引(#8417 and #8419) 将不会被考虑因为他们的首列不出现在查询条件 中。. INDEX# 8418 索引包含的 3 个列中只有 2 列出现在查询条件中,所以只用 2 列的 DENS 来计算过滤 因子 FF = 1.0000e-001 * 1.2500e-001= 1.2500e-002 cost = lvl + FF*#LB + FF*clustering factor = 2 + 19,000*1.2500e-002 + 1176500*1.2500e-002 = 2 + 237.5 + 14706.25 = 14945.75 INDEX# 15755 索引包含的 3 列都出现在查询条件中,用 3 列的 DENS 计算过滤因子 FF = 1.0000e-001 * 1.2500e-001 * 3.0303e-001 = 3.7879e-003 cost = lvl + FF*#LB + FF*clustering factor = 1 + 12,600*3.7879e-003 + 1,890,275*3.7879e-003 = 2 + 47.73 + 7160.13 = 7208.86 INDEX# 8416 索引包含的 3 个列中只有 1 列出现在查询条件中,所以只用 1 列的 DENS 来计算过滤 因子 FF = 1.0000e-001 cost = lvl + FF*#LB + FF*clustering factor = 2 + 25,800*1.0000e-001+ 83,900*1.0000e-001 = 2 + 2580 + 8390 = 10972 虽然索引 8416 只有一列出现在查询条件中,但是它的成本还是低于索引 8418,因为 它的聚合因子(clustering factor)比较低,所以统计出来成本也比较低。关于聚合因子可 以参考 oracle 官方文档。 综合计划: 这一部分开始是 10053 最大的一部分,在这里 CBO 会评估各种 JOIN 方式及顺序的成 本。 1. NL - NESTED LOOP JOIN join cost = cost of accessing outer table + (row number of outer table * cost of accessing inner table ) 2. SM – SORT MERGE JOIN join cost = (cost of accessing outer table + outer sort cost) + (cost of accessing inner table + inner sort cost) 3. HA – HASH JOIN join cost = (cost of accessing outer table) + (cost of building hash table) + (cost of accessing inner table ) JOIN ORDER [N] Join order[1]: DEPT [DEPT] EMP [EMP] Now joining: EMP [EMP] ******* JOINS – NL NL Join ........................................................................................................................................... ...................................1 Outer table: cost: 1 cdn: 16 rcz: 13 resp: 1..................................................................................2 Inner table: EMP ......................................................................................................................................... .............3 Access path: tsc Resc: 6 ...............................................................................................................................4 Join resc: 97 Resp: 97 ...............................................................................................................................5 Access path: index (join stp) ...........................................................................................................................6 INDEX#: 23575 TABLE: EMP ...........................................................................................................................7 CST: 39 IXSEL: 0.0000e+000 TBSEL: 2.3810e-002.......................................................................8 Join resc: 625 resp:625 .............................................................................................................................9 Access path: index (join index).....................................................................................................................10 INDEX#: 23576 TABLE: EMP .........................................................................................................................11 CST: 37 IXSEL: 0.0000e+000 TBSEL: 8.3333e-002.....................................................................12 Join resc: 593 resp:593 ...........................................................................................................................13 Access path: and-equal................................................................................................................................... 14 CST: 19 .............................................................................................................................................. .................15 Join resc: 305 resp:305 ...........................................................................................................................16 Join cardinality: 172 = outer (16) * inner (172) * sel (6.2500e-002) [flag=0].................17 Best NL cost: 97 resp: 97...............................................................................................................................18 第 1 行为 JOIN 方式 第 2 行为驱动表的成本,行数,行大小。这里的行数为 16,平均行长原本为 20,但 是因为 DEPT 表包含(DEPTNO, DEPT, and LOC)3 列但仅有 DEPTNO,DEPT 等 2 列需 要被 join,所以计算后平均行长为 16,所以在这里也被称为 low row size. 第 3 行到 16 行通过 NL JOIN 的成本计算公式,计算出几种不同 join 方法的成本。 1. Tablescan of EMP at a cost of 6: cost = cost of outer + cardinality of outer * cost of inner = 1 + 16 * 6 = 97 lines 3 to 5 2. Scan of index 23575 on ENAME at a cost of 39: cost = 1 + 16 * 39 = 625 lines 6 to 9 3. Scan of index 23576 on DEPTNO at a cost of 37: cost = 1 + 16 * 37 = 593 lines 10 to 13 4. An “and-equal” access at a cost of 19: cost = 1 + 16 * 19 = 305 lines 14 to 16 第 17 行 CBO 估算出这个 JOIN 结果集的记录数,它将被最为下一次 join 的输入。它 的计算公式为 Join cardinality:= outer * inner * join selectivity 而 join selectivity 为 join selectivity = 1/max[ NDV(t1.c1), NDV(t2.c2) ] * [ (card t1 - # t1.c1 NULLs) / card t1 ] * [ (card t2 - # t2.c2 NULLs) / card t2 ] Join cardinality 只会被用于 NL JOIN 中,其他 JOIN 会采取不同办法。 最后在 18 行,CBO 将会列出成本最低的 NL JOIN 的方法。 JOINS - SM SM Join Outer table: resc: 1 cdn: 16 rcz: 13 deg: 1 resp: 1 Inner table: EMP resc: 6 cdn: 172 rcz: 9 deg: 1 resp: 6 SORT resource Sort statistics Sort width: 3 Area size: 43008 Degree: 1 Blocks to Sort: 1 Row size: 25 Rows: 16 Initial runs: 1 Merge passes: 1 Cost / pass: 2 Total sort cost: 2 SORT resource Sort statistics Sort width: 3 Area size: 43008 Degree: 1 Blocks to Sort: 1 Row size: 20 Rows: 172 Initial runs: 1 Merge passes: 1 Cost / pass: 2 Total sort cost: 2 Merge join Cost: 10 Resp: 10 SM Join (with index on outer) Access path: index (no sta/stp keys) INDEX#: 23577 TABLE: DEPT CST: 2 IXSEL: 1.0000e+000 TBSEL: 1.0000e+000 Outer table: resc: 2 cdn: 16 rcz: 13 deg: 1 resp: 2 Inner table: EMP resc: 6 cdn: 172 rcz: 9 deg: 1 resp: 6 SORT resource Sort statistics Sort width: 3 Area size: 43008 Degree: 1 Blocks to Sort: 1 Row size: 20 Rows: 172 Initial runs: 1 Merge passes: 1 Cost / pass: 2 Total sort cost: 2 Merge join Cost: 10 Resp: 10 在 SM JOIN 中成本为 Cost of outer + cost of inner + sort cost for outer + sort cost for inner = 1+ 6 + 2 + 2 = 11. 在这里 CBO 减去 1 所以最终等于 10。在第 2 个 SM JOIN 的方法下通过了已经排序的 索引,所以成本为 2 + 6 + 0 (no sort on outer) + 2 = 10. JOINS – HA HA Join Outer table: resc: 1 cdn: 16 rcz: 13 deg: 1 resp: 1 Inner table: EMP resc: 6 cdn: 172 rcz: 9 deg: 1 resp: 6 Hash join one ptn: 1 Deg: 1 hash_area: 32 buildfrag: 33 probefrag: 1 ppasses: 2 Hash join Resc: 8 Resp: 8 Join result: cost: 8 cdn: 172 rcz: 22 根据 HA JOIN 公式,计算出成本为 (cost of accessing outer table)+ (cost of building hash table)+ (cost of accessing inner table ) =1+6+1=8 所以在这里 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 顺序。若想了解多重 join 的更多信息, 请搜索相关 sql 调整的资料。 结论: 10053 是一个很好的理解 CBO 工作机制的工具,如果辅以 10046 事件查看执行计划, 那么整个 sql 语句从解析到执行的过程都一目了然了
还剩17页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

zhjiafe

贡献于2012-09-26

下载需要 8 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf