Oracle DB数据库维护（1）

Oracle微学堂 2020-01-09

982

管理优化程序统计信息

• 管理自动工作量资料档案库(AWR)

• 使用自动数据库诊断监视器(ADDM)

• 说明和使用指导框架

• 设置预警阈值

• 使用服务器生成的预警

• 使用自动任务

数据库维护

通过复杂的Oracle DB 基础结构，可以方便地执行预先数据库维护，主要包括以下要素：

• 每个Oracle DB 中都有一个内置的资料档案库，即自动工作量资料档案库(AWR)。

Oracle DB 服务器会定期为所有重要统计信息及工作量信息创建快照，并将这些数据存储在AWR 中。用户可以对捕获的数据进行分析，也可以由数据库自己进行分析，

或者两者兼有。

• 数据库使用自动任务执行常规维护操作，如定期备份、刷新优化程序统计信息以及数据库健康检查。

被动数据库维护包括数据库健康检查器发现的严重错误和状态：

• 当出现无法自动解决并需要通知管理员的问题（如用完空间时）时，Oracle DB 服务器会提供服务器生成的预警。Oracle DB 服务器默认情况下会监视其本身，并发送预警通知发生的问题。预警不仅通知你出现问题，通常还会就如何解决报告的问题提供建议。

• 建议由一些指导生成，其中每个指导负责一个子系统。例如，有内存指导、段指导和SQL 指导。

查看预警历史记录

“Alert History（预警历史记录）”页会显示一个图表，图表上根据指定的时间段显示当前数据库的预警历史记录。出现预警表明可能存在问题：不是监视度量达到警告阈值或严重阈值，就是表明目标不再可用。单击“Alert History（预警历史记录）”页上列出的度量名称可获得每个预警的详细统计信息、图形和实际时间戳。此外，该页上还提供了一个输入预警相关注释（如解决方案信息）的位置。

术语

• 自动工作量资料档案库(AWR)：是用于数据搜集、分析和提供解决方案建议的基础结构

• AWR 基线：用于性能比较的一组AWR 快照

• 度量：累计统计数据中的更改率

• 统计信息：提供数据库和对象详细信息的数据集合

– 优化程序统计信息：供查询优化程序使用

– 数据库统计信息：用于了解性能

• 阈值：比较度量值时所根据的边界值

自动工作量资料档案库(AWR) 通过为内部Oracle 服务器组件提供服务来收集、处理、维护和使用性能统计信息，以便进行问题检测和自优化。活动会话历史记录(ASH) 是存储在AWR 中的最近会话活动的历史记录。

统计信息是一些数据的集合，可提供有关数据库及数据库中对象的详细信息。查询优化程序会使用优化程序统计信息为每个SQL 语句选择最佳执行计划。数据库统计信息则提供用于性能监视的信息。

AWR 快照包括数据库统计信息和度量、应用程序统计信息（事务处理量和响应时间）、操作系统统计信息及其它度量。AWR 基线是在一段时间内收集的一组AWR 快照。基线用于性能比较，这既可以是当前性能与基线的比较，也可以是一个基线与另一基线的比较。

在Oracle Database11g中，默认情况下会收集“系统移动窗口”基线。系统移动窗口基线是一组不断变化的快照，默认情况下包括最近八天的快照。收集了足够的数据并且计算了统计信息之后，此基线就会变为有效。默认情况下，统计信息计算被安排在每周六的午夜。

Oracle 优化程序：概览

Oracle 优化程序可确定最有效的执行计划，这是处理任何SQL 语句最重要的一步。

该优化程序可以：

• 对表达式和条件求值

• 使用对象和系统统计信息

• 确定如何访问数据

• 确定如何联接表

• 确定最有效的路径

优化程序是Oracle DB 的一部分，用于为SQL 语句创建执行计划。确定执行计划是处理任何SQL 语句的重要一步，会对执行时间产生重大影响。

执行计划是执行语句时按顺序执行的一系列操作。优化程序会考虑许多与被引用对象相关的以及与查询中所指定的条件相关的因素。优化程序所需的信息包括：

