本文档风哥主要介绍Oracle ASH转储分析相关知识，包括ASH转储的概念、ASH转储组成、ASH转储使用、ASH转储配置、ASH转储监控、ASH转储故障处理等内容，由风哥教程参考Oracle官方文档Troubleshooting内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 ASH转储的概念
Oracle ASH（Active Session History）转储是Oracle数据库提供的一种性能分析工具，用于捕获活动会话的历史信息。ASH转储包含会话等待事件、SQL执行信息、资源使用等详细数据，是诊断性能问题、分析系统瓶颈的重要手段。更多视频教程www.fgedu.net.cn
- 历史数据：捕获活动会话的历史信息
- 性能分析：诊断性能问题的重要手段
- 详细信息：包含等待事件、SQL信息等
- 问题定位：帮助定位性能瓶颈
- Oracle Support：向Oracle Support提供诊断数据
1.2 ASH转储组成
Oracle ASH转储组成：
- 会话信息：会话ID、用户、程序等
- 等待事件：等待事件名称、参数、时间
- SQL信息：SQL ID、执行计划、执行时间
- 资源使用：CPU、内存、I/O使用情况
- 时间信息：采样时间、持续时间
1. 会话信息
– 会话ID和序列号
– 会话用户和程序
– 会话状态
– 会话模块和操作
– 会话机器和终端
2. 等待事件
– 等待事件名称
– 等待事件类别
– 等待参数P1、P2、P3
– 等待时间
– 等待计数
3. SQL信息
– SQL ID
– SQL文本
– 执行计划
– 执行时间
– 执行次数
4. 资源使用
– CPU使用时间
– 内存使用量
– I/O请求数
– 网络传输量
– 锁等待时间
5. 时间信息
– 采样时间
– 持续时间
– 时间范围
– 采样间隔
– 总采样数
1.3 ASH转储使用
Oracle ASH转储使用：
- 性能诊断：诊断性能问题
- 瓶颈分析：分析系统瓶颈
- SQL分析：分析SQL性能
- 等待分析：分析等待事件
- Oracle Support：向Oracle Support提供诊断数据
Part02-生产环境规划与建议
2.1 ASH转储规划
Oracle ASH转储规划要点：
– 转储时机：确定何时进行ASH转储
– 转储范围：确定转储的时间范围
– 转储位置：确定转储文件存储位置
– 结果分析：确定如何分析转储结果
# 转储时机规划
– 性能问题时：立即进行ASH转储
– 定期检查：定期检查ASH数据
– 升级前后：升级前后检查ASH
– 变更前后：变更前后检查ASH
# 转储范围规划
– 时间范围：确定转储的时间范围
– 会话范围：确定转储的会话范围
– 事件范围：确定转储的事件范围
– SQL范围：确定转储的SQL范围
# 转储位置规划
– ADR目录：默认存储在ADR目录
– 自定义目录：可以指定自定义目录
– 空间管理：确保有足够的磁盘空间
– 权限设置：确保正确的文件权限
# 结果分析规划
– 问题分类：按原因分类问题
– 问题修复：及时修复问题
– 问题跟踪：跟踪问题修复进度
– 预防措施：制定预防措施
2.2 ASH转储场景
Oracle ASH转储场景：
- 性能问题：系统性能下降分析
- 等待分析：等待事件分析
- SQL分析：SQL性能分析
- 资源争用：资源争用分析
- Oracle Support：向Oracle Support提供数据
1. 性能问题
– 系统性能下降
– 响应时间变长
– 吞吐量下降
– 资源使用异常
2. 等待分析
– 等待事件分析
– 等待链分析
– 阻塞会话分析
– 锁等待分析
3. SQL分析
– SQL性能分析
– SQL执行计划分析
– SQL资源使用分析
– SQL优化建议
4. 资源争用
– CPU争用分析
– 内存争用分析
– I/O争用分析
– 锁争用分析
5. Oracle Support
– 创建SR时收集数据
– 按Oracle Support要求收集
– 上传诊断数据
– 跟踪问题处理
2.3 ASH转储最佳实践
Oracle ASH转储最佳实践：
- 及时转储：性能问题发生时及时转储
- 合适范围：选择合适的转储范围
- 保存文件：保存转储文件
- 分析结果：分析转储结果
- 预防措施：制定预防措施
Part03-生产环境项目实施方案
3.1 ASH转储配置
3.1.1 生成ASH转储
SQL> var ash_report clob
SQL> begin
:ash_report := dbms_workload_repository.ash_report_html(
l_dbid => 1234567890,
l_inst_num => 1,
l_btime => to_date(‘2026-03-31 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’),
l_etime => to_date(‘2026-03-31 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
);
end;
/
PL/SQL procedure successfully completed.
# 2. 查看ASH报告
SQL> set long 1000000
SQL> set longchunksize 1000
SQL> set linesize 200
SQL> print ash_report
ASH Report HTML
…
# 3. 保存ASH报告到文件
SQL> spool /home/oracle/ash_report.html
SQL> print ash_report
SQL> spool off
# 4. 使用DBMS_WORKLOAD_REPOSITORY生成ASH报告（文本格式）
SQL> var ash_report clob
SQL> begin
:ash_report := dbms_workload_repository.ash_report_text(
l_dbid => 1234567890,
l_inst_num => 1,
l_btime => to_date(‘2026-03-31 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’),
l_etime => to_date(‘2026-03-31 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
);
end;
/
PL/SQL procedure successfully completed.
# 5. 使用SQL查询ASH数据
SQL> select sample_time, session_id, session_serial#, user_id,
sql_id, event, wait_class, time_waited
from v$active_session_history
where sample_time between
to_date(‘2026-03-31 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’) and
to_date(‘2026-03-31 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
order by sample_time;
SAMPLE_TIME SESSION_ID SESSION_SERIAL# USER_ID SQL_ID EVENT WAIT_CLASS TIME_WAITED
—————————— ———- ————— ———- ————- ——————————– ————— ———–
2026-03-31 09:00:01.123 150 1234 84 a1b2c3d4e5f6 enq: TX – row lock contention Application 10000
2026-03-31 09:00:02.123 150 1234 84 a1b2c3d4e5f6 enq: TX – row lock contention Application 10000
2026-03-31 09:00:03.123 160 5678 84 b2c3d4e5f6g7 db file sequential read User I/O 5000
# 6. 创建ASH转储脚本
$ vi /home/oracle/scripts/run_ashdump.sh
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# ASH转储脚本
export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH
LOG_DIR=”/home/oracle/ashdump”
mkdir -p $LOG_DIR
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE=”$LOG_DIR/ashdump_$DATE.log”
echo “$(date): Running ASH dump…” > $LOG_FILE
sqlplus -s / as sysdba <
set long 1000000
set longchunksize 1000
set linesize 200
set pagesize 0
var ash_report clob
begin
:ash_report := dbms_workload_repository.ash_report_text(
l_dbid => (select dbid from v\$database),
l_inst_num => 1,
l_btime => sysdate – 1/24,
l_etime => sysdate
);
end;
/
print ash_report
EOF
echo “$(date): ASH dump completed.” >> $LOG_FILE
cat $LOG_FILE
# 7. 执行脚本
$ chmod +x /home/oracle/scripts/run_ashdump.sh
$ /home/oracle/scripts/run_ashdump.sh
3.1.2 分析ASH转储
SQL> select event, wait_class, count(*) as wait_count,
round(count(*) * 100 / sum(count(*)) over(), 2) as pct
from v$active_session_history
where sample_time between
to_date(‘2026-03-31 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’) and
to_date(‘2026-03-31 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
group by event, wait_class
order by wait_count desc;
EVENT WAIT_CLASS WAIT_COUNT PCT
——————————– ————— ———– ——
db file sequential read User I/O 5000 50.00
enq: TX – row lock contention Application 2000 20.00
CPU + Wait for CPU CPU 1500 15.00
log file sync Commit 1000 10.00
SQL*Net message from client Idle 500 5.00
# 2. 分析Top SQL
SQL> select sql_id, count(*) as sample_count,
round(count(*) * 100 / sum(count(*)) over(), 2) as pct
from v$active_session_history
where sample_time between
to_date(‘2026-03-31 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’) and
to_date(‘2026-03-31 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
and sql_id is not null
group by sql_id
order by sample_count desc;
SQL_ID SAMPLE_COUNT PCT
————- ———— ——
a1b2c3d4e5f6 3000 30.00
b2c3d4e5f6g7 2000 20.00
c3d4e5f6g7h8 1500 15.00
d4e5f6g7h8i9 1000 10.00
e5f6g7h8i9j0 500 5.00
# 3. 分析Top会话
SQL> select session_id, session_serial#, user_id, program,
count(*) as sample_count
from v$active_session_history
where sample_time between
to_date(‘2026-03-31 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’) and
to_date(‘2026-03-31 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
group by session_id, session_serial#, user_id, program
order by sample_count desc;
SESSION_ID SESSION_SERIAL# USER_ID PROGRAM SAMPLE_COUNT
———- ————— ———- —————- ————
150 1234 84 sqlplus 3000
160 5678 84 sqlplus 2000
170 9012 84 sqlplus 1500
# 4. 分析等待链
SQL> with ash_data as (
select sample_time, session_id, session_serial#, event,
blocking_session, blocking_session_serial#
from v$active_session_history
where sample_time between
to_date(‘2026-03-31 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’) and
to_date(‘2026-03-31 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
and blocking_session is not null
)
select blocking_session, blocking_session_serial#,
session_id, session_serial#,
event, count(*) as wait_count
from ash_data
group by blocking_session, blocking_session_serial#,
session_id, session_serial#, event
order by wait_count desc;
BLOCKING_SESSION BLOCKING_SESSION_SERIAL# SESSION_ID SESSION_SERIAL# EVENT WAIT_COUNT
—————- ————————- ———- ————— ——————————– ———-
160 5678 150 1234 enq: TX – row lock contention 2000
170 9012 160 5678 enq: TX – row lock contention 1000
# 5. 使用脚本分析ASH转储
$ vi /home/oracle/scripts/analyze_ashdump.sh
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# 分析ASH转储脚本
export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH
LOG_DIR=”/home/oracle/ashdump_analysis”
mkdir -p $LOG_DIR
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE=”$LOG_DIR/ashdump_analysis_$DATE.log”
echo “========================================” > $LOG_FILE
echo “ASH Dump Analysis Report” >> $LOG_FILE
echo “Date: $(date)” >> $LOG_FILE
echo “========================================” >> $LOG_FILE
sqlplus -s / as sysdba <
set linesize 200
set pagesize 100
— Top等待事件
prompt
prompt === Top Wait Events ===
select event, wait_class, count(*) as wait_count
from v\$active_session_history
where sample_time > sysdate – 1/24
group by event, wait_class
order by wait_count desc
fetch first 10 rows only;
— Top SQL
prompt
prompt === Top SQL ===
select sql_id, count(*) as sample_count
from v\$active_session_history
where sample_time > sysdate – 1/24
and sql_id is not null
group by sql_id
order by sample_count desc
fetch first 10 rows only;
— Top会话
prompt
prompt === Top Sessions ===
select session_id, session_serial#, program, count(*) as sample_count
from v\$active_session_history
where sample_time > sysdate – 1/24
group by session_id, session_serial#, program
order by sample_count desc
fetch first 10 rows only;
exit
EOF
echo “” >> $LOG_FILE
echo “Analysis completed at $(date)” >> $LOG_FILE
cat $LOG_FILE
# 6. 执行分析脚本
$ chmod +x /home/oracle/scripts/analyze_ashdump.sh
$ /home/oracle/scripts/analyze_ashdump.sh
3.2 ASH转储监控
3.2.1 配置ASH转储监控脚本
$ vi /home/oracle/scripts/monitor_ashdump.sh
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# ASH转储监控脚本
export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH
LOG_DIR=”/home/oracle/ashdump_monitor”
mkdir -p $LOG_DIR
DATE=$(date +%Y%m%d)
LOG_FILE=”$LOG_DIR/ashdump_monitor_$DATE.log”
echo “$(date): Starting ASH dump monitor…” > $LOG_FILE
# 检查ASH数据
sqlplus -s / as sysdba <
set linesize 200
set pagesize 100
— 检查活跃会话
select count(*) as active_sessions
from v\$session
where status = ‘ACTIVE’;
— 检查等待事件
select event, count(*) as wait_count
from v\$session_wait
where wait_class != ‘Idle’
group by event
order by wait_count desc;
— 检查ASH采样数
select count(*) as sample_count
from v\$active_session_history
where sample_time > sysdate – 1/24;
exit
EOF
echo “$(date): ASH dump monitor completed.” >> $LOG_FILE
# 发送报告
mail -s “ASH Dump Monitor Report – $DATE” admin@fgedu.net.cn < $LOG_FILE
# 2. 设置脚本权限
$ chmod +x /home/oracle/scripts/monitor_ashdump.sh
# 3. 测试脚本
$ /home/oracle/scripts/monitor_ashdump.sh
# 4. 设置定期监控
$ crontab -e
# 每10分钟监控ASH
*/10 * * * * /home/oracle/scripts/monitor_ashdump.sh >> /home/oracle/ashdump_monitor/cron.log 2>&1
3.2.2 配置自动ASH转储
$ vi /home/oracle/scripts/auto_ashdump.sh
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# 自动ASH转储脚本
export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH
LOG_DIR=”/home/oracle/ashdump”
mkdir -p $LOG_DIR
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE=”$LOG_DIR/ashdump_$DATE.log”
echo “========================================” > $LOG_FILE
echo “ASH Dump Report” >> $LOG_FILE
echo “Date: $(date)” >> $LOG_FILE
echo “========================================” >> $LOG_FILE
# 检查是否有性能问题
WAIT_COUNT=$(sqlplus -s / as sysdba <
EOF
)
if [ $WAIT_COUNT -gt 10 ]; then
echo “Found $WAIT_COUNT waiting sessions” >> $LOG_FILE
echo “Running ASH dump…” >> $LOG_FILE
sqlplus -s / as sysdba <
set long 1000000
set longchunksize 1000
set linesize 200
set pagesize 0
var ash_report clob
begin
:ash_report := dbms_workload_repository.ash_report_text(
l_dbid => (select dbid from v\$database),
l_inst_num => 1,
l_btime => sysdate – 1/24,
l_etime => sysdate
);
end;
/
print ash_report
exit
EOF
# 发送告警
mail -s “ASH Dump Alert: $WAIT_COUNT waiting sessions” admin@fgedu.net.cn < $LOG_FILE
else
echo "No significant issue detected ($WAIT_COUNT waiting sessions)" >> $LOG_FILE
fi
echo “” >> $LOG_FILE
echo “Dump completed at $(date)” >> $LOG_FILE
# 2. 设置脚本权限
$ chmod +x /home/oracle/scripts/auto_ashdump.sh
# 3. 测试脚本
$ /home/oracle/scripts/auto_ashdump.sh
# 4. 设置定期转储
$ crontab -e
# 每5分钟检查ASH
*/5 * * * * /home/oracle/scripts/auto_ashdump.sh >> /home/oracle/ashdump/cron.log 2>&1
3.3 ASH转储故障处理
3.3.1 ASH转储常见问题
# 错误信息
SQL> select count(*) from v$active_session_history;
COUNT(*)
———-
0
# 解决方案
# 1. 检查ASH配置
SQL> show parameter statistics_level
NAME TYPE VALUE
———————————— ———– ——————————
statistics_level string TYPICAL
# 2. 启用统计信息
SQL> alter system set statistics_level = typical;
System altered.
# 3. 检查ASH大小
SQL> select * from v$ash_info;
# 4. 调整ASH大小
SQL> alter system set sga_target = 2G scope=spfile;
System altered.
# 问题2：ASH报告生成失败
# 错误信息
SQL> begin
:ash_report := dbms_workload_repository.ash_report_html(…);
end;
/
ERROR at line 1:
ORA-13509: error encountered during updates to AWR tables
# 解决方案
# 1. 检查AWR状态
SQL> select * from dba_hist_wr_control;
# 2. 检查SYSAUX空间
SQL> select tablespace_name, used_space, free_space
from dba_tablespace_usage_metrics
where tablespace_name = ‘SYSAUX’;
# 3. 清理AWR数据
SQL> exec dbms_workload_repository.drop_snapshot_range(
low_snap_id => 1,
high_snap_id => 100
);
PL/SQL procedure successfully completed.
# 问题3：ASH查询性能差
# 错误信息
— ASH查询执行时间过长
# 解决方案
# 1. 使用时间范围限制
SQL> select * from v$active_session_history
where sample_time > sysdate – 1/24;
# 2. 使用索引提示
SQL> select /*+ index(ash ASH_SAMPLE_TIME_IDX) */ *
from v$active_session_history ash
where sample_time > sysdate – 1/24;
# 3. 使用DBA_HIST_ACTIVE_SESS_HISTORY
SQL> select * from dba_hist_active_sess_history
where sample_time > sysdate – 1;
3.3.2 ASH转储问题修复
# 查看Top等待事件
SQL> select event, count(*) from v$active_session_history
where sample_time > sysdate – 1/24
group by event order by 2 desc;
# 查看Top SQL
SQL> select sql_id, count(*) from v$active_session_history
where sample_time > sysdate – 1/24
and sql_id is not null
group by sql_id order by 2 desc;
# 查看阻塞会话
SQL> select blocking_session, session_id, event, count(*)
from v$active_session_history
where sample_time > sysdate – 1/24
and blocking_session is not null
group by blocking_session, session_id, event
order by 4 desc;
# 2. 根据分析结果采取行动
# 如果是锁等待问题
# 识别阻塞会话
SQL> select sid, serial#, username, program
from v$session
where sid =
# 终止阻塞会话
SQL> alter system kill session ‘
# 如果是SQL性能问题
# 查看SQL文本
SQL> select sql_text from v$sql where sql_id = ‘
# 查看执行计划
SQL> select * from table(dbms_xplan.display_cursor(‘
# 优化SQL
SQL> exec dbms_sqltune.create_tuning_task(sql_id => ‘
# 如果是资源争用问题
# 查看资源使用
SQL> select * from v$resource_limit;
# 调整资源参数
SQL> alter system set processes = 500 scope=spfile;
# 3. 验证修复
# 重新生成ASH报告
SQL> var ash_report clob
SQL> begin
:ash_report := dbms_workload_repository.ash_report_text(
l_dbid => (select dbid from v$database),
l_inst_num => 1,
l_btime => sysdate – 1/24,
l_etime => sysdate
);
end;
/
SQL> print ash_report
Part04-生产案例与实战讲解
4.1 ASH转储案例
在生产环境中使用ASH转储的完整案例：
4.1.1 场景描述
某企业生产数据库出现性能问题，需要使用ASH转储进行诊断。
4.1.2 分析步骤
$ vi /home/oracle/scripts/analyze_ashdump_full.sh
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# ASH转储自动化脚本
export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH
LOG_DIR=”/home/oracle/ashdump_analysis”
mkdir -p $LOG_DIR
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE=”$LOG_DIR/ashdump_analysis_$DATE.log”
echo “========================================” > $LOG_FILE
echo “ASH Dump Analysis Report” >> $LOG_FILE
echo “Date: $(date)” >> $LOG_FILE
echo “========================================” >> $LOG_FILE
# 生成ASH报告
echo “” >> $LOG_FILE
echo “Generating ASH report…” >> $LOG_FILE
sqlplus -s / as sysdba <
set long 1000000
set longchunksize 1000
set linesize 200
set pagesize 0
var ash_report clob
begin
:ash_report := dbms_workload_repository.ash_report_text(
l_dbid => (select dbid from v\$database),
l_inst_num => 1,
l_btime => sysdate – 1/24,
l_etime => sysdate
);
end;
/
print ash_report
exit
EOF
# 分析ASH数据
echo “” >> $LOG_FILE
echo “Analyzing ASH data…” >> $LOG_FILE
sqlplus -s / as sysdba <
set linesize 200
set pagesize 100
— Top等待事件
prompt
prompt === Top Wait Events ===
select event, wait_class, count(*) as wait_count
from v\$active_session_history
where sample_time > sysdate – 1/24
group by event, wait_class
order by wait_count desc
fetch first 10 rows only;
— Top SQL
prompt
prompt === Top SQL ===
select sql_id, count(*) as sample_count
from v\$active_session_history
where sample_time > sysdate – 1/24
and sql_id is not null
group by sql_id
order by sample_count desc
fetch first 10 rows only;
— Top会话
prompt
prompt === Top Sessions ===
select session_id, session_serial#, program, count(*) as sample_count
from v\$active_session_history
where sample_time > sysdate – 1/24
group by session_id, session_serial#, program
order by sample_count desc
fetch first 10 rows only;
exit
EOF
echo “” >> $LOG_FILE
echo “Analysis completed at $(date)” >> $LOG_FILE
# 发送报告
mail -s “ASH Dump Analysis Report – $DATE” admin@fgedu.net.cn < $LOG_FILE
# 2. 设置脚本权限
$ chmod +x /home/oracle/scripts/analyze_ashdump_full.sh
# 3. 测试脚本
$ /home/oracle/scripts/analyze_ashdump_full.sh
4.2 ASH转储故障处理
在ASH转储故障处理过程中的方法和技巧：
4.2.1 故障处理流程
# 1. 识别问题
# – 检查系统性能
# – 检查等待事件
# – 检查ASH数据
# 2. 收集信息
# – 生成ASH报告
# – 查询ASH数据
# – 收集其他诊断数据
# 3. 分析原因
# – 分析Top等待事件
# – 分析Top SQL
# – 分析Top会话
# 4. 制定方案
# – 优化SQL
# – 调整参数
# – 增加资源
# 5. 执行修复
# – 执行修复操作
# – 验证修复结果
# – 记录修复过程
# 示例：处理性能问题
# 1. 识别问题
SQL> select count(*) from v$session where status = ‘ACTIVE’;
# 2. 收集信息
SQL> var ash_report clob
SQL> begin
:ash_report := dbms_workload_repository.ash_report_text(
l_dbid => (select dbid from v$database),
l_inst_num => 1,
l_btime => sysdate – 1/24,
l_etime => sysdate
);
end;
/
# 3. 分析原因
SQL> select event, count(*) from v$active_session_history
where sample_time > sysdate – 1/24
group by event order by 2 desc;
# 4. 制定方案
# – 识别性能瓶颈
# – 决定优化方案
# 5. 执行修复
SQL> alter system set sga_target = 4G scope=spfile;
4.3 ASH转储优化
优化ASH转储配置的最佳实践：
4.3.1 优化ASH转储管理
SQL> create or replace procedure manage_ashdump_results(
p_analysis_date in date,
p_total_wait_events in number,
p_total_sql_count in number
) as
begin
dbms_output.put_line(‘— ASH Dump Results Management —‘);
dbms_output.put_line(‘Analysis Date: ‘ || to_char(p_analysis_date, ‘YYYY-MM-DD HH24:MI:SS’));
dbms_output.put_line(‘Total Wait Events: ‘ || p_total_wait_events);
dbms_output.put_line(‘Total SQL Count: ‘ || p_total_sql_count);
— 可以扩展为记录到表中
end manage_ashdump_results;
/
Procedure created.
# 2. 执行管理存储过程
SQL> set serveroutput on
SQL> exec manage_ashdump_results(sysdate, 10, 50);
— ASH Dump Results Management —
Analysis Date: 2026-03-31 10:00:00
Total Wait Events: 10
Total SQL Count: 50
PL/SQL procedure successfully completed.
# 3. 创建ASH转储历史表
SQL> create table ashdump_history (
id number primary key,
analysis_date date,
total_wait_events number,
total_sql_count number,
report_file varchar2(4000),
notes varchar2(4000)
);
Table created.
SQL> create sequence ashdump_history_seq;
Sequence created.
# 4. 记录分析历史
SQL> insert into ashdump_history values (
ashdump_history_seq.nextval,
sysdate,
10,
50,
‘/home/oracle/ashdump/ashdump_20260331_100000.log’,
‘Performance issue analysis’
);
1 row created.
SQL> commit;
Commit complete.
# 5. 查看分析历史趋势
SQL> column analysis_date format a20
SQL> select analysis_date, total_wait_events, total_sql_count
from ashdump_history
order by analysis_date desc
fetch first 12 rows only;
ANALYSIS_DATE TOTAL_WAIT_EVENTS TOTAL_SQL_COUNT
——————– —————– —————
2026-03-31 10:00:00 10 50
2026-03-30 10:00:00 15 60
2026-03-29 10:00:00 20 70
Part05-风哥经验总结与分享
5.1 ASH转储总结
Oracle ASH转储是诊断性能问题的重要手段，具有以下特点：
- 历史数据：捕获活动会话的历史信息
- 性能分析：诊断性能问题的重要手段
- 详细信息：包含等待事件、SQL信息等
- 问题定位：帮助定位性能瓶颈
- Oracle Support：向Oracle Support提供诊断数据
5.2 ASH转储检查清单
Oracle ASH转储检查清单：
- 及时转储：性能问题发生时及时转储
- 合适范围：选择合适的转储范围
- 保存文件：保存转储文件
- 分析结果：分析转储结果
- 预防措施：制定预防措施
- 定期检查：定期检查ASH数据
5.3 ASH转储工具推荐
Oracle ASH转储工具推荐：
- ASH报告：DBMS_WORKLOAD_REPOSITORY
- ASH查询：V$ACTIVE_SESSION_HISTORY
- AWR报告：AWR报告
- ADRCI：诊断命令行工具
- Shell脚本：自动化分析过程
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
