【IT168 技术文章】
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. 可以这样设置多个事件：
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 blockdump level 112511416’; (*)
在oracle8x 及之上的版本也有这样的语句：
Alter system dump datafile 13 block 15; 实现的功能与(*)是类似的。
3、使用DBMS_SYSTEM.SET_EV 的方法
a. 过和定义如下
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.
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 诊断事件列表
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 (
关闭：
SYS.DBMS_SYSTEM.SET_EV (
跟其他跟踪事件不同，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
_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
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
_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 将会计算单表访问的成本
单表访问路径
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 中对应的列名如下
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
再来看一下过滤因子和查询条件的关系
不使用绑定变量的情况:
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):
col1 = :b1 col1.density
col1 {like | > | >= | < | <=} :b1 {5.0000e-02 | col1.
col1 between :b1 and :b2 5.0000e-02 * 5.0000e-
包含and 和or 的情况:
predicate 1 and predicate 2 FF1 * FF2
predicate 1 or predicate 2 FF1 + FF2 – FF1 * FF2
索引访问成本
现在我们知道了聚合因子的概念，我们再来看一看索引访问的成本
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
让我们用别的例子证明一下索引成本计算，语句为
where a.col#1 = :b1
and a.col#12 = :b2
and a.col#8 = :b3
索引和列的基本统计数据如下
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) ...........................................................
