@@@客户***数据库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。