OCP课程56：管理II之SQL调优

  • stonebox1122

    2017-04-11 09:03:32
  • Oracle
  • 原创

课程目标：

  • 使用SQL调优指导定位使用最多资源的SQL语句并调优
  • 使用SQL访问指导调优负载

1、SQL调优

clipboard

一般来说，对SQL调优会获得最大的性能提升。差的SQL语句会消耗额外的操作系统和数据库资源，增加响应时间。我们需要先定位到差的SQL语句再进行调优。可以对单个SQL语句进行调优，但往往对一个语句的调优方案会影响到其他语句的性能。

使用最多资源的SQL语句往往需要调优，包括运行最长时间，使用最多CPU或者最多物理读或逻辑读。

通过检查优化器统计数据，执行计划，测试其他SQL结构，索引，物化视图和分区调优SQL语句。

评估使用调优的SQL语句后，对整个应用来说，性能是否更好了。调优单个语句并不是很复杂，难的是单个语句调优后测试对整个系统的影响。

在Oracle 11g中，提供了一些SQL指导用于识别和调优语句。

2、SQL指导

clipboard[1]

Oracle 11g提供了一系列的SQL指导，包括SQL访问指导，SQL调优指导，SQL性能分析器和SQL修复指导。AWR识别和记录最近高负载SQL语句的统计数据。

SQL调优指导一次分析一个或多个SQL语句，检查统计数据，SQL配置文件，索引，物化视图，并重组SQL。SQL调优指导可以随时手动运行，在每一个维护窗口中都会对近期的高负载SQL语句运行SQL调优指导。单击“自动SQL调优结果集”查看和实施建议。这种自动工作可以配置为自动执行SQL配置文件。

SQL访问指导用于评估对一组SQL语句集进行改变后性能的提升。语句集可以是一组假设的SQL，历史SQL集或者手动创建的SQL集。

对于影响SQL执行计划的任何数据库环境变化，SQL性能分析程序可以用来预测和防止潜在的性能问题。

当一个SQL语句由于关键错误运行失败，会从支持工作台运行SQL修复指导。一个关键的错误也会产生一个事件。修复指导试图寻找和定位一个SQL补丁。如果没有找到补丁，可以继续在支持工作台打包事件并作为服务请求提交给Oracle支持。

clipboard[2]

clipboard[3]

3、自动SQL调优结果集

clipboard[4]

自动SQL调优任务默认每天晚上运行。在自动SQL调优结果集页面点击”查看报告”，可以查看已经检查过的SQL语句。

单击“配置”按钮改变自动调整任务的默认设置并启用SQL配置文件自动执行。

clipboard[5]

clipboard[6]clipboard[7]

4、实施自动调优建议

clipboard[8]

在自动调优结果汇总页面点击”查看报告”，会看到自动SQL调优结果的细节。可以执行所有的建议或向下钻取查看或执行单独的建议。在”建议”页面，点击眼镜图标查看执行SQL概要文件后的差异。

clipboard[9]

clipboard[10]

clipboard[11]

clipboard[12]

clipboard[13]

5、SQL调优指导：概览

clipboard[14]
SQL调优指导是主要的调优手段，执行以下几类分析：

  • Statistics Analysis：检查查询对象的统计数据是否缺失或者过时，并建议收集相关的统计数据。
  • SQL Profiling：使用辅助信息创建SQL概要文件，使查询优化器可以产生一个调优的执行计划。
  • Access Path Analysis：评估是否需要通过新的索引来提高性能。
  • SQL Structure Analysis：定位使用性能低下的执行计划的SQL语句并对重构该SQL语句提出建议。

SQL调优指导在独立的指导任务中评估SQL语句，不考虑其他因素。比如创建索引可能会提高查询速度，但会影响DML操作的速度。所以需要使用SQL访问指导在负载下评估是否对性能有好处。

6、使用SQL调优指导

clipboard[15]

SQL调优指导作为自动SQL调优任务每天晚上自动运行。针对需要立即进行调优的SQL语句，可以使用SQL调优指导在任何时间分析SQL语句并获取性能建议。一般情况下，会作为ADDM性能查找活动来运行SQL调优指导。

另外，也可以使用SQL调优指导来分析消耗最大CPU，I/O和内存的TOP SQL语句。

即使可以在一个任务中提交多条语句进行分析，但是还是对每一条语句单独进行分析。要获取一个SQL集的整体的性能建议，需要使用SQL访问指导。

7、SQL调优指导选项

clipboard[16]

在“调度SQL调优指导”页面，可以选择调优任务的SQL语句。如果有ADVISOR系统权限，可以设置SQL语句来源并提交任务。EM会为SQL调优指导创建一个调优任务。

可以从顶级活动中选择一个或者多个SQL语句，从AWR中选择历史SQL语句或者从已经创建的SQL调优集选择SQL语句进行调优。

如果选择“Limited”选项，则不会生成SQL概要文件。选择“Comprehensive”模式会调用SQL profiling模式下的优化器来创建SQL概要文件。

8、SQL调优指导建议

clipboard[17]

