oracle之三存储库及顾问框架

AWR存储库及顾问框架(PPT-I-349-360)

14.1 Oracle数据库采样ASH和AWR。

1） ASH(Active Session History)

ASH收集的是活动会话的样本数据，Oracle的会话状态可以分为3种，1）on cpu,2)waiting, 3)idle. 前两种都是活动会话（Active Session）
而v$session包括了所有的（三种状态）当前会话，它每秒采样一次，那么ASH就以v$session为数据源，只记录活动会话信息，不活动的会话
不会记录，记录数据在SGA缓冲区中。最终将ASH信息存入了AWR库。有关ASH数据采集由后台进程MMNL来完成的。

生成ASH报告：

SQL> @/u01/oracle/rdbms/admin/ashrpt.sql

2）AWR（Automatic Workload Repository)

AWR架构是从oracle 10g开始的，它以快照形式自动收集并保存和数据库有关性能统计数据，它的前身Statspack，AWR的作用是提供一个时间
段内整个系统资源使用情况的报告，它存储重要的累计统计信息。通过这个报告，我们就可以了解一个系统在某个时间段内的运行情况。

*考点：
AWR工作时是由后台进程MMON负责，于每1小时生成一个内存统计的快照,并写入磁盘上的sysaux表空间，快照不能移动到其他位置，快照也会
作为ADDM的原始数据，缺省情况下，Oracle将快照保留8天。

生成AWR报告，注意：必须是从起始快照至结束快照之间（一个连续时间段)的报告。

SQL> @/u01/oracle/rdbms/admin/awrrpt.sql

14.2 相关的一些概念:

1） AWR收集数据库有关性能信息：它是新的数据库自动调优机制的核心，结果是以每小时一次快照的形式将关键数据的写入SYSAUX表空间。

所谓关键数据包括：

*基本统计数据，也是v$sysstat和v$sesstat视图中收集的系统和会话的统计信息；
*SQL统计数据，分别按执行时间、cpu时间、执行次数等标准来统计
*对象的统计信息，
*时间模型统计信息，告知每个数据库活动要花多长时间。(在v$sys_time_model和v$sess_time_model视图中查看)；
*等待统计数据（来自V$session视图中的几个新添加的字段）
*ASH统计信息，包含近期会话活动的历史记录
*数据库特性利用的统计数据
*各种管理顾问会话的结果，如ADDM、Segment Advisor、Sql Access Adivisor等
*操作系统的统计数据，如I/O和内存的利用率

2） AWR度量（metric)： 两个或多个统计数据综合的结果。它是衡量累计性能统计数据变化率的统计指标。度量有两个主要作用:
1）几乎所有的advisor都使用metrics诊断性能问题并给出调优建议。2）metrics是服务器产生预警特性的基础。

3）AWR基准线（baseline)。是一组快照，通过把当前性能与基本阶段的性能进行比较，可以检验数据库运行的优劣。
基线可以被无限期保留，除非你设定删除，因此基线总是拿来比较性能，而不像快照有retention限制。

Oracle数据库中包含了二种类型的基线：

第一类，移动窗口基线(Moving Window Baseline)
Oracle设计移动窗口基线是为了得到基于基线的度量。只有一个移动窗口基线，即Oracle内置的system moving baseline, 它使用AWR
保留期内存在的所有AWR数据。可以自动产生并调整，即自适应阈值(Adaptive Thresholds)，（见后叙），system moving baseline的
默认窗口大小必须小于等于当前AWR保留的时间，默认为8天。如果你要使用自适应阈值，可以考虑使用更大的移动窗口，例如30天，可
以更精确地计算出阈值。若你需要增大移动窗口的大小，首先需要增加AWR的保留时间。

第二类，静态基准线(Fixed Baselines)，又分为两种

*固定基线（Static）（针对过去时）
指定的历史上的一个固定的、连续的时间范围。在创建固定基线以前，要慎重考虑这个时间段，因为基线代表了一个理想状态的系统状态。
之后，你可以用这个基线和其他基线或者某个时间范围内的快照来分析性能上的退化情况。

*基线模板(Baseline Templates)（针对将来时）
你可以创建一个基线，作为未来一个时间连续的时间段可以使用的基线模板。有两种类型的基线模板：单一（single）的和重复的（Repeating） 。
你可以为未来一个单独的连续时间段的（将会产生快照）创建单一基线模板。如果你要提前准备获取一个未来的时间段，这个技术会很有用处。
例如，你安排好要在周末进行一个系统测试，并准备获取AWR数据，这种情况下，你可以创建一个单一基线模板，用以在测试时自动获取该时间
范围内的数据。你也可以使用重复基线模板来创建或者删除一个重复的时间计划，当你想自动获取一个连续的时间范围，这将很有用。例如，你可
能希望在一个月里的每周一早晨获取AWR数据，这种情况下，你可以创建一个重复基线模板来自动为每个周一创建基线，并且在设置了过期时间
(例如一个月)后，自动删除过期的基线。

