SQL优化概述【待完成】

引言：ＳＱＬ语句优化的目标是让该ＳＱＬ语句执行查询的过程中产生的逻辑读（物理读最终是为了逻辑读）出来的总的块的容量接近（肯定会大于）该ＳＱＬ语句所得到的结果集的空间大小。

第二篇深入分析Oracle的SQL优化技术。总体上来说，SQL优化技术可以分为两类：

逻辑优化技术和物理优化技术。其中，逻辑优化主要是指查询转换技术。本篇当中详尽地分
析了10g、11g中现有的各种查询转换技术，并给出实际示例帮助读者理解这些技术。而物
理优化则指的是优化器通过代价估算来选择最佳的执行计划。优化器要正确估算执行计划及
其操作的代价，则需要准确的统计数据的支持。因此本书在分析优化器的代价估算方法之
前，先分析Oracle如何收集、统计系统和对象的统计数据。然后，结合作者推导出的各种代

价估算公式，演示了各种情形下的代价计算方法。

SQL优化技术可以分为两类：逻辑优化技术和物理优化技术。

其中，逻辑优化主要是指查询转换技术。

物理优化则指的是优化器通过代价估算（代价估算是以准确的统计数据为根据）来选择最佳的执行计划。优化器要正确估算执行计划及其操作的代价，则需要准确的统计数据的支持。

sql语句在优化器的优化过程

查询转换器对sql语句实现逻辑优化后，计划生成器会考虑可能的访问路径（Access Path）、关联方法和关联顺序，生成不同的执行计划。接着，代价估算器就会以准确的统计数据为根据来选择最佳的执行计划。

没有一种执行计划可以说性能上是绝对好的，只有在最适合他的环境下运行，才能发挥它的性能。而这个环境包括（主机硬件）系统环境和对象环境。系统环境，如IO寻址时间、IO传输速度、读取数据块（包括内存上的和磁盘上的）的速度等，而对象环境是指执行sql语句时涉及到的数据库对象，主要是考虑对对象访问（访问路径（Access Path））时的代价。

对环境进行统计数据，就是指对环境的一种采样，类似于概率论上对一批产品做随机抽样，用样品的性能来近似作为整体的性能。比如系统同时多个用户并发操作等可以影响当时IO的性能，说明系统环境是会变的。比如表会被经常插入，删除等，即表的内容是经常变化的，这样，原先的统计数据的陈旧数据百分比（stale_percent）就会增加，也就是说原先的对象统计数据过时了，不能反应出现在对象的实际内容了，此时就需要重新做统计数据。表没什么变化的，就不需要经常做统计数据。相对于表来说，系统环境应该不太变化很大，可以不需要经常做统计数据。

sql语句运行性能分析：通过代价估算（代价估算是以准确的统计数据为根据）而获得的所谓最佳的执行计划，还是只是一个估计而已，不能反应在实际环境运行的性能，因而可能在实际环境运行时就会出现性能不如预期的问题，此时要通过性能统计数据来分析语句的性能瓶颈在哪里，从而做出调整。

注释：系统统计数据  看 数据字典AUX_STATS$

SQL引擎包括SQL编译器和SQL执行引擎，SQL编译器包括解析器、查询优化器（即优化器）和行源生成器。

1、SQL编译器

解析器
对SQL语句的语法、语义分析，将查询中的视图展开、划分为小的查询块。

查询优化器
查询转换器、代价估算器、计划生成器

行源生成器
为执行计划生成行源

SQL执行引擎
按照语句的执行计划进行操作，产生查询结果

查询转换器（Query Transformer）—查询转换器决定是否重写用户的查询（包括视图
合并、子查询反嵌套），以生成更好的查询计划。

代价估算器（Estimator）—代价估算器使用统计数据来估算操作的选择率（Selectivity）、
返回数据集的势（Cardinality）和代价，并最终估算出整个执行计划的代价。

计划生成器（Plan Generator）—计划生成器会考虑可能的访问路径（Access Path）、
关联方法和关联顺序，生成不同的执行计划，让查询优化器从这些计划中选择出代价最小的
一个计划。