调优任务完成后会生成调优结果集，可以在“指导中心”页面看到。

9、使用SQL调优指导：示例

clipboard[18]

按照以下步骤使用SQL调优指导：

（1）在主页点击“相关链接”区域的“指导中心”。

（2）点击“SQL Advisors（SQL 指导）”，可以看到“SQL Tuning Advisor（SQL 调优指导）”链接。其数据来源有三类：

    • Active SQL（顶级活动）：分析当前活动的Top SQL语句
    • SQL Tuning Sets（SQL优化集）：分析提供的SQL语句集
    • Historical SQL（AWR）：分析AWR中的SQL语句

（3）选择Active SQL（顶级活动）。通过拖动阴影框选项分析的时间间隔，然后选择一个或者多个语句来进行分析。

（4）选择“Run SQL Tuning Advisor”，在“SQL Tuning Options（SQL调优选项）”页面可以看到选择的SQL语句。指定任务名称和描述，选择“Comprehensive（综合）”以及“Immediately（立即）”，然后点击OK。

（5）回到“Advisor Central（指导中心）”页面，在“结果”区域列出了各个指导任务的状态。等到刚刚的任务完成，点击任务名称查看结果。

（6）选择SQL语句查看生成的建议。

例子：使用SQL调优指导

clipboard[19]

clipboard[20]

clipboard[21]

clipboard[22]

clipboard[23]

clipboard[24]

clipboard[25]

clipboard[26]

clipboard[27]

SQL> create table hr.objtest as select * from dba_objects;

Table created.

SQL> conn hr/hr

Connected.

SQL> insert into objtest select * from objtest;

86987 rows created.

SQL> r

  1* insert into objtest select * from objtest

173974 rows created.

SQL> r

  1* insert into objtest select * from objtest

347948 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from objtest;

  COUNT(*)

----------

    695896

SQL> exec dbms_stats.gather_table_stats('HR','OBJTEST');

PL/SQL procedure successfully completed.

SQL> select owner,object_name from objtest where object_name='EMP';

SQL> set autot traceonly

SQL> r

  1* select owner,object_name from objtest where object_name='EMP'

24 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 927592698

-----------------------------------------------------------------------------

| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |         |    13 |   403 |  2750   (1)| 00:00:34 |

|*  1 |  TABLE ACCESS FULL| OBJTEST |    13 |   403 |  2750   (1)| 00:00:34 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OBJECT_NAME"='EMP')

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

      10109  consistent gets

          0  physical reads

          0  redo size

       1043  bytes sent via SQL*Net to client

        530  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         24  rows processed

clipboard[28]

clipboard[29]

clipboard[30]

clipboard[31]

clipboard[32]

10、重复的SQL

clipboard[33]

重复的SQL语句是指那些只有字变量的值不一样的语句。在库缓存中不同的语句有不同的游标，重复的SQL语句如果使用绑定变量替换字变量就可以使用相同的游标。

可以通过“性能”页面的“其他监视链接”区域的“重复的SQL”链接查看重复的SQL语句。这个可以帮助你确定应用里面哪些SQL语句可以合并以降低对库缓存的需求及提高执行速度。

例子：查看重复的SQL

SQL> select * from employees where employee_id=100;

SQL> select * from employees where employee_id=101;

SQL> select * from employees where employee_id=102;

SQL> select * from employees where employee_id=103;

SQL> select * from employees where employee_id=104;

clipboard[34]

clipboard[35]

创建绑定变量的情况：

SQL> var deptid number

SQL> exec :deptid:=20;

PL/SQL procedure successfully completed.

SQL> select * from departments where department_id=:deptid;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

           20 Marketing                             201        1800

SQL> exec :deptid:=30;

PL/SQL procedure successfully completed.

SQL> select * from departments where department_id=:deptid;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

           30 Purchasing                            114        1700

SQL> exec :deptid:=40;

PL/SQL procedure successfully completed.

SQL> select * from departments where department_id=:deptid;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

           40 Human Resources                       203        2400

SQL> exec :deptid:=50;

PL/SQL procedure successfully completed.

SQL> select * from departments where department_id=:deptid;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

           50 Shipping                              121        1500

11、SQL访问指导：概览

clipboard[36]

SQL 访问指导将评估工作量源中的 SQL 语句, 并可以建议索引, 分区, 物化视图和物化视图日志, 它们可以改善工作量的整体性能。

SQL访问指导可以建议位图索引，函数索引和B树索引。还可以对如何优化物化视图提供建议。

12、典型的SQL访问指导会话

clipboard[37]

可以选择使用推荐的预定义指导选项启动SQL访问指导会话，还可以通过选择“从以前保存的任务或模板继承选项”复选框，使用包括通用环境、OLTP和数据仓库模版或者之前运行过的任务使用的选项，来启动任务。

clipboard[38]

clipboard[39]

clipboard[40]

13、工作量源

clipboard[41]

在工作量源页面指定进行分析的工作量，支持的工作量源如下：

  • 当前和最近的 SQL 活动
  • 使用现有的 SQL 优化集
  • 从下列方案和表创建假想工作量

