Oracle SQL性能调整注意事项

ibaobei

　　1、不要认为将optimizer_mode参数设为rule，就认为所有的语句都使用基于规则的优化器。不管optimizer_mode参数如何设置，只要满足下面3个条件，就一定使用CBO。
　　1） 如果使用Index Only Tables（IOTs）， 自动使用CBO.
　　2） Oracle 7.3以后，如果表上的Paralle degree option设为>1，则自动使用CBO, 而不管是否用rule hints.
　　3） 除rlue以外的任何hints都将导致自动使用CBO来执行语句
　　总结一下，一个语句在运行时到底使用何种优化器可以从下面的表格中识别出来，从上到下看你的语句到底是否满足description列中描述的条件：
　　Description        对象是否被分析   优化器的类型
　　~~~~~~~~~~~        ~~~~~~~~~~~~    ~~~~~~~~~
　　Non-RBO Object （Eg:IOT） n/a        #1
　　Parallelism > 1      n/a        #1
　　RULE hint         n/a        RULE
　　ALL_ROWS hint       n/a        ALL_ROWS
　　FIRST_ROWS hint      n/a        FIRST_ROWS
　　*Other Hint        n/a        #1
　　OPTIMIZER_GOAL=RULE    n/a        RULE
　　OPTIMIZER_GOAL=ALL_ROWS  n/a        ALL_ROWS
　　OPTIMIZER_GOAL=FIRST_ROWS n/a        FIRST_ROWS
　　OPTIMIZER_GOAL=CHOOSE   NO        RULE
　　OPTIMIZER_GOAL=CHOOSE   YES        ALL_ROWS
　　#1 表示除非OPTIMIZER_GOAL 被设置为FIRST_ROWS ，否则将使用ALL_ROWS。在PL/SQL中，则一直是使用ALL_ROWS
　　*Other Hint 表示是指除RULE、ALL_ROWS 和FIRST_ROWS以外的其它提示
　　2、当CBO选择了一个次优化的执行计划时，不要同CBO过意不去，先采取如下措施：
　　a） 检查是否在表与索引上又最新的统计数据
　　b） 对所有的数据进行分析，而不是只分析一部分数据
　　c） 检查是否引用的数据字典表，在oracle 10G之前，缺省情况下是不对数据字典表进行分析的。
　　d） 试试RBO优化器，看语句执行的效率如何，有时RBO能比CBO产生的更好的执行计划
　　e） 如果还不行，跟踪该语句的执行，生成trace信息，然后用tkprof格式化trace信息，这样可以得到全面的供优化的信息。
　　3、假如利用附录的方法对另一个会话进行trace，则该会话应该为专用连接
　　4、不要认为绑定变量（bind variables）的缺点只有书写麻烦，而优点多多，实际上使用绑定变量虽然避免了重复parse，但是它导致优化器不能使用数据库中的列统计，从而选择了较差的执行计划。而使用硬编码的SQL则可以使用列统计。当然随着CBO功能的越来越强，这种情况会得到改善。目前就已经实现了在第一次运行绑定变量的sql语句时，考虑列统计。
　　5、如果一个row source 超过10000行数据，则可以被认为大row source
　　6、有（+）的表不是driving table，注意：如果有外联接，而且order hint指定的顺序与外联结决定的顺序冲突，则忽略order hint
　　7、影响CBO选择execution plan的初始化参数：
　　这些参数会影响cost值
　　ALWAYS_ANTI_JOIN
　　B_TREE_BITMAP_PLANS
　　COMPLEX_VIEW_MERGING
　　DB_FILE_MULTIBLOCK_READ_COUNT
　　FAST_FULL_SCAN_ENABLED
　　HASH_AREA_SIZE
　　HASH_JOIN_ENABLED
　　HASH_MULTIBLOCK_IO_COUNT
　　OPTIMIZER_FEATURES_ENABLE
　　OPTIMIZER_INDEX_CACHING
　　OPTIMIZER_INDEX_COST_ADJ
　　OPTIMIZER_MODE> / GOAL
　　OPTIMIZER_PERCENT_PARALLEL
　　OPTIMIZER_SEARCH_LIMIT
　　PARTITION_VIEW_ENABLED
　　PUSH_JOIN_PREDICATE
　　SORT_AREA_SIZE
　　SORT_DIRECT_WRITES
　　SORT_WRITE_BUFFER_SIZE
　　STAR_TRANSFORMATION_ENABLED
　　V733_PLANS_ENABLED
　　CURSOR_SHARING
　　oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html