记一次ora-7445 kkecpst()+824报错处理

报错信息：

Mon May 20 22:02:49 2019
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x12CDA84, kkecpst()+824] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/*****/*****/trace/*****_j002_234169.trc  (incident=323258):
ORA-07445: exception encountered: core dump [kkecpst()+824] [SIGSEGV] [ADDR:0x0] [PC:0x12CDA84] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/*****/*****/incident/incdir_323258/*****_j002_234169_i323258.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

trace文件中的部分dump

========= Dump for incident 323258 (ORA 7445 [kkecpst()+824]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x12CDA84, kkecpst()+824] [flags: 0x0, count: 1]
Registers:
%rax: 0x00007ff5a962a8a0 %rbx: 0x0000000000000000 %rcx: 0x0000000000000000
%rdx: 0x0000000000000000 %rdi: 0x00007ff5a962a8a0 %rsi: 0x00007ff5a962a978
%rsp: 0x00007fffe910f2b0 %rbp: 0x00007fffe910f2e0  %r8: 0x00007ff5a962ad00
 %r9: 0x0000000000002000 %r10: 0x0000000009b364e0 %r11: 0x0000000000000168
%r12: 0x0000000000000000 %r13: 0x00007ff5aa093040 %r14: 0x00007ff5a962ab68
%r15: 0x0000000000000000 %rip: 0x00000000012cda84 %efl: 0x0000000000010202
  kkecpst()+800 (0x12cda6c) mov 0x90(%r13),%rdi
  kkecpst()+807 (0x12cda73) mov 0x8(%rdi,%rdx),%rdi
  kkecpst()+812 (0x12cda78) mov 0x90(%r14),%r8
  kkecpst()+819 (0x12cda7f) mov 0x8(%r8,%rdx),%r15
> kkecpst()+824 (0x12cda84) mov (%r15),%r9
  kkecpst()+827 (0x12cda87) mov %r9,(%rdi)
  kkecpst()+830 (0x12cda8a) mov 0x8(%r15),%r10
  kkecpst()+834 (0x12cda8e) mov %r10,0x8(%rdi)
  kkecpst()+838 (0x12cda92) mov 0x10(%r15),%r11

经查询MOS，与文档：ORA-07445 kkecpst() While Gathering Statistics (文档 ID 1565008.1) 类似
问题发生的原因可能是由于bug引起的
Bug 14076166 - ORA-7445 KKECPST ON A SQL ON SYSMAN OBJECTS
出现这个问题表示有直方图数据损坏
可以通过修改隐藏参数_optimizer_cost_based_transformation解决

查看隐藏参数

col name for a30;
col value for a10;
select
x.ksppinm  name,
y.ksppstvl  value,
y.ksppstdf  isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')  ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and x.ksppinm ='_optimizer_cost_based_transformation'
order by
translate(x.ksppinm, ' _', ' ')
/

修改参数

alter system set "_optimizer_cost_based_transformation"=off scope=both;

MOS上另一种处理方案，重建损坏的直方图数据

Delete and re-gather histogram data for all columns of the problematic table

Assuming you are connected as the owner of the table.

To delete histograms from a column leaving behind base column statistics i.e. minimal information required for the Optimizer (this is for pre-Oracle11g versions:)

SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name> size 1');

To completely erase all types of column statistics/histograms for a column including minimal statistics:

SQL> exec dbms_stats.delete_column_stats(null, '<table_name>', '<column_name>');
 
This is generally not recommended as the Optimizer needs at least the minimal (size 1) information.


In Oracle11g there is new syntax to delete the histogram for a column while still leaving behind the base column statistics:

SQL> exec dbms_stats.delete_column_stats(null, '<table_name>', '<column_name>', col_stat_type=>'HISTOGRAM');

Additionally there is functionality to delete histograms for a partition:

exec dbms_stats.delete_column_stats(ownname=>'<owner>',tabname=>'<table_name>', colname=>'<column_name>',partname=>'<partition_name>', col_stat_type=>'HISTOGRAM')

and to delete column histograms for the table and all its partitions:

exec dbms_stats.delete_column_stats(ownname=>'<owner>',tabname=>'<table_name>', colname=>'<column_name>',cascade_parts=>TRUE,col_stat_type=>'HISTOGRAM')