改进数据库性能：SQL查询优化（1）

oracle性能调优-转自《Oracle Database 11g数据库管理艺术》第19章（改进数据库i你能：SQL查询优化）
性能调优是DBA花费时间最多的一个领域，主要包括：编写高效的SQL语句、分配合适的计算资源，
以及分析系统中的等待事件和资源争用情况。

Oracle建议在设计数据库时遵循以下步骤：
1. 正确地设计应用程序；（应用程序最初阶段就考虑性能调优为‘主动调优’，反之为‘被动调优’）
2. 调优应用程序的SQL代码；
3. 调优内存；
4. 调优IO；
5. 调优争用和其他问题；

用户的SQL语句要经历语法分析、优化和执行阶段。
1. 语法分析：主要包括检查SQL语句的语法和语义，最终结果是创建一个语法分析树。
2. 优化：基于成本的优化程序CBO，分为查询重写阶段和物理执行计划生成阶段。
   查询重写阶段--语法分析树被转换为一个抽象的逻辑查询计划。
   执行计划生成阶段--将逻辑查询计划转为一个物理查询计划。

成本优化例子
select * from employee e, dept d where e.dept_no=d.dept_no and(e.job='SUPERVISOR' and d.city='DALLAS')
说明1：用户每次只能读写一行数据，行级别（实际可能是块级别）
说明2：数据库将每个中间步骤都写到磁盘上（实际可能不是）
说明3：表上没有索引
说明4：employee表2000行，dept表40行（管理人的数量也是40-每个部门1个）
说明5：有10个部门在Dallas

查询1：笛卡尔联结，雇员表和部门表构成一个笛卡儿积，在这个笛卡儿积上看看有多少行满足需求。
成本评估：
（1）雇员表和部门表的笛卡儿积需要对两个表进行读取的次数：2000 + 40 = 2040读
（2）创建笛卡儿积：2000 * 40 = 80000写
（3）读取笛卡儿积并与选择条件相比较：2000 * 40 = 80000读
（4）合计IO成本：2040 + 80000 + 80000 = 162040

查询2：两个表的联结，先在dept_no列上对雇员表和部门表进行联结操作，再选择满足条件的行。
成本评估：
（1）联结雇员表和部门表首先需要对两个表的所有行进行读取：2000 + 40 = 2040读
（2）创建雇员表和部门表的联结：2000写
（3）读取联结结果花费：2000读
（4）合计IO成本：2040 + 2000 + 2000 = 6040

查询3：简化关系的联结，先读取雇员表以获取所有的管理人行，然后读取部门表得到所有Dallas部门，
最后，对从雇员表和部门表中的到的结果进行联结。
成本评估：
（1）读取雇员表得到管理人行：2000读
（2）写上个步骤中得到的管理人行：40写
（3）从部门表得到所有的Dallas部门：40读
（4）写上个步骤中得到的Dallas部门：10写
（5）联结从该查询前面步骤得到的管理人行和部门行：总计40 + 10 = 50写
（6）从前一步中读取联结结果：50读
（7）合计IO成本：2000 + 40 + 40 + 10 + 50 + 50 = 2190      

例子总结：说明笛卡儿积要比多数约束性联结的成本都高。

查询处理的启发式策略：
1. 尽早执行选择操作，这样就可以在操作的早期阶段消除多数的笛卡尔行。如果在最后阶段还保留
大多数记录，就会与迟早要去除的记录进行很多不必要的比较。
2. 尽早执行投影操作，以限制要处理的列的数目
3. 如果需要执行连续的联结操作，首先执行较小的联结
4. 计算一次公共表达式，并保存其结果

Oracle的旧版本中，可以在基于规则的和基于成本的优化程序之间进行选择。
CBO方法差不多总是要比老的、基于规则的方法执行的要快。
基于规则的优化程序是一个即将淘汰的产品。

为优化程序提供数据统计：默认下，每晚或周末，oracle 11g存在一个名为GATHER_STARTS_JOB的作业
在运行，监控全部数据库对象的所有DML更改，如insert、update和delete，可以在dba_tab_modifications
视图中查看这些更改信息，根据该监控oracle决定是否要为对象收集新的统计数据。
1. 检查GATHER_STATS_JOB是否在收集统计数据：
select last_analyzed, table_name, owner, num_rows, sample_size
  from dba_tables
 where last_analyzed is not null
       and table_name = 'your table name'
 order by last_analyzed desc
2. 查看列的统计数据：
select column_name, num_distinct from dba_tab_col_statistics where table_name='your table name'

设置优化程序方式
1. ALL_ROWS: 默认的优化程序方式，不管是否有查询中的每个表的统计数据，该方式都命令oracle使用CBO，
             以实现最大吞吐量这个明确目标。
2. FIRST_ROWS_n: 此优化方式不管统计数据是否可用都使用成本优化，目的是使输出前n行的响应时间最快。
3. FIRST_ROWS：使用成本优化和某种试探法（经验规则），而无论是否有统计数据。
了解数据库当前的优化程序方式：select name, value from v$parameter where name = 'optimizer_mode'

优化程序做些什么
1. SQL转换，oracle几乎不会以原始形式来执行查询。如果CBO确定另一个sql表达形式可以更有效的得到同样
的查询结果，那么在执行该查询之前就会转换该语句。
典型例子：具有or条件的查询，CBO将其转化为一个使用union或union all的语句
（1）将in转换为or语句；
（2）将or转换为union或union all语句；
（3）将非直接相关的嵌套的选择语句转换为更有效的联结（join）；
（4）将外联结转换为更有效的内联结（inner join）；
（5）将复杂的子查询转为联结、半联结和反联结（antijoin）；
（6）对基于星型模式（star schema）的数据仓库表进行星型转换；
（7）将between转换为大于等于或小于等于语句；
2. 选择访问路径，通过不同的路径访问相同的数据。对每个查询评估所有可用途径并选取资源消耗最少的
优化程序在确定执行路径前要经过的步骤：
（1）全表扫描：顺序地读取表中每个块，但是对于大表，全表扫描效率通常不高；
（2）通过rowid对表进行访问：这是oracle中检索行最快的方式；
（3）索引扫描：索引存储两种东西（索引列的列值和表中包含该列值的行的rowid）；
3. 选择联结方式，当访问两个或多个表时，oracle根据共同的列来联结这些表。
CBO使用的一些常见的联结方法：
（1）嵌套循环联结（nested-loop join）：指定一表为驱动表（外部表），另一表为内部表，对驱动表中
每行数据都要读取内部表的所有行。
（2）散列联结（hash join）：用其中较小的表在联结键上构建一个散列表，然后对大的表进行搜索，并
从散列表中返回被联结的行。
（3）分类合并联结（sort-merge join）：将联结键上的输入值分类，将已分类的列表进行合并。
4. 选择联结次序