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

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

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

3、RDER=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解释了500次，对应的在内存中占用500倍的空间，带来严重的latch争

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

5、t_updated_date, collect_userFROM hw_cpb_relationWHERE 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_date, created_by, last_updated_by, last

6、_updated_date, collect_userFROM hw_cpb_relationWHERE created_date =to_date(:b1, yyyy/mm/dd hh24:mi:ss) v在SQL语句的Where条件的字段上加上函数，导致索引不可用或效率低索引的创建v经常在哪些字段做连接或加过滤条件v数据是否经常做updatev值的重复频率是否非常高v是否几个字段一起经常用来做组合查询索引的建立原则v对于值不多但记录非常多，且值分布比较均匀的字段，基于此字段的索引对查询的优化没有作用，同时带来Insert操作时索引的维护开销；v对于状态类字段，值不多，如果记录多，但这些记录

7、在状态中的分布极不均匀，可以建立索引，索引的条件带常量才可用到索引。索引的使用v不应当用于表上10以上的数据。v很小的表一两次IO就可以对其扫描也没有必要使用索引v组合索引可以压缩以减少IO，但会增加CPU的消耗v反序索引的使用vBitmap索引的使用v不要在表上建太多索引编写sql的一些技巧v使用hints，让sql按照希望的方式来执行v不使用某些特定的索引v不要频繁commitv业务规则定义在数据库底层的效率高v外键应当被索引v存储过程要比外部过程效率高v尽可能使用绑定变量来减少HARD PARSEv尽可能重用游标来减少SOFT PARSE为什么索引没有被使用v查询条件没有包含组合索引的主

8、要边界v索引不包含空的条目v查询条件对索引列使用了函数v低效的索引vCBO的统计信息过于陈旧DB link 的优化(1)驱动表的选择：对于有一个DB Link的语句，选取远程表作为驱动表select COMPANY.Namefrom COMPANY, SALESREMOTE1where COMPANY.Company_ID = SALES.Company_IDand SALES.Period_ID =3and SALES.Sales_Total1000;vNESTED LOOPSREMOTE (TABLE ACCESS FULL SALESREMOTE1)TABLE ACCESS BY ROW

9、ID COMPANYvINDEX UNIQUE SCAN COMPANY_PKDB link 的优化(2)多个DB Link，需要返回大量的记录，采用Merge可能比Nest Loop快select /*+ USE_MERGE(COMPANY,SALES) */COMPANY.Namefrom COMPANYREMOTE1, SALESwhere COMPANY.Company_ID = SALES.Company_IDand SALES.Period_ID =3and SALES.Sales_Total1000;vMERGE JOINSORT JOINvTABLE ACCESS FULL S

10、ALESSORT JOINvREMOTE (TABLE ACCESS FULL COMPANYREMOTE1)DB link 的优化(3)在同一SQL语句中对同一个数据库的多个远程表进行连接，有如下情况：v1、如果这个查询中既有本地表，又有多个远程表，如果查询条件是基于运程表的过滤，可以考虑在远端数据库中建立一个View，这个View基于参与操作的表，然后在本地建立一个同义词对应这个运端的View。这样可以大大减少网络的IO。View操作产生的结果（不多）与本地表进行连接，加快速度。v如果所有的表是远端表，这样做的效果不明显，因为语句都是在远端执行，最终的结果才返回到本地。存在性检验的优化v适用场合：对于利用In , Not In的语句，一般来说都可以用Exist, Not Exist来代替，用哪一个，决定于哪一个的执行效率高，IO小；这两种语法的互相代替可以减少全表扫描和操作决数，有效提高处理时间。In - Exists执行计划大表操作优化问题防止没有效果的索引用到。DB Buffer对索引取到的数据和全表扫描取到的数据内存使用策略不同。创建分区及分区索引，在SQL语句中