oracle高性能sql调整-笔记2

********************sql优化器简介*************************

dba需要深入了解sql优化器和优化器模式，并了解如何更改默认的执行计划来提高查询速度，dba必须了解每一个优化器的有点和缺点，并且能够通过设置oracle的默认的优化器模式以降低人工调整的工作量。

9i以后使用基于成本的优化器模式

主要内容：
基本的优化器技术
优化器模式
有关基于规则的优化器的调整
有关基于规则的优化器的调整
有关基于成本的优化器的调整
设定默认优化器模式
迁移到基于成本的优化器

 

--------------------基本的优化器技术---------------------
优化器的目的是为sql语句生成最快的，并且消耗资源最少的执行计划。
目标：
--产生最快的反应速度  firstrows模式
--具有最佳整体吞吐量的执行计划 allrows模式

两个参数
optimizer_mode 设置数据库范围的优化器默认值
alter session set optimizer_goal  更改单独的会话的优化器模式

 

…………………………优化器的结构…………………………  
基于规则的优化器 基于成本的优化器
 | \ / | \
     rule       choose    first rows  all rows

即使设置了优化器模式是rule，但是只要使用了除rule以外的提示，仍然会导致oracle调用all_rows模式

 

------------------基于规则的优化器-------------------------
10年的历史 第一个 oracle优化器

rbo使用迭代过程生成执行计划，并且检查from子句中的每个表，以及该表与其他表的链接方式。每一个可行的路径根据他们所要消耗的成本进行排序，并选择成本最低的路径。

rbo进行分级的方法取决于对不同操作的成本级别的比较。

特征：
总是使用索引
总是从驱动表开始 from子句中最后一个是驱动表 驱动表的记录最少最好
只有在不可避免的情况下才使用全表扫描
任何索引都可以   rbo有时候会选择一个并非最理想的索引来对查询进行服务
有时 越简单越好

 

--------------------基于成本的优化器-------------------------------
oracle认为，如果优化器了解表和索引中的细节，那么sql优化将变得更加有效。
那么优化器需要了解的数据包括
--表数据
--记录的数据
--无理数据块的数目
--索引数据
--索引中唯一值的数目
--索引中值的分布
--索引的选择性
--索引的聚簇性

CBO和统计：
--统计工具：
analyze分析
dbms_stats包
如果表和索引重要特性经过改编，那么需要对表和索引进行重新分析，但是重新分析后sql的执行计划就会有相应的改变。使得sql调整变的很难。
柱状图：
cbo允许在数据字段包含有非常不平衡的数据分布时生成柱状图，例如，位图索引。
所以柱状图会通知cbo在生成执行计划的时候考虑到数据的分布，哪怕采用了绑定变量的方法，那么cbo也应该会知道对于数据量很多的时候使用全表扫描，而数据少的时候使用索引范围扫描。

注意：9i之前 绑定变量的查询不能使用柱状图。从9i以后，柱状图的出现，会是oracle优化器可以根据柱状图对执行计划重新评估，这个功能就被称为窥视功能。但是缺点是，柱状图会给数据库的管理带来很大的负担，因为每次执行查询前都要对它进行重新的解析。

警告：字段柱状图应该只用于那些有少量不同值的字段，并且某些字段值分布在不成比例的记录中。柱状图计算很耗时，并且可能导致对程序库缓存中sql语句的重新解析。

cbo和提示：
由于cbo自身的这些缺点，oracle增加了提示的功能，使得我们进行人工的选择。

执行计划持久性：
8i以后引入了优化器计划稳定性的新功能，提供sql语句的执行存储的一个框架。目的是降低生成计划所需要的时间，和总是生成一致的执行计划。

-------------------优化器模式--------------------------------------
rule
first_rows
all_rows
choose

rule模式：
完全基于数据字典信息生成执行计划
注意：rbo坑农选择并非理想的索引为查询服务。

choose模式
oracle自动选择使用rbo还是cbo如果有统计信息，那么就会选择cbo，并且会对没有统计信息的表进行分析。
注意：统计资料不完全的时候choose会造成数据库性能急剧下降