行源生成器（Row Source Generator）—行源生成器从优化器接收到优化的执行计
划后，为该计划生成行源（Row Source）。行源是一个可被迭代控制的结构体，它能以迭代
方式处理一组数据行、并生成一组数据行。

2. SQL执行引擎（SQL Execution Engine）

SQL执行引擎依照语句的执行计划进行操作，产生查询结果。在每一个操作中，SQL执
行引擎会以迭代方式执行行源、生成数据行。
提示：当Oracle引入一些新的优化技术时，会出现一些新的组件，例如，SQL进化管
理器（SPM）、SQL性能分析器（SPA）等，这些组件会与SQL引擎的组件融合，提供更好
的优化和调优方法。

===========================================================================

百度     db_mutli_count

验证数据库支持db_file_multiblock_read_count参数的最大值       10046事件跟踪sql语句运行全过程

http://blog.itpub.net/23135684/viewspace-749200

http://blog.csdn.net/leshami/article/details/8985734   

============================================================================

百度   direct path read

direct path read[转]        并行性能

http://blog.chinaunix.net/uid-20607558-id-1916302.html

===================================================================

逻辑读，包括服务器进程从高速缓存（buffer cache）和服务器进程的私有内存PGA里的UGA（如有排序操作时就会从UGA读取）上读取数据块的次数。

等于db block gets 和 consistent gets之和。

缓存命中率   buffer cache hit radio

并行查询、直接加载（direct load）大表等时，会发生一致性直接读取（consistent gets direct），即服务器进程会绕过高速缓存（buffer cache）直接从磁盘上的数据文件读取数据块，发送给客户端的用户进程。

一致性直接读取（consistent gets direct）是物理直接读（physical reads direct）的一种情况。

consistent gets =  consistent gets direct  +  consistent gets from cache。

consistent gets计数是不包括consistent changes的。

consistent changes是在一致性（模式）读过程中，将回滚记录的回滚数据应用到数据块上的次数。

db block gets=db block gets direct  + db block gets  from cache。

db block gets计数是不包括  db block changes的。

db block changes是在当前模式读过程中，将所有数据块（包括回滚数据块）被修改的次数。

一致性直接读取（consistent gets direct）是物理直接读（physical reads direct）的一种情况。

当前直接读取（db block gets direct）是物理直接读（physical reads direct）的另一种情况。

物理直接读（physical reads direct）（的次数）即

一致性直接读取（consistent gets direct）与当前直接读取（db block gets direct）之和。

small_table_threshold     并行查询

 oracle 并行 性能  谷歌

======================================================

大表阀值参数，即表的空间大于某一值后，就不会缓存在buffer cache上了，每次读取该表时，都会产生物理读。

假如两个表A和B连接，那么限制条件里涉及到两个表上的列，则该限制条件为过滤谓词所用；限制条件里只是涉及到一个表上的列，则该限制条件为访问谓词所用。

物化视图本质上就是一个普通表（存放结果集）加上刷新机制组成的。

如果你没有正确的指定Hints，Oracle将忽略该Hints，并且不会给出任何错误。
hint被忽略

如果CBO认为使用hint会导致错误的结果时，hint将被忽略，详见下例
SQL> select /*+ index(t t_ind) */ count(*) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    57   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 50366 |    57   (2)| 00:00:01 |
-------------------------------------------------------------------

因为我们是对记录求总数，且我们并没有在建立索引时指定不能为空，索引如果CBO选择在索引上进行count时，但索引字段上的值为空时，结果将不准确，故CBO没有选择索引。
SQL>  select /*+ index(t t_ind) */ count(id) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 646498162
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     5 |   285   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE  |       |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN| T_IND | 50366 |   245K|   285   (1)| 00:00:04 |
--------------------------------------------------------------------------

因为我们只对id进行count，这个动作相当于count索引上的所有id值，这个操作和对表上的id字段进行count是一样的(组函数会忽略null值)


语法  
 
  {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */  
 
  or  
 
  {DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...  
 
  如果语（句）法不对，则ORACLE会自动忽略所写的HINT，不报错