ORACLE性能优化之SQL优化优化器.docx
- 文档编号:6167015
- 上传时间:2023-01-04
- 格式:DOCX
- 页数:37
- 大小:93.31KB
ORACLE性能优化之SQL优化优化器.docx
《ORACLE性能优化之SQL优化优化器.docx》由会员分享,可在线阅读,更多相关《ORACLE性能优化之SQL优化优化器.docx(37页珍藏版)》请在冰豆网上搜索。
ORACLE性能优化之SQL优化优化器
Oracle9i优化器介绍
ByDavis
E-Mail:
todavis@
Blog:
选择合适的优化器目标
默认情况下,CBO以最佳吞吐量为目标,这意味着Oracle使用尽可能少的资源去处理
被语句访问到的所有行;当然CBO也可以用最快的响应速度来优化SQL,这意味着Oracle
用尽可能少的资源去处理被语句访问到的第一行或前面少数行,当然这种情况对于整个语句
来说可能消耗更多的资源。
优化器产生的执行计划会因“优化器目标”的不同而不同。
如果以最佳吞吐量为目标,
结果更倾向于使用全表扫描而不是索引扫描,或者使用排序合并连接而不是嵌套循环连接;
如果以最快的响应速度为目标,其结果则通常倾向于使用索引扫描和嵌套循环连接。
例如,假使你有一个语句既能运行于嵌套循环连接又能运行于排序合并连接,排序合并
连接能够较快的返回全部查询结果,而嵌套循环能快速的返回第一行或前面少数行结果。
如
果你是以提高吞吐量为优化器目标,优化器就会倾向于选择排序合并连接;如果你的优化器
目标是提高响应速度,则优化器倾向于选择嵌套循环连接。
选择优化器目标要以你的应用为基础,一般规则是:
1、对于批处理应用,以最佳吞吐量为优化目标为好。
例如Oracle报表应用程序。
2、对于交互式应用,以最快响应速度为优化目标为好。
例如SQLPLUS的查询。
影响优化器优化目标的因素主要有:
1、OPTIMIZER_MODE初始化参数。
2、数据字典中的CBO统计数据。
3、用来改变CBO优化目标的Hints。
OPTIMIZER_MODE初始化参数
这个初始化参数用来规定实例的默认优化方法。
其值列表及说明如下:
Value
CHOOSE
ALL_ROWS
Description
此为缺省值。
优化器既可以使用基于成本的优化方法(CBO),也可以使用
基于规则的优化方法(RBO),其决定于是否有可用的统计信息。
1、如果在被访问的表中,至少有一个表在数据字典中有可用的统计
信息存在,则优化器使用基于成本的方法。
2、如果在被访问的表中,只有部分表在数据字典中有可用的统计信
息,优化器仍然会使用基于成本的方法,但是优化器必须为无统
计信息的表利用一些内部信息去尝试其他的统计,比如分配给这
些表的数据块的数量等,这可能会导致产生不理想的执行计划。
3、如果在被访问的表中,没有一个表在数据字典中有统计信息,则
优化器使用基于规则的方法。
不论是否有统计信息存在,优化器都使用基于成本的方法,并以最佳吞
1
吐量为优化目标。
FIRST_ROWS_n不论是否有统计信息存在,优化器都使用基于成本的方法,并以最快的
速度返回前n行数据集,n可以是1,10,100,1000。
FIRST_ROWS
RULE
优化器使用成本与试探法混合的方式,去寻找一个可以最快返回前面少
数行的执行计划。
注:
CBO使用试探法产生的执行计划,其成本可能会比不使用试探法要
大。
FIRST_ROWS可用于向后兼容和计划稳定性。
不论是否有统计信息存在,优化器都会使用基于规则的方法。
你可以在SESSION中改变CBO优化目标:
ALTERSESSIONSETOPTIMIZER_MODE。
例如:
1、在初始化参数文件中加入如下语句,可以在实例级改变CBO优化目标:
OPTIMIZER_MODE=FIRST_ROWS_1
2、下面的语句可以改变当前SESSION的CBO优化目标:
ALTERSESSIONSETOPTIMIZER_MODE=FIRST_ROWS_1
可以改变CBO优化目标的Hints
使用如下Hints可以单独为具体的SQL指定CBO优化目标,SQL语句中Hints能够覆
盖OPTIMIZER_MODE初始化参数。
●FIRST_ROWS(n),n为任意正整数。
●FIRST_ROWS
●ALL_ROWS
●CHOOSE
●RULE
数据字典中的CBO统计信息
CBO使用的统计信息存放于数据字典中,你可以使用DBMS_STATS包或ANALYZE
语句以精确的方式或估算的方式来统计对象的物理存储特征和数据分布情况。
注意:
Oracle公司建议使用DBMS_STATS包来代替ANALYZE语句收集统计信息。
DBMS_STATS包可以并行的收集统计信息,可以为分区对象收集全局统计信息,以及使用
其他方式优化收集操作。
但是,收集和基于成本优化器无关的信息必须用ANALYZE而不是DBMS_STATS,比
如:
●使用VALIDATE或LISTCHAINEDROWS子句。
●收集freelist块的信息。
CBO如何对SQL做最快响应的优化
2
OPTIMIZER_MODE被设置成FIRST_ROWS_n、FIRST_ROWSS,或者SQL语句中使
用了FIRST_ROWS(n)、FIRST_ROWS提示,CBO都会对SQL做最快响应的优化。
这非常适用于联机用户,像通过OracleForms或Web访问的用户。
联机用户的特点是
只对前面少数行感兴趣,很少看整个查询的结果,特别是在查询结果巨大的情况下。
对于这
样的用户,优化SQL使前面少数行尽可能快速的返回是有意义的,即使产生整个查询结果
的时间并不理想。
CBO在做这种优化时,会产生一个处理第一行或前面少数行消耗成本最低的执行计划。
CBO有两种用来产生最快响应速度的方法,一个是旧方法一个是新方法。
旧的方法就是用
FIRST_ROWS提示或初始化参数,这种方法CBO会使用成本和规则混合的方式来产生一个
计划。
Oracle保留这种方法是为了向后兼容。
新方法FIRST_ROWS_n或FIRST_ROWS(n)提示,是完全基于成本的。
如果n值较小,
CBO倾向于产生一个包含嵌套循环连接和索引查询的执行计划;如果n值较大,则CBO倾
向于产生一个包含散列连接和全表扫描的执行计划。
理解基于成本的优化器
CBO根据可用的访问路径和表、索引等对象的统计信息来确定当前SQL的哪个执行计
划是最高效的或成本最低的;同时CBO也会考虑Hints的建议。
CBO执行下列步骤:
1、优化器根据可用的访问路径和Hints为SQL语句产生一组潜在的执行计划。
2、优化器根据数据字典的统计信息评估每个计划的成本。
成本就是一个评估值,它与SQL语句按照某个计划执行所消耗的计算机资源是成
正比的。
优化器基于对计算机资源(I/O、CPU、内存)的评估,计算访问路径和连接顺序
的成本。
3、优化器对比执行计划的成本,从而选择一个成本最低的执行计划。
CBO包含下列组件:
●查询变换器(QueryTransformer)
●评估器(Estimator)
●计划生成器(PlanGenerator)
如下图所示:
3
查询变换器
(基于成本的优化器组件)
被解析器解析过的查询语句进入查询变换器,表现出来的是一组查询块(queryblock),
这些查询块之间是相互关联的或者是嵌套的,查询的形式决定这些查询块相互之间如何被关
联。
查询变换器的主要目的就是决定改变查询的形式是否有利于产生一个好的执行计划。
查
询变换器使用四种不同的查询变换技术:
●视图合并(ViewMerging)
●谓词推进(PredicatePushing)
●非嵌套子查询(SubqueryUnnesting)
●物化视图的查询重写(QueryRewritewithMaterializedViews)
最终应用于查询的也可以是以上四种变换技术的任意组合。
视图合并
查询中的每个视图都会被解析器扩展到一个独立的查询块中,这个查询块本质上是用来
描述视图定义的,是视图的结果。
优化器的一个任务就是去分析这个独立视图查询块(view
queryblock)并产生一个视图子计划(subplan),然后优化器在产生整个查询执行计划的同时使
用视图子计划来处理剩余的查询部分。
由于视图是被独立在整个查询之外被优化的,因此这
种技术常常会导致一个不良执行计划的产生。
查询变换器通过将视图查询块合并到查询块中从而消除这种不良执行计划。
绝大多数类
型的视图是可以被合并的。
在一个视图被合并后,它原有的视图查询块被包含到查询块中,
也就是说视图查询块不存在了,因此也不再需要产生一个子计划。
谓词推进
4
对于那些不能合并的视图,查询变换器能够将相关的谓词从查询块中推进到视图查询块
中。
由于被推进的谓词能够用来访问索引或者用于过滤,这个技术通常可以改进那些不能被
合并的视图子计划。
非嵌套的子查询
和视图一样,子查询也是用一个独立的查询块来代表的。
子查询是被嵌套在主查询或其
他子查询之中的,计划产生器在找到一个成本最低的执行计划之前被迫要试验所有可能的计
划。
由嵌套子查询产生的限制可以在转换为非嵌套的子查询和连接之后消除,经由查询转换
器过滤之后绝大多数的子查询都会被转换为非嵌套的,然后这些非嵌套的子查询产生独立的
子计划,这些子计划按照一种高效的方式进行排列,从而提高了整个查询计划的执行速度。
物化视图的查询重写
物化视图就是把一个查询的结果事先固化存储在一个表里,当发现和物化视图一致的查
询语句就将相应的项用物化视图来重写。
由于绝大多数的查询结果都事先计算好了,因此这
种技术可以极大的提高查询速度。
查询转换器负责查找和用户查询相关的所有物化视图,用
其中的一个或多个来重写查询。
利用物化视图来重写查询也是基于成本的,如果不使用物化
视图的成本更低一些,则不会去使用物化视图。
评估器
评估器会产生下列三个度量值:
●选择性(Selectivity)
●基数(Cardinality)
●成本(Cost)
这些值是相互关联的,一个值由其他值导出,评估器的最终目标是评估计划的总体成本。
如果有统计信息可用,评估器使用统计信息来计算这些值,统计信息可以提高其精确度。
选择性
这里的第一个度量值——选择性,表示所选择的行与行集的比值。
所谓行集可以是表、
视图,或者是一个连接或GROUPBY操作的中间结果。
选择性与查询中的谓词有关,比如
last_name=’Smith’,或者一个联合谓词last_name=’Smith’andjob_type=’Clerk’。
一个谓词充
当着一个过滤器的角色,在行集中过滤了一定量的行,谓词的选择性是一个比值,它表示一
个行集经过谓词的过滤后剩下的行占原有行集的比例。
其值在0.0和1.0之间,0.0表示在行
集中没有行被选择;1.0表示行集中的所有行都被选择了。
如果没有可用的统计信息,评估器为选择性赋予一个内部的缺省值,这个内部缺省值随
着谓词的不同而不同。
例如:
等式谓词(last_name=’Smith’)的内部缺省值低于范围谓词
(last_name>’Smith’),评估器会假定等式谓词返回的行数小于范围谓词。
当存在可用的统计信息,评估器将使用统计信息来估算选择性。
例如:
对于一个等式谓
词(last_name=’Smith’),选择性的值是distinctlast_name的倒数即:
(1/count(distinct
last_name))。
但是如果在last_name字段上存在直方图(histogram),则选择性值为:
count
5
(last_name)wherelast_name=’Smith’/count(last_name)wherelast_nameisnotnull。
可见
在数据倾斜的字段上应用直方图能够帮助CBO进行准确的选择性评估。
基数
基数就是行集中行的数量。
基数分为:
●基础基数(Basecardinality):
就是基表中的行数。
基础基数在表分析期间获得。
如
果表没有可用的统计信息,则评估器利用表中区(extents)的数量来估算基础基数。
●有效基数(Effectivecardinality):
就是从基表中选择的行数。
有效基数与具体的谓
词和字段有关。
有效基数是根据基础基数和作用于该表的所有谓词的选择性得出
的,如果没有谓词作用于该表,则有效基数就等于基础基数。
●连接基数(Joincardinality):
就是两个行集在连接之后产生的行数。
连接就是由两
个行集产生的笛卡尔积,再由连接谓词过滤结果。
因此,连接基数是两个行集基数
与连接谓词选择性的乘积。
●Distinct基数(Distinctcardinality):
就是一个行集的字段distinct之后的行数。
一个
行集的distinct基数是基于字段中的数据的。
例如:
一个拥有100行的行集,如果
一个字段distinct之后还剩下20行,则distinct基数就为20。
●Group基数(Groupcardinality):
就是一个行集在应用GROUPBY之后产生行的数
量。
Group基数依赖于每个组中字段的distinct基数和行集的行数。
GROUP基数例子:
假如对一个有100行的行集groupbycolx,colx字段的distinct基数是30,则Group基
数为30。
但是如果groupbycolx,coly呢?
coly字段的distinct基数是60,这种情况下Group
基数大于max(colxdistinct基数,colydistinct基数),而小于min(colxdistinct基数*colydistinct
基数,行集的行数),用公式表示出来如下:
groupcardinalityliesbetween
max(dist.card.colx,dist.card.coly)
and
min((dist.card.colx*dist.card.coly),numrowsinrowset)
对于上面的例子Group基数大于max(30,60)而小于min(30*60,100),也就是Group
基数位于60和100之间。
成本
成本是用来描述工作单元或资源使用的。
CBO是用磁盘I/O、CPU和内存的使用情况
来作为工作单元的,因此CBO使用的成本可以描述为,在一次操作的执行过程中所用的磁
盘I/O数量以及CPU和内存的总使用量。
这里的操作可以是扫描一张表、通过索引访问表、
连接两个表、或者一个行集的排序。
一个查询计划的成本就是运行这个查询并产生结果的同
时需要的工作单元的数量。
访问路径(accesspath)决定着在基表中获得数据所需要的工作单元数量。
访问路径可
以是表扫描(tablescan)、快速全索引扫描(fastfullindexscan)、索引扫描(indexscan)等。
在表扫描或快速全索引扫描期间,多个块可以在一次I/O中获得,因此表扫描或快速全索引
扫描的成本依赖于被扫描的块数和多块读取的数量。
索引扫描的成本依赖于B树的深度、
被扫描的索引页块数量、和用ROWID获取的行数,使用ROWID获取行的成本倚赖于索引
6
聚集因子(clusteringfactor)。
尽管聚集因子是索引的一个属性,它实际也关系到表数据块中被索引的字段值。
一较低
的聚集因子表明行被集中在表的少数块里,相反一个较高的聚集因子表明行被随机分散到表
的数据块中。
因此,聚集因子过高意味着通过范围扫描用ROWID获取行成本会较高,因为
需要访问表中过多的块才能返回数据。
聚集因子对成本的影响
假设环境如下:
●一个表有9行数据。
●在col1上有一个非唯一索引
●distinctcol1值是A、B、C
●这个表占据三个Oracle块
第一种情况:
索引聚集因子低,如下图:
Block1Block2Block3
----------------------
AAABBBCCC
索引字段相同的值都在同一个物理块中,这种情况下做范围扫描返回col1=A的所有的
行成本就很低,因为只需要在表中读取一个块就可以返回数据。
第二种情况:
索引聚集因子高,如下图:
Block1Block2Block3
----------------------
ABCABCABC
索引字段相同的值被分散存储到表中的块,这时要得到col1=A的行则要读取三个块。
联合单独访问两个表的成本就是连接的成本,在一个连接中分为内行集和外行集。
●嵌套循环连接(nestedloopjoin):
对于外行集中的每一行都要在内行集寻找全部与
它匹配的行,然后连接。
因此,在嵌套循环连接中外行集有多少行,内行集就被访
问多少次。
成本计算公式如下:
cost=outeraccesscost+(inneraccesscost*outercardinality)
●排序合并连接(sortmergejoin):
如果两个行集的连接键是无序的,则进行排序。
成本计算公式如下:
cost=outeraccesscost+inneraccesscost+sortcosts(ifsortisused)
●散列连接(hashjoin):
内部行集被散列到内存中,并用连接键建立一个散列表,
然后探测外部行集并连接与之匹配的行。
如果内部行集非常大,则只会把一部分散
列到内存中,这叫做一个散列分区。
此时,内存中的散列分区探测外部行集并连接
所有匹配的行,重复这个过程直到用完内部行集的所有分区。
成本计算公式如下:
cost=(outeraccesscost*numofhashpartitions)+inneraccesscost
计划生成器
7
由于不同的访问路径、连接方式和连接顺序可以任意组合,以不同的方式访问和处理数
据,但可以产生同样的结果,因此一个SQL可能存在大量不同的计划。
计划生成器的主要
作用正是为查询试验出所有这些可能存在的计划,并选择一个其中成本最低的。
连接顺序就是不同的连接项(如,表)以一定的顺序被访问和连接在一起。
例如:
有一
个连接按照t1、t2、t3的顺序,则t1是第一个被访问的,然后是t2,访问t2的同时与t1做
连接并产生连接后的结果,最后t3被访问,t3的数据与t1和t2产生的中间结果做连接。
在建立一个查询的计划之前要先为每个被嵌套的子查询和未合并的视图建立子计划,每
个嵌套的子查询和未合并的视图都是独立的查询块,这些查询块以自底向上的顺序进行优
化,也就是最里层的查询块最先优化并产生子计划,最外层的查询块最后优化。
计划生成器通过试验不同的访问路径、连接方式和连接顺序去探测各种计划,对于一个
查询来说可能存在的计划与FROM字句后面的连接项是成比例的,并以指数增长。
然而实
际上计划生成器很少会试验所有的可能存在的计划,如果它发现当前计划的成本已经很低
了,它将停止试验,相反当前计划的成本如果很高它将继续试验其他计划,因此如果计划生
成器一开始就能够找到一个成本较低的计划则会大量减少时间,计划生成器通常按照连接项
有效基数由小到大的顺序排列初使连接,。
理解执行计划
Oracle用来运行一个语句的步骤就叫做执行计划(executionplan),执行计划包含了语
句所涉及的每个表的访问路径和连接顺序。
执行计划概述
使用EXPLAINPLAN语句可以查看优化器所选择的执行计划,下面看一个例子:
1、创建PLAN_TABLE,用来存放执行计划的描述信息:
CONNECTHR/your_password
@$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL
Tablecreated.
当然你也可以改变PLAN_TABLE的名字。
注意:
Oracle公司建议你在做完数据库版本升级之后删除PLAN_TABLE然后再重建,因为字
段可能会有所变化,这可能会导致脚本失效或TKPROF失效。
当然你也可以改变PLAN_TABLE的名字。
2、运行执行计划:
8
EXPLAINPLANFOR
SELECTe.employee_id,j.job_title,e.salary,d.department_name
FROMemployeese,jobsj,departmentsd
WHEREe.employee_id<103
ANDe.job_id=j.job_id
ANDe.department_id=d.department_id;
用下面的语句可以指定PLAN_TABLE的名字:
EXPLAINPLAN
INTOmy_plan_table
FOR
YOUR_SQL;
3、显示执行计划信息:
这里可以用以下两个脚本
UTLXPLS.SQL–显示计划表信息,以串行的方式处理。
UTLXPLP.SQL–显示计划表信息,以并行的方式处理。
@$ORACLE_HOME/rdbms/utlxplp.sql;
-----------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|
-----------------------------------------------------------------------------------
|0|SELECTSTATEMENT||3|189|10(10)|
|1|NESTEDLOOPS||3|189|10(10)|
|2|NESTEDLOOPS||3|141|7(15)|
|*3|TABLEACCESSFULL|EMPLOYEES|3|60|4(25)|
|4|TABLEACCESSBYINDEXROWID|JOBS|19|513|2(50)|
|*5|INDEXUNIQU
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE 性能 优化 SQL