• 为系统（I/O、CPU 等）以及方案对象（行数、索引等）搜集的统计信息

• 字典中的信息

• WHERE子句限定符

• 开发人员提供的提示

使用诊断工具（如Enterprise Manager、EXPLAIN PLAN和SQL*Plus AUTOTRACE）时，可以看到优化程序选择的执行计划。

注：根据其功能的不同，Oracle 优化程序具有两个名称：“查询优化程序”和“自动优化程序”。

优化程序统计信息

优化程序统计信息：

• 是某一时间点的快照

• 每次重新启动实例后会变为永久信息

• 可自动收集

SQL> SELECT COUNT(*) FROM hr.employees;

COUNT(*)

----------

214

SQL> SELECT num_rows FROM dba_tables

2 WHERE owner='HR' AND table_name ='EMPLOYEES';

NUM_ROWS

----------

107

优化程序统计信息

优化程序统计信息包括表、列、索引和系统的统计信息。表和索引的统计信息存储在数据字典中。这些统计信息不提供实时数据。而是为优化程序提供数据存储和数据分发的正确统计快照，优化程序会使用该快照来决定如何访问数据。

收集的统计信息包括：

• 数据库块中表或索引的大小

• 行数

• 平均行大小和链计数（仅限表）

• 已删除叶行的高度和数量（仅限索引）

插入、删除和修改数据后，这些统计信息会发生变化。由于维护实时数据分发统计信息会对性能带来负面影响，因此，通过定期搜集表和索引的统计信息来更新这些统计信息。

优化程序统计信息由自动维护作业自动收集，该作业默认情况下在预定义的维护窗口中每日运行一次。系统统计信息是优化程序要使用的操作系统特征。不会自动收集这类统计信息。优化程序统计信息不同于AWR 快照中搜集的数据库性能统计信息。

使用“管理优化程序统计信息”页

要在Enterprise Manager中管理优化程序统计信息，请单击“Server（服务器）”选项卡，然后单击“Query Optimizer（查询优化程序）”部分下的“Manage Optimizer Statistics（管理优化程序统计信息）”。

在此页上，可以对统计信息执行下列任务：

• 手动搜集优化程序统计信息。

• 将优化程序统计信息还原到以前的某个时间点。选择的时间点必须位于优化程序统计信息保留期内，默认为30 天。

• 锁定优化程序统计信息以确保永远不覆盖某些对象的统计信息。如果在存在很有代表性的数据时计算了某个表的统计信息，而你又希望永远保留这些统计信息，那么该任务就很有帮助。如果锁定了统计信息，表的任何波动都不会影响这些统计信息。

• 取消对优化程序统计信息的锁定以撤消以前执行的锁定。

• 删除优化程序统计信息以删除统计信息。

最佳实践提示

可使用自动维护任务搜集优化程序统计信息。要启用搜集优化程序统计信息的任务，必须确保STATISTICS_LEVEL初始化参数设置为TYPICAL或ALL。

手动搜集优化程序统计信息

某些时候可能需要手动搜集统计信息，例如表的内容在两次自动搜集作业之间发生了很大的变化，以致于统计信息无法再准确表示表。对于24 小时内其大小变化超过10% 的大型表，通常需要此操作。

最佳实践提示：收集统计信息的频率应足够高，以保证表在两个收集期之间的变化率不超过10%。这可能需要手动收集统计信息或使用其它维护窗口。

可以使用Enterprise Manager或DBMS_STATS程序包手动收集统计信息。系统统计信息只能使用DBMS_STATS程序包来搜集。系统统计信息向查询优化程序描述了系统的硬件特征，例如I/O 以及CPU 性能和利用率。

选择“Gather OptimizerStatistics（搜集优化程序统计信息）”菜单会启动一个向导，使用该向导可以为要搜集优化程序统计信息的作业选择作用域、对象、选项和调度。该向导会在你指定的以下作用域提交DBMS_STATS.GATHER_*_STATS作业：表、方案或数据库。

