几种改变执行计划的方法

一、使用Hint

1、可以从v$sql_hint视图中查询Oracle所支持的所有的hint

2、Hint分为6种：

    1）初始化参数hint(可以覆盖在系统级或者会话级定义的初始化参数)，如：

all_rows, first_rows,cursor_sharing_exact, dynamic_sampling, gather_plan_statisitcs,

  no_cpu_costing, optimizer_features_enable,opt_param,

  (no_)result_cache,rule

    2）查询转化hint（在逻辑优化阶段控制查询转化技术），如：

(no_ )eliminate_join,no_expand,(no_)merge,(no_)outer_join_inner,(no_)push_pred,(no_)push_subq,

  no_query_transformation,(no_)rewrite,(no_)nunest,no_xmlindex_rewrite,no_xml_query_rewrite use_concat

    3）访问路径hint（控制访问数据的方法，比如是否使用索引等等），如：

cluster,full,hash,(no_)index,index_asc,index_combine,index_desc,(no_)index_ffs,index_join,(no_)index_ss,

   index_ss_asc,index_ss_desc

    4）连接提示hint（不仅控制连接的方法，还控制连接表的顺序），如：

leading,(no_)nljbatching,ordered,(no_)start_transformation,(no_)swap_join_inputs,(no_)use_hash,

   (no_)use_merge,use_merge_cartesian,(no_)use_nl,use_nl_with_index

    5）并行处理hint（控制如何使用并行处理），如：

(no_)parallel,(no_)parallel_index,pq_distribute,(no_)px_join_filter

    6）其他hint（控制没有归到前几种分类的其他一些特性的使用），如：

(no_)append, (no_)cache,driving_site,model_min_analysis,(no_)monitor,qd_name

二、使用outline(大纲)或sql profile固定执行计划
create or replace outline myoutline
for category mycategory
on
select count(*) from t  ;

三、SQL改写
1、with 子句：可解决表被多次扫描的问题
2、insert all： （在查询期间数据无变化的前提下），可将多条insert语句用insert all来改写
3、rownum分页：
4、rownum实体化: 加入rownum之后，某些情况下可改变表连接的顺序(11.2.0.3以前的版本)
5、TABLE ACCESS BY USER ROWID方式：若在查询条件中使用rowid作为条件，则可在一定程度上优化执行计划
6、result cache： 使用缓存结果集
select /*+ result_cache */ count(*) from t;
此SQL今后的执行中将使用第一次缓存的结果集；但是，当结果集发生变化时（数据更新后），会重新刷新缓存结果集，因此，此方法不适用于经常变化的表！！
7、谓词中是否使用了分区条件：如果是分区表，则谓词中一定使用分区条件，才会在分区范围内进行查询。

四、利用数据库设计特性
1、分区设计：根据实际情况，考虑是否使用分区表
2、cluster（簇表）表类型：Cluster表可避免排序；但是cluster表因为排序的特性，更新较慢
3、iot（索引组织表）表类型：iot表可消除“回表” — TABLE ACCESS BY INDEX ROWID，因此查询性能会有所提升
4、物化视图：物化视图区别于普通视图，它实际存储了数据，并且只在刷新时才会去重新查询一次，所以适时地使用物化视图，能提高查询性能
5、并行度的影响：适当地使用并行查询
6、列空值的影响：适当地加上非空约束也能优化执行计划
7、主外键影响：表连接查询时，是否存在主外键，也会影响执行计划

五、其他手段
1、使用dbms_stats.SET_table_stats人为修改表的统计信息，也会影响执行计划
2、预估索引效果（虚拟索引）：create index ix_t_id on t(object_id) nosegment;

转载于:https://www.cnblogs.com/wanghang/p/6299171.html