oracle性能诊断工具

1、Oracle 自带性能诊断工具介绍

1.1 statspack （9i 必用,10g,11g,12c 兼容。）

Oracle Statspack 工具从 Oracle 8.1.6 开始引入，通过 Statspack 可以很容易地收集数据库性能数据，并通过这些数据进而分析确定 Oracle 数据库的瓶颈所在。

1.2 awr Oracle 10g 以后提供了一个新的工具：(AWR:Automatic Workload Repository)。

Oracle 建议用户用这个取代 Statspack。AWR 实质上是一个 Oracle 的内置工具，它采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题。与 Statspack 不同，快照由一个称为 MMON 的新的后台进程及其从进程自动地每小时采集一次。
采集的数据时间（快照频率和保留时间都可以由用户修改）：

• oracle10g 在 7 天后自动清除,
• oracle11g 在 8 天后清理。

1.3 addm

ADDM(Automatic Database Diagnostic Monitor) 是 Oracle 数据库的一个自诊断引擎.ADDM 通过检查和分析 AWR获取的数据来判断 Oracle数据库中可能的问题.是 Oracle 内部的一个顾问系统，能够自动的完成最数据库的一些优化的建议，给出SQL 的优化，索引的创建，统计量的收集等建议。

1.4 ash ASH (Active Session History)

ASH 以 V$SESSION 为基础，每秒采样一次，记录活动会话等待的事件。不活动的会话不会采样，采样工作由新引入的后台进程 MMNL 来完成。

1.5 awrdd

AWRDD 是用于比较两个 AWR 快照，从而获得不同时期的性能。

1.6 AWRSQL

在 AWR 中定位到问题 SQL 语句后想要了解该 SQL statement 的具体执行计划，于是就用 AWR 报告中得到的 SQL ID 去 V$SQL 等几个动态性能视图中查询，但发现V$SQL 或 V$SQL_PLAN 视图都已经找不到对应 SQL ID 的记录，一般来说这些语句已经从 shared pool 共享池中被替换出去了。

2、日常维护中的性能工具操作过程

2.1 statspack

01.修改参数

show parameter job
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
10g 默认是 0，
show parameter timed
------------------------------------ ----------- ------------------------------
timed_statistics boolean TRUE

alter system set job_queue_processes=1000 scope=both;
alter system set timed_statistics=TRUE scope=both;

02.创建表空间

create tablespace perfstat datafile '/oracle/app/oracle/oradata/orclfs/perfstat.dbf' size 200m autoextend off;

03.安装 statspack

@?/rdbms/admin/spcreate.sql

04.测试 statspack

conn perfstat/perfstat
execute statspack.snap
@?/rdbms/admin/spreport.sql

05.自动任务

conn perfstat/perfstat
@?/rdbms/admin/spauto.sql

06.移动定时任务

select job,log_user,last_date,next_date,interval from user_jobs;

execute dbms_job.remove('3');
execute dbms_job.remove('4');

07.删除历史数据

select max(snap_id) from stats$snapshot;
delete from stats$snapshot where snap_id <=2;
select max(snap_id) from stats$snapshot;	

08.删除 statspack 功能

@?/rdbms/admin/sptrunc.sql
@?/rdbms/admin/spdrop.sql

09.删除 statspack 表空间

drop tablespace perfstat including contents and datafiles;

2.2 awr

01.单实例

@?/rdbms/admin/awrrpt.sql

02.RAC

每个节点运行
@?/rdbms/admin/awrrpt.sql
每个节点运行/一个节点上运行收集多个。
@?/rdbms/admin/awrrpti.sql

03.一个节点上生成多个节点的。

@?/rdbms/admin/awrgrpti.sql	

2.3 addm

01.单实例

@?/rdbms/admin/addmrpt.sql

02.RAC

每个节点运行
@?/rdbms/admin/addmrpt.sql
每个节点运行/一个节点上运行收集多个。
@?/rdbms/admin/addmrpti.sql

2.4 awrsql

01.单实例

@?/rdbms/admin/awrsqrpt.sql

02.RAC

@?/rdbms/admin/awrsqrpti.sql

2.5 ash

01.单实例
@?/rdbms/admin/ashrpt.sql
02.RAC
@?/rdbms/admin/ashrpti.sql

2.6 awrdd

01.单实例
@?/rdbms/admin/awrddrpt.sql
02.RAC
@?/rdbms/admin/awrgdrpt.sql
@?/rdbms/admin/awrgdrpi.sql

03.某一个实例

@?/rdbms/admin/awrddrpi.sql

3、awr 性能数据的收集时间

awr 默认通过 mmon 及 mmnl 进程来每小自动运行一次，为了节省空间，采集的数据在 10g/7,11g/8 天后自动清除。

select * from dba_hist_wr_control



begin
	dbms_workload_repository.modify_snapshot_settings(interval =>60,retention => 30*24*60);
end;
/



select * from dba_hist_wr_control;

4、awr 收集性能报告收集过程

01.单实例

@?/rdbms/admin/awrrpt.sql

02.RAC

每个节点运行
@?/rdbms/admin/awrrpt.sql
每个节点运行/一个节点上运行收集多个。
@?/rdbms/admin/awrrpti.sql
03.一个节点上生成多个节点的。
@?/rdbms/admin/awrgrpti.sql

5、awr 手工快照如何实现

01、手工创建快照：
可以使用 create_snapshot 存储过程手动创建快照来捕获非自动生成快照的时间内的统计信息;

dbms_workload_repository.create_snapshot(flush_level in varchar2 default 'typical')
	return number;
	
flush_level 参数 flush level 可以是 'typical' 或 'all'

–手工创建快照

begin
	dbms_workload_repository.create_snapshot();
end;
/



select * from dba_hist_snapshot;