建立一个singe基线模板的例子（考题）

begin
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(
START_TIME => TO_TIMESTAMP(SYSDATE+2),
END_TIME => TO_TIMESTAMP(SYSDATE+10),
BASELINE_NAME => 'Mybase4',
TEMPLATE_NAME => 'Mytemp4',
EXPIRATION => NULL);
end;
/

建立一个repeat基线模板的例子

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
day_of_week => 'monday', hour_in_day => 17,
duration => 3, expiration => 30,
start_time => '2014-01-01 17:00:00',
end_time => '2014-12-31 20:00:00',
baseline_name_prefix => 'baseline_2014_mondays_',
template_name => 'template_2014_mondays');
END;
/

4）自适应阈值(Adaptive Thresholds)

自适应阈值可以帮你以最低的开销来监控和检测性能问题。自适应阈值只能从移动窗口基线捕获到的度量值里得到的统计信息，
为系统metric自动设置预警和关键预警(warning and critical alert)的阈值。这些统计信息每周会重新生成，并可能由于系统性
能随着时间变化改变，而产生新的阈值。

打个比方，很多数据库白天是一个OLTP系统，而到晚上需要执行一些批量进程(例如生成报表)。每个事务响应时间的性能度量
对检测OLTP的性能退化问题在白天可能很有用，但是这个阈值常常对于批量工作来说会太低，而频繁触发报警。自适应阈值能
检测到这样的工作量模式，并自动为白天和夜里设置不同的阈值。

自适应阈值的类型有两种：
1）最大值的百分比：阈值以最大值的百分比倍数的方式设计，

2）重要性级别：阈值被设为一个统计学中的百分位来观察基于移动窗口基线数据的阈值以上的值，来体现异常程度。百分位能
　指定为以下几种：高(0.95)，100个中只有5个能超过这个值；非常高(0.99)：100个中只有1个能超过这个值；
　严重的(0.999)：1000个中只有1个能超过这个值；极端的(0.9999)：10000个里只有1个能超过这个值。

当一个系统以高峰期工作量来设计的，并且你希望在当前工作量接近或超过先前的高值时触发报警，最大值百分比阈值将非常有用。
例如，每秒产生redo量的度量就是个典型的例子。

重要性级别阈值在以下情况很有用：当系统运行正常时表现得很稳定，但当性能变差时可能会在一个大范围内波动。例如，每个事
务的响应时间的度量在一个优化过的OLTP系统上将表现平稳，但当性能问题凸显时，可能会波动很大。采用重要性级别阈值，当环
境产生不正常的度量值和系统性能时触发报警。

5） 与AWR有关参数
SQL> show parameter statistics_level

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL>

若参数STATISTICS_LEVEL设置为TYPICAL或ALL将默认启用AWR来采集数据库统计信息。ALL参数收集信息最全，参数的默认值是TYPICAL，

*考点：
如果STATISTICS_LEVEL设为BASIC，将禁用自动收集快照和运行顾问。但你仍可以通过DBMS_WORKLOAD_REPOSITORY包来手动获得AWR统计信息。

14.3 ADDM (Automatic Database Dianostic Monitor)

14.3.1 概念
ADDM是一个顾问系统，它能够自动的完成对数据库的一些优化建议，它是根据AWR每小时采集的数据，看看有没有性能问题，
如果有就给出建议调用各个相关的指导（Advisor)，比如建议做SQL Tuning Advisor, 或SQL Access Advisor，或者建议创建相关索引。

ADDM的另一个特点是自动生成的ADDM报告，默认它会包括当前快照和前一个快照的时间段，如果想要ADDM跨越更长的时间段，
也可以手动调用ADDM生成包括任意两个快照间的时间段。

与ADDM有关的参数：
control_management_pack_access 缺省是DIAGNOSTIC+TUNING，如果设成NONE，则ADDM关闭。

*考点：收集AWR快照时自动运行ADDM，根据情况决定是否产生报告，也可以根据需要手动生成ADDM报告，并将其结果保存到AWR中。
默认情况下ADDM报告保存30天。

ADDM 报告生成：
SQL> @/u01/oracle/rdbms/admin/addmrpt.sql

例：
1）在OEM中了解最新AWR快照号和ADDM报告

2）模拟业务高峰

2.1）建立一个大表和小表，分别叫bigtable, smalltable.
scott:
create table bigtable (id number(10),name varchar2(100));
create table smalltable (id number(10),name varchar2(100));

2.2）大表中插入一百万行记录
begin
for i in 1..1000000 loop
insert into bigtable values(i,'test'||i);
if mod(i, 100)=0 then
commit;
end if;
end loop;
end;
/

