@@@客户***数据库STATSPACK报告无法生成TOP SQL章节问题处理过程
关键字:
STATSPACK
作者:
ioriakyo
一、问题描述:
数据库版本:ORACLE 9.2.0.8 单机
操作系统版本:AIX 5308
1、成功执行STATSPACK.SNAP。
2、执行SNAP操作后,PERFSTAT用户下的stats$sqltext表没有产生任何记录信息。
3、STATSPACK报告无法生成TOP SQL章节。
[@more@]二、处理分析过程:
1、使用以下函数修改STATSPACK快照级别和阀值:
SQL>execute statspack.snap(i_snap_level=>0,i_modify_parameter=>'true');
SQL>execute statspack.modify_statspack_parameter(i_buffer_gets_th=>100000,i_disk_reads_th=>100000);
并通过查询stats$statspack_parameter表,确定快照级别修改生效。
分析与结论:STATSPACK报告无法生成TOP SQL章节。
2、查看STATSPACK程序,并使用PLSQL Developer工具进行测试:
发现对stats$sqltext表进行插入的过程无法成功执行:
while (not (l_insert_done) and l_counter < l_counter_maxvalue) loop
begin
insert into stats$sqltext
( hash_value
, text_subset
, piece
, sql_text
, address
, command_type
, last_snap_id
)
select /*+ ordered use_nl(vst) */
vst.hash_value
, new_sql.text_subset
, vst.piece
, vst.sql_text
, vst.address
, vst.command_type
, new_sql.snap_id
from (select hash_value
, address
, text_subset
, snap_id
from stats$sql_summary ss
where ss.snap_id = l_snap_id
and ss.dbid = p_dbid
and ss.instance_number = p_instance_number
and not exists (select 1
from stats$sqltext sst
where sst.hash_value = ss.hash_value
and sst.text_subset = ss.text_subset
and sst.piece = 0
)
) new_sql
, v$sqltext vst
where vst.hash_value = new_sql.hash_value
and vst.address = new_sql.address
order by vst.hash_value, new_sql.text_subset
, vst.piece; -- deadlock avoidance
l_insert_done := TRUE;
exception
when DUP_VAL_ON_INDEX then
l_counter := l_counter + 1; end;
end loop;
l_counter := 0;
l_insert_done := FALSE;
分析与结论:测试进入死循环,l_counter_maxvalue定义值100,尝试100次后跳出循环。
3、将程序中的SQL语句截取修改,尝试手动插入操作:
insert into stats$sqltext
( hash_value
, text_subset
, piece
, sql_text
, address
, command_type
, last_snap_id
)
select /*+ ordered use_nl(vst) */
vst.hash_value
, new_sql.text_subset
, vst.piece
, vst.sql_text
, vst.address
, vst.command_type
, new_sql.snap_id
from (select hash_value
, address
, text_subset
, snap_id
from stats$sql_summary ss
where ss.snap_id = 8
and ss.dbid = 1638417141
and ss.instance_number = 1
) new_sql
, v$sqltext vst
where vst.hash_value = new_sql.hash_value
and vst.address = new_sql.address
order by vst.hash_value, new_sql.text_subset, vst.piece;
分析与结论:告警插入失败,违反STATS$SQLTEXT_PK唯一索引约束。
4、将STATS$SQLTEXT表的主键DISABLE,并建立一个普通索引,重新测试SNAP过程。
分析与结论:可以正常生成STATSPACK报告的TOP SQL章节。
三、总结:
1、这个故障主要是由于违反了主键约束,导致STATS$SQLTEXT表无法正常插入数据产生。
2、处理的同时也对STATS$SQLTEXT表进行了多次truncate操作,但仍然存在违反了主键约束的报错,可能存在BUG。