Merge-Statement crashes with ORA-7445 [kdu_close] caused by Real Time Statistics?

In a recent project we migrated an Oracle database previously running on 12.1.0.2 on an Oracle Database Appliance to an Exadata X8 with DB version 19.7. Shortly after the migration a merge-statement (upsert) failed with an

ORA-07445: exception encountered: core dump [kdu_close()+107] [SIGSEGV] [ADDR:0xE0] [PC:0x1276AE6B] [Address not mapped to object] [] 

The stack looked as follows:

kdu_close - updThreePhaseExe - upsexe - opiexe - kpoal8 - opiodr - ttcpip - opitsk - opiino - opiodr - opidrv - sou2o - opimai_real - ssthrdmain - main - __libc_start_main - _start

As experienced Oracle DBAs know an ORA-7445 error is usually caused by an Oracle bug (defect). Searching in My Oracle Support didn’t reveal much for module “kdu_close” and the associated error stack. Working on a Service Request (SR) with Oracle Support hasn’t provided a solution or workaround to the issue so far as well. Checking Orafun also didn’t provide much insight about kdu_close other than the fact that we are in the area of the code about kernel data update (kdu).

As the merge crashed at the end of its processing (from earlier successful executions we knew how long the statement usually takes) I setup the hypothesis that this issue might be related to the 19c new feature Real Time Statistics on Exadata. To verify if the hypothesis is correct, I did some tests first with Real Time Statistics and merge-statements in my environment to see if they do work as expected and if we can disable them with a hint:

1.) Enable Exadata Features

alter system set "_exadata_feature_on"=TRUE scope=spfile;
shutdown immediate
startup

2.) Test if a merge-statement triggers real time statistics

I setup a table tab1 and tab2 similar to the setup on Oracle-Base and run a merge statement, which actually updates 1000 rows:

Initially we just have statistics on tab1 from dbms_stats.gather_table_stats. Here e.g. the columns:

testuser1@orcl@orcl> select column_name, last_analyzed, notes from user_tab_col_statistics where table_name='TAB1';

COLUMN_NAME      LAST_ANALYZED       NOTES
---------------- ------------------- ----------------------------------------------------------------
ID               07.08.2020 17:29:37
DESCRIPTION      07.08.2020 17:29:37

Then I ran the merge:

testuser1@orcl@orcl> merge
  2  into	tab1
  3  using	tab2
  4  on	(tab1.id = tab2.id)
  5  when matched then
  6  	     update set tab1.description = tab2.description
  7  WHEN NOT MATCHED THEN
  8  	 INSERT (  id, description )
  9  	 VALUES ( tab2.id, tab2.description )
 10  ;

1000 rows merged.

testuser1@orcl@orcl> commit;

Commit complete.

testuser1@orcl@orcl> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

testuser1@orcl@orcl> select column_name, last_analyzed, notes from user_tab_col_statistics where table_name='TAB1';

COLUMN_NAME      LAST_ANALYZED       NOTES
---------------- ------------------- ----------------------------------------------------------------
ID               07.08.2020 17:29:37
DESCRIPTION      07.08.2020 17:29:37
ID               07.08.2020 17:37:34 STATS_ON_CONVENTIONAL_DML
DESCRIPTION      07.08.2020 17:37:34 STATS_ON_CONVENTIONAL_DML

So obviously Real Time Statistics gathering was triggered.

After the verification that merge statements trigger statistics to be gathered in real time I disabled Real Time Statistics on that specific merge-statement by adding the hint

/*+ NO_GATHER_OPTIMIZER_STATISTICS */

to it.

testuser1@orcl@orcl> select column_name, last_analyzed, notes from user_tab_col_statistics where table_name='TAB1';

COLUMN_NAME      LAST_ANALYZED       NOTES
---------------- ------------------- ----------------------------------------------------------------
ID               07.08.2020 17:46:38
DESCRIPTION      07.08.2020 17:46:38

testuser1@orcl@orcl> merge /*+ NO_GATHER_OPTIMIZER_STATISTICS */
  2  into	tab1
  3  using	tab2
  4  on	(tab1.id = tab2.id)
  5  when matched then
  6  	     update set tab1.description = tab2.description
  7  WHEN NOT MATCHED THEN
  8  	 INSERT (  id, description )
  9  	 VALUES ( tab2.id, tab2.description )
 10  ;

1000 rows merged.

testuser1@orcl@orcl> commit;

Commit complete.

testuser1@orcl@orcl> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

testuser1@orcl@orcl> select column_name, last_analyzed, notes from user_tab_col_statistics where table_name='TAB1';

COLUMN_NAME      LAST_ANALYZED       NOTES
---------------- ------------------- ----------------------------------------------------------------
ID               07.08.2020 17:46:38
DESCRIPTION      07.08.2020 17:46:38

So the hint works as expected.

The statement of the real application was generated and could not be modified, so I had to create a SQL-Patch to add the hint at parse-time to it:

var rv varchar2(32);
begin
   :rv:=dbms_sqldiag.create_sql_patch(sql_id=>'13szq2g6xbsg5',
                                      hint_text=>'NO_GATHER_OPTIMIZER_STATISTICS',
                                      name=>'disable_real_time_stats_on_merge',
                                      description=>'disable real time stats');
end;
/
print rv

REMARK: If a statement is no longer in the shared pool, but available in the AWR history, you may use below method to create the sql patch:

var rv varchar2(32);
declare
   v_sql CLOB;
begin
   select sql_text into v_sql from dba_hist_sqltext where sql_id='13szq2g6xbsg5';
   :rv:=dbms_sqldiag.create_sql_patch(
             sql_text  => v_sql,
             hint_text=>'NO_GATHER_OPTIMIZER_STATISTICS',
             name=>'disable_real_time_stats_on_merge',
             description=>'disable real time stats');
end;
/
print rv

It turned out that disabling Real Time Statistics actually worked around the ORA-7445 issue. It might be a coincidence and positive side effect that disabling Real Time Statistics worked around the issue, but for the moment we can cope with it and hope that this information helps to resolve the opened SR so that we get a permanent fix from Oracle for this defect.

Update 24.08.2020

The opened SR has been updated with the following info:
The Development team confirmed that the error ORA-7445 [KDU_CLOSE] is due to an invalid pointer passed to the failing function kdu_close() from updThreePhaseExe(). This is caused by BUG 31237127, which is still under investigation by Oracle development. Once the bug is fixed My Oracle Support Note 31237127.8 will be available with details. There is no workaround available.