提升数据库性能的关键指南-Oracle AWR报告
-- 假设我们已经有了一份Oracle AWR报告,并且我们想要找到影响性能的Top SQL
-- 以下是一个简化的SQL脚本,用于查找Top SQL并关联相关的SQL文本
SELECT
sql_id,
executions,
parse_calls,
disk_reads,
buffer_gets,
elapsed_time,
cpu_time,
user_io_wait_time
FROM
(SELECT
*
FROM
dba_hist_sqlstat
WHERE
snapshot_id BETWEEN :begin_snap AND :end_snap
AND sql_id IN (SELECT
sql_id
FROM
dba_hist_sqlstat
WHERE
snapshot_id BETWEEN :begin_snap AND :end_snap
GROUP BY
sql_id
ORDER BY
(total_waits + total_timeouts + total_errors) DESC
FETCH FIRST 10 ROWS ONLY))
ORDER BY
(buffer_gets + disk_reads) DESC
FETCH FIRST 10 ROWS ONLY;
这个简化的SQL脚本用于查找在指定的AWR时间范围内,消耗最多资源的Top SQL,并且可以通过绑定变量来动态传递时间范围。这个例子展示了如何结合使用DBA\_HIST\_SQLSTAT视图来查找性能瓶颈的SQL,并且通过子查询和排序来找到最需要关注的SQL。
评论已关闭