- 浏览: 1183287 次
- 性别:
- 来自: 上海
文章分类
- 全部博客 (361)
- java综合 (33)
- 项目管理 (10)
- 工作流 (6)
- spring (11)
- hibenate (17)
- struts (0)
- javascript,html,css,ajax,jquery (11)
- IDE (9)
- 生活 (0)
- 工作 (0)
- 杂记 (1)
- 数据库 (96)
- 服务器 (5)
- 可视编辑 (0)
- freemarker (6)
- 操作系统 windows (13)
- web页面 (6)
- jms (15)
- 调优 (4)
- 测试和bug管理 (2)
- 原理 (1)
- 項目-atf (17)
- 安全 (3)
- xml (4)
- 操作系统 liunx (21)
- 网络 (22)
- office (11)
- 设计 (2)
- 软件 (1)
- 数据库 mysql (6)
- 胖客户端-flex (1)
- 正则 (9)
- oracle- liunx (3)
- sql2000 (2)
- 模式 (1)
- 虚拟机 (2)
- jstl (2)
- 版本控制 打包工具 (0)
- AOP (1)
- demo (1)
- 小软件 (2)
- 感恩 (1)
- iphone 4 (1)
- 反欺诈业务经验整理 (0)
最新评论
-
sea0108:
mark
java内存模型 -
XingShiYiShi:
方便把:testPNR();具体实现发出来吗?谢谢
用正则表达式解析 航信的电子客票和pnr报文 -
wh359126613:
如果js和webservice不在同一个服务器上,有跨域问题如 ...
使用javascript调用webservice示例 -
雨飛雁舞:
...
oracle 动态性能(V$)视图 -
ouyang1224:
好东西
oracle 动态性能(V$)视图
基于规则的优化器 -- Rule Based (Heuristic) Optimization(简称RBO):
在ORACLE7之前,主要是使用基于规则的优化器。ORACLE在基于规则的优化器中采用启发式的方法(Heuristic Approach)或规则(Rules)来生成执行计划。例如,如果一个查询的where条件(where clause)包含一个谓词(predicate,其实就是一个判断条件,如”=”, “>”, ”<”等),而且该谓词上引用的列上有有效索引,那么优化器将使用索引访问这个表,而不考虑其它因素,如表中数据的多少、表中数据的易变性、索引的可选择性等。此时数据库中没有关于表与索引数据的统计性描述,如表中有多上行,每行的可选择性等。优化器也不考虑实例参数,如multi block i/o、可用排序内存的大小等,所以优化器有时就选择了次优化的计划作为真正的执行计划,导致系统性能不高。
如,对于
select * from emp where deptno = 10;
这个查询来说,如果是使用基于规则的优化器,而且deptno列上有有效的索引,则会通过deptno列上的索引来访问emp表。在绝大多数情况下,这是比较高效的,但是在一些特殊情况下,使用索引访问也有比较低效的时候,现举例说明: 13
1) emp表比较小,该表的数据只存放在几个数据块中。此时使用全表扫描比使用索引访问emp表反而要好。因为表比较小,极有可能数据全在内存中,所以此时做全表扫描是最快的。而如果使用索引扫描,需要先从索引中找到符合条件记录的rowid,然后再一一根据这些rowid从emp中将数据取出来,在这种条件下,效率就会比全表扫描的效率要差一些。
2) emp表比较大时,而且deptno = 10条件能查询出表中大部分的数据如(50%)。如该表共有4000万行数据,共放在有500000个数据块中,每个数据块为8k,则该表共有约4G,则这么多的数据不可能全放在内存中,绝大多数需要放在硬盘上。此时如果该查询通过索引查询,则是你梦魇的开始。db_file_multiblock_read_count参数的值200。如果采用全表扫描,则需要500000/db_file_multiblock_read_count=500000/200=2500次I/O。但是如果采用索引扫描,假设deptno列上的索引都已经cache到内存中,所以可以将访问索引的开销忽略不计。因为要读出4000万x 50% = 2000万数据,假设在读这2000万数据时,有99.9%的命中率,则还是需要20000次I/O,比上面的全表扫描需要的2500次多多了,所以在这种情况下,用索引扫描反而性能会差很多。在这样的情况下,用全表扫描的时间是固定的,但是用索引扫描的时间会随着选出数据的增多使查询时间相应的延长。
上面是枯燥的假设数据,现在以具体的实例给予验证:
环境: oracle 817 + linux + 阵列柜,表SWD_BILLDETAIL有3200多万数据;
表的id列、cn列上都有索引
经查看执行计划,发现执行select count(id) from SWD_BILLDETAIL;使用全表扫描,执行完用了大约1.50分钟(4次执行取平均,每次分别为1.45 1.51 2.00 1.46)。而执行select count(id) from SWD_BILLDETAIL where cn <'6';却用了2个小时还没有执行完,经分析该语句使用了cn列上的索引,然后利用查询出的rowid再从表中查询数据。我为什么不使用select count(cn) from SWD_BILLDETAIL where cn <'6';呢?后面在分析执行路径的索引扫描时时会给出说明。
下面就是基于规则的优化器使用的执行路径与各个路径对应的等级:
RBO Path 1: Single Row by Rowid(等级最高)
RBO Path 2: Single Row by Cluster Join
RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key
RBO Path 4: Single Row by Unique or Primary Key
RBO Path 5: Clustered Join
RBO Path 6: Hash Cluster Key
RBO Path 7: Indexed Cluster Key
RBO Path 8: Composite Index
RBO Path 9: Single-Column Indexes
RBO Path 10: Bounded Range Search on Indexed Columns
RBO Path 11: Unbounded Range Search on Indexed Columns
RBO Path 12: Sort Merge Join
RBO Path 13: MAX or MIN of Indexed Column
RBO Path 14: ORDER BY on Indexed Column
RBO Path 15: Full Table Scan(等级最低)
上面的执行路径中,RBO认为越往下执行的代价越大,即等级越低。在RBO生成执行计划时,如果它发现有等级高的执行路径可用,则肯定会使用等级高的路径,而不管任何其它影响性能的元素,即RBO通过上面的路径的等级决定执行路径的代价,执行路径的等级越高,则使用该执行路径的代价越小。如上面2个例子所述,如果使用RBO,则肯定使
14
用索引访问表,也就是选择了比较差的执行计划,这样会给数据库性能带来很大的负面影响。为了解决这个问题,从ORACLE 7开始oracle引入了基于代价的优化器,下面给出了介绍。
基于代价的优化器 -- Cost Based Optimization(简称CBO)
Oracle把一个代价引擎(Cost Engine)集成到数据库内核中,用来估计每个执行计划需要的代价,该代价将每个执行计划所耗费的资源进行量化,从而CBO可以根据这个代价选择出最优的执行计划。一个查询耗费的资源可以被分成3个基本组成部分:I/O代价、CPU代价、network代价。I/O代价是将数据从磁盘读入内存所需的代价。访问数据包括将数据文件中数据块的内容读入到SGA的数据高速缓存中,在一般情况下,该代价是处理一个查询所需要的最主要代价,所以我们在优化时,一个基本原则就是降低查询所产生的I/O总次数。CPU代价是处理在内存中数据所需要的代价,如一旦数据被读入内存,则我们在识别出我们需要的数据后,在这些数据上执行排序(sort)或连接(join)操作,这需要耗费CPU资源。
对于需要访问跨节点(即通常说的服务器)数据库上数据的查询来说,存在network代价,用来量化传输操作耗费的资源。查询远程表的查询或执行分布式连接的查询会在network代价方面花费比较大。
在使用CBO时,需要有表和索引的统计数据(分析数据)作为基础数据,有了这些数据,CBO才能为各个执行计划计算出相对准确的代价,从而使CBO选择最佳的执行计划。所以定期的对表、索引进行分析是绝对必要的,这样才能使统计数据反映数据库中的真实情况。否则就会使CBO选择较差的执行计划,影响数据库的性能。分析操作不必做的太频繁,一般来说,每星期一次就足够了。切记如果想使用CBO,则必须定期对表和索引进行分析。
对于分析用的命令,随着数据库版本的升级,用的命令也发生了变换,在oracle 8i以前,主要是用ANALYZE命令。在ORACLE 8I以后,又引入了DBMS_STATS存储包来进行分析。幸运的是从ORACLE 10G以后,分析工作变成自动的了,这减轻的DBA的负担,不过在一些特殊情况下,还需要一些手工分析。
如果采用了CBO优化器,而没有对表和索引进行分析,没有统计数据,则ORACLE使用缺省的统计数据(至少在ORACLE 9I中是这样),这可以从oracle的文档上找到。使用的缺省值肯定与系统的实际统计值不一致,这可能会导致优化器选择错误的执行计划,影响数据库的性能。
要注意的是:虽然CBO的功能随着ORACLE新版本的推出,功能越来越强,但它不是能包治百病的神药,否则就不再需要DBA了,那我就惨了!!!实际上任何一个语句,随着硬件环境与应用数据的不同,该语句的执行计划可能需要随之发生变化,这样才能取得最好的性能。所以有时候不在具体的环境下而进行SQL性能调整是徒劳的。
在ORACLE8I推出的时候,ORACLE极力建议大家使用CBO,说CBO有种种好处,但是在那是ORACLE开发的应用系统还是使用基于规则的优化器,从这件事上我们可以得出这样的结论:1) 如果团队的数据库水平很高而且都熟悉应用数据的特点,RBO也可以取得很好的性能。2)CBO不是很稳定,但是一个比较有前途的优化器,Oracle极力建议大家用是为了让大家尽快发现它的BUG,以便进一步改善,但是ORACLE为了对自己开发的应用系统负责,他们还是使用了比较熟悉而且成熟的RBO。从这个事情上给我们的启发就是:我们在以后的开发中,应该尽量采用我们熟悉并且成熟的技术,而不要一味的采用新技术,一味采用新技术并不一定能开发出好的产品。幸运的是从ORACLE 10G后,CBO已经足够的强大与智能,大家可以放心的使用该技术,因为ORACLE 10G后,Oracle自己开发的应用系统也使用CBO优化器了。而且ORACLE规定,从ORACLE 10G开始,开始废弃RBO优化器。这句话并不是指在ORACLE 10G中不能使用RBO,而是从ORACLE 15
10G开始开始,不再为RBO的BUG提供修补服务。
在上面的第2个例子中,如果采用CBO优化器,它就会考虑emp表的行数,deptno列的统计数据,发现对该列做查询会查询出过多的数据,并且考虑db_file_multiblock_read_count参数的设置,发现用全表扫描的代价比用索引扫描的代价要小,从而使用全表扫描从而取得良好的执行性能。
判断当前数据库使用何种优化器:
主要是由optimizer_mode初始化参数决定的。该参数可能的取值为:first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows | choose | rule。具体解释如下:
RULE为使用RBO优化器。
CHOOSE则是根据实际情况,如果数据字典中包含被引用的表的统计数据,即引用的对象已经被分析,则就使用CBO优化器,否则为RBO优化器。
ALL_ROWS为CBO优化器使用的第一种具体的优化方法,是以数据的吞吐量为主要目标,以便可以使用最少的资源完成语句。
FIRST_ROWS为优化器使用的第二种具体的优化方法,是以数据的响应时间为主要目标,以便快速查询出开始的几行数据。
FIRST_ROWS_[1 | 10 | 100 | 1000] 为优化器使用的第三种具体的优化方法,让优化器选择一个能够把响应时间减到最小的查询执行计划,以迅速产生查询结果的前 n 行。该参数为ORACLE 9I新引入的。
从ORACLE V7以来,optimizer_mode参数的缺省设置应是"choose",即如果对已分析的表查询的话选择CBO,否则选择RBO。在此种设置中,如果采用了CBO,则缺省为CBO中的all_rows模式。
注意:即使指定数据库使用RBO优化器,但有时ORACLE数据库还是会采用CBO优化器,这并不是ORACLE的BUG,主要是由于从ORACLE 8I后引入的许多新特性都必须在CBO下才能使用,而你的SQL语句可能正好使用了这些新特性,此时数据库会自动转为使用CBO优化器执行这些语句。
什么是优化
优化是选择最有效的执行计划来执行SQL语句的过程,这是在处理任何数据的语句(SELECT,INSERT,UPDATE或DELETE)中的一个重要步骤。对Oracle来说,执行这样的语句有许多不同的方法,譬如说,将随着以什么顺序访问哪些表或索引的不同而不同。所使用的执行计划可以决定语句能执行得有多快。Oracle中称之为优化器(Optimizer)的组件用来选择这种它认为最有效的执行计划。
由于一系列因素都会会影响语句的执行,优化器综合权衡各个因素,在众多的执行计划中选择认为是最佳的执行计划。然而,应用设计人员通常比优化器更知道关于特定应用的数据特点。无论优化器多么智能,在某些情况下开发人员能选择出比优化器选择的最优执行计划还要好的执行计划。这是需要人工干预数据库优化的主要原因。事实表明,在某些情况下,确实需要DBA对某些语句进行手工优化。
注:从Oracle的一个版本到另一个版本,优化器可能对同一语句生成不同的执行计划。在将来的Oracle 版本中,优化器可能会基于它可以用的更好、更理想的信息,作出更优的决策,从而导致为语句产生更优的执行计划。
- pdf.rar (552.6 KB)
- 下载次数: 73
发表评论
-
并发行级锁超2秒报警监控sql
2010-07-20 16:18 1872并发行级锁超2秒报警监控sql -
数据设计规范v
2010-06-22 19:47 0数据设计规范v -
Oracle 有趣排序包括那些
2010-06-20 16:51 1396按拼音排序 select * from table ... -
Oracle sql 性能优化调整
2010-06-20 16:15 1359Oracle sql ... -
Delete、Drop、Truncate的比较(转)
2010-05-31 13:39 1437Delete、Drop、Truncate的比较 关键字: d ... -
Oracle误操作解决方案(转)
2010-05-31 13:35 1360Oracle误操作解决方案 文章分类:数据库 一.误删除数 ... -
一个递归调用的存储过程
2010-04-19 01:51 1799一个递归调用的存储过程 -
oracle index学习总结
2009-12-08 10:44 19579oracle index 1.index需要储存空间 ... -
Jdbc 和hibernate
2009-11-16 23:38 1836Jdbc 和hibernate 一、Jdbc是java ... -
去掉 powerDesigner 中表设计时的 name和code联动的功能
2009-11-16 17:04 3439去掉 powerDesigner 中表设计时的 name和co ... -
oracle数据库索引未被使用的问题及其解决2007
2009-11-15 01:42 2100一次,在进行WEB页面上进行历史数据文件检查时,发现数据库访问 ... -
RMAN配置及备份与恢复练习
2009-09-27 18:06 15991.创建表空间SQL>create tablespace ... -
误删除数据后怎么立即恢复(不考虑全库备份和利用归档日志)
2009-09-27 18:01 3352要达到删除数据,有以下几种方式都可以:1、delete2、dr ... -
IMP数据到指定的表空间
2009-09-27 12:47 9060IMP数据到指定的表空间 一直以来,我都认为只要指定用户的默 ... -
ORACLE UPDATE 语句语法与性能分析
2009-09-27 09:49 1844为了方便起见,建立了以下简单模型,和构造了部分测试数据: 在 ... -
oracle job 例子
2009-09-22 12:38 3035一、设置初始化参 ... -
oracle 时间加减综合
2009-09-17 11:43 1957加法 select sysdate,add_mon ... -
详细介绍ORACLE sqlplus命令
2009-09-09 16:42 2020一、ORACLE的启动和关闭1、在单机环境下要想启动或关闭OR ... -
AWR介绍与SYSAUX空间关系 SYASAUX表空间满了 系统慢
2009-09-08 09:45 7840AWR介绍与SYSAUX空间关系 2007-11-05 14 ... -
to_char 'NLS_DATE_LANGUAG参数
2009-09-02 18:41 1437select to_char(sysdate, 'Day'), ...
相关推荐
通过分析SQL语句的执行计划优化SQL,F5执行计划如何优化
通过分析SQL语句的执行计划优化SQL总结)........
本文档主要介绍与SQL调整有关的内容,内容涉及多个方面:SQL语句执行的过程、ORACLE优化器,表之间的关联,如何得到SQL执行计划,如何分析执行计划等内容,从而由浅到深的方式了解SQL优化的过程,使大家逐步步入SQL...
判断SQL语句是否与共享内存中某一SQL相同Rowid ,Recursive SQL, Row Source ,Predicate Driving Table ,Probed Table等概念4种类型的索引扫描
通过分析SQL语句的执行计划优化SQL总结.doc
执行计划是SQL优化中最为复杂也是最为关键的部分,只有知道了ORACLE在内部到底是如何执行该SQL语句后,我们才能知道优化器选择的执行计划是否为最优的。执行计划对于DBA来说,就象财务报表对于财务人员一样重要。...
NULL 博文链接:https://desert3.iteye.com/blog/758643
[精华]通过分析SQL语句的执行计划优化SQL(总结)7年DBA工作经验[定义].pdf
Join过程的各个步骤经常是串行操作,即使相关的row source可以被并行访问,即可以并行的...通过首先存取特定的表,即将该表作为驱动表,这样可以先应用某些限制条件,从而得到一个较小的row source,使连接的效率较高。
执行计划的每一步返回一组行,它们或者为下一步所使用,或者在最后一步时返回给发出SQL语句的用户或应用。由每一步返回的一组行叫做行源(row source)。图5-1树状图显示了从一步到另一步行数据的流动情况。每步的...
SQL语句最优化SQL语句最优化SQL语句最优化SQL语句最优化
对于SQL语句存取的任何表中的任何行,可能存在许多存取路径(存取方法),通过它们可以定位和查询出需要的数据。优化器选择其中自认为是最优化的路径。在物理层,oracle读取数据,一次读取的最小单位为数据库块(由多个...