ash使用恢复数据 oracle,执行查询坏块语句后，ASH报告出现wait for cpu等待事件

1、背景：

在2016.12.5上午10点左右执行数据库坏块查询语句，语句如下：

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#

, greatest(e.block_id, c.block#) corr_start_block#

, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#

, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)

- greatest(e.block_id, c.block#) + 1 blocks_corrupted

, corruption_type description

FROM dba_extents e, v$database_block_corruption c

WHERE e.file_id = c.file#

AND e.block_id <= c.block# + c.blocks - 1

AND e.block_id + e.blocks - 1 >= c.block#

UNION

SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#

, header_block corr_start_block#

, header_block corr_end_block#

, 1 blocks_corrupted

, corruption_type||' Segment Header' description

FROM dba_segments s, v$database_block_corruption c

WHERE s.header_file = c.file#

AND s.header_block between c.block# and c.block# + c.blocks - 1

UNION

SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#

, greatest(f.block_id, c.block#) corr_start_block#

, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#

, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)

- greatest(f.block_id, c.block#) + 1 blocks_corrupted

, 'Free Block' description

FROM dba_free_space f, v$database_block_corruption c

WHERE f.file_id = c.file#

AND f.block_id <= c.block# + c.blocks - 1

AND f.block_id + f.blocks - 1 >= c.block#

order by file#, corr_start_block#;

2、应用反馈：

约11:30左右应用反应业务缓慢，调取11:30-11:44期间双节点的ASH报告。

在节点1的ASH报告中，TOP SQL有且仅有该查询语句，且对应WAIT EVENT为CPU + Wait for CPU。

3、请求分析：

麻烦帮忙分析为何在执行查询语句后，已过约1小时，该查询语句会影响业务运行，且出现Wait for CPU的等待事件。

备注：

附件

1、11:30-11:44期间数据库双节点ASH报告及12:00-14:30节点1每半小时的AWR报告；

2、该SQL近一周的执行情况。

2016-12-5 16:14 上传

点击文件名下载附件

259.32 KB, 下载次数: 676