Oracle研发技巧

Dear 丶 2022-03-27 13:54 249阅读 0赞

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感兴趣的数据可能很多，如果可以的话，可以考虑分库，避免这种个别业务影响整体系统的稳定。

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

高执行频率从如下几个方面解决：  
   数据Cache：把表的数据cache在缓存层并持久化，减少对数据库的读写  
   简化/优化sql：减少单个sql的资源消耗，从而减少sql响应时间  
   分库：采用分库的方式来分担压力

2.2 DBA角度设计架构  
系统最容易出现瓶颈的地方就是DB，那在设计之初就考虑好应对方案，要比已经发现DB瓶颈，再去解决所需要的成本低很多，有时因无法解决DB瓶颈，不得已要重新开发全部系统。DBA在实践中总结经验，为避免DB出现瓶颈，提出站在DBA角度补充系统架构设计，在我们面对巨大的访问量和数据量时，我们系统要做到化繁为简，化大为小.

系统设计规模  
     明确设计的系统支持的业务规模，系统支持的容量，系统的是否需要扩展

系统中最宝贵的资源是：cpu，memory，io，network。Io又是重中之重，是块短板，极易出现瓶  
     颈，在们设计系统要以其基准，比如设计oltp系统，参考下面公式  
     Pv/(24\*3600)\*Dpv\*lvdpv\*x\*(1-hit)\*rwrate=r\_iops  
     Pv/(24\*3600)\*Dpv\*lvdpv\*x\*(1-hit)\*rwrate=w\_iops

说明：  
      Pv: pv/天  
      Dpv: 动态pv率,每天pv中动态pv所占比例  
      Lvdpv：每个动态pv所产生的逻辑读  
      Hit：cache命中率  
      Rwdate：系统读写比例  
      
  比如我们设计支持1000万pv的系统，需要多少io，而这些io需要多少块磁盘支撑；数据存储容量如何规划  
      
  常见硬盘IOPS参考值：  
　　   2,5" 10.000 rpm  SAS 113  IOPS  
　　   2,5" 15.000 rpm  SAS 156  IOPS  
　　   3,5" 15.000 rpm  SAS 146  IOPS  
　　   2,5" 5.400 rpm SATA 71 IOPS  
　　   3,5" 7.200 rpm SATA 65 IOPS  
           3,5" 15.000 rpm FC 150 IOPS  
   监控  
       做好性能和故障的高效精确监控，可以提前规避大部分问题  
   预案  
       准备好突发情况处理的预案，避免手忙脚乱、误操作  
   服务降级保护  
      在某模块突然故障，避免级联影响或保证核心业务正常，通过降低模块耦合性或设置模块起停开关实现  
   预防数据预热雪崩  
      在设计DB架构时，应考虑采用何种方式预防数据预热引起性能抖动甚至访问雪崩，比如有：前段高性  
      能kv库、app对DB的漏斗式访问  
   DB削峰设计  
      在访问量突增时，异常峰值会对系统性能极大影响，甚至拖垮系统，我们可以通过削峰设计规避这  
      个问题，使系统在性能最佳状态处理请求，例如：通过队列设计最大接受请求，平缓处理请求通过  
      数据库session控制数据库最大处理并发量，使数据库工作在最佳性能状态  
   高效利用nosql库  
      nosql数据库在应对kv环境时效率非常高，为高效利用nosql库，降低RDBMS存储DB的访问，可以  
      采用RDBMS主动推送变化数据到nosql库，应用主读nosql，超时读取nosql后端存储RDBMS

\---end-----