10g数据库上一个绑定变量sql能问题的分析与优化.doc

关于10g数据库上一个绑定变量sql性能问题的分析与优化 近期，有crm组的同事反映，他们新上线一个前台功能页面，在使用中，该页面在使用绑定变量sql时执行效率很差，与未绑定变量时性能相差很大，配合进行了分析优化，现将过程记录如下：1.分析过程-未绑定变量时sql执行计划（很快即可查出结果值）select phone_no,act_name,task_id,01,a.status_code,b.status_name,普通营销活动, to_char(create_date,yyyy-mm-dd hh24:mi:ss),to_char(create_date,yyyy-mm-dd hh24:mi:ss) ,to_char(limit_date,yyyy-mm-dd hh24:mi:ss),group_id,客户经理渠道,c.task_name from dsaletask_08 a,ssaleTASKstatusCODE b,SSALETASKTYPE c where a.status_code=b.status_code and a.task_type=c.task_type and a.status_code=1 and service_no=aa021A-| Id | Operation | Name |-| 0 | SELECT STATEMENT | | 1 | NESTED LOOPS | | -nl连接| 2 | NESTED LOOPS | | 3 | TABLE ACCESS BY INDEX ROWID| SSALETASKSTATUSCODE |* 4 | INDEX UNIQUE SCAN | PK_SSALETASKSTATUSCODE |* 5 | TABLE ACCESS BY INDEX ROWID| DSALETASK_08 |* 6 | INDEX RANGE SCAN | IDX_DSALETASK_H_SERVICE_NO | -使用索引| 7 | TABLE ACCESS BY INDEX ROWID | SSALETASKTYPE |* 8 | INDEX UNIQUE SCAN | PK_SSALETASKTYPE | -使用索引-绑定变量时sql的执行计划（变量赋值相同，但很长时间无法查出结果值）select phone_no,act_name,task_id,01,a.status_code,b.status_name,普通营销活动, to_char(create_date,yyyy-mm-dd hh24:mi:ss),to_char(create_date,yyyy-mm-dd hh24:mi:ss) ,to_char(limit_date,yyyy-mm-dd hh24:mi:ss),group_id,客户经理渠道,c.task_name from dsaletask_08 a,ssaleTASKstatusCODE b,SSALETASKTYPE c where a.status_code=b.status_code and a.task_type=c.task_type and a.status_code=:1 and service_no=:2-| Id | Operation | Name |-| 0 | SELECT STATEMENT | |* 1 | HASH JOIN | | -hash连接| 2 | NESTED LOOPS | | 3 | TABLE ACCESS BY INDEX ROWID| SSALETASKSTATUSCODE |* 4 | INDEX UNIQUE SCAN | PK_SSALETASKSTATUSCODE | 5 | TABLE ACCESS FULL | SSALETASKTYPE | -全扫|* 6 | TABLE ACCESS FULL | DSALETASK_08 | -全扫-对于绑定和不绑定变量，两个sql的执行计划有着明显差别，为什么会出现此种情况？在使用绑定变量的时候，CBO由于不知道具体的值是什么，那么他就可能无法正确判断是使用fts还是index，如果使用常量而不使用绑定变量，CBO则根据具体查询的值可以形成好的执行计划，但是SQL又会导致硬解析。如何在绑定变量和SQL优化之间进行取舍呢？在OLTP应用中，由于并发性较高，CPU上的争用会比较严重，同时SQL本身执行时间较短，涉及到的数据量较少，解析所占的时间在整个SQL执行时间中占的比例较高，而花在I/O上的时间占的比例较低。因此尽管绑定变量会有SQL不够优化的问题，还是建议使用绑定变量。为了解决这一问题，oracle引入了绑定变量窥视（Bind Variables Peeking）。绑定变量窥视是9i以后的一个新特性。所谓绑定变量窥视，就是指oracle在第一次解析SQL语句的时候（也就是说该SQL第一次传入shared pool），会将你输入的绑定变量的值带入SQL语句里，从而参考你的字面值来猜测该SQL大概会返回多少条记录，从而得到优化的执行计划。然后，以后再次执行相同的SQL语句时，不再考虑你所输入的绑定变量的值，直接取出第一次生成的绑定变量。但是，很可惜的是，使用绑定变量从而共享游标与SQL优化是两个矛盾的目标。Oracle使用绑定变量的前提，是oracle认为大部分的列的数据都是分布比较均匀的。从而，使用第一次的绑定变量的值所得到的执行计划，大多数情况下都能适用于该绑定变量的其他的值。很明显，如果第一次传入的绑定变量的值恰好占整个数据量的百分比较高，从而导致全表扫描的执行计划。而后来传入的绑定变量的值都占整个数据量的百分比都很低，则应该走索引扫描会更好的，但是由于使用了绑定变量，从而oracle并不会再去看你的绑定变量的值，而是直接拿全表扫描的执行计划来用。这时，由于使用了绑定变量，虽然我们达到了游标共享，从而节省CPU的目的，但是SQL的执行计划可能却不够优化了。在10g数据库上可以通过修改数据库隐含参数_optim_peek_user_binds来打开或关闭bind peeking（true为打开，false为关闭），但考虑oltp生产库sql执行计划稳定和性能的重要性，并且10g上的变量窥视还存在一些bug和问题，一般此项特性都是建议关闭的（但可以在session或sql级使用，后续会有介绍，本例的10g库为false）。而从11g开始，这个尴尬的问题开始得到了改善。因此从11g开始，引入了所谓的自适应游标共享（Adaptive Cursor Sharing）。该特性是一个非常复杂的技术，用来平衡游标共享和SQL优化这两个矛盾的目标。有机会大家自己研究吧！2.优化过程1)方案1(目前使用)：加入hints，使绑定与不绑定变量sql的执行计划达到一致a.取得不绑定变量sql的hints（也可考虑其他方法，此处仅供参考）步骤1：在目标库执行不绑定sql语句步骤2：由shared pool创建该语句的一个SQL Profile步骤3：查询dba_sql_profiles,sqlprof$attr视图取得正确hints结果如下：select attr_val from dba_sql_profiles a,sqlprof$attr bwhere a.signature = b.signatureand =PROF_16qvt0vhyp55k_1134956389;ATTR_VAL-IGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE()OPT_PARAM(_optim_peek_user_binds false)OPT_PARAM(_optimizer_cost_based_transformation off)OPT_PARAM(_optimizer_squ_bottomup false)OPT_PARAM(_gby_hash_aggregation_enabled false)ALL_ROWSOUTLINE_LEAF(SEL$1)INDEX_RS_ASC(SEL$1 BSEL$1 (SSALETASKSTATUSCODE.STATUS_CODE)-使用的表索引INDEX_RS_ASC(SEL$1 ASEL$1 (DSALETASK_08.SERVICE_NO)INDEX_RS_ASC(SEL$1 CSEL$1 (SSALETASKTYPE.TASK_TYPE)LEADING(SEL$1 BSEL$1 ASEL$1 CSEL$1) -表连接顺序USE_NL(SEL$1 ASEL$1) -表连接方式USE_NL(SEL$1 CSEL$1)最终得到正确hints如下：/*+ LEADING(b a c) USE_NL(a c) index(a IDX_DSALETASK_H_SERVICE_NO) index(c PK_SSALETASKTYPE) */-另一个表索引已经使用，不必加入；b.由应用加入该hints至绑定变量sql中，执行计划达到一致，性能得到优化select /*+ LEADING(b a c) USE_NL(a c) index(a IDX_DSALETASK_H_SERVICE_NO) index(c PK_SSALETASKTYPE) */ phone_no,act_name,task_id,01,a.status_code,b.status_name,普通营销活动, to_char(create_date,yyyy-mm-dd hh24:mi:ss),to_char(create_date,yyyy-mm-dd hh24:mi:ss) ,to_char(limit_date,yyyy-mm-dd hh24:mi:ss),group_id,客户经理渠道,c.task_name from dsaletask_08 a,ssaleTASKstatusCODE b,SSALETASKTYPE c where a.status_code=b.status_code and a.task_type=c.task_type and a.status_code=:1 and service_no=:2-| Id | Operation | Name |-| 0 | SELECT STATEMENT | | 1 | NESTED LOOPS | | -使用nl连接| 2 | NESTED LOOPS | | 3 | TABLE ACCESS BY INDEX ROWID| SSALETASKSTATUSCODE |* 4 | INDEX UNIQUE SCAN | PK_SSALETASKSTATUSCODE |* 5 | TABLE ACCESS BY INDEX ROWID| DSALETASK_08 |* 6 | INDEX RANGE SCAN | IDX_DSALETASK_H_SERVICE_NO | -使用索引| 7 | TABLE ACCESS BY INDEX ROWID | SSALETASKTYPE |* 8 | INDEX UNIQUE SCAN | PK_SSALETASKTYPE | -使用索引-2)方案2：在session或sql级使用_optim_peek_user_binds，开启变量窥视特性；a.在session级使用alter session set _OPTIM_PEEK_USER_BINDS = true; -开启绑定变量问题sql;alter session set _OPTIM_PEEK_USER_BINDS = false; -关闭.b.在sql级使用在对应的绑定变量问题sql中加入这个hints，该特性只对此sql有效；/*+ opt_param(_optim_peek_user_binds, true) */3)方案3：建立SQL