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