oracle_cbo&rbo详解
ORACLE cbo成本计算
tes=103)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST1_N1' (NON-UNIQUE) (Cost=
SQL> declare
2 i number;
3 begin
4 i:=1;
5 for j in 1..5000 loop
6 i:=mod(j,250);
7 insert into test2 values(i,'test');
8 insert into test3 values(i);
1 0 TABLE ACCESS (FULL) OF 'TEST2' (Cost=9 Card=20 Bytes=2060)
SQL> select /*+ index (test2 IDX_TEST2_N1)*/ * from test2 where n1 = 100;
Execution Plan
TEST1 5000 1 1 76
TEST2 5000 1 20 5000
表已创建。
SQL> create table test3(n1 number(10));
表已创建。
begin
for i in 1..5000 loop
insert into test1 values(i,'test');
end loop;
end;
/
PL/SQL 过程已成功完成。
----------------------------------------------------------
优化器模式optimizer_mode
优化器模式optimizer_modeoracle的optimizer_mode参数说明：Syntax OPTIMIZER_MODE ={ first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows }Default value all_rows在oracle中，sql语句优化分成RBO（Rule-Based Optimization）基于规则的优化和CBO（Cost-Based Optimization）基于代价的优化。
在较早的oracle的版本中，oracle是采取基于规则的优化，根据oracle指定的规则优先顺序，对于指定的表进⾏执⾏计划的选择。
⽐如在规则中，索引的优先级⼤于全表扫描，那在查询某张拥有所有的表的时候，那就⼀定是使⽤索引。
在后来，oracle发现这样的做法并不科学，规则是死的，数据是活的，并不是说在所有的情况下使⽤规则都是可⾏的。
于是oracle开始推出了基于代价的优化，收集对象的统计信息并分析得出最优的执⾏计划。
从oracle9i开始，oracle就强烈建议使⽤CBO，这点从oracle10g和oracle11g的⽂档中关于优化参数optimizer_mode完全不提与RBO有关的⼏个选项就可以看出。
（有⼈说CBO考虑的代价主要是cpu和内存，这点不太赞同，在sql执⾏中，我们最需要考虑的⼀定是和io有关的部分，所以考虑的⽅向应该是逻辑读。
在下⾯的⼀个测试例⼦中可以看出RBO和CBO的⼀些区别）optimizer_mode决定了oracle使⽤RBO还是CBO，可选值如下：Choose:这是RBO和CBO“中间”的⼀种模式，具体是这样：1.当所包含的所有对象有统计信息时，那就是⽤CBO的优化⽅式。
2.当所包含的部分对象有统计信息时，也使⽤CBO的⽅式，并且对剩下的对象的统计信息进⾏“猜测”。
3.如果包含所有对象都没有统计信息时，使⽤RBO的⽅式。
oracle cbo原理
oracle cbo原理
Oracle中的CBO（Cost-Based Optimizer）是一种查询优化器，它负责分析SQL查询语句并确定最有效的执行计划。
CBO的原理涉
及多个方面，包括统计信息、成本估算和执行计划选择。
首先，CBO使用统计信息来了解表和索引的数据分布情况，这
些统计信息包括表的大小、列的基数、索引的选择度等。
这些统计
信息有助于CBO评估不同执行计划的成本，并选择最佳执行计划。
其次，CBO通过成本估算来评估不同执行计划的代价。
成本估
算考虑了多个因素，包括I/O成本、CPU成本和内存成本等。
CBO会
根据这些成本估算来比较不同执行计划的代价，从而选择最佳执行
计划。
最后，CBO根据成本估算选择最佳的执行计划。
它会考虑查询
的复杂性、索引的利用情况、连接顺序等因素，以确定最终的执行
计划。
CBO的目标是选择一个执行计划，使得查询的总成本最小化，从而提高查询的性能。
总的来说，CBO的原理涉及统计信息的收集、成本估算和执行
计划选择。
通过这些步骤，CBO能够为查询选择最有效的执行计划，从而提高数据库查询的性能。
Oracle RBO、CBO简介
我们考虑另一种情况，如果一百万数据行实际不是在4种deptno值间平均分配，其中有99万行对应着值10，5000行对应值20，3000行对应值30，2000行对应值40。在这种数据分布图案中对除值为10外的其它deptno值搜索时，毫无疑问，如果索引能被应用，那么效率会高出很多。我们可以采用对该索引列进行单独分析，或用analyze语句对该列建立直方图，对该列搜集足够的统计数据，使ORACLE在搜索选择性较高的值能用上索引。
9、使用CBO 时，必须保证为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析，可用SQL语句“analyze table xxx compute statistics for all indexes;"ORACLE掌握了充分反映实际的统计数据，才有可能做出正确的选择。
1、RBO自ORACLE 6版以来被采用，有着一套严格的使用规则，只要你按照它去写SQL语句，无论数据表中的内容怎样，也不会影响到你的“执行计划”，也就是说对数据不“敏感”；CBO计算各种可能“执行计划”的“代价”，即cost，从中选用cost最低的方案，作为实际运行方案。各“执行计划”的cost的计算根据，依赖于数据表中数据的统计分布，ORACLE数据库本身对该统计分布并不清楚，必须要分析表和相关的索引（使用ANALYZE 命令），才能搜集到CBO所需的数据。
4、如果一个语句使用 RBO的执行计划确实比CBO 好，则可以通过加 " rule" 提示，强制使用RBO。
5、使用CBO 时，SQL语句 "FROM" 子句后面的表，必须全部使用ANALYZE 命令分析过，如果"FROM" 子句后面的是视图，则此视图的基础表，也必须全部使用ANALYZE 命令分析过；否则，ORACLE 会在执行此SQL语句之前，自动进行ANALYZE 命令分析，这会极大导致SQL语句执行极其缓慢。
基于CBO的Oracle的成本分析
福
建 电
脑
２ １ 年第 ２期 ０１
基 于 Ｃ Ｏ的 Ｏ ａｌ Ｂ ｒｃｅ的成 本 分 析
钟 明 ．杨 邦 荣
（中南 大学信 息科 学与工程 学院 湖 南 长沙 ４ ０ ８ １ ０３）
【 摘 要 】 本 文 首先介 绍 了 Ｏｒｃ ： ａｌ 于成本的优 化 器 Ｃ Ｏ， ｅ基 Ｂ 并列举 出了不 同版 本计 算成本 的公 式。并
在这 里 ． 他 的数据 都可 以通Ｏａｌ ｉ 始 ， ｒｅ ８ 开 ｅ 引入 了基 于成 本 的优 化器 Ｃ Ｏ． 只有 Ｃ Ｕ 的运转 次数 （Ｃ Ｕ ｙｌｓ 无 法获 取 ． 里指 Ｂ Ｐ ＃Ｐ Ｃｃ ） ｅ 这 的是更 改优 化器 执行 计划 时所 需要 消 耗 的 Ｃ Ｕ资 源 ． Ｐ 他 的思 路是 让 Ｏ ａｌ ｒｃ ｅ获取 所 有 执 行计 划 的相 关 信息 ．
全 表 扫描 ：下 面的查 询语 句是 一个 包 含 了复 杂的 级 。执行 的 Ｓ Ｌ语句 符合 哪 些规 则 ． 按照 哪个 规则 Ｑ 就 来制定 相应 的计划 。 Ｏ ａｌ１Ｇ ２开 始 ． 动了 自动 过 滤条件 的全 表扫描 语句 ： 从 ｒｃ ０ Ｒ ｅ 启
统 计 收集 ． 以提高 基于成 本优 化器 的效率ｆ 可 １ Ｊ
记 录与过 滤条件 进行 匹配 ：获取 匹配 成功 的记 录上 所 Ｉ Ｏ表示 物理 Ｉ ／ Ｏ请求 ． Ｐ Ｃ Ｕ表示 逻 辑 Ｉ 求 。 ｅ Ｏ请 Ｎ． 需要 查询 的 字段 ；因此 ， 由这 ４个操 作 步骤 ， 构成 了 就 ｔ Ｉ Ｏ表示通 过数据 库访 问远 程数 据库 的逻辑 Ｉ ／ ０请 求 ． 全 表扫描 下 的 ４个 Ｃ Ｕ消 耗 的部 分 。 到 以下公式 ： Ｐ 得 Ｃ Ｏ会尝试 计算 所有 可 能执行 计 划 的物理 １ 选 择最 Ｂ ０． ＃ Ｐ Ｃ Ｃ Ｅ ＝ ｎ ｍ ｒ ｆ ｌ ｋ ｏｂ ｅｄｆ ｍ ｄｓ） （ ｃｅ ＣＵ Ｙ ＬＳ ｕ ｂ ｏ ｓｔ ｅｒａ ｏ ｉ ｃ ｌｓｔ ｅ ｏｂｃ ｒ ｋ ｙ ｏ 小 物理 １ ０的计 划 。 由公 式 可知 ． ｒｃｅｉ ｃ ｓ 仅仅 ｒａ ｎ ｌｃ ｒｍ ｄｓ）（ｕ ｂ ｒｏ ｏ ｓｔ ｒ ｅｓ ） （ ｙｌ ｏ Ｏ ａ ｌ８ 的 ｏｔ ｅｄ ｏｅｂｏｋｆ ｉ ＋ｎ ｍ ｆｒｗ ｏｂ ｐｏ ｓｅ （ ｃｅ ｔ ｏ ｋ ｅ ｅ ｃ ｄ ｃ ｓ 是 执行 Ｓ Ｌ的 １ Ｑ ０次 数 ．该 计算 方式 存 在一 些 缺陷［， ｐｏｅｓ１ｒｗ ＋ｎ ｍｂｒｏ ｏ ｓｔ ｅｐｅ ｉａ ｄ （ ｃ ｓｔ ｒｄｃｔ ３ ］ ｒｃｓ ｏ ）（ｕ ｅ ｆｒｗ ｂ ｒ ｃｔ ） ｃ ｌ ｏｐｅｉａ １ ｏ ｄ ｅ ｙｅ ｅ ｒｗ）（ｙｌｓｔ ｐｏｅｔ ｏｕ ｎ ） ｏ ） ｅｃｅ ｒｊｃ ｃｌｍ ｓ ＋ ｏ 具 体 如下 ： 取 的时候 ． 能 是读 取单 个 数 据块 。 者 读 可 或 是读 取 了多个数据 块 ， 两种 读取 方式 的 成本必 然不 同 ， ３ 构建估 算方 法进行 验证 、 在全 表扫描 中 ． ｒｃ Ｏａｌ ｅ高水 位 以下 的所 有数 据块 和 ｏａｌ８ 都 视 同单数 据块 处理 。Ｉ ｒ ｅｉ ｃ Ｏ不 是整个 Ｓ Ｌ执行 Ｑ 数 据块 上 的所 有记 录都将 需要 被处 理 ．公式 可 以简化 中耗 费资 源 的所 有 。还 应 该 考 虑 Ｃ Ｕ 的使 用 。执 行 Ｐ Ｓ Ｌ读 取时 ．数 据块 可 能 已经 在 Ｓ Ａ的缓 冲池 里 了 ， 为 ： Ｑ Ｇ ＃ Ｐ Ｃ Ｃ Ｅ ＝（ ｂｅｂｏｋ）（ｙｌｓｔ ｒ ｄｏｅｂｏｋｆ ｍ ｉ ） Ｃ Ｕ Ｙ Ｌ Ｓ ｔ ｌ ｌｃ ｓ ｃｃｅ ｏ ｅ ｎ ｌ ｏ ｄｓ ＋ ａ ａ ｃ ｒ ｋ Ｉ 的成 本 要 比从 文件 系 统读 取要 小 ．ｒｅ ８ 中没 有 Ｏ ０ａｌ ｉ ｅ
ORACLE的CBO及表分析
在做NL连接时，emp做为外表，先被访问，由于连 接机制原因，外表的数据访问方式是全表扫描，
emp.deptno上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描。
第二部分
[Q] 怎么样分析表或索引？
[A]命令行方式可以采用analyze命令
如Analyze table tablename compute statistics;
Analyze index|cluster indexname estimate statistics;
ANALYZE TABLE tablename COMPUTE STATISTICS
ห้องสมุดไป่ตู้这会极大导 致SQL语句执行极其缓慢。
6、使用CBO 时，SQL语句 "FROM" 子句后面的表的个数不宜太多，因为CBO在选择表连接顺序时，会对"FROM" 子句后面的表进行阶乘运算，
选择最好的一个连接顺序。假 如"FROM" 子句后有6个表，则其可选择的连接顺序就是6*5*4*3*2*1 = 720 种，CBO 选择其中一种，
节省了程序员为了从多个可能的“执行计划”中选择一个最优的方案而花费的调试时间，但在某些场合下 也会存在问题。
较典型的问题有：有时，表明明建有索引，但查询过程显然没有用到相关的索引，导致查询过程耗时漫长，占用资源巨大，
这时就 需要仔细分析执行计划，找出原因。例如，可以看连接顺序是否允许使用相关索引。
但是不 要擅自对数据字典表做分析，否则可能导致死锁，或系统性能严重下降。
数据库cbo规则-概述说明以及解释
数据库cbo规则-概述说明以及解释1.引言1.1 概述在数据库优化中，CBO（Cost-Based Optimizer，基于成本的优化器）规则是一个重要的概念。
它是Oracle数据库中的一个核心组件，用于优化SQL查询语句的执行计划，以提高查询性能和系统效率。
通过详细的成本估算和统计信息分析，CBO规则可以选择最佳的执行路径，以尽可能减少IO和CPU开销，从而提高查询的执行效率。
本文将深入探讨数据库CBO规则的原理、作用、优缺点等方面，帮助读者更好地理解并利用CBO规则进行数据库优化，提高系统性能和稳定性。
1.2 文章结构本文将首先介绍数据库CBO规则的概念和作用，然后分析其优缺点。
在正文部分，将详细讨论什么是数据库CBO规则，以及它在数据库优化中的作用。
同时，我们将深入探讨CBO规则的优点和不足之处，以帮助读者全面了解该规则的应用场景和局限性。
在结论部分，我们将总结文章内容，并提出相应的应用建议，以便读者更好地利用数据库CBO规则进行数据库优化。
同时，我们也将展望未来CBO规则的发展方向，为读者带来更多关于数据库性能优化的思考和探讨。
1.3 目的本文的目的是探讨数据库CBO（Cost-Based Optimizer）规则在数据库优化中的作用和意义。
通过深入分析CBO规则的定义、功能和影响，帮助读者更好地理解数据库性能优化的重要性，并为数据库管理员和开发人员提供指导和建议，以提高数据库系统的性能和效率。
同时，通过对CBO 规则的优缺点进行评估和讨论，帮助读者更全面地了解CBO规则的适用范围和局限性，从而更好地应用CBO规则来优化数据库系统。
最终达到提升数据库系统性能，提高用户体验的目的。
2.正文2.1 什么是数据库CBO规则数据库中的CBO（Cost-Based Optimization）规则是一种优化数据库查询性能的方法。
它是一种自动化的优化器，根据查询的成本估计来选择最佳的查询执行计划。
CBO规则基于优化器的成本模型，通过分析表的统计信息和索引等数据库对象的信息，来估计每个可能执行计划的成本，然后选择成本最低的执行计划来执行查询。
Oracle数据库的数据统计（Analyze）
SQL> analyz e tableemploy ee comput e statis tics;表已分析。
SQL> set autotr ace onSQL> select count(*) from employ ee ;COUNT(*)----------299999Execut ion Plan----------------------------------------------------------0 SELECT STATEM ENT Optimi zer=CHOOSE (Cost=7 Card=1)1 0 SORT (AGGREG ATE)2 1 BITMAP CONVER SION(COUNT)3 2 BITMAP INDEX(FAST FULL SCAN) OF 'IDX_BM P_EMP_SEX'Statis tics----------------------------------------------------------153 recurs ive calls0 db blockgets96 consis tentgets11 physic al reads0 redo size370 bytessent via SQL*Net to client425 bytesreceiv ed via SQL*Net from client2 SQL*Net roundt ripsto/from client0 sorts(memory)0 sorts(disk)1 rows proces sedSQL> analyz e tableemploy ee delete statis tics;表已分析。
SQL> select count(*) from employ ee;COUNT(*)----------299999Execut ion Plan----------------------------------------------------------0 SELECT STATEM ENT Optimi zer=CHOOSE1 0 SORT (AGGREG ATE)2 1 TABLEACCESS (FULL) OF 'EMPLOY EE'Statis tics----------------------------------------------------------0 recurs ive calls0 db blockgets5418 consis tentgets3144 physic al reads0 redo size370 bytessent via SQL*Net to client425 bytesreceiv ed via SQL*Net from client2 SQL*Net roundt ripsto/from client0 sorts(memory)0 sorts(disk)1 rows proces sedSQL> analyz e tableempplo yee ESTIMA TE statis tics; analyz e tableempplo yee ESTIMA TE statis tics*ERROR位于第1 行:ORA-00942:表或视图不存在SQL> analyz e tableemploy ee ESTIMA TE statis tics;SQL> select count(*) from employ ee;COUNT(*)----------299999Execut ion Plan----------------------------------------------------------0 SELECT STATEM ENT Optimi zer=CHOOSE (Cost=7 Card=1)1 0 SORT (AGGREG ATE)2 1 BITMAP CONVER SION(COUNT)3 2 BITMAP INDEX(FAST FULL SCAN) OF 'IDX_BM P_EMP_SEX'Statis tics----------------------------------------------------------0 recurs ive calls0 db blockgets12 consis tentgets8 physic al reads0 redo size370 bytessent via SQL*Net to client425 bytesreceiv ed via SQL*Net from client2 SQL*Net roundt ripsto/from client0 sorts(memory)0 sorts(disk)1 rows proces sedSQL> analyz e tableemploy ee delete statis tics;表已分析。
优化optimizer_mode
ORACLE的优化模式有如下几种：RULE，CHOOSE，FIRSTROWS，ALLROWS四种，这四种模式必须和前面讲的优化方式放在一起解释，CHOOSE就是选择的意思，如果有表和索引有统计信息，ORACLE就会CHOOSE CBO的优化方式了。对于FIRSTROWS，我给的解释是它和CHOOSE差不多，只是以最快的方式返回前面的记录行了；当然了ALLROWS就是以最快的方式返回所有行。
以上讲了ORACLE的优化方式和优化模式，以及ORACLE如何选择，下面我再讲讲我们认为的改变ORACLE的优化了
1、 修改ORACLE的init.ora的OPTIMIZER_MODE参数；
2、 在写SQL时人为指定优化方式，如：SELECT /*RULE*/ * FROM TABLE_NAME
前面讲了ORACLE性能调优原则，但是可能有许多朋友不知道ORACLE优化机理到底是什么？下面作一简单说明。
解释
先说明一下，ORACLE有一个优化器（Optimizer），ORACLE的优化机理就是从Optimizer开始的。
明确两个概念：Optimizer 对ORACLE的优化方式有两种，一种是基于规则的，我们称为RBO（Rule-Based Optimization），一种是基于代价的CBO（Cost-Based Optimization），我们从字面就可基本理解这两个优化方式的含义，不错，RBO是根据ORACLE的内定规则实现的，比如我在“ORACLE性能调优原则”中讲到的：索引，索引就是ORACLE的内定规则；而对于CBO，由于是基于代价的，也就是ORACLE机器的资源了，比如CPU和内存等，这个时候有朋友就问了，既然有这两个方式，那ORACLE到底如何选择呢，其实，这个问题有一半需要我们自己回答，有一半由ORACLE回答。因为我们在安装ORACLE时就已决定了ORACLE到底调用什么方式，这个就是init.ora中设定的OPTIMIZER_MODE参数，如果我们设为OPTIMIZER_MODE=RULE，它就会按RBO方式进行，反之按CBO了。当然，我们在开发程序时也可认为的改变规则，如SQL
CBO 和 RBO
例如： 我们常见的，当一个where子句中的一列有索引时去走索引。但是需要注意，走索引不一定就是优的，比如一个表只有两行数据，一次IO就可以完成全表的检索,而此时走索引时则需要两次IO, 这时全表扫描(full table scan)的效率更优。
一个例子：
SELECT *
FROM emp
WHERE ename = 'JACKSON';
如果 ename 是一个唯一性键或是主键，优化器很可能就会使用 unique scan on index 来使用索引。
如果 ename 不是一个唯一性键或是主键，那么优化器就会使用下面的统计信息：
USER_TAB_COLUMNS.NUM_DISTINCT 表的每一列的数值的数量
USER_TABLES.NUM_ROWS 表的行数
2 基于规则（Rule-Based）
优化器会根据这些因素来选择获取路径：
◎语句的可用的获取路径
◎获取路径的级别
优化器第一步会根据 where 子句的条件得到可利用的获取路径，然后选择最高级别的获取路径。
2、SMJ即使相关列上建有索引，最多只能因索引的存在，避免数据排序过程。
3、HJ由于须做HASH运算，索引的存在对数据查询速度几乎没有影响。
D、使用CBO时，需要注意什么吗？
1、必须保证为表和相关的索引搜集足够的统计数据， 对数据经常有增、删、改的表最好定期对表和索引进行分析
8 Composite key
9 Single-column indexes
10 Bounded range search on indexed columns
CBO全表扫描代价计算公式推导
1.前言CBO是一种相对更加精确的优化器，它在选择访问路径时能够更多的考虑一些客观因素，得出更加合理的查询计划。
但是，Oracle并没有给出完整的代价计算公式，所以我们并不清楚这些因素在整个代价中到底产生多大的影响，或者在尝试调整一些参数时，也没有可依靠的理论公式。
不过，Oracle提供了对CBO的Trace方法和输出。
这对于我们来说相当于是个“黑盒子”，我们可以向黑盒子输入一些数据，黑盒子会输出结果。
很多人可能都做过一些IQ测试题，其中一种很典型的题型就是给出一组数字，但是中间缺1、2个，让你通过观察出这一组数字的规律来补充出缺少的数字。
比如，一组数字1、2、？、5、8、？、21，通过观察，可以对这组数字得出一个这样的规律X n = X n-1 + X n-2，根据这个规律，可以得出两个缺失的数字分别是3、13。
那么我们是否可以由黑盒子的输入、输出来尝试推导出黑盒子里到底如何运算的呢？2.背景Oracle的CBO是基于代价的，因此、查询计划的代价计算就是CBO的核心。
通过Oracle Concept以及很多相关文档，我们了解到查询计划的代价和许多因素相关。
例如hwm下的数据块数量、表的记录数、字段的唯一值数、索引的高度、主机的CPU处理能力等等。
在Metalink上，我们也能找到Oracle给出的一个COST计算公式：Cost = (#SRds * sreadtim +#MRds * mreadtim +#CPUCycles / cpuspeed) / sreadtim对其中因子的解释是：·#SRDS：单数据块读的次数；·#MRDS：多数据块读的次数；·SREADTIM：一次单数据块读的时间；·MREADTIM：一次多数据块读的时间；·#CPUCYCLES：完成查询所需要发出的CPU指令数·CPUSPEED：CPU的处理速度但是，这个公式还不足以帮助我们计算出一个查询计划的代价，因为你可以发现，除了CPUSPEED是一个可以直接获得的数据（由System Statistics获取或者有CPU主频乘以数量算出）外，其它几个因子还需要更多的信息和计算公式才能计算得出（在workload模式下，SREADTIM、MREADTIM会有系统统计得出，但是在noworkload模式下，这两个数据仍然需要通过计算得出）。
oracle统计分析信息拷贝介绍
数据库统计分析信息拷贝介绍1.数据库统计分析简介统计分析主要包括产生表及索引的统计信息。
表的统计信息主要包括表的行数，每行的平均长度〔字节〕，空闲块，统计时间等信息；索引的统计信息主要包括行数、层数、叶块数、统计时间等信息。
另外ORACLE还可以统计列及数据不对称信息。
ORACLE 执行本钱分析时首先取出所应用表及索引的统计数据进展分析，其中数据行数是一个重要的参数，由于 ORACLE 在分析表大小时行数为主要参数，假设进展两个表联合时，ORACLE 会通过分析表的大小，打算应用小表进展全表查询，而大表执行联合查询，这种性能明显高于先大表进展全表扫描。
索引的统计信息对分析也产生比较大的影响，如ORACLE 通过统计可以分析产生多个索引的优先级及索引的有用性来确定最优的索引策略。
ORACLE 还可以统计列及数据对称信息以产生更准确的分析。
dbms_stats能良好地估量统计数据〔尤其是针对较大的分区表〕，并能获得更好的统计结果，最终制定出速度更快的SQL执行打算。
2.存储过程解析2.1DBMS_STATS.GATHER_TABLE_STATS 介绍DBMS_STATS.GATHER_TABLE_STATS 功能为：统计表、列、索引的统计信息。
DBMS_STATS.GATHER_TABLE_STATS的语法如下:DBMS_STATS.GATHER_TABLE_STATS (ownname VARCHAR2,tabname VARCHAR2,partname VARCHAR2,estimate_percent NUMBER,block_sample BOOLEAN,method_opt VARCHAR2,degree NUMBER,granularity VARCHAR2,cascade BOOLEAN,stattab VARCHAR2,statid VARCHAR2,statown VARCHAR2,no_invalidate BOOLEAN,force BOOLEAN);参数说明:ownname:要分析表的拥有者tabname:要分析的表名.partname:分区的名字,只对分区表或分区索引有用.estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全局部析, 不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle绝定最正确取采样值.block_sapmple:是否用块采样代替行采样.method_opt:打算histograms信息是怎样被统计的.method_opt的取值如下:for all columns:统计全部列的histograms.for all indexed columns:统计全部indexed列的histograms.for all hidden columns:统计你看不到列的histogramsfor columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY: 统计指定列的histograms.N的取值范围 [1,254]; REPEAT 上次统计过的 histograms;AUTO由oracle打算N的大小;SKEWONLY multiple end-points with the same value which is what we define by “there is skew in the datadegree:打算并行度.默认值为null. degree => 15granularity:Granularity of statistics tocollect ,only pertinent if the table is partitioned.cascace:是收集索引的信息.默认为falase.stattab指定要存储统计信息的表,statid假设多个表的统计信息存储在同一个stattab中用于进展区分.statown存储统计信息表的拥有者.以上三个参数假设不指定,统计信息会直接更到数据字典.no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.force:即使表锁住了也收集统计信息.例子:execute dbms_stats.gather_table_stats(ownname => ”owner”,tabname => ”table_name” ,estimate_percent => null ,method_opt => ”for all indexed c olumns” ,cascade => true);2.2oracle 程序包Oracle 供给的程序包如下：COPY_CBO_STATS_SUBPART.pksCOPY_CBO_STATS_SUBPART.pkbCOPY_CBO_STATS.pksCOPY_CBO_STATS.pkbORADB_COPY_STATS.prc主要实现信息统计分析和拷贝的功能。
ORACLE 10g 关闭自动收集CBO统计信息功能
ORACLE10g自动收集CBO统计信息从Oracle Database 10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息。
这个特性减少了由于sql语句统计失效或陈旧而导致性能很差的可能性,通过提高查询优化器的最佳的输出提高sql执行的性能.gather_stats_job默认的,在数据库创建的时候就创建了gather_stats_job,执行dbms_stats.gather_database_stats_job_proc过程,使用schedular.默认的定义了两个窗口.weekenight_window定义在下午10点到上午六点.从周一到周五.weekend_window定义从上午12点到周一12点.调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。
该过程首先检测统计信息缺失和陈旧的对象。
然后确定优先级，再开始进行统计信息。
说明：当做完统计信息后，如果对对象的行数修改达到10%，DBMS_STATS就认为是统计信息过旧。
可以通过以下查询这个JOB的运行情况：SQL> select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'其实同在10点运行的Job还有一个AUTO_SPACE_ADVISOR_JOB：SQL> select JOB_NAME,LAST_START_DATE from dba_scheduler_jobs;JOB_NAME LAST_START_DATE------------------- ----------------------------------------AUTO_SPACE_ADVISOR_JOB 04-DEC-07 10.00.00.692269 PM +08:00GATHER_STATS_JOB 04-DEC-07 10.00.00.701152 PM +08:00FGR$AUTOPURGE_JOBPURGE_LOG 05-DEC-07 03.00.00.169059 AM PRC默认的maintenance_window_group组包含这两个窗口.gather_stats_job使用特定的计划任务叫auto_tasks_job_class.这个类是自动创建的,而且跟特定的资源组联系,这个组叫做auto_task_consumer_group.这样确保任务使用auto_task_consumer_group.如果想控制gather_stats_job的资源使用,之需要为maintenance_window_group定义一个资源管理者组来为auto_task_cousumer_group分配资源.为了让gather_stats_job工作正常,必须确定statistics_level初始化参数设置为typical.改变gather_stats_job的计划任务可以为定义的管理窗口来自定义开放时间.例如,可以改变他们的时间间隔和重复的频度.也可以添加资源计划到这些窗口中,来控制gather_stats_job使用的资源.从db control主页,点击管理标签,然后在scheduler部分点击windows链接.就让到了scheduler windows 页.这里可以选中一个窗口,并且可以点edit来修改它的特性.在这页,还可以打开或关闭特定的窗口.只要在下拉框为特定的窗口选择相应的行为,点ok.锁定统计阻止自动收集主要用在可变的表中-没有统计的锁定,意味着动态的取样.-锁定代表值的统计在oracle10g,可以看到特定表的统计,通过新的dbms_stats包的lock_table_stats过程.可以锁定一个表的统计,来阻止自动统计收集,这样就可以使用动态的取样.也可以在某个时间点锁定可变的表的统计.可以使用lock_schema_stats运行在sechma基本锁定统计.可以查看user/all/dba_tab_statistics视图来看stattype_locked列来看一个表是不是被锁定.然而这个自动化功能已经影响了很多系统的正常运行，晚上10点对于大部分生产系统也并非空闲时段。
oracle 强制绑定执行计划方法
oracle 强制绑定执行计划方法下载提示：该文档是本店铺精心编制而成的，希望大家下载后，能够帮助大家解决实际问题。
文档下载后可定制修改，请根据实际需要进行调整和使用，谢谢!本店铺为大家提供各种类型的实用资料，如教育随笔、日记赏析、句子摘抄、古诗大全、经典美文、话题作文、工作总结、词语解析、文案摘录、其他资料等等，想了解不同资料格式和写法，敬请关注!Download tips: This document is carefully compiled by this editor. I hope that after you download it, it can help you solve practical problems. The document can be customized and modified after downloading, please adjust and use it according to actual needs, thank you! In addition, this shop provides you with various types of practical materials, such as educational essays, diary appreciation, sentence excerpts, ancient poems, classic articles, topic composition, work summary, word parsing, copy excerpts, other materials and so on, want to know different data formats and writing methods, please pay attention!在Oracle数据库中，强制绑定执行计划是一种强制让数据库引擎执行一个特定的执行计划的方法。
Oracle表连接操作——Nest Loop Join(嵌套循环)
结论：如果确定需要使用嵌套循环Nest Loop Join，那么最好考虑保证连接列上能存在索引对象。这样可以很大程度上提高Nest Loop Join的连接效率。
Plan hash value: 2834620917
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1355 | 381K| 360 (0)| 00:00:05 |
Oracle表连接操作——Nest Loop Join（嵌套循环）
关系型数据库并不是最早出现的数据库表现形式，之前还存在层次、网状数据库结构。随着关系型数据库的出现，以数据表的方式进行信息留存的方案迅速发展起来。关系型数据库的重要元素包括数据表和表连接，借助各种类型的表连接，可以将平铺直叙的信息加以组装拼接。
注意：此处有两个需要注意的问题。其一是驱动表的确定。另一个就是检索内侧表的方法。这两个问题在CBO时代的回答都是成本问题，Oracle通过成本试算获取到。对Nest Loop Join而言，条件列、连接列上的索引是会很大程度上影响执行计划的。
下面是一个SQL语句的执行计划，由于CBO操作的复杂性，本SQL使用hint来进行强制的Nest Loop路径。
CBO的相关原理
CBO的相关原理作者｜邱大龙CBO在oracle7中被引入，基于数据对象的统计信息（包括数据集的行数，唯一值的个数等等）来计算执行计划的执行成本。
随着版本的演化，CBO逐渐完善起来，在9i开始使用系统统计信息(system statistics，系统统计信息的出现是为了估算SQL在CPU方面的消耗)。
但是CBO仍然存在一些缺陷，通过了解CBO的一些相关原理，其缺陷大家也就很容易理解了，从而也会明白，很多时候CBO所依赖的统计信息都收集的百分之百准确了，还是会选错执行计划的原因。
执行计划的选择CBO在生成一条执行计划后，会计算其成本；然后和已经生成的执行计划中成本最低的进行比较。
这种比较在以下条件满足其一就停止：1.所有执行计划都已经被计算过2.查询块的join排列数超过了OPTIMIZER_MAX_PERMUTATIONS（10g及以后为_OPTIMIZER_MAX_PERMUTATIONS）参数指定的值。
默认是2000.我们可以做个简单的计算，比如下面这个SQL：一个查询块中有7张表，这7张表做join可能的顺序有：a1 -> a2 -> a3 -> a4 -> a5 -> a6 -> a7a1 -> a2 -> a3 -> a4 -> a5 -> a7 -> a6a1 -> a2 -> a3 -> a4 -> a6 -> a5 -> a7......所有可能的排列数就是7!=5040，远远超过了OPTIMIZER_MAX_PERMUTATIONS的默认值。
那么这种情况下，CBO不会把所有可能的join顺序计算一遍。
这就有可能错过了成本最低的执行计划。
之所以这么设计是防止过多的对执行计划成本的比较导致花费在SQL解析的时间过长。
成本计算的基本概念1 cardinality（基数）cardinality指的是一个行源的结果集的行数。
Oracle优化器(Optimizer)
Oracle优化器（Optimizer）是Oracle在执行SQL之前分析语句的工具。
Oracle的优化器有两种优化方式：基于规则的优化方式：Rule-Based Optimization（RBO）优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。
比如我们常见的，当一个where子句中的一列有索引时去走索引。
基于成本或者统计信息的优化方式(Cost-Based Optimization：CBO)CBO是在ORACLE7 引入，但到ORACLE8i 中才成熟。
ORACLE 已经声明在ORACLE9i之后的版本中，RBO将不再支持。
它是看语句的代价(Cost),这里的代价主要指Cpu和内存。
CPU Costing的计算方式现在默认为CPU+I/O两者之和.可通过DBMS_XPLAN.DISPLAY_CURSOR观察更为详细的执行计划。
优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。
统计信息给出表的大小、有少行、每行的长度等信息。
这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。
按理，CBO应该自动收集，实际却不然，有时候在CBO情况下，还必须定期对大表进行分析。
Oracle优化器的优化模式：1) CHOOSE仅在9i及之前版本中被支持，10g已经废除。
8i及9i中为默认值。
这个值表示SQL语句既可以使用RBO优化器也可以使用CBO优化器，而决定该SQL到底使用哪个优化器的唯一因素是，所访问的对象是否存在统计信息。
如果所访问的全部对象都存在统计信息，则使用CBO 优化器优化SQL；如果只有部分对象存在统计信息，也仍然使用CBO优化器优化SQL，优化器会为不存在统计信息对象依据一些内在信息（如分配给该对象的数据块）来生成统计信息，只是这样生成的统计信息可能不准确，而导致产生不理想的执行计划；如果全部对象都无统计信息，则使用RBO来优化该SQL 语句。
Oracle 统计信息详解
Oracle统计信息1. 统计信息的作用在CBO(基于代价的优化器模式)条件下，SQL语句的执行计划由统计信息来决定，若没有统计信息则会采取动态采样的方式决定执行计划。
可以说统计信息关乎SQL的执行计划是否正确，属于SQL执行的指导思想。
优化器统计范围：表统计--行数，块数，行平均长度；all_tables：NUM_ROWS，BLOCKS，AVG_ROW_LEN列统计--列中唯一值的数量（NDV），NULL值的数量，数据分布--DBA_TAB_COLUMNS：NUM_DISTINCT，NUM_NULLS，HISTOGRAM索引统计--叶块数量，等级，聚簇因子；--DBA_INDEXES：LEAF_BLOCKS，CLUSTERING_FACTOR，BLEVEL系统统计--I/O性能与使用率--CPU性能与使用率--存储在aux_stats$中，需要使用dbms_stats收集，I/O统计在X$KCFIO中2. 统计信息的内容①行统计信息(user_tables)行数(NUM_ROWS)，块数(BLOCKS)，行平均长度(AVG_ROW_LEN)②列统计信息(user_tab_columns)列中唯一值的数量（NUM_DISTINCT），NULL值的数量(NUM_NULLS)，数据分布(HISTOGRAM)③索引统计(user_index)叶块数量(LEAF_BLOCKS)，等级(BLEVEL)，聚簇因子(CLUSTERING_FACTOR)④系统统计系统统计描述系统硬件的特征，包括I/O和CPU。
在选择执行计划时，优化器考虑查询所需的CPU和I/O代价。
系统统计允许优化器更加精确的评价CPU和IO代价，选择更好的查询计划。
使用DBMS_STATS.GATHER_SYSTEM_STATS收集系统统计，Oracle推荐收集系统统计。
收集系统统计需要DBA权限。
3. 统计信息的收集3.1 统计信息收集相关Oracle的初始化参数statistics_level控制收集统计信息的级别，有三个参数值：BASIC：收集基本的统计信息TYPICAL：收集大部分统计信息(数据库的默认设置)ALL：收集全部统计信息由于自动统计收集在夜间进行，因此对于一些更新频繁的对象其统计可能已经过期。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
oracle cbo&rbo 详解Oracle 2010-11-01 19:50:35 阅读65 评论1字号：大中小订阅转载ORACLE 提供了CBO、RBO两种SQL优化器。
CBO在ORACLE7 引入，但在ORACLE8i 中才成熟。
ORACLE 已经明确声明在ORACLE9i之后的版本中(ORACLE 10G )，RBO将不再支持。
因此选择CBO 是必然的趋势。
CBO和RBO作为不同的SQL优化器，对SQL语句的执行计划产生重大影响，如果要对现有的应用程序从RBO向CBO移植，则必须充分考虑这些影响，避免SQL 语句性能急剧下降；但是，对新的应用系统，则可以考虑直接使用CBO，在CBO模式下进行SQL 语句编写、分析执行计划、性能测试等工作，这需要开发者对CBO的特性比较熟悉。
以下小结几点在CBO下写SQL语句的注意事项：1、RBO自ORACLE 6版以来被采用，有着一套严格的使用规则，只要你按照它去写SQL 语句，无论数据表中的内容怎样，也不会影响到你的“执行计划”，也就是说对数据不“敏感”；CBO计算各种可能“执行计划”的“代价”，即cost，从中选用cost最低的方案，作为实际运行方案。
各“执行计划”的cost的计算根据，依赖于数据表中数据的统计分布，ORACLE数据库本身对该统计分布并不清楚，必须要分析表和相关的索引（使用ANALYZE 命令），才能搜集到CBO所需的数据。
2、使用CBO 时，编写SQL语句时，不必考虑"FROM" 子句后面的表或视图的顺序和"WHERE" 子句后面的条件顺序；ORACLE自7版以来采用的许多新技术都是基于CBO的，如星型连接排列查询，哈希连接查询，函数索引，和并行查询等。
3、一般而言，CBO所选择的“执行计划”都不会比RBO的“执行计划”差，而且相对而言，CBO对程序员的要求没有RBO那么苛刻，节省了程序员为了从多个可能的“执行计划”中选择一个最优的方案而花费的调试时间，但在某些场合下也会存在问题。
较典型的问题有：有时，表明明建有索引，但查询过程显然没有用到相关的索引，导致查询过程耗时漫长，占用资源巨大，这时就需要仔细分析执行计划，找出原因。
例如，可以看连接顺序是否允许使用相关索引。
假设表emp的deptno列上有索引，表dept的列deptno上无索引，WHERE语句有emp.deptno=dept.deptno条件。
在做NL连接时，emp 做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，emp.deptno 上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描。
4、如果一个语句使用RBO的执行计划确实比CBO 好，则可以通过加" rule" 提示，强制使用RBO。
5、使用CBO 时，SQL语句"FROM" 子句后面的表，必须全部使用ANALYZE 命令分析过，如果"FROM" 子句后面的是视图，则此视图的基础表，也必须全部使用ANALYZE 命令分析过；否则，ORACLE 会在执行此SQL语句之前，自动进行ANALYZE 命令分析，这会极大导致SQL语句执行极其缓慢。
6、使用CBO 时，SQL语句"FROM" 子句后面的表的个数不宜太多，因为CBO在选择表连接顺序时，会对"FROM" 子句后面的表进行阶乘运算，选择最好的一个连接顺序。
假如"FROM" 子句后有6个表，则其可选择的连接顺序就是6*5*4*3*2*1 = 720 种，CBO 选择其中一种，而如果"FROM" 子句后有12个表，则其可选择的连接顺序就是2*11*10*9*8*7*6*5*4*3*2*1= 479001600 种，可以想象从中选择一种，会消耗多少CPU 时间？如果实在是要访问很多表，则最好使用ORDER 提示，强制使用"FROM" 子句表固定的访问顺序。
7、使用CBO 时，SQL语句中不能引用系统数据字典表或视图，因为系统数据字典表都未被分析过，可能导致极差的“执行计划”。
但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能严重下降。
8、使用CBO 时，要注意看采用了哪种类型的表连接方式。
ORACLE的共有Sort Merge Join（SMJ）、Hash Join（HJ）和Nested Loop Join（NL）。
CBO有时会偏重于SMJ 和HJ，但在OLTP 系统中，NL 一般会更好，因为它高效的使用了索引。
在两张表连接，且内表的目标列上建有索引时，只有Nested Loop才能有效地利用到该索引。
SMJ即使相关列上建有索引，最多只能因索引的存在，避免数据排序过程。
HJ由于须做HASH运算，索引的存在对数据查询速度几乎没有影响。
9、使用CBO 时，必须保证为表和相关的索引搜集足够的统计数据。
对数据经常有增、删、改的表最好定期对表和索引进行分析，可用SQL语句“analyze table xxx compute statistics for all indexes;"ORACLE掌握了充分反映实际的统计数据，才有可能做出正确的选择。
10、使用CBO 时，要注意被索引的字段的值的数据分布，会影响SQL语句的执行计划。
例如：表emp，共有一百万行数据，但其中的emp.deptno列，数据只有4种不同的值，如10、20、30、40。
虽然emp数据行有很多，ORACLE缺省认定表中列的值是在所有数据行均匀分布的，也就是说每种deptno值各有25万数据行与之对应。
假设SQL搜索条件DEPTNO=10，利用eptno列上的索引进行数据搜索效率，往往不比全表扫描的高，ORACLE理所当然对索引“视而不见”，认为该索引的选择性不高。
我们考虑另一种情况，如果一百万数据行实际不是在4种deptno值间平均分配，其中有99万行对应着值10，5000行对应值20，3000行对应值30，2000行对应值40。
在这种数据分布图案中对除值为10外的其它deptno 值搜索时，毫无疑问，如果索引能被应用，那么效率会高出很多。
我们可以采用对该索引列进行单独分析，或用analyze语句对该列建立直方图，对该列搜集足够的统计数据，使ORACLE在搜索选择性较高的值能用上索引。
——————————————————————————————show parameter optimizer_mode --看ORACLE处于何种模式，Oracle V7以来缺省的设置应是"choose",即如果对已分析的表查询的话选择CBO,是否选择RBO.如果该参数设置为"rule",则不论表是否分析过, 一概选用RBO,除非在语句中用hint强制.analyze table xxx compute statistics for all indexes --对表进行分析。
Oracle 的优化器有两种优化方式,即基于规则的优化方式(Rule-Based Optimization，简称为RBO)和基于代价的优化方式(Cost-Based Optimization，简称为CBO)，在Oracle8及以后的版本,Oracle强列推荐用CBO的方式RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。
比如我们常见的，当一个where子句中的一列有索引时去走索引。
CBO方式：它是看语句的代价(Cost),这里的代价主要指Cpu和内存。
优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。
统计信息给出表的大小、有少行、每行的长度等信息。
这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。
注意：走索引不一定就是优的，比如一个表只有两行数据，一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时全表扫描(full table scan)是最好优化模式包括Rule、Choose、First rows、All rows四种方式：Rule：基于规则的方式。
Choolse：默认的情况下Oracle用的便是这种方式。
指的是当一个表或或索引有统计信息，则走CBO的方式，如果表或索引没统计信息，表又不是特别的小，而且相应的列有索引时，那么就走索引，走RBO的方式。
First Rows：它与Choose方式是类似的，所不同的是当一个表有统计信息时，它将是以最快的方式返回查询的最先的几行，从总体上减少了响应时间。
All Rows:也就是我们所说的Cost的方式，当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐量。
没有统计信息则走RBO的方式。
设定选用哪种优化模式：A、Instance级别我们可以通过在initSID.ora文件中设定OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS如果没设定OPTIMIZER_MODE参数则默认用的是Choose方式。
B、Sessions级别通过ALTER SESSION SETOPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS来设定。
C、语句级别用Hint（/*+ ... */）来设定为什么表的某个字段明明有索引，但执行计划却不走索引？1、优化模式是all_rows的方式2、表作过analyze，有统计信息3、表很小，上文提到过的，Oracle的优化器认为不值得走索引。
一、如何使用CostBased优化器优化查询操作？Oracle 提供了基于成本（CostBased）和基于规则（RuleBased）两种优化器，简称为CBO 和RBO，用于确定查询操作的执行计划。
CostBased优化器将计算各种执行计划的开销，然后选出最低成本的执行计划。
可使用下列方法选择使用CBO：1.在INIT.ORA文件中设置参数OPTIMIZER_MODE=choose；2.在Session级设置OPTIMIZER_GOAL=FIRST_ROWS或ALL_ROWS。
3、在查询语句中使用Hint，包括CHOOSE、ALL_ROWS、FIRST_ROWS等。
二、为什么我的执行计划不是最好的？CBO是依赖于表的一些统计信息来选择出最低成本的执行计划，当这些统计信息不准确时，产生的计划便可能不是最佳的。
因而应使用ANALYZE命令及时对表进行分析统计。
三、我的查询上周的性能很好，为什么现在查询速度很慢？这是由于执行计划被改变而造成的，下列因素将会改变一个执行计划：1、INIT.ORA文件中的参数OPTIMIZER_MODE被改变；2、表上定义或改变了并行查询度；3、使用ANALYZE命令重新分析了表，而且使用了ESTIMATE方式，这种方式选择不同的百分比可产生不同的分析结果；4、DB_FILE_MULTIBLOCK_READ_COUNT参数被修改；5、SORT_AREA_SIZE参数被修改。