Oracle性能管理；AWR报告(自动化作业任务)

一、AWR报告

分析工具AWR(Automatic Workload Repository，自动负载仓库)：默认每小时生成一份快照，保留8天，第二次快照和第一份快照相减对比出来。有主动维护：定期备份、自动刷新自动生成AWR报告；被动维护：告警日志非常危险且无法自动修复的错误，sql顾问和段顾问会自动收集oracle的一些awr报告。性能调优主要在I/O设备竞争、资源竞争、应用代码问题、网络瓶颈四个方面。

1、术语介绍

(1)、统计信息，有Optimizer statistics和database statistics两种

(2)、Metric：统计信息中心的变化率

(3)、Threshold：用来对比度量值的边界值

(4)、snapshot：特定时间点捕获到一系列性能统计信息

(5)、Automatic Workload Repository(AWR)：用于数据收集、分析和解决方案建议的基础信息仓库。

(6)、AWR Baseline：一组AWR快照，用于性能比较

(7)、Active Session History(ASH)：存储在AWR中的历史会话活动信息，对很短时间内的数据库运行情况进行汇总收集。

@$ORACLE_HOME/rdbms/admin/ashrpt.sql;  //这个是单实例，ashrpti.sql指定实例或rac

(8)、Automatic Database Diagnostic Monitor(ADDM)：用来监视实例，检测瓶颈点(自动在每个AWR快照后运行(也可以手动生成)，每次AWR快照生成后，ADDM自动分析最近的两份快照，主动监控实例和检测瓶颈点)，下面是手动生成的方法，采用的是sql脚本的方法，系统包比较复杂。

 @?/rdbms/admin/addmrpt.sql

 

2、管理baseline

1、生成baseline

select snap_id,startup_time,begin_interval_time,end_interval_time from dba_hist_snapshot order by snap_id;  //查看snap_id,开始时间是begin_interval_time
begin
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
start_snap_id => 616,
end_snap_id   => 617,
baseline_name => 'oltp_peakload_bl',
expiration    => 10); 
end;
/

2、删除baseline

begin
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(
baseline_name => 'oltp_peakload_bl',
cascade=> true);
end;
/
//cascade表示同时删除快照

 

3、管理AWR报告

采集等级控制受参数statistics_level影响，basic：禁用多数的ADDM，自动收集优化信息功能被关闭；typical：建议值，也是默认值；all：收集所有可能的优化信息。

(1)、生成AWR报告步骤

@?/rdbms/admin/awrrpt.sql;   //生成awr报告
//输入报告类型：html
Enter value for report_type:html
//提示num_days收集几天的报告信息
Enter value for num_days:1
//提示begin_snap抓取时间范围对应的开始快照id，如果快照很少可以手动生成快照
Enter value for begin_snap:613
//提示end_snap抓取时间范围对应的结束快照id
Enter value for end_snap:614
//提示report_name输出报告名称
Enter value for report_name:awrrpt20221204.html
//一般情况下文件保存当前目录下，可通过!pwd查看

(2)、生成快照和删除快照的方法

begin
dbms_workload_repository.create_snapshot();
end;
/
begin
dbms_workload_repository.drop_snapshot_range(
low_snap_id=>127,
high_snap_id=>127,
dbid=3344514553
);
end;
/

(3)、修改AWR配置

begin
dbms_workload_repository.modify_snapshot_settings(
retention=>9*24*60,
interval=>60,
topnsql=>50
);
end;
/
//retention表示快照保留10天；interval表示每30分钟采集一次；topnsql表示统计top50的sql，statistics=Typical下的默认是30，all是100。

 

4、AWR报告分析(将awr报告下载到本地并用Google浏览器打开)

参考连接：Oracle AWR报告详细分析

(1)、WORKLOAD REPOSITORY report for

数据库的基本信息，数据库名称DB Name、版本Release、最近一次启动时间Startup Time。

DB Time= cpu time + all of nonidle wait event time，db time记录服务器花在数据库运算和等待(非空闲等待)上的时间，如果db time远远小于Elapsed时间，说明数据库比较空闲。

 

Elapsed是快照的时间间隔，DB Time是60min内使用的CPU时间，CPU数是2，所以如果DB Time大于60*2=120min，说明CPU负载高。

(2)、Report Summary

Instance Efficiency Percentages (Target 100%)：实例有效占比(目标100%)