应用过滤器可以缩小工作量中找到的语句的范围。使用过滤器有两个好处。首先, 它可以指示“指导”根据工作量中特定语句子集提出建议案, 从而产生更高质量的建议案。其次, 移去工作量中无关的语句可以显著减少处理时间。

过滤选项有：

  • 消耗资源最多的 SQL 语句
  • 用户、模块及操作
  • 表

clipboard[42]

clipboard[43]

14、建议案选项

clipboard[44]

从“建议的访问结构”区域选择访问方式，可以选择索引、物化视图以及分区，默认是选择索引。也可以都不选择，仅对现有结构进行分析。

可在限制模式或综合模式下运行。限制模式是指处理最高成本语句后迅速返回, 有可能忽略成本低于特定阈值的语句。而在综合模式下则执行详尽分析。t

clipboard[45]

15、建议案选项

clipboard[46]

高级选项包括工作量类别、空间限制、优化优先级以及默认存储位置。

工作量类别中，如果是数据仓库工作量, 选择工作量不稳定性下的“只考虑查询”复选框，如果工作量代表所有访问结构用例时，选择工作量范围下的“建议删除未使用的访问结构”复选框。

空间限制中，可以选择不限制建议的索引和物化视图所使用的附加空间，也可以指定将附加空间限制为多少M，设置为零或负数表示建议删除现有的访问结构, 以便为更好的访问结构留出空间。

优化优先级中，选择确定优化 SQL 语句优先级的依据，如果选择“考虑访问结构创建成本建议案”复选框，SQL 访问指导将针对 SQL 语句执行时间的频率和改善可能性来衡量创建访问结构的成本。如果未选中, 将忽略创建成本。如果您不需要为并非频繁执行的语句生成的特定建议案, 则选中此复选框。

默认存储位置中，默认情况下, 索引将被放置在它们所引用的表的方案和表空间中, 而实体化视图将被放置在查询中引用的第一个表的方案和表空间中, 实体化视图日志将被放置在它们所引用的表方案的默认表空间中。使用相应的字段可以更改这些默认位置。

clipboard[47]

16、查看建议

clipboard[48]

使用指导中心页，可以列出所有SQL访问指导完成的任务。选择一个建议，然后点击查看结果按钮，以获得指导结果的概述。该网页的图表和统计数据提供整体的工作负载性能和查询执行时间潜在的改进建议。可以使用该页面显示语句计数和推荐操作数。

点击页面上的其他三个选项卡：建议案，SQL语句或者详细资料查看指定任务的其他方面。

建议案页面列出了一些建议案, 这些建议案最初是按最大成本改善进行排序的。实施排在最前面的建议案将最大程度地改善整体性能。

clipboard[49]

17、SQL性能分析程序：概览

clipboard[50]

Oracle数据库11g包括SQL性能分析程序，可以测试和分析变更对 SQL 优化集中包含的 SQL 执行性能的影响。SQL性能分析程序可以帮助你预测对SQL查询的工作量潜在变化的影响。此功能为DBA提供了SQL语句性能的详细信息，如之前和之后执行统计数据，性能提升或者下降的语句。

clipboard[51]

18、SQL性能分析器：使用案例

clipboard[52]

对于任何数据库环境变化影响的SQL执行计划，SQL性能分析程序可以用来预测和防止潜在的性能问题。这些环境变化包括（但不限于）以下：

  • 数据库升级
  • 调优建议的实施
  • 模式变化
  • 统计数据收集
  • 数据库参数变化
  • 操作系统和硬件更改

可以在复杂的环境中使用SQL性能分析程序预测SQL性能的变化。随着应用程序进入开发生命周期，数据库应用程序开发人员可以测试模式，数据库对象的变化，并重写应用程序，以减轻任何潜在的性能影响。

可以通过EM或使用dbms_sqlpa包访问SQL性能分析程序。

19、使用SQL性能分析程序

clipboard[53]

使用SQL性能分析程序的步骤：

（1）收集SQL：在生成系统上搜集有代表性的SQL语句。

（2）传输：必须将所得的工作量传输给测试系统。从生产系统导出STS然后导入到测试系统。

（3）计算改变前的性能：在任何变化发生前，执行SQL语句，收集对后续有性能影响的改变的基准信息。

（4）做出改变：执行改变，查看对性能的影响。

（5）计算改变后的性能：在数据库环境更改后，模拟运行SQL语句，收集与步骤3相同的信息。

（6）比较和分析SQL性能：在有了改变前后的性能数据之后，可以进行性能比较分析。

（7）调优SQL：在这个阶段，你已经确定了哪些SQL语句可能会导致性能问题时。可以使用任何数据库工具来调优系统。实施任何调优动作后，应该重复该过程以创建新的版本，并分析性能差异，以确保新的性能是可以接受的。

例子：使用SQL性能分析程序评估参数变化的影响

创建SQL优化集

clipboard[54]

clipboard[55]

clipboard[56]

clipboard[57]

clipboard[58]clipboard[59]

clipboard[60]

clipboard[61]

clipboard[62]

请使用浏览器的分享功能分享到微信等