ORACLE_OCP之性能管理-SQL调优

ORACLE_OCP之性能管理-SQL调优

• 文章目标：
  • 管理优化器统计信息
  • 使用SQL Tuning Advisor可以: （重点记忆）
  • 识别使用最多资源的SQL语句
  • 调整使用最多资源的SQL语句
  • 使用SQL Access Advisor调整工作负载（重点记忆）

一、SQL优化

• 识别和调整不良的SQL语句。
• 调整各个语句。
• 调整整个应用程序。

二、Oracle Optimizer（ORACLE优化器）：概述

• Oracle优化器确定最有效的执行计划，并且是处理任何SQL语句中最重要的步骤.
• 优化器:
  • 评估SQL表达式和条件
  • 使用对象和系统统计信息
  • 决定如何访问数据
  • 决定如何联接表
  • 确定最有效的路径（最佳的执行计划）

三、优化器统计

• 一个时间点的快照
• 持续跨实例收集
• 自动收集

四、优化器统计信息收集

• SQL性能调优：取决于准确的统计信息的收集

• 优化器统计：

  • 对象统计
  • 操作系统统计

• 收集统计信息的方法：

  • 自动：自动维护任务
  • 手动：DBMS_STATS软件包
  • 通过设置数据库初始化参数
  • 通过从另一个数据库导入统计信息

• 系统统计信息向查询优化器描述了系统的硬件特性，例如I / O以及CPU性能和利用率。 选择执行计划时，优化器会估算每个查询所需的I / O和CPU资源。 系统统计信息使查询优化器可以更准确地估计I / O和CPU成本，从而选择更好的执行计划。

• 使用DBMS_STATS.GATHER_SYSTEM_STATS过程收集系统统计信息。

• Oracle数据库服务器收集系统统计信息时，会在指定的时间段内分析系统活动。 系统统计信息不会自动收集。 Oracle Corporation（甲骨文公司）建议您使用DBMS_STATS包来收集系统统计信息。

• 可以直接使用DBMS_STATS软件包收集优化器统计信息：

  SQL> EXEC dbms_stats.gather_table_stats(‘HR’，‘EMPLOYEES’);
  SQL> SELECT num_rows FROM dba_tables WHERE owner=‘HR’ AND table_name = ‘EMPLOYEES’;

• 最佳实践提示：创建数据库后执行以下命令：

  SQL> EXEC dbms_stats.gather_system_stats（‘NOWORKLOAD’）;

• NOWORKLOAD选项花费几分钟（取决于数据库的大小），并捕获I / O特性（例如平均读取寻道时间和I / O传输速率）的估计值。

五、设置优化器统计信息首选项

• 优化器统计信息首选项包含以下默认参数:

  • Automatic statistics collection（自动统计信息收集）
  • DBMS_STATS.GATHER_*_STATS procedures

• 设置性能级别:

  Table, schema, database, or global

• 首选项: CASCADE, DEGREE, ESTIMATE_PERCENT, NO_INVALIDATE, METHOD_OPT, GRANULARITY, INCREMENTAL, PUBLISH, STALE_PERCENT

• 使用DBMS_STATS.SET | GET | DELETE | EXPORT | IMPORT_*_PREFS 管理相关性能

EXEC DBMS_STATS.SET_TABLE_PREFS(‘SH’,‘SALES’,‘STALE_PERCENT’,‘13’);

• 首选项包括：
  • CASCADE –确定是否将索引统计信息收集为收集表统计信息的一部分
  • DEGREE –设置用于收集统计信息的并行度
  • PUBLISH –用于决定是将统计信息发布到字典还是将其存储在私有区域中。这使DBA可以先验证统计信息，然后再使用PUBLISH_PENDING_STATS过程将统计信息发布到数据字典中。
  • STALE_PERCENT –用于确定阈值级别，在该阈值级别，对象被认为具有过时的统计信息。该值是自上次统计信息收集以来已修改的行的百分比。该示例仅将SH.SALES的10％默认值更改为13％。
  • INCREMENTAL –用于以增量方式收集分区表的全局统计信息
  • METHOD_OPT –确定用于收集列统计信息的列和直方图参数
  • GRANULARITY –确定要收集的统计信息的粒度（仅在表已分区时才相关）
  • NO_INVALIDATE –用于确定是否使游标无效
  • ESTIMATE_PERCENT –用于确定要获得良好统计信息的样本行数。它是表中行数的百分比

