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).
-- 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.
--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.
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)
--------------------- 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())
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';
-- To implement the solution, please execute the following steps::
Setting cursor_sharing=EXACT
SQL > show parameter cursor_sharing
NAME TYPE VALUE
---------------- ------- -----
cursor_sharing string exact