2.3）小表中插入1000条记录
begin
for i in 1..1000 loop
insert into smalltable values(i,'test'||i);
if mod(i, 100)=0 then
commit;
end if;
end loop;
commit;
end;
/

3）清除shared pool，buffer cache
sys:
alter system flush shared_pool;
alter system flush buffer_cache;

scott:
执行缺少统计信息和索引的关联查询

select count(*) from smalltable a where a.id=(select b.id from bigtable b where b.id=a.id); //需要1.5--2分钟左右

4）手工即时生成AWR快照
sys:
exec dbms_workload_repository.create_snapshot;

查看ADDM

OEM：Server-->Advisor Central-->Advisor Task Results（画面下方）已经显示了一个ADDM结果，

我们可以尝试单独生成一个ADDM报告。方法：

Server-->Advisor Central--> ADDM-->Run ADDM to analyze past performance

结合图型高峰段选择Period Start Time和Period End Time-->ok

可以看到报告生成的名称是task_nn,然后有Impact(%)，Finding，Occurrences等信息
进入第一行，即Finding是Top SQL by DB Time,里面是Recommendations，

也可以通过SQL Tuning Advisor去捕获Top Sessions和Activity 捕获一组你希望的SQL Tuning Set(STS)，以备进一步分析。

14.3.2 其他顾问 （Advisor）

顾问就是通过分析AWR捕获的数据，提出改进性能的建议。ADDM本身就是顾问，它的报告里还会建议你找其他的顾问。具体病症还要看专科。

Oracle 11g 主要的一些Advisor：(PPT-II-382)

1）SQL Advisors 其中包括：

SQL Tuning Advisor: 对单个SQL语句提供调优建议,生成sql profile，sql语句执行路径分析，sql语句结构分析
SQL Access Advisor: 评估SQL语句对数据库负荷的影响，提供建议。如index,partition, materializer view等
SQL Repair Advisor: 对可能的oracle内部错误，如ORA-600需要的patch(补丁）提出建议

2）Memory Advisors：可以对Oraclen内存结构（SGA+PGA）做自动调整，以适应数据库在不同时间段的工作量变化。

3）Segment Advisor: 提供段收缩命令（shrink)。释放未使用的空间。

4）Undo Advisor: 为undo表空间的大小提供建议，如避免快照太旧的问题。从v$undostat取数据。

5）MTTR Advisor：为实例恢复的时间提供建议。

14.3.3 关于Advisor的API's程序包

首先是DBMS_ADVISOR: 不过，它只是顾问管理中一部分包的套件，有一些Advisor有它们自己的包，如：

Automatic Database Diagnostic Monitor (DBMS_ADDM)
SQL Performance Analyzer (DBMS_SQLPA)
SQL Repair Advisor (DBMS_SQLDIAG)
SQL Tuning Advisor (DBMS_SQLTUNE)

14.3.5 例：通过OEM观察：如何使Segment Advisor完成段重组。

接续14.3.1的实验，

SQL>delete bigtable where rownum<=900000;
SQL>commit;

scott. bigtable表产生了较高的HWM，delete命令使该表没有任何记录，但没有降低HWM。

OEM：Server-->Advisor Central-->Segment Advisor-->Tablespace-->进入顾问评估的四个步骤，在第二步骤选择add users 表空间（可以看到users当前的表空间used%）。-->Submit

这时进入Segment Advisor的分析状态，它的数据源是AWR，Refresh后Status：COMPLETED, 然后点击这个任务的name看看是否有Recommendations，如果有，点击Recommendation Detail 可以进入实施（Implement）对于users整个表空间的段重组。

选择 Recommendation项下的 Shrink可有一下选择：

Compact Segments and Release Space（压缩并释放空间）

Compact Segments（压缩）

然后选择Implement，最后观察users表空间used%棒图大大降低了。说明HWM下降了，unused block已经回收了。

14.4 优化器和优化器统计信息

14.4.1 概念：

优化器(Optimizer),也叫查询优化器，它的功能是为了对DML操作寻找最理想的执行计划。早期的RBO（Rule_Based Optimizer)已经淘汰,
现在Oracle使用CBO（Cost_Based Optimizer）,基于成本的优化器。

CBO优化器得出执行计划，依据的是优化器统计数据(optimizer statistics), 而优化器统计数据又是怎么产生的？由DBMS_STATS程序包派生。

如果在查询中，你访问的对象（表、索引），如果已经有了统计信息，那么正好优化器会很快的得到一个好执行计划，如果缺少统计信息，
优化器不得不使用动态采样技术在运行时收集统计信息。这可能是仓促的，不准确的，即得到一个不好的执行计划。

SQL> show parameter optimizer_dynamic_sampling

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2

