SQL Tuning如何写出优质高效的数据库程序

1、 基本原则 v简单的是最有力的； v2/8原则； v性能优化是动力是基于对美的追求。 基本约定 vSQL语句书写的约定，如关键字要大写,函数，对象名， 变量要小写，一个关键字一行，每行以关键字右对齐， 等号左右各空一位等，各个部分仅空一格； v在SQL语句中对于值非常多的列条件要用变量绑定， 不要用常量；对于值少记录多，且分布不均匀，如果要 用上该列的索引，就要用常量。 v在SQL语句中尽量不要用左值函数； v查询条件的顺序要尽量能够利用已有索引，如果不能 利用现有的索引而需要新建综合索引，索引列的顺序、 查询条件的顺序要和表中的列顺序一致。 v如果能够确保两个输出值集没有重复值，Union

2、All 比 Union 有效率。 一些建议 v某些复杂和耗时的SQL语句可以考虑采用几个 语句来完成。 v对使用外连接的语句，可以尝试用一些组合语 句来代替。 v如果能够用行操作SQL语句完成，不要用集操 作SQL(如count, Distinct, minus, intersect)语句 完成。 问题1 vSELECT BOARD_BARCODE FROM SMT_BOARD_WORK_REC bwr, SMT_DEP_TASK_INFO dti WHERE BOARD_BARCODE=025767102A000842 AND dti.Second_Series=标准 and bwr.Tas

3、k_No=dti.Task_No AND PROC_ORDER=1; vSELECT BOARD_BARCODE FROM SMT_BOARD_WORK_REC bwr,SMT_DEP_TASK_INFO dti WHERE BOARD_BARCODE=:b1 AND dti.Second_Series=标准 and bwr.Task_No=dti.Task_No AND PROC_ORDER=1; v没有用变量绑定，导致大量的Parse，占用CPU时 间和内存。 效果 v下面是使用文字变量和绑定变量通过Spotlight进行对比的观察结果。不 良代码为执行1500行操作，将同样的SQL解释了

4、500次，对应的在内存 中占用500倍的空间，带来严重的latch争用。 v在使用绑定变量的情况下，留意对应SQL的Loads和Exections列值，运 行次数逐渐增长为500、1000、1500直到2000。而变化时，Loads值始 终是1，说明shared pool共享性好，同时latch争用也会比较少。 问题2 vSELECT ROWID, organization_id, wip_entity_name, cpb_bar_code, cpb_revision, cpb_item_id, zcb_bar_code, zcb_revision, zcb_item_id, created_

5、date, created_by, last_updated_by, last_updated_date, collect_user FROM hw_cpb_relation WHERE TO_CHAR (created_date, yyyy/mm/dd hh24:mi:ss) = 2002/10/16 15:16:06 vSELECT ROWID, organization_id, wip_entity_name, cpb_bar_code, cpb_revision, cpb_item_id, zcb_bar_code, zcb_revision, zcb_item_id, created

6、_date, created_by, last_updated_by, last_updated_date, collect_user FROM hw_cpb_relation WHERE created_date =to_date(:b1, yyyy/mm/dd hh24:mi:ss) v在SQL语句的Where条件的字段上加上函数，导致 索引不可用或效率低 索引的创建 v经常在哪些字段做连接或加过滤条件 v数据是否经常做update v值的重复频率是否非常高 v是否几个字段一起经常用来做组合查询 索引的建立原则 v对于值不多但记录非常多，且值分布比较均 匀的字段，基于此字段的索引对查询的优

7、化 没有作用，同时带来Insert操作时索引的维护 开销； v对于状态类字段，值不多，如果记录多，但 这些记录在状态中的分布极不均匀，可以建 立索引，索引的条件带常量才可用到索引。 索引的使用 v不应当用于表上10以上的数据。 v很小的表一两次IO就可以对其扫描也没有必要使用 索引 v组合索引可以压缩以减少IO，但会增加CPU的消耗 v反序索引的使用 vBitmap索引的使用 v不要在表上建太多索引 编写sql的一些技巧 v使用hints，让sql按照希望的方式来执行 v不使用某些特定的索引 v不要频繁commit v业务规则定义在数据库底层的效率高 v外键应当被索引 v存储过程要比外部过程效

8、率高 v尽可能使用绑定变量来减少HARD PARSE v尽可能重用游标来减少SOFT PARSE 为什么索引没有被使用 v查询条件没有包含组合索引的主要边界 v索引不包含空的条目 v查询条件对索引列使用了函数 v低效的索引 vCBO的统计信息过于陈旧 DB link 的优化(1) 驱动表的选择：对于有一个DB Link的语句，选取远程表作为驱动表 select COMPANY.Name from COMPANY, SALESREMOTE1 where COMPANY.Company_ID = SALES.Company_ID and SALES.Period_ID =3 and SALES.S

9、ales_Total1000; vNESTED LOOPS REMOTE (TABLE ACCESS FULL SALESREMOTE1) TABLE ACCESS BY ROWID COMPANY vINDEX UNIQUE SCAN COMPANY_PK DB link 的优化(2) 多个DB Link，需要返回大量的记录，采用Merge可能比Nest Loop 快 select /*+ USE_MERGE(COMPANY,SALES) */ COMPANY.Name from COMPANYREMOTE1, SALES where COMPANY.Company_ID = SALES.C

10、ompany_ID and SALES.Period_ID =3 and SALES.Sales_Total1000; vMERGE JOIN SORT JOIN vTABLE ACCESS FULL SALES SORT JOIN vREMOTE (TABLE ACCESS FULL COMPANYREMOTE1) DB link 的优化(3) 在同一SQL语句中对同一个数据库的多个远程表进行连 接，有如下情况： v1、如果这个查询中既有本地表，又有多个远程表， 如果查询条件是基于运程表的过滤，可以考虑在远端 数据库中建立一个View，这个View基于参与操作的 表，然后在本地建立一个同义词

11、对应这个运端的 View。这样可以大大减少网络的IO。View操作产生 的结果（不多）与本地表进行连接，加快速度。 v如果所有的表是远端表，这样做的效果不明显，因为 语句都是在远端执行，最终的结果才返回到本地。 存在性检验的优化 v适用场合： 对于利用In , Not In的语句，一般来说都可以用 Exist, Not Exist来代替，用哪一个，决定于哪一 个的执行效率高，IO小； 这两种语法的互相代替可以减少全表扫描和操作 决数，有效提高处理时间。 In - Exists 执行计划 大表操作优化问题 防止没有效果的索引用到。DB Buffer对索 引取到的数据和全表扫描取到的数据内存使 用策略不同。 创建分区及分区索引，在SQL语句中要有条 件来使执行路径只处理相关的的分区。 SQLIB的使用