没有合适的资源?快使用搜索试试~ 我知道了~
oracle-优化器详解.docx
1.该资源内容由用户上传,如若侵权请联系客服进行举报
2.虚拟产品一经售出概不退款(资源遇到问题,请及时私信上传者)
2.虚拟产品一经售出概不退款(资源遇到问题,请及时私信上传者)
版权申诉
0 下载量 45 浏览量
2023-09-12
21:31:09
上传
评论
收藏 93KB DOCX 举报
温馨提示
试读
22页
oracle_优化器详解
资源推荐
资源详情
资源评论
1
Oracle9i 优化器介绍
选择合适的优化器目标
默认情况下,CBO 以最佳吞吐量为目标,这意味着 Oracle 使用尽可能少的资源去处理
被语句访问到的所有行;当然 CBO 也可以用最快的响应速度来优化 SQL,这意味着 Oracle
用尽可能少的资源去处理被语句访问到的第一行或前面少数行,当然这种情况对于整个语句
来说可能消耗更多的资源。
优化器产生的执行计划会因“优化器目标”的不同而不同。如果以最佳吞吐量为目标,
结果更倾向于使用全表扫描而不是索引扫描,或者使用排序合并连接而不是嵌套循环连接;
如果以最快的响应速度为目标,其结果则通常倾向于使用索引扫描和嵌套循环连接。
例如,假使你有一个语句既能运行于嵌套循环连接又能运行于排序合并连接,排序合并
连接能够较快的返回全部查询结果,而嵌套循环能快速的返回第一行或前面少数行结果。如
果你是以提高吞吐量为优化器目标,优化器就会倾向于选择排序合并连接;如果你的优化器
目标是提高响应速度,则优化器倾向于选择嵌套循环连接。
选择优化器目标要以你的应用为基础,一般规则是:
1、 对于批处理应用,以最佳吞吐量为优化目标为好。例如 Oracle 报表应用程序。
2、 对于交互式应用,以最快响应速度为优化目标为好。例如 SQLPLUS 的查询。
影响优化器优化目标的因素主要有:
1、 OPTIMIZER_MODE 初始化参数。
2、 数据字典中的 CBO 统计数据。
3、 用来改变 CBO 优化目标的 Hints。
OPTIMIZER_MODE 初始化参数
这个初始化参数用来规定实例的默认优化方法。其值列表及说明如下:
Value
Description
CHOOSE
此为缺省值。优化器既可以使用基于成本的优化方法(CBO),也可以使用
基于规则的优化方法(RBO),其决定于是否有可用的统计信息。
1、 如果在被访问的表中,至少有一个表在数据字典中有可用的统计信
息存在,则优化器使用基于成本的方法。
2、 如果在被访问的表中,只有部分表在数据字典中有可用的统计信
息,优化器仍然会使用基于成本的方法,但是优化器必须为无统
计信息的表利用一些内部信息去尝试其他的统计,比如分配给这
些表的数据块的数量等,这可能会导致产生不理想的执行计划。
3、 如果在被访问的表中,没有一个表在数据字典中有统计信息,则
优化器使用基于规则的方法。
ALL_ROWS
不论是否有统计信息存在,优化器都使用基于成本的方法,并以最佳吞
吐量为优化目标。
FIRST_ROWS_n
不论是否有统计信息存在,优化器都使用基于成本的方法,并以最快的
速度返回前 n 行数据集,n 可以是 1,10,100,1000。
2
FIRST_ROWS
优化器使用成本与试探法混合的方式,去寻找一个可以最快返回前面少
数行的执行计划。
注:CBO 使用试探法产生的执行计划,其成本可能会比不使用试探法要
大。FIRST_ROWS 可用于向后兼容和计划稳定性。
RULE
不论是否有统计信息存在,优化器都会使用基于规则的方法。
你可以在 SESSION 中改变 CBO 优化目标:ALTER SESSION SET OPTIMIZER_MODE。
例如:
1、在初始化参数文件中加入如下语句,可以在实例级改变 CBO 优化目标:
OPTIMIZER_MODE=FIRST_ROWS_1
2、下面的语句可以改变当前 SESSION 的 CBO 优化目标:
ALTER SESSION SET OPTIMIZER_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 或 LIST CHAINED ROWS 子句。
收集 freelist 块的信息。
CBO 如何对 SQL 做最快响应的优化
3
OPTIMIZER_MODE 被设置成 FIRST_ROWS_n、FIRST_ROWSS,或者 SQL 语句中使
用了 FIRST_ROWS(n)、FIRST_ROWS 提示,CBO 都会对 SQL 做最快响应的优化。
这非常适用于联机用户,像通过 Oracle Forms 或 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 包含下列组件:
查询变换器(Query Transformer)
评估器(Estimator)
计划生成器(Plan Generator)
如下图所示:
4
(基于成本的优化器组件)
查询变换器
被解析器解析过的查询语句进入查询变换器,表现出来的是一组查询块(query block),
这些查询块之间是相互关联的或者是嵌套的,查询的形式决定这些查询块相互之间如何被关
联。查询变换器的主要目的就是决定改变查询的形式是否有利于产生一个好的执行计划。查
询变换器使用四种不同的查询变换技术:
视图合并(View Merging)
谓词推进(Predicate Pushing)
非嵌套子查询(Subquery Unnesting)
物化视图的查询重写(Query Rewrite with Materialized Views)
最终应用于查询的也可以是以上四种变换技术的任意组合。
视图合并
查询中的每个视图都会被解析器扩展到一个独立的查询块中,这个查询块本质上是用来
描述视图定义的,是视图的结果。优化器的一个任务就是去分析这个独立视图查询块(view
query block)并产生一个视图子计划(subplan),然后优化器在产生整个查询执行计划的同时使
用视图子计划来处理剩余的查询部分。由于视图是被独立在整个查询之外被优化的,因此这
种技术常常会导致一个不良执行计划的产生。
查询变换器通过将视图查询块合并到查询块中从而消除这种不良执行计划。绝大多数类
型的视图是可以被合并的。在一个视图被合并后,它原有的视图查询块被包含到查询块中,
也就是说视图查询块不存在了,因此也不再需要产生一个子计划。
谓词推进
5
对于那些不能合并的视图,查询变换器能够将相关的谓词从查询块中推进到视图查询块中。
由于被推进的谓词能够用来访问索引或者用于过滤,这个技术通常可以改进那些不能被合并
的视图子计划。
非嵌套的子查询
和视图一样,子查询也是用一个独立的查询块来代表的。子查询是被嵌套在主查询或其他
子查询之中的,计划产生器在找到一个成本最低的执行计划之前被迫要试验所有可能的计划。由
嵌套子查询产生的限制可以在转换为非嵌套的子查询和连接之后消除,经由查询转换器过滤
之后绝大多数的子查询都会被转换为非嵌套的,然后这些非嵌套的子查询产生独立的子计划,
这些子计划按照一种高效的方式进行排列,从而提高了整个查询计划的执行速度。
物化视图的查询重写
物化视图就是把一个查询的结果事先固化存储在一个表里,当发现和物化视图一致的查
询语句就将相应的项用物化视图来重写。由于绝大多数的查询结果都事先计算好了,因此这
种技术可以极大的提高查询速度。查询转换器负责查找和用户查询相关的所有物化视图,用
其中的一个或多个来重写查询。利用物化视图来重写查询也是基于成本的,如果不使用物化视
图的成本更低一些,则不会去使用物化视图。
评估器
评估器会产生下列三个度量值:
选择性(Selectivity)
基数(Cardinality)
成本(Cost)
这些值是相互关联的,一个值由其他值导出,评估器的最终目标是评估计划的总体成本。如果
有统计信息可用,评估器使用统计信息来计算这些值,统计信息可以提高其精确度。
选择性
这里的第一个度量值——选择性,表示所选择的行与行集的比值。所谓行集可以是表、
视图,或者是一个连接或 GROUP BY 操作的中间结果。选择性与查询中的谓词有关,比如
last_name=’Smith’,或者一个联合谓词 last_name=’Smith’ and job_type=’Clerk’。一个谓词充
当着一个过滤器的角色,在行集中过滤了一定量的行,谓词的选择性是一个比值,它表示一
个行集经过谓词的过滤后剩下的行占原有行集的比例。其值在 0.0 和 1.0 之间,0.0 表示在行
集中没有行被选择;1.0 表示行集中的所有行都被选择了。
如果没有可用的统计信息,评估器为选择性赋予一个内部的缺省值,这个内部缺省值随
着谓词的不同而不同。例如:等式谓词(last_name=’Smith’)的内部缺省值低于范围谓词
(last_name>’Smith’),评估器会假定等式谓词返回的行数小于范围谓词。
当存在可用的统计信息,评估器将使用统计信息来估算选择性。例如:对于一个等式谓
词( last_name=’Smith’ ), 选择性的值是 distinct last_name 的倒数即:( 1/count(distinct
last_name))。但是如果在 last_name 字段上存在直方图(histogram),则选择性值为:count
剩余21页未读,继续阅读
资源评论
小小哭包
- 粉丝: 1900
- 资源: 3864
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功