Buffer Nowait：在内存获取数据的未等待比例，在缓冲区中获取Buffer的未等待比例。一般要大于99%，否则可能存在争用。

Redo Nowait：表示LOG缓冲区获得BUFFER的未等待比例。

Buffer Hit：进程从内存中找到数据块的比例，监视这个值是否发生重大变化比这个值本身更重要。命中率突然变大，可以检查top buffer get SQL，查看导致大量逻辑读的语句和索引；命中率突然变小，可以检查top physical reads SQL。

In-memory Sort：在内存中排序的比率，如果过低说明有大量的排序在临时表空间中进行。

Library hit：Oracle从Library Cache中检索到一个解析过的SQL或PL/SQL语句的比例。低的Library Hit ratio会导致过多的解析，增加CPU消耗，降低性能。STATEMENT在共享区的命中率，应保持在95%以上，否则要考虑加大共享池、使用绑定变量、修改cursor_sharing等参数。

Soft Parse：软解析的百分比，近似当做sql在共享区的命中率，太低则需要使用绑定变量。

Latch Hit：Latch是一种保护内存结构的锁。要确保大于99%，否则意味Share Pool Latch争用。

Parse CPU to  Parse Elapsed：解析实际运行时间/(解析实际运行时间+解析中等待资源时间)，越高越好。

Non-Parse CPU：SQL实际运行时间/(SQL实际运行时间+SQL解析时间)，太低表示解析消耗时间过多。

Execute to Parse：是语句执行与分析的比例。如果SQL重用率高，则这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。

 

Top 10 Foreground Events by Total Wait Time：按总等待时间排序的前10个前台事件

Event是事件名称；Waits是等待事件发生次数；Total Wait Time(sec)是等待事件消耗的总计时间；Avg Wait是该等待事件平均等待时间。

(3)、main report(sql statistics)

SQL ordered by Elapsed Time：SQL按照运行时间排序

Elapsed Time：SQL运行的总时长

Executions：SQL语句在监控范围内的执行次数总计

Elapsed Time per Exec：执行一次sql的平均时间

%Total：是SQL的Elapsed Time时间占数据库总时间的百分比

SQL Text：是简略的sql语句。

点击SQL_ID查看详细的SQL语句。

 

二、性能优化方式

1、SQL调优集

(1)、创建索引

a、尽量避免全表扫描，首先考虑在where及order by涉及的列上建立索引。

b、在经常需要检索的字段上创建索引，个表的索引数最好不要超过6个

c、及时调整索引不生效的情况

d、避免在索引上使用计算，避免在索引列使用NOT、IS NULL或IS NOT NULL

 

(2)、尽量将多条SQL语句压缩到一句SQL中：每次执行SQL都要建立网络连接、SQL语句的查询优化、发送执行结果

(3)、用Where子句替换Having：Having会在检索出所有记录之后对结果集进行过滤，这个处理需要排序、总计等操作。

(4)、用TRUNCATE替代DELETE：TRUNCATE是全表删除，是DDL，不能再被恢复；DELETE是DML，回滚段用来存放可以被恢复的信息，如果没有commit，Oracle会将数据恢复到删除之前的状态。

(5)、使用表的别名：SQL连接多个表时。使用表的别名把别名前缀与每个列名上，减少解析时间和列名歧义引起的语法错误

(6)、减少查询字段，不使用" * "

 

2、硬解析、软解析

(1)、执行SQL语句时，要经历几个步骤：

a、语法检查

b、语义检查

c、解析SQL语句

Oracle使用内部哈希算法获取sql的哈希值，然后查找该哈希值是够存在于库缓存中(共享池的库高速缓存)。若存在，将该sql与缓存中的sql进行比较，若相同则使用现有的分析树和执行计划，省略优化器的相关工作，这是软解析的过程；若不存在哈希值或不相同，优化器将执行创建解析树并生成执行计划，此过程称为硬解析。

d、执行SQL并返回结果

 

(2)、绑定变量

select * from scott.emp where empno =7900;

select * from scott.emp where empno = :emp_no;

第一条语句：查询员工编号是 7900 的员工，Oracle经过第一次分析编译后执行，下次要查询编号为 7902 和 7934 员工信息时，Oracle会再将这句SQL分析编译，然后再执行。

第二条语句：定义变量emp_no，将 7900 赋给变量，第一次经过编译后，把查询计划存储在一个共享池中，后执行，下次查询编号为 7902 和 7934 员工信息时，不会再进行编译。

