oracle性能优化专题.docx
- 文档编号:6686901
- 上传时间:2023-01-09
- 格式:DOCX
- 页数:13
- 大小:24.58KB
oracle性能优化专题.docx
《oracle性能优化专题.docx》由会员分享,可在线阅读,更多相关《oracle性能优化专题.docx(13页珍藏版)》请在冰豆网上搜索。
oracle性能优化专题
简单介绍一下优化的目标;
系统环境(基于成本的,基于规则的)
引入hint的基本概念
驱动表和被探测表概念,使用hint影响执行计划
索引相关概念,使用hint影响执行计划.
前言:
1
第一章优化的目标1
第二章认识优化的环境2
2.1基于规则的优化程序(RBO)2
2.2基于成本的优化程序(CBO)2
2.3优化模式的选择3
第三章oracle表的连接及调整3
3.1嵌套循环连接(nestedloop)4
3.2,哈希连接(hashjoin)7
3.3,排序合并连接(mergejoin)8
3.4跟表连接有关的几个HINT8
第四章索引11
第五章实例11
前言:
哥们一直在尝试寻找一种方法来优化基于oracle的系统,而不是仅仅优化某个数据库或者某个实例.
Oracle性能管理是一种利用已经证实过的方法,反复试验/判断和实现优化方案的循序渐进的过程.也就是说性能管理很多情况下要依靠我们对数据库的已有认识.但这种认识未必是正确的.所以,这里我们也会试着讨论一些关于oracle优化的荒谬的说法.这种说法可能被很多人认可,但却是不真实的.
误解1:
如果用更快的cpu升级硬件系统,将会得到更好的性能.
事实:
用更快的cpu升级硬件系统试图获得更好性能的方法,在cpu确实不是瓶颈时将会明显地降低性能.这是因为虽然cpu快了,但是IO系统并没相应加快,从而导致系统不平衡.如果使cpu的速度加倍,则受到IO瓶颈牵制的作业有可能要处理两倍的IO争用.CPU越强处理作业越快,而对IO资源的要求也就越多.因此在升级CPU前应该仔细考虑一下.
第一章优化的目标
1.1什么是优化
简单点说就是增加吞吐量,减少响应时间.
1.2为什么要优化
做了这么多年信息化,大家都有一种很强烈的感觉,很少有系统是在对怎样使用他们和对他们有什么要求的可靠理解下设计出来的.很少有系统在经过初始编码阶段后没有对设计做过大的变动的.
于是,本来好使的索引性能可能下降,数据的分布变得不再均匀.这些都会延长oracle返回数据给用户的时间.
1.3优化到何种程度,何时停止优化
Oracle的优化有点类似于对投影仪聚焦的调整.慢慢调整投影仪的旋钮会使得本来模糊的图像变得清晰,但如果拧过了头,则会使得图像再次模糊.
Oracle也一样.对oracle的优化也有可能过头.因为系统的每个组件是相互依赖的,更改一个可能会妨碍另外一个的性能.这表示优化一个运行5分钟的查询,使它只运行3秒而不是10秒可能并不总是一个好主意.如果以3秒运行这个查询导致其他操作(如某些不到1秒的基于关键任务的事务处理)的响应时间增加,所带来的问题可能会更多.
所以我们在执行优化之前需要制定一些清晰合理的,可以量化的目标.
误解2:
如果oracle数据库的高速缓存命中率很高(99.999%),则oracle的性能也很好.
事实:
根本不对,高速缓存命中率会由于应用程序中具有反复访问同一组数据块的几个相关子查询而提高.在这种情况下,即使高速缓存命中率很高,用户也可能要等待很长时间才能得到数据的输出.很多时候优化oracle根本就不涉及命中率.优化基于oracle的系统的基础是事件而不是命中率.
第二章认识优化的环境
2.1基于规则的优化程序(RBO)
在oracle7.0版本之前,oracle内部只存在基于规则的优化程序.优化器在分析SQL语句时,所遵循的是oracle内部预定的一些规则.
Oracle多表连接执行的最佳顺序应该是按照表的记录大小从小到大的顺序依次执行.我们在对表执行优化时的一个很重要的指导原则应该也是尽量使得执行计划按照表的从小到大的顺序进行关联(当然,最终的优化结果还是需要综合其他很多因素进行考量).
有资料说,oracle在建立执行树时,其关联数序是按照表在from后面出现的顺序按照倒叙排列的,这一点似乎正确.至少,大部分情况下,在from后面最后出现的表一般会作为第一个驱动表.但这不是绝对的,where谓词后的数据连接字段的位置和谓词中是否使用了对表的过滤字段也会对查询树的建立造成影响.
基本上,oracle为了避免更大的结果和另外一个未知(没执行analyze对表进行分析)大小的结果进行嵌套循环连接,会按照扫描表,扫描索引然后访问表,直接访问索引可以得到数据.这样的顺序对表进行关联.
比如说,如果一个sql查询语句中,需要对表进行acessfull,那么其顺序肯定是靠前的,而相对于可以直接在index中获得所有的结果,TABLEACCESSBYINDEXROWID的表顺序也比较靠前.在这之后才会考虑表在from后面出现的位置.
2.2基于成本的优化程序(CBO)
从oracle7.3开始,oracle引入CBO.它是看sql语句执行的代价来选择执行语法树的.这里的代价主要指CPU和内存.优化器在判断是否用某种执行方式时,主要参照的是表及索引的统计信息.统计信息存储在oracle的动态性能试图中,记录了表的大小,有多少行,每行的长度等信息.这些信息起初在库内是没有的.需要定期对表对象和索引对象执行analyze后才出现.很多的时候过期统计信息会令优化器做出一个错误的执行计划.
2.3优化模式的选择
Oracle的优化模式有四种:
Rule:
基于规则的方式;
Choose:
默认情况下oracle使用的方式.指的是当一个表或索引有统计信息,则走cbo的方式,如果表或索引没有统计信息,表又不是特别的小,而且相应的列有索引时,那么走rbo方式.
Firstrows:
与choose方式类似,不同处在于它将以最快的方式返回查询的最先的几行.
Allrows:
也就是所谓的rbo模式.
设定选用的优化模式
A,instance级别我们可以通过在initsid.ora文件中设定oprimizer_mode=rule/choose/first_rows/all_rows来修改数据库实例的优化模式,如果没有设定,则默认为choose模式;
B,sessions级别我们可以通过altersessionsetoptimizer_mode=rule/choose/first_rows/all_rows来设定.
C,语句级别使用hint(/*…*/)来设定.
我们的数据库往往是没有设定optimizer_mode的,也就是说是使用基于choose模式的优化方式.另外,我们的数据库表对象一般不存在统计信息,即使存在,也往往是不能及时更新.所以我们的执行多为RBO.
RBO模式的一个很重要的特点就是要求我们的编码人员自己调整关联表的执行顺序.这点由于在数据库设计和系统应用之初往往因为数据库数据量较小而没有太大影响.但随着数据量增加,会出现前期书写的语句或者设置的查询方式不再适合当前的数据库环境.
一句话,需要调整.
在大多数环境中,我们都必须接受他人的难以置信的编程技术或IO子系统.假定大部分性能问题都与SQL有关.所以这里我们主要将目标放在优化sql语句的技巧上.
第三章oracle表的连接及调整
在日常基于数据库应用的开发过程中,我们经常需要对多个表或者数据源进行关联查询而得出我们需要的结果集。
那么Oracle到底存在着哪几种连接方式?
优化器内部又是怎样处理这些连接的?
哪种连接方式又是适合哪种查询需求的?
只有对这些问题有了清晰的理解后,我们才能针对特定的查询需求选择合适的连接方式,开发出健壮的数据库应用程序。
选择合适的表连接方法对SQL语句运行的性能有着至关重要的影响。
下面我们就Oracle常用的一些连接方法及适用情景做一个简单的介绍。
3.1嵌套循环连接(nestedloop)
嵌套循环连接的工作方式是这样的:
1、Oracle首先选择一张表作为连接的驱动表,这张表也称为外部表(OuterTable)。
由驱动表进行驱动连接的表或数据源称为内部表(InnerTable)。
2、提取驱动表中符合条件的记录,与被驱动表的连接列进行关联查询符合条件的记录。
在这个过程中,Oracle首先提取驱动表中符合条件的第一条记录,再与内部表的连接列进行关联查询相应的记录行。
在关联查询的过程中,Oracle会持续提取驱动表中其他符合条件的记录与内部表关联查询。
这两个过程是并行进行的,因此嵌套循环连接返回前几条记录的速度是非常快的。
在这里需要说明的是,由于Oracle最小的IO单位为单个数据块,因此在这个过程中Oracle会首先提取驱动表中符合条件的单个数据块中的所有行,再与内部表进行关联连接查询的,然后提取下一个数据块中的记录持续地循环连接下去。
当然,如果单行记录跨越多个数据块的话,就是一次单条记录进行关联查询的。
3、嵌套循环连接的过程如下所示:
Nestedloop
Outerloop
Innerloop
我们可以看出这里面存在着两个循环,一个是外部循环,提取驱动表中符合条件的每条记录。
另外一个是内部循环,根据外循环中提取的每条记录对内部表进行连接查询相应的记录。
由于这两个循环是嵌套进行的,故此种连接方法称为嵌套循环连接。
嵌套循环连接适用于查询的选择性强、约束性高并且仅返回小部分记录的结果集。
通常要求驱动表的记录(符合条件的记录,通常通过高效的索引访问)较少,且被驱动表连接列有唯一索引或者选择性强的非唯一索引时,嵌套循环连接的效率是比较高的。
嵌套循环连接驱动表的选择也是连接中需要着重注意的一点,有一个常见的误区是驱动表要选择小表,其实这是不对的。
假如有两张表A、B关联查询,A表有1000000条记录,B表有10000条记录,但是A表过滤出来的记录只有10条,这时候显然用A表当做驱动表是比较合适的。
因此驱动表是由过滤条件限制返回记录最少的那张表,而不是根据表的大小来选择的。
在外连接查询中,如果走嵌套循环连接的话,那么驱动表必然是没有符合条件关联的那张表,也就是后面不加(+)的那张表。
这是由于外连接需要提取可能另一张表没符合条件的记录,因此驱动表需要是那张我们要返回所有符合条件记录的表。
比如下面这个查询,
嵌套循环连接返回前几行的记录是非常快的,这是因为使用了嵌套循环后,不需要等到全部循环结束再返回结果集,而是不断地将查询出来的结果集返回。
在这种情况下,终端用户将会快速地得到返回的首批记录,且同时等待Oracle内部处理其他记录并返回。
如果查询的驱动表的记录数非常多,或者被驱动表的连接列上无索引或索引不是高度可选的情况,嵌套循环连接的效率是非常低的
--删除原表
droptablet1;
--建立测试表
createtablet1(
f1varchar2(10),
f2varchar2(1000)
)
tablespaceCTL
pctfree98;
--填充测试内容
insertintot1(f1,f2)
selectrownum,lpad(rownum,700,'0')
fromdba_tablesa,dba_tab_colsb
wherea.owner=b.owner
andrownum<10000;
commit;
--检查测试内容格式
selectsys.dbms_rowid.rowid_block_number(rowid),f1,f2fromt1;
--每条记录都存储在单独的数据块中
selectcount(distinctsys.dbms_rowid.rowid_block_number(rowid))fromt1;
/*
用同样的方式建立表t2
*/
--删除原表
droptablet2;
--建立测试表
createtablet2(
f1varchar2(10),
f2varchar2(1000)
)
tablespaceCTL
pctfree98;
--填充测试内容
insertintot2(f1,f2)
selectrownum*10,lpad(rownum*10,700,'0')
fromdba_tablesa,dba_tab_colsb
wherea.owner=b.owner
andrownum<1000;
commit;
--检查测试内容格式
selectsys.dbms_rowid.rowid_block_number(rowid),f1,f2fromt2;
--每条记录都存储在单独的数据块中
selectcount(distinctsys.dbms_rowid.rowid_block_number(rowid))fromt2;
createindexind_t1_f1ont1(f1);
createindexind_t2_f1ont2(f1);
--首先我们来看使用nestedloop关联方式,不同表作为驱动时的情况.
1,表t2作为驱动表
select/*+ordereduse_nl(t1,t2)*/
t1.f1,t2.f1
fromctl.t2t2,ctl.t1t1
wheret1.f1=t2.f1
andt1.f1<1000;
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=84Card=4Bytes=56)
10NESTEDLOOPS(Cost=84Card=4Bytes=56)
21TABLEACCESS(FULL)OF'T2'(Cost=2Card=82Bytes=574)
31INDEX(RANGESCAN)OF'IND_T1_F1'(NON-UNIQUE)(Cost=1C
ard=1Bytes=7)
Cost=outeraccesscost+(inneraccesscost*outercardinality)
Cost=2+1*82=84;
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
2020consistentgets
23physicalreads
0redosize
2650bytessentviaSQL*Nettoclient
721bytesreceivedviaSQL*Netfromclient
8SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
99rowsprocessed
2,t1作为驱动表
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=6Card=4Bytes=56)
10NESTEDLOOPS(Cost=6Card=4Bytes=56)
21TABLEACCESS(FULL)OF'T1'(Cost=2Card=4Bytes=28)
31INDEX(RANGESCAN)OF'IND_T2_F1'(NON-UNIQUE)(Cost=1C
ard=1Bytes=7)
Cost=outeraccesscost+(inneraccesscost*outercardinality)
Cost=2+1*4=84;
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
11123consistentgets
3physicalreads
0redosize
2650bytessentviaSQL*Nettoclient
721bytesreceivedviaSQL*Netfromclient
8SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
99rowsprocessed
3.2,哈希连接(hashjoin)
哈希连接分为两个阶段,如下。
1、构建阶段:
优化器首先选择一张小表做为驱动表,运用哈希函数对连接列进行计算产生一张哈希表。
通常这个步骤是在内存(hash_area_size)里面进行的,因此运算很快。
2、探测阶段:
优化器对被驱动表的连接列运用同样的哈希函数计算得到的结果与前面形成的哈希表进行探测返回符合条件的记录。
这个阶段中如果被驱动表的连接列的值没有与驱动表连接列的值相等的话,那么这些记录将会被丢弃而不进行探测
哈希连接比较适用于返回大数据量结果集的连接。
使用哈希连接必须是在CBO模式下,参数hash_join_enabled设置为true,
哈希连接只适用于等值连接。
从Oracle9i开始,哈希连接由于其良好的性能渐渐取代了原来的排序合并连接。
SQL>select/*+ordereduse_hash(t1,t2)*/
t1.f1,t2.f1
fromctl.t1t1,ctl.t2t2
wheret1.f1=t2.f1234;
999rowsselected.
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=5Card=82Bytes=1148
)
10HASHJOIN(Cost=5Card=82Bytes=1148)
21TABLEACCESS(FULL)OF'T1'(Cost=2Card=82Bytes=574)
31TABLEACCESS(FULL)OF'T2'(Cost=2Card=82Bytes=574)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
11113consistentgets
0physicalreads
0redosize
23590bytessentviaSQL*Nettoclient
1381bytesreceivedviaSQL*Netfromclient
68SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
999rowsprocessed
3.3,排序合并连接(mergejoin)
排序合并连接的方法非常简单。
在排序合并连接中是没有驱动表的概念的,两个互相连接的表按连接列的值先排序,排序完后形成的结果集再互相进行合并连接提取符合条件的记录。
相比嵌套循环连接,排序合并连接比较适用于返回大数据量的结果。
排序合并连接在数据表预先排序好的情况下效率是非常高的,也比较适用于非等值连接的情况,比如>、>=、<=等情况下的连接(哈希连接只适用于等值连接)。
由于Oracle中排序操作的开销是非常消耗资源的,当结果集很大时排序合并连接的性能很差,于是Oracle在7.3之后推出了新的连接方式——哈希连接。
1,rbo模式;
2,不等于关联(><>=<=<>)
3,hash_join_enabled=false;
4,数据源已排序
3.4跟表连接有关的几个HINT
测试环境:
数据库版本:
oracle9.2.8.0
操作系统环境:
aix
数据库实例:
BI
针对查询
selectdpi.model_name,psj.day_sale
fromdw.d_product_infodpi,
dw.pm_stock_sale_jybpsj
wheredpi.product_id=psj.product_id
SELECTSTATEMENT,GOAL=CHOOSE
MERGEJOIN
SORTJOIN
TABLEACCESSFULL对象名称=PM_STOCK_SALE_JYB
SORTJOIN
TABLEACCESSFULL对象名称=D_PRODUCT_INFO
(1)use_nl(t1,t2):
表示对表t1、t2关联时采用嵌套循环连接。
select/*+use_nl(dpi,psj)*/
dpi.model_name,psj.day_sale
fromdw.d_product_infodpi,
dw.pm_stock_sale_jybpsj
wheredpi.product_id=psj.product_id
(2)use_merge(t1,t2):
表示对表t1、t2关联时采用排序合并连接。
select/*+use_merge(dpi,psj)*/
dpi.model_name,psj.day_sale
fromdw.d_product_infodpi,
dw.pm_stock_sale_jybpsj
wheredpi.product_id=psj.product_id
(3)use_hash(t1,t2):
表示对表t1、t2关联时采用哈希连接。
select/*+use_hash(dpi,psj)*/
dpi.model_name,psj.day_sale
fromdw.d_product_infodpi,
dw.pm_stock_sale_jybpsj
wheredpi.product_id=psj.product_id
(4)leading(t):
表示在进行表连接时,选择t为驱动表。
select/*+leading(psj)use_hash(dpi,psj)*/
dpi.model_name,psj.day_sale
fromdw.d_product_infodpi,
dw.pm_stock_sale_jybpsj
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 性能 优化 专题