在该向导中，将首选项设置为DBMS_STATS程序包所使用的默认值，并将此作业安排在你决定的时间运行。

不建议手动搜集常规统计信息，因为通过维护窗口搜集统计信息的效率更高，而且对用户的影响更小。如果自动作业已失败或已禁用，也可提交手动作业。

此外，还可以直接通过DBMS_STATS程序包搜集优化程序统计信息：

SQL> EXECdbms_stats.gather_table_stats('HR','EMPLOYEES');

SQL> SELECT num_rows FROMdba_tables WHERE owner='HR' AND table_name = 'EMPLOYEES';

NUM_ROWS

----------

214

注意，现在的行数正确地反映了截止到搜集统计信息时表中的行数。通过DBMS_STATS还可对整个方案甚至整个数据库启用手动收集统计信息。

除非工作量发生了显著变化，否则系统统计信息不会改变。因此，不需要频繁调整系统统计信息。DBMS_STATS.GATHER_SYSTEM_STATS过程会在指定时间段内收集系统统计信息，你也可以启动系统统计信息的搜集过程并执行另一个调用来停止搜集。

最佳实践提示：创建数据库时使用以下命令：

SQL> EXEC dbms_stats.gather_system_stats('NOWORKLOAD');

NOWORKLOAD选项会花费几分钟的时间（具体取决于数据库大小）来捕获I/O 特征的估计值，如平均读取查找时间和I/O 传输率。

用于搜集统计信息的首选项

可在各种级别调用DBMS_STATS.GATHER_*_STATS过程，以便搜集整个数据库或单个对象（比如表）的统计信息。调用GATHER_*_STATS过程时，通常可将其中的几个参数设置为默认值。提供的默认值适用于数据库中的多数对象，但对于某些对象或方案，需要更改这些默认值。在Oracle Database11g中，可以为单个对象、方案或数据库设置值（称为“首选项”），也可以使用全局级命令更改默认值，而无需为每个对象运行手动作业。

这些首选项指定了赋予搜集过程的参数。SET_*_PREFS过程为不属于SYS或SYSTEM的任何对象创建首选项值。对于应位于数据库范围内的任何参数，一般由DBA 为其设置全局首选项。这些过程适用于任何可设置为默认值的参数。

设置指定的首选项时，SET_DATATBASE_PREFS过程会循环访问数据库中的所有表和方案。SET_SCHEMA_PREFS循环访问指定方案中的表。SET_TABLE_PREFS为单个表设置首选项值。

无论是在数据库级、方案级还是在表级设置，所有对象首选项均保存在一个单个表中。

在方案级更改这些首选项会覆盖以前在表级设置的首选项。

在执行各种搜集过程时，这些过程会检索已经为每个对象设置的对象级首选项。你可以在DBA_TAB_STAT_PREFS视图中查看这些对象级首选项。未在对象级设置的所有首选项都会被设置为全局级首选项。通过对每个首选项调用DBMS_STATS.GET_PREFS过程，可以查看全局首选项。

你可以在表级、方案级、数据库级和全局级设置、获取、删除、导出和导入这些首选项。

首选项的值一般来说是按全局级向表级的顺序进行设置，最终将首选项应用于最小的组。

如下实例：

begin