first_rows模式
基于成本的优化器模式 以最快的速度返回记录 但是会造成整体性能的下降和耗费更多的资源；倾向于选择完全索引扫描而不是并行全表扫描。适用于在线系统，因为这样的系统对于用户需求来说，用户希望以最快的速度看到一些结果。

all_rows模式
基于成本的优化器模式 确保总体查询时间最短 但是它可能在收到第一条记录的操作上花费的时间比firstrows要长。allrows通常选择并行全表扫描而不是完全索引扫描。所以allrows适用于批量查询，因为这种情况下不需要查看中间结果记录。

区别：
在有order by子句中--
完全索引扫描可以在瞬间开始返回记录，但是需要更多的输入输出，因为索引已经是有序的，减少了排序带来的消耗；
全表扫描耗费更少的资源，但是需要等到查询结束，我们才可以使用查询结果集，需要在得到结果集之后在临时表空间排序，增加了排序的消耗。

----------------------基于规则优化的调整-------------
在from子句中表出现的顺序和在where子句中布尔值出现的顺序都会影响查询的执行计划。

---更改基于规则的驱动表
在oracle基于规则的优化器中 from子句表名出现的顺序将决定哪个表作为驱动表，驱动表之所以重要是因为它最先被读取，然后将来自第二个表的结果添加到第一个表的结果集中。所以根据where子句使得第二个表返回最少的记录是至关重要的。

注意：驱动表 并不总是拥有最少记录数的表，sql语句where子句中的条件必须经过评估，而且驱动表必须是可以返回最少记录的表。

通过使用基于规则的优化器，表的名称从右向左读取，因此最后一个表一定要是返回结果集最小的一个表。
举例： order表有1000行数据，customer有5000行数据。但是where条件规定 customer中的local字段等于 beijing，而且符合条件的北京在customer表中的记录数中只有300条，对于order表where条件仅仅是做了一个链接，那么我们就有必要将customer表作为驱动表。因为customer会返回更少的记录。

-----------如果基于规则的优化器没有使用正确的索引-------
基于规则的优化器不知道索引的选择性和集群因子以及二元高度等信息，所以可能就会选择错误的索引去读数据块，造成性能的下降。在使用基于规则的优化器的时候 需要使用index提示指定最好的索引，或者强制使我们不想用的索引失效。

 

-----------基于成本优化的调整------------------------
使用一些提示列表
怎样调用基于成本优化的调整

设置init.ora 参数 optimizer_mode=all_rows、first-rows 或者choose
alter session set ptimizer = all_rows or first_rows
基于成本的提示  /* + all_rows*/ 或 -- + all rows

 

-------------为cbo收集统计信息--------------------
analyze脚本
dbms_stats 工具

analyze的缺点：大多数将这脚本每周运行一次，或者在表出现重要变更时运行，但是，在相对稳定的表和索引重新分析师没有意义的。

如果使用dbms_stats 工具就可以使用这个包的选项有目的性的进行统计资料的收集，可以并行；

 

8i以后 cbo成为主流，因为rbo一直没有变化，而cbo在一直优化

选择基于成本的优化器的思想非常依赖于oracle数据库的性质，这个库是动态还是静态的有很大关系。

--表和索引的统计资料 对于动态库，每发生一次变化统计资料就要重新计算，静态库不用依赖于统计资料。
--优化器计划稳定性 动态库需要享有在数据变化时改变执行计划的自由，但是金泰库依赖优化器计划稳定性使调整更持久化
--cursor sharing force   sql共享

--------------------------sql内部处理-----------------

主要内容：
oracle共享sql和专用sql区域
sql的sga统计资料
程序库缓存的内部情况
监控并调整oracle排序
在程序库缓存中确定具有高影响力的sql语句
关于来自程序库缓存的sql的报告

 

####################sga  pga##########################
当表执行了多于100次的全表扫描时，会自动存储少于200个块的所有表

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24799772/viewspace-677651/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24799772/viewspace-677651/