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

如何写出优质高效的数据库程序基本原则简单的是最有力的；2/8原则；性能优化是动力是基于对美的追求。基本约定SQL语句书写的约定，如关键字要大写,函数，对象名，变量要小写，一个关键字一行，每行以关键字右对齐，等号左右各空一位等，各个部分仅空一格；在SQL语句中对于值非常多的列条件要用变量绑定，不要用常量；对于值少记录多，且分布不均匀，如果要用上该列的索引，就要用常量。在SQL语句中尽量不要用左值函数；查询条件的顺序要尽量能够利用已有索引，如果不能利用现有的索引而需要新建综合索引，索引列的顺序、查询条件的顺序要和表中的列顺序一致。如果能够确保两个输出值集没有重复值，UnionAll比Union有效率。一些建议某些复杂和耗时的SQL语句可以考虑采用几个语句来完成。对使用外连接的语句，可以尝试用一些组合语句来代替。如果能够用行操作SQL语句完成，不要用集操作SQL(如count,Distinct,minus,intersect)语句完成。问题1SELECTBOARD_BARCODEFROMSMT_BOARD_WORK_RECbwr,SMT_DEP_TASK_INFOdtiWHEREBOARD_BARCODE='025767102A000842'ANDdti.Second_Series='标准'andbwr.Task_No=dti.Task_NoANDPROC_ORDER=1;SELECTBOARD_BARCODEFROMSMT_BOARD_WORK_RECbwr,SMT_DEP_TASK_INFOdtiWHEREBOARD_BARCODE=:b1ANDdti.Second_Series='标准'andbwr.Task_No=dti.Task_NoANDPROC_ORDER=1;没有用变量绑定，导致大量的Parse，占用CPU时间和内存。效果下面是使用文字变量和绑定变量通过Spotlight进行对比的观察结果。不良代码为执行1500行操作，将同样的SQL解释了500次，对应的在内存中占用500倍的空间，带来严重的latch争用。

。在使用绑定变量的情况下，留意对应SQL的Loads和Exections列值，运行次数逐渐增长为500、1000、1500直到2000。而变化时，Loads值始终是1，说明sharedpool共享性好，同时latch争用也会比较少。问题2SELECT

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_updated_date,

collect_user

FROMhw_cpb_relation

WHERETO_CHAR(created_date,'yyyy/mm/ddhh24:mi:ss')

>=

'2002/10/1615:16:06'SELECT

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_updated_date,

collect_user

FROMhw_cpb_relation

WHEREcreated_date>=to_date(:b1,'yyyy/mm/ddhh24:mi:ss’)

在SQL语句的Where条件的字段上加上函数，导致索引不可用或效率低索引的创建经常在哪些字段做连接或加过滤条件数据是否经常做update值的重复频率是否非常高是否几个字段一起经常用来做组合查询索引的建立原则对于值不多但记录非常多，且值分布比较均匀的字段，基于此字段的索引对查询的优化没有作用，同时带来Insert操作时索引的维护开销；对于状态类字段，值不多，如果记录多，但这些记录在状态中的分布极不均匀，可以建立索引，索引的条件带常量才可用到索引。索引的使用不应当用于表上10％以上的数据。很小的表一两次IO就可以对其扫描也没有必要使用索引组合索引可以压缩以减少IO，但会增加CPU的消耗反序索引的使用Bitmap索引的使用不要在表上建太多索引编写sql的一些技巧使用hints，让sql按照希望的方式来执行不使用某些特定的索引不要频繁commit业务规则定义在数据库底层的效率高外键应当被索引存储过程要比外部过程效率高尽可能使用绑定变量来减少HARDPARSE尽可能重用游标来减少SOFTPARSE为什么索引没有被使用查询条件没有包含组合索引的主要边界索引不包含空的条目查询条件对索引列使用了函数低效的索引CBO的统计信息过于陈旧DBlink的优化(1)驱动表的选择：对于有一个DBLink的语句，选取远程表作为驱动表selectCOMPANY.NamefromCOMPANY,SALES@REMOTE1whereCOMPANY.Company_ID=SALES.Company_IDandSALES.Period_ID=3andSALES.Sales_Total>1000;NESTEDLOOPSREMOTE(TABLEACCESSFULLSALES@REMOTE1)TABLEACCESSBYROWIDCOMPANYINDEXUNIQUESCANCOMPANY_PKDBlink的优化(2)多个DBLink，需要返回大量的记录，采用Merge可能比NestLoop快select/*+USE_MERGE(COMPANY,SALES)*/COMPANY.NamefromCOMPANY@REMOTE1,SALESwhereCOMPANY.Company_ID=SALES.Company_IDandSALES.Period_ID=3andSALES.Sales_Total>1000;MERGEJOINSORTJOINTABLEACCESSFULLSALESSORTJOINREMOTE(TABLEACCESSFULLCOMPANY@REMOTE1)DBlink的优化(3)在同一SQL语句中对同一个数据库的多个远程表进行连接，有如下情况：1、如果这个查询中既有本地表，又有多个远程表，如果查询条件是基于运程表的过滤，可以考虑在远端数据库中建立一个View，这个View基于参与操作的表，然后在本地建立一个同义词对应这个运端的View。这样可以大大减少网络的IO。View操作产生的结果（不多）与本地表进行连接，加快速度。如果所有的表是远端表，这样做的效果不明显，因为语句都是在远端执行，最终的结果才返回到本地。存在性检验的优化适用场合：对于利用In,NotIn的语句，一般来说都可以用Exist,NotExist来代替，用哪一个，决定于哪一个的执行效率高，IO小；这两种语法的互相代替可以减少全表扫描和操作决数，有效提高处理时间。In->Exists执行计划大表操作优化问题防止没有效果的索引用到。DBBuffer对索引取到的数据和全表扫描取到的数据内存使用策略不同。创建分区及分区索引，在S