使用绑定变量减少编译次数(硬编译->软编译)：要求变量名称，数据类型以及长度是一直的，减少编译次数可以使用较少的时间，而且可以减少锁存时间，降低锁存频率。

select * from scott.emp where empno = 7900;
select * from scott.emp where empno = 7902;
select * from scott.emp where empno = 7934;

select sql_text from v$sql where sql_text like 'select * from scott.emp where empno%'; //存储在shared pool里保存最近执行的sql，或者获取硬解析的次数，以上三条数据都在

var emp_no number;  //声明变量
exec :emp_no :=7900;
select * from scott.emp where empno = :emp_no;
exec :emp_no :=7902;
select * from scott.emp where empno = :emp_no;
exec :emp_no :=7934;
select * from scott.emp where empno = :emp_no;
select sql_text from v$sql where sql_text like 'select * from scott.emp where empno%';  //只有一条sql

 

(3)、更改参数cursor_sharing

cursor_sharing = {SIMILAR | EXACT | FORCE}

EXACT：要求SQL语句完全相同才会重用，否则会被重新执行硬解析操作。

SIMILAR：某条SQL语句的谓词条件可能会影响他的执行计划，才会重新解析。

Force：任何情况下，无条件重用SQL

show parameter cursor_sharing; 
alter session set sql_trace=true;  //sql_trace是用于进行sql语句追踪的工具

//当cursor_sharing参数是exact
alter session set cursor_sharing=exact;  //修改参数
select * from scott.emp where empno = 7900;
select * from scott.emp where empno = 7902;
select * from scott.emp where empno = 7934;
select sql_text from v$sql where sql_text like 'select * from emp where empno%';  //有三条数据

//当cursor_sharing参数是similar
alter system flush share_pool;  //清理共享池，测试环境使用，生产环境不使用
alter session set cursor_sharing=similar;
select * from scott.emp where empno = 7900;
select * from scott.emp where empno = 7902;
select * from scott.emp where empno = 7934;
select sql_text from v$sql where sql_text like 'select * from emp where empno%';   //显示出select * from scott.emp where empno= :"SYS_B_0"，系统自己绑定一个变量

//当cursor_sharing参数是Force
alter system flush share_pool;
alter session set cursor_sharing=force;
select * from scott.emp where empno = 7900;
select * from scott.emp where empno like  '7902';
select sql_text from v$sql where sql_text like 'select * from emp where empno%';   //显示出select * from scott.emp where empno= :"SYS_B_0"，select * from scott.emp where empno like :'SYS_B_0'

 

3、资源管理

 (1)、dbms_resource_manager包：用于维护资源计划，资源使用组和资源计划指令，以下是功能列表

资源消费组：具有相似资源需求的用户组或会话；

资源计划：在资源消费组之间分配资源的计划，同一时间内，一个数据库只能计划一个资源计划；

资源计划指令：指定如何在资源消费组之间分配资源。

spool /tmp/e.txt;   //将包输出到文件里
desc dbms_resource_manager;
spool off;

dbms_resource_manager.clear_pending_area();  //清除资源管理器
dbms_resource_manager.create_pending_area();  //创建pending内存区，该内存区用于改变资源管理对象
dbms_resource_manager.validate_pending_area();  //校验资源管理器的改变
dbms_resource_manager.submit_pending_area();  //提交资源管理器的改变

dbms_resource_manager.create_plan(plan,comment,cpu_mth);  //创建资源计划，plan是指定资源计划名，comment指定用户注释信息
dbms_resource_manager.create_cdb_plan(plan,comment);  //创建cdb资源计划
dbms_resource_manager.delete_plan(plan);  //删除资源计划
dbms_resource_manager.delete_cdb_plan(plan);  //删除cdb资源计划
dbms_resource_manager.update_plan(plan);  //更新资源计划的定义
dbms_resource_manager.update_cdb_plan(plan);  //更新cdb资源计划的定义

dbms_resource_manager.create_plan_directive(plan,group_or_subplan,comment,cpu_mth,mgmt_mth,active_sess_pool_mth,parallel_degree_limit_mth,max_idle_blocker_time);  
//建立资源计划指令,mgmt_mth分配cpu使用率，mth是p1、p2...；active_sess_pool_mth是限制活动会话数量；parallel_degree_limit_mth是任何操作的并行限制；max_idle_blocker_time是最大执行时间
dbms_resource_manager.create_cdb_plan_directive(plan,pluggable_database,comment);  ////建立cdb资源计划指令
dbms_resource_manager.delete_plan_directive(plan);  //删除资源计划指令
dbms_resource_manager.delete_cdb_plan_directive(plan,pluggable_database);  //删除cdb资源计划指令
dbms_resource_manager.update_plan_directive(plan,pluggable_database);  //更新资源计划指令
dbms_resource_manager.update_cdb_plan_directive(plan,pluggable_database);  //更新cdb资源计划指令