dbms_stats.gather_table_stats(
ownname=> 'HR',
tabname=> 'EMPLOYEES' ,
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=> DBMS_STATS.AUTO_CASCADE,
degree=> null,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> 'AUTO',
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
dbms_stats.lock_table_stats(ownname=> 'HR', tabname=> 'EMPLOYEES' );
end;

Oracle Database 11g中的首选项：

• CASCADE，确定在搜集表统计信息的过程中是否收集索引统计信息。

• DEGREE，设置用于搜集统计信息的并行度。

• PUBLISH，用于确定是将统计信息发布到字典还是将其存储在专用区域中。这使得DBA 可以先验证统计信息，然后再使用PUBLISH_PENDING_STATS过程将其发布到数据字典。

• STALE_PERCENT用于确定判断对象是否具有过时统计信息所依据的阈值级别。该值是自上次搜集统计信息以来已修改的行数百分比。

exec dbms_stats.set_table_prefs('SH','SALES','STALE_PERCENT ','13');

示例仅将SH.SALES的默认值10%更改为13%。

• INCREMENTAL，以增量方式搜集分区表的全局统计信息。

• METHOD_OPT，确定列和用于搜集列统计信息的直方图参数。

• GRANULARITY，确定收集统计信息的粒度（仅当表为分区表时才适用）。

• NO_INVALIDATE，确定是否使游标失效。

• ESTIMATE_PERCENT，确定为获得有效的统计信息而采样的行数。它是表中行数的百分比。

使用DBMS_STATS.DELETE_*_PREFS过程，可在表级、方案级和数据库级删除首选项。

使用DBMS_STATS.RESET_PARAM_DEFAULTS过程，可将全局首选项重置为建议值。

自动工作量资料档案库(AWR)

• 内置的性能信息资料档案库

• 每60 分钟获取一次数据库度量快照，保留期为八天

• 所有自我管理功能的基础

自动工作量资料档案库(AWR)

AWR 是为Oracle Database11g组件提供服务的基础结构，借助它可以收集、维护和利用统计信息进行问题检测和自优化。可将此基础结构视为数据库统计信息、度量等的数据仓库。

数据库（默认情况下）每60 分钟从SGA 中自动捕获一次统计信息，然后将其以快照形式存储在AWR 中。这些快照通过一个名为易管理性监视器(MMON) 的后台进程存储在磁盘上。默认情况下，快照会保留八天。你可以修改快照时间间隔和保留间隔。

AWR 中包含数百个表，所有这些表均属于SYSMAN方案且存储在SYSAUX表空间中。

Oracle 建议仅使用Enterprise Manager或DBMS_WORKLOAD_REPOSITORY程序包访问资料档案库，对AWR 进行操作。不支持直接对资料档案库表进行DML 操作。

AWR 基础结构

AWR 基础结构由两个主要部分组成：

• 一个内存中统计信息收集工具，Oracle Database 11g组件使用它来收集统计信息。出于性能方面的考虑，这些统计信息存储在内存中。可以通过动态性能(V$) 视图访问存储在内存中的统计信息。

• 代表该工具持久部分的AWR 快照。AWR 快照可以通过数据字典视图和EnterpriseManager Database Control 来访问。

出于以下几方面的考虑，统计信息存储在持久存储中：

• 实例崩溃后统计信息需要仍然可用。

• 某些分析需要使用历史记录数据进行基线比较。

• 可能会发生内存溢出。当旧统计信息因内存不足而被新统计信息替换时，被替换的数据可以存储起来供以后使用。

内存版本的统计信息定期通过MMON后台进程转移到磁盘上。使用AWR 时，Oracle DB可自动捕获历史统计信息，而不需要DBA 进行干预。

AWR 基线

AWR 基线是一个AWR 快照集。通常是在AWR 中标记和保留的某个重要时段的快照数据的集合。基线是用一对快照定义的；这两个快照通过其快照序列号(snap_id) 或起始和结束时间进行标识。每个快照集都有起始和结束快照，并包含位于这两个快照之间的所有快照。快照集用于保留快照数据。因此，默认情况下，在删除快照集前，属于快照集的快照会一直保留。可以根据快照要保留的天数设置一个过期值。

基线通过用户提供的名称来标识。执行CREATE_BASELINE过程可基于一个快照集创建基线，并可以指定名称和一对快照标识符。对于新创建的基线，会为其分配一个在数据库生命周期内唯一的基线标识符。通常是基于过去有代表性的时段建立快照集，以用于与当前系统行为进行比较。你也可以在Database Control 中使用基线来设置基于阈值的预警。可以使用该过程的过期参数设置以天数表示的过期时间。默认值为NULL，表示“永不过期”。

可从DBA_HIST_SNAPSHOT或Database Control 中直接获取snap_id。