介绍Oracle数据库开发的原则 下

上篇中，我们主要介绍了进行Oracle数据库开发应该关注的几条原则问题，本篇继续讨论这个话题。　　4、The Trap In Your Where Condition and Order/Group clause　　我们进行的日常数据库开发，主要集中在DML操作类型，以select/update/insert/delete为核心。在这些类型操作，我们*需要关注的并不是select的结果集合列表，也不是insert/update的具体数值，而是定位操作对象的where条件和进行大规模计算操作的group/order。　　Where条件的作用是让Oracle可以定位到我们需要进行检索处理的记录。Where条件的书写起到两个层面的作用，其一是描述了结果集合属性，另一个是间接影响到Oracle定位数据的方式，也就是执行计划。　　SQL是一种描述性语言，我们只需要描述需要的数据属性就可以了。但是也就是这个特性，往往会让我们书写出很糟糕的SQL。同样的结果集合，不同的描述方式（SQL where条件），执行效果和执行计划可能会千差万别。　　写好where条件的规则技巧有很多，比如in用exists替换、is null不选择等等，每种技巧都是基于特定的应用场景和内部背景。这里列举一个对条件列不要轻易处理的例子。　　如果我们在where条件中书写一些表达式，通常Oracle在SQL预处理前就会将表达式进行处理。但是，对于携带数据列的条件表达式，这种改写变化是不会越过等号的。下面进行试验。　　SQL> create table t as select * from dba_objects;　　Table created　　SQL> create index idx_t_id on t(object_id);　　Index created　　SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);　　PL/SQL procedure successfully completed　　选择在数据表t的object_id列添加索引。两个SQL含义等价，但是执行计划**不同。　　SQL> explain plan for select * from t whereobject_id=999+1;　　Explained　　SQL> select * from table(dbms_xplan.display);　　PLAN_TABLE_OUTPUT　　--------------------------------------------------------------------------------　　Plan hash value: 514881935　　--------------------------------------------------------------------------------　　| Id | Operation                  | Name    | Rows | Bytes | Cost (%CPU)| Ti　　--------------------------------------------------------------------------------　　|  0 | select STATEMENT           |         |    1 |   93 |    2  (0)| 00　　|  1 | TABLE ACCESS BY INDEX ROWID| T       |    1 |   93 |    2  (0)| 00　　|* 2 |  INDEX RANGE SCAN         | IDX_T_ID |    1 |      |    1  (0)| 00　　--------------------------------------------------------------------------------　　Predicate Information (identified by operation id):　　---------------------------------------------------　　2 - access("OBJECT_ID"=1000)　　14 rows selected　　SQL> explain plan for select * from t whereobject_id-1=999;　　Explained　　SQL> select * from table(dbms_xplan.display);　　PLAN_TABLE_OUTPUT　　--------------------------------------------------------------------------------　　Plan hash value: 1601196873　　--------------------------------------------------------------------------　　| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time    |　　--------------------------------------------------------------------------　　|  0 | select STATEMENT |     |  513 | 47709 |  160  (3)| 00:00:02 |　　|* 1 | TABLE ACCESS FULL| T   |  513 | 47709 |  160  (3)| 00:00:02 |　　--------------------------------------------------------------------------　　Predicate Information (identified by operation id):　　---------------------------------------------------　　1 - filter("OBJECT_ID"-1=999)　　13 rows selected　　相同的含义SQL，只是进行简单的算术移向操作，就有如此大的执行计划成本差异。在**个SQL中，Oracle自动将运算完成后走索引路径。而第二个SQL因为减一操作在object_id同侧，视为将object_id处理的表达式。表达式是不会选择普通索引路径的，只有创建特定的函数索引。　　此类问题还可以引申到对列进行函数处理或者表达式处理，这样的SQL语句非常容易出现在where条件中。作为一名开发人员，应该尽可能的消除这样的SQL语句，增加SQL优化的概率。　　Group by/order也是同样的问题。在Oracle中，分组操作和排序操作是相当“昂贵”的。当进行group by/order的时候，对应的数据集合需要存放在PGA的专门区域中进行。这种操作消耗PGA甚至临时Temp表空间空间，同时也会消耗一定的CPU资源。所以，如果没有明确的需求，我们尽量少用这两种类型操作。　　5、Hard Parse VS Soft Parse　　Oracle开发人员平时听到*多的数据库SQL技巧恐怕就是绑定变量SQL的书写了。使用绑定变量的原因简单的说，就是为了增加SQL共享游标的共享概率，减少硬解析hard parse。　　对Oracle来说，内存的缓存cache技术是贯穿在整个体系框架中的。当一个新的SQL语句输入时，要经历语法、语义和权限等一系列检查，之后要进行parse过程。如果在SGA的Library Cache中没有能找到，就会自己生成该SQL的执行计划和共享游标，这个过程要消耗SGA空间和CPU成本，同时还会带来一定数量的library Cache Latch和Library Cache Pin。进行执行计划生成之后，该SQL以shared cur***的形式缓存在library cache中，等待再次被使用。这个过程被称为hard parse，硬解析。　　当library cache中存在该SQL的执行计划时，另一次SQL调用输入。如果新SQL与原来的SQL字面值和其他一些参数相同，就存在游标共享的可能。这样，新SQL不需要进行SGA空间分配和执行计划生成，会使用原有的执行计划。这个过程我们称之为Soft Parse。　　无论是Oracle自身的SQL和PL/SQL，还是Java/.NET的接口语言，都存在使用绑定变量的接口API。使用绑定变量可以增加SQL出现soft parse的几率，增加数据库并行性。　　*后，我们谈一下绑定变量的适用环境。并不是什么样的场景使用绑定变量都是没有问题的，在OLTP这类事务并发和事务密集型的系统中，使用绑定变量会提高系统整体并发能力。但是在OLAP和DSS类的系统中，一个SQL执行次数很少，但是执行时间很长，这样场景下使用绑定变量的意义就不大。　　此外，使用绑定变量存在出现bind peeking的可能性，这方面的性能抖动问题也不能忽视。　　6、Prioritize your Use Cases for Tunning　　*后说说性能优化。系统从业务需求分析、设计、开发到投产运维，性能分析优化是贯穿整个生命周期的。性能分析优化手段越是往前规划，我们可以使用的优化选择手段就越多，性价比就越好。传统意义上的SQL调优，都是谈在投产运维阶段进行的DBA运维调优。在运维阶段进行的手段很有限，而且收效往往很低。　　在开发阶段，我们进行优化的方式主要是SQL改写和索引选择。大多数性能优化手段都是需要付出额外的成本。比如索引，建立索引的确可以获取很好的select效率，但是另一方面要付出update/insert/delete成本，而且索引本身也是要有空间占用和管理成本。所以，我们追求的优化，实际上就是*优性价比的优化。　　那么，面对诸多的需求场景，我们如何选择呢？笔者以为：所谓关键用例确定架构，关键用例同样决定优化策略方向。我们面对的需求不可能是相同优先级别的，对用户而言，必然存在轻重缓急。我们开发系统的目标是实现用户的愿景，实现用户目标的*大化。但是，用户的目标实现是不可能**实现。在“质量-工期-成本”三角形的控制下，必然有需求会被裁减。我们追求的目标就是将优先的优化资源分配给尽可能高优先级别的用例需求中。　　举一个例子，两个数据列都有加索引优化的需求，但是资源限制下只能加一个索引。一个用例是在界面上显示系统参数，这个界面对应SQL如果是全表扫描，要多消耗5秒钟。但是该用例很少有人用，每年只会打开一次。另一个用例是每日的Daily Job，每天都会运行成百上千次的SQL。经过详细分析，用例的优先级别立刻可以看出来了，优化方案自然也就出来了。　　7、结论　　本系列集中介绍了开发阶段我们需要关注的技术和原则，大部分的技巧是思路和指导原则，希望对读者有所帮助。

介绍Oracle数据库开发的原则 上