–手工删除快照

select * from dba_hist_snapshot;
begin
	dbms_workload_repository.drop_snapshot_range(low_snap_id=>30,high_snap_id => 31);
end;
/

6、awr baseline 的手工调整与管理

01,创建一个基线

select * from dba_hist_snapshot;
begin
	dbms_workload_repository.create_baseline(start_snap_id => 51, end_snap_id => 52, baseline_name =>'orcl_baseline-51-52',expiration => 30);
end;
/



select * from dba_hist_baseline;

02,重命名一个基线

begin
	dbms_workload_repository.rename_baseline(old_baseline_name=>'orcl_baseline-51-52',new_baseline_name =>'orcl_baseline-51-to-52');
end;
/

03,删除一个基线

begin
	dbms_workload_repository.drop_baseline(baseline_name=>'orcl_baseline-51-52');
end;
/
select * from dba_hist_baseline

7、awr 性能相关的视图介绍

01.v$active_session_history 02.v$ 度量视图 v$metricgroup
03.dba_hist 视图
这类视图包括：

dba_hist_active_sess_history 显示最近的系统活动的内存中活动会话历史的历史信息。
dba_hist_baseline 显示数据库捕获的 baseline 的信息，如每个 baseline 的时间范围和 baseline type
dba_hist_baseline_details 显示特定 baseline 的详细信息
dba_hist_baseline_template 显示系统用于生成 baseline 的 baseline template
dba_hist_database_instance 显示数据库环境相关的信息
dba_hist_db_cache_advice 显示每行对应的缓存大小所产生的 physical read 的数量的历史预测信息
dba_hist_dispatcher 显示捕获快照时每个 dispatcher 进程的历史信息
dba_hist_dyn_remaster_stats 显示动态 remastering 进程相关的信息
dba_hist_iostat_detail 显示按照文件类型和功能统计的 i/o 统计信息
dba_hist_shared_server_summary 显示共享服务器的历史信息，如共享服务器活动、普通队列和 dispatcher 队列
dba_hist_snapshot 显示系统中的快照信息
dba_hist_sql_plan 显示 sql 执行计划
dba_hist_wr_control 显示控制 awr 的设置

用这个查询：



spool /oracle/dba_hist_all.out
col table_name format a34
col comments format a66
set lin 200
set pagesize 200
select * from dict where table_name like 'DBA_HIST%';
spool off;

8、awr 性能分析数据的迁移

很多时候我们直接在客户机器上分析 awr 不太方便,需要通过收集客户 awr 信息到另一台机器上进行分析数据库性能等
oracle database 允许我们在几个数据库之间传输 awr 数据。当您要在单独的系统上分析 awr 数据时，这非常有用。要传输 awr 数据，您必须先从源数据库上抽取出 awr
快照数据，然后将该数据载入目标数据库中。以下内容将介绍如何在 oracle database上抽抽取和载入 awr 数据。

01.导出 awr 性能数据

mkdir -p /home/oracle/bakcup
cd /home/oracle/backup
sqlplus "/as sysdba"
create directory awr_dir as '/home/oracle/backup';
@?/rdbms/admin/awrextr.sql

02.导入 awr 性能数据（找一台新机）
–模拟清空所有的 AWR 数据(同一个实例)

select * from dba_hist_snapshot;
begin
dbms_workload_repository.drop_snapshot_range(low_snap_id=>1,high_snap_id => 52);
end;
/



begin
	dbms_workload_repository.drop_snapshot_range(low_snap_id=>1,high_snap_id => 53);
end;
/

–模拟清空所有的 AWR 数据(非同一个实例)

begin
	dbms_swrf_internal.unregister_database(123456789); --dbid
end;
/

–如果以前创建了基线，就是要手工删除基线

begin
	dbms_workload_repository.drop_baseline(baseline_name=>'orcl_baseline-51-to-52',cascade => TRUE);
end;
/

–先创建 awr_dir 目录

–awrload.sql (sys)

mkdir -p /home/oracle/bakcup
cd /home/oracle/backup
sqlplus "/as sysdba"
create directory awr_dir as '/home/oracle/backup';
@?/rdbms/admin/awrload.sql
--检查
@?/rdbms/admin/awrrpt.sql
select * from dba_hist_snapshot;

9、awr 性能诊断报告的分析

10、awrsql 报告的获取与分析

–手工创建快照

begin
dbms_workload_repository.create_snapshot();
end;
	/

	select * from orcl.orcl_member where name='orcl12345';
	select * from orcl.orcl_member where name='orcl12345678';
	
	begin
	dbms_workload_repository.create_snapshot();
	end;
	/

	@?/rdbms/admin/awrrpt.sql
	@?/rdbms/admin/awrsqrpt.sql
	@?/rdbms/admin/awrsqrpi.sql

11、addm 性能顾问系统的使用

单机

@?/rdbms/admin/addmrpt.sql
RAC
@?/rdbms/admin/addmrpti.sql
查与 awr/addm/ash 类似信息的视图
select * from v$session;
select * from v$session_wait;
select * from v$session_wait_history
select * from v$acitve_session_history
select * from wrh$_active_session_history
select * from dba_hist_active_sess_history

12、addm 性能建议报告的分析

11gR2
10gR2

13、ash 介绍与生成 ash 报告

4M， 最大 30M，期望值 1 小时
	单机
	@?/rdbms/admin/ashrpt.sql
	RAC
	@?/rdbms/admin/ashrpti.sql

14、awrdd 报告生成与分析

01.单实例
	@?/rdbms/admin/awrddrpt.sql
	02.RAC
	@?/rdbms/admin/awrgdrpt.sql
	@?/rdbms/admin/awrgdrpi.sql
	03.某一个实例
	@?/rdbms/admin/awrddrpi.sql