author:skate
time:2013/03/04

记录下总结的oracle研发技巧，谁有好的意见请留言补充

Oracle研发技巧

1. Oracle初级技巧
   1.1 sql高性能优化
   1.2 表设计技巧 
2. Oracle中高级技巧
   2.1 海量表设计技巧
   2.2 DBA角度设计架构

1. Oracle初级技巧
    1.1 sql高性能优化
    最优化数据读取涉及的范围非常广，不仅涉及的物理方面的io，存储类型，硬件，网络环境等，而且涉及逻辑方面的dbms环境设置，sql类型，执行计划，索引类型，组合索引列的顺序等，我们这里只从程序员的角度出发，对最优化数据读取方面讨论；DB的环境就是sql地盘，如何高效的利用DB环境跑出高效的sql

•  减少单个sql消耗资源
  
•  减少sql执行次数

    减少单个sql消耗资源
       要降低sql消耗资源，就要需要sql在oracle环境是如何运行的，执行过程是什么？

    1. 创建游标
    2. 分析语句：做语法分析，检查sql书写、验证定义和权限等，选择最佳执行计划并装入sql共享区；在分析语句期间，oracle通过绑定变量实现共享sql，

        来减少sql解析。所以绑定变量是优化点
    3. 描述查询结果集决定查询结果的特征：如数据类型、字段名字、长度
    4. 定义查询输出数据指定与查询出的列值对应的接收变量的位置、大小和数据类型，如果必要，oracle默认转换数据类型
    5. 绑定变量
    6. 并行执行语句在创建索引、用子查询创建表、在分区表上的操作，可以执行并行操作，通过消耗更多的资源快速执行sql
    7. 运行语句Sql语句已经做好执行的准备，开始运行sql，这个过程可以批处理来优化。
    8. 取查询出来的行返回查询的结果集，通过批处理优化
    9. 关闭游标

从上面的分析sql执行过程可以知道，我们能通过如下方式减少单个sql资源消耗

• 绑定变量实现共享sql
• 最优执行计划选择
      最优的执行计划决定了表的连接方式，数据访问路径。因数据的读取方式分为连续扫描方式、随机扫描方式，而根据磁盘的物理特性，随机扫描非常影响数据的读取性能，我们优化sql，也就是把随机扫描转向连续范围扫描，局部范围扫描就是在连续范围扫描里，oracle可以智能的只读取部分数据，而不是读取全部数据。所以无论要求的数据范围有多大，都能确保较快的执行速度。

 减少排序操作，尽量使用索引替代排序
 创建合适的复合索引，尽可能限制查询结果集的大小
   
    减少sql执行次数
         通过绑定变量减少sql分析，通过优化业务逻辑减少sql的执行次数
     
1.2 表设计技巧    

 在我们设计的DB系统里，我们要清楚自己的系统里都有什么样的表，基本优化思路是什么，这里我们把表的基本分类如下：

1).数据量较少的表
2).参考作用的大中型表
3).管理业务行为的大中型表
4).存储用的大型表

1). 数据量较少的表
定义：一个io就可以把全表读到内存中，既存储表的block数量小于db_file_multiblock_read_count,这种
            表一般为字典表的，很少更新，所以可以考虑用IOT表、据簇表（父子表）或堆表
特点：一般放在嵌套循环的内循环，需要被多次执行
优化方式：pctfree，cache，索引，单独分库

2). 其参考作用的大中型表
定义：主要用于存储业务的行为、主体、目的等对象的数据，例如用户信息表。
特点：存储的数据很大，以随机读取的方式、小范围数据扫描为主，一般以主键读取或表连接，放在内循环
            中；很少数据插入，以select为主；需要重点优化这个表，一般会创建大量的索引
优化方法：创建合适索引，分区，聚簇

3). 管理业务行为的大中型表
定义：存储业务的交易活动数据，随着时间推移，数量不断变大
特点：因为分析的维度非常丰富，所以读取类型多种多样，数据量非常大，而且增加很快，通常位于循环
            的外侧；有时无法通过特定的列减少数据范围，所以常用组合索引。
优化方法：创建合适索引，分区，聚簇，数据分层

4). 存储用的大型表
定义：用来存储和管理日志性的数据
特点：数据量巨大而且不断增加，插入代价较大
优化方式：pctfree，分区，单独分库

2. Oracle中高级技巧
  2.1 海量表设计技巧
这个“海量“有两层意思，一个是数据量大，一个是执行频率高。对于像我们这种OLTP系统，其实每个sql感兴趣的数据都非常少，只要我们能做到每个sql都处理自己感兴趣的数据、每个表存储新鲜数据即可，这就需要从表的设计和sql书写来保证。像我们的trader后台有的sql感兴趣的数据可能很多，如果可以的话，可以考虑分库，避免这种个别业务影响整体系统的稳定。

 数据的海量从如下几个方面解决：
   分区表：分区表的拆分优点是对应用透明
   多维度拆分：可以从时间维度，功能维度等把大表拆分多个表，有相应的路由规则对应，如果拆
               表后，单台服务器还是无法满足，那就考虑分库存储
   中间表：对数据量大的表又需要多表关联，如果业务允许，可创建中间表，直接提供结果