如何诊断Oracle数据库运行缓慢或hang住的问题

从oracle11gr1开始,dia0后台进程开始收集Hang分析信息并存储在内存中的hang analysis cache中.它会每3秒钟收集一次本地的Hang分

为了诊断Oracle运行缓慢的问题首先要决定收集哪些诊断信息,可以采取下面的诊断方法:
1.数据库运行缓慢这个问题是常见还是在特定时间出现
如果数据库运行缓慢是一个常见的问题那么可以在问题出现的时候收集这个时期的awr或者statspack报告(通常收集时间间隔是一个小时).生成awr报告的方法如下:
awr是通过sys用户来收集持久系统性能统计信息并且这些信息保存在sysaux表空间.缺省情况下快照是一个小时生成一次并且保留7天.awr报告输出了基于指定快照之间的一系列的统计信息用于性能分析和调查其它问题.
运行基本的报告
可以执行下面的脚本来生成一个awr报告:
$ORACLE_HOME/rdbms/admin/awrrpt.sql

可以根据自己收集awr报告的原因来决定生成一个快照的时间间隔也可以指定生成awr报告的格式(text或html).

生成各种类型的awr报告
可以根据各种要求来运行各种sql脚本来生成各种类型的awr报告.每一种报告都有两种格式(txt或html):
awrrpt.sql
显示指定快照范围内的各种统计信息

awrrpti.sql
显示一个特定数据库和实例中指定快照范围内的各种统计信息

awrsqrpt.sql
显示一个指定快照范围内的一个特定的sql语句的统计信息.运行这个报告是为了检查或调查一个特定sql语句的性能

awrsqrpi.sql
显示一个特定sql在指定快照范围内的的统计信息.

awrddrpt.sql
比较在两个选择的时间间隔期间内详细的性能数据和配置情况

awrddrpi.sql
在一个特定的数据库和平共处实例中比较在两个选择的时间间隔期间内详细的性能数和配置情况

各种awr相关的操作
怎样修改awr快照的设置:
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (43200 = 30 Days).
-- Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.
END;
/

创建一个awr基线:
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 10,
end_snap_id => 100,

baseline_name => 'AWR First baseline');
END;
/

在oracle11G中引入了一个新的dbms_workload_repository.create_baseline_template过程来创建一个awr基线模板
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
start_time => to_date('&start_date_time','&start_date_time_format'),
end_time => to_date('&end_date_time','&end_date_time_format'),
baseline_name => 'MORNING',
template_name => 'MORNING',
expiration => NULL ) ;
END;
/

"expiration=>NULL"这意味着这个基线将永远保持有效.

删除一个awr基线
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (
baseline_name => 'AWR First baseline');
END;
/

也能从一个老的数据库中删除一个awr基线:
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline',
cascade => FALSE, dbid => 3310949047);
END;
/

删除awr快照:
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
(low_snap_id=>40,
High_snap_id=>80);
END;
/

也可能基于报告时间期间对创建和删除的awr基线指定一个模板:
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
day_of_week => 'MONDAY',
hour_in_day => 9,
duration => 3,
start_time => to_date('&start_date_time','&start_date_time_format'),
end_time => to_date('&end_date_time','&end_date_time_format'),
baseline_name_prefix => 'MONDAY_MORNING'
template_name => 'MONDAY_MORNING',
expiration => 30 );
END;
/

将会在'&start_date_time'到'&end_date_time'期间的每一个星期一都会生成基线

手动生成的一个awr快照:
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_snapshot();
END;
/

工作负载资料档案库视图:
V$ACTIVE_SESSION_HISTORY - 显示历史活动会话信息每秒抽样一样
V$METRIC - 显示度量标准信息
V$METRICNAME - 显示与每个度量标准组相关的度量标准
V$METRIC_HISTORY - 显示历史度量标准
V$METRICGROUP - 显示所有的度量标准组
DBA_HIST_ACTIVE_SESS_HISTORY - 显示历史活动会话的详细信息
DBA_HIST_BASELINE - 显示基线信息
DBA_HIST_DATABASE_INSTANCE - 显示数据库环境信息
DBA_HIST_SNAPSHOT - 显示快照信息
DBA_HIST_SQL_PLAN - 显示sql执行计划
DBA_HIST_WR_CONTROL - 显示awr设置情况

如果数据库运行缓慢在特定时间出现那么可以当问题存在时生成一个awr或statspack报告,报告的时间间隔包含了问题出现的时间.另外为了比较可以收集没有出现问题而时间间隔相同的数据库正常运行的报告这样可以对报告进行比较.