SQL优化思路

SQL优化思路

1. 基础数据做少量冗余，减少表关联

2. 对历史数据做迁移，保留最近一段时间数据

3. 80%的效率问题因为低效的SQL导致

4. AWR中主要查看項目

   SQL ordered by Elapsed Time查每个执行消耗的时间

   SQL ordered by CPU Time查每个执行消耗的时间

   SQL ordered by Gets        buffer gets 逻辑读 

   SQL ordered by Reads     物理读

5. 逻辑读物理读高的SQL及运行时间长的SQL需要优化

6. 通过执行计划优化SQL语句:explain plan, sql trace等

SQL> explain plan for select * from dual;

SQL> select * from table(dbms_xplan.display);

7. OLTP中优化SQL原则：尽量减少数据的读取(只读取必要的数据)

8. 表的关联部分：不管多少表连接，都是先两个表得出结果集，

   然后结果集与表或结果集的操作。

9. 几个要素：预估记录数(操作后的结果集)，开销(cost)及可选择性

10. 关联条件：where a.col1 =b.col1，

    过滤条件：where a.col1<=103（常量）

11. 使用hint来改变存在问题的执行计划

12. 查询统计信息是否是最新的(表，列，索引，柱状图)

    推荐使用 DBMS_STATS.GATHER_XXX_STATS();

13. 复杂SQL采用拆分的方式进行优化

14. 考虑新建索引以及实施分区

15. 视图访问可以考虑使用物化视图取代，减少多表连接查询

16. 表连接选择：

    嵌套循环连接：有高选择性索引或进行限制性搜索时效率比较高，适用于返回较少结果集。

    排序合并连接：当缺乏索引或者索引条件模糊时，排序合并连接比嵌套循环有效。

                  适合对大数据量需要不全等于（>, >=,<, <=）操作的情况下。

    哈希连接    ：当缺乏索引或者索引条件模糊时，哈希连接连接比嵌套循环有效。

                  通常比排序合并连接快。在数据仓库环境下，如果表的纪录数多，

                  效率高。用于等价连接下大数据关联。

17. 参数 OPTIMIZER_INDEX_COST_ADJ， OPTIMIZER_INDEX_CACHING

18. CBO尽可能从过滤性最强的条件入手

19. 子查询上约束条件强，适用IN, 主语句上约束条件强，使用EXISTS

20. 调优相关的几个视图

v$session

V$sqltext

V$sql_plan

V$sql_plan_statistics

V$sql_plan_statistics_all

V$ses_optimizer_env

User_table_histograms

User_tab_statistics

User_tab_col_statistics

User_ind_statistics

21. 使用10046事件，可以跟踪某个SQL语句完整的执行过程，

获取其解析，执行，CPU使用时间，等待事件，每个操作的

具体耗时等信息。这对获取语句详细的执行计划，分析定位

其效率问题，从而有针对性地优化该语句，非常有用。

Alter session set events '10046 trace name conetxt,  level  n';

执行SQL。

Alter session set events '10046 trace name conetxt,  off';

22. 使用outline固定执行计划

23. 使用DBMS_SQLTUNE包及DBMS_ADVISOR包获取语句最简执行计划建议

24. 给CBO采集系统的统计信息，执行计划将更优。

Exec dbms_stats.gather_system_stats(‘INTERVAL’, 180);

Select * from sys.aux_stats$;

25. DBMS_PROFILE包用来调优存储过程，通过跟踪及记录存储过程

    中每条SQL的执行时间，来确定哪些SQL语句最耗时。

25. 11g中ACS自适应共享游标克服绑定窥视的弊端

26. 11g中对联合列收集统计信息