14.4.2 CBO优化器的工作模式

CBO优化器严重依赖收集对象的统计信息

通常情况下，对一个马上要执行sql语句，CBO优化器会根据已有统计信息预估一个相应的执行计划，如果统计数据缺失、陈旧、不准确，
都可能得到一个较差的执行计划。为了得到一个较优的执行计划，对于TOP sql 可以使用SQL Tuning Advisor. 目的是让Oracle有充足
的时间细致的分析sql语句, 以便给出以下四个方面的建议(RECOMMEND)：

1）统计信息分析： 是否需要重新收集统计信息，
2）SQL profile分析： 可能产生SQL profile，是否可以利用 ，
3）访问路径分析： 使用全表扫描还是索引，
4）SQL结构分析： 是否需要重构SQL语句

14.4.3 自动和手动收集optimizer statistics

Oracle通过自动维护任务（Automated Maintenance Tasks）框架自动收集optimizer statistics，所谓“自动任务”是将任务纳入资源
管理的中，由Automatic Statistics Gathering作业来完成，好处是可以被Window定时激活，同时被限制过度使用资源（25%以下）。
定时的收集数据库中所有的objects信息，表、列、索引，系统IO等统计信息都属于optimizer statistics范畴，这些信息都存入了数据字典。

Oracle 推荐optimizer statistics使用“自动收集“。此外，特别情况下也可以“手动收集”，即Manual Statistics，使用
DBMS_STATS包随时可以进行optimizer statistics的收集。

测试手工收集scott下所有对象的统计信息

OEM->Server->Query Optimizer->Manage Optimizer Statistics->Gather Optimizer Statistics

查看信息：

SQL> select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where owner='SCOTT';

SQL> select job_name,LAST_START_DATE from dba_scheduler_jobs;

SQL> select column_name,num_distinct from dba_tab_col_statistics where table_name='EMP';

COLUMN_NAME NUM_DISTINCT
------------------------------ ------------
DEPTNO 3
COMM 4
SAL 12
HIREDATE 13
MGR 6
JOB 5
ENAME 14
EMPNO 14

一般情况下，一旦一个object的统计信息新版本产生，老版本统计信息就被自动保存起来了,当然，oracle 也给你提供了灵活的选择，这就带来了下面的概念：

考点：
1）Restoring Previous Versions of Statistics 恢复老版本的统计信息。
2）Locking Statistics 对于某些schema（如table)可以锁定,就是不产生新版本统计信息了，想想静态表（只读表）可能是需要的。
3）Pending Statistics 收集新版本信息，但是挂起统计结果，暂时不用。
例：EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'EMP', 'PUBLISH', 'false'); //收集EMP表的新版本统计信息，但被挂起了，暂不使用。
4）Automatic Optimizer Statistics Collection过程正在收集所有objects信息，这时Window关闭了，会出现什么状况？

第一，job自动停止，第二，已收集好的objects保存进数据字典，第三，还未收集的objects在下个Window到来时继续收集。
当然也可以通过指定属性改变以上状况，使得当Window结束后未完成的job继续进行，直至job结束。

14.4.4 管理Autotask(PPT-I-362-364)

关于Autotask的三个job：

通过OEM->Server->Oracle Scheduler->Automated Maintenance Tasks查看。

Automatic Optimizer Statistics Collection，
Automatic Segment Advisor，
Automatic SQL Tuning Advisor。

14.4.5 预定义维护窗口
有7种预定义窗口，通过OEM->Server->Oracle Scheduler->Window Groups可以查看，当一个维护窗口打开时，
数据库激活DEFAULT_MAINTENANCE_PLAN，3个自动维护任务运行在ORA$AUTOTASK_SUB_PLAN子计划之下。

14.4.6 Autotask使用的程序包

Oracle不给3种自动维护任务分配永久的Scheduler作业，所以不能用DBMS_SCHEDULER程序包管理这些任务。
如果想修改Autotask必须使用DBMS_AUTO_TASK_ADMIN程序包。

一个反复出现的考点是如何实施顾问产生的推荐

作为一般原则，advisor的RECOMMEND(推荐）是不会自动IMPLEMENT(实施）的，但是自动维护任务中的
Automatic SQL Tuning Advisor产生的SQL PROFILE可以是个例外，通过设置Automatic Implementation
of SQL Profiles为Yes，那么当Oracle分析得出SQL PROFILE可以为你的PLAN带来3倍以上效率时，会自动
IMPLEMENT这个SQL PROFILE。而其他RECOMMEND都必须要DBA手动IMPLEMENT。

14.4.7 监控Autotask

通过两个视图：dba_autotask_clien和dba_autotask_operation

SQL> select client_name,status,attributes,window_group,service_name from dba_autotask_client;
SQL> select client_name,operation_name from dba_autotask_operation;