六、并发统计收集

• 自动统计信息收集作业使用并发性。 适用于自动采集
• 防止并发统计信息收集，以免由于谨慎的资源使用上限而使系统不堪重负。-> 手动采集
• 允许同时收集索引统计信息。
• 允许同时收集多个分区表的统计信息。

七、查看统计信息

八、SQL计划指令

• SQL计划指令是优化程序可用来生成更好计划的附加信息和指令：
  收集缺失的统计信息
  创建列组统计信息
  执行动态采样
• 指令可用于多个语句：
  • 指令是根据查询表达式收集的
• 它们被持久化到SYSAUX表空间中.
• 指令自动维护:
  • 在编译或执行期间根据需要创建:
    • 缺少统计信息，基数估计错误
  • 如果一年后不使用，则清除

九、自适应执行计划

• 语句执行计划在执行期间会更改，因为运行时某些条件会因为优化器估计不足而造成不正确选择。
• 所有自适应执行计划都依赖于语句执行期间收集的统计信息.
• 两种自适应计划技术是：
  • 动态计划生成
  • 优化器再评古
• 当OPTIMIZER_FEATURES_ENABLE设置为12.1.0.1或更高，并且OPTIMIZER_ADAPTIVE_REPORTING_ONLY设置为默认值FALSE时，数据库将使用自适应执行计划。

十、SQL Tuning Advisor（SQL优化顾问）: 概述

十一、使用SQL优化顾问

• 使用SQL Tuning Advisor分析SQL语句并获得性能优化建议.
• SQL Tuning Advisor的源进行分析:

  • Top活动记录: 分析当前活动的TOP SQL语句

    注：这里的TOP指的是高开销语句

  • SQL调优集合：分析您提供的一组SQL语句

  • 历史SQL (AWR):从AWR报告中分析SQL语句

十二、SQL Access Advisor（SQL访问顾问）: Overview

• SQL Access Advisor可以为给定的工作负载推荐适当的物化视图集，物化视图日志，分区和索引。 在优化SQL时，理解和使用这些结构至关重要，因为它们可以显着提高数据检索的性能。
• SQL Access Advisor建议使用位图索引，基于函数的索引和B树索引。 与其他索引技术相比，位图索引可缩短许多类型的即时查询的响应时间，并减少存储需求。 B树索引最常用于数据仓库中，以索引唯一或接近唯一的键。
• SQL Access Advisor的另一个组件还建议如何优化物化视图，以便它们可以快速刷新并利用常规查询重写的优势。

注意：有关实例化视图和查询重写的更多信息，请参见《 Oracle数据库性能调优指南》。

十三、SQL Performance Analyzer(SQL性能分析器): 概述

• 目标用户：DBA，QA（运维），应用程序开发人员
• 帮助预测系统更改对SQL工作负载响应时间的影响
• 构建不同版本的SQL工作负载性能（即，SQL执行计划和执行统计信息）
• 串行执行SQL（不支持并发）
• 分析性能差异
• 提供有关单个SQL的细粒度性能分析
• 与SQL Tuning Advisor集成以调整回归（统计学概念）

十四、SQL性能分析器: 使用条件

• SQL Performance Analyzer在以下用例中很有用：
  • 数据库升级
  • 实施优化建议后
  • 模式（SCHEMA）变更
  • 统计数据收集（也包括一些失效的统计数据）
  • 数据库参数更改
  • 操作系统和硬件更改
• 可通过企业管理器和DBMS_SQLPA程序包进行访问.

十五、使用SQL性能分析器

• 在生产中捕获SQL工作负载。
• 将SQL工作负载传输到测试系统。
• 建立“变更前”性能数据。
• 做出改变。（性能分析器内部改变测试）
• 建立“变更后”性能数据。
• 比较步骤3和5的结果。
• 调整SQL。