How volatile is ORA-00600 [qernsRowP] ?

ORA-600 [qernsRowP] seems related to a parallel query when the execution plan includes SORT GROUP BY NOSORT.

This Blog will demonstrate ORA-00600 [qernsRowP] with one small test code (probably one shortest ORA-00600 test code).

Note: Tested in Oracle 19.13, 19.7, 18.9, 12.1

1. Test Setup

drop type t_char100_varray50 force;

create or replace noneditionable type t_char100_varray50 as varray(50) of varchar2(100)
/

drop table test_tab cascade constraints;

create table test_tab as select level id, t_char100_varray50('a', 'b', 'c') vary
  from dual connect by level <= 1e4;
  
-- No primary key, no error
alter table test_tab add constraint test_tab#p primary key (id);

2. Test Run

Run following query, it throws ORA-00600 [qernsRowP].

select /*+ parallel(4) index(t test_tab#p) */ t.id, count(*)
  from test_tab t, table(t.vary) v
where rownum <= 3000
group by t.id;

    ID   COUNT(*)
 ----- ----------
     1          3
     2          3
     3          3
  ...
  2602          3
  2603          3
  
  ERROR:
  ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], [], [], [], [], [], [], []

  615 rows selected.
  
  Note that sometime error occurs alternatively (not in first run, but in second run).

If we limit rownum to a small number, e.g. 100, no error occurs, but exact number is hard to find (varying with Oracle release, OS, and run sequence).

-- rownum <= 100, no error. Exact number is not fixed. 
select /*+ parallel(4) index(t test_tab#p) */ t.id, count(*)
  from test_tab t, table(t.vary) v
where rownum <= 100
group by t.id;

    ID   COUNT(*)
  ---- ----------
     1          3
     2          3
   ... 
    33          3
    34          1

  34 rows selected.

If we remove parallel hint, there is also no more errors.

--remove parallel hint, no error.
select /*+ index(t test_tab#p) */  t.id, count(*)
  from test_tab t, table(t.vary) v 
where rownum <= 1000
group by t.id; 

        ID   COUNT(*)
  ----- ----------
      1          3
      2          3
    ...  
    333          3
    334          1

  334 rows selected.

For ORA-00600 [qernsRowP] query, Plan Table is dumped in session trace and incident file.
The rowsource line 1 contains "SORT GROUP BY NOSORT".

Plan Table
--------------------------------------------------------------------+-----------------------------------+-------------------------+
| Id  | Operation                             | Name      | Rows  | Bytes | Cost  | Time      | ObjectId  |  TQ  |IN-OUT|PQ Distrib |
--------------------------------------------------------------------+-----------------------------------+-------------------------+
| 0   | SELECT STATEMENT                      |           |       |       |  6016 |           |           |      |      |           |
| 1   |  SORT GROUP BY NOSORT                 |           |  3000 | 5540K |  6016 |  06:26:57 |           |      |      |           |
| 2   |   COUNT STOPKEY                       |           |       |       |       |           |           |      |      |           |
| 3   |    NESTED LOOPS                       |           |   46M |   86G |  6016 |  06:26:57 |           |      |      |           |
| 4   |     PX COORDINATOR                    |           |       |       |       |           |           |      |      |           |
| 5   |      PX SEND QC (RANDOM)              | :TQ10001  |  5963 |   11M |    44 |  00:03:50 |           |:Q1001| P->S |QC (RANDOM)|
| 6   |       TABLE ACCESS BY INDEX ROWID     | TEST_TAB  |  5963 |   11M |    44 |  00:03:50 | 4683207   |:Q1001| PCWP |           |
| 7   |        BUFFER SORT                    |           |       |       |       |           |           |:Q1001| PCWC |           |
| 8   |         PX RECEIVE                    |           |  5963 |       |    11 |  00:00:43 |           |:Q1001| PCWP |           |
| 9   |          PX SEND HASH (BLOCK ADDRESS) | :TQ10000  |  5963 |       |    11 |  00:00:43 |           |      | S->P |HASH (BLOCK ADDRESS)|
| 10  |           INDEX FULL SCAN             | TEST_TAB#P|  5963 |       |    11 |  00:00:43 | 4683210   |      |      |           |
| 11  |     COLLECTION ITERATOR PICKLER FETCH |           |  8168 |       |     8 |  00:00:31 |           |      |      |           |
--------------------------------------------------------------------+-----------------------------------+-------------------------+

Predicate Information:
----------------------
2 - filter(ROWNUM<=3000)
 
Content of other_xml column
===========================
  dop_reason     : hint
  dop            : 4
  px_in_memory_imc: no
  px_in_memory   : no
  db_version     : 19.0.0.0
-----------------

  Hint Report:
    Query Block: SEL$F5BB74E1
      Table: ("T"@"SEL$1") index(t test_tab#p)
    Statement: parallel(4)

Call Stack in incident file looks like:

  --------------------- Binary Stack Dump ---------------------
  [1]  (ksedst1()+95 -> kgdsdst())
  [2]  (ksedst()+58 -> ksedst1())
  [3]  (dbkedDefDump()+23448 -> ksedst())
  [4]  (ksedmp()+577 -> dbkedDefDump())
  [5]  (dbgexPhaseII()+2092 -> ksedmp())
  [6]  (dbgexProcessError()+1871 -> dbgexPhaseII())
  [7]  (dbgePostErrorKGE()+1853 -> dbgexProcessError())
  [8]  (dbkePostKGE_kgsf()+71 -> dbgePostErrorKGE())
  [9]  (kgeadse()+447 -> dbkePostKGE_kgsf())
  [10] (kgerinv_internal()+44 -> kgeadse())
  [11] (kgerinv()+40 -> kgerinv_internal())
  [12] (kgesinv()+21 -> kgerinv())
  [13] (ksesin()+180 -> kgesinv())
  [14] (qernsRowP()+501 -> ksesin())          --ERROR SIGNALED: yes   COMPONENT: SQL_Execution
  [15] (qercoRop()+111 -> qernsRowP())
  [16] (qerocpFetch()+428 -> qercoRop())
  [17] (qerocFetch()+201 -> qerocpFetch())
  [18] (qerjotRowProc()+397 -> qerocFetch())
  [19] (qerpxFetch()+995 -> qerjotRowProc())
  [20] (qerjotFetch()+2094 -> qerpxFetch())
  [21] (qercoFetch()+299 -> qerjotFetch())
  [22] (qernsFetch()+424 -> qercoFetch())
  [23] (opifch2()+3211 -> qernsFetch())
  [24] (opifch()+61 -> opifch2())
  [25] (opiodr()+1202 -> opifch())
  [26] (ttcpip()+1246 -> opiodr())  

MOS provides a workaround, but test shows that it does not work.

     ORA-600 [qernsrowp] (Doc ID 285913.1)
     ORA-00600 [QERNSROWP] When Running a Parallel Query With Group By NOSORT Option (Doc ID 984955.1)

workarounds

  Alter session set events '10119 trace name context forever, level 12';
  alter session set events '10119 trace name context forever';

MOS: Receiving ORA-600 [qernsRowP] Internal Error When Saving Changes. (Doc ID 455139.1) provides solution:

  -- To implement the solution, please execute the following steps::
  Setting cursor_sharing=EXACT

But our test DB is already set "Setting cursor_sharing=EXACT"

  SQL > show parameter cursor_sharing
  
     NAME             TYPE    VALUE
     ---------------- ------- -----
     cursor_sharing   string  exact