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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25152497/viewspace-1047752/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25152497/viewspace-1047752/