dbms_resource_manager.create_consumer_group(consumer_group,comment,cpu_mth);  //建立资源使用组，consumer_group指定资源使用组名
dbms_resource_manager.delete_consumer_group(consumer_group);  //删除资源使用组
dbms_resource_manager.update_consumer_group(consumer_group);  //更新资源使用组信息

(2)、实例(还未建立成功)

show parameter resource;
exec dbms_resource_manager.clear_pending_area();  //清理内存区
exec dbms_resource_manager.create_pending_area();  //建立内存区

exec dbms_resource_manager.create_plan('TestPlan','测试资源计划');  //创建资源计划
exec dbms_resource_manager.create_consumer_group('TestGroup','测试资源使用组');  //创建资源使用组
exec dbms_resource_manager.create_plan_directive(plan=>'TestPlan',group_or_subplan=>'TestGroup',comment=>'测试资源组',cpu_p1=>40,parallel_degree_limit_p1=>3); //建立资源计划指令
exec dbms_resource_manager_privs.grant_switch_consumer_group('scott','TestGroup',false);  //分配用户到资源使用组
alter system set resource_manager_plan='TestPlan' scope=momory
exec dbms_resource_manager.validate_pending_area();  //验证内存区，验证通过即可提交，不许清除pending内存区重建资源对象
exec dbms_resource_manager.submit_pending_area(); //提交内存区

 

4、调度任务(12c没有图形界面EM，一下命令行还未测试成功)

创建/home/oracle/index_shell.sh脚本（索引的重建）

alter table emp move;
alter table dept move;  //保证有无效的索引
select index_name,status from user_index;

vi scheduler

(1)、创建程序

表示调度应该做什么事情，program_type是存储过程(stored_procedure)，需要指定存储过程的名称；

program_type是PL/SQL块(PLSQL_BLOCK)，需要输入完整的PL/SQL代码块；

program_type是外部程序(EXECUTABLE)，需要输入script的名称或操作系统的指令名

begin
dbms_scheduler.create_program(
program_name=>' "SYS"."SCOTT_INDEX_P1" ',
program_action=>'/home/oracle/index_shell.sh',
program_type=>'EXECUTABLE',
number_of_arguments=>0,
comments=>'test a schedule program',
enables=>TRUE
);
//program_name指定程序的名称；program_action实际执行的操作；program_type指定程序的类型；number_of_arguments指定支持的参数个数，0表示没有参数

(2)、创建并使用调度

schedule表示调度计划表，调度从什么时候开始被调度，什么时候结束，以什么频率调度。

BEGIN
dbms_scheduler.create_schedule(
schedule_name=>' "SYS"."SCOTT_INDEX_S1" ',
repeat_interval => 'FREQ=DAILY;BYHOUR=23',
start_date=>systimestamp at time zone 'Asia/Shanghai'
);
END
//schedual_name是调度名称；start_date是开始时间，systimestamp 返回系统当前日期；end_date是结束时间；
//repeat_interval是重复频率，freq是用来指定间隔的时间周期，可选的参数由yearly、monthly、weekly、daily、hourly...，interval是指定间隔的频率，如若freq=daily;interval=7表示每7天执行一次；
//byhour指定执行时的具体的时，byminute指定几分；bysesond指定秒

(3)、创建并提交作业

作业是一个组合，包括调度、要执行的操作说明以及作业需要的所有附加参数。

按照指定的schedule，执行执行program，完成用户指定的工作。

BEGIN
sys.dbms_scheduler.create_job(
job_name=> ' "SYS"."SCOTT_INDEX_JOB1" ',
program_name=>' "SYS"."SCOTT_INDEX_P1" ',
schedule_name=>' "SYS"."SCOTT_INDEX_S1" ',
job_class=> ' "DEFAULT_JOB_CLASS" ',
auto_drop =>FALSE,
enabled=>TRUE);

//job_name任务名称；auto_drop设置job执行完毕都到期是否直接删除job；enabled创建后是否自动激活