We are using version 12.1.0.2 of oracle Exadata. We have optimizer_index_cost_adj =100. optimizer_features_enable 11.2.0.4. We encountered failure of application with below error and then rerun of same process finished well without any issue. And then looking for root cause in oracle alert log we found below error.
Error found in alert log:-
ORA-07445: exception encountered: core dump [qertbStart()+3976] [SIGSEGV] [ADDR:0x0] [PC:0xCFD29C8] [Address not mapped to object] []
Error noticed in application level:-
"SQL Error for database action. Reason:No more data to read from socket. It is because No more data to read from socket"
Team mates and also the support is referring to below bug , however the query which is logged in the tracefile during failure doesn't have any "DISTINCT" or "any subquery in it. Its a plane sql query. So i was wondering how this bug can be tied with this query failure? Also want to understand from experts, as rerun finished well so how this bug can tied to this query then and why it didn't fail when rerun?
Bug 18201352 : ORA-7445 [QERTBSTART+3105] CRASHES QUERY WITH DISTINCT AND PJE TRANSFORMATION
sql query logged in trace:-
select c1,c2,c3,c4
,c5 from tab1 tab1
where
tab1.c1 = :1 and c2 = :2 and c6 = 'XX' and c7 = 'YY' and c8 = 'YY' and c9 is null
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3653 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| tab1 | 1 | 47 | 3653 (1)| 00:00:22 |
| 2 | INDEX RANGE SCAN | tab1_ix1 | 19192 | | 224 (0)| 00:00:02 |
----------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('12.1.0.2')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_optimizer_gather_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "tab1"@"SEL$1" ("tab1"."c1"
"tab1"."c3"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): XXXXX
2 - :2 (NUMBER): XX